Using Spring with Hibernate and c3p0 Connection Pool.

C3P0 is a very nice tool to manage database connections. I had hard time configuring Apache DBCP/2 so tried c3p0. There are many config options to set and the setting has to be done carefully so that we do not end up choking our database. Let us understand some of the config options.
  • testConnectionOnCheckin validates the connection when it is returned to the pool.
  • testConnectionOnCheckOut would ensure active connections before use, would be too expensive to do.
  • idleConnectionTestPeriod sets a limit to how long a connection will stay idle before testing it. Without preferredTestQuery, the default is DatabaseMetaData.getTables() – which is database agnostic, and although a relatively expensive call, is probably fine for a relatively small database. If you’re paranoid about performance use a query specific to your database (i.e. preferredTestQuery="SELECT 1")
  • maxIdleTimeExcessConnections will bring back the connectionCount back down to minPoolSize after a spike in activity, the connection is removed from the pool and returned back to db.
  • numHelperThreads it will help c3p0 spawns helper threads to manage the connections and returning them back

My spring configuration goes as –

<bean id="dataSource"  class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="jdbcUrl">
        <value>jdbc:mysql://localhost:3306/api</value>
    </property>
    <property name="user">
        <value>root</value>
    </property>
    <property name="password">
        <value></value>
    </property>
    <property name="idleConnectionTestPeriod">
        <value>300</value>
    </property>
    <property name="maxIdleTimeExcessConnections" value="180"/>
    <property name="maxPoolSize">
        <value>100</value>
    </property>
    <property name="acquireIncrement">
        <value>1</value>
    </property>
    <property name="maxStatements">
        <value>0</value>
    </property>
    <property name="minPoolSize">
        <value>10</value>
    </property>
    <property name="unreturnedConnectionTimeout">
        <value>3600</value>
    </property>
    <property name="preferredTestQuery">
        <value>SELECT 1</value>
    </property>
    <property name="initialPoolSize">
        <value>10</value>
    </property>
</bean>
Things to keep in mind:
  • To get an idea, please try to check the number of connection the app has hooked up with the database. For MySql try –  SHOW STATUS WHERE variable_name = ‘Threads_connected’;
  • When using Hibernate try to take care of opening and closing of sessions. If sessions are not properly closed, the connections are not freed and eventually it will choke the database.
Further Read - How to configure c3p0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s