[
https://issues.apache.org/jira/browse/AMQ-7368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16998566#comment-16998566
]
Jean-Baptiste Onofré commented on AMQ-7368:
-------------------------------------------
[~gtully] [~cshannon] any thought about this one ? I'm more thinking about an
issue on the PostgreSQL JDBC driver, but I would like to have your thoughts.
> 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
>
>
> 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)
