CODEWITHSUNDEEP
javamysqlhibernate
ashweta
ashweta

Reputation: 1487

Hibernate/mysql connection pooling

We host a service (servlet running on jboss), which receives something like 5-6 requests per second. Every request needs to connect to mysql through hibernate. Most of our requests do selects, with an insert/update every 5th/6th request. The hibernate mysql connection gets timed out after mysql connection time out period (8 hours). Even after having a request pinging our service, every hour, the mysql connection sometimes gets randomly disconnected after a day or so. We want hibernate to reconnect in case the connection is disconnected due to any reason, and maintain the number of connections we want it to.

We tried C3P0, with the following configuration:

<property name=c3p0.acquire_increment>1</property>
<property name=c3p0.idle_test_period>3600</property> 
<property name=c3p0.max_statements>0</property> 
<property name=c3p0.min_size>1</property> 
<property name=c3p0.timeout>3605</property> 
<property name=hibernate.c3p0.preferredTestQuery>select 1;</property>

Hibernate connection_pool size was set to 1.

This made the timeout/disconnect problem go away. But we started facing another problem. Long waits. Normally the request serve time for us is 2-3ms for select and 40-50ms for insert/update. But, after using the above C3P0 configuration, we saw that every request completing after an update was taking more than a minute and sometimes even 4-5 minutes! From our logs, it seemed that randomly a select request would get stuck and will be able to complete only after an update request was received and served.

Above problem goes away if we remove the C3P0 config. Can somebody suggest what we might be doing wrong?

Here is the complete hibernate config for reference:

<?xml version="1.0" encoding="utf-8" ?>

<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>

        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://xxx.xxx.xxx</property>
        <property name="connection.username">xxx</property>
        <property name="connection.password">xxx</property>
        <property name="connection.pool_size">1</property> 
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="current_session_context_class">thread</property>
        <property name="hibernate.cache.use_query_cache">false</property>
        <property name="hibernate.cache.use_second_level_cache">false</property>
        <property name="show_sql">true</property>

        <!-- Transaction isolation 2 = READ_COMMITTED -->
        <property name="connection.isolation">2</property>
        <property name="connection.autocommit">true</property>

        <!-- configuration pool via c3p0-->
        <property name="c3p0.acquire_increment">1</property>
        <property name="c3p0.idle_test_period">3600</property> <!-- seconds -->
        <property name="c3p0.max_size">1</property>
        <property name="c3p0.max_statements">0</property>
        <property name="c3p0.min_size">1</property>
        <property name="c3p0.timeout">3605</property> <!-- seconds -->
        <property name="hibernate.c3p0.preferredTestQuery">select 1;</property>

    </session-factory>
</hibernate-configuration>

Upvotes: 1

Views: 9783

Answers (3)

Darrell Teague
Darrell Teague

Reputation: 4272

The JBoss Hibernate documentation specifically states to not use the Hibernate connection-pool manager for non-production systems (just like Hypersonic - I really don't know why they bother to ship known buggy components). As was previously commented, use the standard JBoss <datasource> setup and associated pool-manager (and configure as needed).

Where there is some misunderstanding is that the open() and close() methods are overridden by the connection manager. Even the Connection object isn't a direct connection to the database but rather a handle to the pool-manager.

The implementation upon getting a new connection (aka open) is to request a connection from the pool (which for efficiency is already opened and, if configured using the <prepared-statement-cache-size> property in the datasource definition, also has previous PreparedStatements still bound to that Connection). This call is uber efficient.

When close() is called on the Connection (again, implemented by the pool-manager), the connection reference state is simply changed to 'available' versus closing the actual database connection. This is also uber efficient. Therefore it is recommended that the smallest possible scope of open/exec/close calls be made to maximize multi-thread use of available connections in the pool.

Upvotes: 0

duffymo
duffymo

Reputation: 308733

I'd recommend abandoning C3P0 and using the JNDI naming service and connection pooling that JBOSS provides.

Be sure that you are closing connections properly and returning them to the pool. Connect, query, close, all in the narrowest scope possible. No connection should be hanging around for eight hours.

Some more links might be pertinent: http://www.hibernate.org/117.html and http://www.informit.com/articles/article.aspx?p=353736&seqNum=4 regarding Hibernate and closing connections, and this MySQL bug that cites problems with MySQL, Hibernate, and connections: http://bugs.mysql.com/bug.php?id=10917

Upvotes: 1

wds
wds

Reputation: 32283

Something seems amiss with your configuration. All configuration parameters should be in the hibernate.c3p0 namespace, not c3p0.*.

But that's probably not the problem. I think most likely your pool is only one connection big and you are experiencing resource contention issues somewhere. Most likely not releasing a connection where you should, or a deadlock on some data. Try setting maxPoolsize to something higher, like 2 and see if the problem is mitigated any. This would probably mean you're not properly returning connections.

Upvotes: 1

Related Questions