[jboss-jira] [JBoss JIRA] Commented: (JBMESSAGING-1841) JBM_MSG_REF primary key update performance issue (possible timeout/failure)

Mon Aug 1 14:20:23 EDT 2011

    [ https://issues.jboss.org/browse/JBMESSAGING-1841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12617941#comment-12617941 ] 

Ryan Hochstetler commented on JBMESSAGING-1841:
-----------------------------------------------

Gerald,

I'm speculating, and don't have time to test it for you, but I think I can imagine a scenario in which the JBM_MSG_REF table would have two rows with the same message ID: clustered topics.  

How has your modification stood-up to testing?  I wouldn't mind trying the performance improvement myself if it has worked consistently for you (and it isn't subject to the hypothetical weakness above).

queue the "this should be a forum discussion, not a bug comment" reply from a committer...

> 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
>            Assignee: Yong Hao Gao
>
> 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