CODEWITHSUNDEEP
javajdbc
helion3
helion3

Reputation: 37391

Too many pool exhaustion errors with jdbc-pool

I have an app that began using jdbc-pool for MySQL connection handling in the past month. Prior to that I'd been using Apache's commons-dbcp. I switched to jdbc-pool after reading some analysis that showed it's faster, is being better maintained, etc. However, I've started noticing that a lot more of my users are seeing pool exhaustion errors.

I don't know if it's related to switching from commons to jdbc-pool, but it seemed to start at that time and I'm considering making a build of our product that reverts and giving that to people with db errors to see what impact it has.

I've written up a document that helps them troubleshoot by adjusting the pool connections, increasing max wait, etc. However, I'm starting to see a lot of people report pool exhaustion errors even when their applications are essentially idle. A show full processlist seems to show 19 connections in "sleep" in 1 connection active.

I run the application a lot in both production and dev environments and have never had an issue. I'm better at managing my mysql envirornments but competitor products with weaker pooling systems have no such issues.

I've spent a lot of time reading the jdbc and pooling docs, but am I doing something that would cause poor performance?

These are examples of our db connection code with the defaults we use in place.

// Called once on application start, connects to the database
public DataSource initDbPool(){
    String dns = "jdbc:mysql://"+config.getString("prism.mysql.hostname")+":"+config.getString("prism.mysql.port")+"/"+config.getString("prism.mysql.database");
    pool = new DataSource();
    pool.setDriverClassName("com.mysql.jdbc.Driver");
    pool.setUrl(dns);
    pool.setUsername( /*username*/ );
    pool.setPassword( /*password*/ );
    pool.setInitialSize( 10 );
    pool.setMaxActive( 20 );
    pool.setMaxIdle( 10 );
    pool.setMaxWait( 30000 );
    pool.setRemoveAbandoned(true);
    pool.setRemoveAbandonedTimeout(60);
    return pool;
}

// Called from various classes that need to run queries
public static Connection dbc(){
    Connection con = null;
    try {
        con = pool.getConnection();
    } catch (SQLException e) {
        System.out.print("Database connection failed. " + e.getMessage());
        e.printStackTrace();
    }
    return con;
}

The actual code can be seen here.

I never do much fancy with queries either. We're constantly running batch insert queries but we've optimized that with a queue system and batch inserts so it's extremely fast and uses as little overhead as possible.

Upvotes: 1

Views: 1163

Answers (1)

kan
kan

Reputation: 28961

As I see you have close() but not in a finally block. It means if exception occurs, the connection will be lost. I suspect that commons-dhcp doesn't handle leaked connections the same way as jdbc-pool.

There is removeAbandoned configuration option, which is off by default, because it is the Right Thing™. But as I understand, it doesn't give any guarantees, because it closes them by timeout. If you would have a lot of exceptions in a short period of time, it could exhaust the pool. So, better approach is to close all connections in a finally block always.

Upvotes: 2

Related Questions