Projects:Connection Pooling Improvements
Contents |
Purpose
This project will improve the Openbravo connection pooling:
- It will merge the two database connection pools Openbravo currently maintains (one of them is used for xsql and the other one is used by DAL). This will improve the performance and will decrease the number of open connections.
- It will allow to change the database connection pool implementation modularily. If no connection pool is specified, the current pool implementations will be used.
- It will provide an implementation of an optimized database connection pool. Some functionality this implementation should include: min/max number of connections, queuing requests and asynchronous connection retrieval, getConnection timeout.
- The new implementation should behave differently depending on what it is being used for. In particular, when executing a query that fetches data to fill a grid, it should allow to specify a timeout, so that when this timeout is reached the connection is canceled.
Technical Notes
Query Timeout
The query timeout can be implemented using JDBC's setQueryTimeout(int seconds) method. For this method to actually work, both the JDBC driver and the DBMS used must support it.
It seems it is properly implemented for Oracle [1], [2]. For postgres it seems that in some versions it is not even implemented [3] and in others the implementation is buggy [4].
[1] http://troyjsd.blogspot.com.es/2012/11/oracle-jdbc-readtimeout-querytimeout.html
[2] https://forums.oracle.com/message/2048172#2048172
[3] https://groups.google.com/forum/#!topic/mybatis-user/TQkSYHL97A8
[4] http://www.postgresql.org/message-id/283FE4ADE0E66642A4301967EDD7BAEE338464@YUL01WMXB02.rp.corp
JDBC Connection Pools Alternatives
Apache DBCP
Features:
- Supports JDBC 3 and JDBC 4 (JDK 1.4-1.6)
Hibernate does no longer recommend it: https://forum.hibernate.org/viewtopic.php?f=1&t=947528&view=next
License: Apache License
C3PO
c3p0 is an easy-to-use library for augmenting traditional (DriverManager-based) JDBC drivers with JNDI-bindable DataSources, including DataSources that implement Connection and Statement Pooling, as described by the jdbc3 spec and jdbc2 std extension.
Features:
- Classes which adapt traditional DriverManager-based JDBC drivers to the newer javax.sql.DataSource scheme for acquiring database Connections.
- Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.
- Removes unused connections from the pool if they have been idle more than a certain amount of time
Requisites:
- JRE 1.6 or newer
License: Eclipse Public License, GNU Library or Lesser General Public License version 2.0 (LGPLv2)
Proxool
Features:
- Transparency: Transparently adds connection pooling to your existing JDBC driver.
- You can monitor the performance of your database connections and listen to connection events
- It's easy to configure using the JDBC API, XML, or Java property files
Inactive since 2008.
DBPool
Requisites:
- SLF4J
- JDBC 4
Features:
- Allows to configure:
- minpool: Minimum number of connections that should be held in the pool.
- maxpool: Maximum number of connections that may be held in the pool
- maxsize: Maximum number of connections that can be created for use.
- idleTimeout: The idle timeout for connections (seconds).
- Connection pool manager: The pool manager provides support for defining connection pools in a properties file. Multiple pool managers are supported, allowing you to define groups of pools from multiple sources.
- Connection validation
- Disabling statement caching
- Asynchronous connection destruction
- Pool listeners: Pools can issue events about their activity to objects which have registered interest. The following events can be monitored:
- INIT_COMPLETED: Fired when the init() method has completed creating new pool connections.
- CHECKOUT: Fired just before a valid connection is handed back from a checkOut(…) request.
- CHECKIN: Fired when a connection is handed back with a checkIn(…) call.
- MAX_POOL_LIMIT_REACHED: Fired when a check-out request causes the pooling limit (maxpool) to be reached.
- MAX_POOL_LIMIT_EXCEEDED: Fired when a check-out request causes the pooling limit (maxpool) to be exceeded.
- MAX_SIZE_LIMIT_REACHED: Fired when a check-out request causes the pool's maximum size limit (maxsize) to be reached.
- MAX_SIZE_LIMIT_ERROR: Fired when a check-out request is made but the pool's maximum size limit (maxsize) has been reached.
- VALIDATION_ERROR: Fired when a connection cannot be validated (when the isValid(…) method call fails).
- PARAMETERS_CHANGED: Fired when the pool's parameters have been changed.
- POOL_FLUSHED: Fired when the pool is flushed of free/unused connections.
- POOL_RELEASED: Fired when a pool has been released. No more events are fired from the same pool following this event, as all listeners are removed.
An example of the pool listener feature is included in the snaq.util.PoolTracer class.
License: DBPool is available under a BSD-style licence as described below. This licence permits redistribution of the binary or source code (or both) for commercial or non-commercial use, provided the licence conditions are followed to acknowledge origination and authorship of the library.
Apache Tomcat Pool
Features:
- The following parameters are configurable:
- initialSize: The initial number of connections that are created when the pool is started. Default value is 10
- minIdle: The minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail. Default value is derived from initialSize:10
- maxIdle: The maximum number of connections that should be kept in the pool at all times. Default value is maxActive:100 Idle connections are checked periodically (if enabled) and connections that been idle for longer than minEvictableIdleTimeMillis will be released.
- maxActive: The maximum number of active connections that can be allocated from this pool at the same time. The default value is 100
- minEvictableIdleTimeMillis: The minimum amount of time an object may sit idle in the pool before it is eligible for eviction. The default value is 60000 (60 seconds).
- removeAbandonedTimeout: Timeout in seconds before an abandoned(in use) connection can be removed. The default value is 60 (60 seconds). The value should be set to the longest running query your applications might have.
- Full List
- JDBC Interceptors
- Asynchronous Connection Retrieval
Configuring jdbc-pool for high concurrency
Active
BoneCP
Features:
- Callback (hook interceptor) mechanisms on a change of connection state.
- Partitioning capability to increase performance
- Automatic resizing of pool
- Statement caching support
- Idle connection timeouts / max connection age support
- Full List
- Configuration Options
Requirements:
- Google Guava library, available for free from here.
- The SLF4J logging library.
- JDK1.5 or higher.
License: Apache v2
Inactive since 2011
HikariCP
Supports Java 6, 7, and 8.
Features:
- Fastest connection pool available
- Simple configuration
- High reliability (never had a reported deadlock)
- Robust recovery from failures
Requirements:
- Java 6 and above
- Javassist 3.18.1+ library
- slf4j library