Get connection pool status using grails
I have a webservice built with grails that connects to a MySQL database. Since I upgraded to 2.4.3, I had problems with the connection not releasing connections, resulting in an exception:
org.apache.tomcat.jdbc.pool.PoolExhaustedException: [http-bio-8080-exec-216] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:50; busy:50; idle:0; lastwait:30000]
This is my Datasources.groovy
dataSource {
url = "jdbc:mysql://..."
username = "xxx"
password = "xxx"
pooled = true
properties {
maxActive = 50
maxAge = 10 * 60000
timeBetweenEvictionRunsMillis = 5000
minEvictableIdleTimeMillis = 60000
numTestsPerEvictionRun=3
testOnBorrow=true
testWhileIdle=true
testOnReturn=true
validationQuery="SELECT 1"
}
}
dataSource_survey {
url = "jdbc:mysql://..."
username = "xxx"
password = "xxx"
pooled = true
properties {
maxActive = 50
maxAge = 10 * 60000
timeBetweenEvictionRunsMillis = 5000
minEvictableIdleTimeMillis = 60000
numTestsPerEvictionRun=3
testOnBorrow=true
testWhileIdle=true
testOnReturn=true
validationQuery="SELECT 1"
}
}
I was reading the grails of JIRA and some people seem to be facing similar problems. But I was unable to fix it with the information provided there.
Accessing the connectionpool status will help you debug a lot. How can I check the status of a connection pool to see how many idle connections are busy / busy?
source to share
The connection pool is registered as javax.sql.DataSource
, but this interface only has getter methods Connection
(one with and without username / password), access to the write log, and get / set the login timeout. Everything else is left to the vendor to decide, and there is very little commonality between vendors in their methods for setting up pools initially, and for running and monitoring them while the application is running.
So, you really need to figure out which library is being used for the pool and use their API. This would ideally be as easy as accessing the dataSource
bean (this one is simple, just dependent - inject it into a service / controller / etc, like any bean - as a class field, in this case def dataSource
) and printing it class name. But we are moving the datasource to multiple proxies to add some important behaviors, so it is not easy to access
You're in luck - for such cases, we leave the original instance unsolicited and register it as a dataSourceUnproxied
bean, which you can also use instead (just don't access any of its connections, just info).
For a long time we used a shared pool to manage data sources, but a while ago we switched to Tomcat JDBC Pool as benchmarks showed it to be faster than any other they looked at (including C3P0) and its tuning methods are pool based communities, so it was a basic replacement with significant performance gains and more configuration.
source to share