Artur,
On 2/23/23 15:55, Artur Tomusiak - Hannon Hill wrote:
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?
I would strongly recommend using the request-parameter configuration. If
you try to stick with that version of tomcat-dbcp forever just to get
performance improvements, you may miss fixed bugs (including security
bugs) in the future. You also have to remember to re-downgrade Tomcat
every single time you upgrade it.
- 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?
You'd have to have this conversation with the commons-dbcp team, but I
suspect they would say it is a "necessary evil".
- 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.
I use Tomcat 8.5 which likely had a similar evolution. But we typically
do a of of work per connection-checkout and are not trying to perform
37k checkouts in a short amount of time. So we haven't noticed any
noticeable performance drop. Maybe our database has less latency than
yours (we also run MariaDB at $work).
-chris
On Wed, Feb 22, 2023 at 12:43 PM Mark Thomas <ma...@apache.org> wrote:
On 22/02/2023 04:58, Konstantin Kolinko wrote:
ср, 22 февр. 2023 г. в 01:31, Artur Tomusiak - Hannon Hill
<artur.tomus...@hannonhill.com>:
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.0 2021-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
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
