Getting replicas back in sync and keeping them there

Overview

A marketplace operator served product search and reporting from read replicas. During nightly inventory reconciliation, replica lag grew past the point where reads returned stale data that customers noticed. AceMQ was engaged to find and fix the cause.

Challenge

The replication stream contained very large single transactions from batch jobs, and apply on the replica was effectively serialized behind them. Row-based binary logging amplified the volume further because the batch updated wide rows. Read traffic was routed to replicas without any lag awareness, so stale data reached the application unnoticed until customers reported it.

Environment

MySQL source and replica topology on AWS supporting catalog, search, and reporting workloads.

Approach

AceMQ instrumented apply throughput separately from network delivery to establish where the lag was accumulating, then attacked both sides: making the write stream easier to apply in parallel, and making the read tier aware of lag so stale data could not be served silently.

Solution

• Separated network delivery lag from apply lag to confirm the bottleneck was replica apply, not transport
• Enabled parallel replication with writeset-based dependency tracking and sized worker threads to the hardware
• Broke batch jobs into bounded transaction sizes so a single statement could not serialize the apply stream
• Reviewed binary log format and row image settings to cut replication volume from wide-row updates
• Configured lag-aware read routing at the proxy so replicas past a threshold are removed from the read pool
• Added apply-lag alerting distinct from the existing seconds-behind-source metric

Outcome

Technologies