Hi,

I'm using Hibernate 2.0 with C3p0 as connection pooling for the production environment. For some reason, the connections are not released properly and after 15 hours the system just crawls and need a restart. I'm not sure about the timeout parameter and guessing thats the reason for this issue. Below is my hibernate configuration xml file.

I'm doubtint this setting <property name="hibernate.c3p0.timeout">5000</property>. Is 5000 as a timeout too much? What is the optimal timeout period that I should set.


<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>

<session-factory>
<property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="hibernate.connection.url">jdbc:oracle:thin:@xxx.xxx.com:1521:xxx</property>
<property name="hibernate.connection.username">me</property>
<property name="hibernate.connection.password">yesme</property>
<property name="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</property>
<property name="show_sql">true</property>
<property name="hibernate.c3p0.max_size">25</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">0</property>


<mapping resource="Singleaaa.hbm.xml"/>
<mapping resource="Singlebbb.hbm.xml"/>
<mapping resource="Singleccc.hbm.xml"/>
<mapping resource="Singleddd.hbm.xml"/>


</session-factory>

</hibernate-configuration>


Thanks very much

Hi,

I'm sorry to mention about the database. We are using Oracle 9i.

Thanks

i don't think the problem is due to the timeout parameter.
Are you sure your app disconnect or close the session?

"after 15 hours the system just crawls" ? are you talking about db or app server?

I was having the same problems with C3P0. I switched to DBCP and it works fine. I am closing my sessions properly. If it was an issue of open/close pairs, I think that DBCP would also render the same memory problems, however I have not studied the internals to DBCP. Also, Hibernate connection pooling would not replicate the memory issue either.

Hi.

First, you might try fetching the latest version of c3p0 (0.8.4.2) from sourceforge. The version that's included with hibernate is quite old, and much has changed. The newer jar file is a drop-in replacement -- just remove the c3p0 jar file from hibernate's lib directory and copy c3p0-0.8.4.2's jar file in and you should be good to go.

That said, I'd like to figure out just what problem you're seeing here. c3p0 is a small library designed for speed -- it shouldn't be "slowing your system to a crawl". When you say c3p0 is not releasing Connections, what exactly do you see -- does the number of clients to your database rise to hibernate.c3p0.max_size and stay there? When you say your your system slows down, what exactly happens? Does the memory footprint of your java app get vary large? Do Connection checkouts take a long time?

Regarding hibernate.c3p0.timeout, do note that Connections will only time out if they are idle for the length of time specified. Thus, if the value is 5000, a Connection will only time out if it is not checked out by any client for more than 80 minutes. You might make this shorter.

You might also add hibernate.c3p0.idle_test_period, which defines a number of seconds after which idle connections in the pool will be tested, and only destroyed if they are invalid. Obviously, this should be significantly shorter than hibernate.c3p0.timeout.

Good luck!

Steve
c3p0 maintainer

Hi,

Thanks for the response. Yes, the connections to the database goes to maximum and stays there eventhough I'm releasing the connections properly. Let me try as you suggested by getting the latest version of c3p0 as I'm using the one that comes with hibernate 2.0. I guess if I try this parameter then things should be OK.

hibernate.c3p0.timeout 3600
hibernate.c3p0.idle_test_period 1800

BTW, What do you mean by destroying the connections in the pool if it is invalid? When does a connection becomes invalid?

Best Regards

> BTW, What do you mean by destroying the connections in the pool if it is invalid? When does a connection becomes invalid?

Sorry for the delayed response!

c3p0 can test Connections periodically by attempting an operation on them. If the operation fails, the Connection will be considered invalid, and get purged from the pool.

If you're using c3p0 via hibernate, setting hibernate.c3p0.validate to true causes Connections to be tested on every checkout (but this slows down checkouts), and setting a nonzero hibernate.c3p0.idle_test_period causes idle connections in the pool to be periodically tested.

Your settings look reasonable.

If you have other c3p0-related questions or problems, feel free to write c3p0-users@lists.sourceforge.net, or to me directly at swaldman@mchange.com.

smiles,
Steve

From my own experience, I would recommend proxool rather than C3P0 or DBCP (DBCP is known to have stability problems in the long run)...

Hibernate has a good support for Proxool.

You can find more information at http://proxool.sourceforge.net