[
https://issues.apache.org/jira/browse/AMQ-7368?focusedWorklogId=395406&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-395406
]
ASF GitHub Bot logged work on AMQ-7368:
---------------------------------------
Author: ASF GitHub Bot
Created on: 29/Feb/20 06:05
Start Date: 29/Feb/20 06:05
Worklog Time Spent: 10m
Work Description: jbonofre commented on pull request #480: [AMQ-7368] Add
limitQuery in HSQL, MySQL and PostgreSQL JDBC adapters
URL: https://github.com/apache/activemq/pull/480
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
Issue Time Tracking
-------------------
Worklog Id: (was: 395406)
Time Spent: 20m (was: 10m)
> 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)
