What are the required C3P0 settings for sleep mode to avoid blocking

Question

I am using Hibernate along with MySQL 5.1.30.

I have the following libraries:

• c3p0-0.0.1.2.jar
• MySQL-socket-Java-5.0.3-bin.jar
• hibernate3.jar

I use hibernate.cfg.xml to configure:

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection settings --> <property name="connection.driver_class">org.gjt.mm.mysql.Driver</property> <property name="connection.url">jdbc:mysql://localhost/fooDatatbase</property> <property name="connection.username">foo</property> <property name="connection.password">foo123</property> <!-- Use the C3P0 connection pool provider --> <property name="hibernate.c3p0.min_size">5</property> <property name="hibernate.c3p0.max_size">20</property> <property name="hibernate.c3p0.timeout">300</property> <property name="hibernate.c3p0.max_statements">50</property> <property name="hibernate.c3p0.idle_test_periods">3000</property> <!-- SQL dialect --> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <!-- Enable Hibernate automatic session context management --> <property name="current_session_context_class">thread</property> <!-- Disable the second-level cache --> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <mapping resource="databaselayer/mail/Mail.hbm.xml"/> <mapping resource="databaselayer/courses/Course.hbm.xml"/> <mapping resource="databaselayer/price/Price.hbm.xml"/> <mapping resource="databaselayer/contact/Contact.hbm.xml"/> <mapping resource="databaselayer/artists/Musician.hbm.xml"/> <mapping resource="databaselayer/concerts/Concert.hbm.xml"/> <mapping resource="databaselayer/welcome/Welcome.hbm.xml"/> <mapping resource="databaselayer/information/Information.hbm.xml"/> </session-factory> </hibernate-configuration> 

The JAVA column with the hibernate book explains c3p0 configuration options:

• hibernate.c3p0.min_size This is the minimum number of JDBC connections that C3P0 always supports.
• hibernate.c3p0.max_size . This is the maximum number of connections in the pool. An exception occurs at run time if this number is exhausted.
• hibernate.c3p0.timeout . Specify the waiting period (in this case 300 seconds) after which a free connection is removed from the pool.)
• hibernate.c3p0.max_statements The maximum number of statements to be cached. Caching prepared statements is essential in order to work best with Hibernate.
• hibernate.c3p0.idle_test_periods This is the iddle time in seconds before the connection is automatically verified.

I am using Java 1.5.0_09 and tomcat 6.0 . I have three applications deployed to tomcat. Each of them uses hibernate with a configuration file almost equivalent to the above (only username, database_name, password and display change changes).

Unfortunately, with the settings above, after several hours of work, I get some unpleasant deadlock errors that end up killing tomcat.

 Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@2437d -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1dc5cb7 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@9cd2ef -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@4af355 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1275fcb -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:35 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run 

This seems to be a mistake that some people have already received. I changed my settings trying to follow the workaround described here http://forum.hibernate.org/viewtopic.php?p=2386237 so that:

 <property name="hibernate.c3p0.acquire_increment">1</property> <property name="hibernate.c3p0.min_size">0</property> <property name="hibernate.c3p0.max_size">48</property> <property name="hibernate.c3p0.timeout">0</property> <property name="hibernate.c3p0.max_statements">0</property> 

With the new settings, I do not get Deadlock, but I get:

 WARNING: SQL Error: 0, SQLState: 08S01 Jan 24, 2009 5:53:37 AM org.hibernate.util.JDBCExceptionReporter logExceptions SEVERE: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.io.EOFException STACKTRACE: java.io.EOFException at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913) 

Does anyone know what I'm doing wrong, and how can I configure c3p0 correctly?

Answer 1

Actually, this is probably too late, but the problem is quite simple: hibernate.c3p0.idle_test_periods should not be higher than hibernate.c3p0.timeout or connections closed by the database will not be detected properly.

In addition, deadlock warnings look like some of your code incorrectly returns connections to the pool (i.e. session.close ())

MysqlIO exceptions occur when your application is idle and MySQL closes the connection on the server. Now, if C3P0 incorrectly checks if the connection is really connected, you get EOFExceptions.

I hope this can be helpful.

Answer 2

There is no single answer to this question, since it varies from application to application depending on the usage and download pattern.

The first point refers to the link https://www.hibernate.org/214.html , as it seems that you did this and went further. Here are some tips;

• numHelperThreads: helper threads that do not contain locked locks. Spread these operations across multiple threads
• maxStatements: size of the global cache PreparedStatement c3p0.
• maxStatementsPerConnection: the number of PreparedStatements c3p0 will be cached for one joint connection.
• maxAdministrativeTaskTime: parameter that causes the call to interrupt the task thread () if the task exceeds the set time limit

The first three parameters can improve or decrease the performance based on the set value, where as the fourth parameter you can interrupt the stream after the set limit and allow you to switch to another stream.

Approximate values

• numHelperThreads = 6
• maxStatements = 100
• maxStatementsPerConnection = 12
• maxAdministrativeTaskTime = enough time is required for a heavy request to be triggered during production

maxStatements and maxStatementsPerConnection should be tested for several months, because of these parameters there are several send points to the dead lock.

Also links to these links will be useful;

• http://www.mchange.com/projects/c3p0/index.html#configuration_properties
• http://www.mchange.com/projects/c3p0/index.html#hibernate-specific

Answer 3

hibernate.c3p0.idle_test_periods must be less than h * ibernate.c3p0.timeout *, because the first is just the time at which hibernate checks for loose connections and tries to close it.

Meanwhile, the second is how long it takes to retrieve the connection.

If idle_test_periods is greater than the timeout parameter than hibernation, look for anything that is null or does not exist in the system. At least I figured it out.

Answer 4

This is a fairly old version of Connector / J. To make sure that you are not fighting a known and fixed bug, I would start by getting the latest version (5.0.8):

http://dev.mysql.com/downloads/connector/j/5.0.html

That an EOFException from MysqlIO little suspicious. In normal / non-bad use, you should never get errors from this level.

Answer 5

Do the three applications have a common connection pool, or does each get its own? I would recommend the latter.

Answer 6

  <property name="hibernate.c3p0.timeout">300</property> <property name="hibernate.c3p0.idle_test_periods">3000</property> 

Value

idle_test_period must be less than the timeout value.