Calls to getConnection appear slow

[dme...@gmail.com]

Hello,

We are experiencing an issue where calls to getConnection seem very slow. We are wondering if perhaps something is wrong in our configuration and can be optimized to reduce the time to obtain a connection. When we use New Relic to analyze our application we can see that for most endpoint calls 80% of the time is spent in getConnection. Our Java code and our MySQL all run very fast.

First a little background, we have set our connection pool sizes to match our CPU counts. We have experimented with various connection pool sizes and found that a lower pool size provided significantly better performance (less context/thread switching, etc.). 

Below is the configuration reported during Hikari startup. Is there anything obvious that would account for why we are seeing calls for getConnection being to slow? As a note this is a Spring based application using Mybatis so we are relying on the framework to manage the connection lifecycle so if there are any spring configurations that might help, any ideas there would also be appreciated.

2017-08-07 15:20:26 DEBUG HikariConfig:938 - HikariPool-3 - configuration:

2017-08-07 15:20:26 DEBUG HikariConfig:967 - allowPoolSuspension.............false

2017-08-07 15:20:26 DEBUG HikariConfig:967 - autoCommit......................true

2017-08-07 15:20:26 DEBUG HikariConfig:967 - catalog.........................none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - connectionInitSql...............none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - connectionTestQuery.............none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - connectionTimeout...............30000

2017-08-07 15:20:26 DEBUG HikariConfig:967 - dataSource......................none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - dataSourceClassName.............none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - dataSourceJNDI..................none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - dataSourceProperties............{user=******, password=<masked>, implicitCachingEnabled=true}

2017-08-07 15:20:26 DEBUG HikariConfig:967 - driverClassName................."com.mysql.jdbc.Driver"

2017-08-07 15:20:26 DEBUG HikariConfig:967 - healthCheckProperties...........{}

2017-08-07 15:20:26 DEBUG HikariConfig:967 - healthCheckRegistry.............none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - idleTimeout.....................600000

2017-08-07 15:20:26 DEBUG HikariConfig:967 - initializationFailFast..........true

2017-08-07 15:20:26 DEBUG HikariConfig:967 - initializationFailTimeout.......1

2017-08-07 15:20:26 DEBUG HikariConfig:967 - isolateInternalQueries..........false

2017-08-07 15:20:26 DEBUG HikariConfig:967 - jdbc4ConnectionTest.............false

2017-08-07 15:20:26 DEBUG HikariConfig:967 - jdbcUrl........................."jdbc:mysql://***.***.***.***:3306/******?autoReconnect=true&autoReconnectForPools=true&interactiveClient=true&characterEncoding=UTF-8&useSSL=false"

2017-08-07 15:20:26 DEBUG HikariConfig:967 - leakDetectionThreshold..........30000

2017-08-07 15:20:26 DEBUG HikariConfig:967 - maxLifetime.....................1800000

2017-08-07 15:20:26 DEBUG HikariConfig:967 - maximumPoolSize.................12

2017-08-07 15:20:26 DEBUG HikariConfig:967 - metricRegistry..................none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - metricsTrackerFactory...........none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - minimumIdle.....................12

2017-08-07 15:20:26 DEBUG HikariConfig:967 - password........................<masked>

2017-08-07 15:20:26 DEBUG HikariConfig:967 - poolName........................"HikariPool-3"

2017-08-07 15:20:26 DEBUG HikariConfig:967 - readOnly........................false

2017-08-07 15:20:26 DEBUG HikariConfig:967 - registerMbeans..................false

2017-08-07 15:20:26 DEBUG HikariConfig:967 - scheduledExecutor...............none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - scheduledExecutorService........internal

2017-08-07 15:20:26 DEBUG HikariConfig:967 - threadFactory...................internal

2017-08-07 15:20:26 DEBUG HikariConfig:967 - transactionIsolation............default

2017-08-07 15:20:26 DEBUG HikariConfig:967 - username........................none

2017-08-07 15:20:26 DEBUG HikariConfig:967 - validationTimeout...............5000

[Brett Wooldridge]

That is not necessarily a bad thing, nor unexpected, as I mention in About Pool Sizing (https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing):

You want a small pool of a few dozen connections at most, and you want the rest of the application threads blocked on the pool awaiting connections. If the pool is properly tuned it is set right at the limit of the number of queries the database is capable of processing simultaneously -- which is rarely much more than (CPU cores * 2) as noted above.
--—--

If your pool size is optimally tuned, whether there are 0 application threads awaiting connections or 20, your request performance cannot go any faster. In terms of pure resource consumption, you can try reducing the "front-end" thread count. But you probably still want 25-50% more front-end threads than the pool size.

You might also want to read https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration.

[Brett Wooldridge]

Also, we do not recommend autoReconnect and autoReconnectForPools. HikariCP will do a better job of managing connection lifetimes.