Operating Production Databases: High Availability Replication & Connection Pooling

At 11:45 PM, a hardware failure knocks out your primary MySQL database server.

Your automated failover orchestrator detects the failure, promotes a read replica to become the new primary, and updates DNS endpoints.

When application traffic resumes, your finance team discovers a critical issue: 1,500 completed customer transactions processed in the 10 seconds before the crash are completely missing from the new primary node.

Simultaneously, 200 web application pods restart, each attempting to establish 50 fresh database connections.

MySQL immediately rejects incoming connections with:

ERROR 1040 (08004): Too many connections

Database CPU utilization hits 100% due to thread context-switching overhead, leaving the entire system unresponsive.

What operational failure modes caused data loss during failover and connection pool collapse during restart?

To run production database clusters reliably, you must master High Availability Replication Protocol Mechanics and Database Connection Management.

1. MySQL Binary Logging (Binlog) Architecture

Replication in MySQL relies on the Binary Log (Binlog), a append-only transaction log maintained by the MySQL Server Layer.

[ Primary Node ]                                           [ Replica Node ]

 +------------------+     Network Stream     +---------------+     +---------------+
 | Primary Binlog   | ---------------------> | Relay Log     | --> | SQL Applier   |
 | Write-Ahead Log  |  (Binlog Dump Thread)  | (Local Disk)  |     | Thread (RAM)  |
 +------------------+                        +---------------+     +---------------+

The Three Binlog Formats

Statement-Based Logging (SBR): Records raw SQL statements (e.g., UPDATE users SET status = 'ACTIVE' WHERE age > 30).
- Risk: Non-deterministic functions like NOW(), UUID(), or RAND() generate different values on replicas, causing data drift!
Row-Based Logging (RBR): Records explicit byte-level row changes before and after modification.
- Advantage: Guarantees exact data consistency across primary and replicas.
- Standard: Industry standard for production systems.
Mixed Logging: Uses Statement-based by default, automatically switching to Row-based for non-deterministic queries.

2. Replication Synchronization Models

How data moves from the Primary Binlog to Replica nodes determines whether failover can cause data loss.

A. Asynchronous Replication (Default)

Primary commits the transaction locally, flushes to Binlog, and immediately returns success to the client application.
The replica fetches binlog events asynchronously via network background threads.

Client               Primary Node            Replica Node
  |                       |                       |
  |--- 1. COMMIT -------->|                       |
  |<-- 2. SUCCESS --------|                       | (Network Latency Lag)
  |                       |--- 3. Send Binlog --->|

Risk: If the primary crashes before sending binlog events to replicas, un-replicated transactions are permanently lost.

B. Semi-Synchronous Replication

Primary holds the client response until at least one replica acknowledges receiving the binlog events in its local Relay Log.

Client               Primary Node            Replica Node
  |                       |                       |
  |--- 1. COMMIT -------->|                       |
  |                       |--- 2. Send Binlog --->|
  |                       |<-- 3. ACK (Relay Log)-|
  |<-- 4. SUCCESS --------|                       |

Guarantee: Ensures zero transaction loss during primary failover because at least one surviving replica holds a copy of every committed transaction in its Relay Log.

3. Global Transaction Identifiers (GTID)

Legacy replication tracked position using file offsets (e.g., mysql-bin.000004, position 10425). If a primary crashed, re-pointing replicas to a new primary required manual byte offset calculations.

Modern MySQL clusters use Global Transaction Identifiers (GTIDs):

$\text{GTID} = \text{Server\_UUID} : \text{Transaction\_ID}$

Example: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-45

Advantages of GTID-Based Failover

Every transaction across the entire cluster has a unique global identifier.
When promoting a replica to primary, surviving replicas simply present their executed GTID sets. The new primary automatically streams only missing GTID payloads.

4. Database Connection Management & Pooling

Opening a raw database TCP connection requires network handshakes, TLS negotiation, authentication, and memory allocation for thread buffers.

If 1,000 application threads open 1,000 raw connections, the database CPU wastes most cycles context-switching between 1,000 active kernel threads.

                      +-----------------------------+
                      |    Application Microservices |
                      +-----------------------------+
                                     |
                                     v
                      +-----------------------------+
                      |   Connection Pool (RAM)     |  (e.g., HikariCP / ProxySQL)
                      |   [ Fixed Pool: 20 Conns ]  |
                      +-----------------------------+
                                     |  Reuses Warm TCP Socket Connections
                                     v
                      +-----------------------------+
                      |    MySQL Primary Server     |
                      +-----------------------------+

Client-Side Pooling (HikariCP) vs Proxy-Side Pooling (ProxySQL)

Client-Side Pool (HikariCP): Maintains a warm pool of pre-established TCP connections within the application JVM, eliminating connection creation overhead.
Proxy-Side Pool (ProxySQL / PgBouncer): Sits between microservice pods and the database cluster. Hundreds of ephemeral pod connections multiplex over a small pool of persistent database backend connections.

The Connection Pool Sizing Formula

A common operational mistake is setting connection pools too large (e.g., maxPoolSize = 200).

According to PostgreSQL and MySQL core engineering benchmarks, optimal connection pool size is calculated using the following hardware formula:

$\text{Optimal Pool Size} = (\text{CPU Cores} \times 2) + \text{Effective Spindle Count}$

Example Calculation

For a database server with 16 CPU cores and fast NVMe storage ( $\text{Spindle Count} = 1$ ):

$\text{Connections} = (16 \times 2) + 1 = 33 \text{ Connections}$

A pool size of 33 connections delivers higher throughput and lower query latency than a pool size of 500 connections because it eliminates CPU thread context switching and disk queue contention!

Summary & Next Steps

Operating production databases requires balancing data safety against throughput constraints:

Row-Based Binlog Logging (RBR) guarantees deterministic replica state across cluster nodes.
Semi-Synchronous Replication with GTID prevents transaction loss during primary host failover.
Connection Pools (HikariCP, ProxySQL) protect database CPUs against thread context-switching thrashing.
Proper Pool Sizing ( $2 \times \text{Cores} + 1$ ) maximizes database throughput.

In the next article, we examine Advanced SQL Performance Tuning: Window Functions, Recursive CTEs, and Partitioning.

References & Further Reading

Wooldridge, B. (2020). Down the Rabbit Hole: Performance Tuning & Bytecode Optimizations in HikariCP. GitHub.
PgBouncer Project. PgBouncer Architecture and Transaction Mode Limitations. PgBouncer Docs.
Oracle Corporation. Oracle JDBC Developer’s Guide: Connection Pooling. Oracle Docs.