How to Size Database Connection Pools: Lessons from HikariCP Performance Tests

The author shares a translation of a HikariCP wiki article that dispels common misconceptions about database connection pool sizing.

Performance tests on an Oracle system with 9600 concurrent threads showed that a pool of 2048 connections caused long queue waits (33 ms) and high CPU usage, while reducing the pool to 1024 cut wait events in half without hurting throughput. Further shrinking the pool to 96 connections reduced average wait to 1 ms and SQL execution time to 2 ms, dropping response latency from ~100 ms to 3 ms.

The article explains that the root cause is thread‑context‑switch overhead and I/O blocking. On a CPU‑bound system, having more threads than cores leads to slower execution; however, when threads spend time blocked on disk or network I/O, a higher thread count can improve utilization.

Three primary bottlenecks are identified: CPU, disk, and network. Disk seeks and rotational latency dominate on HDDs, while SSDs reduce blocking, allowing fewer threads. Network bandwidth also contributes to I/O wait, but is usually a secondary concern.

Based on these observations, the author proposes a simple heuristic for calculating the optimal connection pool size: 连接数 = ((核心数 * 2) + 有效磁盘数) For a 4‑core i7 server with one effective disk, the formula yields 9 connections (rounded to 10). Testing shows that exceeding this number quickly degrades response time and throughput.

Additional recommendations include using separate pools for long‑running and short‑running transactions, matching the pool size to the number of concurrent queries the database can handle, and avoiding overly large pools that waste resources.