怎樣解決MySQL資料庫主從複寫延遲的問題？

標籤：比較   交換   ast   主從   cond   開發   單線程   關閉   自己   

1.網路逾時

2.慢查詢

3.流量

問題一：主庫的從庫太多，導致複寫延遲
從庫資料以3-5個為宜，要複製的從節點數量過多，會導致複寫延遲
問題二：從庫硬體比主庫差，導致複寫延遲
查看Master和Slave的系統配置，可能會因為機器配置不當，包括磁碟I/O、CPU、記憶體等各方面因素造成複製的延遲。一般發生在高並發大資料量寫入情境中
問題三：慢SQL語句過多
假如一條SQL語句執行時間是20秒，那麼從執行完畢到從庫上能查到資料至少需要20秒，這樣就延遲20秒了。
一般要把SQL語句的最佳化作為常規工作不斷地進行監控和最佳化，如果單個SQL的寫入時間長，可以修改後分多次寫入。通過查看慢查詢日誌或show full processlist命令，找出執行時間長的查詢語句或大的事務
問題四：主從複製的設計問題
例如主從複製單線程，如果主庫寫並發太大，來不及傳送到從庫，就會導致延遲。更高版本的MySQL可以支援多線程複製，門戶網站則會開發自己的多線程同步功能。
問題五：主從庫之間的網路延遲
主從庫的網卡、網線、交換器等網路裝置都可能成為複製的瓶頸，導致複寫延遲。另外，跨公網的主從複製很容易導致主從複寫延遲
問題六：主庫讀寫壓力大，導致複寫延遲
架構的前端要加buffer及緩衝層

 

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資料庫主從同步延遲解決方案

答：最簡單的減少slave同步延時的方案就是在架構上做最佳化，盡量讓主庫的DDL快速執行。還有就是主庫是寫，對資料安全性較高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之類的設定，而slave則不需要這麼高的資料安全，完全可以講sync_binlog設定為0或者關閉binlog，innodb_flushlog也可以設定為0來提高sql的執行效率。另外就是使用比主庫更好的硬體裝置作為slave。

4.MySQL資料庫主從同步延遲產生的因素。

1. 網路延遲
2. master負載
3. slave負載
一般的做法是，使用多台slave來分攤讀請求，再從這些slave中取一台專用的伺服器，只作為備份用，不進行其他任何操作，就能相對最大限度地達到’即時’的要求了

另外，再介紹2個可以減少延遲的參數
–slave-net-timeout=seconds
參數含義：當slave從主要資料庫讀取log資料失敗後，等待多久重建立立串連並擷取資料
slave_net_timeout單位為秒 預設設定為 3600秒
| slave_net_timeout | 3600
–master-connect-retry=seconds
參數含義：當重建立立主從串連時，如果串連建立失敗，間隔多久後重試。
master-connect-retry單位為秒 預設設定為 60秒
通常配置以上2個參數可以減少網路問題導致的主從資料同步延遲

怎樣解決MySQL資料庫主從複寫延遲的問題？