Introduction to Client Connection Pools

Core Functions of Connection Pools

A connection pool is an intermediate layer that maintains a group of reusable physical connections between applications and databases. Its core functions are as follows:

• Reducing connection setup overhead: Established connections are reused, eliminating the need for TCP handshakes and authentication for each request, thereby reducing latency and database load.
• Controlling concurrency and resource usage: The maximum number of connections is used to limit application concurrency, preventing burst traffic from exhausting database connections and ensuring stability for the database and other tenants.
• Improving stability: Connection reuse reduces connection setup jitter. In combination with keepalive and reconnection, connections can automatically recover after a primary/standby switchover or network jitter.
• Unifying resource management: Connections are centrally managed, including acquisition, return, recycling, and leakage detection, to reduce connection leakage risks.

Invalid Connection Scenarios

Physical connections are not permanently available after being established. A connection may become invalid without being detected in the following scenarios:

• After a primary/standby switchover, instance restart, or maintenance, the endpoint to which the original connection points is no longer reachable.
• Network middleware (such as NAT, firewalls, and load balancers) unidirectionally drops TCP connections due to extended idleness.
• The database proactively closes idle or timed-out connections, or transient network interruptions cause connections to drop.

If the connection pool is not configured with keepalive, service threads may acquire invalid connections, resulting in communication exceptions on the first execution. These failures occur in batches and intermittently, making troubleshooting difficult.

Configuration Principles

The default behavior of keepalive and reconnection varies among connection pools (some use idle validation by default, some use borrow validation, and some do not perform validation by default). Therefore, you cannot rely on the default settings. Instead, you should explicitly configure the validation SQL, idle validation, keepalive, and reconnection parameters during access, and verify the actual behavior by referring to the official documentation of the tool in use.

• Keepalive detection

  Keepalive uses validation SQL statements to check the validity of connections before use or periodically. Invalid connections are removed or re-established before being delivered to services, ensuring that only available connections are obtained by services. A proper combination is as follows:

  • Idle validation is used as a routine safeguard, which has low overhead and does not block service threads.
  • Keepalive heartbeats are used to keep persistent connections active, adapting to firewall idle disconnections and primary/standby switchovers.
  • Borrow-time validation is weighed against specific scenarios. It is usually disabled for high-concurrency production, leaving idle validation and keepalive mechanisms to cover the risk.
• Reconnection

  Keepalive addresses individual connection failures. When the entire database is unreachable (for example, during a switchover window or maintenance), the reconnection mechanism is required to ensure recovery. Automatic reconnection at the connection pool layer continuously retries during the fault period. After the fault is rectified, the connection is automatically restored, and services can self-heal without the need to restart. If reconnection is disabled, the application must be restarted upon each fault, significantly increasing the recovery cost.

• Lifecycle

  Both keepalive and reconnection are reactive measures, whereas lifecycle management complements them from a proactive prevention perspective. You can set a maximum lifetime to prevent persistent connections from deteriorating due to session status accumulation and route changes, and adjust the keepalive heartbeat interval based on the upstream idle disconnection threshold.

Key Configuration Points

• Keepalive: Explicitly set a validation SQL statement (for example, SELECT 1). Enable idle validation and keepalive heartbeats. By default, borrow-time validation is disabled. Enable or disable it based on the scenario.
• Reconnection: Enable automatic reconnection at the connection pool layer (no interruption upon failure). Ensure that the URL does not depend on driver-level autoReconnect.
• Lifecycle: Set a maximum lifetime and adjust the keepalive heartbeat interval based on the upstream idle disconnection threshold.

Configuration Check

When connecting to a connection pool, you are advised to check the following items one by one:

1. The validation SQL statement has been set and is a lightweight query.
2. Idle validation has been enabled, and the eviction check interval is appropriate.
3. The keepalive heartbeat for persistent connections has been enabled, and the interval is shorter than the upstream idle disconnection threshold.
4. The maximum number of connections meets the database capacity constraints with reserved headroom.
5. Automatic reconnection at the connection pool layer has been enabled (no interruption upon failure). The URL does not depend on driver-level autoReconnect.
6. The maximum lifetime has been set, and monitoring statistics and slow SQL thresholds have been enabled.