Mysql slave 延遲故障一列

首先還是給出我見過的一些延遲可能：

• 大事物延遲 延遲略為2*執行時間 狀態為:reading event from the relay log
• 大表DDL延遲 延遲略為1*執行時間 狀態為:altering table
• 長期未提交的事物延遲，會造成延遲的瞬時增加
• 表上沒有主鍵或者唯一鍵 狀態為:system lock 或者 reading event from the relay log
• innodb層鎖造成延遲 狀態為:system lock 或者 reading event from the relay log
• 從庫引數設定如sync_binlog，sync_relay_log，innodb_flush_log_at_trx_commit等引數

---

一、故障描述

本案例是一個朋友的，從各方面進行展示故障
按照他的描述是在主庫進行了大量形如delete from where col=''；的操作，每次delete會刪除大量的行。主庫刪除並不慢，但是從庫追不上。

下面是現象：

• 延遲較大且無好轉跡象

• 系統整體負載不高但是mysqld程式100%

  
  
  

  image.png

• 無I/O壓力

  
  
  

  image.png

• 有大事物的存在

  
  
  

  image.png
  
  

  這裡這個5G是binlog的複製。

二、故障初次分析

首先我們要明白沒有I/O壓力代表了什麼，沒有I/O壓力代表了我們常說的fsync沒有壓力，對應的不會是下面幾個引數的問題：

• sync_binlog
• sync_relay_log
• innodb_flush_log_at_trx_commit

也就是跟I/O相關的調整我們是不需要考慮的。

我們再來看CPU的問題，用top -HU可以看到執行緒的資源消耗如下：

我們可以清楚的看到某個MySQL執行緒耗用CPU為100%，因為是5.7我們可以方便的使用語句

select a.thd_id,b.THREAD_OS_ID,a.user ,a.conn_id,b.TYPE,a.source,a.program_name from sys.processlist a,performance_schema.threads b where b.thread_id=a.thd_id; 

找到MySQL執行緒和作業系統的對應關係如下：

我們可以清楚看到是我們的SQL_THREAD，所以我們找到的根源是SQL_THREAD耗用了過多的CPU資源但是I/O並不是問題。

三、故障再次分析

一般來講我們遇到SQL_THREAD可能伴隨著I/O問題，但是這裡並沒有，所以瓶頸可能在快取資料的查詢方面。我們使用perf進行一下分析排名靠前的如下：

其中btr_search_guess_on_hash適合AHI自適應的hash索引相關的函式，而rec_get_offsets_func是對索引的每個flied進行定位的函式(當然我也沒仔細看過原始碼只是看了一下所在的檔案位置和函式描述資訊)，也就是他們貌似都是二級索引資料的查詢有關，我們再來看這個表的表結構如下：

我們發現這個表除了二級索引並沒有主鍵，問題基本已經定位，也就是我開始給除的延遲中的一條：

• 表上沒有主鍵或者唯一鍵 狀態為:system lock 或者 reading event from the relay log

所以原因可能就是，因為沒有主鍵或者唯一鍵，event回放的時候使用到了二級索引讓回放速度慢且進行了大量的記憶體資料查詢造成了CPU 100%而沒有I/O的現象。

四、問題解決

也就是對本表加一個自增欄位作為主鍵，速度馬上提高了。當然這個解決辦法其實我很最早就猜測到了，但是我想盡量找到為什麼會這樣。perf中的函式具體的邏輯等我學習AHI的在分析。呵呵呵呵！

作者微信：