Optimum Database Connection Pool Size

Long ago, I was working on a project where I dealt with a situation — what should be the optimum size of connection pool?

While I was aware that it had to be configured on the application server side (web sphere application server v.5.x, in this case), I did not know the optimum pool size.

So I started having a discussion with my peers to try to understand more about it. I received some input and one of them was good enough, backed by strong reasoning, for me to recommend it to the admin. So what was it?

It’s quite simple and straightforward — look for the maximum size of the web container thread pool.

What Is a Web Container Thread Pool?

This pool consists of a number of threads that an application server will create and use to process the requests concurrently. So, for example, if the thread pool size is 50 (not realistic figure), the maximum number of requests an application may process concurrently will be 50.

So, the connection pool size has to be lower than the maximum size of the web container thread pool. Does it look reasonable and logical?

Let’s Analyze It

Max size of web container thread pool: 50

Max size of connection pool size: 65

Now if there are 50 concurrent requests coming to our application server, only 50 connections will be consumed by those 50 requests. But we are still left with 15 more connections — a sheer waste of resource. So, keeping performance statistics in mind; the connection pool size should be lower than the maximum thread pool size.

It may be 30/35 in our case.

There is another factor/attribute that should be considered, connection timeout. This should be calculated by obtaining the average time a database operation takes in our application, plus a few seconds more (let’s say 5).

I believe this post will be quite enlightening for many, and I  hope to receive your input so that I may update my post accordingly.

I always believe that knowledge is free and should always be free.