HikariCP too many connections

Question

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

So here is what i'm doing:

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

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

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.

EDIT: 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

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

yes this is right. All sleeping process are idle process. Do one thing, before creating any connection you need to check the connection status if there is any opened connection then close it and then re create it. — Altmish-E-Azam, Dec 16 '14 at 13:37
Thanks for your fast answer. Well no - there is no check other than that. How can i do that?;) — Fabian Lurz, Dec 16 '14 at 13:40
I will suggest you please decrease the timeout time in your code. `dataSource3.setConnectionTimeout(1000);` — Altmish-E-Azam, Dec 16 '14 at 13:42
Ok - i will set it to 34000 as it was suggested in another tutorial. I'll soon write back here ;) — Fabian Lurz, Dec 16 '14 at 13:48
No - sry that didn't change it. Same error: `java.sql.SQLException: Timeout after 34001ms 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:62) at main.java.item.methods.GET.content.GetCont.getContent(GetCont.java:58) at main.java.RestService.doGet(RestService.java:511)` — Fabian Lurz, Dec 16 '14 at 13:52

score 29 · Accepted Answer · answered Dec 16 '14 at 14:05

29

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

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.

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.

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

answered Dec 16 '14 at 14:05

brettw

10,664
2
42
59

This are my new values now: `dataSource2.setConnectionTimeout(34000); dataSource2.setIdleTimeout(28740000); dataSource2.setMaxLifetime(28740000);` – Fabian Lurz Dec 16 '14 at 14:33
Ok it is working now. I adjusted all the settings as you said. I also found one little leak (i didn't close the connection - stupid...) But is one leak really that harmful? Because before that, i didn't use a Connection Pool and the leak wasn't that harmful. Ah. And thank you of course:) Made my day. – Fabian Lurz Dec 16 '14 at 14:53
7

Unclosed connections without a pool are generally not harmful, especially if you use auto-commit. The Connection object will be garbage collected, and eventually the DB will close its side of the connection. *With* a pool, unclosed connections will result in your application freezing, because the pool has a maximum size, and when connections are not returned it will eventually be emptied of all connections. The next caller to getConnection() will therefore always timeout. That is why almost all pools support leak detection. – brettw Dec 17 '14 at 02:41

HikariCP too many connections

1 Answers1

Linked