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

Background

The author discovered a compelling article on the HikariCP GitHub wiki that clarified common misconceptions about configuring database connection pools, prompting a translation and sharing of the findings.

Performance Test Scenario

Assuming a website handling roughly 10,000 concurrent users (about 20,000 TPS), the author examined how large the database connection pool should be. The key question is phrased as "Should the website's database connection pool be set smaller ?"

A video from Oracle Real World Performance Group (link: http://www.dailymotion.com/video/x2s8uec) demonstrates a stress test on an Oracle database with 9,600 concurrent threads, each pausing 550 ms between database accesses. The initial middleware thread‑pool size was 2,048.

During the test, each request waited 33 ms in the pool queue and spent 77 ms executing SQL. The database exhibited extensive buffer busy waits and CPU utilization around 95 %.

When the middleware connection pool was reduced to 1,024 (keeping all other parameters constant), the wait time per request remained similar, but SQL execution time dropped significantly, improving overall throughput.

The wait time halved while throughput stayed constant, demonstrating that a smaller pool can reduce latency without sacrificing throughput.

Further Reduction to 96 Connections

Next, the pool size was lowered to 96 connections while maintaining 9,600 concurrent threads. The average queue wait dropped to 1 ms and SQL execution time to 2 ms, virtually eliminating wait events and increasing throughput.

Thus, merely shrinking the middleware connection pool reduced request response time from around 100 ms to 3 ms.

Why Does a Smaller Pool Perform Better?

The explanation lies in fundamental computer science: a single CPU core can execute only one thread at a time, and context switching adds overhead. When the number of threads exceeds the number of cores, performance degrades.

Three primary resources can become bottlenecks: CPU, disk, and network. Disk I/O, especially on spinning disks, involves seek time and rotational latency, causing threads to block. While blocked, the CPU can serve other threads, so having more threads than cores can be beneficial when I/O blocking is significant.

However, with modern SSDs that lack seek time, blocking is reduced, and the optimal thread count approaches the number of CPU cores. The same reasoning applies to network I/O: higher bandwidth reduces blocking, but network is usually a lower‑priority factor.

Practical Formula for Sizing Connection Pools

Based on PostgreSQL benchmarks, the author proposes a simple formula applicable to most databases:

connections = ((cores * 2) + effective_disks)

Do not count hyper‑threaded cores; only physical cores. If the active data set fits entirely in cache, effective_disks is 0. As cache hit rate drops, effective_disks approaches the actual number of disks. The formula’s behavior on SSDs remains unverified.

For a 4‑core i7 server, this yields ((4 * 2) + 1) = 9 connections, rounded to 10. Tests show that exceeding this number causes response latency to rise and TPS to fall.

Note: This formula also applies to other I/O‑bound programs. In a Netty‑based messaging service, the optimal thread count matched the CPU core count.

Key Takeaways

Setting a connection pool size equal to the number of concurrent users (e.g., 10,000) is disastrous; a much smaller pool is optimal.

The pool size should reflect the number of queries the database can handle simultaneously, typically not exceeding 2 * CPU cores.

Mixed workloads with long and short transactions may require separate pools for each.

When a task queue limits concurrent execution, align the number of tasks with the connection pool size rather than the other way around.

In summary, understanding the interplay between CPU, disk, and network resources allows you to size database connection pools effectively, leading to dramatically lower latency and higher throughput.