Grails 2.3.4 The pool is empty. Failed to get connection in 30 seconds.

Question

We have a grails application crashing in tomcat 7.0.30. The Grails version was 2.2.4, and it has been very stable over the past year. I tried switching the version of Grails to 2.3.4, in the test environment it worked fine (no problem). But when I put it into production in 20 minutes, I started getting the following exceptions

[ajp-bio-9009-exec-430] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000].. Stacktrace follows: org.apache.tomcat.jdbc.pool.PoolExhaustedException: [ajp-bio-9009-exec-430] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000]. at grails.gorm.DetachedCriteria$_count_closure4.doCall(DetachedCriteria.groovy:686) at grails.gorm.DetachedCriteria$_withPopulatedQuery_closure10.doCall(DetachedCriteria.groovy:931) at org.grails.datastore.gorm.GormStaticApi$_withDatastoreSession_closure20.doCall(GormStaticApi.groovy:680) at org.grails.datastore.mapping.core.DatastoreUtils.execute(DatastoreUtils.java:302) at org.grails.datastore.gorm.AbstractDatastoreApi.execute(AbstractDatastoreApi.groovy:37) at org.grails.datastore.gorm.GormStaticApi.withDatastoreSession(GormStaticApi.groovy:679) at grails.gorm.DetachedCriteria.withPopulatedQuery(DetachedCriteria.groovy:913) at grails.gorm.DetachedCriteria.count(DetachedCriteria.groovy:684) at grails.gorm.DetachedCriteria.count(DetachedCriteria.groovy:683) at com.webbfontaine.wftaglib.BeanDataLoadController.doLoadData(BeanDataLoadController.groovy:30) at com.webbfontaine.wftaglib.BeanDataLoadController$_closure1.doCall(BeanDataLoadController.groovy:14) at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:195) at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63) at org.josso.tc70.agent.SSOAgentValve.invoke(SSOAgentValve.java:684) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722) 

In our application, all database queries are executed through Grails GORM, we do not use SQL or HQL query.

I assume that something is wrong in Grails 2.3.4 itself (some connections are not closed / returned to the pool correctly).

Am I missing something or is his problem with the grails? Any ideas?

Answer 1

Grails 2.3.x replaced Commons DBCP with Tomcat JDBC , this could be the reason for the difference in your application. You can check the differences in the pool configuration.

Answer 2

There are several errors in Grails in the default dataSource settings . These issues will be fixed in Grails 2.3.6.

Here is an example of optimized dataSource parameters for MySQL

 dataSource { pooled = true dbCreate = "update" url = "jdbc:mysql://localhost:3306/my_database" driverClassName = "com.mysql.jdbc.Driver" dialect = org.hibernate.dialect.MySQL5InnoDBDialect username = "username" password = "password" properties { // Documentation for Tomcat JDBC Pool // http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes // https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/PoolConfiguration.html jmxEnabled = true initialSize = 5 maxActive = 50 minIdle = 5 maxIdle = 25 maxWait = 10000 maxAge = 10 * 60000 timeBetweenEvictionRunsMillis = 5000 minEvictableIdleTimeMillis = 60000 validationQuery = "SELECT 1" validationQueryTimeout = 3 validationInterval = 15000 testOnBorrow = true testWhileIdle = true testOnReturn = false ignoreExceptionOnPreLoad = true // http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#JDBC_interceptors jdbcInterceptors = "ConnectionState;StatementCache(max=200)" defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED // safe default // controls for leaked connections abandonWhenPercentageFull = 100 // settings are active only when pool is full removeAbandonedTimeout = 120 removeAbandoned = true // use JMX console to change this setting at runtime logAbandoned = false // causes stacktrace recording overhead, use only for debugging // JDBC driver properties // Mysql as example dbProperties { // Mysql specific driver properties // http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html // let Tomcat JDBC Pool handle reconnecting autoReconnect=false // truncation behaviour jdbcCompliantTruncation=false // mysql 0-date conversion zeroDateTimeBehavior='convertToNull' // Tomcat JDBC Pool StatementCache is used instead, so disable mysql driver cache cachePrepStmts=false cacheCallableStmts=false // Tomcat JDBC Pool StatementFinalizer keeps track dontTrackOpenResources=true // performance optimization: reduce number of SQLExceptions thrown in mysql driver code holdResultsOpenOverStatementClose=true // enable MySQL query cache - using server prep stmts will disable query caching useServerPrepStmts=false // metadata caching cacheServerConfiguration=true cacheResultSetMetadata=true metadataCacheSize=100 // timeouts for TCP/IP connectTimeout=15000 socketTimeout=120000 // timer tuning (disable) maintainTimeStats=false enableQueryTimeouts=false // misc tuning noDatetimeStringSync=true } } } 

Answer 3

I would first check if there are threads that are "stuck" and support the db connection. You can see this from the JVM threaddump.

You can dump the stream in unix by sending a SIGQUIT (3) signal to the PID of the Java process. You can use the kill -3 PID command to do this. Threaddump is sent to stdout (which goes by catina.out by tomcat by default). It will not complete the Java process, so you can usually use this method in production environments.

Another way to get threaddump is to use the jstack PID command.

Usually it is worth making several subsequent dumps in a few seconds. Thus, you can distinguish between dumps to see what changes and what remains unchanged. Usually you have to do different things.