HikariCP and maxLifetime

Question

I moved my project to HikariCP. Everything is going fine so far, but with one setting I'm having trouble.

It's the .setMaxLifetime(30*1000) setting in HikariConfig object. I get this warning

WARN com.zaxxer.hikari.HikariConfig - maxLifetime is less than 120000ms, using default 1800000ms.

I know that they recommend not setting is that low as I am trying to. But Unfortunately due to circumstances that I can not change, every TCP connection that is open longer than 50 secods will be terminated in our production environment.

Lightless

You will need to open an enhancement request for this. As a temporary workaround, it is a hack but you can change the maxLifetime after the pool has started via JMX without an enforcement check. See this wiki page for programmatic access: github.com/brettwooldridge/HikariCP/wiki/JMX-Monitoring – Bosanquet

Oh did not think about setting it through JMX, will try it. And submitted a feature request, wonder what they'll say – Lightless

Answer 1

i don't know your HikariCP Version, but in the version 2.2.4 you will find the reason why it will throw the above warning. HikariConfig.class (in the com.zaxxer.hikari.HikariConfig):

 private void More ...validateNumerics()
  {
     Logger logger = LoggerFactory.getLogger(getClass());

     if (connectionTimeout == Integer.MAX_VALUE) {
        logger.warn("No connection wait timeout is set, this might cause an infinite wait.");
     }

     if (minIdle < 0 || minIdle > maxPoolSize) {
        minIdle = maxPoolSize;
     }

     if (maxLifetime < 0) {
        logger.error("maxLifetime cannot be negative.");
        throw new IllegalArgumentException("maxLifetime cannot be negative.");
     }
     else if (maxLifetime > 0 && maxLifetime < TimeUnit.SECONDS.toMillis(120)) {
        logger.warn("maxLifetime is less than 120000ms, using default {}ms.", MAX_LIFETIME);
        maxLifetime = MAX_LIFETIME;
     }

     if (idleTimeout != 0 && idleTimeout < TimeUnit.SECONDS.toMillis(30)) {
        logger.warn("idleTimeout is less than 30000ms, using default {}ms.", IDLE_TIMEOUT);
        idleTimeout = IDLE_TIMEOUT;
     }
     else if (idleTimeout > maxLifetime && maxLifetime > 0) {
        logger.warn("idleTimeout is greater than maxLifetime, setting to maxLifetime.");
        idleTimeout = maxLifetime;
     }

from this code, the maxLifeTime is at least 120000ms, using default 1800000ms. so you can't set the maxLifeTime to 30000ms(30*1000). I guess your HikariCP version is at least older than 2.2.4.

But when you find the latest HikariCP version 2.7.4. it said "We strongly recommend setting this value, and it should be at least 30 seconds less than any database or infrastructure imposed connection time limit."

the same class HikariConfig.class:

private void validateNumerics() {
    if(this.maxLifetime != 0L && this.maxLifetime < TimeUnit.SECONDS.toMillis(30L)) {
        LOGGER.warn("{} - maxLifetime is less than 30000ms, setting to default {}ms.", this.poolName, Long.valueOf(MAX_LIFETIME));
        this.maxLifetime = MAX_LIFETIME;
    }

    if(this.idleTimeout + TimeUnit.SECONDS.toMillis(1L) > this.maxLifetime && this.maxLifetime > 0L) {
        LOGGER.warn("{} - idleTimeout is close to or more than maxLifetime, disabling it.", this.poolName);
        this.idleTimeout = 0L;
    }

    if(this.idleTimeout != 0L && this.idleTimeout < TimeUnit.SECONDS.toMillis(10L)) {
        LOGGER.warn("{} - idleTimeout is less than 10000ms, setting to default {}ms.", this.poolName, Long.valueOf(IDLE_TIMEOUT));
        this.idleTimeout = IDLE_TIMEOUT;
    }

    if(this.leakDetectionThreshold > 0L && !unitTest && (this.leakDetectionThreshold < TimeUnit.SECONDS.toMillis(2L) || this.leakDetectionThreshold > this.maxLifetime && this.maxLifetime > 0L)) {
        LOGGER.warn("{} - leakDetectionThreshold is less than 2000ms or more than maxLifetime, disabling it.", this.poolName);
        this.leakDetectionThreshold = 0L;
    }

    if(this.connectionTimeout < 250L) {
        LOGGER.warn("{} - connectionTimeout is less than 250ms, setting to {}ms.", this.poolName, Long.valueOf(CONNECTION_TIMEOUT));
        this.connectionTimeout = CONNECTION_TIMEOUT;
    }

    if(this.validationTimeout < 250L) {
        LOGGER.warn("{} - validationTimeout is less than 250ms, setting to {}ms.", this.poolName, Long.valueOf(VALIDATION_TIMEOUT));
        this.validationTimeout = VALIDATION_TIMEOUT;
    }

    if(this.maxPoolSize < 1) {
        this.maxPoolSize = this.minIdle <= 0?10:this.minIdle;
    }

    if(this.minIdle < 0 || this.minIdle > this.maxPoolSize) {
        this.minIdle = this.maxPoolSize;
    }

}

from this code, the maxLifeTime has been updated to 30000ms at least in this version.

So now please update your HikariCP version to the latest version 2.7.4 if you want to set maxLifeTime to 30000ms.

But if you update your HikariCP version to 2.7.4 with JDK 8, i also recommend you two points:

1. to set maxLifeTime value to be at least 30000ms.

2. to set maxLifeTime value few minute less than mysql's wait_timeout(show variables like "%timeout%") to avoid broken connection exception.

Dumah

I am using MSSQL. MSSQL is set to 600s or 600000ms by default. Then I set maxLifeTime == 600000 but still I get the error/warning: idleTimeout is close to or more than maxLifetime. Same thing if I set it less 30 seconds, 570000. The funny thing is that if I don't set this... just use the default value of 30 minutes the error will go away but I am not sure if that is a guarantee that there is no broken connection because its the other way around now. – Camilla

@ArtanisZeratul what's version of your HikariCP? – Dumah

HikariCP-2.7.9.jar and hibernate-hikaricp-5.2.17.Final.jar, I am not sure which of these because I am just let Spring Boot dependency do the version management. The springboot I am using is spring-boot-starter-parent version 2.0.5.RELEASE. – Camilla

@ArtanisZeratul can you debug the HikariConfig.class? – Dumah

I'd love to. But I don't have time right now, I'll check next week. – Camilla

please see my answer above. It is an improvement to your answer :)! Cheers. – Camilla

Postgres doesn't have wait_timeout. PostgreSQL currently has: "statement_timeout" - Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. "idle_in_transaction_session_timeout" - Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. – Polliwog

Answer 2

Using Hikari version 2.7.9 and I made the following settings:

HikariConfig cpConfig = new HikariConfig();
cpConfig.setJdbcUrl(jdbcUrl);
cpConfig.setUsername(username);
cpConfig.setPassword(password);
cpConfig.setMaximumPoolSize(15);
cpConfig.setConnectionTestQuery("SELECT 1");

// performance senstive settings
cpConfig.setMinimumIdle(0);
cpConfig.setConnectionTimeout(30000);
cpConfig.setIdleTimeout(35000);
cpConfig.setMaxLifetime(45000);

cpConfig.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

HikariDataSource cpDatasource = new HikariDataSource(cpConfig);
localContainerEntityManagerFactoryBean.setDataSource(cpDatasource);
localContainerEntityManagerFactoryBean.setJpaProperties(jpaProperties);
localContainerEntityManagerFactoryBean.afterPropertiesSet();

and it works. But please be careful with:

 cpConfig.setMinimumIdle(0);

If your DB e.g. like MSSQL has infinite MaxLifetime this has to be set strictly to 0 or else you will have many connections not closed infinitely (indefinitely)

Cheers,
Artanis Zeratul

Camilla

yes, the HikariCP configuration recommend not setting this value. github.com/brettwooldridge/HikariCP – Dumah

The ` cpConfig.setMinimumIdle(0);` helped me a lot. – Mcreynolds