MySQL學習：為什麼我的MySQL會“抖”一下？

在平時的工作場景中，有時會發現，一條 SQL 語句，正常執行的時候特別快，但是有時也不知道怎麼回事，它就會變得特別慢，並且這樣的場景很難復現，它不只隨機，而且持續時間還很短。看上去，這就像是資料庫“抖”了一下。

1.你的 SQL 語句為什麼變“慢”了

在MySQL學習：一條SQL語句的執行過程第二節一條SQL更新語句是怎麼執行的（日誌系統）中學習了WAL機制（關鍵點就是先寫日誌，再寫磁碟）。現在知道了，InnoDB 在處理更新語句的時候，只做了寫日誌這一個磁碟操作。這個日誌叫作 redo log（重做日誌），也就是《孔乙己》裡咸亨酒店掌櫃用來記賬的粉板，在更新記憶體寫完 redo log 後，就返回給客戶端，本次更新成功。

做下類比的話，掌櫃記賬的賬本是資料檔案，記賬用的粉板是日誌檔案（redo log），掌櫃的記憶就是記憶體。

掌櫃總要找時間把賬本更新一下，這對應的就是把記憶體裡的資料寫入磁碟的過程，術語就是 flush。在這個 flush 操作執行之前，孔乙己的賒賬總額，其實跟掌櫃手中賬本里面的記錄是不一致的。因為孔乙己今天的賒賬金額還只在粉板上，而賬本里的記錄是老的，還沒把今天的賒賬算進去。

當記憶體資料頁跟磁碟資料頁內容不一致的時候，我們稱這個記憶體頁為“髒頁”。記憶體資料寫入到磁碟後，記憶體和磁碟上的資料頁的內容就一致了，稱為“乾淨頁”。

innodb是如何知道一個頁是不是髒頁的？

每個資料頁頭部有LSN，8位元組，每次修改都會變大。對比這個LSN跟checkpoint 的LSN，比checkpoint小的一定是乾淨頁。

不論是髒頁還是乾淨頁，都在記憶體中。在這個例子裡，記憶體對應的就是掌櫃的記憶。

接下來，用一個示意圖來展示一下“孔乙己賒賬”的整個操作過程。假設原來孔乙己欠賬 10 文，這次又要賒 9 文。

回到部落格開頭的問題，不難想象，平時執行很快的更新操作，其實就是在寫記憶體和日誌，而 MySQL 偶爾“抖”一下的那個瞬間，可能就是在刷髒頁（flush）。

那麼，什麼情況會引發資料庫的 flush 過程呢？

還是繼續用咸亨酒店掌櫃的這個例子，想一想：掌櫃在什麼情況下會把粉板上的賒賬記錄改到賬本上？

1. 第一種場景是，粉板滿了，記不下了。這時候如果再有人來賒賬，掌櫃就只得放下手裡的活兒，將粉板上的記錄擦掉一些，留出空位以便繼續記賬。當然在擦掉之前，他必須先將正確的賬目記錄到賬本中才行。這個場景，對應的就是 InnoDB 的 redo log 寫滿了。這時候系統會停止所有更新操作，把 checkpoint往前推進，redo log 留出空間可以繼續寫。這裡畫了一個 redo log 的環形示意圖，便於理解。
   checkpoint 可不是隨便往前修改一下位置就可以的。比如圖中，把 checkpoint 位置從 CP 推進到 CP’，就需要將兩個點之間的日誌（淺綠色部分），對應的所有髒頁都 flush 到磁碟上。之後，圖中從 write pos 到 CP’之間就是可以再寫入的 redo log 的區域。

2. 第二種場景是，這一天生意太好，要記住的事情太多，掌櫃發現自己快記不住了，趕緊找出賬本把孔乙己這筆賬先加進去。這種場景，對應的就是系統記憶體不足。當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰一些資料頁，空出記憶體給別的資料頁使用。如果淘汰的是“髒頁”，就要先將髒頁寫到磁碟。
   這時候難道不能直接把記憶體淘汰掉，下次需要請求的時候，從磁碟讀入資料頁，然後拿 redo log 出來應用不就行了？這裡其實是從效能考慮的。如果刷髒頁一定會寫盤，就保證了每個資料頁有兩種狀態：

   1. 一種是記憶體裡存在，記憶體裡就肯定是正確的結果，直接返回；
   2. 另一種是記憶體裡沒有資料，就可以肯定資料檔案上是正確的結果，讀入記憶體後返回。這樣的效率最高。

3. 第三種場景是，生意不忙的時候，或者打烊之後。這時候櫃檯沒事，掌櫃閒著也是閒著，不如更新賬本。
   這種場景，對應的就是 MySQL 認為系統“空閒”的時候。當然，MySQL“這家酒店”的生意好起來可是會很快就能把粉板記滿的，所以“掌櫃”要合理地安排時間，即使是“生意好”的時候，也要見縫插針地找時間，只要有機會就刷一點“髒頁”。

4. 第四種場景是，年底了咸亨酒店要關門幾天，需要把賬結清一下。這時候掌櫃要把所有賬都記到賬本上，這樣過完年重新開張的時候，就能就著賬本明確賬目情況了。這種場景，對應的就是 MySQL 正常關閉的情況。這時候，MySQL 會把記憶體的髒頁都 flush 到磁碟上，這樣下次 MySQL 啟動的時候，就可以直接從磁碟上讀資料，啟動速度會很快。

接下來，可以分析一下上面四種場景對效能的影響。

其中，第三種情況是屬於 MySQL 空閒時的操作，這時系統沒什麼壓力，而第四種場景是資料庫本來就要關閉了。這兩種情況下，不會太關注“效能”問題。所以這裡，主要來分析一下前兩種場景下的效能問題。

第一種是“redo log 寫滿了，要 flush 髒頁”，這種情況是 InnoDB 要儘量避免的。因為出現這種情況的時候，整個系統就不能再接受更新了，所有的更新都必須堵住。如果你從監控上看，這時候更新數會跌為 0。

第二種是“記憶體不夠用了，要先將髒頁寫到磁碟”，這種情況其實是常態。InnoDB 用緩衝池（buffer pool）管理記憶體，緩衝池中的記憶體頁有三種狀態：

1. 第一種是，還沒有使用的；
2. 第二種是，使用了並且是乾淨頁；
3. 第三種是，使用了並且是髒頁。

InnoDB 的策略是儘量使用記憶體，因此對於一個長時間執行的庫來說，未被使用的頁面很少。

而當要讀入的資料頁沒有在記憶體的時候，就必須到緩衝池中申請一個資料頁。這時候只能把最久不使用的資料頁從記憶體中淘汰掉：如果要淘汰的是一個乾淨頁，就直接釋放出來複用；但如果是髒頁呢，就必須將髒頁先刷到磁碟，變成乾淨頁後才能複用。

所以，刷髒頁雖然是常態，但是出現以下這兩種情況，都是會明顯影響效能的：

1. 一個查詢要淘汰的髒頁個數太多，會導致查詢的響應時間明顯變長；
2. 日誌寫滿，更新全部堵住，寫效能跌為 0，這種情況對敏感業務來說，是不能接受的。

所以，InnoDB 需要有控制髒頁比例的機制，來儘量避免上面的這兩種情況。

2.InnoDB 刷髒頁的控制策略

接下來，就來說說 InnoDB 髒頁的控制策略，以及和這些策略相關的引數。

首先，要正確地告訴 InnoDB 所在主機的 IO 能力，這樣 InnoDB 才能知道需要全力刷髒頁的時候，可以刷多快。

這就要用到 innodb_io_capacity 這個引數了，它會告訴 InnoDB 你的磁碟能力。這個值建議設定成磁碟的 IOPS。磁碟的 IOPS 可以通過 fio 這個工具來測試，下面的語句是用來測試磁碟隨機讀寫的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

其實，因為沒能正確地設定 innodb_io_capacity 引數，而導致的效能問題也比比皆是。

雖然現在已經定義了“全力刷髒頁”的行為，但平時總不能一直是全力刷吧？畢竟磁碟能力不能只用來刷髒頁，還需要服務使用者請求。所以接下來，就看看 InnoDB 怎麼控制引擎按照“全力”的百分比來刷髒頁。

試想一下，如果設計策略控制刷髒頁的速度，會參考哪些因素呢？

這個問題可以這麼想，如果刷太慢，會出現什麼情況？首先是記憶體髒頁太多，其次是 redo log 寫滿。

所以，InnoDB 的刷盤速度就是要參考這兩個因素：一個是髒頁比例，一個是 redo log 寫盤速度。

InnoDB 會根據這兩個因素先單獨算出兩個數字。

引數 innodb_max_dirty_pages_pct 是髒頁比例上限，預設值是 75%。InnoDB 會根據當前的髒頁比例（假設為 M），算出一個範圍在 0 到 100 之間的數字，計算這個數字的虛擬碼類似這樣：

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB 每次寫入的日誌都有一個序號，當前寫入的序號跟 checkpoint 對應的序號之間的差值假設為 N。InnoDB 會根據這個 N 算出一個範圍在 0 到 100 之間的數字，這個計算公式可以記為 F2(N)。F2(N) 演算法比較複雜，只要知道 N 越大，算出來的值越大就好了。

然後，根據上述算得的 F1(M) 和 F2(N) 兩個值，取其中較大的值記為 R，之後引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷髒頁的速度。

上述的計算流程比較抽象，不容易理解，所以畫一個簡單的流程圖。圖中的 F1、F2 就是上面我們通過髒頁比例和 redo log 寫入速度算出來的兩個值。

現在知道了，InnoDB 會在後臺刷髒頁，而刷髒頁的過程是要將記憶體頁寫入磁碟。所以，無論是查詢語句在需要記憶體的時候可能要求淘汰一個髒頁，還是由於刷髒頁的邏輯會佔用 IO 資源並可能影響到了更新語句，都可能是造成從業務端感知到 MySQL“抖”了一下的原因。

要儘量避免這種情況，就要合理地設定 innodb_io_capacity 的值，並且平時要多關注髒頁比例，不要讓它經常接近 75%。

其中，髒頁比例是通過 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具體的命令參考下面的程式碼：

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

接下來，再看一個有趣的策略。

一旦一個查詢請求需要在執行過程中先 flush 掉一個髒頁時，這個查詢就可能要比平時慢了。而 MySQL 中的一個機制，可能讓查詢會更慢：在準備刷一個髒頁的時候，如果這個資料頁旁邊的資料頁剛好是髒頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續蔓延，也就是對於每個鄰居資料頁，如果跟它相鄰的資料頁也還是髒頁的話，也會被放到一起刷。

在 InnoDB 中，innodb_flush_neighbors 引數就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制，值為 0 時表示不找鄰居，自己刷自己的。

找“鄰居”這個優化在機械硬碟時代是很有意義的，可以減少很多隨機 IO。機械硬碟的隨機 IOPS 一般只有幾百，相同的邏輯操作減少隨機 IO 就意味著系統效能的大幅度提升。

而如果使用的是 SSD 這類 IOPS 比較高的裝置的話，建議把 innodb_flush_neighbors 的值設定成 0。因為這時候 IOPS 往往不是瓶頸，而“只刷自己”，就能更快地執行完必要的刷髒頁操作，減少 SQL 語句響應時間。

在 MySQL 8.0 中，innodb_flush_neighbors 引數的預設值已經是 0 了。

問題：

一個記憶體配置為 128GB、innodb_io_capacity 設定為 20000 的大規格例項，正常會建議將 redo log 設定成 4 個 1GB 的檔案。

但如果在配置的時候不慎將 redo log 設定成了 1 個 100M 的檔案，會發生什麼情況呢？又為什麼會出現這樣的情況呢？

（問題就是如果一個高配的機器，redo log 設定太小，會發生什麼情況。）

每次事務提交都要寫 redo log，如果設定太小，很快就會被寫滿，也就是下面這個圖的狀態，這個“環”將很快被寫滿，write pos 一直追著 CP。

這時候系統不得不停止所有更新，去推進 checkpoint。

這時，看到的現象就是磁碟壓力很小，但是資料庫出現間歇性的效能下跌。

參考：
《MySQL實戰45講》