HikariCP Tuning: Mastering the Connection Pool
In high-traffic Java applications, the Database Connection Pool (usually HikariCP) is often the silent bottleneck. If misconfigured, your app won't crash with an error; it will simply hang, with threads waiting indefinitely for a connection that never comes.
1. The Small Pool Paradox
The most common mistake is creating a pool that is too large.
- The Myth: More connections = more throughput.
- The Reality: Database engines (like Postgres) use a fixed number of workers. If you have 500 connections hitting a 16-core DB, the CPU spends all its time context switching.
- The Formula: A good starting point is
connections = ((core_count * 2) + effective_spindle_count). For most apps, a pool of 20-30 connections is faster than 100.
2. Diagnosing Connection Leaks
A connection leak happens when your code fetches a connection but never returns it to the pool (e.g., forgetting to close a ResultSet or an unhandled exception).
- Detection: Enable
leak-detection-thresholdin your configuration.
spring.datasource.hikari.leak-detection-threshold: 2000 # 2 seconds
If a connection is held longer than 2s without being closed, HikariCP will log a stack trace showing exactly where the leak started.
3. Key Parameters to Tune
maximumPoolSize: The absolute cap on connections. Keep this small.connectionTimeout: How long a thread will wait for a connection before throwing an exception (Default 30s). In production, set this to 2-5 seconds to fail fast.idleTimeout: How long a connection can sit unused before being retired.
4. Monitoring the "Wait State"
The most critical metric to monitor is ConnectionWaitTime. This is the time an application thread spends waiting for a connection from the pool. If this rises while your DB CPU is low, you need a slightly larger pool. If it rises while your DB CPU is 100%, you need to optimize your SQL, not your pool.
Summary
HikariCP is the fastest connection pool in the world, but it can't fix a slow database or a leaky application. Size your pool based on your database's core count, enable leak detection, and always monitor your wait times to ensure your app stays responsive under load.
Next: Java Garbage Collection: G1 vs. ZGC Tuning Previous: Java Virtual Threads: High-Concurrency Loom
