Announcement Announcement Module
Collapse
No announcement yet.
all connections in pool are sleepy! Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • all connections in pool are sleepy!

    Hibernate is not re-using the connections in the pool. When I do "show processlist" in mysql, I can see most of them are in "Sleep" state. The pool keeps growing over time, by my specified increment, until I eventually run out.

    Here's the snippet from the data configuration xml:
    Code:
      <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mydb"/>
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <property name="user" value="myuser"/>
        <property name="password" value="mypass"/>
    
        <property name="initialPoolSize" value="10"/>
        <property name="minPoolSize" value="10"/>
        <property name="maxPoolSize" value="100"/>
        <property name="acquireIncrement" value="5"/>
        <property name="maxStatementsPerConnection" value="5" />
        <property name="maxIdleTime" value="7200" />
        <property name="maxConnectionAge" value="14400" />
        <property name="acquireRetryAttempts" value="10"/>
        <property name="testConnectionOnCheckin" value="false" />
        <property name="preferredTestQuery" value="SELECT 1;" />
        <property name="idleConnectionTestPeriod" value="3600" />
        
      </bean>
    
    ......
    
      <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="hibernateProperties">
          <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
            <prop key="hibernate.jdbc.use_get_generated_keys">true</prop>
            <prop key="show_sql">true</prop>
            
            <prop key="hibernate.c3p0.initialPoolSize">10</prop>
            <prop key="hibernate.c3p0.minPoolSize">10</prop>
            <prop key="hibernate.c3p0.maxPoolSize">100</prop>
            <prop key="hibernate.c3p0.acquireIncrement">5</prop>
            <prop key="hibernate.c3p0.maxStatementsPerConnection">5</prop>
            <prop key="hibernate.c3p0.maxIdleTime">7200</prop>
            <prop key="hibernate.c3p0.maxConnectionAge">14400</prop>
            <prop key="hibernate.c3p0.acquireRetryAttempts">10</prop>
            <prop key="hibernate.c3p0.testConnectionOnCheckin">false</prop>
            <prop key="hibernate.c3p0.preferredTestQuery">SELECT 1;</prop>
            <prop key="hibernate.c3p0.idleConnectionTestPeriod">1800</prop>
            
          </props>
        </property>
      </bean>
    Any suggestions?

    I'm using the following:

    c3p0 0.9.1.2
    hibernate 3.2.2.ga
    spring 2.0.2
    Tomcat 5.5.25
    Mysql 4.1.20

    What other information should I provide?

    Thanks!!!
    Last edited by marsrc; Nov 28th, 2007, 11:38 AM.

  • #2
    well you have multiple settings why? Remove all the c3p0 stuff from your hibernate configuration. You want Spring to manage your connection pool (that is what you configured the ComboPooledDataSource for). Next to that what is your Transaction configuration?

    And please next time use the [ code][/code ] tags.

    Comment


    • #3
      Another interesting note is, when I shut down tomcat, the java process won't die and all mysql pool processes remain there... until I kill the process itself.

      p.s. That's c3p0 I'm using, if it wasn't clear initially

      Comment


      • #4
        Originally posted by mdeinum View Post
        well you have multiple settings why? Remove all the c3p0 stuff from your hibernate configuration. You want Spring to manage your connection pool (that is what you configured the ComboPooledDataSource for). Next to that what is your Transaction configuration?
        I did multiple settings at the suggestion of someone else.

        The original dev. left the project, so I'm a bit of a newb. How do I tell what the "transaction configuration" is?

        Originally posted by mdeinum View Post
        And please next time use the [ code][/code ] tags.
        Done... I used <code> the first time

        Comment


        • #5
          As a started I would remove the double configuration, that might make things a bit better.

          Transaction configuration, how are transactions defined in your ApplicationContext? If you don't have anything configured or setup it might be that for some reason connections remain open, instead of returned to the pool.

          Next to those issues I recall some issues with MySQL and stale connections, not sure where this whas, you might try google.

          Comment


          • #6
            How do I tell if it's stale? Is that what Sleepy means?

            I removed the dup. settings but no go.

            Here is the transaction stuff I think you are talking about:


            Code:
              <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
                <property name="sessionFactory" ref="sessionFactory" />
              </bean>
              
              <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
                <property name="transactionManager" ref="transactionManager"/>
              </bean>
            Does that tell you anything?

            Comment


            • #7
              Well it tells me that you use a TransactionTemplate and thus manage transaction partially yourself. Can you show me some dao code and preferably some transactional code?

              Comment


              • #8
                Here's a transaction sample. Basically when a category is saved we create a new channel:

                Code:
                public void saveCategory(final Category category) throws SpringServiceException {
                  transactionTemplate.execute(new TransactionCallback() {
                    public Object doInTransaction(TransactionStatus status) {
                      try {
                        daoService.save(category);
                        Channel categoryChannel = new Channel();
                        categoryChannel.setName(category.getName());
                        categoryChannel.setObjectState(category.getObjectState());
                        categoryChannel.setType(MYConstants.CHANNEL_TYPE_CATEGORY);
                        categoryChannel.addEntity(category);
                        channelsService.saveChannel(categoryChannel);
                      } catch(Exception e) {
                        status.setRollbackOnly();
                      }
                      return null;
                    }
                  });
                }

                Here are three DAO samples:
                Code:
                public List listAllAssigned(Integer customerId, Integer channelObjectState) throws DaoServiceException {
                  try {
                    List channels = new ArrayList();
                    if(channelObjectState.equals(STPNSConstants.ANY_STATE)) {
                      Query query = getSession().createQuery(
                          "select distinct w from stpns.dom.Channel w where w.customers.id=:customerId  order by w.name");
                      query.setParameter("customerId", customerId);
                      channels = query.list();
                    } else {
                      Query query = getSession()
                          .createQuery(
                              "select distinct w from stpns.dom.Channel w where w.objectState=:state and w.customers.id=:customerId order by w.name");
                      query.setParameter("state", channelObjectState);
                      query.setParameter("customerId", customerId);
                      channels = query.list();
                    }
                    return channels;
                  } catch(Exception e) {
                    throw new DaoServiceException(e);
                  }
                }
                
                public void save(Object domObject) throws DaoServiceException {
                  try {
                    getHibernateTemplate().saveOrUpdate(domObject);
                  } catch(Exception e) {
                    throw new DaoServiceException(e);
                  }
                }
                
                public List listAll(Integer objectState) throws DaoServiceException {
                  try {
                    String qry = "from stpns.dom.Channel";
                    if(!objectState.equals(STPNSConstants.ANY_STATE)) {
                      qry += " where objectState=" + objectState.toString();
                    }
                    qry += " order by name";
                    return getHibernateTemplate().find(qry);
                  } catch(Exception e) {
                    throw new DaoServiceException(e);
                  }
                }
                I really appreciate your help!!

                Comment


                • #9
                  And there we have at least a cause of your problem.

                  Code:
                  public List listAllAssigned(Integer customerId, Integer channelObjectState) throws DaoServiceException {
                    try {
                      List channels = new ArrayList();
                      if(channelObjectState.equals(STPNSConstants.ANY_STATE)) {
                        Query query = getSession().createQuery(
                            "select distinct w from stpns.dom.Channel w where w.customers.id=:customerId  order by w.name");
                        query.setParameter("customerId", customerId);
                        channels = query.list();
                      } else {
                        Query query = getSession()
                            .createQuery(
                                "select distinct w from stpns.dom.Channel w where w.objectState=:state and w.customers.id=:customerId order by w.name");
                        query.setParameter("state", channelObjectState);
                        query.setParameter("customerId", customerId);
                        channels = query.list();
                      }
                      return channels;
                    } catch(Exception e) {
                      throw new DaoServiceException(e);
                    }
                  }
                  You use a session directly but don't close it. I suggest either rewrite that part (and other similair parts) to getHibernateTemplate().findByNamedParam or use a HibernateCallback.

                  findByNamedParam
                  Code:
                  public List listAllAssigned(Integer customerId, Integer channelObjectState) throws DaoServiceException {
                  	String query;
                  	Object[] values;
                  	String[] paramNames;
                  	try {
                      if(channelObjectState.equals(STPNSConstants.ANY_STATE)) {
                  		query = "select distinct w from stpns.dom.Channel w where w.customers.id=:customerId  order by w.name";
                  		paramNames = new String[] {"customerId"};
                  		values=new Object[]{customerId};
                  	} else {
                  		query = "select distinct w from stpns.dom.Channel w where w.objectState=:state and w.customers.id=:customerId order by w.name";
                  		paramNames = new String[] {"state","customerId"};
                  		values=new Object[]{channelObjectStatem customerId};
                  	}
                  	return getHibernateTemplate().findByNamedParam(query, paramNames, values);
                  	} catch (Exception e) {
                  		throw new DaoServiceException(e);
                  	}	
                  }
                  HibernateCallback

                  Code:
                  public List listAllAssigned(final Integer customerId, final Integer channelObjectState) throws DaoServiceException {
                  	return getHibernateTemplate().executeFind(new HibernateCallback() {
                  		public Object doInHIbernate(Session session) throws SQLException, HibernateException {
                  			List channels = new ArrayList();
                  			if(channelObjectState.equals(STPNSConstants.ANY_STATE)) {
                  			  Query query = session.createQuery(
                  				  "select distinct w from stpns.dom.Channel w where w.customers.id=:customerId  order by w.name");
                  			  query.setParameter("customerId", customerId);
                  			  channels = query.list();
                  			} else {
                  			  Query query = 
                  				  session.createQuery(
                  					  "select distinct w from stpns.dom.Channel w where w.objectState=:state and w.customers.id=:customerId order by w.name");
                  			  query.setParameter("state", channelObjectState);
                  			  query.setParameter("customerId", customerId);
                  			  channels = query.list();
                  			}
                  			return channels;			
                  		}
                  	});
                  }

                  Comment


                  • #10
                    That really sucks...that kind of code is all throughout the system.

                    Oh well, you've just booked the rest of my week.

                    I really appreciate your help!!

                    Comment


                    • #11
                      The easiest way is probably the HibernateCallback, that way you can leave most of the code as is. You only need to wrap it in a callback (as my example) and remove the getSession call (replace it with session).

                      Comment


                      • #12
                        I have determined that the piece of code that is accessed about 75% over everything else, is our "view article" function (think news service). It looks like:

                        Code:
                        public Object getByField(String fieldName, String fieldValue) throws DaoServiceException {
                          try {
                            String query = "from myco.dom.Article as a where " + fieldName + " = '" + fieldValue + "'";
                            query += " order by " + this.orderByColumn;
                            List objs = getHibernateTemplate().find(query);
                            if(objs != null && objs.size() > 0) {
                              Article art = (Article)objs.get(0);
                              return getHibernateTemplate().load(Article.class, new Integer(art.getId()));
                            } else {
                              return null;
                            }
                          } catch(Exception e) {
                            throw new DaoServiceException(e);
                          }
                        }
                        Do you see any possible db leaks there?

                        Thanks

                        Comment


                        • #13
                          A lot of things could be said about that piece of code, but there is no connection leak in it.

                          Comment


                          • #14
                            This is why I pay our developers so much money....

                            (Ok, so I won't admin it's mine!)

                            Comment

                            Working...
                            X