MySQL Tuning, Indexing & Concurrency
8-10 Years (Tech Lead)
Hard
Production Scenario
How do you troubleshoot and fix MySQL Replication Lag in Master-Replica setups?
Real-World Scenario / Incident Description
Replica lag jumped to 3,600 seconds on a secondary reader server during nightly report generation.
687 views
•
Updated 1 month ago
Key Fixes for Replication Lag
- Enable Multi-Threaded Slaves (MTS):
slave_parallel_workers = 16 slave_parallel_type = LOGICAL_CLOCK - Eliminate Huge Single-Query Writes: Break massive 5-million-row
DELETEorUPDATEstatements into chunks of 5,000. - Disable Sync Binlog on Replicas: Set
sync_binlog = 0andinnodb_flush_log_at_trx_commit = 2on read replicas.
Related Concepts:
#Replication Lag
#Multi-Threaded Slave
#High Availability
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →