how to determine c3p0 max_statements

Question

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.

I'm using mysql and the deadlock appears to happen when I'm doing some multi threading where I have 4 active threads.

My configuration

<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>

The exception

[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.

Articles I've read

should i activate c3p0 statement pooling?

How to trace and prevent the deadlock appeared in c3po which is running in seperate processes?

score 5 · Answer 1 · answered Jul 29 '15 at 16:38

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 set config param

<property name="hibernate.c3p0.statementCacheNumDeferredCloseThreads">1</property>

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.

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.

how to determine c3p0 max_statements

1 Answers1

Linked