[
https://issues.apache.org/jira/browse/AMQ-7368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17048204#comment-17048204
]
ASF subversion and git services commented on AMQ-7368:
------------------------------------------------------
Commit 330b8e8431f68d3c2f77b0d052a7b347543eb72b in activemq's branch
refs/heads/activemq-5.15.x from jbonofre
[ https://gitbox.apache.org/repos/asf?p=activemq.git;h=330b8e8 ]
[AMQ-7368] Add limitQuery in HSQL, MySQL and PostgreSQL JDBC adapters
(cherry picked from commit 61bf624c6dd5a999dc9dd1f900bffc6c4feb9fa1)
> 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.9, 5.15.10, 5.15.11
> Reporter: Jean-Baptiste Onofré
> Assignee: Jean-Baptiste Onofré
> Priority: Major
> Fix For: 5.16.0, 5.15.12
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> 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)
