Sun Java System Application Server Enterprise Edition 8.2 Performance Tuning Guide

Resources

JDBC Connection Pool Settings

For optimum performance of database-intensive applications, tune the JDBC Connection Pools managed by the Application Server. These connection pools maintain numerous live database connections that can be reused to reduce the overhead of opening and closing database connections. This section describes how to tune JDBC Connection Pools to improve performance.

J2EE applications use JDBC Resources to obtain connections that are maintained by the JDBC Connection Pool. More than one JDBC Resource is allowed to refer to the same JDBC Connection Pool. In such a case, the physical connection pool is shared by all the resources.

Monitoring JDBC Connection Pools

Statistics-gathering is enabled by default for JDBC Connection Pools. The following attributes are monitored:

To get the statistics, use these commands:

asadmin get --monitor=true 
serverInstance.resources.jdbc-connection-pool.*asadmin get 
--monitor=true serverInstance.resources.jdbc-connection-pool. poolName.* *

Tuning JDBC Connection Pools

Set JDBC Connection Pool attributes with the Admin Console under Resources > JDBC > Connection Pools > PoolName. The following attributes affect performance:

Pool Size Settings

The following settings control the size of the connection pool:

Initial and Mimimum Pool Size

Size of the pool when created, and its minimum allowable size.

Maximum Pool Size

Upper limit of size of the pool.

Pool Resize Quantity

Number of connections to be removed when the idle timeout expires. Connections that have idled for longer than the timeout are candidates for removal. When the pool size reaches the initial and minimum pool size, removal of connections stops.

The following table summarizes pros and cons to consider when sizing connection pools.

Table 3–6 Connection Pool Sizing

Connection pool  

Pros  

Cons  

Small Connection pool 

Faster access on the connection table. 

May not have enough connections to satisfy requests. 

Requests may spend more time in the queue. 

Large Connection pool 

More connections to fulfill requests. 

Requests will spend less (or no) time in the queue 

Slower access on the connection table. 

Timeout Settings

There are two timeout settings:

Isolation Level Settings

Two settings control the connection pool’s transaction isolation level on the database server:

Avoid specifying Transaction Isolation Level. If that is not possible, consider setting Isolation Level Guaranteed to false and make sure applications do not programmatically alter the connections’ isolation level.

If you must specify isolation level, specify the best-performing level possible. The isolation levels listed from best performance to worst are:

  1. READ_UNCOMMITTED

  2. READ_COMMITTED

  3. REPEATABLE_READ

  4. SERIALIZABLE

Choose the isolation level that provides the best performance, yet still meets the concurrency and consistency needs of the application.

Connection Validation Settings

The following settings determine whether and how the pool performs connection validation.

Connection Validation Required

If true, the pool validates connections (checks to find out if they are usable) before providing them to an application.

If possible, keep the default value, false. Requiring connection validation forces the server to apply the validation algorithm every time the pool returns a connection, which adds overhead to the latency of getConnection(). If the database connectivity is reliable, you can omit validation.

Validation Method

Type of connection validation to perform. Must be one of:

  • auto-commit: attempt to perform an auto-commit on the connection.

  • metadata: attempt to get metadata from the connection.

  • table (performing a query on a specified table). Must also set Table Name. You may have to use this method if the JDBC driver caches calls to setAutoCommit() and getMetaData().

Table Name

Table name to query when Validation Method is “table.”

Close All Connections On Any Failure

Whether to close all connections in the pool if a single validation check fails. The default is false. One attempt will be made to re-establish failed connections.

Connector Connection Pool Settings

From a performance standpoint, connector connection pools are similar to JDBC connection pools. Follow all the recommendations in the previous section, Tuning JDBC Connection Pools

Transaction Support

You may be able to improve performance by overriding the default transaction support specified for each connector connection pool.

For example, consider a case where an Enterprise Information System (EIS) has a connection factory that supports local transactions with better performance than global transactions. If a resource from this EIS needs to be mixed with a resource coming from another resource manager, the default behavior forces the use of XA transactions, leading to lower performance. However, by changing the EIS’s connector connection pool to use LocalTransaction transaction support and leveraging the Last Agent Optimization feature previously described, you could leverage the better-performing EIS LocalTransaction implementation. For more information on LAO, see Configure JDBC Resources as One-Phase Commit Resources

In the Admin Console, specify transaction support when you create a new connector connection pool, and when you edit a connector connection pool at Resources > Connectors > Connector Connection Pools.

Also set transaction support using asadmin. For example, the following asadmin command could be used to create a connector connection pool “TESTPOOL” with the transaction-support as “LOCAL”.

asadmin> create-connector-connection-pool --raname jdbcra 
--connectiondefinition javax.sql.DataSource 
-transactionsupport LocalTransaction 
TESTPOOL