MySQL 偶爾抽風，效能突然下降

以終為始發表於2020-08-17
MySql

有時會碰到這樣的情況，一條 SQL 在平時執行沒問題，很快。但是突然某個時間執行的就會很慢，而且這種場景並不能復現，只能隨機傳送的。

SQL 執行突然變慢的原因

在之前講解 MySQL Redo log 時，說到了 WAL 機制，為了保證 MySQL 更新的速度，在進行更新操作時，先將更新內容寫入 redo log，後續系統空閒時，再將 redo log 的內容應用到磁碟。

當記憶體資料頁（redo log）和磁碟資料頁內容不一致時，將該記憶體也稱為 “髒頁”。將記憶體資料寫入到磁碟後，資料一致，記憶體頁稱為 "乾淨頁"。

在記憶體資料寫入磁碟時，這個過程稱為 flush 過程。SQL 突然執行變得很慢，效能下降。原因就可能和 flush 操作有關。

因為在進行 flush 操作時，更新操作會等待 redo log 的寫入。

引起 flush 操作的原因

場景一：redo log 日誌已經記滿。這時系統會停止更新操作，將 check point 向前推進，讓 redo log 留出空間可以繼續寫。

這裡假設 CP 到 CP‘ 間隙已經寫入到磁碟，這部分就變成了乾淨頁，此時 write pos 就可以寫入這部分割槽域了。

場景二：系統記憶體不足，需要新的記憶體頁時，發現記憶體不夠用了，就需要淘汰一些資料頁。如果淘汰時，這時資料頁時髒頁，就要將髒頁寫到磁碟。

這時有個問題是，命名 redo log 中的內容已經被記錄到日誌中了，假如記憶體滿了，直接刪除不就可以嗎？下次讀入時，再把 redo log 日誌中的內容應用到磁碟。

沒有選擇直接清空記憶體，是從效能考慮的，因為在查詢資料時，有兩種情況：

  • 首先資料頁在記憶體中，記憶體是就是正確的結果，直接返回
  • 記憶體裡沒有資料，從資料檔案上讀入記憶體。

所以這樣效率比較高。

場景三：MySQL 會在系統空閒時，進入 flush 操作。

場景四：在 MySQL 正常關閉時，會把記憶體髒頁 flush 到磁碟上。

引起 flush 對效能的影響

對於第三，四場景來說，是比較正常的情況，不需要考慮效能問題。

對於第一種場景，InnoDB 會盡量避免，因為在這種情況下，整個系統不再接受更新。

但有時出現人為的配置錯誤，比如記憶體為 128 GB，innodb_io_capacity 設定為 20000 的例項。通常建議將 redo log 設定成 4 個 1GB 的檔案。但由於配置錯誤，設定成 100M 的檔案。

這裡由於 redo log 設定的太小，很快就會被寫滿。write pos 一直追著 check point. 這時，系統只能停止所有更新，推進 checkpoint.

表現就是，磁碟 IO 很小，但是出現間歇性的效能下降。

對於第二種場景，記憶體不夠用的情況，InnoDB 會用緩衝池（buffer pool）管理記憶體

記憶體頁在緩衝池中會有三種狀態：

  • 沒用使用的資料頁
  • 使用了，但是是乾淨頁
  • 使用了，是髒頁

每個資料頁頭部有LSN，8位元組，每次修改都會變大。

對比這個 LSN 跟 checkpoint 的 LSN，比checkpoint小的一定是乾淨頁

由於 InnoDB 的策略是儘可能使用記憶體，所以對於長時間執行的庫來說，未被使用的頁面很少。

當發現想讀入的資料頁沒有在記憶體中時，必須到緩衝池申請資料頁。並會把最久不用得資料頁從記憶體中淘汰

  • 如果是乾淨頁，直接釋放使用
  • 如果是髒頁，必須先刷盤，變成乾淨頁才能複用

當時，如果在下面的情況進行刷髒頁，會明顯影響效能：

  • 要淘汰的髒頁太多，導致查詢響應時間較長。
  • 日誌寫滿，更新被阻塞。

為了解決這個問題，InnoDB 使用控制髒頁比例的機制，來避免上面的情況。

InooDB 控制刷髒頁的策略

在 InnoDB 中，通過 innodb_io_capacity 引數，來告訴 InnoDB 目前主機的磁碟能力是多少，這個值建議設定成磁碟的 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 導致的效能問題很常見，比如有時系統吞吐量（TPS）很低，寫入很慢，但是磁碟 IO 並不高。就有可能是該引數設定的不正確。例如，innodb_io_capacity 的值設定的很低，但是磁碟用的 SSD，導致 InooDB 認為系統能力很差，所以刷髒頁特別慢。造成髒頁累計，影響查詢和更新效能。

InnoDB 在刷盤時主要考慮兩個因素：

  1. 髒頁的比例
  2. redo log 寫盤速度

會通過這兩個因素單獨先算出兩個數字。

innodb_max_dirty_pages_pct 髒頁比例上限，預設 75%.

InnoDB 會根據髒頁的比例（M），算出範圍在 0 - 100 的數字。，過程稱為 F1（M）

# M 髒頁比例
F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

除此之外，InnoDB 每次寫入日誌都會有一個序號 N. 然後根據 N 再算出一個 0 到 100 的數字，這個計算過程稱為 F2（N）

N: 當前寫入的序號和 checkpoint 對應序號之間的差值。

最後，根據 F1（M）和 F2（N）兩個值，取其中較大的值為 R，之後引擎就可以按照 innodb_io_capacity * R 來控制刷髒頁的速度。

所以無論是在查詢，需要載入資料到記憶體資料頁，而淘汰髒頁。還是更新時，導致刷盤操作都有可能造成 MySQL 的效能下降。

為了避免這種情況，要合理的設定 innodb_io_capacity 的值，平時要多關注髒頁比例，不讓其接近 75%.

其中髒頁比例可以通過下面的方式獲取：

mysql> use performance_schema;
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 髒頁的話，如果這個該髒頁的鄰居也是髒頁的話，就會把這個鄰居一起刷掉，如果恰好旁邊還是髒頁的話，就會一直連坐。這時導致 flush 過慢的原因。

可以通過 innodb_flush_neighbors 來控制該行為，值為 1 開啟上述機制，為 0 則關閉。

對於機械硬碟來說，是可以減少很多隨機 IO ，因為機械硬碟 IOPS 一般就幾百，減少隨機 IO 就意味著效能提升。

但如果用 SSD 這類 IOPS 較高的裝置，IOPS 往往不是瓶頸，關閉就好，減少 SQL 語句的響應時間。

在 8.0 中，已經預設是 0 了.

總結

這篇中，主要介紹了 WAL 時的 flush 操作可能會造成 MySQL 突然的效能下降。

引起的原因一般是由於記憶體不夠導致的，進而可以通過設定合適的 innodb_io_capacity 引數，來控制 InnoDB flush 的過程。

相關文章