Why Bigger Database Connection Pools Hurt Performance and How to Size Them Right

1. Introduction

Most projects need to interact with a database, but how large should the connection pool be? Some veteran developers claim that a larger pool (e.g., 200 connections) yields higher throughput, yet this assumption is often wrong.

2. Real‑world Test Results

A performance test on an Oracle database simulated 9,600 concurrent threads with a 550 ms sleep between operations. When the pool size was 2,048, each request waited 33 ms in the queue, SQL execution took 77 ms, and CPU usage stayed around 95%.

Reducing the pool to 1,024 kept the wait time roughly the same but lowered SQL execution time.

Further decreasing the pool to 96 connections dropped the average queue wait to 1 ms and SQL execution to 2 ms, cutting the overall response time from about 100 ms to 3 ms and dramatically increasing throughput.

3. Why This Happens

On a single‑core CPU, only one thread can execute at a time; the operating system switches contexts to give the illusion of concurrency. Excessive threads cause context‑switch overhead, slowing the system.

When the workload is I/O‑bound, having more threads than CPU cores can improve throughput because threads spend most of their time waiting for I/O.

4. Other Factors to Consider

CPU

Disk I/O

Network I/O

Disk I/O involves seek time and rotational latency; SSDs eliminate these delays, so fewer threads are needed. Network I/O also introduces latency, with higher bandwidth reducing wait times.

5. Connection‑Size Formula

The following formula, originally from PostgreSQL, works for most databases:

connections = (cpu_cores * 2) + effective_disk_count

Do not count hyper‑threaded cores, and if the hot data is fully cached, the effective disk count is zero.

For a 4‑core i7 with one effective disk, the calculation yields ((4*2)+1)=9, which you can round to 10 connections.

6. Practical Recommendations

Do not blindly set the pool size to match the number of concurrent users. A small pool (e.g., 10 connections) with a waiting queue often outperforms an oversized pool.

If your application mixes long‑running and short‑running transactions, consider using separate pools for each.

7. Conclusion

The optimal pool size is usually no more than twice the number of CPU cores, adjusted for I/O characteristics. Over‑provisioning leads to higher latency and lower throughput.

Reference

https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing