How to modify the size of the database connections pool

Objective

Modify the size of the database connections pool used by the Contrast application and modify the corresponding Maximum Connections that the database server can support.

Background

The Contrast application uses a pool of JDBC connections to its database, the size of which defaults to 50.  In addition, as of Version 3.6.9, an additional pool, also of 50 connections, is reserved for future use to improve reporting performance in later EOP versions.  As a result of these defaults, 100 connections (from 3.6.9 onwards) or 50 connections (3.6.8 and earlier) will be made to the database on startup by each Contrast Application Server.  In the case of a distributed installation with a single Contrast application server, the default MySQL database server Maximum Connections of 151 will be sufficient to accommodate the default pool sizes detailed above, however if multiple Contrast application server instances are deployed it will be necessary to modify either the pool size or the database server Maximum Connections to suit.

Process

To modify the Maximum Connections that the MySQL database server can support:

• Locate the my.cnf file on the MySQL database server (often in the /etc or /etc/mysql folder on Linux distributions).
• Edit my.cnf and set max_connections to a value that is larger than the combined connection pool sizes on each Contrast application server (for example, for 5 application servers each with a connection pool size of 50, then set max_connections to at least 251).
• Restart the database server.

To modify the size of the JDBC connection pool on a Contrast application server:

• • Use the Encrypted Properties Editor to edit the database.properties file as follows:
    • To change only the size of the non-reporting-related JDBC connections pool, add a property named jdbc.maxPoolSize (note that these are case-sensitive).
    • To change the size of the reporting-related JDBC connections pool, you must add a property named reporting.jdbc.maxPoolSize but also for each existing property in the file named jdbc.<propertyname> you must create a corresponding reporting.jdbc.<propertyname> property (so you will end up with reporting.jdbc.type, reporting.jdbc.url etc.)

Additional Info

The following queries may be useful in verifying the current configuration and status of the database connection pool:

To see the currently configured maximum allowed connections:

mysql> SHOW VARIABLES LIKE "max_connections";

To see the currently connected clients:

mysql> SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
    -> GROUP_CONCAT(DISTINCT user) AS users,
    -> COUNT(*) AS threads
    -> FROM information_schema.processlist
    -> GROUP BY host_short
    -> ORDER BY COUNT(*), host_short;