Tomcat JDBC Connection Pool configuration for production and development
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.
Contents [hide]
The Tomcat Connection pool is configured as a resource described in The Tomcat JDBC documentationwith the only difference being that you have to specify the factory attribute and set the value toorg.apache.tomcat.jdbc.pool.DataSourceFactory. For Podcastpedia.org, it is configured in thecontext.xml file of the web application:
Production environment
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<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=“34”
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 startedmaxActive = 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 filemax_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 thanminEvictableIdleTimeMilliswill be releasedminIdle= 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 theremoveAbandonedTimeout. A connection is considered abandoned and eligible for removal if it has been in use longer than theremoveAbandonedTimeout. 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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<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:
|
1
2
3
4
5
6
7
8
9
10
|
After mysql server was restarted orconnection was lostIgot thiskind 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 iscom.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.
If you liked this, please show your support by helping us with Podcastpedia.org
We promise to only share high quality podcasts and episodes.
