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

目前成都創(chuàng)新互聯(lián)已為千余家的企業(yè)提供了網(wǎng)站建設、域名、虛擬空間、網(wǎng)站運營、企業(yè)網(wǎng)站設計、荔灣網(wǎng)站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

 背景

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

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

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

• 關(guān)閉 Query Cache；

• 設置 InnoDB Log 大小為 1280M；

• 設置 innodb_max_dirty_pages_pct 為 30，innodb_io_capacity 保持 200 不變。

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

為了保險起見，A 項目方面決定采用配有 SSD 的機型，對主庫進行遷移，同時對 24 的從庫 27 進行遷移。待遷移完成后，在新的主庫 39 上，針對 SSD 以及 MySQL InnoDB 參數(shù)進行優(yōu)化。待程序切換完成后，再次對針對 SSD 以及 MySQL InnoDB 參數(shù)進行優(yōu)化。也就是說在上線前后進行優(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)域的一大趨勢，一方面，越來越多的應用會往 NOSQL 遷移。另一方面，重要數(shù)據(jù)總要落地，傳統(tǒng)的機械硬盤已經(jīng)不能滿足目前高并發(fā)、大規(guī)模數(shù)據(jù)的要求。總的來說，一方面，為了提高性能，盡可能把數(shù)據(jù)內(nèi)存化，這也是 InnoDB 存儲引擎不斷改進的核心原則。后續(xù)的 MySQL 版本已經(jīng)對 SSD 做了優(yōu)化。另一方面，盡可能上 SSD。

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

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

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

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

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

• 讀和寫的 I/O 響應延遲不對等（讀要大大好于寫），而普通磁盤讀和寫的 I/O 響應延遲差異很?。?/p>

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

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

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

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

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

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

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

具體來說，我們可以做如下調(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 水平達到均衡；

• 關(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，完全公平排隊 I/O 調(diào)度程序)、NOOP(No Operation，電梯式調(diào)度程序)、Deadline（截止時間調(diào)度程序）、AS(Anticipatory，預料 I/O 調(diào)度程序)。

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

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

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

Deadline 確保了在一個截止時間內(nèi)服務請求，這個截止時間是可調(diào)整的，而默認讀期限短于寫期限，這樣就防止了寫操作因為不能被讀取而餓死的現(xiàn)象。

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

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

還是用數(shù)據(jù)說話吧，以下是 SSD 下針對不同 I/O 調(dià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 算法略勝于其他算法。

接下來講解需要調(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：自適應刷新臟頁；

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

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

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

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

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

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

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

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

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

具體的做法如下，需要注意的是，請根據(jù)實際情況做調(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)整之前，我們來看看當前 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)絡上的文章，針對 SSD 的優(yōu)化，MySQL 方面需要把 innodb_io_capacity 設置為 4000，或者更高。然而實際上，此業(yè)務 UPDATE 較多，每次的修改量大概有 20K，并且基本上都是離散寫。innodb_io_capacity 達到 4000，SSD 并沒有給整個系統(tǒng)帶來很大的性能提升。相反，反而使 IO 壓力過大，until 甚至達到 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 設置為 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

因為 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 干預落地。調(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 沒有效果，還是保持默認打開，讓這個功能持續(xù)起作用吧。

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

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

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

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

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

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

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 的環(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 設置 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)控數(shù)據(jù)來分析 I/O 狀態(tài)。

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

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

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

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

28 號早 8 點到下午 7 點，當臟數(shù)據(jù)上升，也就是在內(nèi)存中的數(shù)據(jù)更多，那么落地就會很少，呈現(xiàn)一個平穩(wěn)的趨勢；當臟數(shù)據(jù)維持不變，也就是臟數(shù)據(jù)達到了 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 性能會更好。

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

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

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

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

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

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

本文題目：如何進行MysqlSSD下的MySQLIO優(yōu)化
地址分享：http://chinadenli.net/article6/jsicog.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護、商城網(wǎng)站、自適應網(wǎng)站、移動網(wǎng)站建設、網(wǎng)站內(nèi)鏈、網(wǎng)站排名

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)