Resource Settings

Tuning JDBC and connector resources can significantly improve GlassFish Server performance.

The following topics are addressed here:

• JDBC Connection Pool Settings

• Connector Connection Pool Settings

JDBC Connection Pool Settings

For optimum performance of database-intensive applications, tune the JDBC Connection Pools managed by the GlassFish 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.

Refer to Chapter 12, Administering Database Connectivity, in Oracle GlassFish Server 3.1 Administration Guide for more information about managing JDBC connection pools.

The following topics are addressed here:

• Monitoring JDBC Connection Pools

• Tuning JDBC Connection Pools

Monitoring JDBC Connection Pools

Statistics-gathering is disabled by default for JDBC Connection Pools. Refer to for instructions on enabling JDBC monitoringChapter 8, Administering the Monitoring Service, in Oracle GlassFish Server 3.1 Administration Guide. If using the Administration Console, JDBC monitoring can be enabled on the Configurations->configuration-name->Monitoring page.

The following attributes are monitored:

• numConnFailedValidation (count) Number of connections that failed validation.

• numConnUsed (range) Number of connections that have been used.

• numConnFree (count) Number of free connections in the pool.

• numConnTimedOut (bounded range) Number of connections in the pool that have timed out.

To get JDBC monitoring statistics, use the following commands:

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

To view JDBC monitoring statistics through the Administration Console, navigate to the server (Admin Server) page and click the Monitor tab. Refer to the Administration Console online help for complete instructions.

Tuning JDBC Connection Pools

Refer to Chapter 12, Administering Database Connectivity, in Oracle GlassFish Server 3.1 Administration Guide for instructions on configuring JDBC connection pools. If using the GlassFish Server Administration Console by navigating to the Resources->JDBC->JDBC Connection Pools->jdbc-pool-name page and then clicking the desired tab.

The following JDBC properites affect GlassFish Server performance:

• Pool Size Settings

• Timeout Settings

• Isolation Level Settings

• Connection Validation Settings

Pool Size Settings

Pool Size settings can be configured in the Pool Settings section on the General tab in the Edit JDBC Connection Pool page.

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 advantages and disadvantages to consider when sizing connection pools.

Table 3-4 Connection Pool Sizing

Timeout Settings

The following JDBC timeout settings can be configured on the in the Pool Settings section on the General tab in the Edit JDBC Connection Pool page.

• 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 GlassFish 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.

Isolation Level Settings

The following JDBC Isolation Level settings can be configured in the Transaction section on the General tab in the Edit JDBC Connection Pool page.

• Transaction Isolation: 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 for the Transaction Isolation level. Applicable only when the Transaction Isolation level is specified. The default value is Guaranteed.

  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 the Transaction Isolation level. If that is not possible, consider disabling the Isolation Level Guaranteed option and then make sure applications do not programmatically alter the connections; isolation level.

If you must specify a Transaction 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

JDBC Connection Validation settings can be configured in the Connection Validation section on the Advanced tab in the Edit JDBC Connection Pool page.

• Connection Validation Required: If enabled, the pool validates connections (checks to find out if they are usable) before providing them to an application.

  If possible, keep this option disabled. 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: Specifies the type of connection validation to perform. Must be one of the following:

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

  • metadata: Attempt to get metadata from the connection.

  • table: Performing the query on a specified table. If this option is selected, Table Name must also be set. Choosing this option may be necessary if the JDBC driver caches calls to setAutoCommit() and getMetaData().

  • custom-validation: Define a custom validation method.

• Table Name: Name of the table to query when the Validation Method is set to table.

• Close All Connections On Any Failure: Specify whether all connections in the pool should be closed if a single validation check fails. This option is disabled by default. 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

You can specify transaction support when you create or edit a connector connection pool.

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

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