On 7/1/07, Bennett McElwee [EMAIL PROTECTED] 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=1
dbcp.validationQuery=select 1 from dual
dbcp.testOnBorrow=true
dbcp.testOnReturn=true
dbcp.testWhileIdle=true
dbcp.timeBetweenEvictionRunsMillis=3
dbcp.numTestsPerEvictionRun=3
dbcp.minEvictableIdleTimeMillis=90
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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
