HikariCP的连接过多 [英] HikariCP too many connections

问题描述

我有一个Java Servlet，我想与jdbc(数据库:mysql)一起使用连接池.

i have a Java Servlet and i want to use connection pooling together with jdbc (Database: mysql).

这就是我在做什么:

(此类为公共最终班级DBConnector)

(This class is public final class 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();
    }
}

当我想调用它时:

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)

由以下原因引起:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:通信链接失败

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

我将mysql max_conncetions设置为1000."SHOW PROCESSLIST"查询向我展示了很多睡眠过程.这些是闲着的吗?

I set mysql max_conncetions to 1000. The "SHOW PROCESSLIST" query shows me a lot of sleeping processes. Are these the idle ones?

我真的很困在这里.不知道哪个设置导致了此问题.所以我的问题是-导致此错误的原因是什么?我究竟做错了什么?任何帮助表示赞赏.

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.

设置Mysql(localhost):

Setup Mysql (localhost):

[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连接器:mysql-connector-java-5.1.25-bin.jar

MySQL Connector: mysql-connector-java-5.1.25-bin.jar

推荐答案

事物的结合.首先，什么版本的HikariCP，Java和MySQL驱动程序?

Couple of things. First, What version of HikariCP, Java, and the MySQL driver?

第二，在一个池中有400个连接? 太多了！每个池中从10到20开始.您每秒可以处理数千笔交易，您会感到惊讶.

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.

第三，这是FAQ中的第二个问题 .阅读答案和链接.您需要将maxLifetime设置为比MySQL本机超时时间短(不到1分钟)的时间.

Third, this is the second question in the FAQ. Read the answer and the link. You need to set maxLifetime to something shorter (by 1 minute) than your MySQL native timeout.

最后，打开DEBUG日志记录，HikariCP并不嘈杂.每30秒，管家线程就会运行并记录一次池统计信息.

Lastly, turn on DEBUG logging, HikariCP is not noisy. Every 30 seconds, the housekeeping thread runs and logs pool statistics.

这篇关于HikariCP的连接过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！