Avoiding database connection pool exhaustion

The xMatters web application is configured to use a pool of database connections to provide significant benefits in terms of application performance, concurrency, and scalability. If the resource pool is not recycled in a timely fashion, application processes that depend on these resources can starve, resulting in the web application becoming unresponsive. The following article explains how to configure and monitor the database pool connection to prevent exhaustion.

Database Connection Pooling

Database connections are often expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. In turn, connection session initialization often requires time-consuming processing to perform user authentication, establish transactional contexts, and establish other aspects of the session that are required for subsequent database usage.
Additionally, the database's ongoing management of its connection sessions can impose a major limiting factor on the scalability of your application. Valuable database resources such as locks, memory, cursors, transaction logs, statement handles, and temporary tables all tend to increase based on the number of concurrent connection sessions.
Resource pooling can often provide significant benefits in terms of application performance, concurrency and scalability. The xMatters web application is configured to use a pool of database connections for this reason.

Exhaustion

A pool of resources usually indicates that the limited number of resources available are recycled back to the application when they are not being used. If these resources are not recycled in a timely fashion, then application processes that depend on these resources can starve. This can produce indeterminate behaviors; in the case of the xMatters web application, it may become unresponsive.

Monitoring

You can configure the xMatters web application to output informational log messages for the connection pool by modifying the <xMHOME>/webserver/webapps/cocoon/WEB-INF/classes/log4j.xml file.

1. In the log4j.xml file, locate the following entry:

1. <logger name="com.mchange">  
2.  <level value="WARN"/>  
3.  <appender-ref ref="COCOON_CONNECTION_POOL" />  
4.  </logger>  

2. Change the entry to the following:

1. <logger name="com.mchange">  
2.  <level value="DEBUG"/>  
3.  <appender-ref ref="COCOON_CONNECTION_POOL" />  
4.  </logger>  

3. Save and close the file.

The resulting log file is called connections.log, and is created in the webserver/webapps/cocoon/WEB-INF/logs directory. To monitor the connection pool resources, examine the connections.log and look for the following statements:

1. DEBUG resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@22402240 [managed: 20, unused: 20, excluded: 0]  
2. DEBUG resourcepool.BasicResourcePool - acquire test -- pool is already maxed out. [managed: 100; max: 100]  

For this example, assume the connection pool configuration specifies a maximum size of 100 and a minimum size of 20. The first statement identifies the state of the connection pool. The keyword "managed" indicates how many connections are allocated in the pool. This will start at 20 and grow to 100. The second keyword, "unused", indicates how many of the managed connections are available. When this value reaches 0, new connections are created. The second statement is logged when managed equals 100 and unused equals 0.
To monitor this, you can look for a value lower than 100. For instance, when the server reaches 90 managed connections and 0 unused the system is reaching the maximum. It may take about 30 to 60 minutes for the system to reach max, depending on load.

Work Around

The connection pool configuration provides two special properties that can help diagnose the problem while keeping the application responsive:

• unreturnedConnectionTimeout: Specifies the number of seconds to automatically close connections if your program fails to do so within 10 seconds of checkout. Setting a long unreturnedConnectionTimeout does little harm.
• debugUnreturnedConnectionStackTraces: Specifies a boolean value to log stack traces of the connection checkouts that eventually failed to be checked in. Pay careful attention to these stack traces since they indicate a code path where the connections were used by the application, but not always closed reliably. This logging will slow down the system; disable the logging after you have finished resolving any leaks.

To implement the configuration changes:

1. Open the c3p0-config.xml file, located in the <xMHOME>/webserver/webapps/cocoon/WEB-INF/classes folder.
2. At the end of the c3p0-config.xml file, change the following property:

1. <property name="debugUnreturnedConnectionStackTraces">false</property>  

To:

1. <property name="debugUnreturnedConnectionStackTraces">true</property>  

3. Restart the web server.

To monitor the stack trace logging, look for a message that contains "Logging the stack trace by which the overdue resource was checked-out."