Comparison of JDBC Connection pools

Question

Does anyone have any information comparing performance characteristics of different ConnectionPool implementations?

Background: I have an application that runs db updates in background threads to a mysql instance on the same box. Using the Datasource com.mchange.v2.c3p0.ComboPooledDataSource would give us occasional SocketExceptions: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method)

Increasing the mysql connection timeout increased the frequency of these errors.

These errors have disappeared on switching to a different connection pool (com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource); however the performance may be worse and the memory profile is noticeably so (we get fewer, and much larger, GC's than the c3p0 pool).

Answer 1

I had this error pop up with mysql & c3p0 as well - I tried various things and eventually made it go away. I can't remember, but what might have solved it was the autoReconnect flag a la

url="jdbc:mysql://localhost:3306/database?autoReconnect=true"

Answer 2

Have you tried Apache DBCP? I don't know about c3po but DBCP can handle idle connections in different ways:

• It can remove idle connections from the pool
• It can run a query on idle connections after a certain period of inactivity

It can also test if a connection is valid just before giving it to the application, by running a query on it; if it gets an exception, it discards that connection and tries with another one (or creates a new one if it can). Way more robust.

Answer 3

Whatever connection pool you use, you need to assume that the connection could be randomly closed at any moment and make your application deal with it.

In the case with a long-standing DB connection on a "trusted" network, what often happens is that the OS applies a time limit to how long connections can be open, or periodically runs some "connection cleanup" code. But the cause doesn't matter too much -- it's just part of networking life that you should assume the connection can be "pulled from under your feet", and deal with this scenario accordingly.

So given that, I really can't see the point of a connection pool framework that doesn't allow you to handle this case programmatically.

(Incidentally, this is another of my cases where I'm glad I just write my own connection pool code; no black boxes mysteriously eating memory, and no having to fish around to find the "magic parameter"...)

Answer 4

You may want to have a look at some benchmark numbers up at http://jolbox.com - the site hosting BoneCP, a connection pool that is faster than both C3P0 and DBCP.

Answer 5

Broken pipe

That roughly means that the other side has aborted/timedout/closed the connection. Aren't you keeping connections that long open? Ensure that your code is properly closing all JDBC resources (Connection, Statement and ResultSet) in the finally block.

Increasing the mysql connection timeout increased the frequency of these errors.

Take care that this timeout doesn't exceed the DB's own timeout setting.