Reducing JDBC connection pool sizes using WebSphere Application Server reserve pool

HCL Commerce servers normally require enough JDBC connections in the JDBC connection pool so that no HCL Commerce threads need to wait for a connection. However, a WebSphere Application Server iFeature allows HCL Commerce to run with smaller JDBC connection pools. The iFeature defines and uses a reserve pool of JDBC connections. Large HCL 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 HCL 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 HCL Commerce APAR JR43306.

Procedure

  1. Enable preallocation of a JDBC connection for each Transaction server thread that requires a database connection.
    Add the following configuration to the <InstanceProperties> element of the HCL Commerce configuration file:
    <com.ibm.commerce.server.TransactionManager preFetchJDBCConnection="true" />
    For more information about the HCL Commerce configuration file, see HCL Commerce configuration file (wc-server.xml).
  2. 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.
  3. 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.