《Mysql應用解決MySQL中的Slave延遲問題的基本教程》要點：
本文介紹了Mysql應用解決MySQL中的Slave延遲問題的基本教程，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL學習

MYSQL學習一、原因分析
一般而言,slave相對master延遲較大,其根本原因就是slave上的復制線程沒方法真正做到并發(fā).簡單說,在master上是并發(fā)模式(以InnoDB引擎為主)完成事務提交的,而在slave上,復制線程只有一個sql thread用于binlog的apply,所以難怪slave在高并發(fā)時會遠落后master.

MYSQL學習ORACLE MySQL 5.6版本開始支持多線程復制,配置選項 slave_parallel_workers 即可實現(xiàn)在slave上多線程并發(fā)復制.不過,它只能支持一個實例下多個 database 間的并發(fā)復制,并不能真正做到多表并發(fā)復制.因此在較大并發(fā)負載時,slave還是沒有方法及時追上master,需要想方法進行優(yōu)化.

MYSQL學習另一個重要原因是,傳統(tǒng)的MySQL復制是異步(asynchronous)的,也就是說在master提交完后,才在slave上再應用一遍,并不是真正意義上的同步.哪怕是后來的Semi-sync Repication(半同步復制),也不是真同步,因為它只保證事務傳送到slave,但沒要求等到確認事務提交成功.既然是異步,那肯定多少會有延遲.因此,嚴格意義上講,MySQL復制不能叫做MySQL同步(處女座的面試官有可能會在面試時把說成MySQL同步的一律刷掉哦).

MYSQL學習另外,不少人的觀念里,slave相對沒那么重要,因此就不會提供和master相同配置級別的服務器.有的甚至不但使用更差的服務器,而且還在上面跑多實例.

MYSQL學習綜合這兩個主要原因,slave想要盡可能及時跟上master的進度,可以嘗試采用以下幾種辦法：

MYSQL學習采用MariaDB發(fā)行版,它實現(xiàn)了相對真正意義上的并行復制,其效果遠比ORACLE MySQL好的很多.在我的場景中,采用MariaDB作為slave的實例,幾乎總是能及時跟上master.每個表都要顯式指定主鍵,如果沒有指定主鍵的話,會導致在row模式下,每次修改都要全表掃描,尤其是大表就非?？膳铝?延遲會更嚴重,甚至導致整個slave庫都被掛起,可參考案例：mysql主鍵的缺少導致備庫hang;
應用程序端多做些事,讓MySQL端少做事,尤其是和IO相關的活動,例如：前端通過內存CACHE或者本地寫隊列等,合并多次讀寫為一次,甚至消除一些寫哀求;
進行合適的分庫、分表策略,減小單庫單表復制壓力,避免由于單庫單表的的壓力導致整個實例的復制延遲;
其他提高IOPS性能的幾種方法,根據(jù)效果優(yōu)劣,我做了個簡單排序：
更換成SSD,或者PCIe SSD等IO設備,其IOPS能力的提升是普通15K SAS盤的數(shù)以百倍、萬倍,甚至幾十萬倍計;
加大物理內存,相應提高InnoDB Buffer Pool大小,讓更多熱數(shù)據(jù)放在內存中,降低發(fā)生物理IO的頻率;
調整文件系統(tǒng)為 XFS 或 ReiserFS,相比ext3可以極大程度提高IOPS能力.在高IOPS壓力下,相比ext4有更穩(wěn)健的IOPS表現(xiàn)(有人認為 XFS 在特別的場景下會有很大的問題,但我們除了剩余磁盤空間少于10%時引發(fā)丟數(shù)據(jù)外,其他的尚未遇到);
調整RAID級別為raid 1+0,它相比raid1、raid5等更能提高IOPS性能.如果已經(jīng)全部是SSD設備了,可以2塊盤做成RAID 1,或者多快盤做成RAID 5(并且可以設置全局熱備盤,提高陣列容錯性),甚至有些土豪用戶直接將多塊SSD盤組成RAID 50;
調整RAID的寫cache策略為WB或FORCE WB,詳情請參考：常用PC服務器陣列卡、硬盤健康監(jiān)控 以及 PC服務器陣列卡管理簡易手冊;
調整內核的io scheduler,優(yōu)先使用deadline,如果是SSD,則可以使用noop策略,相比默認的cfq,個別請客下對IOPS的性能提升至少是數(shù)倍的.

MYSQL學習二 、如何辦理
平時接收的比較多關于主備延時的報警:

MYSQL學習
check_ins_slave_lag (err_cnt:1)critical-slavelag on ins:3306=39438

MYSQL學習相信slave 延遲是MySQL dba 遇到的一個老生長談的問題了.先來分析一下slave延遲帶來的風險
? a. 異常情況下,主從HA無法切換.HA 軟件需要檢查數(shù)據(jù)的一致性,延遲時,主備不一致.
? b. 備庫復制hang會導致備份失敗(flush tables with read lock會900s超時)
? c. 以 slave 為基準進行的備份,數(shù)據(jù)不是最新的,而是延遲.
面對此類問題我們如何辦理 ,如何規(guī)避?分析一下導致備庫延遲的幾種原因
1. ROW模式無主鍵、無索引或索引區(qū)分度不高.

MYSQL學習有如下特征
?? a. show slave status 顯示position一直沒有變
?? b. show open tables 顯示某個表一直是 in_use 為 1
?? c. show create table 查看表結構可以看到無主鍵,或者無任何索引,或者索引區(qū)分度很差.

MYSQL學習解決辦法：
?? a. 找到表區(qū)分度比較高的幾個字段, 可以使用這個辦法判斷：
????

MYSQL學習
select count(*) from xx; 
  select count(*) from (select distinct xx from xxx) t;

MYSQL學習??? 如果2個查詢count(*)的結果差不多,說明可以對這些字段加索引
?? b. 備庫stop slave;
??? 可能會執(zhí)行比較久,因為需要回滾事務.
?? c. 備庫
?

MYSQL學習
  set sql_log_bin=0;
  alter table xx add key xx(xx);

MYSQL學習?? 老的版本slave應用binlog時只會選擇第一個索引,需要把新加的索引放在最前面,可以先把老的索引刪掉,建新的索引,再把老的索引建上.可以放到一個sql中執(zhí)行.
? d. 備庫start slave
??? 如果是innodb,可以通過show innodb status來查看 rows_inserted,updated,deleted,selected這幾個指標來判斷.
??? 如果每秒修改的記錄數(shù)比較多,說明復制正在以比較快的速度執(zhí)行.

MYSQL學習2 MIXED模式無索引或SQL慢
?? 在從庫上show full processlist 查看到正在執(zhí)行的SQL.
解決辦法：
? a.? SQL比較簡單, 則檢查是否缺少索引,并添加索引.
? b. 另一類是 insert into select from的語句,如果select 里包含group by,多表關聯(lián),可能效率會比較低.
????? 這類可以到主庫把binlog_format改成row.

MYSQL學習3 主庫上有大事務,導致從庫延時
現(xiàn)象解析binlog 發(fā)現(xiàn)類似于下圖的情況看

MYSQL學習

MYSQL學習解決辦法：
與開發(fā)溝通,增加緩存,異步寫入數(shù)據(jù)庫,減少直接對db的大量寫入.

MYSQL學習4. 主庫寫入頻繁,從庫壓力跟不上導致延時
? 此類原因的主要現(xiàn)象是數(shù)據(jù)庫的 IUD 操作非常多,slave由于sql_thread單線程的原因追不上主庫.
?解決辦法：
?a 升級從庫的硬件配置,比如ssd,fio.
?b 使用@丁奇的預熱工具-relay fetch
?? 在備庫sql線程執(zhí)行更新之前,預先將相應的數(shù)據(jù)加載到內存中,并不能提高sql_thread線程執(zhí)行sql的能力,也不能加快io_thread線程讀取日志的速度.
?c 使用多線程復制 阿里MySQL團隊實現(xiàn)的方案--基于行的并行復制.
?? 該方案允許對同一張表進行修改的兩個事務并行執(zhí)行,只要這兩個事務修改了表中的不同的行.這個方案可以達到事務間更高的并發(fā)度,但是局限是必須使用Row格式的binlog.因為只有使用????? Row格式的binlog才可以知道一個事務所修改的行的范圍,而使用Statement格式的binlog只能知道修改的表對象.

MYSQL學習5. 數(shù)據(jù)庫中存在大量myisam表,在備份的時候導致slave 延遲

MYSQL學習

MYSQL學習由于xtrabackup 工具備份到最后會執(zhí)行flash tables with read lock ,對數(shù)據(jù)庫進行鎖表以便進行一致性備份,然后對于myisam表 鎖,會阻礙salve_sql_thread 停滯運行進而導致hang
該問題目前的比較好的辦理方式是修改表結構為innodb存儲引擎的表.

維易PHP培訓學院每天發(fā)布《Mysql應用解決MySQL中的Slave延遲問題的基本教程》等實戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/9552.html