[jira] [Updated] (AMQ-7008) Add/document required index when using JDBC persistence adapter

["Jean-Baptiste Onofré (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/AMQ-7008?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jean-Baptiste Onofré updated AMQ-7008:
--------------------------------------
    Fix Version/s: 5.15.11

> Add/document required index when using JDBC persistence adapter
> ---------------------------------------------------------------
>
>                 Key: AMQ-7008
>                 URL: https://issues.apache.org/jira/browse/AMQ-7008
>             Project: ActiveMQ
>          Issue Type: Improvement
>          Components: Broker, JDBC
>    Affects Versions: 5.14.3, 5.15.4
>            Reporter: Jean-Baptiste Onofré
>            Assignee: Jean-Baptiste Onofré
>            Priority: Major
>             Fix For: 5.16.0, 5.15.11
>
>
> 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:
> {code}
> SELECT ID, MSG FROM ACTIVEMQ_MSGS WHERE CONTAINER=$1 AND ID < $2 AND ID > $3 AND XID IS NULL ORDER BY ID
> {code}
> 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):
> {code}
> CREATE INDEX activemq_msgs_pcx_asc_idx ON activemq_msgs (id ASC, xid NULLS FIRST, container);
> vacuum verbose analyze;
> {code}
> 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.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)