Java Connection Pooling best practices?

Question

After getting fed up with c3p0's constant locking I'm turning to BoneCP for an alternative connection pool for my database. I have a server app that processes around 7,000 items per minute and needs to log those items into our MySQL database. I currently have 100 worker threads and have set up my pool like such:

BoneCPConfig config = new BoneCPConfig();
      config.setJdbcUrl("jdbc:mysql://"+Settings.MYSQL_HOSTNAME+"/"+Settings.MYSQL_DATABASE+"?autoReconnectForPools=true" ); 
      config.setUsername(Settings.MYSQL_USERNAME); 
      config.setPassword(Settings.MYSQL_PASSWORD);
      config.setMinConnectionsPerPartition(5);
      config.setMaxConnectionsPerPartition(10);
      config.setPartitionCount(5);
      config.setAcquireIncrement(5);
      connectionPool = new BoneCP(config); // setup the connection pool

Are those acceptable settings for such an app? I'm asking because after a minute or two into running I was getting BoneCP exceptions when trying to call getConnection on the pool. Thanks for the help.

Here is the code I was using for the db calls in my worker threads, it can't fail on the dbConn = this.dbPool.getConnection() line. Am I not closing connections properly?

private void insertIntoDb() {
    try {  
        Connection dbConn = this.dbPool.getConnection();

        try {
            PreparedStatement ps3 = dbConn.prepareStatement("INSERT IGNORE INTO test_table1 SET test1=?, test2=?, test3=?");
            ps3.setString(1, "some string");
            ps3.setString(2, "some other string");
            ps3.setString(3, "more strings");
            ps3.execute();
            ps3.close();

            PreparedStatement ps4 = dbConn.prepareStatement("INSERT IGNORE INTO test_table2 SET test1=?, test2=?, test3=?");
            ps4.setString(1, "some string");
            ps4.setString(2, "some other string");
            ps4.setString(3, "more strings");
            ps4.execute();
            ps4.close();

        } catch(SQLException e) {
            logger.error(e.getMessage());
        } finally {
            try {
                dbConn.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
    } catch(SQLException e) {
        logger.error(e.getMessage());
    }
}

This is the error I was seeing:

 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.

ERROR pool-2-thread-39 2010-09-04 13:36:19,798 com.test.testpackage.MyTask - null
java.sql.SQLException
    at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:381)

Answer 1

Are those acceptable settings for such an app? I'm asking because after a minute or two into running I was getting boneCP exceptions when trying to call getConnection on the pool. thanks for the help.

If you have 100 workers, why do you limit the pool to 50 connections (number of partitions x max number of connections per partition i.e. 5 x 10 in your case)?

Am I not closing connections properly?

Looks ok (but maybe enable connectionWatch as hinted to see what the warning is about exactly). Personally, I close all the resources I use, including statement and result sets. Just in case, here is the idiom I use:

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

try {
    conn = pool.getConnection();
    pstmt = conn.prepareStatement(SOME_SQL);
    pstmt.setFoo(1, foo);
    ...
    rs = pstmt.executeQuery();
    ...
} finally {
    if (rs != null) try { rs.close(); } catch (SQLException quiet) {}
    if (pstmt != null) try { pstmt.close(); } catch (SQLException quiet) {}
    if (conn != null) try { conn.close(); } catch (SQLException quiet) {}
}

You could group the above calls in a static method of a utility class.

Or you could use DbUnit.closeQuietly(Connection, Statement, ResultSet) from Commons DbUtils that already does this.