MySQL 8.0.20 - Master Replica 方案，增加 Source 和 Replica 之間的延遲
我們有一個大約 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 而不是作業系統。我之前提到過這些事情
Dec 07, 2012:動態更改 innodb_flush_log_at_trx_commitFeb 10, 2012:使用 innodb_flush_log_at_trx_commit = 2 是否安全May 04, 2011：關於 MySQL innodb_flush_method 變數的說明由於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=0和innodb_flush_log_at_trx_commit=2：這將在只讀從站上sync_binlog=1和innodb_flush_log_at_trx_commit=1：如果您希望進行故障轉移，這將適用於您希望成為 Master 的奴隸。場景：假設你有一個主人和 3 個奴隸。你有 Master
sync_binlog=1和innodb_flush_log_at_trx_commit=1。奴隸會有sync_binlog=0和innodb_flush_log_at_trx_commit=2。如果您希望進行故障轉移，您將設置一個完全趕上的從屬伺服器 (Seconds_Behind_Master=0) 來擁有
sync_binlog=1和innodb_flush_log_at_trx_commit=1. 然後將應用程序指向那個新的 Slave 並設置其他 Slave 以從新提升的 Master 複製。您可以使用 ProxySQL/Orchestrator 等工具為您設置此類步驟。
這只是臨時更改這些變數的一個範例。
船長日誌：補充
如果您將重做日誌（ib_logfile0，ib_logfile1）、二進制日誌、慢日誌或通用日誌儲存在與數據相同的數據卷中，它們會減慢寫入數據庫的速度。怎麼會這樣 ？？？
在磁碟級別
- 所有的Logs都是按順序寫的
- 所有數據通常以隨機順序寫入
將日誌儲存在單獨的磁碟捲上還可以提高寫入性能。我從 Facebook 工程師的部落格中了解到這一點。（另請參閱如何確定每天通過插入、更新和刪除操作寫入多少數據？以及 SSD 上的 MySQL - 有什麼缺點？）