CODEWITHSUNDEEP
javaoracle-databasespringhibernate
Kevindra
Kevindra

Reputation: 1762

Too many DB connections while using hibernate

I am new to hibernate and spring. We've a spring based service, which is using oracle database using hibernate. This service is deployed to 4 hosts.

We have one external java client which is using our service. It makes about ~40 service calls per second. So ideally there should be at most 40(calls)*4(hosts)=160 open connections. But for some reason, the DB connections used by our service grow even more than 600.

Can we set up some metrics for checking the leak in DB connections? How can we find out these leak connections?

Any help would be appreciated.

Thanks, Kevin

Upvotes: 1

Views: 1848

Answers (4)

Pramod Kumar
Pramod Kumar

Reputation: 8014

If you are using 3rd party jars for connection polling then you can enable their logging and set logAbandoned property to true. So it will log your all logAbandoned connections.

As shown below -

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">
        <property name="driverClassName" value="${dataSource.driverClassName}" />
        <property name="url" value="${dataSource.url}" />
        <property name="username" value="${dataSource.username}" />
        <property name="password" value="${dataSource.password}" />
        <property name="validationQuery" value="${datasource.validationQuery}" />
        <property name="maxActive" value="${datasource.maxActive}" />
        <property name="maxIdle" value="${datasource.maxIdle}" />
        <property name="maxWait" value="${datasource.maxWait}" />
        <property name="testOnBorrow" value="true" />
        <property name="testWhileIdle" value="true" />      
        <property name="minIdle" value="${datasource.minIdle}" />
        <property name="initialSize" value="${datasource.initialSize}" />
        <property name="timeBetweenEvictionRunsMillis" value="${datasource.timeBetweenEvictionRunsMillis}" />
        <property name="logAbandoned" value="${datasource.logAbandoned}" />
</bean>

Upvotes: 2

Pramod Kumar
Pramod Kumar

Reputation: 8014

You can add following appenders in your log4j.xml and all c3p0 logs will be directed to ${catalina.home}/logs/c3poLog.log file - 

<appender name="c3poLogs" class="org.apache.log4j.DailyRollingFileAppender">
    <param name="Threshold" value="DEBUG"/>
    <param name="Append" value="true"/>
    <param name="File" value="${catalina.home}/logs/c3poLog.log"/>
    <!--<param name="DatePattern" value=".yyyy-MM-dd"/>-->
    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n"/>
    </layout>
</appender>


<category name="com.mchange.v2" additivity="false">
    <priority value="${log4j.category.com.mchange.v2}"/>
    <appender-ref ref="c3poLogs"/>
</category>

Upvotes: 0

Kevindra
Kevindra

Reputation: 1762

My datasource bean looks like this: 

<bean id="fssDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass">
         <util:property-path path="FSSConfiguration.driverName"/>
    </property>
    <property name="jdbcUrl">
        <util:property-path path="FSSConfiguration.url" />
    </property>
    <property name="user">
            <util:property-path path="FSSConfiguration.username" />
    </property>
    <property name="password">
            <util:property-path path="FSSConfiguration.password" />
    </property>
    <property name="initialPoolSize" value="1" />
    <property name="minPoolSize" value="1" />
    <property name="maxPoolSize" value="20" />
    <property name="maxIdleTime" value="240" />
    <property name="checkoutTimeout" value="60000" />
    <property name="acquireRetryAttempts" value="0" />
    <property name="acquireRetryDelay" value="1000" />
    <property name="debugUnreturnedConnectionStackTraces" value="true" />
    <property name="unreturnedConnectionTimeout" value="300" />
    <property name="numHelperThreads" value="1" />
    <property name="preferredTestQuery" value="SELECT 1 FROM DUAL" />
</bean>

Can you please validate?

Upvotes: 0

Jeshurun
Jeshurun

Reputation: 23186

If you are using a connection pool with Hibernate, then your pool provider might be keeping a lot of connections around, even if they are not being used. In case of c3po, check the connection.minPoolSize and connection.maxPoolSize properties.

Upvotes: 1

Related Questions