I am developing a web application using Spring MVC + MySQL. To control the JDBC connection, I used c3p0 to pool pools.
If I use c3p0, I assume that if 5 connections are open in the pool and all 5 connections are in sleep mode, and if I request getConnection() from java, then one of them should be returned as Connection . Am I right?
If so, then consider the following stress testing scenario when I get a Connections could not be acquired from the underlying database! exception Connections could not be acquired from the underlying database! . Using Jmeter , I started a load test for 250 users in 15 seconds. I constantly monitor DB connections using SHOE FULL PROCESSLIST for the first few minutes everything is going well, but since all 250 users who got into the count of web server connections are reached up to 500, which is our maxPoolSize . So after that we get Connections could not be acquired from the underlying database! .
At this point, if I am doing SHOE FULL PROCESSLIST , then I can see that all 500 connections are in sleep mode. If I correctly formulated above, any open connection in sleep mode will be returned by c3p0. Then why am I getting this exception.?
Here are my c3p0 properties
MINPOOLSIZE=10 ACQUIREINCREMENT=1 MAXPOOLSIZE=500 INITIALPOOLSIZE=10 NUMBERHELPERTHREAD=100 MAXIDLETIME=10 MAXSTATEMENT=20 MAXSTATEMENTPERCONNECTION=5 IDLECONNECTIONTESTPERIOD=120 ACQUIRERETRYATTEMPT=10 ACQUIRERETRYDELAY=100 AUTOCOMMITONCLOSE=false BREAKAFTERACQUIREFAILURE=false TESTCONNECTIONONCHECKOUT=true TESTCONNECTIONONCHECKIN=true
Update
I found this warning before Connections could not be acquired from the underlying database! An exception
WARN : 30 Jun 2014 10:40:12.078 com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1839) - com .mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@ e49561 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (10). Last acquisition attempt exception: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.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:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2483) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2516) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2301) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) at sun.reflect.GeneratedConstructorAccessor18.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:411) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:183) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:172) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:188) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1074) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1061) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1798) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:636) WARN : 30 Jun 2014 10:40:12.078 com.mchange.v2.resourcepool.BasicResourcePool.forceKillAcquires(BasicResourcePool.java:882) - Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@1035ff9 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.
Amogh source share