mysql主從同步延遲原因

導(dǎo)致備庫(kù)延遲的原因主要有如下幾種：

成都創(chuàng)新互聯(lián)公司是一家專(zhuān)業(yè)提供寧陜企業(yè)網(wǎng)站建設(shè),專(zhuān)注與網(wǎng)站建設(shè)、成都做網(wǎng)站、html5、小程序制作等業(yè)務(wù)。10年已為寧陜眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專(zhuān)業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。

• 通常備庫(kù)所在機(jī)器的性能要比主庫(kù)所在的機(jī)器性能差，執(zhí)行備份自然會(huì)更慢。
• 備庫(kù)的讀壓力大。在備庫(kù)過(guò)多的執(zhí)行繁重的查詢?nèi)蝿?wù)。
• 大事務(wù)。因?yàn)橹鲙?kù)上必須等事務(wù)執(zhí)行完成才會(huì)寫(xiě)入 binlog，再傳給備庫(kù)。一次性地用 delete 語(yǔ)句刪除太多數(shù)據(jù)、表 DDL都可能造成延遲。
• 主庫(kù)是多線程操作，而從庫(kù)卻只有一個(gè)線程在執(zhí)行復(fù)制。

主從同步延遲解決方案

解決方案：

• 提升從庫(kù)物理機(jī)的配置，性能差異不要太大。
• 業(yè)務(wù)的持久化層的實(shí)現(xiàn)采用分庫(kù)架構(gòu)，mysql服務(wù)可平行擴(kuò)展，分散壓力。
• 采用讀寫(xiě)分離，分散主庫(kù)壓力。
• 加入緩存如Redis等，降低mysql的讀壓力。
• 避免執(zhí)行大事務(wù)等費(fèi)時(shí)的操作，可以將事務(wù)內(nèi)容拆開(kāi)執(zhí)行。
• 使用同步并行復(fù)制方案MTS

并行復(fù)制

從MySQL5.6開(kāi)始支持并行復(fù)制，這就解決了之前復(fù)制速度緩慢的問(wèn)題。coordinator 就是原來(lái)的 sql_thread, 他負(fù)責(zé)讀取中轉(zhuǎn)日志和分發(fā)事務(wù)。真正更新日志的，變成了 worker 線程。work 線程的個(gè)數(shù)由參數(shù) slave_parallel_workers 決定的。既然是并行就一定會(huì)有數(shù)據(jù)一致性的問(wèn)題，兩個(gè)不同的事務(wù)如果在不同的work中同時(shí)執(zhí)行，順序的影響也會(huì)造成結(jié)果不同。

所以在 coordinator 分發(fā)任務(wù)的時(shí)候，要滿足以下這兩個(gè)基本要求：

• 不能造成更新覆蓋。這就要求更新同一行的兩個(gè)事務(wù)，必須被分發(fā)到同一個(gè) worker 中。
• 同一個(gè)事務(wù)不能被拆開(kāi)，必須放到同一個(gè) worker 中。

各個(gè)版本的多線程復(fù)制，都遵循了這兩條基本原則。

MySQL 5.6版本策略

官方 MySQL5.6 版本，支持了并行復(fù)制，只是支持的粒度是按庫(kù)并行。用于決定分發(fā)策略的 hash 表里，key 就是數(shù)據(jù)庫(kù)名，同一個(gè)數(shù)據(jù)庫(kù)需要在同一個(gè)worker中串行執(zhí)行，這就避免了事務(wù)之間相互影響的問(wèn)題。

MariaDB 策略

MariaDB 的并行復(fù)制策略利用redo log 組提交 (group commit) 優(yōu)化的特性：能夠在同一組里提交的事務(wù)，一定不會(huì)修改同一行。所以可以按照食物的 commit—_id來(lái)分組。

在實(shí)現(xiàn)上，MariaDB 是這么做的：

• 在一組里面一起提交的事務(wù)，有一個(gè)相同的 commit_id，下一組就是 commit_id+1；
• commit_id 直接寫(xiě)到 binlog 里面；傳到備庫(kù)應(yīng)用的時(shí)候，相同 commit_id 的事務(wù)分發(fā)到多個(gè) worker 執(zhí)行；
• 這一組全部執(zhí)行完成后，coordinator 再去取下一批。

MySQL 5.7策略

MySQL5.7中對(duì) MariaDB 多策略進(jìn)行了優(yōu)化。因?yàn)橥瑫r(shí)處于 prepare 狀態(tài)的事務(wù)，在備庫(kù)執(zhí)行時(shí)是可以并行的，此時(shí)的redolog已經(jīng)經(jīng)過(guò)了并行驗(yàn)證，所以從庫(kù)也可以執(zhí)行。具體步驟不做贅述，參考MariaDB策略。

MySQL 5.7.22 的并行復(fù)制策略

在 2018 年 4 月份發(fā)布的 MySQL 5.7.22 版本里（最新5.7.37），MySQL 增加了一個(gè)新的并行復(fù)制策略，基于 WRITESET 的并行復(fù)制。相應(yīng)地，新增了一個(gè)參數(shù) binlog-transaction-dependency-tracking，用來(lái)控制是否啟用這個(gè)新策略。這個(gè)參數(shù)的可選值有以下三種。

COMMIT_ORDER，表示的就是前面介紹的，根據(jù)同時(shí)進(jìn)入 prepare 和 commit 來(lái)判斷是否可以并行的策略。

• WRITESET，表示的是對(duì)于事務(wù)涉及更新的每一行，計(jì)算出這一行的 hash 值，組成集合 writeset。如果兩個(gè)事務(wù)沒(méi)有操作相同的行，也就是說(shuō)它們的 writeset 沒(méi)有交集，就可以并行。
• WRITESET_SESSION，是在 WRITESET 的基礎(chǔ)上多了一個(gè)約束，即在主庫(kù)上同一個(gè)線程先后執(zhí)行的兩個(gè)事務(wù)，在備庫(kù)執(zhí)行的時(shí)候，要保證相同的先后順序。
• 當(dāng)然為了唯一標(biāo)識(shí)，這個(gè) hash 值是通過(guò)“庫(kù)名 + 表名 + 索引名 + 值”計(jì)算出來(lái)的。如果一個(gè)表上除了有主鍵索引外，還有其他唯一索引，那么對(duì)于每個(gè)唯一索引，insert 語(yǔ)句對(duì)應(yīng)的 writeset 就要多增加一個(gè) hash 值。

總結(jié)

總結(jié)一下，MySQL 并行復(fù)制策略主要是有三種思想：

按照庫(kù)的級(jí)別粒度并行執(zhí)行，用于決定分發(fā)策略的 hash 表里，key 就是數(shù)據(jù)庫(kù)名。

按照行級(jí)別，根據(jù)id、唯一索引、value、庫(kù)名這些來(lái)計(jì)算hash值，做分組標(biāo)示

根據(jù)redo log 持久化原理，同一個(gè)commit組 或者 同時(shí)進(jìn)入prepare或者commit表示可以同步執(zhí)行。

文章題目：MySQL主從復(fù)制太慢，怎么辦？
本文鏈接：http://uogjgqi.cn/article/cceiiis.html