JDBC Connection Pool Attributes

Depending on your application’s database activity, you may need to size connection pool attributes. Attributes of a JDBC connection pool are listed below, along with considerations relating to performance.

• name

  The pool name.

• datasourceclassname

  The jdbc driver class that implements javax.sql.DataSource.

• steadypoolsize

  The size the pool will tend to keep during the life of the server instance. Also 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. This will minimize creation of connections during the life of the application, and will minimize pool resizing. Use a lower number if the pool load is expected to be small. This will minimize resource consumption.

• maxpoolsize

  The maximum number of connections that a pool can have at any given time. Defaults to 32.

  Use this parameter 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.

• poolresizequantity

  Number of connections to be removed when the idletimeout timer expires. Connections that have been idle longer than the timeout are candidates for removal. When the pool size reaches steady-pool-size, the connection removal stops. Defaults to 2.

  Keep this number low for pools that expect regular and steady changes in demand. A higher number is recommended for pools that expect infrequent and pronounced changes in the load.

• idletimeout

  The maximum amount in seconds that a connection is ensured to remain unused in the pool. Also the intervals at which the resizer task will be scheduled.

  Note that this does not control connection timeouts enforced at the database server side. Defaults to 300.

  Setting this attribute to 0 prevents the connections from being closed and causes the resizing task not to be scheduled. This is recommended for pools that expect continuous high demand. Otherwise, administrators are advised to keep this timeout shorter than the database server-side timeout (if such timeouts are configured on the specific vendor's database), to prevent accumulation of unusable connections in the pool.

• maxwaittime

  The amount of time in milliseconds that a request waits for a connection in the queue before timing out. Defaults to 60000.

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

• connectionvalidationrequired

  If set to true, the pool will always execute a call on the connection to verify its validity. Defaults to off.

  The overhead caused by this call can be avoided by setting the parameter to false.

• connectionvalidationmethod

  The method used for validation. Defaults to auto-commit.

  If validation is needed, the methods auto-commit and meta-data are less costly than the method table. The first two require a method call, but they might not be effective if the JDBC driver caches the result of the call. The third method is almost always effective, but it requires the execution of a SQL statement, and thus is less performance-friendly.

• validationtablename

  The user-defined table to be use for validation. Defaults to test.

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

• failallconnections

  Indicates whether all connection in the pool are re-created when one is found to be invalid or only the invalid one. Only applicable if connectionvalidationrequired is set to true. Defaults to off.

  If set to true, all of the re-creation work will be done in one step, and the thread requesting the connection will be heavily affected. If set to false, the load of re-creating connections will be distributed between the threads requesting each connection.

• transactionisolationlevel

  Specifies the Transaction Isolation Level on the pooled database connections. This setting is optional and has no default.

  If left empty, the default isolation level of the connection will be left intact. Setting it to any value will incur the small performance penalty cause by the method call.

• solationlevelguaranteed

  Only applicable if a transactionisolationlevel has been specified. Defaults to off.

  Leaving this as off or false will cause the isolation level to be set only when the connection is created. Setting this to true will set the level every time the connection is leased to an application. It is recommended that you leave this set to false.