C3P0 Connection Pooling

I have been playing around with the C3P0 JDBC3 Connection and Statement Pooling library to figure out the best way to manage connections to a MySQL server.

Ideally I want to be in a position where connections to the MySQL server are dropped after a certain idle period and automatically reconnected when needed because I don’t want to hold idle connections to the server indefinitely. Making a connection to a MySQL server is very cheap, while holding idle connections is wasteful.

So playing around with C3P0, and reading the documentation, (and talking to the developer Steve Waldman) I came up with the following usage patterns:

• Use maxIdleTime if you want a connection to be dropped after a specified idle time though I have noticed that while these connections are dropped, they are sometimes reconnected after a period of time presumably to satisfy a requirement for a minimum number of connections. You can cause a quick release with maxIdleTimeExcessConnections.
• Use idleConnectionTestPeriod if you want to keep the connections open (setting preferredTestQuery to do the job, for MySQL I use “SELECT 0″), this will make sure that connections checked in the pool are kept alive. You also need to make sure that test period is shorter than the wait_timeout set in my.cnf. I use 55 and 60 respectively.
• It probably is good policy to use testConnectionOnCheckin so that connections are verified as ok before being checked back into the pool. This is usually combined with idleConnectionTestPeriod to cause the connection to be verified as it is checked into the pool and then tested on a periodic basis.
• You can also use testConnectionOnCheckout to verify a connection every time it is checked out of the pool but this is not recommended because it can be very expensive.