14

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?

leppie
  • 115,091
  • 17
  • 196
  • 297
Llistes Sugra
  • 991
  • 4
  • 9
  • 24

2 Answers2

24

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.

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • Really, but for my approach I need this value in a variable – Llistes Sugra Nov 07 '10 at 18:22
  • @LlistesSugra: Use whatever suits your needs. But in any case, JMX is the way to get the info. – Pascal Thivent Nov 07 '10 at 19:20
  • 1
    the standard check_jmx nagios plugin does not work with c3p0 because the PooledDataSource has a unique string appended to its name. thats why you need the jmxquery.jar I mentioned below (it supports wildcards) – d4v3y0rk Apr 08 '15 at 00:46
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

d4v3y0rk
  • 345
  • 1
  • 9
  • the wildcard patch for C3P0 seems to be lost, I see 0.9.5.1 has the provision to get metrics by excluding the identity token http://www.mchange.com/projects/c3p0/#jmx_configuration_and_management ` com.mchange.v2.c3p0.management.ExcludeIdentityToken=true ` – kisna Jan 05 '16 at 02:24
  • The original link hostname of the `wildcard patch for c3p0` (**jmxquery.googlecode.com**) seems to be changed or move to **code.google.com/p/jmxquery** and the last version source code (1.3) cannot be downloaded. – Jdamian Feb 21 '18 at 12:39