本文介绍了Java连接池最佳实践?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

这些应用程序可以接受的设置是什么?我问,因为在一两分钟后,我试图调用 getConnection 时遇到BoneCP异常。感谢您的帮助。

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.

这是我在我的工作线程中用于db调用的代码,它不能失败在 dbConn = this.dbPool.getConnection()行。

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());
    }
}

这是我看到的错误:

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


推荐答案

如果您有100个工作,为什么将池限制为50个连接

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

看起来确定(但是可能会启用 connectionWatch 作为提示,看看警告是什么)。就个人而言,我关闭了我使用的所有资源,包括语句和结果集。在这种情况下,这里是我使用的成语:

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.

或者您可以使用从Commons DbUtils已经这样做。

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

这篇关于Java连接池最佳实践?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 06:04
查看更多