Hibernate/Spring: Lots (thousands) of open connections the database

Question

I am having a spring/hibernate/mysql deployed on my server. Since some days I am facing the problem that my application seems to open lots of connections to the database. I am taking this hint from a netstat output, which looks like this (IP Addresses obfuscated):

tcp6       0      0 ************:53547       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53595       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53645       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:34986       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53669       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53710       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53757       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53716       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53627       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53752       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53505       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53549       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:35185       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53604       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:35331       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53488       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:34938       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:34987       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53695       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:35380       ************:3306        TIME_WAIT   0          0           -
tcp6       0      0 ************:53651       ************:3306        TIME_WAIT   0          0           -

However, when looking on the MySQL Workbench Server Health Tab for my MySQL Database, it looks as everything would be fine (see screenshot attached).

Also my application has normal performance.

We are currently actively developing the application, but we cannot see any code changes that affect this behaviour. There must have been a change obviously, but it might be also a configuration issue, or whatever.

Could it be a Hibernate Connection Pool? The strange thing is that all the connections are waiting.

I am thankful for all ideas and hints!

Edit - Hibernate Configuration (using Spring)

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="url">
        <value>jdbc:mysql://************:3306/********</value> 
    </property>
    <property name="username">
        <value>********</value>
    </property>
    <property name="password">
        <value>********</value>
    </property>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="dataSource">
            <ref bean="dataSource" />
        </property>
        <property name="configLocation">
            <value>WEB-INF/hibernate.hbm.xml</value>
        </property>
        <property name="configurationClass">
            <value>org.hibernate.cfg.AnnotationConfiguration</value>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>
                <prop key="hibernate.jdbc.batch_size">0</prop>
                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.bytecode.provider">cglib</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
        <property name="eventListeners">
            <map>
                <entry key="save-update" value-ref="saveUpdateEventListener" />
            </map>
        </property>
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>

SOLUTION: I now ended up using Tomcats JNDI Datasource and it finally works. Only 11(!!) connections to the databse used ;) Thanks @Vineet Reynolds

Answer 1

The Hibernate documentation describes how connection pools are established in Hibernate:

Hibernate will obtain and pool connections using java.sql.DriverManager if you set the following properties:

Table 3.1. Hibernate JDBC Properties

Property name                       Purpose
hibernate.connection.driver_class   JDBC driver class
hibernate.connection.url            JDBC URL
hibernate.connection.username       database user
hibernate.connection.password       database user password
hibernate.connection.pool_size      maximum number of pooled connections

and more importantly:

Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing.

It would be worth checking if the values described in the documentation are being used, which would result in Hibernate managing the pool without delegating the pool management to a production-ready connection pool implementation.

As far as the output of netstat is concerned, the number of connections and the reason for opening them are the most important criteria for deciding whether you have a problem at hand. Usually production-ready pool implementations will open connections only when necessary, and will have the ability to shrink pool sizes as well. Additionally, connections may be dropped by such a pool manager if they are not in use. It would appear that from the number of connections that are in the TIME_WAIT state, the server is waiting for traffic from the client; this might be the case of physical connections not being dropped after they are no longer required.

All of the above observations have only one resulting suggestion though - use a connection pool implementation that is better than the default one, and whose performance characteristics are also well understood. You would get suggestions to use c3p0 or BoneCP, from a lot of people here.

Update

Based on the Spring Hibernate configuration posted, it appears that a datasource is used to obtain connections for Hibernate. The configuration of the underlying connection pool behind the datasource would therefore be in use.

Update #2

The updated Spring application context file, uses the DriverManagerDataSource from the Spring framework. Stating ad verbatim from the documentation:

NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call.

An important thing to note is that DriverManagerDataSource relies on the properties provided to it, to create the connection. And as stated in the documentation, it would be using the DriverManager instead of a JNDI bound DataSource to create the connections. An important point here is that the DriverManager usually returns physical connections to the database, unlike DataSources which return logical connection wrappers. It appears (from the netstat output) that these physical connections are not being closed. Maybe, Hibernate is not closing the connections; but that is unlikely; you would better off using a better connection pool implementation that can be configured to have an actual pool of connection objects that never exceed the pool size.