Java连接池最佳实践？ [英] Java Connection Pooling best practices?
问题描述
在厌倦了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)
推荐答案
这些应用程式可接受的设定吗？我问，因为在一两分钟后，我试图调用池上的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.
如果您有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)?
我没有正确关闭连接？
Am I not closing connections properly?
看起来确定（但是可能会启用 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.
或者您可以使用 DbUnit.closeQuietly（Connection，Statement，ResultSet） 从Commons DbUtils已经这样做。
Or you could use DbUnit.closeQuietly(Connection, Statement, ResultSet) from Commons DbUtils that already does this.
这篇关于Java连接池最佳实践？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
