DBPool : Java Database Connection Pooling
Copyright © 2001-2010 Giles Winstanley
Updated: 12 May 2010
Version: 5.0
What is DBPool?
A Java-based database connection pooling utility, supporting time-based expiry, statement caching, connection validation, and easy configuration using a pool manager.
Why would I use it?
Applications using databases often need to obtain connections to the database frequently. For example, a popular website serving information from a database may need a connection for each client requesting a page using their browser. To ensure good application response time for each client, the application needs to be profiled to find the time spent performing each of its tasks. One of the most expensive database-related tasks is the initial creation of the connection. Once the connection has been made the transaction often takes place very quickly. A connection pool maintains a pool of opened connections so the application can simply grab one when it needs to, use it, and then hand it back, eliminating much of the long wait for the creation of connections.
Licence Agreement
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.
DBPool : Java Database Connection Pooling <http://www.snaq.net/> Copyright (c) 2001-2010 Giles Winstanley. All Rights Reserved. |
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
Where can I get it?
The JDBC specification has changed out of step with the various Java Platform releases. Many pooling libraries solve this by using dynamic method resolution which allows a single codebase to work for all platforms, but at the cost of performance. DBPool aims to maintain its high-performance approach, and as a result different downloads are available depending on your platform. It is highly recommended to use the most recent stable release possible, currently Java Platform 6, for reasons of performance, reliability, and features. The source code is available with or without generics support, so if you're using Java Platform 5 or 6 it's recommended to use the generics-compatible version.
Requirements: DBPool 5.0 requires Java 1.4.x or above, supporting JDBC 3.0 or later. Apache Ant is recommended to build the source code.
Dependencies: DBPool 5.0 makes use of the Apache Commons Logging library, and the library's JAR file should included in the CLASSPATH. The package download includes the most recently available version of this library.
Java Platform 6 (JDBC 4.0) |
|
---|---|
Java Platform 5 (JDBC 3.0) | |
Java 1.4.x (JDBC 3.0) |
|
You can find out which Java version you have by typing java -version
into a terminal session (aka "command prompt"), assuming your development platform is configured for terminal-based Java access. Perhaps confusingly for some, Java Platforms 5 & 6 report "1.5.x_xx" and "1.6.x_xx" respectively (showing internal build numbers, where x varies). After downloading the JAR file for your Java platform, you can find more information by typing: java -jar DBPool-5.0.jar
after navigating to the download directory, whereupon you will be shown some additional useful information.
You can download the latest version of DBPool from: http://www.snaq.net/
What about support?
Please read this documentation before sending a support email. I frequently receive support emails from people who simply haven't taken the time to read the documentation and find the relevant information. Unsurprisingly I usually just point these people back here. If you need to ask for help, please email this address, and include as much information as possible to help diagnose the problem, including log files (with debug option enabled), stack traces, and source code, and properties file where appropriate. I will endeavour to reply as soon as possible.
If you would like to be added to the DBPool mailing list list to receive notification of new versions when they are released, send an email to the support address with your email contact details, asking to be added to the list.
Table of Contents
- How do I use it?
- Using a ConnectionPool
- Using a DataSource
- Using the ConnectionPoolManager
- Defining the behaviour of the pool
- Logging configuration
- Good database programming practices
- Choosing pooling parameters
- Advanced features
- Notes & troubleshooting
- Release notes for DBPool 5.0
- Change log
How do I use it?
To use DBPool you need to have the JAR file in a location where it's available for use by the host system you are using. For standalone applications this usually means in the CLASSPATH, and for application servers there is usually a specific directory is recommended for JAR libraries (for example, when used with Apache Tomcat it can be placed in the <webapp>/WEB-INF/lib directory).
Usually DBPool is used in two different ways:
- Direct use of individual connection pools.
- Using the ConnectionPoolManager to manage one or more connection pools.
If you have never used DBPool before it's recommended that you start by simply integrating a single connection pool into your application/applet to see how it works and performs. This provides the simplest direct support for pooled database connections and will get you up and running quickly. Once you learn exactly how it behaves and the benefits it can give you can try the pool manager approach to manage multiple pools if necessary.
How pooling is controlled
A single pool maintains multiple open connections, where each connection connects to the same database source using the same authentication. The pool also manages how those connections are handed out to be used, and what happens to them when they are closed. Both the size of the pool and the number of connections available change based on user-specified properties, the most common of which are shown in the adjacent table.
Property | Explanation |
---|---|
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). |
When a pool is first created it contains no connections. If minpool > 0 then the pool automatically tries to create this number of new connections ready to be used. A total of maxsize connections can handed out for simultaneous use (unlimited if maxsize = 0). A maximum of maxpool connections are ever retained in the pool for reuse; returned connections above this limit are destroyed. Pooled connections that are not used for idleTimeout seconds are destroyed (if idleTimeout > 0).
A pool therefore has two general types of behaviour: expiring, and non-expiring. An expiring pool is one for which any connection that is idle/unused for a specified time (idle timeout) is "expired" (i.e. removed) from the pool. In both situations the pool can hand out up to maxsize connections, and pool up to maxpool connections. The difference is that a non-expiring pool will not expire unused connections, so will generally retain a larger number of connections for reuse as they only get removed from the pool if they become invalid. The two pool types also differ in how they initially become populated. Immediately after creation both types start out with no connections in the pool. Pooling of connections establishes automatically as items are checked in and out. Because of the additional checks that need to be done, an expiring pool can self-populate very quickly, but a non-expiring pool will populate gradually. If required it can be explicitly requested using the init()
method, which requests that minpool connections are created.
Picking appropriate values for the pooling properties is not always easy. Various factors may affect your decisions, not least of which could be licence restrictions, system resources, etc. Many databases will close any unused connections once a certain time has elapsed (e.g. MySQL). It obviously makes no sense to set idleTimeout above this level (or to zero), and may well be better to set it lower. You should take time to analyse the demand and performance of your application setup before finalising values, which can help to maximize the effectiveness of the pooling.
Using a ConnectionPool
Direct use of ConnectionPool objects can provide substantial performance gains with minimum changes to the previous non-pooling code. A single ConnectionPool provides a centralized location for access to connections to a single database with specific authentication credentials and parameters. For more information on the details of each parameter see the section: Defining the behaviour of the pool.
When creating/using a ConnectionPool instance it is assumed that access to the relevant JDBC drivers has already been established. To ensure this is done your JDBC driver(s) should be appropriately registered with java.sql.DriverManager before creation/use of any pools; for example:
Class c = Class.forName(<class name>);
Driver driver = (Driver)c.newInstance();
DriverManager.registerDriver(driver);
Once the appropriate JDBC drivers have been registered, a connection pool may be created:
ConnectionPool pool = new ConnectionPool(<poolname>, <minpool>, <maxpool>, <maxsize>, <idleTimeout>, <url>, <username>, <password>);
or...
ConnectionPool pool = new ConnectionPool(<poolname>, <minpool>, <maxpool>, <maxsize>, <idleTimeout>, <url>, <properties>);
For example, to create a connection pool to access a database using the Oracle "thin" driver you could do something similar to this:
String url = "jdbc:oracle:thin:@myDB.myISP.com:1521:test";
// Note, timeout is specified in milliseconds.
ConnectionPool pool = new ConnectionPool("local",
5, 10, 30, 180000, url, "b_lightyear", "BeyondInfinity");
Once the pool is created it is ready to hand out connections. By default the pool doesn't open any connections until the first time one is requested, even if minpool > 0. If you would like to trigger the pool to start populating at startup, make a call to the pool's init()
method.
To obtain a Connection object from the pool and use it you can now do this:
Connection con = null; long timeout = 3000; // 3 second timeout try { con = pool.getConnection(timeout); if (con != null) { // ...use the connection... } else { // ...do something else (timeout occurred)... } } catch (SQLException sqlx) { // ...deal with exception... } finally { try { con.close(); } catch (SQLException sqlx) { /* ... */ } }
The only difference between using a pooled connection and using a normal connection is how that connection is obtained. The connection is used and closed just like a normal connection. Be aware that as with unpooled connections, it's important to ensure you close the connection when finished with it, to avoid it becoming a point of unwanted resource retention (i.e. a possible memory leak).
Once the application no longer requires the pool you should release its resources:
pool.release();
Releasing the pool when finished is an important step and should not be omitted. Failure to release the pool can cause an application to hold on to resources, which often leads to unexpected results such as unexpected memory usage, failure of applications to terminate, etc. To help with this it's possible to automate the release with a shutdown-hook, which releases the pool when the Java Virtual Machine exits.
Using a DataSource
DBPool comes with a javax.sql.DataSource implementation which can be used similarly to other DataSource instances, with the benefit that it provides pooled connections using a wrapped ConnectionPool instance. The table below lists the properties that are supported, which are similar to those specified in the section: Defining the behaviour of the pool.
Property | Description |
---|---|
name | Name of the DataSource, which is also used to assign a ConnectionPool name. |
description | Description for the DataSource. |
driverClassName | Fully-qualified class name of JDBC Driver to use. |
url | JDBC URL to connect to the database. |
user | Username for database connections. |
password | Password for database connections. |
passwordDecoderClassName | Fully-qualified class name of snaq.db.PasswordDecoder implementation to use. (It must have a public no-argument constructor). |
minPool | Minimum number of pooled connections to maintain. |
maxPool | Maximum number of pooled connections to maintain. |
maxSize | Maximum number of connection that can be created. |
idleTimeout | Idle timeout of pooled connections (seconds). |
loginTimeout | Timeout for database connection attempts (seconds). |
validatorClassName | Fully-qualified class name of snaq.db.ConnectionValidator implementation to use. (It must have a public no-argument constructor). |
validatorQuery | Query string to use for validation, if validatorClassName not specified. This is passed to a snaq.db.SimpleQueryValidator instance. |
For example:
DBPoolDataSource ds = new DBPoolDataSource();
ds.setName("pool-ds");
ds.setDescription("Pooling DataSource");
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://192.168.1.101:3306/ReplicantDB");
ds.setUser("Deckard");
ds.setPassword("TyrellCorp1982");
ds.setMinPool(5);
ds.setMaxPool(10);
ds.setMaxSize(30);
ds.setExpiryTime(3600); // Specified in seconds.
ds.setValidationQuery("SELECT COUNT(*) FROM Replicants");
There is also a factory class, snaq.db.DBPoolDataSourceFactory, which may be used to create DBPoolDataSource instances, and is useful for including DBPool pooling in application servers. The factory class enables an instance to be created and configured using the standard configuration mechanism of many application servers. For example, a web server "configuration.xml" file might have an entry as show below. For more information see the Javadoc API for snaq.db.DBPoolDataSource.
<Resource factory="snaq.db.DBPoolDataSourceFactory" type="javax.sql.DataSource" name="jdbc/pool-ds" auth="Application" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://192.168.1.101:3306/ReplicantDB" user="Deckard" password="TyrellCorp1982" minPool="5" maxPool="10" maxSize="30" idleTimeout="3600" validationQuery="SELECT COUNT(*) FROM Replicants" />
Using the ConnectionPoolManager
The pool manager provides comprehensive support for defining one or more connection pools in an external properties file. In fact multiple pool managers are support, allowing you to define groups of pools from multiple sources, but most applications generally only require a single pool manager. A ConnectionPoolManager instance provides access to a number of ConnectionPool objects, each of which provides access to a user-specified database source. For each pool manager, the user specifies the JDBC drivers required and the parameters for each connection pool. With this information the pool manager registers the necessary JDBC drivers and creates the pools ready for use.
To use it, you first need to obtain a ConnectionPoolManager using one of the static getInstance(...)
methods:
Method of Access | Explanation |
---|---|
getInstance() * |
Returns the pool manager instance defined by the default properties file ("dbpool.properties") within the CLASSPATH (or appropriate location for ClassLoader to find). |
getInstance(String) * |
Returns the pool manager instance defined by the properties file with the filename specified, located in the CLASSPATH (or appropriate location for ClassLoader to find). |
getInstance(File) * |
Returns the pool manager instance defined by the properties file specified. |
createInstance(Properties) **followed by getInstance() |
Creates a pool manager instance from the specified Properties object and makes it available via the getInstance() method. |
*Note 1: Each of these methods has another version which also takes a string requesting the text-encoding in which the properties should be read. | |
**Note 2: It's not possible to use both a default properties file instance and a Properties object instance simultaneously. If the default properties file instance is obtained and not released, a call to createInstance(Properties) will fail with a RuntimeException. Aside from this limitation, multiple ConnectionPoolManager instances may be used, each with its own parameters. |
Using these instance accessors you have access to a theoretically unlimited number of different pool managers, although in reality using more than just one is rare. So, to obtain the pool manager defined by the default properties file:
ConnectionPoolManager cpm = null; try { cpm = ConnectionPoolManager.getInstance(); } catch (IOException iox) { ... }
This step would normally be done at the initialization stage of an application. For instance, in a web application the pool manager could be created and assigned to an application scope variable, where it could be accessed by other classes which require database access.
Once you have a pool manager reference you can now obtain/return ("check-out"/"check-in") connections from/to its pools. To obtain a connection use the getConnection(<poolname>)
method. This method will obtain a database connection if one is immediately available, or return null if not. If you would rather wait a certain amount of time in case a connection becomes available use the getConnection(<poolname>, timeout)
instead, where timeout is specified in milliseconds. If a connection becomes available within the timeout the method will return with the connection, otherwise null is returned. Once you have finished with this connection you simply close it as you would a normal connection.
For example, the following code obtains a connection from the pool manager, performs some operations, then returns the connection:
Connection con = null; long timeout = 3000; // 3 second timeout try { con = cpm.getConnection(<poolname>, timeout); if (con != null) { // ...use the connection... } else { // ...do something else (timeout occurred)... } } catch (SQLException sqlx) { // ...whatever... } finally { try { con.close(); } catch (SQLException sqlx) { /* ... */ } }
Notice that when you have finished working with a connection you simply call its close() method as you would normally. Instead of being closed the connection is actually recycled within the pool ready to be used again.
When you have completely finished with all the pools managed by a ConnectionPoolManager object you should release it to ensure all of the resources it is using are released.
cpm.release();
Defining the behaviour of the pool
When using a pool manager the behaviour of the pools is governed by either a properties file (by default called "dbpool.properties") or by a Properties object supplied by the user. The format of the properties file is shown below. The same key/value pairs apply when specifying a pool manager using a Properties object.
When using a standalone ConnectionPool many of these properties can also be set using a variety of instance methods.
name=<name> drivers=<class name of driver> <poolname>.url=<JDBC connection URL for database> <poolname>.user=<user name> <poolname>.password=<password> <poolname>.minpool=<minimum pooled connections> <poolname>.maxpool=<maximum pooled connections> <poolname>.maxsize=<maximum possible connections> <poolname>.idleTimeout=<idle timeout of connections (seconds)> <poolname>.validator=<ConnectionValidator implementation> <poolname>.decoder=<PasswordDecoder implementation> <poolname>.prop.<property>=<value>
Properties drivers and url are mandatory, while the rest are optional and take on default values when not supplied as described in the table below. Inevitably you'll likely end up supplying values for user/password, and also for at least maxpool or you'll get no benefit from the pooling system.
Property | Purpose | Possible values | Default value |
---|---|---|---|
name | Specifies a name for the pool manager instance. Optional, but useful to obtain isolated logging output from this instance if required (see logging configuration). | string | |
drivers | Comma-separated list of fully-qualified JDBC Driver class names required by configured pools. | string, ... | |
logfile | Specifies a custom log file for this pool manager. | string | |
dateformat | Date formatting string used for the custom log (java.text.SimpleDateFormat style). | string | |
Any of the following properties may be repeated for each pool defined: | |||
pool.url | Specifies the JDBC database connection URL. | string | |
pool.user | Specifies the JDBC database connection username. | string | |
pool.password | Specifies the JDBC database connection password. | string | |
pool.minpool | Specifies the minimum number of connections that should be held in the pool. | integer, >=0 | 0 |
pool.maxpool | Specifies the maximum number of connections that may be held in the pool. | integer, >=0 (>=minpool) | 0 |
pool.maxsize | Specifies the maximum number of connections that can be created for use. | integer, >=0 (>=maxpool) | 0 (unlimited) |
pool.idleTimeout | Specifies the timeout for individual connections that are idle (seconds). | integer, >=0 | 0 (no timeout) |
pool.validator | Determines how to ensure that connections are valid. | Class name (implements ConnectionValidator) | (none) |
pool.decoder | Specifies an optional password decoder class. | Class name (implements PasswordDecoder) | (none) |
pool.prop.property | Optional properties to be passed to the JDBC driver (e.g. prop.foo=bar). | string | |
These "non-standard" properties may also be repeated for each pool defined: | |||
pool.logfile | Specifies a custom log file for this pool (in addition to regular logging). |
string | |
pool.dateformat | Date formatting string used for the custom log (java.text.SimpleDateFormat style). | string | |
pool.cache | Option to enable/disable caching of statements. | true/false | true (caching enabled) |
pool.access | Pool item selection strategy. | string : {LIFO, FIFO, RANDOM} | LIFO |
pool.async | Option to enable/disable asynchronous destruction of invalid connections. | true/false | false (synchronous) |
pool.recycleAfterDelegateUse | Option to enable/disable recycling of connections which have had the underyling delegate connection accessed. | true/false | false (don't recycle) |
pool.listenerN | Option to specify a pool listener class name to attach to the pool. N denotes an integer, starting at zero, incrementing for each new listener. Valid classes must have no-argument or one-argument (Properties) constructor. |
Class name (implements ConnectionPoolListener/ObjectPoolListener) | |
pool.listenerN.property | Optional properties to pass to constructor of listenerN (e.g. mypool.listener0.foo=bar). Item are collected as Properties instance (without prefix) and passed to class constructor. |
string | |
pool.mbean | Option to register a basic MBean for JMX management of this pool. | true/false | true |
You can define multiple pools in the properties file, provided each one has a different pool name. To specify multiple database driver classes simply separate them using a comma/space. Each driver entry needs to be the fully-qualified class name of a valid JDBC Driver which implements the java.sql.Driver interface.
You can optionally supply additional properties to the JDBC driver by adding <poolname>.prop.property=value
within the properties file.
Internal validation of the pooling properties is performed, so if you specify impossible values the pool will default to: no pooled items, unlimited total connections, no idle timeout. This will behave similarly to not having a pool, so it is worth thinking through the values you choose.
For example, the properties for a MySQL database hosted by a computer on the local subnet could be:
name=poolman1 drivers=com.mysql.jdbc.Driver local.url=jdbc:mysql://10.0.0.7:3306/homeDB local.user=Nemo local.password=LuckyFin local.minpool=0 local.maxpool=3 local.maxsize=10 local.idleTimeout=0
This pool will not create any connections until they are requested, will only ever hand out a maximum of 10 connections simultaneously, and will retain up to 3 to be reused once they have been created. Pooled connections that are not being used will stay in the pool indefinitely.
- minpool determines the minimum number of connections that should be held in the pool.
- maxpool determines the maximum number of connections that may be held in the pool.
- maxsize determines the absolute maximum number of connections that can be created for use.
- idleTimeout is the idle timeout for individual connections (seconds).
The number of connections available for use is dictated by maxpool and maxsize. A maximum of maxpool items are ever held for reuse in the pool, although up to maxsize can be created and handed out; surplus connections will be destroyed when handed back. If maxsize is zero then an unlimited number of connections can be obtained, but the surplus will be destroyed when closed.
Depending on your application's demands you'll see differing behaviour in the pool. With low demand only a few connections are likely to ever be created (sometimes fewer than maxpool), whereas with very high demand you may find the limits of the pooling parameters and have to think about changing them. The likely usage should be carefully taken into consideration when configuring the properties file.
What this means...
You have great flexibility to create connection pools to deal with all likely scenarios. For instance, some database drivers only give you a limited number of connections due to either limited resources or licence limitations. In this case you set maxsize at or just below this limit, and choose a maxpool value which deals well with the average load. If you have a consistently high-demand application with a limited number of connections you may want to have no idleTimeout and make the maxpool equal to maxsize which will simply pool all available connections and keep them open; this gives you maximum database connection performance, but may tie up resources on the machine which could be used elsewhere. Whatever values you choose, make sure you choose them carefully based on a solid understanding of the behaviour of application and the demand for database access.
Example...
The pool shown above will create new connections as required, up to a maximum of 20 simultaneous connections. When each connection is returned it will only be kept in the pool if there are fewer than 10 already in the pool. Otherwise it will close the returned connection. This occurs because there is no idle timeout for the pool.
The example below shows the properties file for an Oracle database using the Oracle Thin JDBC driver to access the database called "test".
# DBPool connection pool definitions. name=poolman2 drivers=oracle.jdbc.driver.OracleDriver # Pool for accessing data from space. pool.url=jdbc:oracle:thin:@myHost.myDomain:1521:test pool.user=b_lightyear pool.password=BeyondInfinity pool.minpool=1 pool.maxpool=10 pool.maxsize=30 pool.idleTimeout=600 pool.validator=snaq.db.Select1Validator
Firstly note that as it's a properties file, comments are allowed. Regarding pooling, there's a maximum limit on the number of connections as might be the case when using an ISP-hosted database server. To save resources and to avoid compromising performance a maximum of 10 connections are pooled for re-use. When demand is high up to 30 connections may be handed out, and when demand is low, each connection will expire after ten minutes of idle time (600 seconds). When there is no demand there should also always be at least one open connection available. Note that this example also make use of custom connection validation (see the Advanced features section for more information).
Logging configuration
While individual pool and pool manager log files are still supported for backwards-compatibility, it's advisable to use the more powerful an configurable logging support that is now included. Logging is supported through the Apache Commons Logging library, which provides automatic support for several logging libraries, of which these are the two most likely to be used:
- Apache log4j
- Java Logging (in Java Platform 1.4 and later)
Log4j is the recommended choice, providing the most out-of-the-box flexibility. The Java Logging API is an easy and obvious choice for many because of its inclusion with the Java Platform, but it doesn't have the versatility of log4j. If you don't already have a logging solution, use log4j to avoid headaches.
Summary explanation: Configuration of logging is done according to the logging system used, so the documentation for the relevant library should be consulted. All libraries share the concept of a "logger", which is named and can be configured using an external file. Loggers have "appenders" (or "handlers" in Java Logging API), which dictate the destination of the log entries for the logger. Each appender (or handler) has a "layout" (or "formatter") which determines how the log entry is written to that destination. Every log entry has a "level" which indicates is severity/importance: {fatal, error, warn, info, debug, trace}. The level of interest can be set for a logger/appender, and log entries which make it past the level-filtering applied by the logger/handler get logged. For more details read the documentation with your chosen logging library which will explain it more thoroughly.
The names of the relevant loggers for DBPool follow the Java package/class format as follows:
Logger Name | Description |
---|---|
snaq.db.ConnectionPoolManager | Parent logger for all pool managers. |
snaq.db.ConnectionPoolManager.<name> | Instance logger for pool manager with specified name. The name may be specified in the properties file, and if not defined the logger will not be created (output goes to parent logger). |
snaq.db.ConnectionPool | Parent logger for all connection pools. |
snaq.db.ConnectionPool.<name> | Instance logger for connection pool with specified name. Pool manager use: name is specified in the properties file in the pool definition (e.g. <name>.foo=bar). Standalone use: name is specified in the constructor, and if not specified (i.e. null or "") is not created (output goes to parent logger). |
Commented examples of basic logging configuration files for the above two libraries (log4j.properties / logging.properties) are included with the complete download archive. The following simple example for the log4j library shows what might be used for a very basic application with minimal logging requirements:
# Appender to write to console. log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout log4j.appender.CONSOLE.layout.ConversionPattern=%-5p %C{1} - %m%n # Appender to write to shared/parent pool manager log. log4j.appender.POOLMANAGER=org.apache.log4j.FileAppender log4j.appender.POOLMANAGER.File=DBPool-managers.log log4j.appender.POOLMANAGER.layout=org.apache.log4j.PatternLayout log4j.appender.POOLMANAGER.layout.ConversionPattern=%d [%5p]: %m%n # Appender to write to shared/parent pool log. log4j.appender.POOLS=org.apache.log4j.FileAppender log4j.appender.POOLS.File=DBPool-pools.log log4j.appender.POOLS.layout=org.apache.log4j.PatternLayout log4j.appender.POOLS.layout.ConversionPattern=%d [%5p]: %m%n # Appender for pool instance (pool-local). log4j.appender.LOCAL=org.apache.log4j.FileAppender log4j.appender.LOCAL.File=DBPool-local.log log4j.appender.LOCAL.layout=org.apache.log4j.PatternLayout log4j.appender.LOCAL.layout.ConversionPattern=%d [%5p]: %m%n # Turn off root logging. log4j.rootLogger=none # Enable parent pool manager logging ("trace" level, to file/console). log4j.logger.snaq.db.ConnectionPoolManager=trace, POOLMANAGER, CONSOLE # Enable parent connection pool logging ("info" level, to file/console). log4j.logger.snaq.db.ConnectionPool=info, POOLS, CONSOLE # Enable instance connection pool logging ("debug" level, to file only). log4j.logger.snaq.db.ConnectionPool.pool-local=debug, LOCAL
Java Logging
Apache Commons & log4j | Java Logging |
---|---|
fatal | SEVERE |
error | SEVERE |
warn | WARNING |
info | INFO |
CONFIG | |
debug | FINE |
FINER | |
trace | FINEST |
For those who prefer to use the Java Logging API, you need to know how the various logging libraries compare in terms of their log levels, indicating the relative severity of each log entry. For example, it would be easy to think the "trace" level of Apache Commons Logging would equate to "FINE" in Java Logging, but it actually equates to "FINEST". The table of equivalents is shown to the right, and should help those trying to get the log output required.
Additionally, a convenient single-line Formatter implementation has been packaged within the distribution to make log output more easily readable: snaq.util.logging.TerseFormatter. By default TerseFormatter produces a simple output: "<date/time> <level>: <message>
", but can be configured a to use a different log message format if required (see the API documentation).
Example logging.properties file for the Java Logging API, which writes single-line entries to the console and XML output to file:
# Handler to write to console (System.err in this case). java.util.logging.ConsoleHandler.formatter=snaq.util.logging.TerseFormatter java.util.logging.ConsoleHandler.encoding=UTF-8 # Handler to write to log files. java.util.logging.FileHandler.formatter=java.util.logging.XMLFormatter java.util.logging.FileHandler.encoding=UTF-8 java.util.logging.FileHandler.pattern=DBPool_%u_log.xml # Turn off root logging (INFO level for when enabled). handlers= .level=INFO # Enable parent pool manager logging ("FINEST"/trace level, to file/console). snaq.db.ConnectionPoolManager.level=FINEST snaq.db.ConnectionPoolManager.handlers=java.util.logging.ConsoleHandler, java.util.logging.FileHandler # Enable parent connection pool logging ("INFO" level, to file/console). snaq.db.ConnectionPool.handlers=java.util.logging.ConsoleHandler, java.util.logging.FileHandler snaq.db.ConnectionPool.level=INFO # Enable instance connection pool logging ("FINE"/debug level, to file only). snaq.db.ConnectionPool.pool-local.handlers=java.util.logging.FileHandler snaq.db.ConnectionPool.pool-local.level=FINE
Good database programming practices
The following two simple practices will help to ensure you get the best from DBPool:
- Always close your ResultSet/Statement/Connection instances once finished with them.
- Always close them in the reverse sequence to their creation:
Connection con = pool.getConnection(); Statement st = con.createStatement(); ResultSet res = st.executeQuery("..."); ... res.close(); st.close(); con.close();
Obviously not every application is this simple, but if the flow of code sticks to this principal, the likelihood of resource retention problems ("memory leaks") and unexpected pooling behaviour is markedly reduced. It seems obvious when stated succinctly, yet many bugs/problems/headaches can be ascribed to not following these two guidelines.
Choosing pooling parameters
Deciding what pooling parameter value to use to get the best performance from a connection pooling system is a difficult task. Your choices depend on many factors: the application (and its code efficiency), the database drivers, the database application, the database platform, etc. All these variables (and more) lead to the conclusion that choosing good parameters is a matter of trial and error; there is no absolute way of knowing what is best without trying out a few ideas. To start, try to step back from the details of the code and view your application as a whole.
Some of the questions below may assist you finding useful parameter values. Bear in mind that you can turn on the debug option for each pool to see more detailed usage information in the log. This helps determine whether the behaviour is appropriate to the circumstances, and is highly recommended during development. Be aware that enabling debug output is detrimental to overall performance, so remember to disable it again for production releases.
General questions to think about:
- Are database accesses generally quick, or do they take a long time?
- Are database accesses generally frequent or infrequent?
- Are there any database accesses which obviously take a long time to complete?
If your application only occasionally requires access to a database then it's possible you don't even need connection pooling. However, it might still be able to provide a convenient mechanism for providing access to the database. Additionally, if the system is likely to be up-scaled you will need to think of the future implications in terms of database access.
Issues that may affect the likely value for minpool/maxpool:
- How much connection demand is there under normal use? ...and under heavy use?
- Are connections usually used for a long time, or only quickly?
Issues that affect the likely value for maxsize:
- Is there a limit on the number of connections imposed by the JDBC driver (either code or licence)?
- Does the database have resource limitations so only a certain number of connections can be used simultaneously?
- Are there other system limitations (memory, processor, etc.) that restrict connection creation (or creation efficiency)?
Issues that affect the likely value for idleTimeout:
- Does the database automatically close connections after a certain period of inactivity?
- Is this behaviour an option which can be switched off?
- Are the connections reliable over long periods of inactivity (hidden resource use, memory leaks, etc.)?
- How much connection demand is there under normal use? ...and under heavy use?
Issues that may affect whether to use statement caching:
- Does the application frequently issue the same queries to the database (Prepared/CallableStatements)?
- Does the application make use of user-specified Statements (ie. ResultSet type, concurrency settings, etc.)?
Issues that may affect whether/how to use custom connection validation:
- Do you frequently find connections in an invalid state after a while?
- Do you need to validate connections, but the database doesn't support the auto-commit feature?
By looking at the system as a whole and trying to determine its database access behaviour you can try to understand the most likely useful values for these parameters. Try them out to see if they give a performance boost. Try adjusting them a bit and seeing what effect this has on the performance. You may find adding timing and/or pool analysis code to the application helps achieve better performance. To achieve this the pooling system has methods to discover connection "hit rate", the pool parameters, and information about the number of connections held, both available and in-use. For more useful information, see the following Advanced features section.
Advanced features
Connection validation
Each time a connection is requested from a pool it is validated prior to being handed out. By default this check is done using the isClosed()
method of the connection object. Although this method is relatively quick to return, it is not reliable as it only returns whether the close()
method has explicitly been called (the connection may have become invalid another way). You can override this default by using a custom validation class, which is simply a class which implements the snaq.db.ConnectionValidator interface. The simplest way of making the validation more rigorous is to use one of the supplied validator classes:
- snaq.db.AutoCommitValidator
- snaq.db.Select1Validator
- snaq.db.SimpleQueryValidator (not designed for use with pool manager)
The AutoCommitValidator class has been provided as a convenience to perform connection validation using the standard SQL call setAutoCommit(true)
which should determine whether the connection can be used without problems, provided your JDBC database driver supports this feature. Another fairly common way of validating connections is to issue the SQL query "SELECT 1", which can be done by using the snaq.db.Select1Validator class. However, you can write your own validation classes as required by implementing the ConnectionValidator interface, or by using/sub-classing the (Simple)QueryValidator convenience class.
If using a standalone ConnectionPool call the setValidator(ConnectionValidator)
method. If using the pool manager include this line (key/value pair) in your properties:
<poolname>.validator=snaq.db.AutoCommitValidator
Bear in mind that you will need to find a practical balance between the validation rigorousness and application performance. To obtain (almost) error-proof validation you want a validator that performs full queries on the database to determine if it's operational. Sometimes this is necessary to provide the best service, but it can also impact performance greatly. It's worth noting that some database/driver combinations can operate in a strange way if the validation is not rigorous enough, due to connections being closed unexpectedly and weak validation not identifying the closure. The chances of this is greater when using non-Type-4 JDBC drivers (i.e. one which uses native code instead of being pure Java). If in doubt use a validator that is more rigorous than required, then reduce the rigorousness later to enhance speed performance and assess if quality of validation becomes a problem. If necessary view debug logs to obtain more information about the functioning of the pool.
Disabling statement caching
Caching of Statement objects is provided for by the pooling system by using a connection wrapper class (statements created with a user-specified ResultSet type and concurrency are not cached; only the default statements are cached). If caching is not required you can disable it by adding the following to the properties file:
<poolname>.cache=false
or by directly disabling caching on a standalone pool by calling one of the setCaching(...)
methods.
Password encryption
Connection pools are usually setup using the text-based properties file, which contains a plaintext password. Should the security of the machine hosting this properties file be compromised this allows the plaintext password to become available, which in turn compromises the security of the database server. To deal with this users can create a class which implements the snaq.db.PasswordDecoder interface, which specifies single method:
char[] decode(String encoded);
A very simple example PasswordDecoder (snaq.db.RotDecoder) has been provided which performs simple Rot13-encoding. This algorithm is very insecure and is only provided as an example for demonstration purposes.
Once you have implemented your PasswordDecoder class, if using a standalone ConnectionPool call the setPasswordDecoder(PasswordDecoder)
method, or if using the pool manager include this line (key/value pair) in your properties:
<poolname>.decoder=<class name>
Note: this mechanism only allows for implementation of password-based security.
Shutdown Hook
When a pool or pool manager is no longer required, the standard procedure is to "release" it by calling the appropriate release()
method, on either the ConnectionPool or ConnectionPoolManager instance. Another option is to register a "shutdown hook" to perform this step automatically when the Java VM is exiting. Bear in mind that because it occurs when the VM is exiting, you need to check if this is an appropriate time for the release for your application. To register a shutdown hook, simply call registerShutdownHook()
on a ConnectionPool/ConnectionPoolManager/DBPoolDataSource instance, or use the static method ConnectionPoolManager.registerGlobalShutdownHook()
.
Note: if individual ConnectionPool hooks are registered, then a ConnectionPoolManager global hook registered and removed, the individual pools will have lost their individual shutdown hooks, so will need to be re-registered as required.
Asynchronous connection destruction
Sometimes you will come across situations where the destruction of a database connection takes a disproportionate length of time. This could occur because the connections regularly get into an unstable state for some reason, or simply that the nature of the system being used means that many other resources need to be cleaned up as well. This situation can be indicative of a more serious instability (worth considering more rigorous connection validation), but there are some occasions when this is simply unavoidable. To ensure that pooling performance is not affected when this occurs you can turn on this option to perform the connection destruction asynchronously, thereby returning control immediately and not tying up the pool unnecessarily. It is recommended (and the default setting) that you leave this option disabled unless you are sure you need it. To enable this option add this line to the properties file:
<poolname>.async=true
or call setAsyncDestroy(true)
on a ConnectionPool instance.
Asynchronous/forced pool release
You can ask a pool to release its resources asynchronously by calling the releaseAsync()
method instead of the release()
method when finished with a pool. This method returns immediately and performs the pool cleanup in a background thread. This can be useful if the application continues after it has finished using the pool, but you don't want the application to have to wait for all the resources of the pool to be released.
You can force a pool to be released by using the releaseForcibly()
method. Any open connections which have not been returned to the pool will be forcibly closed using this method. This can sometimes be useful when the pool has been used to hand out connections to third party code where it is unreliable whether or not all connections will be returned, and an application termination is required. Bear in mind that forcibly closing connections may lead to unpredictable database contents and data loss.
Note: When using the pool manager calling release()
performs the release synchronously and forcibly, so it's wise to ensure all connections from the underlying pools have been closed.
Pool listeners
It can be useful to have code able react to the current state of a pool. For this reason pools can issue events about their activity to objects which have registered interest. Listener objects must implement the snaq.db.ConnectionPoolListener or snaq.util.ObjectPoolListener interface, and can register/deregister their interest by using the provided methods, for example:
- addConnectionPoolListener(ConnectionPoolListener)
- removeConnectionPoolListener(ConnectionPoolListener)
Also provided for convenience is the snaq.db.ConnectionPoolEventAdapter class, which provides empty implementations of all the methods in the ConnectionPoolListener interface, so can be extended to override fewer methods. The events triggered are as follows:
Event Identifier | Description |
---|---|
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. (This generally only happens is flush() is explicitly called for a pool.) |
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. |
PoolTracer
An example of the above pool listener feature is that of the included snaq.util.PoolTracer class. This class implements a simple PoolListener to write pool activity data to a log file. This class can either be instantiated via one of its constructors programatically, or if using the pool manager using the <poolname>.listenerN
pool property. PoolTracer requires a filename for the trace log file, which can be specified as <poolname>.listenerN.file=<filename>
. See the Javadoc API for the PoolTracer class for more information.
For example, to specify the pool tracer as the only listener on a pool "pool-local", writing to a trace log file called "trace.log" in the current folder, place the following lines in the properties file alongside the other properties:
pool-local.listener0=snaq.util.PoolTracer
pool-local.listener0.file=trace.log
# Why not specify the optional date format too?
pool-local.listener0.dateformat=HH:mm:ss,SSS
Notes & troubleshooting
Exception/Error: java.lang.AbstractMethodError
As the Java API documentation states, "this error can only occur at run time if the definition of some class has incompatibly changed since the currently executing method was last compiled". When using a pre-compiled DBPool JAR library, this error appears when using a database driver which does not support the same JDBC specification as the JAR library. The best solution is either to download the appropriate version of DBPool from the website, or recompile the library from source using the appropriate Java/JDBC versions.
Exception/Error: java.lang.NoSuchMethodError
This error is thrown if a class file cannot find a specified method, and is usually the result of running DBPool in an older virtual machine than that for which it was compiled. The solution is to either upgrade your Java Platform, or use a compatible version of DBPool, either downloaded or recompiled from source.
Exception/Error: java.sql.SQLException: Unsupported feature
This exception is raised when trying to use using a JDBC feature which the underlying database driver doesn't support, usually because your Java platform is using a more recent JDBC specification than the one the driver was compiled against. To resolve this issue, either update your database driver to the appropriate version, or use a different version of DBPool.
CLASSPATH problems? Unable to find properties file?
With some application servers the CLASSPATH variable is less than obvious. Due to the need for segregation of both resources and security policies they very often use separate classloaders which only permit loading of classes and resources from specific locations. Not surprisingly this is vendor-specific, and the only way to really get to grips with the problem is to thoroughly read the documentation provided with the application server. If it helps to diagnose the problem the pool manager uses the following syntax to obtain a locate the properties file:
ConnectionPoolManager.class.getResourceAsStream(<filename>)
where filename is prepended with a "/" character to ensure it is accessed directly. The default properties file is therefore accessed using the call Class.getResourceAsStream("/dbpool.properties")
. Bear in mind that classloaders provided by other vendors may not conform to the standard resource-finding mechanism as used by the system classloader, but this should help in tracking down problems. Alternatively you can use a different ConnectionPoolManager.getInstance(...) method.
Connection creation
The following pseudo-code shows how the connection pool tries to establish new database connections, and may help debug certain problems.
- if Properties object used
- ... use
DriverManager.getConnection(url, properties)
- else if username specified
- ... use
DriverManager.getConnection(url, username, password)
- else
- ... use
DriverManager.getConnection(url)
Note: if any driver properties are specified in the properties file (e.g. <poolname>.prop.foo=bar
), the pool manager injects the username/password into the properties for convenience so the first getConnection(...) method above works.
Release notes for DBPool 5.0
This major release of DBPool is not backwards-compatible with previous versions. Compiled code which depends on previous versions of DBPool might need to be modified, and will need to be recompiled against this new version in order to work correctly. If you're only using the pool manager functionality you may find you can simply drop in the new JAR file, the associated logging JAR file, and keep your application working. The key compatibility-related changes are as follows:
- Terminology change: "idle timeout" instead of "expiry" (for clarity/consistency).
- Idle timeout for ConnectionPool instances is now specified in seconds (instead of milliseconds).
- Parameter/property change: idleTimeout (instead of expiry, see above).
- Parameter/property addition: minpool, which determines the minimum number of items to aim to keep in the pool.
- Parameter/property change: maxpool (instead of poolsize, for consistency).
- Parameter/property change: maxsize (instead of maxconn).
Upgrade steps to 5.0
Pool Manager users
- Update properties file:
- Change entries from
<poolname>.maxconn
to<poolname>.maxsize
- Add entry for
<poolname>.minpool
if required - Change entries from
<poolname>.expiry
to<poolname>.idleTimeout
- Check pooling requirements and adjust values of
minpool
/maxpool
/maxsize
/idleTimeout
as required
- Change entries from
- Make sure your code releases the pool manager when no longer needed:
- Either: explicitly call
release()
on the ConnectionPoolManager instance when no longer needed. - Or: register a shutdown hook.
- Either: explicitly call
- Add Apache Commons Logging library as an application dependency (if not already used).
- Configure logging for your application (optional, but recommended).
If you choose to use the new logging support, you may also want to also remove entries for logfile
/debug
/dateformat
.
Standalone pool users
- Modify pool constructor calls to include the extra minpool property as required.
- Modify pool constructor and
setParameters(...)
method calls to specify idle timeout in seconds (instead of milliseconds) - Change deprecated
getExpiryTime()
method calls togetIdleTimeout()
. - Make sure your code releases the pool when no longer needed:
- Either: explicitly call
release()
on the ConnectionPool instance when no longer needed. - Or: register a shutdown hook.
- Either: explicitly call
- Add Apache Commons Logging library as an application dependency (if not already used).
- Configure logging for your application (optional, but recommended).
DataSource users
- Add optional
setMinPool(...)
method calls as required. - Change deprecated
get
/setExpiryTime(...)
method calls toget
/setIdleTimeout(...)
. - Make sure your code releases the pool when no longer needed:
- Either: explicitly call
release()
on the DBPoolDataSource instance when no longer needed. - Or: register a shutdown hook.
- Either: explicitly call
- Add Apache Commons Logging library as an application dependency (if not already used).
- Configure logging for your application (optional, but recommended).
Details of API changes
DBPool 5.0 is a major update, and as such the API has changed in significant ways. While some efforts have been made to maintain backwards-compatibility, some changes will impact end-users, most likely those using standalone ConnectionPool instances. Changes to method names for functionality that has stayed the same generally signify care should be taken to find out how the mechanism now functions.
The following API methods have been removed:
ObjectPool(String,int,int,int)
ObjectPool.getPoolSize()
- use getMaxPool()ObjectPool.log(...)
ObjectPool.setLog(PrintStream)
ObjectPool.getLogger()
- use getCustomLogger()ObjectPool.setDebug(boolean)
- use getCustomLogger().setDebug(boolean)ObjectPool.isDebug()
- use getCustomLogger().isDebug()ObjectPool.setDateFormat(...)
- use getCustomLogger().setDateFormat(...)ObjectPool.getDateFormat(...)
- use getCustomLogger().getDateFormat()ConnectionPoolManager.instances()
ConnectionPoolManager.setValidator(...)
- should be called for individual pools insteadConnectionPoolManager.log(...)
ConnectionPoolManager.setLog(...)
CacheConnection.getRawConnection()
- use getDelegateConnection()
The following have changed method signatures:
ConnectionPoolManager.getPools()
- changed to return java.util.Collection instead of array
And the following have been deprecated, and are likely to be removed soon:
ObjectPool.getExpiryTime()
- use getIdleTimeout()ObjectPool.getHitRate()
Other upgrade notes
As with the previous release, those upgrading from pre-4.9 versions should be aware of a couple of small compatibility changes, only affecting code which makes use of the underlying delegate connection returned from a pool. Code doing this usually does so to access vendor-specific driver features, which increases the potential for placing connections in a state less suitable for recycled use (which is why it is generally discouraged). Version 4.9+ flags such use of the delegate, and does not return the connection to the pool for recycling, but instead discards it. This new default behaviour may impact performance for applications making heavy use of such delegate connections, but may be overridden using either a pool manager property (recycleAfterDelegateUse) or directly on a standalone pool with the method setRecycleAfterDelegateUse(boolean)
. When using JDBC 4.0 this also applies when obtaining the delegate connection using the Connection.unwrap(Class<T>)
method
Change log
2010-05-12 (v5.0) |
|
---|---|
2010-01-14 (v4.9.3) |
|
2010-01-11 (v4.9.2) |
|
2009-10-06 (v4.9.1) |
|
2009-09-05 (v4.9) |
|