Best configuration of c3p0

Question

I'm struggling with a problem facing c3p0 configuration. I posted a question last week

Answer 1

This is a configuration I am using which keeps resources to a minimum. Of course you'll want to tailor your application to use the resources it needs...

Reference: http://www.mchange.com/projects/c3p0/index.html

• testConnectionOnCheckin validates the connection when it is returned to the pool. testConnectionOnCheckOut, although 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.

Below configuration sets poolsize between 3-20. Idle connections are retested every 5 minutes to keep them active. Because of idleConnectionTestPeriod, this will only keep the the minumum number of connections alive. If there are more than 3 connections at the 4-minute mark, it kills those connections freeing resources back to the minimum.

Use of maxIdleTimeExcessConnections and idleConnectionTestPeriod negates the need for maxIdleTime

<Context docBase="myapp" path="/myapp" reloadable="true">
    <Resource description="My DB Datasource" name="jdbc/mydb"
        auth="Container" factory="org.apache.naming.factory.BeanFactory"
        type="com.mchange.v2.c3p0.ComboPooledDataSource" 
        user="myuser" password="******"
        minPoolSize="3"
        maxPoolSize="20"
        acquireIncrement="1" 
        driverClass="com.mysql.jdbc.Driver" 
        jdbcUrl="jdbc:mysql://localhost:3306/mydb"
        testConnectionOnCheckin="true" 
        idleConnectionTestPeriod="300"
        maxIdleTimeExcessConnections="240"
    />
</Context>