MySQL - 主從同步延遲

1. MySQL數(shù)據(jù)庫主從同步延遲原理？

2. MySQL數(shù)據(jù)庫主從同步延遲是怎么產(chǎn)生的？

3. MySQL數(shù)據(jù)庫主從同步延遲解決方案？

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

1. MySQL數(shù)據(jù)庫主從同步延遲原理

答：談到MySQL數(shù)據(jù)庫主從同步延遲原理，得從mysql的數(shù)據(jù)庫主從復(fù)制原理說起，mysql的主從復(fù)制都是單線程的操作，主庫對所有DDL(數(shù)據(jù)庫定義語言)和DML(數(shù)據(jù)庫操作語言)產(chǎn)生binlog，binlog是順序?qū)懀孕屎芨?，slave的Slave_IO_Running線程到主庫取日志，效率很比較高，下一步，問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產(chǎn)生lock爭用，由于Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會等待這個DDL執(zhí)行完才會繼續(xù)執(zhí)行，這就導(dǎo)致了延時。有朋友會問：“主庫上那個相同的DDL也需要執(zhí)行10分，為什么slave會延時？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。

2. MySQL數(shù)據(jù)庫主從同步延遲是怎么產(chǎn)生的。

答：當(dāng)主庫的TPS并發(fā)較高時，產(chǎn)生的DDL數(shù)量超過slave一個sql線程所能承受的范圍，那么延時就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待。

3. MySQL數(shù)據(jù)庫主從同步延遲解決方案。

問題一：主庫的從庫太多，導(dǎo)致復(fù)制延遲

從庫數(shù)據(jù)以3-5個為宜，要復(fù)制的從節(jié)點數(shù)量過多，會導(dǎo)致復(fù)制延遲

問題二：從庫硬件比主庫差，導(dǎo)致復(fù)制延遲

查看Master和Slave的系統(tǒng)配置，可能會因為機(jī)器配置不當(dāng)，包括磁盤I/O、CPU、內(nèi)存等各方面因素造成復(fù)制的延遲。一般發(fā)生在高并發(fā)大數(shù)據(jù)量寫入場景中

問題三：慢SQL語句過多

假如一條SQL語句執(zhí)行時間是20秒，那么從執(zhí)行完畢到從庫上能查到數(shù)據(jù)至少需要20秒，這樣就延遲20秒了。

一般要把SQL語句的優(yōu)化作為常規(guī)工作不斷地進(jìn)行監(jiān)控和優(yōu)化，如果單個SQL的寫入時間長，可以修改后分多次寫入。通過查看慢查詢?nèi)罩净騭how full processlist命令，找出執(zhí)行時間長的查詢語句或大的事務(wù)

問題四：主從復(fù)制的設(shè)計問題

例如主從復(fù)制單線程，如果主庫寫并發(fā)太大，來不及傳送到從庫，就會導(dǎo)致延遲。更高版本的Mysql可以支持多線程復(fù)制，門戶網(wǎng)站則會開發(fā)自己的多線程同步功能。

問題五：主從庫之間的網(wǎng)絡(luò)延遲

主從庫的網(wǎng)卡、網(wǎng)線、交換機(jī)等網(wǎng)絡(luò)設(shè)備都可能成為復(fù)制的瓶頸，導(dǎo)致復(fù)制延遲。另外，跨公網(wǎng)的主從復(fù)制很容易導(dǎo)致主從復(fù)制延遲

問題六：主庫讀寫壓力大，導(dǎo)致復(fù)制延遲

架構(gòu)的前端要加buffer及緩存層

門戶網(wǎng)站的解決方案：

優(yōu)酷的解決方案：數(shù)據(jù)庫分片技術(shù)，而拋棄了由于數(shù)據(jù)量的越來越多導(dǎo)致復(fù)制延遲的問題。按照user_id進(jìn)行分片，這樣必須有一個全局的表來管理用戶與shard的關(guān)系,根據(jù)user_id可以得到share_id，然后根據(jù)share_id去指定的分片查詢指定的數(shù)據(jù)

淘寶的解決方案：修改源碼，對應(yīng)的機(jī)制是Transfer機(jī)制，此處通過對Binlog日志重做采用多線程實現(xiàn)，從而提高slave的QPS