前言

在業(yè)務(wù)迭代中，隨著數(shù)據(jù)量的上升，會出現(xiàn)慢SQL情況，但是當我們?nèi)シ治鰡螚lSQL的時候，發(fā)現(xiàn)其執(zhí)行速度并沒有那么慢，原因是什么呢，那么就可能是RDS服務(wù)器IO產(chǎn)生了瓶頸。

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

一、業(yè)務(wù)背景

活動 MySQL 規(guī)格：4C，最大連接數(shù) 2500，最大 IOPS 4500。

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

阿里云 RDS 中 MySQL 的 IOPS 指標

阿里云 RDS 機器的 IOPS 指標

你可能會發(fā)現(xiàn) RDS 實例最大限制不是 4500 嗎？為何這里已經(jīng)達到了 11000 以上了呢？起初我理解的是 MySQL 統(tǒng)計 IOPS，大部分操作都命中了緩沖區(qū)，限制的磁盤  IOPS。后面也咨詢了 DBA，說是 IOPS 其實沒辦法準確限制。這到底是什么情況？我們接著往后看。

這時候會統(tǒng)計出來一些查詢類的慢 SQL，我們優(yōu)先去分析這些 SQL 的執(zhí)行計劃，發(fā)現(xiàn)其走了索引，也會回表，掃描的行數(shù)比較大：

同期慢 SQL 統(tǒng)計

產(chǎn)生慢 SQL 的表，是一張業(yè)務(wù)明細表，每個用戶平均每天產(chǎn)生的數(shù)據(jù)量約 20 條，假如日活 5w 的話，每天的增量 100w，半年產(chǎn)生的數(shù)據(jù)約 2 個億，該業(yè)務(wù)已持續(xù)運營 一年以上。那么面對這樣的場景，我們該如何定位原因、如何動手優(yōu)化呢？

二、分析方法

各個業(yè)務(wù)線有很多預(yù)警、告警，很容易監(jiān)控到 RDS 運行異常問題。當我們拿到異常的時候，首先肯定是通過監(jiān)控圖表觀測技術(shù)指標，確定影響范圍，設(shè)計止血方案，然后才是定位問題，解決問題。

相對來說，IOPS 過高等告警都是短暫性的，一般發(fā)生在業(yè)務(wù)高峰期。這種情況經(jīng)常是漸變產(chǎn)生的，隨著業(yè)務(wù)增長，數(shù)據(jù)量也在增長，表結(jié)構(gòu)也越來越復(fù)雜，一些早期的 SQL 在索引選擇上發(fā)生了變化，取得目標數(shù)據(jù)掃描的行數(shù)越來越多。

1. MySQL 指標

上面的業(yè)務(wù)背景中數(shù)據(jù)庫 QPS 峰值 1 w，TPS峰值 2.5 k。下面結(jié)合這個前提來分析 MySQL 的運行指標。除了上面提到的 IOPS 指標，Buffer pool 請求次數(shù)、Redo 寫次數(shù)等數(shù)據(jù)指標，這些健康指標協(xié)同起來看，會發(fā)現(xiàn)該時段真實產(chǎn)生的讀、寫操作都比較頻繁。

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

(1)  Redo 寫次數(shù)

(2) Row Operations

(3) Buffer Pool 請求次數(shù)

(4) 慢 SQL

(5) 其他指標

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

這些指標阿里云未在健康圖表上給出，應(yīng)該是覺得目前的圖表已經(jīng)夠用了。這些指標可通過登錄 RDS 執(zhí)行  show global status like '%innodb%read%'  查看，但是這類指標一般是累計值，需要對比上一個取值時間的差值才能有比較實際的作用，通常也是用來判斷 MySQL 的讀寫比例用，結(jié)合上表的 pending 數(shù)據(jù)和其他的系統(tǒng)指標來綜合判斷 IO 系統(tǒng)的負載。

2. 機器I/O分析

一般情況，業(yè)務(wù)開發(fā)無法直接或者間接訪問 RDS 機器的，經(jīng)常由 DBA 統(tǒng)一管理。這里，我們可以了解一下 Linux下I/O 分析工具。

(1) iostat

iostat -x

關(guān)于 CPU 的指標，我們重點看  %iowait 和 %idle 兩個指標。

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

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

若%iowait 的值過高，則表示硬盤存在 I/O 瓶頸；若 %idle 值高，表示 CPU 較空閑。如果 %idle 值高但系統(tǒng)響應(yīng)慢時，有可能是 CPU 等待分配內(nèi)存，此時應(yīng)加大內(nèi)存容量。%idle 值如果持續(xù)低于 10，那么系統(tǒng)的 CPU 處理能力相對較低，表明系統(tǒng)中最需要解決的資源是 CPU。

關(guān)于 Disk 指標，我們重點看 %utils、svctm、await 和 avgque-sz幾個指標。

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

  await: 平均每次設(shè)備 I/O 操作的等待時間 (毫秒)；

  svctm: 平均每次設(shè)備 I/O 操作的服務(wù)時間 (毫秒)；

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

若 %util 接近 100%，說明產(chǎn)生的 I/O 請求太多，I/O 系統(tǒng)已經(jīng)滿負荷，該磁盤可能存在瓶頸；若 svctm 比較接近 await，說明 I/O 幾乎不需要等待；若 await 遠大于 svctm，說明 I/O 隊列太長，I/O 響應(yīng)太慢，則需要進行必要優(yōu)化；若 avgqu-sz 比較大，也表示有大量 IO 在等待。

(2) iotop

iotop -oP

通過輸出結(jié)果，我們可以清晰地了解當前哪些進程在讀寫磁盤，以及讀寫速率和 IO 使用占比。

綜上，通過 MySQL 指標及機器運行指標分析當前 MySQL 的 IO 健康狀態(tài)，以及 IO 負載過高時的慢 SQL，我們再從慢 SQL 來分析其執(zhí)行計劃，從而根據(jù)具體業(yè)務(wù)場景來制定優(yōu)化方案。

三、解決方案

當我們業(yè)務(wù)中遇到IO問題時，我們可以從以下幾個方面考慮：SQL優(yōu)化、配置優(yōu)化、存儲優(yōu)化和硬件升級優(yōu)化。

1. 硬件升級

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

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

2. 存儲優(yōu)化

我一般將存儲優(yōu)化理解成分庫分表、數(shù)據(jù)歸檔兩個方面。何時進行數(shù)據(jù)歸檔，何時進行分庫分表，也是老生常談的問題。

• 數(shù)據(jù)歸檔：一般適用于歷史數(shù)據(jù)幾乎沒有訪問場景，比如說上一個賽季的金幣記錄、半年前的領(lǐng)取的活動津貼。這些歷史數(shù)據(jù)的歸檔對于當前業(yè)務(wù)沒有任何影響，數(shù)據(jù)量又增長得比較快。歸檔后只作為算法優(yōu)化的底層數(shù)據(jù)，對業(yè)務(wù)接口的性能是非常有幫助的。

• 分庫分表：歷史數(shù)據(jù)有使用場景。比如說某個用戶的歷史訂單，或者就是用戶數(shù)據(jù)本身。這些數(shù)據(jù)不知什么時候用到，但又必須支持提供的。很長一段時間內(nèi)都是很大量級存在的業(yè)務(wù)數(shù)據(jù)，建議分庫分表。

那么做了以上兩個優(yōu)化后，對 IO 的正向影響就是減少了數(shù)據(jù)量，一些慢 SQL 掃描的行數(shù)自然下降。

3. SQL優(yōu)化

SQL 優(yōu)化又分為兩個方向，既有索引下 SQL 語句的優(yōu)化和索引調(diào)整層面的優(yōu)化。根據(jù)具體業(yè)務(wù)場景及數(shù)據(jù)調(diào)整索引策略，這個方面沒什么好說的，盡可能使得掃描的行數(shù)降低。

4. 配置優(yōu)化

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

• innodb_buffer_pool_size

我們可以通過此參數(shù)指定緩沖池的大小。如果緩沖池很小并且有足夠的內(nèi)存，那么通過減少查詢訪問InnoDB表所需的磁盤 I/O 量可以提高緩沖池的性能，從而提高性能。innodb_buffer_pool_size 選項是動態(tài)的，允許在不重新啟動服務(wù)器的情況下配置緩沖池大小。

#設(shè)置大小
set global innodb_buffer_pool_size = 26843545600

針對寫操作頻繁的場景，我們可以利用 undo/redo log 和 binlog 的寫入磁盤機制，來分析和配置這些參數(shù)：

• innodb_flush_log_at_trx_commit

此項配置用來針對 undo/redo log 的磁盤寫入配置。有3個取值:

• • 0：會每隔1秒把緩存中的 undo/redo log 寫入到磁盤；

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

  • 2：寫入系統(tǒng)的緩存，但會每隔一秒才調(diào)用文件系統(tǒng)的“flush”將緩存刷新到磁盤上去。這樣 MySQL 即使崩了，系統(tǒng)緩存還在，比 0 的方案優(yōu)。

     

如果我們可以在數(shù)據(jù)庫服務(wù)器宕機的時候，允許有 1 秒的數(shù)據(jù)丟失，其實用設(shè)置為 2 是最優(yōu)的方案，可以提高性能。

#查看當前配置
show variables like 'innodb_flush_log_at_trx_commit'; 
#設(shè)置生效
set global innodb_flush_log_at_trx_commit=2;

• sync_binlog

此項配置用來針對 binlog 的磁盤寫入配置，可以用來配置合并多少條 binlog 一次性寫入磁盤。

• • 0：代表依賴系統(tǒng)執(zhí)行合并寫入；

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

  • N（一般100-1000）：代表每N條后，合并寫入磁盤。

針對sync_binlog，同樣允許數(shù)據(jù)庫服務(wù)器宕機的情況下能接受丟失N條數(shù)據(jù)的， 可以配置為N，能提高性能。

#查看當前配置
show variables like 'sync_binlog'; 
#設(shè)置生效
set global sync_binlog=100;

四、總結(jié)

最后簡單總結(jié)一下 IO 問題分析，上面主要分析的是我們現(xiàn)在的活動業(yè)務(wù)，也就是隨機讀寫頻繁的場景，這時候 IOPS 是最為關(guān)鍵的衡量指標。另一個重要指標是數(shù)據(jù)吞吐量 (Throughput)，指單位時間內(nèi)可以成功傳輸?shù)臄?shù)據(jù)數(shù)量。對于大量順序讀寫的應(yīng)用，我們可以關(guān)注吞吐量指標。

通常我們可以通過硬件升級、SQL 優(yōu)化、表結(jié)構(gòu)優(yōu)化、分庫分表、數(shù)據(jù)歸檔等方向去做優(yōu)化策略，適當?shù)夭捎靡环N或幾種協(xié)同是比較好的解決方案。

參考目錄

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

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

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

到此這篇關(guān)于MySQL中IO問題的深入分析與優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL中IO問題分析內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！