Gotchas with DBCP

I had to make use of DB connection pool in one of the applications that I was implementing. Since I am new to Java, everyone in the team was suggesting why don't you make use of Commons DBCP. Being a fan of reuse, I decided to make use of DBCP and started reading about the parameters that are exposed so that I can customize DBCP to fit my needs.

While reading through the available configuration parameters, I was thinking, "I don't need to check the connection to be bad on every borrow or return. It is sufficient if I check for being bad only when the connection is sitting idle in the pool." So I set the testWhileIdle to be true and set the validationQuery to be "SELECT 1 FROM DUAL". I was thinking that the configuration that I had was the optimal for the situation in hand.

But one thing I overlooked was, the default value for the testOnBorrow is true. So the moment you set the validationQuery to be any non-null value, every connection you borrow from the pool is going to be validated before it is returned to you. Effectively, if my application was executing 10 DB queries for each customer request, underneath there are 10 more validation requests being executed.

I realized this only when I ran some load tests. The performance was so poor that I couldn't belive what is wrong with my code. Especially even under moderate stress, the system was giving believable results.

So the moral of the story is, make sure you set all the parameters explicitly. Especially make sure you set testOnBorrow, testOnReturn and testWhileIdle parameters to fit your needs. Below is the list of properties that you can set/get in Apache Commons DBCP (version 1.2.2):

initialSize
maxActive
maxIdle
minIdle
maxWait
testOnReturn
testOnBorrow
validationQuery
numTestsPerEvictionRun
url
username
password
numActive (read only)
numIdle (read only)
defaultAutoCommit
defaultTransactionIsolation
timeBetweenEvictionRunsMillis
minEvictableIdleTimeMillis
defaultReadOnly
defaultCatalog
logWriter
loginTimeout
wrapperFor (read only)
testWhileIdle
driverClassName
accessToUnderlyingConnectionAllowed
removeAbandonedTimeout
logAbandoned
poolPreparedStatements
maxOpenPreparedStatements
removeAbandoned

Okay, those are just the list of properties. There is one more important that you should keep in your mind regarding the eviction thread. The process of eviction is synchronized on the entire connection pool. This means that while the eviction process is going on, no other thread can borrow or return connections from or to the pool. That means a total freeze for any other thread that that wants to borrow or return at that time. There is one parameter that you can use to tune the freeze time: numTestsPerEvictionRun. The default value of this is 3. This means that the eviction thread will first acquire a lock on the connection pool, will inspect three connections in the pool and release connection.

In case you have set testWhileIdle to be true(and provided validationQuery), then for each connection inspected and decided to be retained, the validationQuery will be executed. This is very important to keep in mind, since this will increase the amount of time the connection pool is locked.

Let us say you know that the idle connections in the pool is very small and you know that your database takes only 1 millisecond to execute "SELECT 1 FROM DUAL" (which is your validation query), you might consider keeping the numTestsPerEvictionRun value high. But again, is 3 millisecond (for checking three connections) high or low is entirely dependent on your application.

One middle ground between these two extremes (testOnBorrow and testWhileIdle) is to selectively validate connections once in every N borrows. For e.g. you should be able to ask DBCP to validate a connection once in every 10 borrows. This way you don't have to have the eviction thread running as well as you will not have any long freezes. But this facility is NOT available in Apache DBCP now. And I am not aware of any other connection pool that is providing this facility either.

Keep in mind that the eviction thread only validates idle connections. If there is a bad connection and it was borrowed from the pool all the time the eviction thread is running, then you will see that sporadically transactions will fail. Running eviction thread with testWhileIdle set to true is not a fool proof way to safeguard the connection pool from bad connections.

Hope that helps someone who is facing a similar problem as I did.