Re: Communications link failure due to underlying exception
Posted by: Mark Matthews
Date: July 19, 2005 07:41AM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: Communications link failure due to underlying exception
July 19, 2005 07:41AM
June 09, 2006 04:42AM
August 24, 2006 05:27PM
July 15, 2007 04:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.