JBM_MSG_REF primary key update performance issue (possible timeout/failure) --------------------------------------------------------------------------- Key: JBMESSAGING-1841 URL: https://issues.jboss.org/browse/JBMESSAGING-1841 Project: JBoss Messaging Issue Type: Enhancement Components: JMS Clustering, Messaging Core Persistence Affects Versions: 1.4.3.GA Environment: JBoss 5.1.0.GA in "all" configuration, PostOffice has FailoverOnNodeLeave = true, using MySQL Cluster 7.1.9a backend (ndb-persistence-service.xml) Reporter: Gerald Turner The JBM_MSG_REF table has primary key MESSAGE_ID+CHANNEL_ID. On cluster node failover a statement is run that changes CHANNEL_ID, thus modifies the primary key - an expensive operation. UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ? I'm using a pair of hefty servers for NDB storage node backends (16 CPUs, 8GB RAM dedicated to NDB process, no other processess). I've witnessed this update statement taking around 90 seconds with 70,000 messages in queue, and 180 seconds with 140,000 messages in queue. Our MySQL DataSource connections had parameter socketTimeout 30000, causing the failover process to rollback and retry several times until giving up, crashing the PostOffice. This timeout parameter can easily be changed or removed (block forever?), but failover still takes a very long time. I altered the JBM_MSG_REF table so that MESSAGE_ID is the only part of the primary key. This makes the failover CHANNEL_ID update statement run instantly. I can't imagine a case where the JBM_MSG_REF would have duplicate MESSAGE_ID rows. If this is reasonable, could the next release of jboss-messaging 1.4.x drop CHANNEL_ID from the primary key of the CREATE TABLE JBM_MSG_REF statement? Otherwise please let me know if this table alteration is unsafe. In particular, I changed one line of ndb-persistence-service.xml SqlProperties parameter, from: CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (MESSAGE_ID BIGINT, CHANNEL_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID)) ENGINE = NDBCLUSTER to: CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (MESSAGE_ID BIGINT, CHANNEL_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(MESSAGE_ID)) ENGINE = NDBCLUSTER -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira