Hibernate, C3P0, Mysql Connection Pooling

Question

I recently switched from Apache DBCP connection pooling to C3P0 and have gone through my logs to see that there are connection timeout issues. I haven't had this in the past with DBCP and Tomcat, so I'm wondering if it is a configuration issue or driver issue.

Whenever I load a page after the server has been idle for a while, I'll see that some content is not sent (as the server cannot get a connection or something). When I refresh the page, all of the content is there.

Does anyone recommend using the MySQL connection pool since I'm using MySQL anyway? What are your experiences with the MySQL Connection Pool?

Walter

Answer 1

If the database you're working with is configured to timeout connections after a certain time of inactivity, they are already closed and thus unusable when they are borrowed from the pool.

If you cannot or do not want to reconfigure your database server, you can configure C3P0 (and most other connection pools) to test the connections with a test query when they are borrowed from the pool. You can find more detailed information in the relevant section of the C3P0 documentation.

Edit: Of course you're right, it's also possible that there was a maximum idle time configured in the DBCP pool, causing them to be removed from the pool before they would time out. Anyway, using either a test query or making sure the connections are removed from the pool before they time out should fix the problem.

Answer 2

Just adding a link to another connection pool; BoneCP (http://jolbox.com); the connection pool that is faster than both C3P0 as well as DBCP.

Like C3P0 and DBCP, make sure you configure idle connection testing to avoid the scenario you described (probably MySQL's wait_timeout setting is kicking in, normally set to 8 hours).