使用 SSD 的 MySQL IO 優化

aaanly 8年前發布 | 17K 次閱讀數據庫優化 MySQL 數據庫服務器

一、背景

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

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

找到問題的可能所在，試圖在 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 狀態。

二、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 的隨即寫要好很多，因為隨即寫會帶來大量的擦除。

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

三、基于 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（輸出）請求的數量。

四、A 項目 MySQL 主從關系圖

A 項目 MySQL 主從關系如圖一：

使用 SSD 的 MySQL IO 優化

圖一 A 項目 MySQL 主從關系圖

五、程序切換之前調優

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

5.1 修改系統 IO 調度算法

系統默認的 I/O 調度算法是 CFQ，我們試圖先修改之。至于為什么修改，可以查看第四節。

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

echo “noop” > /sys/block/sda/queue/scheduler

如果想永久生效，需要更改 /etc/grup.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% 左右，整個系統的參數趨于穩定。

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

六、程序切換之后調優

在業務低峰，凌晨 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

七、小結

此次針對 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.5 Optimizing for InnoDB Tables

來源：http://blog.itpub.net/29096438/viewspace-2121974/

本文由用戶 aaanly 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1469024366974.html

數據庫優化 MySQL 數據庫服務器

使用 SSD 的 MySQL IO 優化

一、背景

二、SSD 特性

三、基于 SSD 的數據庫優化

四、A 項目 MySQL 主從關系圖

五、程序切換之前調優

六、程序切換之后調優

七、小結

八、參考

相關經驗

相關資訊

相關文檔

目錄