MySQL中的IO問題分析與優化

語言: CN / TW / HK

在業務迭代中,隨著資料量的上升,會出現慢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 方面出現了阻塞的現象,也可以觀察以下幾個指標:

引數名

意義

備註

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,如果指標較高且影響業務的話,參考寫壓力的應對方式

這些指標阿里雲未在健康圖表上給出,應該是覺得目前的圖表已經夠用了。這些指標可通過登入 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 兩個指標。

  1. %iowait:CPU 等待輸入輸出完成時間的百分比;

  2. %idle:CPU 空閒時間百分比。

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

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

  1. avgqu-sz: 平均 I/O 佇列長度;

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

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

  4. %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 優化、表結構優化、分庫分表、資料歸檔等方向去做優化策略,適當地採用一種或幾種協同是比較好的解決方案。

參考目錄

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

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

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

如果對你有用,歡迎轉發與分享,讓更多需要的人看到這篇文章!

*文/王世員

關注得物技術,每週一三五晚18:30更新技術乾貨