Connection Pooling Options With Jdbc: Dbcp VS C3P0

Connection pooling options with JDBC: DBCP vs C3P0

DBCP is out of date and not production grade. Some time back we conducted an in-house analysis of the two, creating a test fixture which generated load and concurrency against the two to assess their suitability under real life conditions.

DBCP consistently generated exceptions into our test application and struggled to reach levels of performance which C3P0 was more than capable of handling without any exceptions.

C3P0 also robustly handled DB disconnects and transparent reconnects on resume whereas DBCP never recovered connections if the link was taken out from beneath it. Worse still DBCP was returning Connection objects to the application for which the underlying transport had broken.

Since then we have used C3P0 in 4 major heavy-load consumer web apps and have never looked back.

UPDATE: It turns out that after many years of sitting on a shelf, the Apache Commons folk have taken DBCP out of dormancy and it is now, once again, an actively developed project. Thus my original post may be out of date.

That being said, I haven't yet experienced this new upgraded library's performance, nor heard of it being de-facto in any recent app framework, yet.

JDBC Connection pooling for SQL Server: DBCP vs C3P0 vs No Pooling

c3p0 is not deader than a doornail. It's old but (somewhat) actively maintained. Whether newer alternatives better suit your application is for you to decide.

What version of c3p0 are you using? If you think it is deader than a doornail, are you using an old version? You should be using 0.9.5.2.

The outcome of the test as you've defined it will be highly dependent on lots of things difficult to evaluate with the information you've provided. As Mark Rotteveel points out, you've not shown any information about your config. You've not said anything about the location of the SQL Server. You'll notice greater benefit from a Connection pool when the database is remote than when it is local, as some of the performance improvement comes from amortizing the network latency of Connection acquisition over multiple client uses. Your test executes a query and iterates through the result set. The longer the result set, the more you'll see overhead from the Connection pool (which must proxy the ResultSet) overtake the benefits of faster Connection acquisition. (The numbers you are getting look unusually bad, though. c3p0 typically has very fast ResultSet passthrough performance.) With a sufficiently long queries, the cost of Connection acquisition becomes negligible, if iterating through a ResultSet, the overhead of the pooling library increases, making a Connection pool not so useful.

But this is far from the typical use case for web or mobile clients, which usually make short queries, inserts, and updates. For short queries, inserts, and updates, the cost of a de novo Connection acquisition can be very large relative to the execution of the query. This is the use-case for which Connection pools offer a large improvement. That may not be what you are testing; it depends on how big MyTable is.

MysqlConnectionPoolDataSource or c3p0 like library?

A ConnectionPoolDataSource is not a connection pool (or at least: it shouldn't be), it is intended to be used by a DataSource that provides pooling (eg from an application server). A ConnectionPoolDataSource provides the physical connections that will be held in the connection pool. Besides creating those physical connections a ConnectionPoolDataSource shouldn't do anything else.

So if you are working in an application server, use the pooling provided by the DataSources of the application server. If you are in a standalone application or a server that doesn't provide datasources on its own, use third party connection pools like BoneCP, c3p0 or Apache DBCP. If MySQL also provides a normal DataSource that provides pooling, then you could use that.

c3p0 or dbcp or BoneCP can handle broken connections

1.) I don't know about dbcp but with regards to C3P0 this functionality does exist in the C3P0PooledConnection class (look in the invoke method, the exception is caught and handled, if you want more details on the exact handling I can add them). I also needed to know if it contained it to drop testOnCheckin/Checkout and I verified it contains this behaviour.

2.) Really hard to say, since on the one hand C3P0 is widely used in many production sites and the maintainer has resumed active development but on the other hand BoneCP seems to have some very interesting design principles (pool sharding for examples) and some flattering benchmarks. Since you usually have a pretty good indirection from your connection pool library (mostly the dependency is contained to a config file or two) I can suggest to start with one and once you have a production with actual data try to optimize it and compare it with another library (also optimized of course). It very well may be that either library you choose will be sufficient for your needs.

Why there are two options for database connection pooling with tomcat (tomcat-dbcp and tomcat-jdbc)?

In summary

Notes from a member of the Tomcat commit team (see here):

Tomcat JDBC is Tomcat’s "home grown" database connection pooling and does not use poolPreparedStatements. Tomcat DBCP is Tomcat’s package renamed fork of Apache Commons DBCP 2. Tomcat DBCP is used by default.

The Default DBCP 2 Tomcat Pool

This is the newer of the two pools included in Tomcat and it is the one used by default. It is based on the Commons DBCP 2 pool, as described here.

You can see more details by visiting the official DBCP site.

Tomcat's Home-Grown JDBC Pool

The main documentation page for this is here.

You may see this referred to as "new" in some places in the Tomcat documentation - for example here:

So why do we need a new connection pool?

It was new at one point in time. It was superseded by the DBCP2 pool.

Which One to Use?

That is somewhat a matter of opinion and may also depend on your specific circumstances. You can start with Tomcat's default DBCP 2 pool, if you cannot decide.

Just to add: You can use either of the above pools with Tomcat, or you can use other pools such as HikariCP, c3p0 and so on. You don't have to choose only between the two Tomcat-provided pools.

Java Database connection pool (BoneCP vs DBPool vs c3p0)

At work we have used BoneCP (as the replacement for c3p0) and as far as I know haven't had any issues (I did not do the upgrade myself). From what I have seen and read it seems like a well-designed solid library, and I would personally use it over alternatives: it appears to be one of those "just works" libraries that are nice to have around.

Nothing negative to say about DBPool, I am just not familiar enough with it; although looking at its site documentation certainly seems like a plus.

---

Related Topics