Hot tip for gigantic queues + MySQL

[emcee21 <mi...@gmail.com>]

Running 4.1.1, journaled-jdbc on MySQL 5.0, I have a 10GB ACTIVEMQ_MSGS table
with ~100k messages.  I found the default "deleteOldMessagesStatement" was
performing very poorly.  This is due to a known issue on MySQL with
correlated subqueries.  

To address the problem, I created the following stored procedure:

CREATE PROCEDURE deleteOldMessages (minExpiration LONG)
BEGIN

CREATE TEMPORARY TABLE MinAcks SELECT CONTAINER, MIN(LAST_ACKED_ID) id from
ACTIVEMQ_ACKS GROUP BY CONTAINER;

delete m from ACTIVEMQ_MSGS m LEFT OUTER JOIN MinAcks a ON m.CONTAINER =
a.container WHERE (a.id is not null and m.ID <= a.id) or (expiration <> 0
and expiration < minExpiration);

DROP TEMPORARY TABLE MinAcks;

END


and added the following to my broker-config.xml:

		<persistenceAdapter>
			<journaledJDBC journalLogFiles="5"
				dataDirectory="activemq-data" dataSource="#queueDataSource"
				useDatabaseLock="false">
				<statements>
					<statements deleteOldMessagesStatement="call deleteOldMessages(?)"/>
				</statements>
			</journaledJDBC>
		</persistenceAdapter>


Believe it or not, this provided a drastic improvement in performance for my
application.
-- 
View this message in context: http://www.nabble.com/Hot-tip-for-gigantic-queues-%2B-MySQL-tf4695270s2354.html#a13421060
Sent from the ActiveMQ - User mailing list archive at Nabble.com.