Spring book 2 Hikari connection not available when concurrent api calls more than 10000 in aws

Question

I am using spring boot 2 for APIs, hosted on aws ecs fargate. and database is postgress 10.6 on RDS with 16 gb ram and 4 cpu.

my hikari configuration is as follows:

spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1
spring.datasource.hikari.maximum-pool-size=100
spring.datasource.hikari.minimum-idle=80
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=500000
spring.datasource.hikari.max-lifetime=1800000

Now generally this works perfectly.. but when load comes on server, say around 5000 concurrent API request..(which is also not huge though.. ), my application crashes. Have enabled debug log for hikari.. so getting below messages:

hikaripool-1 - pool stats (total=100 active=100 idle=0 waiting=100)

Exception message says connection is not available:

HikariPool-1 - Connection is not available, request timed out after 30000ms.
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC

At the same time when I see RDS postgress performance insighter, max query execution time is < 0.03 second..And CPU utilization also under 50%. So no issue with the Database server.

I am using entitymager and JPA only.. not using any query by opening connection manually. so closing connection or connection leak might not be an issue. But after enabling leak detection:

spring.datasource.hikari.leakDetectionThreshold=2000

Getting warn in logs saying apparent connection leak detected: when I check the method pointing to this error: then it's just JPA findById() method..

so what should be the root cause of connection not available and request time out for just 10k api request.. with 100 pool size.. why its not releasing any connection after active connection goes to 100 and wait is 100? My ECS application server restarts automatically with this error and accessible only after 5-7 minutes after that..

Answer 1

HikariCP recommend removing minimumIdle when there's spike demands as you are testing

for maximum performance and responsiveness to spike demands, we recommend not setting this value and instead allowing HikariCP to act as a fixed size connection

And if you remove it. also idle-timeout is irrelevant

See also configure HikariCP for PostgreSQL

Answer 2

It is likely that your application is throttling it self into timeouts because of the wrong connection pool size in your configuration. A pool size of 100 is 10 times too many and this will affect performance and stability

HikariCP Pool size formula can be found in their wiki, but it looks like this:

((core_count * 2) + effective_spindle_count). Core count should not include HT threads, even if hyperthreading is enabled.

If you have 4 cores then your connection pool size can be left at the default size of 10.

Answer 3

if this might help, I was facing this issue recently and it gave me a tough time.

The server accepts too many requests which hikari pool is not able to handle and so hikari tries to obtain extra connections to serve this spike in demand.

Eg. For tomcat with 200 default threads, if your maxPoolSize = 10, on spike demands, your server would try to serve 200 threads at the same time. If the connections in the pool are busy, hikari would try to obtain 190 connections and this is what you see in the waiting.

Here is how I am managing it.

I made sure that tomcat threads do not exceed the number of hikari maxPoolSize. In that way, there won't be need to ask for more connections during spike.

in spring boot, this is the config I used.

server.tomcat.threads.max = 50
spring.datasource.hikari.maximumPoolSize = 50

Note: 50 is variable based on your server capacity