我正在使用帶有主從複製設置的 MySQL-5.1.50。

大多數時候，奴隸落後於主人。

當我執行show processlist;時，沒有需要很長時間的查詢。我也啟用slow_log了。但是，它沒有發現任何執行緩慢的查詢。

從站不斷發出警報，表明複製落後於主站幾秒鐘。有時，延遲時間會增加。

如何診斷問題的原因？

我需要緊急幫助，因為這個問題在過去 20 天內一直存在。

Seconds_Behind_Master 就像是通過時間旅行來回顧過去。

這樣想：

• 太陽距離地球 93,000,000 英里
• 光速為 186,000 英里/秒
• 簡單的劃分錶明，太陽光到達地球大約需要 500 秒（8 分 20 秒）
• 當你看太陽時，你實際上看不到太陽。您會看到 8 分 20 秒前的位置。

同樣，Master 似乎同時處理了很多查詢。

你回頭看看奴隸，跑SHOW SLAVE STATUS\G，它說 200 for Seconds_Behind_Master。這個數字是怎麼計算的？從站的時鐘時間 (UNIX_TIMESTAMP(NOW()) - 查詢完成並記錄在主站的二進制日誌中時的 TIMESTAMP。

除此之外，還有另一個指標需要查看Seconds_Behind_Master。該指標稱為Relay_Log_Space。這表示從站上所有中繼文件的所有字節的總和。預設情況下，最大單個中繼日誌限制為 1GB。如果Relay_Log_Space小於 1GB，這表明在 Master 上並行執行了許多長時間執行的查詢。不幸的是，由於 Replication 的 SQL 執行緒的單執行緒特性，查詢是一個接一個地執行的。

例如，假設您在 Master 上有以下場景：

• 啟用慢查詢日誌
• 在 Master 上並行執行 20 個查詢
• 每個查詢耗時 3 秒
• 每個查詢都以相同的時間戳記錄在主二進制日誌中

當 Slave 從其中繼日誌中讀取這些查詢並一一處理時

• 奴隸的時鐘將移動
• 20 個查詢中的每一個的 TIMESTAMP 都是相同的
• 差異會增加 3 秒完成查詢
• 這導致 60 秒Seconds_Behind_Master

關於慢日誌，long_query_time的預設值為 10 秒。如果您在中繼日誌中的所有查詢都少於 10 秒，那麼您將永遠無法在慢查詢日誌中擷取任何內容。

對於主伺服器和從伺服器，我有以下建議

• 建議 #1：升級到 MySQL 5.5。在 MySQL 5.5 和 Percona Server 5.1.38+ 下，您可以調整 InnoDB 以訪問多個 CPU。我以前寫過關於這個的文章

  • Apr 26, 2012: CPU 性能與數據庫伺服器有關嗎？
  • Sep 20, 2011:多核和 MySQL 性能
  • Sep 12, 2011:可以讓 MySQL 使用多個核心嗎？
  • May 26, 2011:關於單執行緒與多執行緒數據庫的性能

• 建議 #2：對所有表使用 InnoDB。InnoDB 在 RAM 中記憶體數據和索引，MyISAM 只記憶體索引。

• 建議#3：增加記憶體。您必須在 Slave 和 Master 上記憶體更多的數據和索引

• 建議 #4：調整所有查詢。從執行數百次的查詢中減少毫秒對減少Seconds_Behind_Master.

進一步的故障排除

如果要查看導致複製滯後的查詢，請執行以下操作：

• SHOW SLAVE STATUS\G
• 從中獲取中繼日誌的名稱Relay_Log_File
• STOP SLAVE;
• START SLAVE;
• 在作業系統中，cd /var/lib/mysql或任何寫入中繼日誌的地方
• 將中繼日誌轉儲到文本文件

例如，讓我們做SHOW SLAVE STATUS\G

              Slave_IO_State: Waiting for master to send event
                 Master_Host: 10.64.51.149
                 Master_User: replicant
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.000009
         Read_Master_Log_Pos: 1024035856
              Relay_Log_File: relay-bin.000030
               Relay_Log_Pos: 794732078
       Relay_Master_Log_File: mysql-bin.000009
            Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
             Replicate_Do_DB:
         Replicate_Ignore_DB: search_cache
          Replicate_Do_Table:
      Replicate_Ignore_Table:
     Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                  Last_Errno: 0
                  Last_Error:
                Skip_Counter: 0
         Exec_Master_Log_Pos: 1024035856
             Relay_Log_Space: 794732271
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
             Master_SSL_Cert:
           Master_SSL_Cipher:
              Master_SSL_Key:
       Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error:
              Last_SQL_Errno: 0
              Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
            Master_Server_Id: 106451149

如果我執行STOP SLAVE; START SLAVE;，中繼日誌將關閉並打開一個新日誌。然而，你想要relay-bin.000030.

轉儲內容如下：

cd /var/lib/mysql
mysqlbinlog relay-bin.000030 > /root/RelayLogQueries.txt
less /root/RelayLogQueries.txt

您現在可以看到從站目前正在嘗試處理的查詢。您可以使用這些查詢作為調整的起點。

您使用的是什麼二進制日誌格式？您使用的是 ROW 還是 STATEMENT ？
" SHOW GLOBAL VARIABLES LIKE 'binlog_format';"

如果您使用 ROW 作為二進制日誌格式，請確保您的所有表都具有主鍵或唯一鍵：

SELECT t.table_schema,t.table_name,engine FROM information_schema.tables t INNER JOIN information_schema .columns c on t.table_schema=c.table_schema and t.table_name=c.table_name and t.table_schema not in ('performance_schema','information_schema','mysql') GROUP BY t.table_schema,t.table_name HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;

例如，如果您在主伺服器上執行一條刪除語句以刪除沒有 PK 或唯一鍵的表上的 100 萬條記錄，那麼主伺服器一側將只進行一次全表掃描，而從伺服器則不會發生這種情況。

當使用 ROW binlog_format 時，MySQL 將行更改寫入二進制日誌（而不是像 STATEMENT binlog_format 這樣的語句），並且該更改將逐行應用於從站一側，這意味著將進行 100 萬次全表掃描在從屬設備上僅反映主設備上的一個刪除語句，這會導致從屬設備滯後問題。

引用自：https://dba.stackexchange.com/questions/24793