Java tomcat app showing many client connections to MySql DB

Question

I have a website i am running using tomcat 7 and MYSQL. It runs on a dedicated server. I am using the simple connection pool and simple DAO's

classes outlined below:

I have also attached the screen shot of all the localhost connections on the server. I run another application on a different server using similar DAO's and the same connection pool and i only ever see 4 connections on that server.

Why would there be so many connections and is there a way to trace it back to my application to see why so many are spawning or is this normal?

public class ConnectionPool {

private static ConnectionPool pool = null;
private static DataSource dataSource = null;

public synchronized static ConnectionPool getInstance()
{
    if (pool == null)
    {
        pool = new ConnectionPool();
    }
    return pool;
}

private ConnectionPool()
{
    try
    {
        InitialContext ic = new InitialContext();
        dataSource = (DataSource) ic.lookup("java:comp/env/jdbc/DBNAME");
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
}

public Connection getConnection()
{
    try
    {
        return dataSource.getConnection();
    }
    catch (SQLException sqle)
    {
        sqle.printStackTrace();
        return null;
    }
}

public void freeConnection(Connection c)
{
    try
    {
        c.close();
    }
    catch (SQLException sqle)
    {
        sqle.printStackTrace();
    }
    catch (NullPointerException npe){
        npe.printStackTrace();
    }
}

}

DAO

    public static DefaultMetaInfo loadDefaultMetaData() {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String query = "SELECT META_TAG, TITLE_TAG FROM SITE_SETTINGS";

    try {
        ps = connection.prepareStatement(query);
        rs = ps.executeQuery();

        if (rs.next()){
            DefaultMetaInfo settings = new DefaultMetaInfo();
            settings.setMetaTag(rs.getString("META_TAG"));
            settings.setTitleTag(rs.getString("TITLE_TAG"));
            return settings;
        }
    } catch(SQLException e) {
        e.printStackTrace();
        return null;
    } finally {
        DaoUtil.closeResultSet(rs);
        DaoUtil.closePreparedStatement(ps);
        pool.freeConnection(connection);
    }
    return new DefaultMetaInfo();
}

context

<?xml version='1.0' encoding='utf-8'?>
    <Context>
    <Resource name="jdbc/DBNAME" 
    auth="Container" 
    type="javax.sql.DataSource"
    maxActive="1200" 
    maxIdle="30" 
    maxWait="10000" 
    testOnBorrow="true" 
    validationQuery="select 1"  
    removeAbandoned="true"      
    removeAbandonedTimeout="60"  
    logAbandoned="true"   
    username="user" 
    password="pw" 
    driverClassName="com.mysql.jdbc.Driver" 
    url="jdbc:mysql://127.0.0.1:3306/dbname?autoReconnect=true"/>
    </Context>

Client Connections

I believe the reason why this happened is because when the app loads it caches a bunch of data in the application scope. So about 10 different DAO calls are being done one right after the other. I assume the connection is still being used by the previous call so it is spawning another connection from the pool. Surely enough when I woke up in the am I only saw 2 active connections through out the day. I was worried that they would hit my max client connections of 30 but if my assumption is right this is only because of the server startup cache and those connections eventually go away. — JonnyD91, Mar 21 '14 at 21:20

score 1 · Accepted Answer · edited May 23 '17 at 10:31

If you notice that almost all of your connections from the pool are into SLEEP state.

In the finally block of your loadDefaultMetaData() method you have used below statement to free up the connection for you and return it to the configured connection pool.

pool.freeConnection(connection);

This statement releases your "reservation" on that connection and returns it to the pool. It will not necessarily close the underlying, actual database connection. Interesting part is that freeConnection(connection) do nothing to prevent you from using the connection object again after freeing it (if you still had a reference to it).

And since it could be remaining open (usually will be), unclosed PreparedStatement or ResultSet could be interpreted by the pool layer as an indication of being still busy.

You will be able to inspect or debug the Connection object to identify its state at run-time, to confirm this.

The actual resolution for this probelm is to RTM and configure the connection pool & strategy correctly, as described in below reference URLs.

Shishir

In essence my config is fine given my comment above. I suppose if i wanted to I could add timeBetweenEvictionRunsMillis=300000 to my config. But really as long as i don't creep close to the 30 max client connections it should be fine as is. — JonnyD91, Mar 21 '14 at 21:21

score 1 · Answer 2 · answered Mar 21 '14 at 06:50

Your configuration states maxIdle=30 and it seems like you have less than 30 idling (sleeping) connections. To me this looks like an expected behaviour.

Shishir Kumar already stated that connections don't necessarily get closed when returned to the pool. The reason is that creating connections is actually an expensive operation, thus you typically want to keep them open and reuse them - the validationQuery, that you also configure, is used to check if the connection is still operative.

IMHO What you see is expected behavior when working with a connection pool. Also, please check if you really need (and can handle) 1200 maxActive connections. This looks like an extremely high number. If you ever get into the area where you need that many open connections, you'll want to have more than 30 of them idling.

Java tomcat app showing many client connections to MySql DB

2 Answers2