commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Phil Steitz" <phil.ste...@gmail.com>
Subject Re: DBCP sample configuration parameters? (pool connections failing)
Date Mon, 02 Jul 2007 06:38:59 GMT
On 7/1/07, Bennett McElwee <Bennett.McElwee@telecom.co.nz> wrote:
> What is a good conservative set of configuration parameters for DBCP?
>
> We are having some problems with a web app using DBCP. It looks as if
> the problems are with the connection pool, but it's not easy to find
> much discussion of basic or recommended settings for things like abandon
> settings, eviction timeouts, etc. If you have any recommendations based
> on the information below we would be grateful. I will post a summary if
> I get any replies.
>
> General
> * The web app is actually a portlet application, running under Vignette
> Portal.
> * A firewall sits between the DB and app servers, and the firewall
> timeout = 60 minutes
> * The web app uses Spring 2 for connection management.
> * Traffic levels would be around 10-50 queries per second. Most queries
> are <100ms, but a few take around 5 seconds.
>
> DBCP
> * The web app uses DBCP connecting to Oracle 8i with BasicDataSource
> with pool settings given below.
> * The connection pool seems unreliable - it works sometimes and not
> others.
> * When it fails we see either 'socket error' or 'connection already
> closed' errors in the logs, at the point where the code goes to grab a
> new connection
> * Sometimes simply a browser refresh fixes it (for that request)
> * It's as if some connections are invalid, or losing connections... like
> the connection pool is not being maintained properly
> * DBCP Settings:
> dbcp.defaultAutoCommit=true
> dbcp.defaultReadOnly=false
> dbcp.initialSize=10
> dbcp.maxActive=50
> dbcp.maxIdle=10
> dbcp.minIdle=5
> dbcp.maxWait=10000
> dbcp.validationQuery=select 1 from dual
> dbcp.testOnBorrow=true
> dbcp.testOnReturn=true
> dbcp.testWhileIdle=true
> dbcp.timeBetweenEvictionRunsMillis=30000
> dbcp.numTestsPerEvictionRun=3
> dbcp.minEvictableIdleTimeMillis=900000
> dbcp.poolPreparedStatements=false
> dbcp.maxOpenPreparedStatements=10
> dbcp.removeAbandoned=true
> dbcp.accessToUnderlyingConnectionAllowed=false
> dbcp.removeAbandonedTimeout=60
> dbcp.logAbandoned=true
>


Bennet,

Best settings for DBCP really depend on load, database response
patterns, how good the client code is at closing connections (best is
not to rely on the pool to try to detect and remove abandoned
connections) and how expensive it is for you to have connections open.
 One rule of thumb that could help your situation is that if your load
or database response time is variable, it is not a good idea to have
minIdle and maxIdle close to each other in value.  Under variable
load, this can cause lots of physical open/close operations as the
pool tries to maintain the number of idle connections in the range
between these values.  Especially on Windows, that can lead to socket
errors.  You might try eliminating the minIdle setting, which will
default it to 0.  You might also want to increase the maxIdle setting.

The "connection already closed" errors can mean that a client is
trying to close the same (logical) connection twice.  DBCP throws
SQLExceptions when a client tries to close a logical connection that
has already been closed and released back into the pool.  Can you
include more information, ideally stack traces, showing the errors
that you are seeing?  Also, please provide the version of DBCP and
commons pool that you are running, the jdk, oracle driver and
operating system that the client code is running.

One more thing that can be useful in tuning the pool is to observe the
number of physical connections open and the number of physical
open/close operations over time by monitoring the database.

Phil

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message