Thanks everyone for the information and advice. Thanks to you we were able
to track this down to a specific version of Tomcat and DBCP. Simply copying
tomcat-dbcp.jar file from Tomcat 9.0.38 to Tomcat 9.0.33 and running Tomcat
9.0.33 results in the slowness. This means that the issue was introduced in
DBCP 2.8.0-SNAPSHOT 6d232e5. Using the tomcat-dbcp.jar file from Tomcat
9.0.72 results in the same slowness as Tomcat 9.0.38, so that means that
the newest version of DBCP 2.10.0-SNAPSHOT f131286 has not fixed the
problem.
It does sound like https://issues.apache.org/jira/browse/DBCP-558 has
introduced the problem with latency. Based on the linked Pull Request
<https://github.com/apache/commons-dbcp/pull/35> and implementation of
com.mysql.jdbc.ConnectionImpl it looks like calling
`connection.isReadOnly()` results in an extra trip to the database. Based
on implementation of com.mysql.jdbc.ConnectionImpl, providing an extra
parameter in connection URL: `&useLocalSessionState=true` fixes the
performance problem in Tomcat 9.0.69.
Without providing the extra `&useLocalSessionState=true` parameter in
connection URL, copying tomcat-dbcp.jar file from Tomcat 9.0.33 to Tomcat
9.0.69 also fixes the database performance in Tomcat 9.0.69 and it does not
seem to be causing any problems at first sight, so we have found the
culprit and we have 2 potential workarounds:
- Use tomcat-dbcp.jar file from Tomcat 9.0.33 in Tomcat 9.0.69, or
- Append `&useLocalSessionState=true` param to connection URL
A few follow up questions:
- Which of the two workarounds would be more recommended? Would there be
any problems with mismatching tomcat-dbcp.jar version or with using the
useLocalSessionState parameter?
- Since the solution in the Pull Request seems to result in a
considerable performance hit, would that be considered an issue or
necessary evil? Should we expect such an issue to be fixed in the future or
do we just need to adjust and live with it?
- Out of curiosity, has anyone else here noticed such database
connection performance degradation between these versions of Tomcat
(essentially between 9.0.37 or earlier, and 9.0.38 or later)? Since DBCP
2.8.0 was released in 2020-09-21 and Tomcat 9.0.38 was released
in 2020-08-11, it's a bit strange that this issue was not reported
previously by anyone.
Thank you,
Artur Tomusiak
On Wed, Feb 22, 2023 at 12:43 PM Mark Thomas wrote:
> On 22/02/2023 04:58, Konstantin Kolinko wrote:
> > ср, 22 февр. 2023 г. в 01:31, Artur Tomusiak - Hannon Hill
> > :
> >>
> >> After upgrading from Tomcat 9.0.33 to Tomcat 9.0.69,
> >
> > Note that using a binary search (bisection) one could limit the version
> range.
>
> Relevant version information is:
>
> 9.0.71 - DBCP f131286 2.10.0-SNAPSHOT 2022-12-30
> 9.0.53 - DBCP 2abdb49 2.9.02021-08-03
> 9.0.42 - DBCP e24196a 2.9.0-SNAPSHOT 2021-01-15
> 9.0.38 - DBCP 6d232e5 2.8.0-SNAPSHOT 2020-08-11
> 9.0.30 - DBCP a363906 2.8.0-SNAPSHOT 2019-12-06
>
>
> > Alternatively, it is possible to reconfigure the pool to use Apache
> > Commons DBCP 2 and Apache Commons Pool 2 directly (instead of
> > package-renamed version used by Tomcat), and bisect their version
> > ranges.
>
> Given Tomcat's tendency to update to specific commits rather than
> releases, bisecting Tomcat versions is probably easier.
>
> Does your database provide any form of debug logging? The additional
> commands should be easy to spot in such a log.
>
> Changes to autoCommit and readOnly handling in 9.0.38 could result in
> additional database calls. See
> https://issues.apache.org/jira/browse/DBCP-558
>
> I don't see anything else obvious in the change history but a debug log
> for the connection is likely to be the quickest way to see what is going
> on.
>
> Mark
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
>
