TomcatExpert

Using the Tomcat 7 JDBC Connection Pool in Production

posted by dmikusa on January 23, 2012 03:14 PM

One of the new features with Tomcat 7 is a replacement to the commons-dbcp connection pool. While the commons-dbcp connection pool works fine for small or low traffic applications, it is known to have problems in highly concurrent environments (think multi-core/multi-cpu).

Fortunately, this is where the JDBC Connection Pool excels. It is a completely new connection pool which has been written from the ground up, with a focus on highly concurrent environments and performance.

Given its focus on high concurrency and performance, many users are finding that the JDBC Connection Pool can be great for use in a production environment. This article will discuss the features and options which make using the JDBC Connection Pool a great choice.

The Basics

Getting started with the JDBC Connection Pool is very simple. It was purposefully designed as a drop-in replacement for commons-dbcp and as such, if you've ever used commons-dbcp you'll be immediately familiar with the configuration for the JDBC Connection Pool.

This means the most existing commons-dbcp users can switch to the JDBC Connection Pool by simply adding the following property to their configuration factory=”org.apache.tomcat.jdbc.pool.DataSourceFactory”.

What about the other commons-dbcp configuration options? You don't need to change them at all. Virtually all of the commons-dbcp configuration options are supported by the JDBC Connection Pool. Including but not limited to: testWhileIdle, testOnBorrow, testOnReturn, maxActive, minIdle, maxWait, initialSize, removeAbandoned, removeAbandonedTimeout and logAbandoned.

Connection Validation

How many users have seen this scenario before? You deploy a new application to production and it runs great all day, however the next morning you find that the application is dead. Upon examining the logs, you find that they are full of connection errors. What happened?

A common scenario at many companies is for a database to be briefly shutdown at night for maintenance (or backup). Unfortunately, when this occurs all of the connections in your application's connection pool are lost. Furthermore, because the connection was broken by the server, the connection pool does not know that the connection has been lost. As a result, the connection pool still thinks that the connection is valid, and will continue to distribute the connection to your application. Not until your application actually attempts to use the connection will it find out that the connection is bad.

Fortunately, there is an easy way to resolve this issue. You need the connection pool test each connection before it lends the connection to your application. With the JDBC Connection Pool, just like with commons-dbcp, this feature can be enabled by setting testOnBorrow to true and validationQuery to a simple test query like SELECT 1 or SELECT 1 FROM DUAL. Alternatively, the validatorClassName property can be used to indicate the full name of the class that will be used to validate connections. The class specified by validatorClassName will need to implement org.apache.tomcat.jdbc.pool.Validator, and can be used to implement any custom validation logic.

Unlike commons-dbcp, the JDBC Connection Pool provides an additional parameter called validationInterval. This parameter allows you to control how frequently a given validation query is executed. By default it is set to 30 seconds; therefore, the given validation query will only run once per connection every 30 seconds.

To illustrate the power of this feature, let's take a look at an example. You have a connection pool configured to validate connections and that pool hands out 10 connections per second to your application. Given this, the pool will execute 10 validation queries per second for a total of 300 queries over a 30 second period. However, if the validation interval is enabled and set to 30 seconds, that means only 10 queries will be run for the same 30 second time period, offering a reduction of 290 queries. Despite the fact that a typical validation query executes quickly, it is still easy to see how this can be a big savings for your application.

When deciding the value to use for the validationInterval property, the following rule can be helpful. Larger values offer better performance, while smaller values decrease the chance of a stale connection being presented to your application.

For example, a value of 60 seconds means that a validation query only needs to run once every 60 seconds for each connection. However, that also means that once a connection is validated it will not be revalidated for another 60 seconds. If a database connection were to be lost one second after it was validated then there would be a period of 59 seconds where the connection would still be considered valid by the connection pool. If a connection were to be borrowed while in this state, use of the connection would fail and the application would get a connection error.

As you can see from the example, picking a value for the validationInterval property is a trade-off and ultimately depends on what is acceptable for your application.

JDBC Interceptors

Another feature specific to the JDBC Connection Pool is the concept of a JDBC Interceptor. From the documentation, “An interceptor is a powerful way to enable, disable or modify functionality on a specific connection or its sub components”. At first glance this definition may seem a bit vague, but what this essentially means is that functionality can be added or removed from the connection pool with only a simple configuration adjustment. Even better, the code does not have to live inside the JDBC Connection Pool JAR. You can create and add custom JDBC Interceptors in the same manner as you would add an interceptor that is bundled with the connection pool.

Enabling an interceptor is easy; add the jdbcInterceptors property to your connection pool configuration and specify a semi-colon separated list containing the names of the interceptors to be used. For example, jdbcInterceptors=”ConnectionState;StatementFinalizer”.

If the interceptor that you are adding resides in the package org.apache.tomcat.jdbc.pool.interceptor specify the short name, as in the previous example. However, if you are adding an interceptor that resides in a difference package, like a custom interceptor, you would just reference the full class name of your interceptor. For example, com.mycompany.interceptors.MyCustomInterceptor.

With that in mind, let's take a look at some of the interceptors that come with the JDBC Connection Pool.

ConnectionState Interceptor

The ConnectionState interceptor is a simple interceptor which caches the properties autoCommit, readOnly, transationIsolation and catalog.

Normally when one of these properties is accessed, the connection must talk to the server and retrieve the requested value. Since this action results in communication over the network, access to these properties can be slow.

The benefit of the ConnectionState interceptor is that the values will be cached locally, allowing faster access and limiting network chatter.

StatementFinalizer Interceptor

The StatementFinalizer interceptor watches for any statement that is created by a connection with the createStatement, prepareStatement or prepareCall methods. It monitors the statements; when the connection is eventually returned to the pool, the interceptor ensures that any statements that were created are properly closed.

This interceptor comes in handy when dealing with legacy or third party applications where statement handling is problematic, but it is not possible for you to fix the code itself.

SlowQueryReport and SlowQueryReportJmx Interceptors

The SlowQueryReport and SlowQueryReportJmx interceptors monitor the amount of time that a query takes to execute, and flag any query that exceed a predefined threshold. The SlowQueryReport interceptor will log any slow queries at the WARN level, while the SlowQueryReportJmx interceptor will produce a JMX notification in addition to the WARN log statement.

I hope that the benefits to these interceptors are obvious. If you have an application in which performance is slow, you can enable this interceptor and automatically get profiling information about the SQL queries being executed by your application.

ResetAbandonedTimer Interceptor

The ResetAbandonedTimer interceptor works in conjunction with the removeAbandoned property. When the removeAbandoned property is enabled, the connection pool will attempt to reclaim connections which have been checked out of the pool for a long period of time. The rationale here is that connections checked out for an extended period of time are typically connections which have not been properly closed by a deployed application. The removeAbandoned setting will thus allow you to reclaim connections from badly behaving applications.

Most of the time this works just fine, however the abandoned timer can be tricked by a long running process. Let's say that you have a batch process that takes 20 minutes to execute. What happens when it is still running, but the removeAbandoned timeout elapses? Unfortunately, the connection will still be reclaimed by the pool.

This is the benefit of the ResetAbandonedTimer interceptor. With it enabled, the abandoned timeout will be reset every time an SQL operation is executed on the connection. Thus enabling the removeAbandoned process to be more accurate, particularly for legitimate long running processes.

Summary

The JDBC Connection Pool is a fantastic addition to Tomcat 7. Not only does it provide an alternative connection pool, but one with superior performance and features. I would encourage any current users of commons-dbcp to give it a try and see how it can benefit their applications.

More information on the JDBC Connection Pool can be obtained from the Tomcat 7 documentation at the following link.

https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

Daniel Mikusa is a Software Support Engineer at Pivotal.  Daniel has been a user of Apache Tomcat for the past six years and is currently focused on providing enterprise-class support for Pivotal's Tomcat and tc Server customers. Daniel attended college at Kent State University and holds a bachelor's degree in Computer Science.

Comments

distribute trasactions

Thanks for write up.

I have a basic question on tomcat.

can tomcat 7 supports distribute transactions or we have to use dbcp connection pool mechansim for this

Learn java,tomcat,hadoop,linux with examples

Out-of-the-box Tomcat does

Out-of-the-box Tomcat does not have support for distributed transactions. If you require distributed transactions for your application then you'll need to use a third-party transaction manager like Bitronix or Atomikos.

Comparison to C3P0

How does Tomcat 7 JDBC Connection Pool compare with C3P0?

Regards,
Franklin

This article is a bit old,

This article is a bit old, but provides some comparison data. Additionally, the source code for the tests is available, so you can download and run the tests for yourself.

http://www.tomcatexpert.com/blog/2010/03/22/understanding-jdbc-pool-performance-improvements

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.