如何确定c3p0 max_statements [英] how to determine c3p0 max_statements
问题描述
我想知道如何正确确定c3p0 max_statements使用什么值.我经历了一些缓存死锁，这些死锁似乎可以根据我已阅读的所有SO Q& A指向我的max_statements配置.
I'm wondering how to properly determine what value to use for c3p0 max_statements. I've experienced some caching dead locks which seems to point back to my max_statements configuration based on all the SO Q&A I've read.
我正在使用mysql，当我在有4个活动线程的情况下执行多线程时，似乎发生了死锁.
I'm using mysql and the deadlock appears to happen when I'm doing some multi threading where I have 4 active threads.
我的配置
<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.max_size">50</property>
<property name="hibernate.c3p0.idle_test_period">1800</property>
<property name="hibernate.c3p0.timeout">3600</property>
例外
[WARN] async.ThreadPoolAsynchronousRunner com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@72df1587 -- APPARENT DEADLOCK!!! Complete Status:
Managed Threads: 3
Active Threads: 3
Active Tasks:
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@e877a61
on thread: C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#2
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@109b1150
on thread: C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#0
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@3eb42946
on thread: C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#1
Pending Tasks:
com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@52729f95
Pool thread stack traces:
Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#0,5,main]
com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2765)
com.mysql.jdbc.StatementImpl.close(StatementImpl.java:541)
com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#1,5,main]
com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2765)
com.mysql.jdbc.StatementImpl.close(StatementImpl.java:541)
com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#2,5,main]
com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2765)
com.mysql.jdbc.StatementImpl.close(StatementImpl.java:541)
com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
所以我的问题是如何确定这些值应该是什么.我敢肯定，一定要有办法做到这一点，不要猜测.
So my question is how do I determine what these values should be. I'm sure there must be away to do this without guessing.
我读过的文章
推荐答案
要解决与Oracle/jTDS/mySQL下的语句缓存关联的死锁，请确保您使用的是最新的c3p0(当前版本为0.9.5.1)，并请参阅 statementCacheNumDeferredCloseThreads 和
To resolve deadlocks associated with Statement caching under Oracle / jTDS / mySQL, please make sure you are using a recent c3p0 (0.9.5.1 is the current version), and please see statementCacheNumDeferredCloseThreads and Configuring Statement Pooling.
TL; DR设置配置参数
TL;DR set config param
<property name="hibernate.c3p0.statementCacheNumDeferredCloseThreads">1</property>
max_statements的确切值仅与此问题相关.如果max_statements太小，您将不必要地浏览语句，并且与PreparedStatement.close()的脆弱性相关的此问题在某些驱动程序中会更频繁地出现.
The exact value of max_statements is only incidentally associated with this issue. If max_statements is too small, you will churn through statements unnecessarily, and this issue, associated with the fragility of PreparedStatement.close(), in some drivers will appear more frequently.
但是，对于maxPoolSize 50池来说，hibernate.c3p0.max_statements的值太小.即使在解决死锁问题之后，通过语句进行搅动也将减少或杀死语句高速缓存的任何性能优势.要为hibernate.c3p0.max_statements(映射到c3p0.maxStatements)计算一个好的值，请计算在应用程序中频繁使用的不同PreparedStatement的数量，然后将其乘以maxPoolSize(或者在您的情况下) hibernate.c3p0.max_size).或者，只需将hibernate.c3p0.maxStatementsPerConnection设置为应用程序经常使用的不同PreparedStatements的数量.
However, your value for hibernate.c3p0.max_statements is too small for a pool of maxPoolSize 50. Even after you fix the deadlock issue, churning through statements will diminish or kill any performance benefit from the statement cache. To compute a good value for hibernate.c3p0.max_statements (which maps to c3p0.maxStatements), count the number of distinct PreparedStatements that are used frequently in your application and multiply that by maxPoolSize (or in your case hibernate.c3p0.max_size). Or, alternatively, just set hibernate.c3p0.maxStatementsPerConnection to the number of distinct PreparedStatements used frequently by your application.
请参阅
Please see maxStatements, maxStatementsPerConnection, and Configuring Statement Pooling.
这篇关于如何确定c3p0 max_statements的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
