Common Pitfalls in Read-Write Separation

Read-write separation architectures face challenges due to replication lag between master and replica nodes, leading to potential stale data reads.

Architectural Considerations

The primary goal of read-write separation is to distribute write load from the master node. Two common architectures exist:

1. Direct Client Connection: Simplified architecture with direct client-to-database connections. Offers better performance but requires client-side changes during topology changes.
2. Proxy-Based Architecture: Centralized control over backend topology. Requires robust proxy implementation to handle high availability scenarios.

Stale Read Solutions

Several strategies address replication lag issues:

1. Forced master query routing for time-sensitive operations
2. Artificial delay (sleep) before replica reads
3. Replication lag verification mechanisms
4. Combination with semi-synchronous replication
5. Binlog position/GTID wait strategies

Forced Master Query

Classify queries based on freshness requirements, directing critical operations to the master node while routing non-critical reads to replicas.

Sleep Strategy

Introduce artificial delays before replica reads. This approach provides no guarantees and is generally unreliable.

Replication Lag Verification

Use the following methods to confirm replication status:

1. Query SHOW SLAVE STATUS and check Seconds_Behind_Master
2. Compare binlog coordinates:
   • Master: Master_Log_File, Read_Master_Log_Pos
   • Replica: Relay_Master_Log_File, Exec_Master_Log_Pos
3. GTID set comparison:
   • Verify Retrieved_Gtid_Set equals Executed_Gtid_Set

Semi-Synchronous Integration

With semi-synchronous replication enabled, the master waits for atleast one replica to acknowledge receipt before committing transactions. This guarantees data consistency in single-replica setups but fails in multi-replica environments where unacknowledged replicas may still serve stale data.

Binlog Position Waiting

SELECT MASTER_POS_WAIT(file, position [, timeout]);

1. After transaction commit, retrieve current binlog coordinates from master
2. On replica, execute MASTER_POS_WAIT() with timeout
3. If returns >=0, execute query on replica; otherwise, fallback to master

This approach requires implementing flow control when timeout occurs.

GTID-Based Solution

MySQL 5.7.6+ supports transaction GTID tracking:

SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid_set, timeout);

1. Enable session_track_gtids=OWN_GTID to capture transaction GTID
2. On replica, wait until specified GTID is executed
3. Returns 0 for success, 1 for timeout

Advanced implementation details available in MySQL C API documentation.