Uploaded image for project: 'ActiveMQ'
  1. ActiveMQ
  2. AMQ-7008

Add new index when using JDBC persistence adapter

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 5.14.3, 5.15.4
    • 5.15.11, 5.16.0
    • Broker, JDBC
    • None

    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.

      Attachments

        Issue Links

          Activity

            People

              jbonofre Jean-Baptiste Onofré
              jbonofre Jean-Baptiste Onofré
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 20m
                  20m