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 - 有什麼缺點?)