[jira] [Created] (AMQ-5359) DefaultDatabaseLocker is incompatible with Postgresql

["Peter Tillemans (JIRA)" <ji...@apache.org>]

Peter Tillemans created AMQ-5359:
------------------------------------

             Summary: DefaultDatabaseLocker is incompatible with Postgresql
                 Key: AMQ-5359
                 URL: https://issues.apache.org/jira/browse/AMQ-5359
             Project: ActiveMQ
          Issue Type: Bug
    Affects Versions: 5.6.0
            Reporter: Peter Tillemans


The way the DefaultDatabaseLocker is working to select the master in a master slave setup with shared database in a PostgreSQL is incompatible.

Currently ActiveMQ starts a transaction in the doStart() method and locks a record with ID=1 for update. The keepAlive updates this record from time to time and in the doClose() method the transaction is rolled back (?) which closes the transaction.

This means the transaction is open the complete time that the master broker is master.

This interferes in the way that postgresql reclaims deleted records. This is done with a process called VACUUM. What vacuum does is to see in which transaction a row was deleted and if this transaction is before the start of the longest running active transaction it is added back to the free list to be reused.

Since the broker keeps the transaction open, postgresql can never reclaim deleted rows in the activemq_msgs and activemq_acks tables so that these tables are essentially continuously appended to, leading to degrading performance over time.

example output:

INFO:  vacuuming "public.activemq_msgs"
INFO:  "activemq_msgs": found 0 removable, 1513322 nonremovable row versions in 265477 pages
DETAIL:  1505390 dead row versions cannot be removed yet.

This output was for a table with about 8000 active messages in the queues, however the actual table on disk contained 1.5M rows. 

This leads to terrible performance over time.

It appears the LeaseDatabaseLocker does not exhibit this behavior.

I think that 'fixing' this in the documentation by adding a warning not to use default database locker with postgresql is the simplest solution.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)