C3P0 - Managing Connection Pool

C3P0 is an open source database connection pool library. By using the ComboPooledDataSource and reading the document, you can easily set up a database connection pool for your application.

I will talk a bit more about managing database connection pool. A database connection pool is simply just a group of persistent database socket.

Why do we want a connection pool? Mainly for performance. A pool allow you to reuse the same set of established persistent connections over and over again. This will greatly reduce the wait time due to connection establishment.

For example, a naive new connection usually require the following time

1. network connection setup + database connection initialization
2. database transaction and commit
3. database connection close + network socket close

This is fine if we are only performing a single database transaction. Imagine if we are performing hundreds of transaction per minute. Time used for step 1 and 3 will hinder the application performance.

If you are using a connection pool, after initial connection establishment, only step 2 time is a factor on application performance.

Then, since the connection is persistent, why don't we just use 1 persistent connection instead of a connection pool? Database transaction requires time. It may take a long transaction if the commit are huge. Nowaday, applications perform multiple transaction in a seconds. So, if you use only 1 persistent connection and the current transaction is huge, it will create a "hang" effect of the application.

You may be thinking that since the connection is persistent, won't it waste system resource if the application idle most of the time? Connection pool is a balance between performance and resource. If you have a huge pool, you may have a good application performance. However, you will be using a lot of system resource. The reverse applies.

Now, back to C3P0. By default, C3P0 does not expires connections. So, if you use their default setup, you may see your connection increase over time and occupying the system resource. And at one point of time, you may see your application complaining for "connection limit exceeded"

To handle this issue, the following will help

initialPoolSize - This allow you to set the initial pool size for your connection pool. C3P0 will only accept logical input. And by testing, the value must be more than 2. If not, it will set to default value of 3.

minPoolSize - This allow you to set the minimum pool size for your connection pool. Minium pool size means that your pool will always have this amount of connections in your application at any time.

maxIdleTimeExcessConnections - This allow you to set the timeout value in second for connections that is over your minPoolSize. This is an important value if you want to maintain your connection pool as close to minPoolSize as possible or prevent connection spike that cause a sudden increase of your connections.

An example - initialPoolSize = 2, minPoolSize = 1, maxIdleTimeExcessConnections = 300

This means C3P0 will start a connection with initial of 2 connections and C3P0 will actively purge any connection that over minPoolSize that has idle for 5 minutes. So, after the startup, C3P0 will purge the initial 2 connections down to 1 connection (minPoolSize) if either of them idle for 5 minutes


Reference: http://www.mchange.com/projects/c3p0/#managing_pool_size