MySQL - 主從同步延遲

1. MySQL數據庫主從同步延遲原理？

2. MySQL數據庫主從同步延遲是怎么產生的？

3. MySQL數據庫主從同步延遲解決方案？

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

1. MySQL數據庫主從同步延遲原理

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

2. MySQL數據庫主從同步延遲是怎么產生的。

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

3. MySQL數據庫主從同步延遲解決方案。

問題一：主庫的從庫太多，導致復制延遲

從庫數據以3-5個為宜，要復制的從節點數量過多，會導致復制延遲

問題二：從庫硬件比主庫差，導致復制延遲

查看Master和Slave的系統配置，可能會因為機器配置不當，包括磁盤I/O、CPU、內存等各方面因素造成復制的延遲。一般發生在高并發大數據量寫入場景中

問題三：慢SQL語句過多

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

一般要把SQL語句的優化作為常規工作不斷地進行監控和優化，如果單個SQL的寫入時間長，可以修改后分多次寫入。通過查看慢查詢日志或show full processlist命令，找出執行時間長的查詢語句或大的事務

問題四：主從復制的設計問題

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

問題五：主從庫之間的網絡延遲

主從庫的網卡、網線、交換機等網絡設備都可能成為復制的瓶頸，導致復制延遲。另外，跨公網的主從復制很容易導致主從復制延遲

問題六：主庫讀寫壓力大，導致復制延遲

架構的前端要加buffer及緩存層

門戶網站的解決方案：

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

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