Java HikariCP 连接过多
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27505536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
HikariCP too many connections
提问by Fabian Lurz
i have a Java Servlet and i want to use connection pooling together with jdbc (Database: mysql).
我有一个 Java Servlet，我想将连接池与 jdbc（数据库：mysql）一起使用。
So here is what i'm doing:
所以这就是我在做什么：
(This class is public final class DBConnector)
（这个类是公共final类DBConnector）
private static final HikariDataSource dataSource = new HikariDataSource();
private static final HikariDataSource dataSource2 = new HikariDataSource();
private static final HikariDataSource dataSource3 = new HikariDataSource();
static {
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/contentdb");
dataSource.setUsername("root2");
dataSource.setPassword("password");
dataSource.setMaximumPoolSize(400);
dataSource.setMinimumIdle(5);
dataSource.setLeakDetectionThreshold(15000);
dataSource.setConnectionTestQuery("SELECT 1");
dataSource.setConnectionTimeout(1000);
dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
dataSource2.setJdbcUrl("jdbc:mysql://localhost:3306/userdb");
dataSource2.setUsername("root");
dataSource2.setPassword("password");
dataSource2.setMaximumPoolSize(300);
dataSource2.setMinimumIdle(5);
dataSource2.setLeakDetectionThreshold(15000);
dataSource2.setConnectionTestQuery("SELECT 1");
dataSource2.setConnectionTimeout(1000);
dataSource3.setDriverClassName("com.mysql.jdbc.Driver");
dataSource3.setJdbcUrl("jdbc:mysql://localhost:3306/analysedb");
dataSource3.setUsername("root2");
dataSource3.setPassword("password");
dataSource3.setMaximumPoolSize(200);
dataSource3.setMinimumIdle(5);
dataSource3.setLeakDetectionThreshold(15000);
dataSource3.setConnectionTestQuery("SELECT 1");
dataSource3.setConnectionTimeout(1000);
}
private DBConnector() {
//
}
public static Connection getConnection(int dataBase) throws SQLException {
if (dataBase == 0) {
return dataSource.getConnection();
} else if (dataBase == 1) {
return dataSource2.getConnection();
} else {
return dataSource3.getConnection();
}
}
And when i want to call it:
当我想调用它时：
Connection con = null;
PreparedStatement query = null;
ResultSet result = null;
try {
con = DBConnector.getConnection(0);
}catch(SQLException ex){
}finally{
if (result != null) {
try {
result.close();
} catch (SQLException logOrIgnore) {
}
}
if (query != null) {
try {
query.close();
} catch (SQLException logOrIgnore) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException logOrIgnore) {
}
}
}
But when i click through my app, after a while it starts hanging and i get these errors:
但是当我点击我的应用程序时，它开始挂起并出现以下错误：
java.sql.SQLException: Timeout after 1001ms of waiting for a connection.
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:208)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:108)
at main.java.db.DBConnector.getConnection(DBConnector.java:60)
at main.java.ressources.SingleItemData.getVotes(SingleItemData.java:1088)
at main.java.item.methods.GET.content.GetStreamContent.getStreamContent(GetStreamContent.java:126)
at main.java.RestService.doGet(RestService.java:254)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
引起：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException：通信链接失败
I set mysql max_conncetions to 1000. The "SHOW PROCESSLIST" query shows me a lot of sleeping processes. Are these the idle ones?
我将 mysql max_conncetions 设置为 1000。“SHOW PROCESSLIST”查询显示了很多睡眠进程。这些是闲人吗？
I'm really kind of stuck here. Don't know which setting is causing that issue. So my question is - what causes this error? What am I doing wrong? Any help appreciated.
我真的有点被困在这里。不知道是哪个设置导致了这个问题。所以我的问题是 - 是什么导致了这个错误？我究竟做错了什么？任何帮助表示赞赏。
EDIT: Setup Mysql (localhost):
编辑：设置Mysql（本地主机）：
[mysqld]
user=mysql
port=3306
socket =/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock
key_buffer=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
max_connections = 1000
wait_timeout = 28800
interactive_timeout = 28800
HikariCP: HikariCP-java6-2.2.5.jar
HikariCP： HikariCP-java6-2.2.5.jar
MySQL Connector: mysql-connector-java-5.1.25-bin.jar
MySQL 连接器： mysql-connector-java-5.1.25-bin.jar
采纳答案by brettw
Couple of things. First, What version of HikariCP, Java, and the MySQL driver?
几件事。首先，HikariCP、Java 和 MySQL 驱动程序的版本是什么？
Second, 400 connections in one pool? Way too many!Start with 10 to 20, in each pool. You'll be surprised that you can handle a few thousand transactions per second.
第二，一个池中有 400 个连接？ 太多了！从 10 到 20 个开始，在每个池中。您会惊讶于每秒可以处理几千个事务。
Third, this is the second question in the FAQ. Read the answer and the link. You need to set maxLifetimeto something shorter (by 1 minute) than your MySQL native timeout.
第三，这是FAQ 中的第二个问题。阅读答案和链接。您需要设置maxLifetime为比 MySQL 本机超时更短的时间（1 分钟）。
Lastly, turn on DEBUG logging, HikariCP is not noisy. Every 30 seconds, the housekeeping thread runs and logs pool statistics.
最后，打开 DEBUG logging，HikariCP 没有噪音。每 30 秒，管理线程运行一次并记录池统计信息。
