[
https://issues.apache.org/jira/browse/AMQ-7368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16998949#comment-16998949
]
Gary Tully edited comment on AMQ-7368 at 12/18/19 8:51 AM:
-----------------------------------------------------------
there was a generic limitQuery statement added in
https://issues.apache.org/jira/browse/AMQ-6049 b/c the oracle driver was doing
the same thing, fetching and discarding.
It may make sense to use that in this case and override for PostgreSQL
but in general, any database specific sql needs to go in the individual drivers
was (Author: gtully):
there was a generic limitQuery statement added in
https://issues.apache.org/jira/browse/AMQ-6049
it may make sense to use that in this case and override for PostgreSQL
but in general, any database specific sql needs to go in the individual drivers
> 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)
