Connection pool C3P0 logging

Question

I have a web application that uses c3p0 as the connection pool. we use hibernate as the orm tool. Recently, we have been getting connection timeout exceptions. To debug these exception, i enabled the logging fro C3p0 and got some information in logs. Can anybody help me in making complete sense out of it.

DEBUG 2012-08-05 14:43:52,590 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0] com.mchange.v2.c3p0.stmt.GooGooStatementCache: checkinAll(): com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache stats -- total size: 2; checked out: 0; num connections: 1; num keys: 2

from the above, i can observe that the total size of the connection pool is 2. No. of checked out connections are 0. Is this right? And what are num_connections and num keys in the above?

Thanks..

Answer 1

What you are seeing in the bit of log that you've quoted is a snapshot of the Statement cache, not the Connection pool. There were two cached PreparedStatements, which belonged to a single Connection, at the time that message was logged. Neither of the Statements was checked out/in use.

I hope this helps!

Answer 2

The exceptions might be due to incorrectly defining the C3P0 settings. Ensure that you link to the right jar file Your application must be linked to "hibernate-c3p0*.jar" and not to "c3p0*.jar". The hibernate.cfg file must have the hibernate.connection.provider_class property defined in order for the c3p0 settings to take effect. Below is a sample settings

<property name="hibernate.connection.provider_class"> org.hibernate.connection.C3P0ConnectionProvider </property>

<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>

The log entry specifies that there are 4 connections in the pool and all the 4 connections are available for the application to use. Running a query towards your database to list all the active connections will show the pool connections. For eg in Mysql you could run "show processlist;" to see these connections.