Tomcat JDBC Connection Pool configuration for production and development

(P) Codever is an open source bookmarks and snippets manager for developers & co. See our How To guides to help you get started. Public bookmarks repos on Github ⭐🙏

As mentioned in the post Install Eclipse Kepler 64 bit on Windows 7 64 bit, Podcastpedia.org uses Apache Tomcat 7 as application server. This post presents how the Tomcat JDBC Connection Pool is configured in development and production for Podcastpedia.org. The used database is MySql.

The Tomcat Connection pool is configured as a resource described in The Tomcat JDBC documentation with the only difference being that you have to specify the factory attribute and set the value to org.apache.tomcat.jdbc.pool.DataSourceFactory. For Podcastpedia.org, it is configured in the context.xml file of the web application:

Production environment

  
    <Resource
    	  name="jdbc/pcmDB"
    	  auth="Container"
    	  type="javax.sql.DataSource"
    	  factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    	  initialSize="34"
    	  maxActive="377"
    	  maxIdle="233"
    	  minIdle="89"
    	  timeBetweenEvictionRunsMillis="34000"
    	  minEvictableIdleTimeMillis="55000"
    	  validationQuery="SELECT 1"
    	  validationInterval="34000"
    	  testOnBorrow="true"
    	  removeAbandoned="true"
    	  removeAbandonedTimeout="55"
    	  username="xxx"
    	  password="yyy"
    	  driverClassName="com.mysql.jdbc.Driver"
    	  url="jdbc:mysql://localhost:3306/pcmdb?allowMultiQueries=true"
     />
   
 

Sizing the conection pool

• initialSize = 34 – the initial number of connections that are created when the pool is started
• maxActive = 377 – the maximum number of active connections that can be allocated from this pool at the same time. This attribute is used to limit the number of connections a pool can have open so that capacity planning can be done on the database side – in my.cnf MySql configuration file max_connections = 610 (maxActive+maxIdle)
• maxIdle = 233 – the maximum number of idle connections that should be kept in the pool at all times. Idle connections are checked periodically (if enabled) and connections that have been idle for longer than minEvictableIdleTimeMillis will be released
• minIdle= 89 – the minimum number of established connections that should be kept in the pool at all times. The connection pool can shrink below this number if validation queries fail.
• timeBetweenEvictionRunsMillis = 34000 – the number of milliseconds to sleep between runs of the idle connection validation/cleaner thread. This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often we validate idle connections.
• minEvictableIdleTimeMillis = 55000 – the minimum amount of time an object may sit idle in the pool before it is eligible for eviction.

Validate connections

At first I avoided to configure connection validation, as I thought it would have an impact on performance. But several problems forced me to activate that. With the following configuration, connections are validated, but no more than every 34 seconds:

• testOnBorrow = true – by setting this, the objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE – for a true value to have any effect, the validationQuery parameter must be set to a non-null string.
• validationInterval = 34000 – used to avoid excess validation, only run validation at most at this frequency – time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The larger the value, the better the performance, but you increase the chance of a stale connection being presented to your application.
• validationQuery= "SELECT 1" – MySql SQL query used to validate connections from the pool before returning them to the caller

Connection leaks

There are several configuration settings to help detect connection leaks:

• removeAbandoned = true – Flag to remove abandoned connections if they exceed the removeAbandonedTimeout. A connection is considered abandoned and eligible for removal if it has been in use longer than the removeAbandonedTimeout. This way db connections can be recovered from applications that fail to close a connection.
• removeAbandonedTimeout = 54 – timeout in seconds before an abandoned(in use) connection can be removed. The value should be set to the longest running query your applications might have.
• validationQuery= "SELECT 1" – MySql SQL query used to validate connections from the pool before returning them to the caller

The validation/cleaner thread

timeBetweenEvictionRunsMillis > 0 AND removeAbandoned=true AND removeAbandonedTimeout > 0 means the pool sweeper is enabled. The pool sweeper is the background thread that can test idle connections and resize the pool while the pool is active. The sweeper is also responsible for the detection of connection leaks. In this case the number of idle connections can grow beyond maxIdle, but can shrink down to minIdle if the connection has been idle for longer than minEvictableIdleTimeMilis.

Development environment

  
    <Resource
    	 name="jdbc/pcmDB"
    	 auth="Container"
    	 type="javax.sql.DataSource"
    	 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    	 initialSize="5"
    	 maxActive="55"
    	 maxIdle="21"
    	 minIdle="13"
    	 timeBetweenEvictionRunsMillis="34000"
    	 minEvictableIdleTimeMillis="55000"
    	 validationQuery="SELECT 1"
    	 validationInterval="34"
    	 testOnBorrow="true"
    	 removeAbandoned="true"
    	 removeAbandonedTimeout="233"
    	 username="xxx"
    	 password="yyy"
    	 driverClassName="com.mysql.jdbc.Driver"
    	 url="jdbc:mysql://localhost:3307/pcmDB?allowMultiQueries=true"
    />
  

 The development environment configuration is just a copy of the configuration used in production, with smaller values for attributes to size the pool, and bigger values for attributes to determine leaked connection, so that I can be in debug mode longer.

Watch out for

One of the exceptions I have got was:

  
    After mysql server was restarted or connection was lost I got this kind of errors:
    org.springframework.dao.DataAccessResourceFailureException:
    ### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
    ### The error may exist in maps/PodcastMapper.xml
    ### The error may involve org.podcastpedia.dao.PodcastDao.getTopRatedPodcasts
    ### The error occurred while executing a query
    ### SQL: SELECT      p.podcast_id,      p.url,   (select sum(rating) / count(rating) from ratings    where podcast_id = p.podcast_id and episode_id=-1) as podcast_rating,   (select count(rating)  from ratings      where podcast_id = p.podcast_id and episode_id=-1) as podcast_number_ratings,      p.number_visitors,      p.description,      p.short_description,      p.podcast_image_url,      p.title,      p.last_episode_url,      p.title_in_url,      p.publication_date       FROM      podcasts p     WHERE      p.availability=200     ORDER BY podcast_rating DESC      limit 0, ?;
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
    ; SQL []; No operations allowed after connection closed.Connection was implicitly closed by the driver.;
     nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
   
 

 This was solved by introducing the validation attributes mentioned above.

Well that’s it… Thanks again to the open source community for developing Tomcat, and a special thank you to Filip Hanik for explaining the JDBC- pool configuration so clearly.

If you notice any room for improvement, please contact us or leave a message.

Resources

• Apache Tomcat
• The Tomcat JDBC Connection Pool
• Apache Tomcat 7 – JNDI Datasource HOW-TO
• Using the Tomcat 7 JDBC Connection Pool in Production
• Configuring jdbc-pool for high-concurrency

Adrian Matei

Creator of Podcastpedia.org and Codepedia.org, computer science engineer, husband, father, curious and passionate about science, computers, software, education, economics, social equity, philosophy - but these are just outside labels and not that important, deep inside we are all just consciousness, right?