Databases
StackOverflow2013
Postsdbo
POSpring, Hibernate, MySQL stress test
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpring, Hibernate, MySQL stress test
    text
    copied!<p>While stress testing my web application, it seems <a href="http://en.wikipedia.org/wiki/Hibernate_%28Java%29" rel="nofollow">Hibernate</a> can no longer create a connection to the database.</p> <p>The web application is developed using <a href="http://en.wikipedia.org/wiki/Spring_Framework" rel="nofollow">Spring</a> 3.0, Hibernate 3.6, and c3p0 0.9.2.1 connection pooling. It runs under <a href="http://en.wikipedia.org/wiki/Apache_Tomcat" rel="nofollow">Tomcat</a> 7. The DBMS is MySQL Server 5.5. All the transaction are managed by Spring via the <code>@Transactional</code> annotation.</p> <p>The following are some of the settings of Hibernate/c3p0</p> <pre><code> &lt;property name="acquireIncrement" value="5" /&gt; &lt;property name="initialPoolSize" value="15" /&gt; &lt;property name="minPoolSize" value="10" /&gt; &lt;property name="maxPoolSize" value="75" /&gt; &lt;property name="maxStatements" value="100" /&gt; &lt;property name="maxIdleTime" value="600" /&gt; &lt;property name="checkoutTimeout" value="2500" /&gt; &lt;property name="autoCommitOnClose" value="true" /&gt; &lt;bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"&gt; &lt;property name="sessionFactory" ref="sessionFactory" /&gt; &lt;property name="defaultTimeout" value="15" /&gt; &lt;/bean&gt; </code></pre> <p>This is the JDBC connection URL:</p> <pre><code>jdbc.url=jdbc:mysql://myserver:myport/mydb?connectTimeout=31000&amp;socketTimeout=30000 </code></pre> <p>When I reach around 200 concurrent users, the CPU load goes near 100% (but the connection pool seems still under 75) and the following exception occurs:</p> <pre><code> SEVERE: Cannot connect to database. org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransa ctionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connec tion at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789) at javax.servlet.http.HttpServlet.service(HttpServlet.java:647) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.my-company.SimpleConnetionLogFilter.doFilter(SimpleConnetionLogFilter.java:132) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) 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:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:200) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310) 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: org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org .hibernate.exception.GenericJDBCException: Cannot open connection at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622) at com.my-company.bizlogic.spring.XService$$EnhancerByCGLIB$$4e6a04f7.digest(&lt;generated&gt;) at com.my-company.XController.handleRequest(XController.java:55) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) ... 24 more Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:160) at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:81) at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:551) ... 35 more Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out. at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118) at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:687) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446) ... 39 more Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@7624a28d -- timeout at awaitAvailable() at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1416) at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606) at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682) ... 42 more </code></pre> <p>What I clearly do not understand yet is what happens when all the 75 connections in the pool are used?</p> <p>I thought that the next transaction will wait for one free connection.</p> <p>And since I have set the <em>defaultTimeout</em> for transactions I expected this to happen in this case. This kind of error will be managed in the application and the client will know that the server is busy. But currently a generic "Server Internal Error" is generated, because on the Tomcat side it seems like MySQL is no more reachable, not just busy.</p> <p>Moreover, am I missing something in the settings? I have already tried to raise some of the Hibernate/c3p0 and JDBC parameters, but the only effect is that I get a different Exception, always related to the JDBC connection.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload