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

High‑Concurrency Experiment

A stress test performed by the Oracle Real World Performance Group used 9,600 concurrent threads against an Oracle database. Each thread slept 550 ms between two database calls. The middleware (application server) connection pool was initially set to 2,048 connections.

With 2,048 connections each request waited about 33 ms in the pool queue and spent 77 ms executing SQL.

Reducing the pool to 1,024 connections

The number of threads remained 9,600, but the pool size was halved. Queue wait time stayed roughly the same, while SQL execution time dropped dramatically, improving overall latency.

The throughput remained stable, but the number of wait events was cut roughly in half.

Further shrinking to 96 connections

Keeping the same 9,600 threads but limiting the pool to 96 connections reduced average queue wait to 1 ms and average SQL time to 2 ms . Most wait events disappeared and throughput increased, dropping overall response time from ~100 ms to ~3 ms.

Why Smaller Pools Perform Better

Even on a single‑core CPU the operating system can interleave many threads via time‑slice scheduling, but only one thread executes at any instant. Adding threads beyond the number of physical cores incurs context‑switch overhead, which degrades performance. When the thread count exceeds core count, the system generally slows down.

Resource Constraints

Database bottlenecks fall into three categories: CPU, disk, and network. Memory bandwidth is usually orders of magnitude higher and rarely the limiting factor.

CPU: On an 8‑core server, setting the number of connections (or worker threads) equal to the core count (ignoring hyper‑threading) yields optimal CPU utilization.

Disk I/O: Mechanical disks introduce seek and rotational latency; even with caching, the fundamental limitation remains. SSDs eliminate seek latency, so the optimal thread count often aligns more closely with the number of cores because I/O wait is minimal.

Network I/O: Higher bandwidth reduces wait time, but network latency is usually the third consideration after CPU and disk.

When a thread blocks on I/O, the OS can schedule another ready thread. Therefore, for I/O‑bound workloads it can be beneficial to have more threads than cores, but the excess should be limited to avoid excessive context switching.

Sizing Formula

The following formula, originally published for PostgreSQL, works well for most relational databases: Pool size = ((CPU cores * 2) + effective disks) "Effective disks" counts only those disks that actually contribute to I/O. If the entire dataset fits in cache, this value is zero. Hyper‑threading should be ignored when counting cores.

Example : A 4‑core i7 server with one effective disk yields ((4 * 2) + 1) = 9 connections; rounding to 10 is reasonable. Tests show that exceeding this pool size quickly increases response latency and reduces transactions per second (TPS).

Practical Recommendations

Use a connection pool sized around 2 × CPU cores (or slightly higher if disk I/O is a bottleneck).

Avoid setting the pool size equal to the number of concurrent users; a pool of 10,000 connections for 10,000 users is counter‑productive.

For mixed workloads (long‑running vs. short‑running transactions), consider separate pools to prevent contention.

Monitor wait events and throughput; if wait events rise sharply while throughput stays flat, the pool is likely too large.

Reference (plain URL): https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing