MySQL Bugs: #37386: The Driver should cache server configuration by default

Bug #37386	The Driver should cache server configuration by default
Submitted:	13 Jun 2008 8:27	Modified:	2 Feb 2022 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

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.

Hi Bernt and thanks for your reasonable feature request. Let's see what Mark has to say.

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).

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.

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.