Andrew's Garage

http://www.mimul.com/pebble/default/2008/06/24/1214258760000.html

기본적으로 mysql은 8시간 이후에 동작하지 않는 커넥션은 종료시킵니다. 그래서 커넥션 풀이 비정상 상태가 될 확률이 있어 아래와 같은 설정이 필요하게 됩니다. 기본적으로 데이터 소스는 dbcp, c3p0 두개 중에 하나를 사용하시면 되고, hibernateProperties에 c3p0 속성을 추가해주면 됩니다.
한번 적용해보세요. 가끔 TOMCAT과 MySQL 간의 CLOSE_WAIT가 발생하는 경우도 예방할 수 있습니다.

#The number of seconds the server waits for activity on a connection before closing it.
wait_timeout=28800
#The number of seconds the server waits for activity on an interactive connection before closing it.
interactive_timeout=28800

<bean id="dataSource"
        class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
	<property name="driverClassName"
	      value="${jdbc.driverClassName}" />
	<property name="url" value="${jdbc.url}" />
	<property name="username" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
	<property name="maxActive" value="30"/>
	<property name="maxIdle" value="10"/>
	<property name="maxWait" value="20000"/>
	<property name="logAbandoned" value="true"/>
	<property name="removeAbandoned" value="true"/>
	<property name="removeAbandonedTimeout" value="60"/>
	<property name="validationQuery" value="${jdbc.validationQuery}" />
	<property name="testOnBorrow" value="true" />
	<property name="testOnReturn" value="true" />
</bean>
혹은

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
      destroy-method="close">
	<property name="driverClass" value="${jdbc.driverClassName}"/>
	<property name="jdbcUrl" value="${jdbc.url}"/>
	<property name="user" value="${jdbc.username}"/>
	<property name="password" value="${jdbc.password}"/>
	<property name="acquireRetryAttempts" value="10"></property>
	<property name="preferredTestQuery" value="SELECT 1;"></property>
	<property name="testConnectionOnCheckin" value="true"></property>
	<property name="testConnectionOnCheckout" value="false"></property>
	<property name="maxIdleTime" value="300"></property>
	<property name="idleConnectionTestPeriod" value="30"></property>
</bean>
<bean id="hibernateProperties"
        class="org.springframework.beans.factory.config.PropertiesFactoryBean">
	<property name="properties">
		<props>
		    <prop key="hibernate.dialect">${hibernate.dialect}</prop>
		    <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
		    <prop key="hibernate.hbm2ddl.auto">
	 			${hibernate.hbm2ddl.auto}</prop>
		    <prop key="jdbc.fetch_size">50</prop>
		    <prop key="jdbc.batch_size">25</prop>
		    <prop key="hibernate.cache.use_query_cache">true</prop>
		    <prop key="hibernate.cache.provider_class">
		    	org.hibernate.cache.EhCacheProvider</prop>
		    <prop key="hibernate.query.substitutions">
                         true 1,false 0</prop>
		    <prop key="hibernate.c3p0.initialPoolSize">10</prop>
		    <prop key="hibernate.c3p0.minPoolSize">10</prop>
		    <prop key="hibernate.c3p0.maxPoolSize">50</prop>
		    <prop key="hibernate.c3p0.acquireIncrement">2</prop>
		    <prop key="hibernate.c3p0.timeout">10</prop>
		    <prop key="hibernate.c3p0.max_statement">50</prop>
		    <!-- dataSource가 c3p0 일경우 생략 -->
		    <prop key="hibernate.c3p0.testConnectionOnCheckin">
                         true</prop>
		    <!-- dataSource가 c3p0 일경우 생략 -->
		    <prop key="hibernate.c3p0.testConnectionOnCheckout">
                         false</prop>
		    <prop key="hibernate.c3p0.maxStatementsPerConnection">
                         5</prop>
		    <!-- dataSource가 c3p0 일경우 생략 -->
		    <prop key="hibernate.c3p0.maxIdleTime">300</prop>
		    <prop key="hibernate.c3p0.maxConnectionAge">14400</prop>
		    <prop key="hibernate.c3p0.acquireRetryAttempts">10</prop>
		    <prop key="hibernate.c3p0.preferredTestQuery">
                         SELECT 1;</prop>
		    <!-- dataSource가 c3p0 일경우 생략 -->
		    <prop key="hibernate.c3p0.idleConnectionTestPeriod">
                         300</prop>
		</props>
	</property>
</bean>

PS : 위의 설정의 dataSource부분에서 com.mchange.v2.c3p0.ComboPooledDataSource 라이브러리를 활용할 경우에는 c3p0 – JDBC3 Connection and Statement Pooling을 다운받아서 WEB-INF/lib에 카피해야함.