title: Hibernate/mysql connection pooling
body: <p>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.</p>
  
  <p>We tried C3P0, with the following configuration:</p>
  
  <pre><code>&lt;property name=c3p0.acquire_increment&gt;1&lt;/property&gt;
  &lt;property name=c3p0.idle_test_period&gt;3600&lt;/property&gt; 
  &lt;property name=c3p0.max_statements&gt;0&lt;/property&gt; 
  &lt;property name=c3p0.min_size&gt;1&lt;/property&gt; 
  &lt;property name=c3p0.timeout&gt;3605&lt;/property&gt; 
  &lt;property name=hibernate.c3p0.preferredTestQuery&gt;select 1;&lt;/property&gt;
  </code></pre>
  
  <p>Hibernate connection_pool size was set to 1.</p>
  
  <p>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.</p>
  
  <p>Above problem goes away if we remove the C3P0 config. Can somebody suggest what we might be doing wrong?</p>
  
  <p>Here is the complete hibernate config for reference:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
  
  &lt;!DOCTYPE hibernate-configuration PUBLIC
            "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
            "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;
  
  &lt;hibernate-configuration&gt;
      &lt;session-factory&gt;
  
          &lt;property name="connection.driver_class"&gt;com.mysql.jdbc.Driver&lt;/property&gt;
          &lt;property name="connection.url"&gt;jdbc:mysql://xxx.xxx.xxx&lt;/property&gt;
          &lt;property name="connection.username"&gt;xxx&lt;/property&gt;
          &lt;property name="connection.password"&gt;xxx&lt;/property&gt;
          &lt;property name="connection.pool_size"&gt;1&lt;/property&gt; 
          &lt;property name="dialect"&gt;org.hibernate.dialect.MySQLDialect&lt;/property&gt;
          &lt;property name="current_session_context_class"&gt;thread&lt;/property&gt;
          &lt;property name="hibernate.cache.use_query_cache"&gt;false&lt;/property&gt;
          &lt;property name="hibernate.cache.use_second_level_cache"&gt;false&lt;/property&gt;
          &lt;property name="show_sql"&gt;true&lt;/property&gt;
  
          &lt;!-- Transaction isolation 2 = READ_COMMITTED --&gt;
          &lt;property name="connection.isolation"&gt;2&lt;/property&gt;
          &lt;property name="connection.autocommit"&gt;true&lt;/property&gt;
  
          &lt;!-- configuration pool via c3p0--&gt;
          &lt;property name="c3p0.acquire_increment"&gt;1&lt;/property&gt;
          &lt;property name="c3p0.idle_test_period"&gt;3600&lt;/property&gt; &lt;!-- seconds --&gt;
          &lt;property name="c3p0.max_size"&gt;1&lt;/property&gt;
          &lt;property name="c3p0.max_statements"&gt;0&lt;/property&gt;
          &lt;property name="c3p0.min_size"&gt;1&lt;/property&gt;
          &lt;property name="c3p0.timeout"&gt;3605&lt;/property&gt; &lt;!-- seconds --&gt;
          &lt;property name="hibernate.c3p0.preferredTestQuery"&gt;select 1;&lt;/property&gt;
  
      &lt;/session-factory&gt;
  &lt;/hibernate-configuration&gt;
  </code></pre>