[AMQ-7008] Add new index when using JDBC persistence adapter

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.14.3, 5.15.4
• Fix Version/s: 5.15.11, 5.16.0
• Component/s: Broker, JDBC
• Assignee: Jean-Baptiste Onofré
• Reporter: Jean-Baptiste Onofré
• Created: 10/Jul/18 11:46
• Updated: 20/May/20 13:45
• Resolved: 19/Nov/19 10:15

Description

When using a JDBC persitence adapter with a lot of pending messages in a queue, AMQ is almost stuck waiting the execution of the following SQL query:

SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER=$1 AND ID < $2 AND ID > $3 AND XID IS NULL ORDER BY ID

This query is not a problem when the number of pending messages stays "low". However, this query almost never ends when the number of pending messages is high.

In order to improve this query, the following index is required (I'm using PostgreSQL here):

CREATE INDEX activemq_msgs_pcx_asc_idx ON activemq_msgs (id ASC, xid NULLS FIRST, container);
vacuum verbose analyze;

I don't think it would be so easy to add such index in the JDBC adapter directly (I will investigate), but we need at least to document this.

Issue Links

• links to: GitHub Pull Request #417