Limit jdbc connection pool fixed amount

Question

Hi i use micronaut data together with various jdbc connection pools. I first had hikari and also tried the tomcat one.

What i was assuming that setting the datasource to maximum-pool-size: 10 results in max 10 open connections. But it seems that there is a lot of opening and closing going on. Together with a lot o requests at the same time, it uses much more than only 10 connections. The thing is, that the azure postgresql only allows 100 connections in total.

Currently i have running 7 apps accessing that database. Which i expect to result in 70 connections max total. But in reality it is much more.

I also tried using the tomcat jdbc pool, he behaves a little differntly. But also uses more than the 10 connections. I also checked using a java profiler and figured out, that some times its up to 100 open/close connection events per second.

Any suggestion how to act in that case, except of using a second database. I was hoping that the pool will buffer the calls, especially cause they come from a kafka topic. But well, seems to be differently.

--- edit add hikari log

Here is the log output of hikari

2020-12-11 11:59:40,983 [main] DEBUG com.zaxxer.hikari.HikariConfig - Driver class org.postgresql.Driver found in Thread context class loader jdk.internal.loader.ClassLoaders$AppClassLoader@2c13da15
2020-12-11 11:59:40,993 [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
2020-12-11 11:59:40,999 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
2020-12-11 11:59:41,000 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
2020-12-11 11:59:41,000 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
2020-12-11 11:59:41,001 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
2020-12-11 11:59:41,001 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery............."SELECT 1;"
2020-12-11 11:59:41,001 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
2020-12-11 11:59:41,002 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
2020-12-11 11:59:41,002 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
2020-12-11 11:59:41,002 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
2020-12-11 11:59:41,003 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
2020-12-11 11:59:41,004 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."org.postgresql.Driver"
2020-12-11 11:59:41,004 [main] DEBUG com.zaxxer.hikari.HikariConfig - exceptionOverrideClassName......none
2020-12-11 11:59:41,004 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
2020-12-11 11:59:41,005 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
2020-12-11 11:59:41,005 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
2020-12-11 11:59:41,005 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
2020-12-11 11:59:41,006 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
2020-12-11 11:59:41,007 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:postgresql://URL:5432/postgres
2020-12-11 11:59:41,007 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
2020-12-11 11:59:41,007 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
2020-12-11 11:59:41,008 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
2020-12-11 11:59:41,008 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
2020-12-11 11:59:41,008 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
2020-12-11 11:59:41,009 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................10
2020-12-11 11:59:41,009 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
2020-12-11 11:59:41,009 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
2020-12-11 11:59:41,010 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
2020-12-11 11:59:41,010 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
2020-12-11 11:59:41,011 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
2020-12-11 11:59:41,011 [main] DEBUG com.zaxxer.hikari.HikariConfig - schema.........................."SCHEMA"
2020-12-11 11:59:41,011 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
2020-12-11 11:59:41,011 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
2020-12-11 11:59:41,012 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"USERNAME"
2020-12-11 11:59:41,012 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000

Answer 1

I found the mistake - or at least something that solves the issue.

While saving some data into the database i also try to update the cache. But due to change on caffeines loadingcache, each save also results in a get on the exactly same data object instance.

My guessing is due the transaction that causes trouble. After replacing the cache.get with the cache.replace everything works fine.