主從同步的延遲問題、原因及解決方案

MySQL的主從同步在實際使用過程中會有從庫延遲的問題,那么為什么會有這種問題呢? 如何避免這種問題呢?

情況一: 從服務(wù)器配置過低導(dǎo)致延遲

這類延遲場景的出現(xiàn)往往是主節(jié)點擁有較大規(guī)格的配置，而只讀節(jié)點卻購買了一個最小規(guī)格的配置

只讀節(jié)點的數(shù)據(jù)為了和主節(jié)點保持同步，采用了MySQL binlog復(fù)制技術(shù)，由一個IO線程和一個SQL線程來完成，IO線程負責將主庫的binlog拉取到只讀節(jié)點，SQL線程負責消費這些binlog日志，這兩個線程會消耗掉只讀節(jié)點的IO資源，所以當只讀節(jié)點IOPS配置不夠的時候，則會導(dǎo)致只讀節(jié)點的數(shù)據(jù)出現(xiàn)延遲

解決辦法: 升級從服務(wù)器的配置,讓只讀節(jié)點的配置大于或者等于主節(jié)點的配置即可

情況二: 主庫的QPS過高導(dǎo)致只讀節(jié)點延遲

由于只讀節(jié)點與主庫的同步采用的是單線程同步，而主庫的壓力是并發(fā)多線程寫入，這樣勢必會導(dǎo)致只讀節(jié)點的數(shù)據(jù)延遲

解決辦法: 開啟只讀節(jié)點的并行復(fù)制 (mysql5.6.3以后支持多線程復(fù)制)

--------------------------------------------------------------------------------------------------------

拓展：

在MySQL5.6中，引入了并發(fā)復(fù)制，這個并發(fā)復(fù)制是數(shù)據(jù)庫級別的，這意味著一個SQL線程可以處理一個數(shù)據(jù)庫的連續(xù)事務(wù)，而不用等待其它數(shù)據(jù)庫完成。

這個版本的并發(fā)復(fù)制，可以理解成一個數(shù)據(jù)庫一個SQL線程。

其與并發(fā)有關(guān)的參數(shù)如下：

slave_parallel_workers #worker 線程個數(shù)

slave-checkpoint-group #隔多少個事務(wù)做一次

checkpointslave-checkpoint-period #隔多長時間做一次

checkpointslave-pending-jobs-size-max #分發(fā)給worker的、處于等待狀態(tài)的event的大小上限

MySQL5.6基于DATABASE級別的并發(fā)復(fù)制可以解決業(yè)務(wù)表放在不同的database下同步延遲的問題

但是在實際生產(chǎn)中大部分表還是放在同一個庫中的，這種情況即使設(shè)置slave_parallel_workers大于0，也無法進行并發(fā)。在高并發(fā)的情況下，依然會造成主從復(fù)制延遲.

MySQL 5.7版本才真正支持“真正”的并行復(fù)制功能.在MySQL5.7中，引入了新的并發(fā)復(fù)制方法，基于LOGICAL_CLOCK的并發(fā)復(fù)制，可以支持在一個database中，并發(fā)執(zhí)行relaylog中的事務(wù)。

相同的二進制日志組在master上提交并行應(yīng)用到slave節(jié)點上，沒有跨數(shù)據(jù)庫的限制，并且不需要把數(shù)據(jù)分割到多個數(shù)據(jù)庫。

要實現(xiàn)這個功能，需要在master節(jié)點標記binlog中提交的事務(wù)哪些是可以并發(fā)執(zhí)行，雖然的MySQL5.6中已經(jīng)引入binarylog group commit,但是沒有將可并發(fā)的事務(wù)標記出來。

在MySQL5.7中，已經(jīng)解決了主從復(fù)制延遲的問題，具體配置參數(shù)如下：

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=16

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

----------------------------------------------------------------------------------------------------

情況三: 主庫的DDL語句導(dǎo)致只讀節(jié)點延遲

可能1:只讀節(jié)點與主庫的DDL同步是串行進行的，如果DDL操作在主庫執(zhí)行時間很長，那么同樣在備庫也會消耗同樣的時間.

比如在主庫對一張500W的表添加一個字段耗費了10分鐘，那么在只讀節(jié)點上也同樣會耗費10分鐘，所以只讀節(jié)點會延遲600S

可能2：只讀節(jié)點上有一個執(zhí)行時間非常長的的查詢正在執(zhí)行，那么這個查詢會堵塞來自主庫的DDL，讀節(jié)點表被鎖，直到查詢結(jié)束為止，進而導(dǎo)致了只讀節(jié)點的數(shù)據(jù)延遲。

在只讀節(jié)點上可以通過執(zhí)行show processlist命令查看連接的狀態(tài)處于:

Waiting for table metadata lock

解決辦法: 對于可能1，只能說執(zhí)行操作之前對可能帶來的影響要有考量; 對于情況2，可以kill掉只讀節(jié)點上的大查詢進行，就可以恢復(fù)只讀節(jié)點與主節(jié)點的數(shù)據(jù)同步

情況四: 主庫執(zhí)行大事務(wù)導(dǎo)致延遲

主庫執(zhí)行了一條insert … select非常大的插入操作，該操作產(chǎn)生了近幾百G的binlog文件傳輸?shù)街蛔x節(jié)點，進而導(dǎo)致了只讀節(jié)點出現(xiàn)應(yīng)用binlog延遲。

解決辦法: 將大事務(wù)拆分成為小事務(wù)進行排量提交，這樣只讀節(jié)點就可以迅速的完成事務(wù)的執(zhí)行，不會造成數(shù)據(jù)的延遲。

情況五：無主鍵的表進行DML操作導(dǎo)致延遲

如:mysql> update test set kk='fafa01';

由于表中沒有主鍵，所以導(dǎo)致了每一個事務(wù)條目的更新都是全表掃描，如果表中很很多的數(shù)據(jù)，則備庫執(zhí)行該更新的事務(wù)條目的時候，就會出現(xiàn)很多的全表掃描更新；

進一步說明就是，由于表中沒有主鍵，在ROW模式下，每刪一條數(shù)據(jù)都會做全表掃，也就是說一條delete，如果刪了10條，會做10次全表掃，所以slave會一直卡??；

解決辦法: 每張表在設(shè)計的時候都加上一個主鍵

----------------------------------------------------------------------------------------------------------

拓展：

主鍵對于innodb來說，是非常重要的，每張表的設(shè)計的時候，都應(yīng)該把主鍵默認的加上，不管你需不需要他

主鍵的設(shè)計最好選擇自增型的主鍵

自增主鍵的好處：

a.自增型主鍵以利于插入性能的提高；

b.自增型主鍵設(shè)計(int,bigint)可以降低二級索引的空間，提升二級索引的內(nèi)存命中率；

c.自增型的主鍵可以減小page的碎片，提升空間和內(nèi)存的使用

------------------------------------------------------------------------------------------------------

四.總結(jié)

為了避免MySQL主從復(fù)制延遲,我們可以從以下幾方面入手:

1.數(shù)據(jù)庫設(shè)置: 主從同步加速

1).sync_binlog在slave端設(shè)置為0

2).–log-slave-updates 從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進制日志.

3).直接禁用slave端的binlog

4).slave端,如果使用的存儲引擎是innodb,innodb_flush_log_at_trx_commit =2

2.架構(gòu)方面:在架構(gòu)上做優(yōu)化,盡量讓主庫的DDL快速執(zhí)行,盡量減輕數(shù)據(jù)庫的壓力

1).業(yè)務(wù)的持久化層的實現(xiàn)采用分庫架構(gòu),mysql服務(wù)可平行擴展,分散壓力.

2).單個庫讀寫分離,一主多從,主寫從讀,分散壓力.這樣從庫壓力比主庫高,保護主庫.

3).服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和mysql之間加入memcache或者redis的cache層.降低mysql的讀壓力.

4).不同業(yè)務(wù)的mysql物理上放在不同機器,分散壓力.

3.硬件方面:使用比主庫更好的硬件設(shè)備作為slave

1).采用好服務(wù)器,比如4u比2u性能明顯好,2u比1u性能明顯好.

2).存儲用ssd或者盤陣或者san,提升隨機寫的性能.

3).主從間保證處在同一個交換機下面,并且是萬兆環(huán)境.文章來源地址http://www.zghlxwxcb.cn/news/detail-601921.html