CODEWITHSUNDEEP
mysqlgrailsconnection-pooling
user903772
user903772

Reputation: 1562

Grails Database connection pooling - load testing

I'm using Grails with MySQL. I implemented connection pooling in DataSource.groovy and I'm getting this error when load testing:

production {
   dataSource {
      driverClassName = "com.mysql.jdbc.Driver"
      dbCreate = "update" // one of 'create', 'create-drop','update'
      url = "jdbc:mysql://localhost:3306/mpest"
      username = "root"
      password = "Cos"
      pooled = true
      //pooling parameters
      properties {
         maxActive = 300
         minIdle = 1
         initialSize = 10 
         maxWait = 10000 
         testOnBorrow = true 
         testWhileIdle = true 
         testOnReturn = false 
         validationQuery = "SELECT 1"
      }
   }
}

But keep getting error.

2012-04-07 05:48:20,716 [http-bio-80-exec-120] ERROR StackTrace  - Sanitizing stacktrace:
org.hibernate.exception.JDBCConnectionException: could not inspect JDBC autocommit mode
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at org.hibernate.jdbc.JDBCContext.afterNontransactionalQuery(JDBCContext.java:275)
    at org.hibernate.impl.SessionImpl.afterOperation(SessionImpl.java:444)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1604)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
    at org.codehaus.groovy.grails.orm.hibernate.metaclass.FindByPersistentMethod$1.doInHibernate(FindByPersistentMethod.java:100)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
    at org.codehaus.groovy.grails.orm.hibernate.metaclass.FindByPersistentMethod.doInvokeInternalWithExpressions(FindByPersistentMethod.java:67)
    at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractClausedStaticPersistentMethod.doInvokeInternal(AbstractClausedStaticPersistentMethod.java:562)
    at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke(AbstractStaticPersistentMethod.java:64)
    at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke(AbstractStaticPersistentMethod.java:57)
    at org.codehaus.groovy.grails.commons.metaclass.StaticMethodInvocation$invoke$29.call(Unknown Source)
    at org.codehaus.groovy.grails.plugins.orm.hibernate.HibernatePluginSupport$_addDynamicFinderSupport_closure17.doCall(HibernatePluginSupport.groovy:466)
    at sun.reflect.GeneratedMethodAccessor560.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1058)
    at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1070)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:886)
    at groovy.lang.Closure.call(Closure.java:282)
    at org.codehaus.groovy.runtime.metaclass.ClosureStaticMetaMethod.invoke(ClosureStaticMetaMethod.java:59)
    at groovy.lang.MetaClassImpl.invokeStaticMissingMethod(MetaClassImpl.java:1355)
    at groovy.lang.MetaClassImpl.invokeStaticMethod(MetaClassImpl.java:1343)
    at groovy.lang.ExpandoMetaClass.invokeStaticMethod(ExpandoMetaClass.java:1082)
    at org.codehaus.groovy.runtime.callsite.StaticMetaClassSite.call(StaticMetaClassSite.java:50)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.StaticMetaClassSite.call(StaticMetaClassSite.java:55)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:124)
    at org.mPest.ReportManagement.addReport(ReportManagement.groovy:14)
    at org.mPest.ReportManagement$addReport$55.call(Unknown Source)
    at org.mPest.ReportController$_closure1.doCall(ReportController.groovy:12)
    at sun.reflect.GeneratedMethodAccessor505.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSite.invoke(PogoMetaMethodSite.java:225)
    at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite.callCurrent(PogoMetaMethodSite.java:51)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:149)
    at org.mPest.ReportController$_closure1.doCall(ReportController.groovy)
    at sun.reflect.GeneratedMethodAccessor490.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:90)
    at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:233)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1058)
    at groovy.lang.ExpandoMetaClass.invokeMethod(ExpandoMetaClass.java:1070)
    at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:886)
    at groovy.lang.Closure.call(Closure.java:282)
    at groovy.lang.Closure.call(Closure.java:277)
    at org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.handleAction(SimpleGrailsControllerHelper.java:368)
    at org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.executeAction(SimpleGrailsControllerHelper.java:232)
    at org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.handleURI(SimpleGrailsControllerHelper.java:190)
    at org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsControllerHelper.handleURI(SimpleGrailsControllerHelper.java:129)
    at org.codehaus.groovy.grails.web.servlet.mvc.SimpleGrailsController.handleRequest(SimpleGrailsController.java:73)
    at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
    at org.codehaus.groovy.grails.web.servlet.GrailsDispatcherServlet.doDispatch(GrailsDispatcherServlet.java:292)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:684)
    at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:471)
    at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:402)
    at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:329)
    at org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:298)
    at org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:264)
    at org.codehaus.groovy.grails.web.util.WebUtils.forwardRequestForUrlMappingInfo(WebUtils.java:255)
    at org.codehaus.groovy.grails.web.mapping.filter.UrlMappingsFilter.doFilterInternal(UrlMappingsFilter.java:183)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.codehaus.groovy.grails.web.sitemesh.GrailsPageFilter.obtainContent(GrailsPageFilter.java:245)
    at org.codehaus.groovy.grails.web.sitemesh.GrailsPageFilter.doFilter(GrailsPageFilter.java:134)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.codehaus.groovy.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:69)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.codehaus.groovy.grails.web.filters.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:65)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:579)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.GeneratedConstructorAccessor139.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    at sun.reflect.GeneratedConstructorAccessor131.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1158)
    at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:224)
    at $Proxy6.getAutoCommit(Unknown Source)
    at org.hibernate.jdbc.ConnectionManager.isAutoCommit(ConnectionManager.java:212)
    at org.hiber

The following tells me there's Too many connection

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.GeneratedConstructorAccessor139.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    at sun.reflect.GeneratedConstructorAccessor131.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
    at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1158)
    at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
    at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:224)
    at $Proxy6.getAutoCommit(Unknown Source)
    at org.hibernate.jdbc.ConnectionManager.isAutoCommit(ConnectionManager.java:212)
    at org.hibernate.jdbc.JDBCContext.afterNontransactionalQuery(JDBCContext.java:266)

Upvotes: 2

Views: 3698

Answers (2)

Chris
Chris

Reputation: 8109

You have reached the maximum number of MySQL connections. If your physical database has reached its capacity, then grails cannot do anything for you.

The default number of active connections is 150 for MySQL. You need to set maximum_connections in your my.cnf to 300, as you have configured in your pooling properties.

If you have, double check, that the pooling parameters are correctly taken, e.g. using netstat -anp | grep 3306, while your load test is running on your database machine.

Upvotes: 1

Burt Beckwith
Burt Beckwith

Reputation: 75671

I'd say this is your issue:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"

Can you connect with a MySQL client (either GUI or commandline) using this connect information? Make sure you can connect in general outside of Grails, then configure Grails to use the database.

As @rlovtang said in his comment, it's a good idea to specify the dialect. It's especially important with MySQL because of the MyISAM/InnoDB thing, but if you specify it Grails doesn't need to inspect the connection metadata to figure out what database you're using. Although in this case if you can't get a connection to figure out the dialect, you'll certainly have problems doing persistence in general.

Upvotes: 2

Related Questions