TomcatExpert

Explaining jdbc-pool, a High Concurrency Alternative to Connection Pooling Module

posted by fhanik on March 12, 2010 02:23 PM

Why another connection pool project

In this article we are going to explore one of the modules that is being developed in the Tomcat development trunk. The module named, jdbc-pool, is exactly that, an implementation of pooling of JDBC resources.

Connection pools have been around for a long time. I wrote one myself in 1997 when I first was introduced to Java, and used that for a long time in different projects I worked on. When JDBC came up, developers quickly realized that pooling JDBC connections would be beneficial for performance, and also a way to siphon connections and control the resource usage on the database server. This was especially important in horizontally scaled clusters where the database was always a risk of being overloaded. In those days we typically ran 1, 2 and sometimes 4 CPU machines for the application servers.

Connection pooling quickly became something that J2EE vendors developed in-house to integrate with their transaction services and became proprietary and integrated into the server itself. Outside of the J2EE containers a number of open source projects popped up. A few examples are Proxool, Apache Commons DBCP, C3P0 and Poolman. These are the most common ones we encounter in the field. Apache Tomcat has for very many years relied on DBCP to provide Tomcat users with a connection pooling implementation.

All of these open source projects have their pros and cons, but they all have something in common. They have become fairly stagnant and the code bases have not continued to evolve to meet new features in the Java language and are not optimized for multicore hardware that is increasingly becoming the common platform to deploy Java applications on. Some of the projects have also suffered from unneeded complexity in the source code, most likely one of the main reasons the project code is not moving forward, making it hard to maintain and easy to break.

Out of the four open source projects mentioned above, the two we see in Apache Tomcat deployments today are Commons DBCP and C3P0. A short summary of these two projects would be:

Commons DBCP

  • Ships with Apache Tomcat, used because it is readily available
  • Fairly stable code base
  • Plagued by dead locks for a very long time
  • Customer on lower clock speed but higher parallelism often complain about throughput bottlenecks

C3P0

  • Often used instead of DBCP
  • Based on our experience in the field, still fairly buggy
  • Reputation to perform better than DBCP, albeit our results show different

Both of these implementations are fairly complex. DBCP is built on top of the Apache Commons Pool project, and all together forms a code base of over 60 source files. C3P0 has grown to over 200 source files. Yet both pools fall short on features, to mention a few:

  • Pools are often statically tied to a Java version, meaning if the pool was written for Java 5, it wont have the java.sql and javax.sql interfaces compatible with Java 6. A user would typically receive a NoSuchMethodException when trying to execute one of the newer interface methods.
  • The pools are limited to java.sql.Driver interface, making it impossible to pool resources from a javax.sql.DataSource
  • No support for XA data sources
  • No flexibility for plugging in new features

So back to our question, why yet another pool project? The answer was simple, we needed better performance and more features. We also believed that taking on one of the legacy implementations would require rewriting a majority of the code base. So, it came to make a judgement call, do we want to rewrite 60+ sources files, or even 200+.

I know what you're probably thinking, here is yet another engineer that believes in rewriting from scratch rather than contributing to the existing code base. In almost every case, I prefer to reuse and fix than rewrite. However, after carefully judging the following questions:

  • How many lines of code do we actually need to provide the exact same implementation of Commons DBCP?
  • Can we make my implementation flexible enough to expose all the methods of java.sql.Connection that the driver exposes?
  • Can we provide a framework for exposing additional features?
  • Can we make the implementation scale in multi core environments/?

The next challenge we faced with such a project, within the Apache Software Foundation, was how to build a community around the project. Community is often grown by having lots of code that is not quite finished, meaning, there is work to be done in multiple areas making it enjoyable for a group to work together.

By this time, we had a prototype, it was 8 source files, and provided all the features of the other pools combined. Not all that exciting for a group of Java developers. Trying to fire off a separate project would probably not be very fruitful from a community perspective. We've since then grown it to 24 source files mostly by creating prebuilt addons that can be configured.

And so the jdbc-pool module was started.

The jdbc-pool did get some early adoption and the number of bugs was very low. Most early bugs were actual workarounds for typical use of the java.sql interfaces, calling close multiple times on a connection etc.

Today we see jdbc-pool, even in its unreleased state, several cases of production use. SpringSource ships the pool module with its tc Server product and you can already read about it in blog posts.

The jdbc-pool has an almost identical configuration as the Commons DBCP pool, in order to provide an easy migration path. For users already using Apache Tomcat and DBCP, the only difference would be the factory attribute  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory".

For users of dependency injection frameworks, like the Spring Framework it's easy to just configure the org.apache.tomcat.jdbc.pool.DataSource bean.

Folks, that is why we have yet another pool module for JDBC connections. Over the next few articles, I will be digging deeper into:

  • Performance of jdbc-pool
  • The feature set, how and when to use it
  • The actual implementation and why it scales
  • How to build extensions

Filip Hanik is a Senior Software Engineer for the SpringSource Division of VMware, Inc. (NYSE: VMW) and a key participant in the company's Apache Tomcat initiatives. Filip brings 15 years of extensive experience in architecture, design and development of distributed application frameworks and containers and is recognized for his top-quality system development skills and continuous participation of Open Source development projects. Filip is an Apache Software Foundation member and a committer to the Apache Tomcat project where he is a leading authority on Tomcat clustering and a key contributor to the core of the platform. Filip has made contributions to software initiatives for Walmart.com, Sony Music, France Telecom and has held a variety of senior software engineering positions with technology companies in both the United States and Sweden. He received his education at Chalmers University of Technology in Gothenburg, Sweden where he majored in Computer Science and Computer Engineering.

Comments

Switching from C3P0...

Hi Filip.


I'm using C3P0 in a product I work on. I occasionally have problems with deadlocks, but in general it works pretty well. Do you think jdbc-pool is mature enough to start considering an eventual switch?



--Matt

RE: Switching from C3P0...

Hi Matt, absolutely. The jdbc-pool has faired very well in many environments that we've seen. So go for the switch.
What we see as common pitfalls, is that some code doesn't have proper closure of statements, for that they would use the StatementFinalizer, or applications don't reset auto commit and read only values, and for that they would use the ConnectionState interceptor.
We have an upcoming article about configuration, that will talk more in depth about these issues.

If you run into any problems, you can ping me on my apache.org email, my id is fhanik, or ask a question on the tomcat user mailing list.

best
Filip

RE: Switching from C3P0...

Thanks Filip. I'm working on a product that's about to have a release. Once that's out, I'll give jdbc-pool a try. Maybe I'll be able to include it in the next release. :)

--Matt

Sorry for the slow reply - for some reason my dev build of Chrome is consistently unhappy with the reply page. It took me a few days to get around to writing it in Firefox. ;)

Is this aimed at Tomcat 6, 7,

Is this aimed at Tomcat 6, 7, or both? I just got bitten by Commons DBCP, so I'm interested. :)

Tomcat 6 or 7

It's an independent module. If the tomcat committers feel it viable, it would be the default in Tomcat 7, but that still has to be decided upon. It works well with Tomcat 6, that is the test platform I've been using for it. Feel free to try it out. Download from: http://people.apache.org/~fhanik/jdbc-pool/ Documentation: http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html best Filip

How to deal with password encryption

Dear Filip: How could I provide a plugin to read an encrypted password from context.xml instead of plain text. I have done this with DBCP by subclassing the Factory, is the same approach with this component? I know that this is no solid protection, but a customer of mine asked for it, and it was good to be able to respond to the requirement. Is it possible to do this with this fine component? Thank you, Martin.

How to deal with password encryption

I dont know if this is the best but this works for me. Replace RefAddr object before calling DataSourceFactory.getObjectInstance()
public class MyDataSourceFactory extends org.apache.tomcat.jdbc.pool.DataSourceFactory {

private Reference updatePassword(Reference in)
{
int inSize = in.size();
for (int i = 0; i < inSize; i++) {
RefAddr ra = in.get(i);
if(PROP_PASSWORD.equals(ra.getType()))
{
StringRefAddr ranew = new StringRefAddr(PROP_PASSWORD, decrypt_password_here(ra.getContent()));
in.remove(i);
in.add(i, ranew);
break;
}
}

return in;
}
@Override
public Object getObjectInstance(Object obj, Name name, Context nameCtx, Hashtable environment) throws Exception {

if ((obj == null) || !(obj instanceof Reference)) {
return null;
}
Reference ref = (Reference) obj;
updatePassword(ref);

return super.getObjectInstance(obj, name, nameCtx, environment);
}
}

vaporware

I spent past 2 hrs looking for this thing....where the hell do i download the jar from? It doesn't seem to be in maven, there is no tomcat-jdbc.jar in tomcat 702/lib, all I see is reference to svn....

Appserver or servlet containers are not the only places people can use a connection pool, you know...so the least you could do in this blog is to mention the download url for the component that you are so feverishly sell'n.

classis vaporware

found it here:

http://people.apache.org/~fhanik/jdbc-pool/

OK, ...first there was log4j,

OK, ...first there was log4j, then java logger, then slf4j, now this juli!!!

I am trying to create a pool in a standa alone java program and i am getting java.lang.NoClassDefFoundError: org/apache/juli/logging/LogFactory
What the hell is Juli...not my gf for sure.

Come on folks, Sun didn't pick log4j as the standard for logging. get over it and start using Java logging api instead of torturing with numerous thrid party logging apis.

ok so tomcat-juli 5.5.23

ok so tomcat-juli 5.5.23 doesnt have org/apache/juli/logging/LogFactory class.

I give up on this crapware.

the JDBC Driver has been forcibly unregistered.

Hello Filip,

I am not sure if You are still running this site, but anyway I would like to make a question.

I am trying to deploy an application in Tomcat 6.0 and I got this error, which causes an exception and the application is not deployed.

" SEVERE: A web application registered the JBDC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
Aug 29, 2011 6:18:31 PM org.apache.catalina.loader.WebappClassLoader clearReferencesThreads
SEVERE: A web application appears to have started a thread named [Timer-0] but has failed to stop it. This is very likely to create a memory leak.
"

Do you have any recommendation to solve this ?

I did thinhs like removing work anf temp folders, server restarted,etc.

I appreciate Your help.

Thanks

JL

Resource lifecycle

Do you configure the pool as part of the context? If so, ensure that you call stop on the pool when you close the context. You can do this in your servlet context listener. If you use tomcat 7, there should be a stop-method attribute on the resource itself.

how to get OracleCallableStatement

Hi Filip,

I was trying to use tomcat jdbc conn pool. Earlier I was using commons-dbcp.
My old code for getting conn from pool:
Connection dconn = ((DelegatingConnection)conn).getInnermostDelegate();

cstmt = (OracleCallableStatement) dconn.prepareCall(sqlQuery.trim());

here I was able to cast statment object to OracleCallableStatement.

Since I have moved on to use tomcat jdbc pool. I can't use DelegatingConnection anymore.

So I tried ..
Connection dconn = ((javax.sql.PooledConnection)conn).getConnection();

cstmt = (OracleCallableStatement) dconn.prepareCall(sqlQuery.trim());

This is throwing exception as:
oracle.jdbc.driver.OracleCallableStatementWrapper cannot be cast to oracle.jdbc.OracleCallableStatement

Do you know any solution to this problem? Please help.

oracle.jdbc.driver.OracleCallableStatementWrapper cannot be cast to oracle.jdbc.OracleCallableStatement

how can we getting information about connections status

Hi,
is any mechinanisim to getting information about connections status rather than using jmx.

Thanks
Mohan Challagonda

migrating from dbcp to jdbc-pool

Hi we are planning to mirgrate dbcp to jdbc-pool.in dbcp problem with stale cononnections.we looking a feature programatically get the information about connections status and delete or close the all connections ,what ever the connection status.

Thanks
Mohan Challagonda

Potential Memory Leaks using jdbc-pool with mysql jdbc driver

Hi,

Mysql jdbc driver connector/J retains references to open statements and result sets of a connection. So when jdbc-pool returns the connection to the pool, these resources are not GCd. They could be Live the entire length of the application, potentially leading to OutOfMemoryExceptions, if the open ResultSets are numerous or large.

Is there a way to configure jdbc-pool to avoid such scenarios, other than being paranoid about closing both Statements and ResultSets?

-Thanks
Austin

Note: I have resolved by using using a StatementFinalizerInterpretor in my program. Thanks Austin

Post new comment

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