Tuesday, August 14, 2012

DB connection pool - production tips

Every developer knows that DB IO is the bottle neck of most web application.

It is also a common best practice to use an open connection to the DB and manage it in a pool, since opening a DB connection is costly operation.

However, in many DB connection pools tutorials, often the mentioned parameters are the size of the initial pool (min, max size) and how many connection to increment.

I would like to state here additional configuration which is important mainly in production use to efficient pool management -

The following are configuration parameters which are specific to C3P0 pool, however, one can

  1. maxIdleTime - Num of seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire. I would recommend not keeping idle connection much time.
  2. maxIdleTimeExcessConnections - number of seconds that Connections in excess of minPoolSize should be permitted to remain idle in the pool before being culled. Intended for applications that wish to aggressively minimize the number of open Connections.
  3. maxConnectionAge -  A Connection older than maxConnectionAge will be destroyed and purged from the pool. This differs from maxIdleTime in that it refers to absolute age.
  4. unreturnedConnectionTimeout - Will destroy open/active connection if  it wasn't returned to the pool within the specified amount of time. This could potentially prevent memory leaks if exception which prevent connection to close occurred. use it with parameter- debugUnreturnedConnectionStackTraces=true so you could debug the and find the reason for such behavior.

references - C3P0 configuration