Database Connection Pool Configuration Recommendation

Configuring database connection pool is absolutely critical to the performance of the application. Configuring database connection pool is a double edged sword; too strict configuration would degrade the application’s response time, whereas too lenient configuration would consume lot of resources on the database server side. Thorough stress test needs to be conducted to come up with optimal configuration.

Here are essential recommendations in configuring database connection pool.

Max connection pool limit

It’s a recommended practice to set your Max connection pool limit to be equivalent of your peak traffic utilization.

Connection Checkout Wait period

What is the connection checkout wait period? Say suppose all the connections in the connection pool are busy, when a new request comes to check out a new connection from the connection pool, it would wait for stipulated time period. This period is called as connection checkout wait period. In several connection pools (like C3P0), default value is infinite. The request shouldn’t be allowed to wait for an indefinite period, as it would increase the overall response time of the transaction. Based on your application’s requirement a finite value has to be set for this property. Here is an example: If your application SLA promise is 1 sec and your average response time is 250ms, then you can set connection checkout wait period to be 200ms. So that your application has adequate time to meet your SLA promise.

LIFO Algorithm to checkout connection

Some connection pool implementations (like BoneCP) would allow the option of configuring the algorithm, based on which connections will be checked out. For Database connection pools LIFO (Last In First Out) is THE BEST algorithm. I will write a blog on this shortly, substantiating with proper tests. LIFO algorithm would help to keep less number of connections active to database. Less number of connections translates to less resource utilization on the database server.

Idle Timeout Period

When a connection is idle for a time period, those connections should be evicted from the connection pool. Idle connections would consume unnecessary memory on the database server side. Optimal idle timeout period should be set. Setting very less value would cause frequent destruction and construction of connection objects.

For typical online applications 5 minutes can be an idle timeout period.

Here is an interesting stress test report on the database connection pool performances.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: