Re: [c3p0-users] a lot of threads waiting for a GlobalMaxOnlyStatementCache

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Alexander,

It sounds like you are giving the Statement cache a workout!

Two suggestions:

1) Right now you have set only maxStatements (and left maxStatementsPerConnection alone). To be _certain_ to avoid any bottleneck at Statement acquisition, maxStatements must be [maxPoolSize x numberOfPreparedStatementsInApp]. This is an upper limit of what is necessary; your app probably contains PreparedStatements that are used very infrequently, and isn't always under full load, etc. But in the continuum from that upper limit down to maxStatements = 1, you are trading off the memory/resource footprint of holding open Statements against the performance cost of wait()ing for cached Statements. It looks like you should tolerate quite a bit more footprint from open Statements, that is, set maxStatements much, much higher than it presently is.

2) Consider setting maxStatements to zero, and using maxStatementsPerConnection instead. Set maxStatementsPerConnection to the number of PreparedStatements in frequent use by your app, and c3p0 will scale the limit according to the number of Connections in the pool. IMHO, this is a much more elegant way of specifying a Statement cache resource limit, and ought to have been the JDBC spec parameter rather than maxStatements. c3p0 supports setting either parameter or both; if both are set a per-Connection limit is subject to a global limit as well. I'd recommend just using maxStatementsPerConnection: it's easy to understand, and in practice I've rarely received complaints about the memory/resource footprint of the Statement cache.

3) If you have not, please do upgrade to the most recent release [currently c3p0-0.9.2-pre4]. Although not directly related to the issues you describe, the c3p0-0.9.2 series contains significant improvements to the Statement cache, especially a means of preventing deadlocks arising from JDBC drivers that are fragile with respect to concurrent use of Connections. (In theory Connections must be robust to concurrent use while all of Connection's children need not be; in practice several drivers do not tolerate any concurrent use of Connections well.)

4) If you need to follow up, please provide more information about your config. In particular, it's a good idea to paste in the config information c3p0 dumps into logs at INFO level on pool init.

For more information on Statement cache configuration, please see http://www.mchange.com/projects/c3p0/#configuring_statement_pooling

Good luck!

    smiles,
        Steve

On Jun 28, 2012, at 3:50 PM, Alexander Shutyaev wrote:

> Hi all!
> 
> I ran into problem in my application. The threaddump shows that there are 111 threads (almost 30%) are waiting for a monitor com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache and 35 threads are locking this monitor. Below are typical stacktraces of a locking and waiting thread. My application has a lot of users and db load is very high, so I guess I need to adjust some settings in hibernate, but I can't figure out which ones.
> 
> Can anyone help? Thanks in advance
> 
> locking thread stacktrace:
> Code:
> "http-9090-Processor43" daemon prio=10 tid=0x00007fd7007c9800 nid=0x173f in Object.wait() [0x00007fd6f8e35000]
>    java.lang.Thread.State: BLOCKED (on object monitor)
>    at java.lang.Object.wait(Native Method)
>    at java.lang.Object.wait(Object.java:502)
>    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.acquireStatement(GooGooStatementCache.java:552)
>    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkoutStatement(GooGooStatementCache.java:168)
>    - locked <0x0000000683d90348> (a com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache)
>    at com.mchange.v2.c3p0.impl.NewPooledConnection.checkoutStatement(NewPooledConnection.java:234)
>    - locked <0x000000068abfeeb8> (a com.mchange.v2.c3p0.impl.NewPooledConnection)
>    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:199)
>    - locked <0x000000079d2b3e98> (a com.mchange.v2.c3p0.impl.NewProxyConnection)
>    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
>    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
>    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
>    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
>    at org.hibernate.loader.Loader.doQuery(Loader.java:696)
>    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
>    at org.hibernate.loader.Loader.doList(Loader.java:2228)
>    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
>    at org.hibernate.loader.Loader.list(Loader.java:2120)
>    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
>    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
>    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
>    at com.gridnine.xtrip.server.storage.SearchQueryHelper.execute(SearchQueryHelper.java:76)
>    at com.gridnine.xtrip.server.storage.EntityStorage.search(EntityStorage.java:82)
>    at com.gridnine.xtrip.server.storage.EntityStorage.getModifications(EntityStorage.java:701)
>    at com.gridnine.xtrip.server.rpc.service.EntityServiceImpl.getModifications(EntityServiceImpl.java:215)
>    at sun.reflect.GeneratedMethodAccessor110.invoke(Unknown Source)
>    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>    at java.lang.reflect.Method.invoke(Method.java:616)
>    at org.jboss.remoting.transporter.TransporterHandler.invoke(TransporterHandler.java:87)
>    at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:908)
>    at org.jboss.remoting.transport.servlet.ServletServerInvoker.processRequest(ServletServerInvoker.java:309)
>    at org.jboss.remoting.util.ServletSecurityUtility.processRequest(ServletSecurityUtility.java:53)
>    at org.jboss.remoting.transport.servlet.web.ServerInvokerServlet.processRequest(ServerInvokerServlet.java:128)
>    at com.gridnine.xtrip.server.web.rpc.RPCServlet.processRequest(RPCServlet.java:50)
>    at org.jboss.remoting.transport.servlet.web.ServerInvokerServlet.doPost(ServerInvokerServlet.java:157)
>    at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
>    at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
>    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
>    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
>    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
>    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
>    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
>    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
>    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
>    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
>    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
>    at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
>    at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
>    at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
>    at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
>    at java.lang.Thread.run(Thread.java:679)
> 
> waiting thread stractrace:
> Code:
> "pool-315-thread-3" prio=10 tid=0x00007fd708a39800 nid=0x2845 waiting for monitor entry [0x00007fd6e16d5000]
>    java.lang.Thread.State: BLOCKED (on object monitor)
>    at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkoutStatement(GooGooStatementCache.java:158)
>    - waiting to lock <0x0000000683d90348> (a com.mchange.v2.c3p0.stmt.GlobalMaxOnlyStatementCache)
>    at com.mchange.v2.c3p0.impl.NewPooledConnection.checkoutStatement(NewPooledConnection.java:234)
>    - locked <0x00000007475304a0> (a com.mchange.v2.c3p0.impl.NewPooledConnection)
>    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:199)
>    - locked <0x00000007a26a33b0> (a com.mchange.v2.c3p0.impl.NewProxyConnection)
>    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
>    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
>    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
>    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
>    at org.hibernate.loader.Loader.doQuery(Loader.java:696)
>    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
>    at org.hibernate.loader.Loader.loadEntity(Loader.java:1881)
>    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:71)
>    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:65)
>    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3072)
>    at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:434)
>    at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:415)
>    at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:165)
>    at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:223)
>    at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:126)
>    at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:905)
>    at org.hibernate.impl.SessionImpl.get(SessionImpl.java:842)
>    at org.hibernate.impl.SessionImpl.get(SessionImpl.java:835)
>    at com.gridnine.xtrip.server.storage.EntityStorage.findData(EntityStorage.java:799)
>    at com.gridnine.xtrip.server.storage.EntityStorage.save(EntityStorage.java:256)
>    at com.gridnine.xtrip.server.storage.EntityStorage.save(EntityStorage.java:227)
>    at com.gridnine.xtrip.server.model.export.service.ExportDocumentsHelper.processDocuments(ExportDocumentsHelper.java:279)
>    at com.gridnine.xtrip.server.model.export.service.ProcessExportDocumentsService$1.run(ProcessExportDocumentsService.java:146)
>    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
>    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
>    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
>    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:679)
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and 
> threat landscape has changed and how IT managers can respond. Discussions 
> will include endpoint security, mobile security and the latest in malware 
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/_______________________________________________
> c3p0-users mailing list
> c3p...@li...
> https://lists.sourceforge.net/lists/listinfo/c3p0-users