Peter Andrews wrote:
> My final conclusion is that useLocalSessionState
> and autoReconnect are incompatible. I was using
> useLocalSessionState because I could not get
> elideSetAutoCommits=true to work without it.
Peter,
Hrm, useLocalSessionState and elideSetAutoCommits shouldn't be related, neither should autoReconnect and useLocalSessionState (I'd be interested to hear in the behavior you observe). elideSetAutoCommits only works with MySQL-4.1 or newer, are you using that?
I'm also curious as to why you need 1000 connections and up to 500 idle ones. Most deployments of MySQL using DBCP use _much_ smaller numbers and are fine. You should probably have almost _no_ idle connections allowed unless you have very spikey load. Having connections lingering around idle wastes resources (client and server side), and exposes you to the risk of being disconnected (wait_timeout on MySQL, firewalls timing out if you're going through a DMZ, someone rebooting a switch or the server, etc).
I don't recommend using "autoReconnect=true" in production deployments where you have other options available. It's sole purpose is for old deployments that don't pool, or pools that can't test connections, or expire idle ones after some timeout.
If you really need to hang on to idle connections using DBCP, I'd suggest you turn on idle connection testing, and make the checking interval shorter than wait_timeout (from the documentation for Connector/J):
<!-- Don't use autoReconnect=true, it's going away eventually
and it's a crutch for older connection pools that couldn't
test connections. You need to decide if your application is
supposed to deal with SQLExceptions (hint, it should), and
how much of a performance penalty you're willing to pay
to ensure 'freshness' of the connection -->
<parameter>
<name>validationQuery</name>
<value>SELECT 1</value>
</parameter>
<!-- The most conservative approach is to test connections
before they're given to your application. For most applications
this is okay, the query used above is very small and takes
no real server resources to process, other than the time used
to traverse the network.
If you have a high-load application you'll need to rely on
something else. -->
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<!-- Otherwise, or in addition to testOnBorrow, you can test
while connections are sitting idle -->
<parameter>
<name>testWhileIdle</name>
<value>true</value>
</parameter>
<!-- You have to set this value, otherwise even though
you've asked connections to be tested while idle,
the idle evicter thread will never run -->
<parameter>
<name>timeBetweenEvictionRunsMillis</name>
<value>10000</value>
</parameter>
<!-- Don't allow connections to hang out idle too long,
never longer than what wait_timeout is set to on the
server...A few minutes or even fraction of a minute
is sometimes okay here, it depends on your application
and how much spikey load it will see -->
<parameter>
<name>minEvictableIdleTimeMillis</name>
<value>60000</value>
</parameter>
Of course checking _500_ idle connections will take some time. (I'd double check that setting, myself).
-Mark
Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html