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.
As the creation of JDBC connections are expensive and frequently cause performance bottlenecks in applications, it is crucial to monitor how a JDBC connection pool is releasing and creating new connections, and how many threads are waiting to retrieve a connection from a particular pool. Statistics-gathering is enabled by default for JDBC Connection Pools. The following attributes are monitored:
averageConnWaitTime (count): Average wait time of connections for successful connection request attempts to the connector connection pool.
connectionRequestWaitTime (range): The longest and shortest wait times of connection requests.
numConnAcquired (count): Number of logical connections acquired from the pool.
numConnCreated (count): Number of physical connections created since the last reset.
numConnDestroyed (count): Number of physical connections destroyed since the last reset.
numConnFailedValidation (count): Number of connections that failed validation.
numConnFree (count): Number of free connections in the pool.
numConnReleased (count): Number of logical connections released to the pool.
numConnTimedOut (bounded range): Number of connections in the pool that have timed out.
numConnUsed (range): Number of connections that have been used.
waitQueueLength (count): Number of connection requests in the queue waiting to be serviced.
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.* *
Set JDBC Connection Pool attributes with the Admin Console under Resources > JDBC > Connection Pools > PoolName. The following attributes affect performance:
The following settings control the size of the connection pool:
Size of the pool when created, and its minimum allowable size.
Upper limit of size of the pool.
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–4 Connection Pool Sizing
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.
There are two timeout settings:
Max Wait Time: Amount of time the caller (the code requesting a connection) will wait before getting a connection timeout. The default is 60 seconds. A value of zero forces caller to wait indefinitely.
To improve performance set Max Wait Time to zero (0). This essentially blocks the caller thread until a connection becomes available. Also, this allows the server to alleviate the task of tracking the elapsed wait time for each request and increases performance.
Idle Timeout: Maximum time in seconds that a connection can remain idle in the pool. After this time, the pool can close this connection. This property does not control connection timeouts on the database server.
Keep this timeout shorter than the database server timeout (if such timeouts are configured on the database), to prevent accumulation of unusable connection in Application Server.
For best performance, set Idle Timeout to zero (0) seconds, so that idle connections will not be removed. This ensures that there is normally no penalty in creating new connections and disables the idle monitor thread. However, there is a risk that the database server will reset a connection that is unused for too long.
Two settings control the connection pool’s transaction isolation level on the database server:
Transaction Isolation Level: specifies the transaction isolation level of the pooled database connections. If this parameter is unspecified, the pool uses the default isolation level provided by the JDBC Driver.
Isolation Level Guaranteed: Guarantees that every connection obtained from the pool has the isolation specified by the Transaction Isolation Level parameter. Applicable only when the Transaction Isolation Level is specified. The default value is true.
This setting can have some performance impact on some JDBC drivers. Set to false when certain that the application does not change the isolation level before returning the connection.
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.
Choose the isolation level that provides the best performance, yet still meets the concurrency and consistency needs of the application.
The following settings determine whether and how the pool performs connection validation.
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.
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().
Because many JDBC drivers cache the results of these calls, they do not always provide reliable validations. Check with the driver vendor to determine whether these calls are cached or not.
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
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 sampleRA --connectiondefinition javax.resource.cci.ConnectionFactory -transactionsupport LocalTransaction TESTPOOL
You may be able to further improve performance by configuring four connection pool settings.
Validate Atmost Once: Reduces the number of validation requests by a connection. Specify the amount of time (in seconds), after which a connection is validated atmost once. Default value is 0 which means this property is not enabled.
Associate with Thread: Associates a connection with the thread. When the same thread is in need of a connection, it reuses the connection associated with it. This avoids the overhead of getting a connection from the pool. Values can be either true or false; default is false.
Lazy Connection Association: Lazily associates connections when an operation is performed on them. They are disassociated when the transaction is completed and a component method ends, which helps reuse of the physical connections. Default value is false.
Lazy Connection Enlistment: Enable this option to enlist a resource to the transaction only when it is actually used in a method.