Best configuration of c3p0

Question

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

Solution 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>

Solution 2

Best configuration is to setup JPA to use the container environment to get DataSource.

This allows the container to provide the connection pooling rather than configuration it directly into your JPA project.

You do not indicate which container enviroment you are working with. I almost always use c3p0 with Standalone applications, such as Java SE desktop or server applications. I also use it with Spring framework.

When using a container such as Servlet (Tomcat/Jetty) or Application Server (such as Glashfish or JBoss AS) these already provide a DataSource connection pooler implementation that is usually not c3p0 but provides equivalent function.

I have never tried to configure a connection pooler inside a JPA project because this means editing configuration to customize it for every environment it needs to run in. This is a headache so it best that JPA part be given the DataSource to use during bootstrap.

Comments

[Babak Behzadi]

Thanks for your answer. I'm using hibernate3.0 ,c0p3-0.9.1 and Tomcat. Do you have any sample for your strategy to configure connection pool? It's very important to avoid MySQL to break the idle connections after 8 hours, if your past activities help this, It'll be welcomed to me.

[Darryl Miles]

Even if MySQL breaks idle connections after 8 hours, c3p0 can be configured to always test the connection before use, as well as keep testing when it is idle. Did you search StackOverflow for that info ? stackoverflow.com/questions/10526313/… (there are many other references, you need to start with telling Tomcat to not use C3P0 then on setting up the params as you need for your WAR deployment, then change your JPA project to use JNDI lookup).

[Babak Behzadi]

I know testing connection can help and I have c3p0 configurations but I'm not sure that these configurations are true and working. Please take a look at my last question stackoverflow.com/questions/12446266/c3p0-configurations-whe‌​re-and-how

[Babak Behzadi]

Thanks for your answer, Domenic. I'd red the reference that you pointed and I'm aware about the c3p0 properties behavior, but in my project- I think- these properties- the configuration in my other post- do no thing because they're not in a perfect state and need to other properties or they must be set in another configuration file.

[Janning]

-1 I don't see any advantage in configuring the pool inside the application server. Editing a configuration once for an application doesn't cause me headache at all.

[Muhammad Zeeshan]

@Domenic: I am getting the same issue, unable to create new connection. Here are my all details. I feel like you con figure out problem in my c3p0. stackoverflow.com/questions/38994849/…

[ronit]

thats seems helpful, but i have a confusion, the idleConnectionTestPeriod is greater then maxIdleTimeExcessConnections, so the connections would be evicted before they are tested. is that right, so any idle connection wont ever be tested because it would have been discarded already....please help here @Domenic.D

[Domenic D.]

@ronit, you are mostly correct. idleConnectionTestPeriod is used on the few remaining connections that must remain connected as defined by minPoolSize. Think of it as a database that is used primarily during the day. Throughout the day, we just terminate idle connections after 4m, but at night, we test the 3 remaining idle ones every 5 minutes to ensure it's always available.

[asgs]

Hello. your statement "Use of maxIdleTimeExcessConnections and idleConnectionTestPeriod negates the need for maxIdleTime" made me curious. In our application, we have set maxIdleTime to 120 s whereas idleConnectionTestPeriod is set to 300 s. does it mean the idle connections may not always be purged by the time it hits 120 s? please clarify

[Domenic D.]

That is my understanding. If you're testing connections every 300s, the maxIdleTime is irrelevant if it's less than 300.