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)
