MySQL Replication Lag: Why It Happens and How to Catch Up
Separates IO from SQL thread lag, finds common causes (big transactions, PK-less tables, single thread) and gives steps to catch up.
Operational Safety & Risk Notice
This runbook contains operational intervention steps; several are write-impacting (KILL, terminate, DROP, RESIZE). Verify in a staging environment first, and apply sequentially in production with verification.
1. Symptom & Signals
- `Seconds_Behind_Source` büyüyor (M15 > 30 sn).
- Replica'dan okuyan uygulamalar eski veri görüyor.
- Daha kötüsü: `Replica_IO_Running` veya `Replica_SQL_Running` = `No`.
2. Business & Technical Impact
Okuma tutarsızlığı, failover'da veri kaybı riski (RPO bozulur), yedek replica'dan alınıyorsa tutarsız yedek.
3. Diagnosis & Root-Cause Analysis
SHOW REPLICA STATUS\G -- 8.0.22 öncesi: SHOW SLAVE STATUS\GBakılacaklar: `Replica_IO_Running`, `Replica_SQL_Running`, `Seconds_Behind_Source`, `Last_IO_Error`, `Last_SQL_Error`, `Retrieved_Gtid_Set` / `Executed_Gtid_Set`.
- **IO gecikmesi:** `Retrieved` kaynağın gerisinde. Ağ veya kaynak/replica disk sorunu.
- **SQL (apply) gecikmesi:** `Retrieved` güncel, `Executed` geride. Replica olayları yeterince hızlı uygulayamıyor. **En yaygın durum.**
- **Thread durmuş:** `Last_SQL_Error` içinde nedene bakın (örn. 1062 duplicate key, 1032 satır bulunamadı).
- **Tek büyük transaction** (toplu UPDATE/DELETE, büyük `LOAD DATA`): `SHOW PROCESSLIST` içinde SQL thread aynı olayda uzun süredir.
- **Primary key'i olmayan tablolar** (S29): `row` formatında her satır için tam tablo taraması yapılır, çok yavaştır.
- **Paralel uygulama kapalı:** tek thread, kaynakta çok thread yazıyor.
- Replica disk I/O doygunluğu veya replica'da ek yük (raporlama sorguları, yedek).
4. Remediation & Action Plan
STOP REPLICA SQL_THREAD;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_preserve_commit_order = ON;
START REPLICA SQL_THREAD;(5.7: `slave_parallel_type`, `slave_parallel_workers`.)
SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;STOP REPLICA;
SET GTID_NEXT = '<uuid>:<n>';
BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;Atlamadan sonra `pt-table-checksum` ile tutarlılığı doğrulayın.
5. **Kalıcı çözüm:** Tüm tablolara primary key ekleyin; büyük toplu işlemleri küçük parçalara bölün (örn. 1000-5000 satırlık batch'ler); `binlog_transaction_dependency_tracking = WRITESET` (primary'de) paralelliği artırır.
5. Verification & Health Checks
- `Seconds_Behind_Source` düşüyor ve 0'a yaklaşıyor, M15 normale döndü.
- `Replica_IO_Running` ve `Replica_SQL_Running` = `Yes`, `Last_*_Error` boş.
- Gecikme kapandıktan sonra gevşetilen ayarlar eski haline getirildi.
6. Rollback & Contingency Plan
- Gevşetilen dayanıklılık ayarlarını eski haline alın: `sync_binlog = 1`, `innodb_flush_log_at_trx_commit = 1`.
- Paralel uygulama sorun çıkarırsa `replica_parallel_workers = 0` ile tek thread'e dönün.
- Atlanan GTID geri alınamaz; gerekirse replica'yı yeniden kurun.