CODEWITHSUNDEEP
springconnection-poolingc3p0
Pushkar
Pushkar

Reputation: 571

c3p0 pool is not shrinking

I am using c3p0 connection pool with Spring (with plain jdbc, no hibernate). Here is my configuration for pool

<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="${jdbc.driver}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="acquireIncrement" value="3"/>
    <property name="minPoolSize" value="3"/>
    <property name="maxPoolSize" value="25"/>
    <property name="maxStatementsPerConnection" value="0"/>
    <property name="numHelperThreads" value="6"/>        
    <property name="testConnectionOnCheckout" value="false" />
    <property name="testConnectionOnCheckin" value="false" />
    <property name="idleConnectionTestPeriod" value="10"/>
    <property name="preferredTestQuery" value="select curdate()"/>
    <property name="maxIdleTime" value="5" />   
    <property name="unreturnedConnectionTimeout" value="5" />     
    <property name="debugUnreturnedConnectionStackTraces" value="true" />
</bean>

I am using JMX to monitor my connection pool. I see that my pool grows to 25 under load but never shrinks back. Am I missing some cofig here ?

Upvotes: 1

Views: 939

Answers (1)

John R
John R

Reputation: 2096

The default is to not shrink the pool. You need to set maxIdleTimeExcessConnections. From the manual (emphasis added):

maxIdleTimeExcessConnections

Default: 0

Number of seconds that Connections in excess of minPoolSize should be permitted to remain idle in the pool before being culled. Intended for applications that wish to aggressively minimize the number of open Connections, shrinking the pool back towards minPoolSize if, following a spike, the load level diminishes and Connections acquired are no longer needed. If maxIdleTime is set, maxIdleTimeExcessConnections should be smaller if the parameter is to have any effect. Zero means no enforcement, excess Connections are not idled out.

For your pool to shrink after a period of high load:

  • Define a reasonable values for minPoolSize and maxPoolSize. The defaults are 3 and 15, respectively which should work OK for many applications.
  • Set a value for maxIdleTime. I picked 2700 seconds (45 mins) because the Cisco firewall between my app and db servers times out TCP connections after an hour. C3P0 will discard connections that have idled for longer than this period of time.
  • Set a value for maxIdleTimeExcessConnections. I picked 600 seconds (10 mins). C3P0 will close connections that have idled for this period of time until there are only minPoolSize connections open.

Upvotes: 2

Related Questions