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.

Comments

Post a Comment

Popular posts from this blog

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 po
Read more

A note on Java's Calendar set() method

Remember that the Calendar's internal fields include year, month, date, hour, minutes, seconds, milliseconds and time zone. Whenever you are calling a set() method with multiple fields, like set(year, month, date), it will not affect the rest of the fields. Remember that there is no set() method with multiple fields available to set the milliseconds. If you would like to set the milliseconds, you must use set(Calendar.MILLISECOND, value). Likewise, if you are planning to set all the fields, its a good idea to reset all the fields using clear() method. This will clear milliseconds as well. Most of the times, millisecond field may not be of interest to you. But if you are going to use the UTC milliseconds, by calling getTimeInMillis(), then make sure you set the right values for milliseconds as well.
Read more

The mysterious ORA-03111 error

Recently one of the applications that I developed started throwing exceptions, that had the following message: SQL state [72000]; error code [1013]; ORA-03111: break received on communication channel When I googled around, I couldn't come across anything useful. Sadly enough most of the sites just showed the documentation for that error, without any explanation from anyone experiencing that issues. So here you go, with the best possible explanation that I could come up with. My application sets two things on the connection that is throwing this exception: It sets the fetchSize to be 2500 rows It sets the query timeout to be 10 seconds The database server and the application are separated over a long latency network (actually there is a NetEm box that emulates the long latency between these two boxes) which has a latency characteristic of 50+/-5 milliseconds. This is the whole setup. It is important to understand how the timeout is handled by the Oracle client (in my case JDBC clien
Read more