Hikari cp losing connections.

[quark]

Hi all,
I'm using Hikari 2.4.12 (java 7) version with the following configuration in Hibernate (5.1.2) and Spring (4.3.3):

Code:

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
      <property name="poolName" value="springHikariCP" />
      <property name="connectionTestQuery" value="${database.connectionTestStatement}" />
      <property name="jdbcUrl" value="${database.jdbc.url}" />
      <property name="connectionTimeout" value="15000"/>
      <property name="maximumPoolSize" value="${database.poolConnections:40}" />
      <property name="maxLifetime" value="27000000"/>
      <property name="dataSourceProperties">
         <props>
            <prop key="url">${database.jdbc.url}</prop>
            <prop key="user">${database.user}</prop>
            <prop key="password">${database.password}</prop>
            <prop key="cachePrepStmts">true</prop>
            <prop key="prepStmtCacheSize">250</prop>
            <prop key="prepStmtCacheSqlLimit">2048</prop>
         </props>
      </property>

We have seen that the number of connections is being reduced in time and after a week more or less we get connection exceptions:
QueryTimeoutException: Unable to acquire JDBC Connection.....

That's to say, there are no available connections in the pool.
I attach a plot of the behaviour of the connections (the recover corresponds to backend deployments so the connections are recovered) :
[img]
https://ibb.co/jm7goa
[/img]
https://ibb.co/jm7goa

It seems that after the maxLifeTime the connections can not be reestablished in the pool. The database (mysql) wait_timeout is 28800 seconds, so the pool maxLifeTime is less as recommended.
We do not use miminumIdle in favour of better performance and responsiveness as says the Hikari Cp documentation.

Is there a better/correct configuration to avoid the loose of connections?.

By the way, I'm not shure but maybe the maxLifeTime in my production environment is not working (it works locally) and it's applied the wait_timeout of the database. 
Maybe this would explain the loose of connections.

Best regards.

[Brett Wooldridge]

Almost certainly a connection leak.  Enable the leakDetectionThreshold, set it to something high like 5 minutes (300000ms) and watch your logs.

EDIT: If it takes a few days to exhaust the pool, the leak is likely in a very seldomly executed piece of code.  So, it may take many hours or even a day or two before you see a log pin-pointing the leak.

-Brett

[quark]

Thank you Brett. I will log the possible connection leaks.

By the way, I've not set any  minimunIdle value and the default idleTimeOut is applied. To avoid that this idleTimeout applies is to set value=0. The documentation says that iddleTimeOut is applied when minimumIdle is defined and is less than maximumPoolSize .

Maybe is a bug???

Raül. 

[Brett Wooldridge]

I'm not sure I understand your question exactly.  I'll provide some explanations...

If minimumIdle is not set, then minimumIdle=maximumPoolSize.  By default, HikariCP operates as a fixed-size pool (recommended).

The purpose of idleTimeOut is to shrink the pool toward minimumIdle, during idle periods, in environments where a dynamically sized pool is desirable.  For example, when a database is shared by many applications.

However, if minimumIdle=maximumPoolSize (i.e. minimumIdle has not been set), then there is no point to retiring a connection at idleTimeOut, because it will be replaced immediately in the pool.  That is why the documentation says:

This setting only applies when minimumIdle is defined to be less than maximumPoolSize.

Now, assuming that the user has set minimumIdle, then the idleTimeOut will be applied.  If the pool is larger than minimumIdle, and some of the connections have been idle for more than idleTimeOut, they will be retired from the pool.

Finally, we get to idleTimeOut=0.  The documentation says:

A value of 0 means that idle connections are never removed from the pool.

This means that the pool will not shrink due to idle conditions.  However, the pool can still shink.  Connections are still retired by the maxLifetime setting, as well as by evictions caused by unrecoverable errors, and by manual connection eviction by the user.  As long as there are more than minimumIdle connections in the pool, they will not be replaced.  Only when demand by the application causes the number of available connections to drop below minimumIdle will additional connections to be added to the pool.

Is it clear now?

-Brett

[quark]

Thank you for your explanation Brett. More clear now.

Finally I activated the leak detection and it seems that detected connection leaks:

2017-09-13 12:42:02,300 WARN Hikari Housekeeping Timer (pool springHikariCP) LeakTask.run - Connection leak detection triggered for connection com.mysql.jdbc.JDBC4Connection@127abb4d, stack trace follows

java.lang.Exception: Apparent connection leak detected

at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)

at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:386)

at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:84)

at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:109)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)

at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)

at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1934)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1903)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881)

at org.hibernate.loader.Loader.doQuery(Loader.java:925)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)

at org.hibernate.loader.Loader.doList(Loader.java:2622)

at org.hibernate.loader.Loader.doList(Loader.java:2605)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434)

at org.hibernate.loader.Loader.list(Loader.java:2429)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)

at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)

at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339)

at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)

at com.pack.model.dao.hibernate.HibernateClientDao.findByUrlName(HibernateClientDao.java:60)

at com.pack.backend.ServicesDispatcher.initializeServicesDispatcher(ServicesDispatcher.java:416)

at com.pack.backend.ServicesDispatcher.call(ServicesDispatcher.java:718)

at com.pack.backend.ServicesDispatcher.call(ServicesDispatcher.java:82)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

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:745)

There are 10 exceptions like this that match with the 10 lost connections to mysql DB.

By the moment I do not understand the cause because this query: findByUrlName is executed in every transaction and there are thousands of transactions.

I continue searching the cause.

[Brett Wooldridge]

Read this discussion https://github.com/brettwooldridge/HikariCP/issues/923#issuecomment-327201478