Getting replicas back in sync and keeping them there
PrometheusLinuxPeak replica lag during batch windows dropped from hours to well under a minute, and stale reads stopped reaching customers. The lag-aware routing now fails safe even when a replica does fall behind.
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
- 1Separated network delivery lag from apply lag to confirm the bottleneck was replica apply, not transport
- 2Enabled parallel replication with writeset-based dependency tracking and sized worker threads to the hardware
- 3Broke batch jobs into bounded transaction sizes so a single statement could not serialize the apply stream
- 4Reviewed binary log format and row image settings to cut replication volume from wide-row updates
- 5Configured lag-aware read routing at the proxy so replicas past a threshold are removed from the read pool
- 6Added apply-lag alerting distinct from the existing seconds-behind-source metric
Outcome
Peak replica lag during batch windows dropped from hours to well under a minute, and stale reads stopped reaching customers. The lag-aware routing now fails safe even when a replica does fall behind.
Technologies
PrometheusLinuxRelated Use Cases
MySQL InnoDB Lock Contention Support
Ongoing support for deadlocks and lock wait timeouts under booking concurrency, including history list growth from long-running transactions.
MySQL Topology and Backup Recoverability Assessment
Review of replication topology, failover readiness, backup recoverability, and configuration drift across a MySQL estate that had grown organically.
Facing a MySQL Production Issue?
AceMQ's senior MySQL engineers have handled this exact type of engagement before. Whether you need architectural guidance, hands-on remediation, or an ongoing managed partnership, we're ready to help.