Why HikariCP recommends using a fixed-size pool for better performance

Question

According to the HikariCP documentation, they mentioned creating a fixed-size pool to improve performance.

minimumIdle

This property controls the minimum number of idle connections that HikariCP is trying to maintain in the pool. If the connections idle below this value, HikariCP will use its best efforts to quickly and efficiently add additional connections. However, for maximum performance and responsiveness to spike requirements, we recommend that you do not set this value, but instead allow HikariCP to act as a fixed-size connection pool. Default: same as maximumPoolSize

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

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

So which of the following is the best:

• Create a fixed-size connection pool. those. 200

OR

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

Why is the second paragraph not recommended by HikariCP? I think the second will be the best for my business.

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 connections easily processes 10,000 front-panel users and 20,000 transactions per second.

PostgreSQL recommends the formula:

connections = ((core_count * 2) + effective_spindle_count)

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

Most likely, even with 100 connections, your database is very saturated. Do you have 50 processor cores? If you are disks spinning without an SSD, the head can only be in one place at a time, and if the entire data set is not in memory, it is not possible to serve as many requests at once (100-200).

UPDATE: Directly answering the question about the size of the fixed pool. You will probably get maximum performance from your application with a pool that, as the maximum number of connections, will be turned to the right by the “knee” or “maximum” performance that your database can handle. This is probably a small number. If you have a “demand for spike”, as many applications do, trying to deploy new connections to grow the pool at the time of the surge is counterproductive (creates a lot of server load). A small, consistent pool will give you predictable performance.

Answer 2

It really depends on the behavior of applications when performing long and short transactions. Sometimes I feel that it’s better to keep some idle connections to the pool if we want to respond to the client synchronously.