Incident Context

Friday night, 22:30. On the MySQL 5.7 read replica, Seconds_Behind_Master skyrocketed past 14,000 seconds. Read-only microservices began serving stale profile states, database caches flooded, and on-call alerts escalated immediately.

Diagnostics and Thread Inspection

We inspected the replication worker state:

BASH 
mysql -e "SHOW SLAVE STATUS\G"
mysql -e "SHOW FULL PROCESSLIST;"

Output showed the SQL thread blocked on a single massive unbatched statement:

TEXT 
System lock | UPDATE account_balances SET last_audit_ts = NOW() WHERE audit_batch = 4821;

Developers ran an unpaginated batch updating 2.4 million rows. While the master absorbed it on NVMe RAID10 across multiple threads, the replica executed it in a single thread due to MySQL 5.7 schema-based replication limitations.

Disk metrics via iostat -xz 1 revealed 99.4% disk utilization with 50ms write latency:

TEXT 
Device  r/s  w/s   rMB/s  wMB/s  await  r_await  w_await  %util
nvme0n1 12   450   0.1    85.2   48.2   1.2      49.8     99.4%

fsync & doublewrite Bottleneck

The replica was running with maximum durability:

INI 
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1

Flushing huge dirty page bursts forced serial fsync() syscalls in the doublewrite buffer. A gdb backtrace confirmed the block:

TEXT 
#0  0x00007f9c8d10b70b in fsync () from /lib/x86_64-linux-gnu/libc.so.6
#1  0x000055c1e2891f1a in os_file_flush_func ()
#2  0x000055c1e29e012c in fil_flush ()
#3  0x000055c1e27a988d in buf_dblwr_flush_buffered_writes ()

Resolution & Mitigations

  • Temporarily relaxed durability settings during recovery: sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2.
  • Scaled innodb_io_capacity = 4000 to drain the queue.
  • Migrated to MySQL 8.0 WRITESET based multi-threaded replication.
  • Mandated batch updates strictly capped at 5,000 rows.