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: | |
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
[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.