[jira] [Created] (AMQ-7368) Performance issue on PostgreSQL when we have lot of pending messages

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

Jean-Baptiste Onofré created AMQ-7368:
-----------------------------------------

             Summary: Performance issue on PostgreSQL when we have lot of pending messages
                 Key: AMQ-7368
                 URL: https://issues.apache.org/jira/browse/AMQ-7368
             Project: ActiveMQ
          Issue Type: Bug
          Components: JDBC
    Affects Versions: 5.15.11, 5.15.10, 5.15.9
            Reporter: Jean-Baptiste Onofré
            Assignee: Jean-Baptiste Onofré
             Fix For: 5.16.0, 5.15.12


In Statements.java, we have the following query:
{code:java}
public String getFindNextMessagesStatement() {
    if (findNextMessagesStatement == null) {
        findNextMessagesStatement = "SELECT ID, MSG FROM " + getFullMessageTableName()
                                    + " WHERE CONTAINER=? AND ID < ? AND ID > ? AND XID IS NULL ORDER BY ID";
    }
    return findNextMessagesStatement;
} {code}
This statement is used in {{DefaultJDBCAdapter}} in the {{doRecoverNextMessages()}}.

In this method, we do:
{code:java}
s.setMaxRows(Math.min(maxReturned, maxRows)); {code}
{{setMaxRows}} is supposed to limit the number of rows. However, it doesn't seem to prevent the concrete SQL executed on the database side.

It means that we have a huge huge impact on performance (especially with lot of pending messages).



To avoid this performance issue, the findNextMessagesStatement select statement should use limit (with the same value of {{setMaxRows}}).

As it's more "postgresql" specific (any database supporting limit should do the same), we can update PostgreSQLJDBCAdapter to use limit.

It's not yet fully clear to me if the issue if about the PostgreSQL JDBC driver (bad "translation" of the {{setMaxRows()}}) or just the ActiveMQ select statement should use limit. 



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