On 8/22/11 6:31 AM, [email protected] 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:[email protected]] > Sent: Friday, August 19, 2011 3:49 PM > To: Commons Users List > Subject: Re: DBCP Configuration Question > > On 8/19/11 1:06 PM, [email protected] 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: [email protected] > For additional commands, e-mail: [email protected] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
