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 a lot of resources on the database server side. Thorough stress test needs to be conducted to come up with an 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 will 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 the 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.