IO問題成頑疾，鵝廠專家來教你

| 作者 王文安，騰訊CSIG資料庫專項的資料庫工程師，主要負責騰訊云資料庫 MySQL 的相關的作業，熱愛技術，歡迎留言進行交流，

在日常作業中，有時候會發現 MySQL 的狀態不太對勁，這時候就會看看監控指標，可能會發現：寫入 QPS 開始出現毛刺，或者 IO 的指標很高，這時候該怎么辦呢？本文會從 Linux 層面入手，根據不同的 IO 特點來分析 MySQL 資料庫可能遇到的問題，并給出一些可參考的優化/緩解思路，

一、怎么看懂 IO 指標？

檢查 IO 的問題會使用iostat這個命令，這里展示一下命令的效果（iostat -x 1 -m，debian 10.2）：

iostat

avg-cpu 自然就是 CPU 相關的指標，判斷 IO 問題時可以關注 %iowait，其他指標的意義如下：

·r/s 和 w/s：合并過后的讀請求和寫請求的每秒請求數，可以當做 IOPS 來理解，

·rMB/s 和 wMB/s：磁盤的讀寫吞吐量，

·rrqm/s 和 wrqm/s：每秒合并的讀請求和寫請求數量，

·%rrqm 和 %wrqm：合并的讀請求和寫請求百分比，

·r_await 和 w_await：讀請求和寫請求的平均回應時間，包含真正的處理時間和佇列中的等待時間（ms），

·aqu-sz：平均佇列深度，

·rareq_sz 和 wareq_sz：一個讀請求和寫請求的平均物理大小（KB），

·scvtm：計算出來的平均 IO 回應時間，目前已經不準確，不用再關注，

·%util：如果使用了 RAID 或者 SSD，則忽略這個指標，僅在單塊機械盤上準確，

一般來說，評價一塊 IO 設備（忽略機械盤的情況，沒有評價的意義）是否達到了高負載情況，可以看這幾個指標：r/s，w/s，rMB/s，wMB/s，r_await，w_await，aqu-sz，

二、MySQL 與 IO

由于 MySQL 涉及到 IO 相關的引數會比較多，因此這里僅一部分經常用到的引數以及在測驗&模擬中使用默認設定：

引數

設定

備注

innodb_io_capacity

16000

定義了后臺任務可用的 IOPS 量

innodb_io_capacity_max

32000

定義了后臺任務可用的最大 IOPS 量

innodb_flush_log_at_trx_commit

1

控制事務的提交策略，具體資訊請參考官方檔案

sync_binlog

1

控制 binlog 落盤的頻率，具體資訊請參考官方檔案

innodb_io_capacity 和 innodb_io_capacity_max 是最直接限制 IOPS 的指標，大多數時候，SSD 可以設定成 16000 或者更高的數值，如果是云主機或者其他的共享存盤設備，則需要了解一下詳細的 IOPS 上限再具體調整，trx_commit 和 sync_binlog 這兩個引數也放進來的原因是不同的引陣列合對 IO 的壓力也會有區別，通常的用法是雙 1 或者 20（二零），參考官方檔案的描述，雙 1 在每次提交事務的時候都會刷盤，對 IO 的壓力要高不少；20 則是滯后刷盤，對 IO 的壓力會較小，因此寫入 QPS 會高一些，

另外，可以關注到一個細節，innodb_io_capacity 的描述物件是：后臺任務，這代表著 MySQL 后臺的 flush，purge 操作會受到這個引數設定的限制，

三、測驗環境

本次測驗使用騰訊云服務器的高 IO 型 IT3 實體，自帶了 3TB 的本地 NVME，由于騰訊云平臺限制了系統版本（debian 9），因此 iostat 在輸出內容上稍有差異，但是不影響分析，簡單用 fio 跑了一下 16k（innodb_page_size 的默認配置） 的 IO 性能：

型別

IOPS

吞吐量（MB）

隨機讀

121959

1905

隨機寫

98326

1536

隨機讀寫（讀部分）

47129

750

隨機讀寫（寫部分）

47152

754

那么，為什么測驗環境要用一個完全不會有 IO 瓶頸的呢？這是為了方便展示調整 MySQL 之后的效果，如果整套系統的 IO 設備負載長期處于高水位的話，最佳優化策略是升級 IO 設備，而不是調整 MySQL，因此所有的分析和應對的場景都屬于中、短時間內的高 IO 負載，

四、IO 分析

1. 純寫入

先看一種比較純粹，但是較少出現的 IO 負載場景：

iostat_wo

這種型別的指標有一個明顯的特點：IO 負載中沒有，或者幾乎沒有讀取相關的壓力，這種負載的特征一般是快取足夠放下所有的資料，因此不需要從磁盤上讀資料，壓力全部在寫入上，

首先能想到的，顯然是trx_commit 和 sync_binlog 這兩個引數，把雙 1 改成 20 的配置，產生 QPS 變化的原因也比較好理解：原本一個事務需要刷一次磁盤，變成多個事務刷盤操作合并到了一起，就像是提高了每個 IOPS 的“事務處理效率”，比如從 1 事務/IOPS 變成了 N 事務/IOPS，

除了提高“每個 IOPS 的事務處理效率”以外，其實還會有另外一種思路：適當限制后臺任務的 IOPS，實際上 MySQL 的寫入會涉及到非常多的 buffer，log，并產生后臺任務相關的資料，出現中等時間的高寫入場景時，后臺任務一般會慢慢堆積需要 flush 和 purge 的資料，如果 innodb_io_capacity 和 innodb_io_capacity_max 的引數設定得比較高，可能會讓后臺任務消耗過多的 IO 資源，這時候適當調低一些可以在一段時間內穩住寫入 QPS，等高寫入的壓力過去之后再回滾設定，

另外，如果有更加精細化的調整方式，應該會有更好的效果，目前只能靠這個引數一刀切，不過不要改得太低，因為當后臺任務堆積的資料過多，觸發強制刷臟/checkpoint 等機制時，會大幅度的侵占 IO 資源，導致非常劇烈的寫入 QPS 波動，這一點需要注意，

這里給出“反向調整”的效果，日志資料取自于某一個 sysbench 客戶端，在 2050s 左右的時候大幅度調高了 io_capacity：

2. 純讀取

另外一種比較純粹的場景，自然就是純讀取了，例如：

iostat_ro

純讀取的 IO 特征說明快取不夠大，需要從磁盤讀取熱資料，那么增加記憶體和調高 innodb_buffer_pool_size，把更多的資料放到記憶體中就是最好的解決方案，至于需要加多少記憶體，可以結合實際業務 SQL 的回應時間（做好索引優化之后）和 buffer_pool 的命中率，從經驗值來看，命中率（show engine innodb status里面）高于 99.5% 是比較理想的，如果實際 SQL 的回應時間不滿足業務的需求，那么就可以根據實際命中率來估算需要的記憶體大小，

由于從 5.7 開始，MySQL 支持動態調整 innodb_buffer_pool_size 這個引數了，因此變更帶來的影響相對小了很多，不過調整還是有代價的，盡量在業務低峰期操作，

3. 讀寫混合

最常見的肯定是讀寫混合的場景，比如像這樣子的：

iostat_rw

分析起來會相對復雜一點，但是結合純讀取和純寫入的分析之后，可以比較容易想到如下的可能性：

場景一：讀寫混合的場景，

場景二：純寫入的場景，但是記憶體放不下所有的資料，需要從磁盤讀取之后再修改，

先看比較簡單的場景2，本質上還是類似于純寫入場景，但是由于記憶體不夠大，因此在排查 MySQL 的讀寫 SQL 比例（global status 中的 com_xxx 系列資料）之后，可以參考純寫入這個章節的內容進行分析處理，

雖然場景 1 會復雜一些，但是結合純寫和純讀的內容，分析的思路就有了，比如依次思考如下問題：

業務讀寫比例大概是多少？

IO 系統的讀性能問題比較大還是寫性能問題比較大？

如果說：

業務讀的比例高（例如 >4:1），IO 系統讀的性能問題比較大：那么參考純讀取的內容，調高 buffer_pool_size ，

業務讀的比例高（例如 >4:1），IO 系統寫的性能問題比較大：那么參考純寫入的內容，調整事務提交策略或者 io_capacity，另外，此類場景可能是因為在大批量變更資料，也可以考慮一下優化這種業務行為，

業務寫的比例高（例如<4:1），IO 系統讀的性能問題比較大：那么參考純讀取的內容，

業務寫的比例高（例如<4:1），IO 系統寫的性能問題比較大：那么參考純寫入的內容，

業務的讀寫比例沒有什么明顯的特點，IO 系統讀寫的性能問題都比較嚴重：考慮以上所有的方法，包括升級硬體，

4.一些tips：

吞吐量，IOPS 和一些分散讀寫壓力的手段

吞吐量和 IOPS ，一般情況下衡量 IO 系統性能最直觀的指標，并沒有特別的提及，主要原因還是判斷起來很簡單：如果iostat的指標已經達到或者接近了實際硬體的指標（比如達到了 75%），那么根據業務量增長的情況及早規劃硬體升級或者其他的手段來分散讀寫壓力，

常規的手段，可以簡單的遵循以下場景來酌情使用：讀多寫少讀寫分離，寫多讀少拆庫拆表加快取，

判斷 MySQL IO 情況的指標

如果 MySQL 在 IO 方面出現了阻塞的現象，那么可以觀察以下幾個指標：

引數名

意義

備注

Innodb_data_pending_fsyncs

當前阻塞的 fsync 操作

一般為 0，比較高的話，看一下 innodb_flush_method 的設定

Innodb_data_pending_reads

當前阻塞的 read 操作

一般為 0，如果指標較高且影響業務的話，參考讀壓力的應對方式

Innodb_data_pending_writes

當前阻塞的 write 操作

一般為 0，如果指標較高且影響業務的話，參考寫壓力的應對方式

Innodb_os_log_pending_fsyncs

寫 redo log 時，當前阻塞的 fsync 操作

一般為 0，如果大于 0 的話，通常就是 IO 設備的瓶頸，考慮把 redo log 遷移到 SSD 或者做 IO 隔離，獨占 IO 設備的性能

Innodb_os_log_pending_writes

寫 redo log 時，當前阻塞的 write 操作

一般為 0，如果指標較高且影響業務的話，參考寫壓力的應對方式

InnoDB 還有很多其他的 read 和 write 的指標，通過show global status like '%innodb%read%'之類的操作都可以看到，但是這類指標一般是累計值，需要對比上一個取值時間的差值才能有比較實際的作用，通常也是用來判斷 MySQL 的讀寫比例用，結合上表的 pending 資料和其他的系統指標來綜合判斷 IO 系統的負載，這些指標也是建議監控起來的，

五、總結

解決 IO 問題的手段是多樣化的：最省事的升級硬體；最快捷的調整 MySQL（本文主要內容）；比較常用的架構調整手段（讀寫分離，拆庫拆表）；結合實際情況來優化業務的行為（合并單行操作的 DML，拆分單個大量更新資料的 DML 陳述句等），

雖然不能對上述手段進行全面的介紹，但是iostat提供的資訊在分析 MySQL 瓶頸時還是非常有用的，本文僅從硬體的負載特點出發，簡述了調整 MySQL 的一些思路，實際上需要多種手段結合起來才能比較好的應對 IO 方面的問題，

本文由博客一文多發平臺 OpenWrite 發布！

標籤：其他

上一篇：Redis為什么這么快？

下一篇：高并發下，如何讓你的資料庫再快一點？