You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Je...@Dell.com on 2011/08/19 22:06:23 UTC
DBCP Configuration Question
Hi Everyone,
What is the best way to config the commons DB connection pools to ensure there are not any stale/bad connections?
Also, is there any relation between these 2 groups of parameters?
timeBetweenEvictionRunsMillis
minEvictableIdleTimeMillis
removeAbandoned
removeAbandonedTimeout
Thank you for your help,
Jeff
DBCP 1.4 autocommit
Posted by Andrea Sodomaco <an...@sodomaco.it>.
Hi all,
I can't understand why in
org.apache.commons.dbcp.PoolableConnectionFactory in the passivateObject
method autocommit is set true:
lines 688-680 (dbcp 14)
if(!conn.getAutoCommit()) {
conn.setAutoCommit(true);
}
If defaultAutoCommit is set to false the query "SET autocommit=1" should
be executed for each getConnection() call.
This is the all queries log result:
* 12 Query SET autocommit=1
12 Query SET autocommit=0
* 12 Query select
chiave,email,password,status,props,syncro,tipoUtente,sito,newprops from
utenti where sito='fie' and (tipoUtente='SCUOLASCI' and status=2)
12 Query rollback
* 12 Query SET autocommit=1
12 Query SET autocommit=0
* 12 Query select
chiave,email,password,status,props,syncro,tipoUtente,sito,newprops from
utenti where sito='fie' and (tipoUtente='BIKERENTAL' and status=2)
12 Query rollback
* 12 Query SET autocommit=1
12 Query SET autocommit=0
* 12 Query select
chiave,email,password,status,props,syncro,tipoUtente,sito,newprops from
utenti where sito='fie' and (tipoUtente='RIFUGIO' and status=2)
12 Query rollback
* 12 Query SET autocommit=1
12 Query SET autocommit=0
* 12 Query select
chiave,email,password,status,props,syncro,tipoUtente,sito,newprops from
utenti where sito='fie' and (tipoUtente='SKIRENTAL' and status=2)
12 Query rollback
12 Query SET autocommit=1
thank you in advance for any suggestion.
Andrea
Re: DBCP Configuration Question
Posted by Phil Steitz <ph...@gmail.com>.
On 8/22/11 6:31 AM, Jeff_Blumenthal@Dell.com 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
value.
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.
Phil
>
>> timeBetweenEvictionRunsMillis
>> minEvictableIdleTimeMillis
> Regards,
> Jeff
> -----Original Message-----
> From: Phil Steitz [mailto:phil.steitz@gmail.com]
> Sent: Friday, August 19, 2011 3:49 PM
> To: Commons Users List
> Subject: Re: DBCP Configuration Question
>
> On 8/19/11 1:06 PM, Jeff_Blumenthal@Dell.com 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: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
RE: DBCP Configuration Question
Posted by Je...@Dell.com.
Hi Phil,
Thank you for your response. What would you recommend these settings would be? I was think 15 to 20 minutes.
> timeBetweenEvictionRunsMillis
> minEvictableIdleTimeMillis
Regards,
Jeff
-----Original Message-----
From: Phil Steitz [mailto:phil.steitz@gmail.com]
Sent: Friday, August 19, 2011 3:49 PM
To: Commons Users List
Subject: Re: DBCP Configuration Question
On 8/19/11 1:06 PM, Jeff_Blumenthal@Dell.com 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: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP Configuration Question
Posted by Phil Steitz <ph...@gmail.com>.
On 8/19/11 1:06 PM, Jeff_Blumenthal@Dell.com 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: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org