MySQL複製效能優化和常見問題分析

前言

系列文章：
1.MySQL主從複製
2.OneProxy實現MySQL讀寫分離
3.MySQL資料庫結構設計
4.MySQL基於GTID主從複製的雜談
5.MySQL複製效能優化和常見問題分析

先來說說影響MySQL複製效能的幾個引數吧

二進位制日誌檔案並不是每次寫的時候都會同步到磁碟，當發生當機的時候，可能會有最後一部分資料沒有寫入到binlog中，這給恢復和複製帶來了問題。當sync_binlog=1表示每寫緩衝一次就同步到磁碟，表示同步寫磁碟的方式來寫binlog。也就是說每當向MySQL提交一次事務，MySQL將進行一次fsync之類的磁碟同步命令來將binlog_cache的資料強制刷到磁碟中sync_binlog的值預設為0，sync_binlog=0時表示採用作業系統機制進行緩衝資料同步。採用sync_binlog=1時，會增加磁碟IO的次數，會影響寫入效能。sync_binlog=1時，並不是100%安全，會存在相應的問題。比如說使用Innodb引擎時，在一個事務發出commit前，會將binlog立即刷到磁碟中。如果這時候已經寫入到binlog中，但是還沒有提交就已經掛了，那麼MySQL重啟時，會將通過Redo log、Undo log將這個事務回滾掉，但是binlog已經記入了該事務資訊，不能回滾掉。所以我們需要設定innodb_support_xa=1確保MySQL服務層的binlog和MySQL儲存引擎層的Redo log、Undo log之間的資料一致性。

sync_binlog=1

指定master_info和replay_log_info資訊的儲存方式為table。如果MySQL掛了的後，由於Innodb儲存引擎的特點，可以對這2張表進行故障恢復，保證slave能從正確的位置進行資料恢復。

master_info_repository=TABLE
relay_log_info_repository=TABLE

可以看到slave_master_info這張表儲存著master的binlog以及當前master寫入binlog的位置等資訊。

mysql> select * from slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.000001
        Master_log_pos: 914
                  Host: 192.168.10.21
             User_name: gtid
         User_password: gtid
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca: 
            Ssl_capath: 
              Ssl_cert: 
            Ssl_cipher: 
               Ssl_key: 
Ssl_verify_server_cert: 0
             Heartbeat: 30
                  Bind: 
    Ignored_server_ids: 0
                  Uuid: 67ccaaf1-e4b4-11e7-a07f-c8d3ffc0c026
           Retry_count: 86400
               Ssl_crl: 
           Ssl_crlpath: 
 Enabled_auto_position: 1
          Channel_name: 
           Tls_version: 
1 row in set (0.00 sec)

可以看到slave_relay_log_info記錄了slave的relay log的位置、master binlog的名稱，master binlog當前偏移量，relay log當前偏移量等資訊。

mysql> select * from slave_relay_log_info\G
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./mysql-relay-bin.000004
    Relay_log_pos: 831
  Master_log_name: mysql-bin.000001
   Master_log_pos: 914
        Sql_delay: 0
Number_of_workers: 16
               Id: 1
     Channel_name: 
1 row in set (0.00 sec)

當slave掛了後導致中繼日誌損壞後，導致一部分中繼日誌沒有處理，則放棄所有未執行的relay log，並且重新從master獲取日誌，這樣就保證了relay log的完整性。

relay_log_recovery=1

這些引數和sync_binlog引數一樣,slave的IO執行緒每次接收到master傳送過來的日誌都要寫入到系統緩衝區，然後再刷到磁碟中。這樣master崩潰時，最多丟失一個事務。雖安全，但是會造成大量的磁碟IO。sync_relay_log、sync_relay_log_info、sync_master_info預設值都為10000

sync_relay_log=1
sync_relay_log_info=1
sync_master_info=1

預設是1，自動清空不需要的中繼日誌。

relay_log_purge=1

MySQL5.6有基於庫的並行複製，可以設定slave-parallel-workers引數設定workers的個數。當開啟並行複製功能後，那麼SQL執行緒就變成了Coordinator執行緒。
不同schema下的表併發提交的資料不會有影響，slave會對relay log中不同schema分配workers執行緒，來回放relay log中master已經提交的事務，保持資料一致性。如果單例項僅有一個庫，開啟並行複製功能後，那麼就無法實現並行回放，甚至效能會比以前的單執行緒更差。

在MySQL5.7，引入了基於組提交的並行複製，設定slave-parallel-type=LOGICAL_CLOCK。支援在同一個schema下，支援slave-parallel-worker個worker執行緒併發回放relay log中master的事務。一個組提交的事務是可以並行回放的。在slave中的relay log中具有相同的last_committed值(sequence_num不同)的事務是屬於同一個組的。

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16

選擇複製模式需要考慮的幾個點：
1.所使用的MySQL。如果是5.6以上，可以考慮GTID複製。如果是5.6以下的，可以考慮binlog複製。
2.複製架構及主從切換的方式。如果是一主多從，還是推薦GTID複製，不會為新master的binlog偏移量而擔心。
3.所使用的高可用管理元件。MMM只支援基於日誌的方式。而MHA支援日誌、GTID複製。

在MySQL5.7之前，一個從庫只能有一個主庫。MySQL5.7之後，支援一從多主架構。

一主多從的複製拓撲架構，配置簡單，可以用多個從庫分擔讀負載，使資料差異最小化。用途：1.為不同的業務使用不同的從庫。2.將一臺從庫放到遠端IDC，用於災備恢復。3.分擔主庫的讀負載。

主主(雙機熱備)複製拓撲架構，並不能分擔寫負載。缺點：1.經常產生資料衝突從而造成複製鏈路中斷。2.耗費大量的時間，造成資料丟失。所以建議兩個主庫所操作的表最好能夠分開，使用auto_increment_increment和auto_increment_offset=1|2引數控制自增ID的生成。

主主(單機熱備)複製拓撲架構，只有一臺主伺服器對外提供服務，另一臺伺服器處於只讀狀態並且只作為熱備使用。在對外提供服務的主庫出現故障或是計劃性的維護時才會進行切換。使用這種拓撲架構，需要注意以下幾點：
1.確保兩臺伺服器上的初始資料相同。
2.確保兩臺伺服器上已經啟動binlog，並且有不同的server_id。
3.在兩臺伺服器上啟動log_slave_updates引數。
4.在初始備庫上啟動ready_only。

影響主從延遲的因素：
1.主庫寫入到二進位制的時間。
2.控制主庫的事務大小，分割大事務。
3.二進位制日誌傳輸時間取決於傳輸日誌量的多少。推薦使用mixed日誌格式。
4.預設情況下從庫只有一個sql執行緒。也就是說master上併發的修改在slave變成了序列。可以採用多執行緒複製(設定slave-parallel-type=LOGICAL_CLOCK、slave-parallel-workers=16)。

在MySQL主從複製過程中，要注意以下問題：
1.主庫意外重啟或者主庫的二進位制檔案損壞。我們可以在slave上通過change master命令來重新指定binlog偏移量，同時配置sync_binlog=1使每次寫入對binlog進行同步，落地磁碟，減少當機所丟失的事務數。
2.從庫上的中繼日誌損壞。
3.從庫當機，引起master.info檔案沒有及時更新到磁碟上。master.info記錄著從庫同步主庫的相關資訊，會引起從庫重複同步的操作。
4.在從庫上進行資料修改造成的主從複製錯誤。
5.不唯一的server_id或者server_uuid。
6.max_allowed_packet設定引起的主從複製錯誤。

一般我們可以採用以下幾種方法解決主從複製問題：
1.跳過二進位制日誌檔案。
2.注入空事務的方式先恢復被中斷的複製鏈路
再使用其他方法來對比主從伺服器上的資料。

MySQL複製無法解決的問題有哪些：
1.分擔主資料庫的寫負載。
2.自行進行故障轉移及主從切換。
3.提供讀寫分離功能。

在這裡，我們要可以引出一個概念，高可用。高可用性(HA,High Availability)指的是通過儘量縮短因日常維護操作(計劃)和突發的系統崩潰(非計劃)所導致的停機時間，以提高系統和應用的可用性。

我們應該避免導致系統不可用的因素，減少系統不可用的時間。
1.嚴重的主從延遲。
2.主從複製中斷。
3.鎖引起的大量阻塞。
4.伺服器磁碟空間耗盡，比如備份或者各種查詢日誌快速增長導致磁碟空被佔滿。或者是MySQL由於無法記錄二進位制日誌，無法處理新的請求而產生的系統不可用的故障。
5.效能糟糕的sql。
6.表結構和索引沒有優化。
7.主從資料不一致。
8.人為的操作失敗等等。

那如何實現高可用呢？
1.建立完善的監控及報警系統。
2.對備份資料進行恢復測試。
3.正確配置資料庫環境。
4.對不需要的資料進行歸檔和清理。
5.增加系統冗餘，保證發生系統不可用時可以儘快恢復。(避免存在單點故障，可以進行主從切換及故障轉移)

在這裡我們又要引出一個概念，單點故障。它是指在一個系統中提供相同功能的元件只有一個，如果這個元件失效了，就會影響整個系統功能的正常使用，組成應用系統的各個元件都有可能成為單點。那麼我們如何避免MySQL單點故障呢？
1.利用SUN共享儲存或者DRBD磁碟複製來解決MySQL單點故障。
2.MySQL主從複製(關於主從複製，我們該考慮3個點。一是主伺服器切換後，該如何通知應用新master的ip地址。二是如何檢查MySQL主伺服器是否可用。三是如何處理從伺服器和新主伺服器之間的那種複製關係)

為了解決MySQL主從複製管理的痛點，才衍生出了MMM，MHA這些高可用的管理元件。在本文章的末尾，簡單介紹一下MMM是什麼。

MMM的主要作用是監控和管理MySQL的主主複製拓撲結構，並在當前的主伺服器失效時，進行主和主備伺服器之間的主從切換和故障轉移等工作。

MMM可以在主庫出現當機時進行故障轉移並且自動配置其他從伺服器對新主伺服器的複製。

使用MMM要考慮兩點：一是如何找到從庫對應的新主伺服器的日誌同步點。二是如果存在多個從庫出現資料不一致的情況，如何處理。在一個繁忙的系統中，使用MMM有可能會造成資料丟失。

MMM提供了讀，寫虛擬IP。在主從伺服器出現問題時可以自動遷移虛擬IP。

尾言

大家好，我是cmazxiaoma(寓意是沉夢昂志的小馬)，感謝各位閱讀本文章。
小弟不才。
如果您對這篇文章有什麼意見或者錯誤需要改進的地方,歡迎與我討論。
如果您覺得還不錯的話,希望你們可以點個贊。
希望我的文章對你能有所幫助。
有什麼意見、見解或疑惑，歡迎留言討論。

最後送上：心之所向，素履以往。生如逆旅，一葦以航。