Java连接汇总最佳实践?[英] Java Connection Pooling best practices?

本文是小编为大家收集整理的关于Java连接汇总最佳实践?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

厌倦了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

这些应用程序可接受的设置吗?我之所以问,因为一两分钟的跑步后,我在泳池上尝试调用getConnection时会得到Bonecp例外.感谢您的帮助.

这是我在工作人员线程中使用的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());
    }
}

这是我看到的错误:

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

推荐答案

这些应用程序可接受的设置吗?我之所以问,因为一两分钟的跑步后,我在试图在泳池上致电GetConnection时会得到Bonecp例外.感谢您的帮助.

如果您有100名工人,为什么将池限制为50个连接(分区数x最大每个分区连接数,即5 x 10)?

我不正确关闭连接吗?

看起来还不错(但也许启用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) {}
}

您可以将上述调用以实用程序类的静态方法进行分组.

或者您可以使用java.jjava.sql.sql.connection connection connection,connectim .

本文地址:https://www.itbaoku.cn/post/597649.html

问题描述

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)

推荐答案

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.