Bug #37386 The Driver should cache server configuration by default
Submitted: 13 Jun 2008 8:27 Modified: 2 Feb 10:38
Reporter: Bernt Marius Johnsen Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:5.1.6 OS:Linux ( 2.6.24-16-generic )
Assigned to: Alexander Soklakov CPU Architecture:Any

[13 Jun 2008 8:27] Bernt Marius Johnsen
Description:
When a connection is created, ConnectionImpl.loadServerVariables() takes a long time (unless cacheServerConfiguration=true). If the rate of calls to getConnection is high (e.g. on my hardware, 1 pr. second) I get connection times up to several minutes and the app performs really badly. I would suggest that cacheServerConfiguration=true becomes the default.

(The culprit is the "SHOW VARIABELS ....." in ConnectionImpl.loadServerVariables())

Run on JRE 1.6.0_06-b02

How to repeat:
Create new connections at a high rate.
[17 Jun 2008 12:49] Tonci Grgin
Hi Bernt and thanks for your reasonable feature request. Let's see what Mark has to say.
[19 Jun 2008 13:52] Mark Matthews
I'd be interested in what version of the *server* you're connecting to, and on what platform. We've seen some issues recently with "SHOW VARIABLES" taking much longer than it should in MySQL-5.1.

Have you tried issuing "SHOW VARIABLES" from the client multiple times in a row?

We can't cache the variables by default, because that would provide behavior that is not always correct if someone changes their server configuration without restarting their application(s).
[20 Jun 2008 14:50] Bernt Marius Johnsen
The server was a 6.0.6 alpha running on Linux 2.6.24. I tried the SHOW VARIABLES... query separately (from the mysql SQL client) and measured response times ranging from 2 to 3 seconds when there where no other load on the server.
[2 Feb 10:38] Alexander Soklakov
Posted by developer:
 
This FR is irrelevant to the latest Connector/J 8.0, SHOW VARIABLES... query was replaced with SELECT... there. Server configuration cache is not always working as expected, as Mark mentioned, so changing the cacheServerConfiguration default to 'true' would be wrong.

Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html.