commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Steitz <>
Subject Re: DBCP Configuration Question
Date Mon, 22 Aug 2011 15:32:39 GMT
On 8/22/11 6:31 AM, wrote:
> Hi Phil,
> Thank you for your response.  What would you recommend these settings would be?  I was
think 15 to 20 minutes.

Comments to follow apply to DBCP 1.x.

First, make sure you are running the latest versions of both Commons
Pool and DBCP.  Earlier versions (DBCP <= 1.2.2, Pool <= 1.4) have
synchronization problems that can cause degraded performance when
using the pool maintenance thread.

Personally, I recommend not turning eviction on unless there is a
reason that you need it (i.e. leave timeBetweenEvictionRuns at the
default value, which disables the maintenance thread).  If the issue
is stale connections, I would first just try testOnBorrow with a
validation query.  If after this too many connections are still
ending up idle / going bad in the pool and performance is suffering,
then you can set timeBetweenEvictionRuns and
minEvictableIdleTimeMillis.  Here are some things to consider in
determining those settings if you decide to turn on pool maintenance:

Before setting minEvictableIdleTimeMillis to a positive value
(turning on eviction), first try setting testWhileIdle to true (with
a validation query) and timeBetweenEvictionRuns to a positive
value.  This will cause connections idle in the pool to be tested
when they are visited by the pool maintenance thread.  Bad
connections will be discarded and good ones may end up kept alive on
the server side.  The timeBetweenEvictionRunsMillis setting
determines how often the maintenance thread runs.  The
numTestsPerEvictionRun determines how many connections sitting idle
in the pool the thread visits on each run.  It cycles through the
idle instances in subsequent runs.  So if, for example, you have
timeBetweenEvictionRunsMillis set to 10 minutes,
numTestsPerEvictionRun set to 5 and you have 50 connections sitting
idle in the pool, each one will be visited, on average, every 100
minutes.  Note that this example assumes no movement in/out of the
pool.  If there is any activity at all, the frequency of visit will
be higher.  The maintenance thread in general works retrograde to
the pool queue order - oldest connections visited first.  If you
know how long it takes for a connection to go bad, your objective
should be to make sure idle connections get visited more frequently
than the time it takes to go bad.

If testWhileIdle does not work, then set minEvictableIdleTimeMillis
to a value less than the server side timeout and again arrange to
have connections visited on average more frequently than the timeout

Remember that the default value for testOnBorrow is true, so if you
supply a validation query and also set testWhileIdle to true,
connections will be tested both when they are borrowed and while
idle in the pool.  If the server is slow to respond to validation
queries or does not respond at all when validation is attempted on
stale connections, this can cause performance problems.  As of DBCP
1.3/1.4, you can set a validationQueryTimeout, but this timeout does
not guard against socket connection timeouts trying to reach the
server.  When dealing with bad drivers / engines that do not respond
immediately with errors on server-side closed connections, the best
strategy is to eliminate old connections using
minEvictableIdleTimeMillis.  This approach, however, causes more
connections to be created and destroyed than the testOnBorrow /
testWhileIdle approaches described earlier.

>> timeBetweenEvictionRunsMillis
>> minEvictableIdleTimeMillis
> Regards,
> Jeff
> -----Original Message-----
> From: Phil Steitz [] 
> Sent: Friday, August 19, 2011 3:49 PM
> To: Commons Users List
> Subject: Re: DBCP Configuration Question
> On 8/19/11 1:06 PM, wrote:
>> Hi Everyone,
>> What is the best way to config the commons DB connection pools to ensure there are
not any stale/bad connections?
> These comments refer to 1.x versions of DBCP.
> The simplest way is to set testOnBorrow to true and set up a validation query.  If your
app holds connections long enough for them to go bad before they get returned (generally a
bad idea) you can also set testOnReturn to true.  These settings, with a validation query,
cause the pool to test the connections on the way
> out / in to the idle instance pool.   You can also use the pool
> maintenance thread to "evict" connections that have been idle in the pool too long or
to test them periodically; but the first option
> (testOnBorrow) is usually sufficient.
>> Also, is there any relation between these 2 groups of parameters?
>> timeBetweenEvictionRunsMillis
>> minEvictableIdleTimeMillis
>> removeAbandoned
>> removeAbandonedTimeout
> Not really.  The first set of parameters control the behavior of the pool's maintenance
thread.  The second control  "abandoned"
> connection tracking and cleanup.  Abandoned connection cleanup is triggered by connection
requests and is not effected by the maintenance thread settings.
> Phil
>> Thank you for your help,
>> Jeff
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message