Database connection pool tuning in Tomcat 5

This describes the best parameters for tuning the Tomcat 5 default connection pool for Oracle 10g database.

It refers to the Tomcat 5.0 application server using the DBCP connection pool (version 1.2.1).

JDBC Driver

For Oracle 10gR1 and Oracle 10gR2 servers you should use the Oracle 10gR2 InstantClient JDBC driver.

Parameters on the application server

The parameters of the DBCP connection pool are described in http://jakarta.apache.org/commons/dbcp/configuration.html.

It is recommended to setup the connection pool as a normal JNDI DataSource. The parameters below to be set in the JNDI datasource definition.

Mandatory parameters

You must set the mandatory parameters username, password, url, driverClassName.

Connection parameters

Your code should explicitly specify the auto-commit state of the connection when you fetch it from the pool (normally false). It is reommended not to rely on the defaultAutoCommit parameter (which defaults to true).

It is not recommended to set the defaultReadOnly parameter.

It is not recommended to set the defaultTransactionIsolation parameter. Specify it explicitly in your code if you need to change the transaction isolation level from the driver default.

It is not recommended to set thr defaultCatalog parameter.

Pool parameters

Most of the tuning is done on the initialSize, maxActive, maxIdle, minIdle and maxWait parameters.

...

Connection validation

It is recommended to use connection validation to ensure that timed out connections to the database are identified by the pool and not by your application.

Statement pooling

To check.

Underlying connection

It is not recommended to chnage accessToUnderlyingConnectionAllowed to allow access to the underlying Oracle connection object, although for some Oracle PL/SQL <-> Java mappings it is necessary.

Abandoned connections

You should write your application properly so as not to leak DB connections, e.g. use finally blocks to return the DB connection to the pool on an application exception.

For validation, it is useful to have the feature enabled, since DBCP will log any abandoned connections in recovers (in catalina.out). Set:

• removeAbandoned to true
• logAbandoned to true
• removeAbandonedTimeout to 300 seconds (the default).

It is not recommended to use this in production, once the application has been validated and any leaks resolved. The default for these parameters is false.

Parameters on the database

Last edit: AkosFrohner on 2009-03-09 - 10:36

Maintainer: GavinMcCance