Why Smaller DB Connection Pools Boost Performance: Real-World Test Insights

Why Smaller DB Connection Pools Boost Performance

Database connection pool configuration is a common source of performance pitfalls. Several counter‑intuitive principles must be understood when sizing a pool.

10k Concurrent Users Scenario

Imagine a website handling roughly 10,000 concurrent users (about 20,000 TPS). The correct question is not "How large should the pool be?" but "How small should it be?"

Oracle Real World Performance Group conducted a stress test with 9,600 concurrent threads, each sleeping 550 ms between database accesses. The initial middleware thread pool size was 2,048.

Initial configuration:

Performance with 2,048 connections:

Each request waited 33 ms in the pool queue, then spent 77 ms executing SQL. The database showed heavy wait events and CPU utilization around 95%.

Reducing the pool to 1,024 (threads unchanged) produced similar queue wait times but reduced SQL execution time, while throughput remained stable.

Further reducing the pool to 96 connections (still 9,600 threads) yielded average queue wait of 1 ms and SQL execution of 2 ms. Wait events nearly vanished and throughput increased, cutting response time from ~100 ms to 3 ms.

Why Does a Smaller Pool Help?

Even on a single‑core CPU, the OS can interleave hundreds of threads via time‑slicing, but only one thread executes at any instant. Context switching adds overhead, so having more threads than CPU cores eventually degrades performance.

The fundamental rule is: CPU + disk + network are the primary bottlenecks. Disk I/O involves seek and rotational latency, causing threads to block. While blocked, the CPU can serve other threads, allowing a higher thread‑to‑core ratio for I/O‑bound workloads.

With modern SSDs, seek latency disappears, so fewer threads (closer to core count) achieve better performance. The same logic applies to network bandwidth.

Sizing Formula

Connections = (CoreCount * 2) + EffectiveDiskCount

CoreCount excludes hyper‑threading. EffectiveDiskCount reflects the number of disks that can cause I/O blocking; if the entire dataset fits in cache, it is zero. For a 4‑core i7 server, the formula yields (4 × 2) + 1 = 9, so a pool size of about 10 is optimal.

Practical Recommendations

Never set a connection pool size equal to the number of concurrent users; a 10,000‑user system does not need a 10,000‑connection pool.

Use a small pool (often ≤ 2 × CPU cores) and let the remaining application threads wait in a queue.

Separate pools for long‑running and short‑running transactions when mixed workloads exist.

Adjust the pool size based on observed I/O characteristics; SSDs typically require fewer connections than spinning disks.

By aligning the connection pool size with the server’s CPU cores and I/O characteristics, you can dramatically reduce latency and improve overall throughput.