How to monitor c3p0 connections

I am using Hibernate in my JBoss war, using c3p0 for connection pooling, both configured within a hibernate.cfg.xml config file in my classpath

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

I've seen server.log generates lines with interesting information about the connection pool:

DEBUG [com.mchange.v2.resourcepool.BasicResourcePool] trace com.mchange.v2.resourcepool.BasicResourcePool@63f5e4b6 [managed: 10, unused: 9, excluded: 0]

For my monitoring pool (I am using nagios) I'd like to provide a JSP telling how many connections are being used and how many are free, as the log file says.

How can I ask c3p0 how many managed and unused connections are there?

Solution 1:

You can monitor your connection pool(s) via JMX. From the documentation:

Configuring and Managing c3p0 via JMX

If JMX libraries and a JMX MBeanServer are available in your environment (they are include in JDK 1.5 and above), you can inspect and configure your c3p0 datasources via a JMX administration tool (such as jconsole, bundled with jdk 1.5). You will find that c3p0 registers MBeans under com.mchange.v2.c3p0, one with statistics about the library as a whole (called C3P0Registry), and an MBean for each PooledDataSource you deploy. You can view and modify your DataSource's configuration properties, track the activity of Connection, Statement, and Thread pools, and reset pools and DataSources via the PooledDataSource MBean. (You may wish to view the API docs of PooledDataSource for documentation of the available operations.)

By the way, there seem to be JMX plugins for Nagios, you're not forced to use a JSP.

Solution 2:

You can monitor with Icinga/Nagios like this.

1. Download JMXQuery from google code. You will need to check out revision 18 like so.

   svn checkout -r 18 http://jmxquery.googlecode.com/svn/trunk/ jmxquery-read-only

2. Download this patch. wildcard patch for c3p0

3. use this command to patch the source code: (make sure you are in the jmxquery-read-only/src/main directory)

   patch -p0 -i wildcard_patch.diff

4. now download Apache Maven and extract it using this command

   tar -zxvf apache-maven-*-bin.tar.gz

5. now cd into the jmxquery-read-only folder and run the following command (assuming the apache maven and the jmxquery are in the same folder)

   ../apache-maven-*/bin/mvn compile

6. then run the following command:

   ../apache-maven-3.0.3/bin/mvn package

7. now you should have produced a jmxquery.jar file that you can use to query the c3p0 connection pool like so: (the check_jmx file can be obtained from just downloading the jmxquery code from the google code site like normal. using this link)

   check_jmx -U service:jmx:rmi:///jndi/rmi://localhost:1090/jmxrmi -O com.mchange.v2.c3p0:type=PooledDataSource* -N 1 -A numBusyConnections -w 50 -c 100