Wednesday, April 01, 2009

Is my DBCP configuration bad or my database slow?

Let us say you are making use of DBCP in your application. You are running a load test on your application and you find that the response time is terrible. You suspect that it is the database that is messing up all the performance. Well, as an application developer that would be my first response! But how do I prove that it is the database that is causing the issues, and not my application. The first thing you should be checking is the number of load testing client threads versus the number of connections in your DBCP. If you have more number of client threads than the number of connections in your DBCP, there is a likelihood of your application being the root cause for the bad response.

The easiest way to confirm this one is by using the maxWait parameter of DBCP. You just set the maxWait to a value that you expect the average waiting time to be. For e.g. in my case I never expect the wait time to be more than 30 milliseconds. Once you set this watch for errors in your application logs. You will find that a lot of the following exceptions:
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

This simple yet powerful experiment will help you identify where the long running transactions are spending most of the time, waiting in the queue for getting a connection or executing in the query in the database.

On a side note, I would like to point out that the queuing policy of DBCP is unfair. Meaning, there is no guarantee that the first request will get a connetion from the pool first. This may be because of the fact that the GenericObjectPool might rely upon the Object.notifyAll() to notify the waiting threads.

No comments: