CODEWITHSUNDEEP
java-8connection-poolingc3p0ibatis
ProgrammingEnthusiast
ProgrammingEnthusiast

Reputation: 43

Connection timedout issue with c3p0

I'm using c3p0 as connection pool with Java 8 application along with ibatis as an ORM tool. There are frequent connection timedout issue with database. Following are the configuration parameters.

   <property name="minPoolSize" value="60" />
                                <property name="maxPoolSize" value="200" />
                                <property name="initialPoolSize" value="60" />
                                <property name="statementCacheNumDeferredCloseThreads" value="1" />
                                <property name="acquireRetryAttempts" value="2"/>
                                <property name="checkoutTimeout" value="2000" />
                                <property name="debugUnreturnedConnectionStackTraces" value="true"/>
                                <property name="maxIdleTime" value="120"/>
                                <property name="autoCommitOnClose" value="false" />
                                <property name="testConnectionOnCheckin" value="true" />
                                <property name="numHelperThreads" value="3" />

Here are stack traces:

Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out. at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118) at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:690) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:138) at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:60) at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:336) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) ... 9 more Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@501edcf1 -- timeout at awaitAvailable() at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467) at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644) at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)

... 18 more

Is there anything wrong with the configuration or how can I solve this issue?

Upvotes: 0

Views: 2300

Answers (1)

Steve Waldman
Steve Waldman

Reputation: 14073

You can make the timeout issue go away very easily. Just drop the time out, that is eliminate this line from your config...

<property name="checkoutTimeout" value="2000" />

...or else make the timeout longer.

But the real question is why things are so slow that sometimes it takes longer than 2 seconds for clients to get a Connection. One thing that sticks out is, given the size of the pool, this is probably too small:

<property name="numHelperThreads" value="3" />

Maybe try 10 threads, or 12.

Also, this...

<property name="debugUnreturnedConnectionStackTraces" value="true"/>

you probably want to get rid of. It doesn't do you any good if you don't also have unreturnedConnectionTimeout set, and it does have a performance cost. See here.

Upvotes: 2

Related Questions