-
Notifications
You must be signed in to change notification settings - Fork 2.9k
Pool Analysis
Performance is great, but it cannot come at the cost of reliability. This page contains a critical analysis of popular JDBC connection pools.
Let us agree here today to adopt among ourselves a simple and unwritten rule.
We will not rise to criticize someone else's idea unless we are prepared to
offer an alternative idea of our own.
- Marco Rubio
| Pool | Files | Comments | Code |
|---|---|---|---|
| Vibur | 34 | 1289 | 1927 |
| HikariCP | 21 | 903 | 2228 |
| Tomcat | 31 | 3568 | 6345 |
| BoneCP | 49 | 4716 | 7293 |
| C3P0 | 120 | 7403 | 17646 |
- Tests connections at the point of
getConnection() - Encapsulates internal pool queries (test query and initSQL query) in their own transaction
- Tracks and closes abandoned Statements at
Connection.close()time - Executes a
rollback()on Connections returned to the pool whenautocommit=false - Clears SQL warnings before returning a Connection to a client
- Resets default auto-commit and transaction isolation levels
- Traps and examines
SQLExceptionobjects for disconnection error
The biggest single issue with BoneCP is the inability to configure the pool to test connections at the time of getConnection(). Every other pool here can be configured in that way. This is a sacrifice of reliability for speed.
A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. BoneCP does not do this.
When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise the query becomes part of the user's transaction. The same is true for the "initSQL" that can be run on a Connection when it is created. BoneCP does not encapsulate the Connection test or the initSQL in its own transaction.
By default does not:
- close abandoned open statements
Tomcat does not provide the option to reset connection auto-commit and transaction isolation levels for Connections in the pool.
A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. Tomcat does not do this.
When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise the query becomes part of the user's transaction. The same is true for the "initSQL" that can be run on a Connection when it is created. Tomcat does not encapsulate the Connection test or the initSQL in its own transaction.
The JDBC contract specifies that when a Connection is closed, any open Statements that have not been closed yet should automatically be closed, otherwise a resource leak on the database server-side could occur. Tomcat does not track Statements by default, but can be configured with a StatementFinalizer interceptor that purports to serve this function. Unfortunately, the StatementFinalizer tracks Statements using a list of WeakReference objects, and therefore when the JVM comes under GC-pressure (garbage collection) abandoned Statements maybe garbage collected before Tomcat has a chance to close them. This can result in a subtle memory leak that only occurs under GC-pressure and therefore may be very difficult to track down.
By default does not:
- test connections at
getConnection()time - execute
rollback()on connections returned to the pool - close abandoned open statements
By default does not:
- test connections at
getConnection()time - reset connection auto-commit and transaction isolation levels
While not well known currently, Vibur is an interesting attempt at a high-performance pool. But here is where we think Vibur gets it wrong:
The JDBC contract specifies that when a Connection is closed, any open Statements that have not been closed yet should automatically be closed, otherwise a resource leak on the database server-side could occur. Only in the case of cached PreparedStatements or CallableStatement does Vibur perform such resource cleanup. Even when caching is enabled ordinary Statement objects are not tracked for cleanup. When caching is disabled no Statements are tracked at all.
When the connection is configured for autocommit=false a connection pool should perform a rollback() operation either when the Connection is returned to the pool or before it is taken from the pool. Unless this is performed, transactions can "bleed" over from one use of the Connection to the next. Vibur does not do this.
When the connection is configured for autocommit=false a connection pool should perform the test query or isValid() test within its own transaction, otherwise the query becomes part of the user's transaction. Vibur does not encapsulate the Connection test in its own transaction.
A connection pool should clear SQL warnings via Connection.clearWarnings() either when the Connection is returned to the pool or before it is taken from the pool. Vibur does not do this.
Vibur, like HikariCP and BoneCP, can detect when a SQLException indicates that a Connection has been severed. This is a good thing, but there are two issues. Vibur's list of detected SQL error codes is more limited than either HikariCP or BoneCP. Surprisingly, when Vibur detects that an exception has occurred on one connection, it ejects all connections from the pool, not just the connection that encountered the exception. Maybe this is "pro-active" but it is also likely to create a stampede of new connections to the database. The jury is still out on whether this is a good idea or not, we tend to lean toward not.
By default does not reset connection auto-commit and transaction isolation levels.