Slow HikariDataSource.getConnection() on slow on low traffic, fast on big traffic

Question

Hi I have this strange problem, I use Oracle db and have miroservice with simple endpoint which has just getById which is extremely fast 3-15ms, but the whole operation took 250ms. I dive into our performance monitor tools and saw that we spend over 200ms for the com.zaxxer.hikari.HikariDataSource.getConnection().

Then I execute 2000 request to that endpoint for 10 minutes and the time drooped to 1.3ms . What happens? When have 5 request per hour it took 200s to get connection, but when have 4 per second 1.3.?

Is there a wrong in the configuration

spring:
 datasource:
    connectionTimeout: 10000
    maxLifetime: 18000000
    maximumPoolSize: 5
    cachePrepStmts: true
    prepStmtCacheSize: 250
    prepStmtCacheSqlLimit: 2048
    useServerPrepStmts: true

Edit: As I understand if we have a big period without a call to the DB this physical DB connections wrapped from Hikari are closed. Do I need to set minimumIdle and idleTimeout ? If I have inactivity 2 hours all the connections will be over maxLifetime and new connection will be created? No need of minimumIdle,right? Example: Let have

minimumIdle 1
idleTimeout 2 minutes
maxLifeTime 20 minutes

When my app stays with nobody making requests during the night, I expect Hikari to close each connection 2 minutes after the connection's last request, after the last connection being closed create a new one (and hold it in the pool), and then close and re-create this idle connection every 20 minutes. Did I understood correctly? And is that a solution for my problem?

The pool "refill" occurs every 30 seconds or so. So, if there are 5 idle connections and a request comes in and consumes one of them, leaving 4 idle, if the request completes and the connection is returned before the "refill", the pool will again have 5 idle connections and will not grow.

Link: [Understanding HikariCP’s Connection Pooling behaviour]

Connections timeout, especially when they are idle. So when there is little/no traffic you might run into stale connections. If you have a pool of 5 this could mean 5 connections until a new one is created. Also if this is a recent Spring version you are using the wrong properties. You should be using checking the connections while idle (and set a min and max for your pool). That way idle connections will be cleaned up actively instead of awaiting a check when getting checked out from the pool. — M. Deinum
@M.Deinum is there a way to keep this connections live and prevent from beeing idle? Like executing select (1) or some trick? Which property of HikariCP to use for " You should be using checking the connections while idle (and set a min and max for your pool)" — Xelian
I suggest the spring boot reference guide. The properties for Hikari are spring.datasource.hikari for the vendor specific ones (unless you are using a very old version of Spring Boot). — M. Deinum

user7294900 user7294900 · Accepted Answer · 2019-10-09T05:56:10

You can set idleTimeout when minimumIdle is defined as less than maximumPoolSize

⌚idleTimeout This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. This setting only applies when minimumIdle is defined to be less than maximumPoolSize. Idle connections will not be retired once the pool reaches minimumIdle connections.

Slow HikariDataSource.getConnection() on slow on low traffic, fast on big traffic

1 Answers