Best configuration of c3p0 [closed]

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>