One of the project on which I am working is extremely dependent on the database performance. This project executes an extremely high number of small SQL queries. Each query usually takes less than one millisecond. If the average query time goes from 1 [ms] to 1.5 [ms], after a complex calculation, you can see that we degrade performance by 50%.

Performance from the database side are still considered as good. We need a way to measure those performance in a very precise way to see if performance degradation actually come from the database or if we need to look for problem somewhere else. And we need a way to graph those data to identify problems quickly.

We already use Graphite and Statsd, and we already use Tomcat with its tomcat-jdbc pool. The tomcat-jdbc pool provides an interceptor mechanism that can be used in our case.

The implementation of our interceptor is minimal. First step, let’s get the timings of all operations on the connections. For that, we just need to override the invoke() method :

@Override
public final Object invoke(final Object proxy, final Method method,
        final Object[] args) throws Throwable {
    String methodName = method.getName();
    long start = System.nanoTime();

    Object result = super.invoke(proxy, method, args);

    increment(methodName + ".count");
    timing(methodName + ".timing", System.nanoTime() - start);
    return result;
}

The increment() and timing() methods are taken almost directly from the Statsd Java example. The actual implementation is slightly more complex than what is shown here to ensure that we also proxy the statements created from the connection and get their performances as well.

We need to give the interceptor some information, the name and port of our Statsd server, the sampling rate to make sure we dont hurt performances to badly and a prefix to organize our data.

Activating the interceptor is done by modifying your Tocmat datasource :

<Resource name="jdbc/TestDB"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          jdbcInterceptors="ch.ledcom.tomcat.interceptors.StatsdInterceptor(hostname=localhost,port=8125,sampleRate=0.1,prefix=myapplication.jdbc)"
          username="myusername"
          password="password"
          [...]
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/mysql"/>

Conclusion: JDBC interceptors are a great and easy way to get interesting information from your datasource !

The full code can be found on GitHub, binaries can be downloaded from Maven repo1 repository.