Database Connection Pool Health Check and Failover Mechanism

I. Problem Description
Health checks and failover for database connection pools are core mechanisms to ensure high availability of applications. When a database node fails or network anomalies occur, the connection pool needs to automatically detect abnormal connections, remove failed nodes, and switch requests to healthy nodes. This process involves connection validity verification, heartbeat detection, failure determination strategies, and seamless switchover implementation.

II. Health Check Mechanism

1. Connection Validity Verification

   • Timing: Executed before lending a connection to the application (borrow), when returning a connection (return), or periodically during idle times.
   • Method: Execute a lightweight SQL query (e.g., SELECT 1) or call the database driver's isValid() method. If the query times out or fails, mark the connection as invalid.
   • Optimization: Avoid frequent verification adding overhead by controlling the check interval via parameters (e.g., validationInterval).

2. Heartbeat Detection (Keepalive)

   • Active Heartbeat: The connection pool periodically sends heartbeat packets to the database to monitor network connectivity and database status.
   • Passive Detection: Listen for abnormal events actively pushed by the database server (e.g., ConnectionReset exception triggered when the database actively disconnects).
   • Timeout Configuration: Set maxLifetime to forcibly recycle old connections, avoiding "zombie connections" caused by database restarts.

III. Failover Strategy

1. Failure Determination Conditions

   • Consecutive Failure Threshold: If a node fails N consecutive health checks, it is determined to be faulty.
   • Timeout Ratio: If the proportion of failed requests exceeds a threshold (e.g., 50%) within a time window, trigger failover.
   • Example: HikariCP's connectionTimeout and healthCheckRegistry allow customization of determination logic.

2. Connection Pool Response Actions

   • Remove Faulty Node: Remove the faulty node from the available connection list; new requests are no longer assigned its connections.
   • Graceful Shutdown: Asynchronously close connections of the faulty node to avoid blocking application threads.
   • Logging and Alerts: Record failure information and notify the operations system.

IV. Failover Implementation Patterns

1. Application Layer Switch

   • Multi-DataSource Configuration: Configure primary/standby database connection pools, and dynamically switch data sources via annotations (e.g., @Primary) or routing logic (e.g., Spring's AbstractRoutingDataSource).
   • Code Example:

     public class DynamicDataSource extends AbstractRoutingDataSource {
         @Override
         protected Object determineCurrentLookupKey() {
             return DatabaseContextHolder.getDataSourceType(); // Get current data source from thread context
         }
     }
     

2. Middleware Proxy

   • Proxy Layer Routing: Use middleware like MySQL Router or ProxySQL to automatically forward read/write requests to healthy nodes.
   • Advantage: Transparent to the application, enabling failover without code changes.

3. Driver-Level Support

   • Database Driver High Availability: E.g., PostgreSQL's libpq supports multi-node configuration; MySQL Connector/J's ReplicationConnection can automatically switch to a replica.
   • Configuration Example (MySQL JDBC URL):

     jdbc:mysql:replication://master,slave1,slave2/db?autoReconnect=true&failOverReadOnly=false
     

V. Fault Tolerance and Recovery Mechanisms

1. Failure Recovery Detection

   • Periodic Retry: Periodically attempt to re-establish connections to nodes marked as faulty (e.g., every 30 seconds).
   • Recovery Condition: After passing M consecutive health checks, re-add the node to the connection pool.

2. Avoiding Split-Brain Problem

   • Consensus Decision: Ensure multiple application instances have a consistent view of the failure state via distributed locks or coordination services (e.g., ZooKeeper).
   • Example: In a microservices cluster, uniformly publish the list of faulty nodes via a configuration center.

VI. Practical Considerations

1. Prevent Resource Leaks: Ensure faulty connections are completely released during failover to avoid memory leaks.
2. Performance Balance: Reasonably set the health check frequency to avoid impacting database performance due to excessive checks.
3. Testing and Validation: Use Chaos Engineering tools (e.g., ChaosBlade) to simulate network latency and database outages, verifying the effectiveness of failover.

Through the above steps, the connection pool can respond quickly to database anomalies, ensuring application continuity and data consistency.