Connection leak/spike during heavy load

[shaoxianyang]

Some parts of this message have been removed. Learn more about Nabble's security policy.

Hi,
 
I am observing connection leak/spike for a short period time when DB undergoes heavy load.
 
Let me describe all the relevant configuration first:
 
1. Software version:
 
MySQL 5.1.36,
Hibernate  3.2.5GA,
c3p0 0.9.1.2
 
 
2.Connection pool specified in Tomcat server,   /opt/tomcat/conf/context.xml.   This is a global connection pool
 
                  <Resource auth="Container" description="DB Connection"
                     driverClass="com.mysql.jdbc.Driver"
                     maxPoolSize="30" minPoolSize="30" 
                     acquireIncrement="1" maxIdleTime="1800" 
                     
                     
                     maxStatements="600" maxStatementsPerConnection="20"
                     
                     checkoutTimeout="20000"  unreturnedConnectionTimeout="15"
                     
                     
                     name="jdbc/ProdDS" 
                     user="pcs" password="pcs" 
                     factory="org.apache.naming.factory.BeanFactory"
                     type="com.mchange.v2.c3p0.ComboPooledDataSource"
      jdbcUrl="jdbc:mysql://cywindows2:3306/dev_catalog?autoConnect=true"/> 
 
 
 
3. In order to debug what went wrong, I add the following properties in pool configuration of Tomcat context.xml
 
                     debugUnreturnedConnectionStackTraces="true"
                     
                     automaticTestTable="connectionTest"
                     testConnectionOnCheckout="true"
                     testConnectionOnCheckin="true"
 
 These supposed to tell me the connection state when the connection is checked out, checked in.  And it reports to me whether any client is holding the connection for too long or never return it.
 
4.  In order to fully observe it, I also turn on the following logging level for c3p0:
 
com.mchange.v2&level=INFO
com.mchange.v2.c3p0.impl.DefaultConnectionTester&level=TRACE
com.mchange.v2.c3p0.impl.NewPooledConnection&level=TRACE
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool&level=TRACE
 
 
5.  We have a connection limit at DB level, but not at DB user/schema level.
 
 
When DB is under usual load, there is nothing related to connection leak (some client never return connection back to pool),  or connection spike (connection bump up to more than maxPoolSize).
 
However, when DB is under heavy load, in our case, there is some other heavy cron job running in the same DB server,  My DB monitoring process shows number of connections from a specific DB user and host is beyond maxPoolSize.   For mysql DB, connection statistic can be queryed, i.e:
 
mysql> select id, command, state, time, info from information_schema.processlist where user='pcs_user' and host like 'podcast%';
 
 
The most strange part of it is that it only exceed the maxPoolsize for a very short time, i.e. around 1 minute or so.
 
 
Based on the log and mysql connections statistics, I can tell the following:
 
1) DB was under very heavy load, every connection process can show the query it is executing.  That is unusal.   That somehows shows the DB is under heavy load.
 
2) a lot of ongoing transaction failed because connections are taken back by connection pool.   Maybe DB is slow, that is why any query takes more than 15 seconds to finish. 
 
3) At the same time, connection pool need to expand new connections for those it killed.
 
 
My question is:
1)  Is connection pool's forcibily killing connection an asynchronous process versus to opening new connection?   Does it just issue command to kill connection and not wait till it finishes before it opens new connection?  If so, it can explain why we are seeing the short term spike over maxPoolSize.
 
2) If 1) is true, is this a bug or something derived from the asynchorous design?
 
3) How do we avoid the connection spike which exceed maxPoolSize?
 
 
Thanks.  I look forward to your replies!
 
 
 
 
 
Shaoxian Yang
 
 

 

---

Windows Live: Keep your friends up to date with what you do online.
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
c3p0-users mailing list
c3p0-users@...
https://lists.sourceforge.net/lists/listinfo/c3p0-users