Question
Increase Pool Size or Reduce Transaction Scope?
Zo Dey
0 reputation · 06 Mar 2021, 02:50 UTC
134.9K views1
Loading…
0 reputation · 06 Mar 2021, 02:50 UTC
The goal is to pinpoint why a Hibernate application using HikariCP intermittently runs out of connections during peak load. The core uncertainty lies in whether the culprit is a short‑lived pool size or lingering long transactions that keep connections open.
Key constraints include the interplay of connectionTimeout versus maximumPoolSize, the eviction logic of idleTimeout and maxLifetime, and the server‑side wait_timeout that may silently close idle sockets.
To resolve the behavior, consider the following questions:
idleTimeout and maxLifetime prevents temporary gaps in available connections during heavy load?34775 reputation · 06 Mar 2021, 07:17 UTC
First, try to shrink the transaction scope. A smaller scope returns connections to the pool faster and usually resolves intermittent exhaustion. Only enlarge the pool after confirming that transaction length cannot be reduced further.
hibernate
lessActiveConnections and PendingRequests via JMX or its log output.ActiveConnections == maximumPoolSize and PendingRequests starts to grow.PendingRequests wait longer than connectionTimeout.wait_timeout can close idle sockets, but it does not cause the pool to report pending requests.When a Hibernate session (or @Transactional method) holds a connection for the duration of the transaction, the connection stays in ActiveConnections. If those transactions occasionally run long (e.g., due to lazy loading, batch processing, or OSIV keeping the session open during view rendering), the pool can exhaust even though the configured maximumPoolSize is adequate for short‑lived work.
HikariConfig config = new HikariConfig();
config.setLeakDetectionThreshold(3000); // ms
config.setLogConnectionState(true);ActiveConnections and PendingRequests during a peak load window.PendingRequests rises while ActiveConnections stays near maximumPoolSize, the bottleneck is transaction length.ActiveConnections stays well below the limit but PendingRequests still spikes, the pool may be too small for the concurrent request rate.What is the average duration of @Transactional methods (or Hibernate sessions) during the periods when timeouts occur? If the average is on the order of seconds or more, focus on reducing transaction scope; if it remains sub‑second and the pool still exhausts, consider increasing maximumPoolSize after verifying the database can handle the extra load.
PendingRequests with application‑level transaction timestamps.PendingRequests no longer grows.Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 06 Mar 2021, 08:04 UTC
When maxLifetime exceeds the database’s wait_timeout, the pool may hand out a connection that the server has already closed. The borrower then sees a SQL exception, often retries, which keeps the thread waiting and inflates PendingRequests even though ActiveConnections is below the limit. Setting maxLifetime a few seconds below wait_timeout (e.g., 280 s vs 300 s) lets HikariCP evict and replace the connection before the DB drops it, preventing spurious acquisition failures and reducing unnecessary queue growth.