Bug #37020 JDBC connections run SHOW VARIABLES and then SHOW VARIABLES LIKE 'tx_isolation'
Submitted: 27 May 2008 21:48 Modified: 29 Sep 2008 15:48
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: jdbc, show, variables

[27 May 2008 21:48] Mark Callaghan
Description:
At connection creation, JDBC clients run SHOW VARIABLES and then SHOW VARIABLES like 'tx_isolation'. There is no reason to run the second command given that the first has been run. The second command will be much faster if SELECT @@session.tx_isolation is used. I prefer that the first SHOW VARIABLES command be removed as well.

Several critical production servers have been made unusable soon after upgrades from MySQL4 to MySQL5 because of the drastic change in performance for SHOW commands because they use temp tables.

The JDBC option cacheServerConfiguration helps in some cases, but the default for it is False.

How to repeat:
Upgrade server to MySQL5. Panic.

Suggested fix:
Don't run SHOW commands. Use select @@session.VARIABLE when possible. Change the default for cacheServerConfiguration to True. Be a good corporate citizen. JDBC is the only client that trashes a server in this manner (Python, Perl and others are fine).
[27 May 2008 21:53] Mark Callaghan
Why is this even done?
SHOW VARIABLES LIKE 'tx_isolation'
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Show commands are much more expensive than SET SESSION. Just run the SET SESSION command without trying to figure out the current value of tx_isolation.
[27 May 2008 21:56] Mark Callaghan
Finally, this is also run: select round('inf'), round('-inf'), round('nan');
Given how far apart MySQL and ieee754 are, it is pointless to run this on modern versions of MySQL (note -- MySQL thinks it is OK to round infinity to MAX_DOUBLE when strict mode is not used)
[27 May 2008 22:02] Mark Matthews
Mark,

It's hard to remove the first show command, as the driver has to grab a bunch of various server variables in order to provide consistent behavior across different configurations and versions, and has to do it in ways that even MySQL 3.23 and 4.0 can support. 

As you state, "cacheServerConfig=true" helps, some. I'll look and see how "icky" it is to convert this to version-specific ways of getting this information which are less of an issue for 5.0. Unfortunately, the server is a bit inconsistent between what's supported in "SHOW VARIABLES" and "SELECT @@global.[variable_name]". For example, the JDBC driver needs the variable "language" to know what character set to use to decode error messages. It's not available through @@global.language.

The second part, "tx_isolation" has to do with a JDBC requirement that a call to Connection.getTransactionIsolation() always returns the "live" value as the backend sees it, so it can't be retrieved (at least after the first time) from the data from "SHOW VARIABLES". 

In Connector/J 5.0.6, this was changed to use "SELECT @@session.tx_isolation" for precisely the temp-table creating crippling under load that you're seeing. Do you happen to be using an older version?

Alternatively, if your *queries* never mess with the transaction isolation, and your applications only ever change transaction isolation via the setTransactionIsolation() method on java.sql.Connection, then using "useLocalSessionState=true" as a JDBC URL configuration will tell the driver to trust its own value, and *never* ask the server for it. This feature was introduced in C/J 3.1.7. It will also prevent *some* "chatter" when calling java.sql.Connection.getAutoCommit().
[27 May 2008 22:07] Mark Matthews
> Why is this even done?
> SHOW VARIABLES LIKE 'tx_isolation'
> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

> Show commands are much more expensive than SET SESSION. Just run the SET SESSION command
> without trying to figure out the current value of tx_isolation.

JDBC spec strikes again. There are many frameworks and applications that ask for transaction isolation, and we have to get it from the server when asked for (when running in a strictly-compliant way). 

The *first* time you see this come from the driver, it's internal (which could be optimized, but isn't today, given that there are other ways to optimize it, see previous comment). 

Any *further* calls you see of this query are coming from *something* in your stack calling Connection.getTransactionIsolation(), and *then* calling Connection.setTransactionIsolation(...)...We don't use "READ UNCOMMITTED" as a default *anywhere* in the JDBC driver. Do you use a connection pool of some sort? These are often the culprit of a bunch of spurious getTransactionIsolation(), and get/setAutoCommit() calls in an attempt to be "smart" about cleaning things up, or presenting connections from the pool in a consistent state (and often they're braindead about it, you should've come to my talk at the UC to see one painful example).
[27 May 2008 22:08] Mark Matthews
> Finally, this is also run: select round('inf'), round('-inf'), round('nan');
> Given how far apart MySQL and ieee754 are, it is pointless to run this on modern versions
> of MySQL (note -- MySQL thinks it is OK to round infinity to MAX_DOUBLE when strict mode
> is not used)

You *must* be using an old version of the driver, that hasn't been in the code for 3 years or so?
[27 May 2008 22:27] Mark Callaghan
We use 5.0.5 for JDBC. I will see if they can either upgrade to 5.0.6, or change 5.0.5 to use SELECT. Our code calls getTransactionIsolation() after connecting, so I will ask them to upgrade and set useLocalSessionState=True.