MySQL主從延遲解決方法的歸納和總結

2024-07-24 12:32:12

字體：大中小

來源：轉載

供稿：網友

MySQL主從延遲情況共分為以下四類
一.MIXED模式無索引或SQL慢
在從庫上
pager grep -v Sleep;show processlist 查看到正在執行的SQL
解決方法:
1.SQL比較簡單，則檢查否缺少索引，并添加索引。例如update操作where條件無索引。
2.SQL比較復雜，例如insert into select from語句，如果select里包含group by,多表關聯，可能效率會比較低。這類可以到主庫把binlog_format改為row.

二.主庫上有大事務，導致從庫延時
解決方法：
找開發人員溝通，增加緩存，異步寫入數據庫，減少對DB的大量寫入。

三.主庫寫入頻繁，從庫壓力跟不上主庫導致延時
此類原因的主要現象是數據庫的IUD（增改刪）操作非常多，slave由于sql_thread單線程的原因追不上主庫的寫操作。
解決方法：
1.升級主庫的硬件配置,比如ssd、fio
2.設置日志刷新頻率:
set global_sync_binlog=0;
  innodb_flush_log_at_trx_commit=0每秒將log_buffer的內容刷新到logfile,并且將日志同步刷新到磁盤。該模式下，在事務提交的時候，不會主動觸發寫入磁盤的操作。
  innodb_flush_log_at_trx_commit=1每次commit將log_buffer刷新到logfile,并且將日志同步刷新到磁盤。
  innodb_flush_log_at_trx_commit=2每次commit將log_buffer刷新到logfile,但是flush(刷到磁盤)操作并不會同時進行，每秒將日志刷新到磁盤。

四.數據庫中存在大量myisam表，在備份的時候導致從庫延遲
由于xtrabackup工具備份到最后會執行flush tables with read lock,對數據庫進行鎖表以便進行一致性備份，然后對于myisam表鎖，會阻礙slave sql_thread線程停滯運行而導致hang。
該問題目前比較好的解決方法是修改表結構為innod存儲引擎的表。

（編輯：武林網）

上一篇：MySQL Bug導致異常宕機的分析流程

下一篇：MySQL基礎創建bc網站sql執行錯誤時的控制方式