JDBC Resource Information

A JDBC resource is a named group of JDBC connections to a database. A JDBC resource defines the properties used to create a connection pool. Each JDBC resource uses a JDBC driver to establish a connection to a physical database when the server is started. A pool of connections is created when the first request for connection is made on the pool after you start Web Server.

A JDBC-based application or resource draws a connection from the pool, uses it, and when no longer needed, returns it to the connection pool by closing the connection. If two or more JDBC resources point to the same pool definition, they use the same pool of connections at run time.

The use of connection pooling improves application performance by doing the following:

• Creating connections in advance. The cost of establishing connections is moved outside of the code that is critical for performance.

• Reusing connections. The number of times connections are created is significantly lowered.

• Controlling the amount of resources a single application can use at any moment.

JDBC resources can be created and edited using the Admin Console's Java tab ⇒ Resources sub tab for the configuration. You can also use the wadm create-jdbc-resource and set-jdbc-resource-prop commands. For more information, see the Oracle iPlanet Web Server 7.0.9 Administrator’s Guide.

Each defined pool is instantiated during Web Server startup. However, the connections are only created the first time the pool is accessed. Jump-start a pool before putting it under heavy load.

JDBC resource statistics are available through the Admin Console, CLI, and stats.xml only. They are not shown in perfdump. Some of the monitoring data is unavailable through the Admin Console and can only be viewed through the CLI using wadm get-config-stats and through the stats.xml output.

A pool is created on demand, that is, it is created the first time it is used. The monitoring statistics are not displayed until the first time the pool is used.

JDBC Resource Statistics Available Through the Admin Console

The following table shows an example of the JDBC resource statistics displayed through the Admin Console:

To change the settings for a JDBC resource through the Admin Console, for the configuration, choose the Java tab ⇒ Resources sub tab. Select the JDBC resource. The settings are available on the Edit JDBC Resource page. To change the JDBC resource through the command-line-interface, use wadm set-jdbc-resource-prop.

Connections

This number shows the current JDBC connections, including both free and busy connections.

Tuning – This setting cannot be tuned, but it is a good indicator of recent pool activity. If the number of connections is consistently higher than the minimum number of connections, consider increasing the minimum number of connections to be closer to the number of current JDBC connections. To change the minimum connections for a JDBC resource through the Admin Console, on the Edit JDBC Resources page, edit the Minimum Connections setting. To change the JDBC resource's minimum connections through the command-line-interface, use wadm set-jdbc-resource-prop and change the min-connections property.

Free Connections

This number shows the current number of free connections in the pool. All free connections over the minimum pool size are closed if they are idle for more than the maximum idle timeout. The free connections are not tunable.

Leased Connections

This number shows the current number of connections in use.

Tuning – If number of leased connections is consistently lower than the minimum connections, consider reducing the minimum connections for the JDBC resource. If number of leased connections is consistently higher than minimum connections, consider increasing the minimum connections. If number of leased connections is consistently at the JDBC resource's maximum number of connections, consider increasing the maximum number of connections. The upper limit for the number of leased connections is the number of maximum connections.

To change the minimum or maximum connections for a JDBC resource through the Admin Console, on the Edit JDBC Resource page, edit the Minimum Connections or Maximum Connections fields. To change the JDBC resource's minimum or maximum connections through the command-line-interface, use wadm set-jdbc-resource-prop and change the min-connections or max-connections properties.

Queued Connections

This number shows the current number of requests for connections that are waiting to receive a connection from the JDBC pool. Connection requests are queued if the current number of leased connections has reached the maximum connections.

Tuning – If this number is consistently greater than zero, consider increasing the JDBC resource's maximum connections. To change the maximum connections for a JDBC resource through the Admin Console, edit the Maximum Connections field on the Edit JDBC Resource page. To change the JDBC resource's maximum connections through the command-line-interface, use wadm set-jdbc-resource-prop and change the max-connections property.

JDBC Resource Statistics Not Available in the Admin Console

Some JDBC statistics are available through the wadm get-config-stats command using the --node option, through stats-xml, and through SNMP, but not through the Admin Console.

maxConnections – The configured maximum size of the pool. Use as a reference for other statistics. To change the maximum connections for a JDBC resource through the Admin Console, on the Edit JDBC Resource page, edit the Maximum Connections field. To change the JDBC resource's maximum connections through the command-line-interface, use wadm set-jdbc-resource-prop and change the max-connections property.

peakConnections – The highest number of connections that have been leased concurrently during the history of the pool. This number is a good indication on the upper limit on pool usage. It is limited by the maximum connections setting.

countTotalLeasedConnections – The total number of times a connection has been handed out by the pool. Indicates total pool activity. Not tunable.

countTotalFailedValidationConnections – If connection validation is enabled, shows the number of times a connection has been detected as invalid by the pool. If this number is relatively high, it could signal database or network problems. Not tunable.

peakQueued – The highest number of connection requests that have been queued simultaneously at any time during the lifetime of the pool. Not tunable.

millisecondsPeakWait – The maximum time in milliseconds that any connection request has been in the wait queue. A high number is an indication of high pool activity. The upper limit is the JDBC resource setting wait timeout.

countConnectionIdleTimeouts – The number of free connections that have been closed by the pool because they exceeded the configured JDBC idle timeout. To change the idle timeout for a JDBC resource through the Admin Console, on the Edit JDBC Resource page, edit the Idle Timeout field. To change the JDBC resource's idle timeout through the command-line-interface, use wadm set-jdbc-resource-prop and change the idle-timeout property.

JDBC Resource Connection Settings

Depending on your application’s database activity, you might need to size JDBC resource connection pool settings. Attributes of a JDBC resource which affect performance are listed below, along with performance considerations when setting values.

• Minimum connections

  The size the pool keeps during the life of the server instance, and the initial size of the pool. Defaults to 8. This number should be as close as possible to the expected average size of the pool. Use a high number for a pool that is expected to be under heavy load, to minimize creation of connections during the life of the application and minimize pool resizing. Use a lower number if the pool load is expected to be small, to minimize resource consumption.

• Maximum connections

  The maximum number of connections that a pool can have at any given time. Defaults to 32. Use this setting to enforce a limit in the amount of connection resources that a pool or application can have. This limit is also beneficial to avoid application failures due to excessive resource consumption.

• Idle timeout

  The maximum amount in seconds that a connection is ensured to remain unused in the pool. After the idle timeout, connections are automatically closed. If necessary, new connections are created up to the minimum number of connections to replace the closed connection. Note that this setting does not control connection timeouts enforced at the database server side. Defaults to 60 seconds.

  Setting this attribute to –1 prevents the connections from being closed. This setting is used for pools that expect continuous high demand. Otherwise, keep this timeout shorter than the database server-side timeout (if such timeouts are configured on the specific vendor database), to prevent accumulation of unusable connections in the pool.

• Wait timeout

  The amount of time in seconds that a request waits for a connection in the queue before timing out. After this timeout, the user sees an error. Defaults to 60.

  Setting this attribute to –1 causes a request for a connection to wait indefinitely. This setting could also improve performance by keeping the pool from having to account for connection timers.

• Validation method

  The method used by the pool to determine the health of a connections in the pool. Defaults to off.

  If a validation method is used, the pool executes a sanity check on a connection before leasing it to an application.

  The effectiveness and performance impact depends on the method selected:

  • meta-data is less expensive than table in terms of performance, but usually less effective as most drivers cache the result and do not use the connection, providing false results.

  • table is almost always effective, as it forces the driver to perform an SQL call to the database, but it is also the most costly.

  • auto-commit can provide the best balance of effectiveness and performance cost, but a number of drivers also cache the results of this method.

• Validation Table Name

  The user-defined table to use for validation when the validation method is table. Defaults to test.

  If this method is used, the table used should be dedicated only to validation, and the number of rows in the table should be kept to a minimum.

• Fail All Connections

  Indicates whether all connections in the pool are re-created when one is found to be invalid, or only the invalid one. Only applicable if you have selected a connection validation method. Disabled by default.

  If enabled, all of the re-creation is done in one step, and the thread requesting the connection is heavily affected. If disabled, the load of re-creating connections is distributed between the threads requesting each connection.

• Transaction Isolation Level

  Specifies the Transaction Isolation Level on the pooled database connections.

  By default, the default isolation level of the connection is left intact. Setting it to any value does incur the small performance penalty caused by the method call.

• Guarantee Isolation

  Only applicable if a transaction isolation level is specified. Defaults to disabled.

  Leaving this setting disabled causes the isolation level to be set only when the connection is created. Enabling sets the level every time the connection is leased to an application. In most cases, leave this setting disabled.