DBCP Connection pool limited size 9 connectionis only

yagnesh.chawda · **Posted:** Mon Nov 16, 2009 10:22 am

Tomcat DBCP Connection Pooling to MySQL limited number of connection issue in Spring2.5 + Hibernate3 + commons-DBCP1.2

Hi,
I am using Spring 2.5, Hibernate 3 and MySQL (5.0.45 (32 Bit), 5.0.84(64 Bit)) with commons-DBCP 1.2 in Tomcat-5.5

My issue is that even after specifying Connection pool of initialSize=40 and minIdle=35, my App. server is keeps only 9 connections open under zero load condition. What I could figure out from the MySQL log was that, server did created 40 connections, but also dropped each connection in 1-2 seconds after connection started. I have max_connection=200 in MySQL, and time to keep idle connection in MySQL is also 8Hrs.

Under load, App server creates connections upto 95 as I have described it as maxActive=95
I confirmed it thru "show global status" and "show processlist" on MySQL prompt while simulating a heavy traffic thru JMeter.

I tried with c3p0 from Hibernate, but no luck. Outcome is exactly same. So am wondering what the issue is ??

My bean definition is as follows for DBCP:

Code:

    <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
       <!--property name="driverClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"/>
       <property name="url" value="${db.url}"/>
       <property name="username" value="${db.user}"/>
       <property name="password" value="${db.pass}"/>
       <property name="initialSize" value="40"/>
       <property name="minIdle" value="35"/>
       <property name="maxActive" value="95"/>
       <property name="maxWait" value="20000"/ -->
   </bean>



    <!-- Hibernate related Beans definition starts here -->
    <bean  id="sessionFactory"
         class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
      <property name="dataSource" ref="dataSource" />


      <property name="annotatedClasses">
         <list>
                         <value>com.domain.Company</value>
          <value>com.domain.Country</value>
         </list>
      </property>
      <property name="hibernateProperties">
         <props>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.format_sql">false</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
                <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
         
            <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
              <prop key="hibernate.cache.provider_configuration_file_resource_path">my-ehcache.xml</prop>
              <prop key="hibernate.cache.use_structured_entries">true</prop>
              <prop key="hibernate.cache.use_second_level_cache">true</prop>
              <prop key="hibernate.cache.use_query_cache">true</prop>
              <prop key="hibernate.default_batch_fetch_size">30</prop>
              <prop key="hibernate.jdbc.batch_size">30</prop>
              <prop key="hibernate.generate_statistics">true</prop>
              <prop key="hibernate.order_updates">true</prop>
              <prop key="hibernate.connection.release_mode">auto</prop>
         </props>
      </property>
   </bean>