Tuning Connection Pool for production system

The most import tuning point of Tomcat is connection pool, because most service failures come between WAS and DBMS. Furthermore, tuning connection pool has the biggest impact on increasing WAS throughput.

Tomcat has changed it’s connection pool implementation from Apache DBCP to internal connection pool. (From Tomcat 7 both options are available) You can check dbcp documentation and new connection pool. The new connection pool supports more advanced options which are very important for production system. The following explanation is based on the new connection pool.

Connection pool config example (context.xml)

connection_pool_context.xml

  • New connection pool is enabled by setting factory attribute as org.apaqche.tomcat.jdbc.pool.DataSourceFactory. You can check full parameters here.

Important parameters for performance

  • initalSize : When Tomcat starts, it makes initial connections using this param. This param is important if requests burst in when server starts
  • maxActive : Max connections that Tomcat can make. If more requests come in than maxActive, then they must wait until maxWait seconds. I usually set this value equal to max threads
  • minIdle : After a connection is returned to connection pool, it can be released, but until minIdle number. I usually set minIdle equal to initialSize
  • maxIdle : Idle connections which are above maxIdle are released
  • minEvictableIdleTimeMillis : Connections are marked as idle after it is returned to connection pool and minEvictableIdleTimeMillis has passed

Importat parameters for availability

Connection health check is important if 1) every transaction is critical or 2) network between Tomcat and DBMS is unstable or 3) DBMS is unstable. If invalid connection is returned to web application, the transaction fails with broken pipe. For example the following error occurs when MySQL restarts and invalid connection is accessed, which is created before MySQL shutdown.

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

  • validationQuery : The sql which is run for test. This is applied to all testX options
  • testOnBorrow : Test when DataSource.getConnection() is called
  • testOnReturn : Test when Connection.close() is called
  • testWhileIdle : Test after a connection is returned to connection pool and while idle. Checking interval is timeBetweenEvictionRunsMillis
  • timeBetweenEvictionRunsMillis : This is effective when testWhileIdle is true. If a invalid connection before timeBetweenEvictionRunsMillis is accessed by a program, it fails
  • validationInterval : Test is not done before validationInterval has not passed after last test. This overrides every testX options. For example, validationInterval is 10,000 (10 seconds) and testOnBorrow is true and pooled connections are accessed several times within 1 second, then only the first connection is tested

Too frequent test causes database overhead. But too long interval between tests can cause service failure because of invalid connection. Especially validationInterval (for every testX) and timeBetweenEvictionRunsMillis (for testWhileIdle) are important tuning point between overhead and stability.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.