Java 连接池最佳实践？

Java Connection Pooling best practices?

提问by James

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:

在厌倦了 c3p0 的持续锁定之后，我转向 BoneCP 为我的数据库寻找替代连接池。我有一个服务器应用程序，每分钟处理大约 7,000 个项目，需要将这些项目记录到我们的 MySQL 数据库中。我目前有 100 个工作线程，并像这样设置了我的池：

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 getConnectionon the pool. Thanks for the help.

对于这样的应用程序，这些设置是否可以接受？我问是因为在运行一两分钟后，我在尝试调用getConnection池时遇到 BoneCP 异常。谢谢您的帮助。

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?

这是我在工作线程中用于 db 调用的代码，它不会在线上失败dbConn = this.dbPool.getConnection()。我没有正确关闭连接吗？

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)

采纳答案by Pascal Thivent

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.

对于这样的应用程序，这些设置是否可以接受？我问是因为在运行一两分钟后，我在尝试调用池上的 getConnection 时遇到了 boneCP 异常。谢谢您的帮助。

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)?

如果您有 100 名工作人员，为什么要将池限制为 50 个连接（分区数 x 每个分区的最大连接数，即在您的情况下为 5 x 10）？

Am I not closing connections properly?

我没有正确关闭连接吗？

Looks ok (but maybe enable connectionWatchas 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:

看起来不错（但也许可以connectionWatch按照提示启用以查看警告的确切含义）。就个人而言，我关闭了我使用的所有资源，包括语句和结果集。以防万一，这是我使用的习语：

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.

或者您可以使用DbUnit.closeQuietly(Connection, Statement, ResultSet)已经执行此操作的 Commons DbUtils。