MySQL中IO問題的深入分析與優化

前言

在業務迭代中，隨著資料量的上升，會出現慢SQL情況，但是當我們去分析單條SQL的時候，發現其執行速度並沒有那麼慢，原因是什麼呢，那麼就可能是RDS伺服器IO產生了瓶頸。

日常，我們可以通過 IOPS(Input/Output Per Second) 指標來衡量 IO 是否處於健康的範圍。我們使用的阿里雲 RDS 通常根據不同的規格做了不同的 IOPS 限制。如果短時間內頻繁的操作，不管是 SELECT 帶來的讀磁碟操作，還是 INSERT、UPDATE、DELETE 帶來的寫磁碟操作，均可能會觸發最大 IOPS 限制。本文將從實際業務分析，探討根據 IOPS、Redo 寫次數等指標定位 IO 觸發瓶頸的原因，如何優化。

一、業務背景

活動 MySQL 規格：4C，最大連線數 2500，最大 IOPS 4500。

早上 10 點，是活動業務 QPS 最高的時候，因為這時候通常會釋放獎品庫存。有段時間，監控爆出了慢 SQL 的問題，但是通過監控指標觀測 QPS 的時候，並沒有到達預想中的峰值，但是讀寫RT會出現一些突刺。再進而檢視 IOPS 指標，我們發現異常得高，如下圖：

阿里雲 RDS 中 MySQL 的 IOPS 指標

阿里雲 RDS 機器的 IOPS 指標

你可能會發現 RDS 範例最大限制不是 4500 嗎？為何這裡已經達到了 11000 以上了呢？起初我理解的是 MySQL 統計 IOPS，大部分操作都命中了緩衝區，限制的磁碟  IOPS。後面也諮詢了 DBA，說是 IOPS 其實沒辦法準確限制。這到底是什麼情況？我們接著往後看。

這時候會統計出來一些查詢類的慢 SQL，我們優先去分析這些 SQL 的執行計劃，發現其走了索引，也會回表，掃描的行數比較大：

同期慢 SQL 統計

產生慢 SQL 的表，是一張業務明細表，每個使用者平均每天產生的資料量約 20 條，假如日活 5w 的話，每天的增量 100w，半年產生的資料約 2 個億，該業務已持續運營 一年以上。那麼面對這樣的場景，我們該如何定位原因、如何動手優化呢？

二、分析方法

各個業務線有很多預警、告警，很容易監控到 RDS 執行異常問題。當我們拿到異常的時候，首先肯定是通過監控圖表觀測技術指標，確定影響範圍，設計止血方案，然後才是定位問題，解決問題。

相對來說，IOPS 過高等告警都是短暫性的，一般發生在業務高峰期。這種情況經常是漸變產生的，隨著業務增長，資料量也在增長，表結構也越來越複雜，一些早期的 SQL 在索引選擇上發生了變化，取得目標資料掃描的行數越來越多。

1. MySQL 指標

上面的業務背景中資料庫 QPS 峰值 1 w，TPS峰值 2.5 k。下面結合這個前提來分析 MySQL 的執行指標。除了上面提到的 IOPS 指標，Buffer pool 請求次數、Redo 寫次數等資料指標，這些健康指標協同起來看，會發現該時段真實產生的讀、寫操作都比較頻繁。

其中 innodb_rows_read 已經達到 22w 以上，innodb_rows_updated 達到 1w 以上，相對來說讀操作被放大了 22 倍，寫操作被放大了 4 倍。

(1)  Redo 寫次數

(2) Row Operations

(3) Buffer Pool 請求次數

(4) 慢 SQL

(5) 其他指標

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

這些指標阿里雲未在健康圖表上給出，應該是覺得目前的圖表已經夠用了。這些指標可通過登入 RDS 執行  show global status like '%innodb%read%'  檢視，但是這類指標一般是累計值，需要對比上一個取值時間的差值才能有比較實際的作用，通常也是用來判斷 MySQL 的讀寫比例用，結合上表的 pending 資料和其他的系統指標來綜合判斷 IO 系統的負載。

2. 機器I/O分析

一般情況，業務開發無法直接或者間接存取 RDS 機器的，經常由 DBA 統一管理。這裡，我們可以瞭解一下 Linux下I/O 分析工具。

(1) iostat

iostat -x

關於 CPU 的指標，我們重點看  %iowait 和 %idle 兩個指標。

• %iowait：CPU 等待輸入輸出完成時間的百分比；

  %idle：CPU 空閒時間百分比。

若%iowait 的值過高，則表示硬碟存在 I/O 瓶頸；若 %idle 值高，表示 CPU 較空閒。如果 %idle 值高但系統響應慢時，有可能是 CPU 等待分配記憶體，此時應加大記憶體容量。%idle 值如果持續低於 10，那麼系統的 CPU 處理能力相對較低，表明系統中最需要解決的資源是 CPU。

關於 Disk 指標，我們重點看 %utils、svctm、await 和 avgque-sz幾個指標。

• avgqu-sz: 平均 I/O 佇列長度；

  await: 平均每次裝置 I/O 操作的等待時間 (毫秒)；

  svctm: 平均每次裝置 I/O 操作的服務時間 (毫秒)；

  %util: 一秒中有百分之多少的時間用於 I/O 操作，即被 I/O 消耗的 CPU 百分比

若 %util 接近 100%，說明產生的 I/O 請求太多，I/O 系統已經滿負荷，該磁碟可能存在瓶頸；若 svctm 比較接近 await，說明 I/O 幾乎不需要等待；若 await 遠大於 svctm，說明 I/O 佇列太長，I/O 響應太慢，則需要進行必要優化；若 avgqu-sz 比較大，也表示有大量 IO 在等待。

(2) iotop

iotop -oP

通過輸出結果，我們可以清晰地瞭解當前哪些程序在讀寫磁碟，以及讀寫速率和 IO 使用佔比。

綜上，通過 MySQL 指標及機器執行指標分析當前 MySQL 的 IO 健康狀態，以及 IO 負載過高時的慢 SQL，我們再從慢 SQL 來分析其執行計劃，從而根據具體業務場景來制定優化方案。

三、解決方案

當我們業務中遇到IO問題時，我們可以從以下幾個方面考慮：SQL優化、設定優化、儲存優化和硬體升級優化。

1. 硬體升級

硬體升級，可以說是解決常規效能問題的最有效且快速的方法。不管程式碼層面、 SQL 層面是多麼低效，高配或者超配的硬體規格都能規避效能問題。在一些線上緊急問題處理場景中，不失為一種最優的快速止血方案。

比如上述的業務背景，IOPS 觸發了機器的限制，那麼我們將RDS升配至中等設定，IOPS 上限提高到 9000，便可以快速解決。問題是是否真的緊急和必要，其實 90% 業務場景的緊急程度並沒有那麼高，硬體升級也不是最合適的方案。

2. 儲存優化

我一般將儲存優化理解成分庫分表、資料歸檔兩個方面。何時進行資料歸檔，何時進行分庫分表，也是老生常談的問題。

• 資料歸檔：一般適用於歷史資料幾乎沒有存取場景，比如說上一個賽季的金幣記錄、半年前的領取的活動津貼。這些歷史資料的歸檔對於當前業務沒有任何影響，資料量又增長得比較快。歸檔後只作為演演算法優化的底層資料，對業務介面的效能是非常有幫助的。

• 分庫分表：歷史資料有使用場景。比如說某個使用者的歷史訂單，或者就是使用者資料本身。這些資料不知什麼時候用到，但又必須支援提供的。很長一段時間內都是很大量級存在的業務資料，建議分庫分表。

那麼做了以上兩個優化後，對 IO 的正向影響就是減少了資料量，一些慢 SQL 掃描的行數自然下降。

3. SQL優化

SQL 優化又分為兩個方向，既有索引下 SQL 語句的優化和索引調整層面的優化。根據具體業務場景及資料調整索引策略，這個方面沒什麼好說的，儘可能使得掃描的行數降低。

4. 設定優化

針對讀操作場景，我們可以使用 innodb_buffer_pool_size 來減少 I/O 負載。

• innodb_buffer_pool_size

我們可以通過此引數指定緩衝池的大小。如果緩衝池很小並且有足夠的記憶體，那麼通過減少查詢存取InnoDB表所需的磁碟 I/O 量可以提高緩衝池的效能，從而提高效能。innodb_buffer_pool_size 選項是動態的，允許在不重新啟動伺服器的情況下設定緩衝池大小。

#設定大小
set global innodb_buffer_pool_size = 26843545600

針對寫操作頻繁的場景，我們可以利用 undo/redo log 和 binlog 的寫入磁碟機制，來分析和設定這些引數：

• innodb_flush_log_at_trx_commit

此項設定用來針對 undo/redo log 的磁碟寫入設定。有3個取值:

• • 0：會每隔1秒把快取中的 undo/redo log 寫入到磁碟；

  • 1：每次提交事務（一般的 insert 和 update 都有事務）寫入到磁碟，該方案最安全，也是最慢的；

  • 2：寫入系統的快取，但會每隔一秒才呼叫檔案系統的“flush”將快取重新整理到磁碟上去。這樣 MySQL 即使崩了，系統快取還在，比 0 的方案優。

     

如果我們可以在資料庫伺服器宕機的時候，允許有 1 秒的資料丟失，其實用設定為 2 是最優的方案，可以提高效能。

#檢視當前設定
show variables like 'innodb_flush_log_at_trx_commit'; 
#設定生效
set global innodb_flush_log_at_trx_commit=2;

• sync_binlog

此項設定用來針對 binlog 的磁碟寫入設定，可以用來設定合併多少條 binlog 一次性寫入磁碟。

• • 0：代表依賴系統執行合併寫入；

  • 1：代表每次提交事務後都需要寫入，方案最安全，也是最慢的；

  • N（一般100-1000）：代表每N條後，合併寫入磁碟。

針對sync_binlog，同樣允許資料庫伺服器宕機的情況下能接受丟失N條資料的， 可以設定為N，能提高效能。

#檢視當前設定
show variables like 'sync_binlog'; 
#設定生效
set global sync_binlog=100;

四、總結

最後簡單總結一下 IO 問題分析，上面主要分析的是我們現在的活動業務，也就是隨機讀寫頻繁的場景，這時候 IOPS 是最為關鍵的衡量指標。另一個重要指標是資料吞吐量 (Throughput)，指單位時間內可以成功傳輸的資料數量。對於大量順序讀寫的應用，我們可以關注吞吐量指標。

通常我們可以通過硬體升級、SQL 優化、表結構優化、分庫分表、資料歸檔等方向去做優化策略，適當地採用一種或幾種協同是比較好的解決方案。

參考目錄

• https://developer.aliyun.com/article/603735

• https://www.modb.pro/db/45779

• https://cloud.tencent.com/developer/article/1748024

到此這篇關於MySQL中IO問題的深入分析與優化的文章就介紹到這了,更多相關MySQL中IO問題分析內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！