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.
