Why Smaller DB Connection Pools Boost Performance: Lessons from HikariCP Tests

Background

While researching HikariCP, a popular JDBC connection‑pool library, I found a GitHub Wiki article that clarified many doubts about pool sizing and dramatically improved my understanding.

10,000 Concurrent Users Scenario

Imagine a web service handling roughly 10,000 concurrent users (about 20,000 TPS). The intuitive question is not "how large should the pool be?" but rather "how small should it be?"

Watch the Oracle Real‑World Performance Group video for context: http://www.dailymotion.com/video/x2s8uec

The video shows a stress test on Oracle with 9,600 concurrent threads, each sleeping 550 ms between DB calls, initially using a middleware thread pool of 2,048 connections.

When the pool size was reduced to 1,024 (keeping concurrency unchanged), the average wait time in the pool queue stayed roughly the same, but the SQL execution time dropped noticeably.

Further reducing the pool to 96 connections (still 9,600 threads) yielded an average queue wait of 1 ms and SQL execution time of 2 ms, virtually eliminating wait events and increasing throughput.

Simply shrinking the middleware connection pool reduced request latency from ~100 ms to ~3 ms without changing any other configuration.

Why Does This Happen?

Even on a single‑core CPU, the OS can interleave hundreds of threads, but only one thread runs at a time per core. Adding more threads than cores eventually hurts performance because of context‑switch overhead.

Limited Resources

Database performance bottlenecks fall into three categories: CPU, disk, and network. Disk I/O involves seek time and rotational latency, which block threads and allow the OS to schedule other work. Therefore, having more threads than CPU cores can be beneficial when I/O blocks frequently.

With SSDs, seek time disappears, so fewer threads (closer to the core count) often yield better performance. The same principle applies to network I/O.

Sizing Formula

Connections = ((CPU cores * 2) + Effective disks)

Effective disks count only those that are not fully cached. For a 4‑core i7 server with one effective disk, the formula suggests 9–10 connections, which can comfortably handle 3,000 users at 6,000 TPS. Exceeding this pool size typically increases latency and reduces throughput.

Note: This formula also applies to many I/O‑bound applications beyond databases.

Practical Guideline

Do not set the pool size equal to the number of concurrent users. A small pool (often 2 × CPU cores) with a large waiting queue yields better overall performance. Separate pools for long‑running and short‑running transactions can further improve stability.

Takeaway

The optimal connection‑pool size depends on the system’s characteristics—CPU cores, disk type, and workload mix. Over‑provisioning the pool can cause unnecessary contention, while a modest pool sized according to the formula can dramatically reduce latency and increase throughput.