如何進(jìn)行Mysql SSD下的MySQL IO 優(yōu)化

這篇文章給大家介紹如何進(jìn)行Mysql SSD下的MySQL IO 優(yōu)化，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

 背景

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

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

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

• 關(guān)閉 Query Cache；

• 設(shè)置 InnoDB Log 大小為 1280M；

• 設(shè)置 innodb_max_dirty_pages_pct 為 30，innodb_io_capacity 保持 200 不變。

做了如上調(diào)整以后，I/O 趨于平穩(wěn)，沒有再出現(xiàn)大的波動。

為了保險起見，A 項(xiàng)目方面決定采用配有 SSD 的機(jī)型，對主庫進(jìn)行遷移，同時對 24 的從庫 27 進(jìn)行遷移。待遷移完成后，在新的主庫 39 上，針對 SSD 以及 MySQL InnoDB 參數(shù)進(jìn)行優(yōu)化。待程序切換完成后，再次對針對 SSD 以及 MySQL InnoDB 參數(shù)進(jìn)行優(yōu)化。也就是說在上線前后進(jìn)行優(yōu)化，觀察 I/O 狀態(tài)。

SSD 特性

眾所周知，SSD 的平均性能是優(yōu)于 SAS 的。SSD 能解決 I/O 瓶頸，但互聯(lián)網(wǎng)行業(yè)總要權(quán)衡收益與成本的。目前內(nèi)存數(shù)據(jù)庫是這個領(lǐng)域的一大趨勢，一方面，越來越多的應(yīng)用會往 NoSQL 遷移。另一方面，重要數(shù)據(jù)總要落地，傳統(tǒng)的機(jī)械硬盤已經(jīng)不能滿足目前高并發(fā)、大規(guī)模數(shù)據(jù)的要求。總的來說，一方面，為了提高性能，盡可能把數(shù)據(jù)內(nèi)存化，這也是 InnoDB 存儲引擎不斷改進(jìn)的核心原則。后續(xù)的 MySQL 版本已經(jīng)對 SSD 做了優(yōu)化。另一方面，盡可能上 SSD。

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

• 隨機(jī)讀能力非常好，連續(xù)讀性能一般，但比普通 SAS 磁盤好；

• 不存在磁盤尋道的延遲時間，隨機(jī)寫和連續(xù)寫的響應(yīng)延遲差異不大。

• erase-before-write 特性，造成寫入放大，影響寫入的性能；

• 寫磨損特性，采用 Wear Leveling 算法延長壽命，但同時會影響讀的性能；

• 讀和寫的 I/O 響應(yīng)延遲不對等（讀要大大好于寫），而普通磁盤讀和寫的 I/O 響應(yīng)延遲差異很??；

• 連續(xù)寫比隨機(jī)寫性能好，比如 1M 順序?qū)懕?128 個 8K 的隨即寫要好很多，因?yàn)殡S即寫會帶來大量的擦除。

總結(jié)起來，也就是隨機(jī)讀性能較連續(xù)讀性能好，連續(xù)寫性能較隨機(jī)寫性能好，會有寫入放大的問題，同一位置插入次數(shù)過多容易導(dǎo)致?lián)p壞。

 基于 SSD 的數(shù)據(jù)庫優(yōu)化

基于 SSD 的數(shù)據(jù)庫優(yōu)化，我們可以做如下事情：

• 減少對同一位置的反復(fù)擦寫，也就是針對 InnoDB 的 Redo Log。因?yàn)?Redo Log 保存在 ib_logfile0/1/2，這幾個日志文件是復(fù)寫，來回切換，必定會帶來同一位置的反復(fù)擦寫；

• 減少離散寫入，轉(zhuǎn)化為 Append 或者批量寫入，也就是針對數(shù)據(jù)文件；

• 提高順序?qū)懭氲牧俊?

具體來說，我們可以做如下調(diào)整：

• 修改系統(tǒng) I/O 調(diào)度算法為 NOOP；

• 提高每個日志文件大小為 1280M（調(diào)整 innodb_log_file_size）；

• 通過不斷調(diào)整 innodb_io_capacity 和 innodb_max_dirty_pages_pct 讓落地以及 I/O 水平達(dá)到均衡；

• 關(guān)閉 innodb_adaptive_flushing，查看效果；

• 修改 innodb_write_io_threads 和 innodb_read_io_threads。

針對系統(tǒng) I/O 調(diào)度算法，做如下解釋。系統(tǒng) I/O 調(diào)度算法有四種，CFQ(Complete Fairness Queueing，完全公平排隊(duì) I/O 調(diào)度程序)、NOOP(No Operation，電梯式調(diào)度程序)、Deadline（截止時間調(diào)度程序）、AS(Anticipatory，預(yù)料 I/O 調(diào)度程序)。

下面對上述幾種調(diào)度算法做簡單地介紹。

CFQ 為每個進(jìn)程/線程，單獨(dú)創(chuàng)建一個隊(duì)列來管理該進(jìn)程所產(chǎn)生的請求，也就是說每個進(jìn)程一個隊(duì)列，各隊(duì)列之間的調(diào)度使用時間片來調(diào)度，以此來保證每個進(jìn)程都能被很好的分配到 I/O 帶寬，I/O 調(diào)度器每次執(zhí)行一個進(jìn)程的 4 次請求。

NOOP 實(shí)現(xiàn)了一個簡單的 FIFO 隊(duì)列，它像電梯的工作主法一樣對 I/O 請求進(jìn)行組織，當(dāng)有一個新的請求到來時，它將請求合并到最近的請求之后，以此來保證請求同一介質(zhì)。

Deadline 確保了在一個截止時間內(nèi)服務(wù)請求，這個截止時間是可調(diào)整的，而默認(rèn)讀期限短于寫期限，這樣就防止了寫操作因?yàn)椴荒鼙蛔x取而餓死的現(xiàn)象。

AS 本質(zhì)上與 Deadline 一樣，但在最后一次讀操作后，要等待 6ms，才能繼續(xù)進(jìn)行對其它 I/O 請求進(jìn)行調(diào)度。可以從應(yīng)用程序中預(yù)訂一個新的讀請求，改進(jìn)讀操作的執(zhí)行，但以一些寫操作為代價。它會在每個 6ms 中插入新的 I/O 操作，而會將一些小寫入流合并成一個大寫入流，用寫入延時換取最大的寫入吞吐量。

在 SSD 或者 Fusion IO，最簡單的 NOOP 反而可能是最好的算法，因?yàn)槠渌齻€算法的優(yōu)化是基于縮短尋道時間的，而固態(tài)硬盤沒有所謂的尋道時間且 I/O 響應(yīng)時間非常短。

還是用數(shù)據(jù)說話吧，以下是 SSD 下針對不同 I/O 調(diào)度算法所做的 I/O 性能測試，均為 IOPS。

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

接下來講解需要調(diào)整的 InnoDB 參數(shù)的含義：

• innodb_log_file_size：InnoDB 日志文件的大小；

• innodb_io_capacity：緩沖區(qū)刷新到磁盤時，刷新臟頁數(shù)量；

• innodb_max_dirty_pages_pct：控制了 Dirty Page 在 Buffer Pool 中所占的比率；

• innodb_adaptive_flushing：自適應(yīng)刷新臟頁；

• innodb_write_io_threads：InnoDB 使用后臺線程處理數(shù)據(jù)頁上寫 I/O（輸入）請求的數(shù)量；

• innodb_read_io_threads：InnoDB 使用后臺線程處理數(shù)據(jù)頁上讀 I/O（輸出）請求的數(shù)量。

 A 項(xiàng)目 MySQL 主從關(guān)系圖

A 項(xiàng)目 MySQL 主從關(guān)系如圖一：

 A 項(xiàng)目 MySQL 主從關(guān)系圖

 程序切換之前調(diào)優(yōu)

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

1 修改系統(tǒng) IO 調(diào)度算法

系統(tǒng)默認(rèn)的 I/O 調(diào)度算法 是 CFQ，我們試圖先修改之。至于為什么修改，可以查看第四節(jié)。

具體的做法如下，需要注意的是，請根據(jù)實(shí)際情況做調(diào)整，比如你的系統(tǒng)中磁盤很可能不是 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

此步調(diào)整做完以后，查看 39 I/O 狀態(tài)，并沒有顯著的變化。

2 修改 innodb_io_capacity = 4000

在做這個參數(shù)調(diào)整之前，我們來看看當(dāng)前 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;

網(wǎng)絡(luò)上的文章，針對 SSD 的優(yōu)化，MySQL 方面需要把 innodb_io_capacity 設(shè)置為 4000，或者更高。然而實(shí)際上，此業(yè)務(wù) UPDATE 較多，每次的修改量大概有 20K，并且基本上都是離散寫。innodb_io_capacity 達(dá)到 4000，SSD 并沒有給整個系統(tǒng)帶來很大的性能提升。相反，反而使 IO 壓力過大，until 甚至達(dá)到 80% 以上。

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

之前已經(jīng)將 innodb_max_dirty_pages_pct 設(shè)置為 30，此處將 innodb_max_dirty_pages_pct 下調(diào)為 25%，目的為了查看臟數(shù)據(jù)對 I/O 的影響。修改的結(jié)果是，I/O 出現(xiàn)波動，innodbBuffPoolPagesFlushed 同樣出現(xiàn)波動。然而，由于 39 是 24 的從庫，暫時還沒有切換，所有壓力不夠大，臟數(shù)據(jù)也不夠多，所以調(diào)整此參數(shù)看不出效果。

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

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

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 持續(xù)出現(xiàn)波動，我們接著繼續(xù)下調(diào) innodb_io_capacity，調(diào)整為 1500。I/O until 降低，I/O 波動幅度繼續(xù)減小，innodbBuffPoolPagesFlushed 同樣如此。

6 關(guān)閉 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

既然落地仍然有異常，那我們可以試著關(guān)閉 innodb_adaptive_flushing，不讓 MySQL 干預(yù)落地。調(diào)整的結(jié)果是，臟數(shù)據(jù)該落地還是落地，并沒有受 I/O 壓力的影響，調(diào)整此參數(shù)無效。

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

經(jīng)過以上調(diào)整，關(guān)閉 innodb_adaptive_flushing 沒有效果，還是保持默認(rèn)打開，讓這個功能持續(xù)起作用吧。

8 設(shè)置 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 下調(diào)為 20，觀察臟數(shù)據(jù)情況。由于 InnoDB Buffer Pool 設(shè)置為 40G，20% 也就是 8G，此時的壓力達(dá)不到此閥值，所以調(diào)整參數(shù)是沒有效果的。但業(yè)務(wù)繁忙時，就可以看到效果，落地頻率會增高。

9 設(shè)置 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

經(jīng)過以上調(diào)整，我們需要的是一個均衡的 IO，給其他進(jìn)程一些余地。于是把 innodb_io_capacity 設(shè)置為 1000，此時可以看到 I/O until 維持在 10% 左右，整個系統(tǒng)的參數(shù)趨于穩(wěn)定。

后續(xù)還要做進(jìn)一步的監(jiān)控、跟蹤、分析和優(yōu)化。

 程序切換之后調(diào)優(yōu)

在業(yè)務(wù)低峰，凌晨 1 點(diǎn)左右，配合研發(fā)做了切換。切換之后的主從關(guān)系可以查看第五節(jié)。

1 設(shè)置 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 的環(huán)境下，I/O until 有小幅波動，而且波峰和波谷持續(xù)交替，這種情況是不希望看到的。innodbBuffPoolPagesFlushed 比較穩(wěn)定，但 innodbBuffPoolPagesDirty 持續(xù)上漲，沒有下降的趨勢。故做了如下調(diào)整：innodb_max_dirty_pages_pct = 30，innodb_io_capacity = 1500。調(diào)整完成后，innodbBuffPoolPagesDirty 趨于穩(wěn)定，I/O until 也比較穩(wěn)定。

2 設(shè)置 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 情況，做了如下調(diào)整：innodb_max_dirty_pages_pct = 40，innodb_io_capacity = 2000。

3 分析

針對以上兩個調(diào)整，我們通過結(jié)合監(jiān)控?cái)?shù)據(jù)來分析 I/O 狀態(tài)。

以下是高速緩沖區(qū)的臟頁數(shù)據(jù)情況，如圖二：

圖二 主庫的臟數(shù)據(jù)情況

以下是臟數(shù)據(jù)落地的情況，如圖三

圖三 主庫的臟數(shù)據(jù)落地情況

28 號早 8 點(diǎn)到下午 7 點(diǎn)，當(dāng)臟數(shù)據(jù)上升，也就是在內(nèi)存中的數(shù)據(jù)更多，那么落地就會很少，呈現(xiàn)一個平穩(wěn)的趨勢；當(dāng)臟數(shù)據(jù)維持不變，也就是臟數(shù)據(jù)達(dá)到了 innodb_max_dirty_pages_pct 的限額（innodb_buffer_pool_size 為 40G，innodb_max_dirty_pages_pct 為 40%，也就是在內(nèi)存中的臟數(shù)據(jù)最多為 16G，每個 Page 16K，則 innodbBufferPoolDirtyPages 最大為 1000K），落地就會增多，呈現(xiàn)上升的趨勢，所以才會出現(xiàn)上述圖片中的曲線。

這是最后的配置：

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 參數(shù)優(yōu)化，總結(jié)起來，也就是以下三條：

• 修改系統(tǒng) I/O 調(diào)度算法；

• 分析 I/O 情況，動態(tài)調(diào)整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；

• 試圖調(diào)整 innodb_adaptive_flushing，查看效果。

針對 innodb_write_io_threads 和 innodb_read_io_threads 的調(diào)優(yōu)我們目前沒有做，我相信調(diào)整為 8 或者 16，系統(tǒng) I/O 性能會更好。

還有，需要注意以下幾點(diǎn)：

• 網(wǎng)絡(luò)文章介紹的方法有局限性和場景性，不能親信，不能盲從，做任何調(diào)整都要以業(yè)務(wù)優(yōu)先。保證業(yè)務(wù)的平穩(wěn)運(yùn)行才是最重要的，性能都是其次；

• 任何一個調(diào)整，都要建立在數(shù)據(jù)的支撐和嚴(yán)謹(jǐn)?shù)姆治龌A(chǔ)上，否則都是空談；

• 這類調(diào)優(yōu)是非常有意義的，是真正能帶來價值的，所以需要多下功夫，并且盡可能地搞明白為什么要這么調(diào)整。

文末，說一點(diǎn)比較有意思的。之前有篇文章提到過 SSDB。SSDB 底層采用 Google 的 LevelDB，并支持 Redis 協(xié)議。LevelDB 的設(shè)計(jì)完全是貼合 SSD 的設(shè)計(jì)思想的。首先，盡可能地轉(zhuǎn)化為連續(xù)寫；其次，不斷新增數(shù)據(jù)文件，防止同一位置不斷擦寫。另外，SSDB 的名字取得也很有意思，也很有水平。我猜想作者也是希望用戶將 SSDB 應(yīng)用在 SSD 上吧。

關(guān)于如何進(jìn)行Mysql SSD下的MySQL IO 優(yōu)化就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。