Custom MBean configuration for Connection Pool monitoring

We ran into some interesting problems in configuring Hyperic to monitor JDBC connection pool via JMX. I would like to share the details, hoping it would save time for others.

When the connection pool and data sources are configured in the container using JNDI, Hyperic is able to identify pool MBeans automatically. However, if the pool configuration is within the application (war) and not in the container, MBeans will not be visible in Hyperic. To make them available in Hyperic one will need to configure JMX plugin for Hyperic.

Depending on the connection pool, you might need to register the MBeans explicitly. Some connection pools (c3p0) have auto-registered MBeans, so if you use them, the MBean will be auto-registered and become available to JMX tools like JConsole. However, these MBeans have dynamic object name that changes on server restart. This would be a problem in cases where you need a static MBean name to be able to identify it for setting up monitors and alerts. Hyperic does have a patch to allow monitoring MBeans with dynamic name http://communities.vmware.com/thread/389027, but this patch needs to be installed on the enterprise tcServer installation. 

If you do not wish to use the patch, you will need to register connection pool MBean explicitly with a static name.

Registering (export) the MBean with JMX server can be tricky and can lead to sever connection leak problem. One needs to be careful in selecting the MBean that should be exposed. Typically, the datasource beans itself has all the required connection attributes that one needs for monitoring like number of connection Idle/Used. But this datasource bean also has additional methods like getConnection*, that actually borrows a connection from the pool. 

If this data source is directly registered, and then monitored by JConsole (or likes), it may cause these getConnection* methods to be invoked and thus lead to connections being  borrowed from the pool without ever being released. Tools like JConsole are configured to display all the exposed attributes for the registered MBean in its attribute tab by calling the assessors  to get the attribute value. If the bean had assessors like getConnection*, Jconsole will invoke them, causing connections to be borrowed.

To avoid this connection leakage problem and have a static MBean name that can be used for monitoring and alerts, create a custom MBean. Configure this custom bean to have just the attributes that are needed for monitoring and alerts (caution: do not extend the data source bean).

Here is the JMX configuration for a tomcat - pool that is working well for us through JConsole and Hyperic.

       <beans:bean id="exporterOracle"   class="org.springframework.jmx.export.MBeanExporter" lazy-init="false">

              <beans:property name="beans">

                     <beans:map>

                           <beans:entry key="bean:name=OracleConnectionMBean" value="#{dataSource.getPool().getJmxPool()}"/>        

                     </beans:map>

              </beans:property>

       </beans:bean>

<beans:bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource "

              destroy-method="close">

              <beans:property name="driverClassName"   value="${oracle.db.driver}" />

              <beans:property name="url" value="${oracle.db.url}" />

              <beans:property name="username" value="${oracle.db.user}" />

              <beans:property name="password" value="${oracle.db.password}" />

              <beans:property name="jmxEnabled" value="true" />

              <beans:property name="initialSize" value="${oracle.pool.initialsize}" />

              <beans:property name="maxActive" value="${oracle.pool.maxactive}" />      

              <beans:property name="maxIdle" value="${oracle.pool.maxIdle}" /> <!-- maxIdle needs to be same as maxActive -->

              <beans:property name="testOnBorrow" value="true"></beans:property> <!-- Needs to be set to prevent Connection timeout exception -->         

              <beans:property name="validationQuery" value="select 1 from dual" /> <!-- This needs to be set if testofBorrow is set -->

       </beans:bean>

Check that the MBean name is static "bean:name=OracleConnectionMBean" and bean that is exposed is specific JMXPool interface of the datasource (dataSource.getPool().getJmxPool()), not the datasource itself.