Choosing a database connection pool

Posted by Corina Stratan on September 8, 2016

Some time ago we were looking for a database connection pool library to use at TOPdesk (here is a good introduction to connection pools if this term is new to you). There are many open-source connection pool libraries available, so we did not lack the choice. But with so many options it becomes difficult to choose most suitable one, which is why we decided to do a bit of research. Here are some of the things that we learned.

Our criteria

The first step was to decide which are the most important features we needed. Based on previous experience with our older connection pool (which was developed in-house) and on reading newer documentation, we settled on:

Reliability

Performance is always high on our wishlist, but one thing we find more important is reliability. We therefore planned to check the number of open bugs in the libraries that we were considering. Especially undesirable are deadlocks, a problem that some connection pools have when misconfigured. It actually happens quite often that connection pools are incorrectly or not optimally configured, and one of the causes is that some of them have unexpected default settings. We were therefore looking for a connection pool that is straightforward to configure and has reasonable default settings.

Performance

One thing to keep in mind is that the results of a performance test for connection pools are significantly influenced by how the pools are configured; and of course, the type of load used in a test might not be similar with the load that a real application will have in production. So, although we took into account the results of some performance tests that we found online (for example, this performance test) we also wanted to make sure that the connection pool that we choose will have a good performance in our own environment with our own configuration.

Functionality

For our application we needed a few specific features from the connection pool, such as support for Hibernate 4+ and being able to configure connection properties like the default type of transactions and the default isolation level. We also wanted to be able to configure when and how the connection health checks are done, in a way that is appropriate for the infrastructure and database server being used (our application supports both Oracle and Microsoft SQL Server).

Documentation & user community

Working with well documented libraries is easier, so we planned to check this aspect as well. We also prefer to use libraries with a larger user community, since they are usually better maintained.

Comparing the connection pools

We checked the following connection pools: C3P0, Apache Commons DBCP, BoneCP, Tomcat, Vibur and Hikari. There are performance tests available online that compare these pools, but we were also looking for functional comparisons — and did not find much of this. One exception is Hikari’s web site, which has a great overview of connection pools. This analysis has lists of known issues for each connection pool and was very useful for us as a starting point.

We found that some of the pools did not meet our criteria:

C3P0 is one of the oldest and best known libraries, but according to various reports its configuration is too complicated. When misconfigured, C3P0 can have performance issues or even deadlocks (here are for instance one report and some suggestions for configuration). It also had quite a lot of open bugs and a codebase that we thought was too large.

Apache Commons DBCP seemed to provide most of the features we needed and has good documentation. On the other hand, the user community is rather small and the newer Tomcat JDBC Connection pool (based on the code from Apache Commons DBCP) is now used more widely.

BoneCP and Vibur looked like good choices from the point of view of features, stability and performance. The user community however was small for both of them. In the case of BoneCP, the author himself announced that he considers his library deprecated and that Hikari is a better choice.

We were left with two connection pools that looked promising and that we wanted to try out: Hikari and Tomcat.

Hikari vs. Tomcat

We were very interested in Hikari, as it performed better than other pools in several tests  (http://www.trustiv.co.uk/2014/06/battle-connection-pools and http://blog.wix.engineering/2015/04/28/how-does-hikaricp-compare-to-other-connection-pools/). It is designed to be deadlock-free and aims to provide sensible defaults for most of its configuration parameters, which is not the case for most of the other connection pools. It would have been our first choice, but when trying it out we ran into a problem: due to a bug Hikari had at that time, we could not specify a JDBC driver class name programmatically. Since this was something that we needed, we had to give it up and went further with testing Tomcat.

In the Tomcat connection pool we found all the functionalities we needed, and had a reasonably good experience trying to configure and use it. It does have some flaws (among others, settings that are not clearly documented or have undesirable default values), but we could work around them. In a future post we’ll share some tips that we found for configuring the Tomcat connection pool. Overall we are happy with Tomcat, which has been running and performing well in our production environments for more than a year.