9

I have an mqtt client getting request subscribing from topics, and then I give it to threadpool of fixed size 50. Im using hikaricp 2.4.2 for DB Pooling MySQL database.

Im currently using 2.4.2 and this is my setup

    HikariConfig config = new HikariConfig();
    config.setDataSourceClassName(CLASS_FOR_NAME);
    config.setJdbcUrl(HOST);
    config.setUsername(USER);
    config.setPassword(PASS);
    config.addDataSourceProperty("cachePrepStmts", "true");
    config.addDataSourceProperty("prepStmtCacheSize", "250");
    config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    config.setLeakDetectionThreshold(TimeUnit.SECONDS.toMillis(30));
    config.setValidationTimeout(TimeUnit.MINUTES.toMillis(1));
    config.setMaximumPoolSize(10);
    config.setMinimumIdle(0);
    config.setMaxLifetime(TimeUnit.MINUTES.toMillis(2)); // 120 seconds 
    config.setIdleTimeout(TimeUnit.MINUTES.toMillis(1)); // minutes
    config.setConnectionTimeout(TimeUnit.MINUTES.toMillis(5)); 
    config.setConnectionTestQuery("/* ping */ SELECT 1");

Heres the full log message :

WARNLOG:

811439 [Hikari housekeeper (pool HikariPool-0)] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for connection com.mysql.jdbc.JDBC4Connection@11d0896, stack trace follows java.lang.Exception: Apparent connection leak detected at com.hcpdatabase.DataSource.getConnection(DataSource.java:69) at com.database.AccessDatabase.create_alert(AccessDatabase.java:3849) at com.runnable.StartTaskRunnable2.execute(StartTaskRunnable2.java:78)

Is this normal ? do i have to catch this?

david
  • 2,900
  • 5
  • 28
  • 48
  • Well, it depends, do you want to leak connections? Why don't you show us your `DataSource` class, you know the one where the leak is detected. – Kayaman Nov 24 '15 at 07:11
  • 1
    Are you closing your `Connections`? – Kayaman Nov 24 '15 at 07:40
  • yes, actually sometimes theres some burst transactions for 30 transactions in a second, I think that is why it isnt thread safe(I think). – david Nov 24 '15 at 07:43
  • I always use try{}finally{// closing connection here} all the time. – david Nov 24 '15 at 07:44
  • and sometimes theres a query inside a query so I think i have to restructure some queries. Ill post queries inside queries – david Nov 24 '15 at 07:45
  • 1
    I never said anything about thread safety. Connection leaks are a very simple thing, you check out a connection, but never close it (i.e. return it back to the pool). If you use try/finally (or try-with-resources) consistently everywhere, there should be no leaking connections. – Kayaman Nov 24 '15 at 08:07
  • yes that is whats bothering me, I did close all connections in finally block. again I reviewed all codes and I didnt see any unclose connections. – david Nov 24 '15 at 08:29
  • Well, could it be that you have a single connection checked out for so long that Hikari is suspecting that there's a connection leak? You can also enable the logging for Hikari, and it will display some statistics for you. You can then check whether or not there's an actual leak. – Kayaman Nov 24 '15 at 08:38
  • I guess its not a suspicion anymore because as what im currently seeing in my connection in mysql I see 2 sleeping(with time of 600+ in seconds) connections which does not die, so I presume its already a connection leak because my connection dies at 80 seconds. – david Nov 24 '15 at 08:55

2 Answers2

13

As I have reviewed my codes over and over again. I came to realize that I was barking at the wrong tree, Seems like hikari is very reliable when it comes to connection leak. The problem is when amazon aws ec2 instance is stealing some of my cpu and is even greater than what i thought. So after the cpu goes up 99%, Connection leak is detected even though my codes clearly closed it in finally block. So the problem lies with the machine.

I thank you for all who participated to answer.

david
  • 2,900
  • 5
  • 28
  • 48
4

walk thru the code with 'stack trace' and it would lead you to un-closed connection or the connection that takes longer than threshold.

Nitin
  • 1,582
  • 11
  • 12
  • seems like the problem is the latter, I have fixed it updatiung my connection test query from `config.setConnectionTestQuery("/* ping */ ");` to this `config.setConnectionTestQuery("/* ping */ SELECT 1");` and now I havent seen any 100+ seconds of sleeping connections. – david Nov 26 '15 at 04:57
  • test query taking 100+ seconds ! weird. your question **already** had `config.setConnectionTestQuery("/* ping */ SELECT 1");` However, good thing is if query starts with `/* ping */` (is case and space sensitive) enables Connector/J driver to perform lightweight ping against the server instead of executing statement. – Nitin Nov 26 '15 at 16:27
  • no what i mean is that 100+ seconds idle/sleeping connections is actually leak connection because i already set my connection to die in less than 90 seconds. – david Nov 27 '15 at 00:49
  • where do you set 90 seconds? it is 120 ! from question: `config.setMaxLifetime(TimeUnit.MINUTES.toMillis(2)); // 120 seconds` – Nitin Nov 27 '15 at 07:11
  • `config.setIdleTimeout(TimeUnit.MINUTES.toMillis(1)); // minutes` that is maxlife time , if its idle its only a minute. I dont know why > 80 and less than 90 i observed here – david Nov 27 '15 at 07:16
  • As quoted from the configuration `:watch:idleTimeout This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. Whether a connection is retired as idle or not is subject to a maximum variation of +30 seconds, and average variation of +15 seconds. A connection will never be retired as idle before this timeout. A value of 0 means that idle connections are never removed from the pool. Default: 600000 (10 minutes` seems like the answer to the 15-30 seconds added time lies here – david Nov 27 '15 at 07:19
  • regardless of the question, I think it is fixed now because I moved my server from micro to bigger server in aws, I think the problem lies with the cpu some steal time of the server cpu is getting 100% i thought it was from hikari pool. So that is what killing my connection(I think). – david Nov 27 '15 at 07:24
  • from above, I suggest you try following to have connection life near minute: `config.setIdleTimeout(0); config.setMaxLifetime(TimeUnit.MINUTES.toMillis(1)); and config.setLeakDetectionThreshold(TimeUnit.MINUTES.toMillis(1));` – Nitin Nov 27 '15 at 11:30