Why does HikariCP recommend fixed size pool for better performance

Question

According to HikariCP's documentation they mentioned to create fixed size pool for better performance.

minimumIdle:

This property controls the minimum number of idle connections that HikariCP tries to maintain in the pool.If the idle connections dip below this value, HikariCP will make a best effort to add additional connections quickly and efficiently. However, 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 pool. Default: same as maximumPoolSize

My application usually requires 100 connections and only at a few circumstances reaches 200 connections.

If I create a 200 connection fixed size pool, most of the time 100 connections will be idle.

So which of the following is the best:

1. Create connection pool with fixed size. i.e. 200

OR

2. Create connection pool by setting minimumIdle to 100 and maximumPoolSize to 200.

Why the second point is not recommended by HikariCP? I think the second one would be the best for my case.

Cassidy

Answer 1

I suggest you read this page and watch the attached video. The Oracle Performance Group demonstrates how an application with a pool of 96 connection easily handles 10,000 front-end users and 20,000 transactions per-second.

PostgreSQL recommends a formula of:

connections = ((core_count * 2) + effective_spindle_count)

Where core_count is CPU cores, and effective_spindle_count is the number of disks in a RAID. For many servers, this formula would result in a connection pool of 10-20 maximum connections.

The odds are that with even 100 connections, your database is heavily over saturated. Do you have 50 CPU cores? If you're drives are spinning platters not SSDs the head can only be in one place at a time, and unless the entire dataset is in memory there is no way to service so many requests at once (100-200).

UPDATE: Directly answering your question about fixed-pool sizing. You will likely get the best performance from your application with a pool that as a maximum connection count turned right at the "knee" or "peak" performance that your DB can handle. This is likely a small number. If you have "spike demand", as many applications do, trying to spin up new connections to grow the pool at the instant of the spike is counter-productive (creates more load on the server). A small, constant pool will give you predictable performance.

Sharisharia

If you feel you need to run with 200 connections, but 100 are often idle, I would recommend running as a non-fixed size pool (minimumIdle=100, maximumPoolSize=200). However, what I would also recommend is that you re-evaluate whether you truly need even a maximum of 100 connections. Most queries are very quick, so even if threads wait for connections, they typically won't wait long (milliseconds). – Sharisharia

@brettw, this information is not necessarily true. If you have a large database with complex queries, where transactions can take multiple seconds, your connections can easily get saturated. – Softcover

In my case, it is a data intensive back-end service. Some schedulers run and pull data from one DB and store into postgres. Those are some heavy data pull and runs almost for 2 hrs. There are multples saveAll() and delete() methods for the same. Just not sure how to know the maximum connection count it reaches. Any idea how to check that? – Bukavu

@AshishSingh If you have long-running transactions, and a largely non-interactive workload, then a non-fixed size pool is probably warranted. However, the original question regarding 100 connections is highly dubious, as most servers unless they have 50-cores or more and flash-based storage, are not capable of actually simultaneously processing 100+ queries at the same time. – Sharisharia

greatly articulated\ – Bascinet

Answer 2

It really depends on the application behavior of running long running and short running transactions. Sometimes I feel that it's better to hold of some idle connections to pool if we wants to respond to client synchronous manner.

Zettazeugma