Understanding maxLifeTime on MySQL

[Asaf Mesika]

Hi,

The FAQ states that maxLifeTime should be set to be little less than your MySQL wait_timeout.

I look at the definition of wait_timeout for MySQL is is:

The number of seconds the server waits for activity on a noninteractive connection before closing it.

Meaning, MySQL will only close a connection if it is non active (i..e idle) for wait_timeout seconds.

On the other hand, the meaning of maxLifeTime in Hikari is according to this docs:

This property controls the maximum lifetime of a connection in the pool. When a connection reaches this timeout it will be retired from the pool, subject to a maximum variation of +30 seconds. An in-use connection will never be retired, only when it is closed will it then be removed. We strongly recommend setting this value, and it should be at least 30 seconds less than any database-level connection timeout. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. Default: 1800000 (30 minutes)

 

This means HikariCP will close and remove a connection after maxLifeTime milliseconds, regardless if the connection was used or not (idle or not). 

This somewhat in contradiction to the way wait_timeout in MySQL works.

So I was wondering why use maxLifeTime and not set it to 0?

Thanks!

Asaf Mesika

Logz.io

[Brett Wooldridge]

Asaf, you are correct.  The HikariCP idleTimeout should be less than or equal to the MySQL wait_timeout.  The maxLifetime should be less than any infrastructure imposed timeouts, ie. stateful firewall or load-balancer timeouts.  Even if there is no "infrastructure" with timeouts between the application and the database, "infinite" connections are not recommended due to the unfortunate reality of memory/resource leaks on the database-side.  It is better to retire connections periodically (even if only once daily) to allow the database the opportunity to clean up data structures (parse trees, query metadata caches, thread-local storage, etc.) that are associated with a session.

We will correct the FAQ.

-Brett