Mysql

MySQL 8.0.20 - Master Replica 方案,增加 Source 和 Replica 之間的延遲

  • February 16, 2021

我們有一個大約 1TB 的源數據庫,以及另一個伺服器上的副本(類似架構)。我們已經設置了主從複製,並且從那以後,主從之間的延遲一直在增長。cron 使用主伺服器“高度”使用它插入/更新大量數據,但經過一些研究,我不得不承認我有點卡住了。

你能幫我診斷一下問題的原因嗎?非常感謝任何提示

主 / 從伺服器規格:24 核 / 256 GB DDR4 / NVMe

htop 表示 CPU 使用率:6% / RAM 使用率:~50% / IO:向上:~0.23 Mb/s,向下:~3.5 Mb/s

MySQL 變數:https ://justpaste.it/mysql_variables

Mysql > show replica status \G結果:

            Replica_IO_State: Waiting for master to send event  <- most of the times
                 Source_Host: XX.XX.XX.XX (Source private IP)
                 Source_User: repl
                 Source_Port: 3306
               Connect_Retry: 60
             Source_Log_File: binlog.001720
         Read_Source_Log_Pos: 1024908194                        <- increasing normally
              Relay_Log_File: Linux2-relay-bin.000296
               Relay_Log_Pos: 915186810                         <- increasing normally
       Relay_Source_Log_File: binlog.001676
          Replica_IO_Running: Yes
         Replica_SQL_Running: Yes
             Replicate_Do_DB:
         Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
     Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                  Last_Errno: 0
                  Last_Error:
                Skip_Counter: 0
         Exec_Source_Log_Pos: 915186601                         <- incrising normally
             Relay_Log_Space: 48272449293                       <- incrising normally
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Source_SSL_Allowed: No
          Source_SSL_CA_File:
          Source_SSL_CA_Path:
             Source_SSL_Cert:
           Source_SSL_Cipher:
              Source_SSL_Key:
       Seconds_Behind_Source: 56693                             <- increasing slowly
Source_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error:
              Last_SQL_Errno: 0
              Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
            Source_Server_Id: 1
                 Source_UUID: ece58e3c-5ac0-11eb-ab4b-00505601ddac
            Source_Info_File: mysql.slave_master_info
                   SQL_Delay: 0
         SQL_Remaining_Delay: NULL
   Replica_SQL_Running_State: waiting for handler commit        <- most of the times 
          Source_Retry_Count: 86400
                 Source_Bind:
     Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
              Source_SSL_Crl:
          Source_SSL_Crlpath:
          Retrieved_Gtid_Set:
           Executed_Gtid_Set:
               Auto_Position: 0
        Replicate_Rewrite_DB:
                Channel_Name:
          Source_TLS_Version:
      Source_public_key_path:
       Get_Source_public_key: 0
           Network_Namespace:

請記住 MySQL 複製本質上是單執行緒的。

它有兩個執行緒

  • I/O Thread 將 Binlog 事件從 Master 傳輸到 Slave 的中繼日誌
  • SQL Thread 按接收順序處理 Slave 的中繼日誌中收集的 binlog 事件

從變數的外觀來看,我只推薦兩個臨時更改:

SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

這將放鬆 SQL 執行緒完成的寫入,因為您在從站上有二進制日誌記錄。必須記錄在 Slave 上寫入的每個事務並刷新二進制日誌。將sync_binlog設置為 0 允許作業系統記憶體/刷新而不是 mysqld。

您還希望通過將innodb_flush_log_at_trx_commit設置為 2 來放鬆從屬設備上 ACID 合規性的嚴格性。這將導致 InnoDB 儲存引擎更有規律性,而不是在每次事務送出時刷新。

您必須將innodb_flush_method設置O_DIRECT為進一步將頁面刷新分配給 mysqld 而不是作業系統。

我之前提到過這些事情

由於innodb_flush_method不是動態的,所以必須重啟mysqld。

請將以下變數添加到您的 my.cnf

[mysqld]
innodb_flush_method = O_DIRECT

然後,重新啟動 mysqld。之後登錄到mysql並執行

SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
START SLAVE; /* If replication does not start automatically */

複製應該開始迎頭趕上。一旦趕上,將變數改回

SET GLOBAL sync_binlog = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

更新 2021-02-16 10:12 EST

為什麼會想到臨時使用這些變數?

多年來,我的客戶擁有以下設置之一:

  • sync_binlog=0innodb_flush_log_at_trx_commit=2:這將在只讀從站上
  • sync_binlog=1innodb_flush_log_at_trx_commit=1:如果您希望進行故障轉移,這將適用於您希望成為 Master 的奴隸。

場景:假設你有一個主人和 3 個奴隸。你有 Mastersync_binlog=1innodb_flush_log_at_trx_commit=1。奴隸會有sync_binlog=0innodb_flush_log_at_trx_commit=2

如果您希望進行故障轉移,您將設置一個完全趕上的從屬伺服器 (Seconds_Behind_Master=0) 來擁有sync_binlog=1innodb_flush_log_at_trx_commit=1. 然後將應用程序指向那個新的 Slave 並設置其他 Slave 以從新提升的 Master 複製。

您可以使用 ProxySQL/Orchestrator 等工具為您設置此類步驟。

這只是臨時更改這些變數的一個範例。

船長日誌:補充

如果您將重做日誌(ib_logfile0,ib_logfile1)、二進制日誌、慢日誌或通用日誌儲存在與數據相同的數據卷中,它們會減慢寫入數據庫的速度。怎麼會這樣 ???

在磁碟級別

  • 所有的Logs都是按順序寫的
  • 所有數據通常以隨機順序寫入

將日誌儲存在單獨的磁碟捲上還可以提高寫入性能。我從 Facebook 工程師的部落格中了解到這一點。(另請參閱如何確定每天通過插入、更新和刪除操作寫入多少數據?以及 SSD 上的 MySQL - 有什麼缺點?

引用自:https://dba.stackexchange.com/questions/285442