Reducing JDBC connection pool sizes using WebSphere Application Server reserve pool

WebSphere Commerce servers normally require enough JDBC connections in the JDBC connection pool so that no WebSphere Commerce threads need to wait for a connection. However, a WebSphere Application Server iFeature allows WebSphere Commerce to run with smaller JDBC connection pools. The iFeature defines and uses a reserve pool of JDBC connections. Large WebSphere Commerce deployments with more than 100 application servers might benefit from the reserve pool because the reserve pool can reduce the database resource consumption associated with large numbers of JDBC connections.

Before you begin

Apply WebSphere Application Server feature FEF013660, which is available from WebSphere Commerce customer support, or install WebSphere Application Server fix pack 7.0.0.25 or later to your WebSphere Application Server instance.
Apply the interim fix for WebSphere Commerce APAR JR43306.

Procedure

Enable preallocation of a JDBC connection for each WebSphere Commerce Server thread that requires a database connection.
Add the following configuration to the <InstanceProperties> element of the WebSphere Commerce configuration file:
```
<com.ibm.commerce.server.TransactionManager preFetchJDBCConnection="true" />
```
For more information about the WebSphere Commerce configuration file, see Updating the WebSphere Commerce configuration file.
Define a WebSphere Application Server reserve pool.
Use the WebSphere Application Server administration console to define a connection pool custom property. Use the name numberOfPoolReserves for the custom property and set the value of the property to 1.
Determine the pool size that is appropriate for your environment.
Run performance tests with simulated workloads, measuring throughput and response times. Start with the number of JDBC connections you derive from the instructions for determining the data source connection pool size. From that starting point, run further performance tests, gradually reducing the connection pool size. Eventually you will see a performance degradation. When that happens, increase the pool size (for example, by 10% or 20%) to allow for peak load fluctuations.