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.