Broker startup very slow when using mysql jdbc

[glassfishrobot]

Broker startup is very slow if you restart a broker with a lot of messages and transactions , using enhanced broker cluster with mysql jdbc persistence,persist all messages and auto-ack.

Restarting a broker with 10GB data in 70 000 messages, 14000mb Xmx on broker and 24000 txn out of which 23800 in commited state takes about 3 hours.

Affected Versions

[4.5.2, 5.1.1]

[glassfishrobot]

@glassfishrobot Commented
Reported by rhadoo_io

[glassfishrobot]

@glassfishrobot Commented
rhadoo_io said:
About one hour is spent on queries like "DELETE FROM MQMSG41Cjms WHERE DESTINATION_ID = 'Q:somerandomqueue' AND STORE_SESSION_ID IN (SELECT ID FROM MQSES41Cjms WHERE BROKER_ID = 'brokername1')\G".
This will endup using the Primary key:
MQMSG41Cjms | CREATE TABLE MQMSG41Cjms (
ID varchar(100) NOT NULL,
MESSAGE longblob NOT NULL,
MESSAGE_SIZE int(11) DEFAULT NULL,
STORE_SESSION_ID bigint(20) NOT NULL,
DESTINATION_ID varchar(100) DEFAULT NULL,
TRANSACTION_ID bigint(20) DEFAULT NULL,
CREATED_TS bigint(20) NOT NULL,
PRIMARY KEY (ID),
KEY MQMSG41CjmsIDX1 (STORE_SESSION_ID,DESTINATION_ID),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ,
which will endup doing a scan on the first key STORE_SESSION_ID, and thus parsing the entire table for each queue.
In my case this takes about 350s/queue, the more queues the worse the startup time.

Adding the index :
create index DESTINATION_ID_idx on MQMSG41Cjms(DESTINATION_ID) reduces the start time on both OpenMQ4.5.2 and 5.1 to about 20 minutes, with no downside for our producers/consumers, perhaps with a marginal improvement on throughput.

I suggest adding the index in default properties for mysql InnoDb jdbc persistence.

[glassfishrobot]

@glassfishrobot Commented
rhadoo_io said:
Mysql driver config is:
imq.persist.jdbc.mysql.driver=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource which causes significant performance issues.
Using imq.persist.jdbc.mysql.driver=com.mysql.jdbc.jdbc2.optional.MysqlDataSource in conjunction with
imq.persist.jdbc.connection.validateOnGet=true
imq.persist.jdbc.connection.validationQuery=/* ping */
brings about 50% performance increase

[glassfishrobot]

@glassfishrobot Commented
This issue was imported from java.net JIRA MQ-363

[glassfishrobot]

• Issue Imported From: https://github.com/javaee/openmq/issues/363
• Original Issue Raised By:@glassfishrobot
• Original Issue Assigned To: @amykang2020