SSD 下的 MySQL IO 優化嘗試

溫國兵的隨想錄發表於2015-09-01
MySql優化

1、背景

在閱讀這篇文章之前，讀者需要注意的是，為了維護隱私，用 MySQL 伺服器的 D 段代替完整 IP，並且略去一些私密資訊。

A 專案，因 I/O 出現規律性地劇烈波動。每 15 分鐘落地一次，innodbBuffPoolPagesFlushed 引數監控波峰和波谷交替出現，磁碟 I/O 同樣如此，並且 until 達到 100%。經過排查，排除了觸發器、事件、儲存過程、前端程式定時器、系統 crontab 的可能性。最終定位為 InnoDB 日誌切換，但是否完全是日誌造成的影響，還有待進一步跟蹤和分析。

SSD 下的 MySQL IO 優化嘗試

找到問題的可能所在，試圖在 24 主庫上做了如下調整：

  • 關閉 Query Cache；
  • 設定 InnoDB Log 大小為 1280M；
  • 設定 innodb_max_dirty_pages_pct 為 30，innodb_io_capacity 保持 200 不變。

做了如上調整以後，I/O 趨於平穩，沒有再出現大的波動。

為了保險起見，A 專案方面決定採用配有 SSD 的機型，對主庫進行遷移，同時對 24 的從庫 27 進行遷移。待遷移完成後，在新的主庫 39 上，針對 SSD 以及 MySQL InnoDB 引數進行優化。待程式切換完成後，再次對針對 SSD 以及 MySQL InnoDB 引數進行優化。也就是說在上線前後進行優化，觀察 I/O 狀態。

2、SSD 特性

眾所周知，SSD 的平均效能是優於 SAS 的。SSD 能解決 I/O 瓶頸，但網際網路行業總要權衡收益與成本的。目前記憶體資料庫是這個領域的一大趨勢，一方面，越來越多的應用會往 NoSQL 遷移。另一方面，重要資料總要落地，傳統的機械硬碟已經不能滿足目前高併發、大規模資料的要求。總的來說，一方面，為了提高效能，儘可能把資料記憶體化，這也是 InnoDB 儲存引擎不斷改進的核心原則。後續的 MySQL 版本已經對 SSD 做了優化。另一方面，儘可能上 SSD。

SSD 這麼神祕，接下來我們看看它有哪些特性：

  • 隨機讀能力非常好，連續讀效能一般，但比普通 SAS 磁碟好；
  • 不存在磁碟尋道的延遲時間，隨機寫和連續寫的響應延遲差異不大。
  • erase-before-write 特性，造成寫入放大，影響寫入的效能；
  • 寫磨損特性，採用 Wear Leveling 演算法延長壽命，但同時會影響讀的效能；
  • 讀和寫的 I/O 響應延遲不對等（讀要大大好於寫），而普通磁碟讀和寫的 I/O 響應延遲差異很小；
  • 連續寫比隨機寫效能好，比如 1M 順序寫比 128 個 8K 的隨即寫要好很多，因為隨即寫會帶來大量的擦除。

總結起來，也就是隨機讀效能較連續讀效能好，連續寫效能較隨機寫效能好，會有寫入放大的問題，同一位置插入次數過多容易導致損壞。

3、基於 SSD 的資料庫優化

基於 SSD 的資料庫優化，我們可以做如下事情：

  • 減少對同一位置的反覆擦寫，也就是針對 InnoDB 的 Redo Log。因為 Redo Log 儲存在 ib_logfile0/1/2，這幾個日誌檔案是複寫，來回切換，必定會帶來同一位置的反覆擦寫；
  • 減少離散寫入，轉化為 Append 或者批量寫入，也就是針對資料檔案；
  • 提高順序寫入的量。

具體來說，我們可以做如下調整：

  • 修改系統 I/O 排程演算法為 NOOP；
  • 提高每個日誌檔案大小為 1280M（調整 innodb_log_file_size）；
  • 通過不斷調整 innodb_io_capacity 和 innodb_max_dirty_pages_pct 讓落地以及 I/O 水平達到均衡；
  • 關閉 innodb_adaptive_flushing，檢視效果；
  • 修改 innodb_write_io_threads 和 innodb_read_io_threads。

針對系統 I/O 排程演算法，做如下解釋。系統 I/O 排程演算法有四種，CFQ(Complete Fairness Queueing，完全公平排隊 I/O 排程程式)、NOOP(No Operation，電梯式排程程式)、Deadline（截止時間排程程式）、AS(Anticipatory，預料 I/O 排程程式)。

下面對上述幾種排程演算法做簡單地介紹。

CFQ 為每個程式/執行緒，單獨建立一個佇列來管理該程式所產生的請求，也就是說每個程式一個佇列，各佇列之間的排程使用時間片來排程，以此來保證每個程式都能被很好的分配到 I/O 頻寬，I/O 排程器每次執行一個程式的 4 次請求。

NOOP 實現了一個簡單的 FIFO 佇列，它像電梯的工作主法一樣對 I/O 請求進行組織，當有一個新的請求到來時，它將請求合併到最近的請求之後，以此來保證請求同一介質。

Deadline 確保了在一個截止時間內服務請求，這個截止時間是可調整的，而預設讀期限短於寫期限，這樣就防止了寫操作因為不能被讀取而餓死的現象。

AS 本質上與 Deadline 一樣，但在最後一次讀操作後，要等待 6ms，才能繼續進行對其它 I/O 請求進行排程。可以從應用程式中預訂一個新的讀請求，改進讀操作的執行，但以一些寫操作為代價。它會在每個 6ms 中插入新的 I/O 操作，而會將一些小寫入流合併成一個大寫入流，用寫入延時換取最大的寫入吞吐量。

在 SSD 或者 Fusion IO，最簡單的 NOOP 反而可能是最好的演算法，因為其他三個演算法的優化是基於縮短尋道時間的，而固態硬碟沒有所謂的尋道時間且 I/O 響應時間非常短。

還是用資料說話吧，以下是 SSD 下針對不同 I/O 排程演算法所做的 I/O 效能測試，均為 IOPS。

I/O Type NOOP Anticipatory Deadline CFQ
Sequential Read 22256 7955 22467 8652
Sequential Write 4090 2560 1370 1996
Sequential RW Read 6355 760 567 1149
Sequential RW Write 6360 760 565 1149
Random Read 17905 20847 20930 20671
Random Write 7423 8086 8113 8072
Random RW Read 4994 5221 5316 5275
Random RW Write 4991 5222 5321 5278

可以看到，整體來說，NOOP 演算法略勝於其他演算法。

接下來講解需要調整的 InnoDB 引數的含義：

  • innodb_log_file_size：InnoDB 日誌檔案的大小；
  • innodb_io_capacity：緩衝區重新整理到磁碟時，重新整理髒頁數量；
  • innodb_max_dirty_pages_pct：控制了 Dirty Page 在 Buffer Pool 中所佔的比率；
  • innodb_adaptive_flushing：自適應重新整理髒頁；
  • innodb_write_io_threads：InnoDB 使用後臺執行緒處理資料頁上寫 I/O（輸入）請求的數量；
  • innodb_read_io_threads：InnoDB 使用後臺執行緒處理資料頁上讀 I/O（輸出）請求的數量。

4、A 專案 MySQL 主從關係圖

A 專案 MySQL 主從關係如圖一：

SSD 下的 MySQL IO 優化嘗試
圖一 A 專案 MySQL 主從關係圖

5、程式切換之前調優

程式切換之前，39 只是 24 的從庫，所以 IO 壓力不高，以下的調整也不能說明根本性的變化。需要說明一點，以下調整的平均間隔在 30 分鐘左右。

5.1 修改系統 IO 排程演算法

系統預設的 I/O 排程演算法 是 CFQ，我們試圖先修改之。至於為什麼修改，可以檢視第3節。

具體的做法如下，需要注意的是，請根據實際情況做調整，比如你的系統中磁碟很可能不是 sda。

echo "noop" > /sys/block/sda/queue/scheduler

如果想永久生效，需要更改 /etc/grub.conf，新增 elevator，示例如下：

kernel /vmlinuz-x.x.xx-xxx.el6.x86_64 ro root=UUID=e01d6bb4-bd74-404f-855a-0f700fad4de0 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun1
6 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=noop rhgb quiet

此步調整做完以後，檢視 39 I/O 狀態，並沒有顯著的變化。

5.2 修改 innodb_io_capacity = 4000

在做這個引數調整之前，我們來看看當前 MySQL 的配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 200
innodb_max_dirty_pages_pct 30
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

修改方法如下：

SET GLOBAL innodb_io_capacity = 4000;

網路上的文章，針對 SSD 的優化，MySQL 方面需要把 innodb_io_capacity 設定為 4000，或者更高。然而實際上，此業務 UPDATE 較多，每次的修改量大概有 20K，並且基本上都是離散寫。innodb_io_capacity 達到 4000，SSD 並沒有給整個系統帶來很大的效能提升。相反，反而使 IO 壓力過大，until 甚至達到 80% 以上。

5.3 修改 innodb_max_dirty_pages_pct = 25

修改方法如下：

SET GLOBAL innodb_max_dirty_pages_pct = 25;

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 4000
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

之前已經將 innodb_max_dirty_pages_pct 設定為 30，此處將 innodb_max_dirty_pages_pct 下調為 25%，目的為了檢視髒資料對 I/O 的影響。修改的結果是，I/O 出現波動，innodbBuffPoolPagesFlushed 同樣出現波動。然而，由於 39 是 24 的從庫，暫時還沒有切換，所有壓力不夠大，髒資料也不夠多，所以調整此引數看不出效果。

5.4 修改 innodb_io_capacity = 2000

修改方法不贅述。

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

因為 innodb_io_capacity 為 4000 的情況下，I/O 壓力過高，所以將 innodb_io_capacity 調整為 2000。調整後，w/s 最高不過 2000 左右，並且 I/O until 還是偏高，最高的時候有 70%。我們同時可以看到，I/O 波動幅度減小，innodbBuffPoolPagesFlushed 同樣如此。

5.5 修改 innodb_io_capacity = 1500

修改方法不贅述。

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

I/O 持續出現波動，我們接著繼續下調 innodb_io_capacity，調整為 1500。I/O until 降低，I/O 波動幅度繼續減小，innodbBuffPoolPagesFlushed 同樣如此。

5.6 關閉 innodb_adaptive_flushing

修改方法如下：

SET GLOBAL innodb_adaptive_flushing = OFF;

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing OFF
innodb_write_io_threads 4
innodb_read_io_threads 4

既然落地仍然有異常，那我們可以試著關閉 innodb_adaptive_flushing，不讓 MySQL 干預落地。調整的結果是，髒資料該落地還是落地，並沒有受 I/O 壓力的影響，調整此引數無效。

5.7 開啟 innodb_adaptive_flushing

修改方法如下：

SET GLOBAL innodb_adaptive_flushing = ON;

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 25
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

經過以上調整，關閉 innodb_adaptive_flushing 沒有效果，還是保持預設開啟，讓這個功能持續起作用吧。

5.8 設定 innodb_max_dirty_pages_pct = 20

修改方法不贅述。

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 20
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

接著我們將 innodb_max_dirty_pages_pct 下調為 20，觀察髒資料情況。由於 InnoDB Buffer Pool 設定為 40G，20% 也就是 8G，此時的壓力達不到此閥值，所以調整引數是沒有效果的。但業務繁忙時，就可以看到效果，落地頻率會增高。

5.9 設定 innodb_io_capacity = 1000

修改方法不贅述。

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1000
innodb_max_dirty_pages_pct 20
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

經過以上調整，我們需要的是一個均衡的 IO，給其他程式一些餘地。於是把 innodb_io_capacity 設定為 1000，此時可以看到 I/O until 維持在 10% 左右，整個系統的引數趨於穩定。

後續還要做進一步的監控、跟蹤、分析和優化。

6、 程式切換之後調優

在業務低峰，凌晨 1 點左右，配合研發做了切換。切換之後的主從關係可以檢視第五節。

6.1 設定 innodb_max_dirty_pages_pct = 30，innodb_io_capacity = 1500

修改方法不贅述。

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 1500
innodb_max_dirty_pages_pct 30
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

在 innodb_io_capacity 為 1000，innodb_max_dirty_pages_pct 為 20 的環境下，I/O until 有小幅波動，而且波峰和波谷持續交替，這種情況是不希望看到的。innodbBuffPoolPagesFlushed 比較穩定，但 innodbBuffPoolPagesDirty 持續上漲，沒有下降的趨勢。故做了如下調整：innodb_max_dirty_pages_pct = 30，innodb_io_capacity = 1500。調整完成後，innodbBuffPoolPagesDirty 趨於穩定，I/O until 也比較穩定。

6.2 設定 innodb_max_dirty_pages_pct = 40，innodb_io_capacity = 2000

修改方法不贅述。

修改之後的 MySQL 配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 40
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

針對目前這種 I/O 情況，做了如下調整：innodb_max_dirty_pages_pct = 40，innodb_io_capacity = 2000。

6.3 分析

針對以上兩個調整，我們通過結合監控資料來分析 I/O 狀態。

以下是高速緩衝區的髒頁資料情況，如圖二：

SSD 下的 MySQL IO 優化嘗試
圖二 主庫的髒資料情況

以下是髒資料落地的情況，如圖三

SSD 下的 MySQL IO 優化嘗試
圖三 主庫的髒資料落地情況

28 號早 8 點到下午 7 點，當髒資料上升，也就是在記憶體中的資料更多，那麼落地就會很少，呈現一個平穩的趨勢；當髒資料維持不變，也就是髒資料達到了 innodb_max_dirty_pages_pct 的限額（innodb_buffer_pool_size 為 40G，innodb_max_dirty_pages_pct 為 40%，也就是在記憶體中的髒資料最多為 16G，每個 Page 16K，則 innodbBufferPoolDirtyPages 最大為 1000K），落地就會增多，呈現上升的趨勢，所以才會出現上述圖片中的曲線。

這是最後的配置：

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 40
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

7、小結

此次針對 SSD 以及 MySQL InnoDB 引數優化，總結起來，也就是以下三條：

  • 修改系統 I/O 排程演算法；
  • 分析 I/O 情況，動態調整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；
  • 試圖調整 innodb_adaptive_flushing，檢視效果。

針對 innodb_write_io_threads 和 innodb_read_io_threads 的調優我們目前沒有做，我相信調整為 8 或者 16，系統 I/O 效能會更好。

還有，需要注意以下幾點：

  • 網路文章介紹的方法有侷限性和場景性，不能親信，不能盲從，做任何調整都要以業務優先。保證業務的平穩執行才是最重要的，效能都是其次；
  • 任何一個調整，都要建立在資料的支撐和嚴謹的分析基礎上，否則都是空談；
  • 這類調優是非常有意義的，是真正能帶來價值的，所以需要多下功夫，並且儘可能地搞明白為什麼要這麼調整。

文末，說一點比較有意思的。之前有篇文章提到過 SSDB。SSDB 底層採用 Google 的 LevelDB，並支援 Redis 協議。LevelDB 的設計完全是貼合 SSD 的設計思想的。首先，儘可能地轉化為連續寫；其次，不斷新增資料檔案，防止同一位置不斷擦寫。另外，SSDB 的名字取得也很有意思，也很有水平。我猜想作者也是希望使用者將 SSDB 應用在 SSD 上吧。

8、參考

MySQL 文件： 8.5 Optimizing for InnoDB Tables

（題圖來自：pachosting.hk）

相關文章