前言

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

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

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

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

早上 10 點(diǎn)，是活動(dòng)業(yè)務(wù) QPS 最高的時(shí)候，因?yàn)檫@時(shí)候通常會(huì)釋放獎(jiǎng)品庫(kù)存。有段時(shí)間，監(jiān)控爆出了慢 SQL 的問(wèn)題，但是通過(guò)監(jiān)控指標(biāo)觀測(cè) QPS 的時(shí)候，并沒(méi)有到達(dá)預(yù)想中的峰值，但是讀寫(xiě)RT會(huì)出現(xiàn)一些突刺。再進(jìn)而查看 IOPS 指標(biāo)，我們發(fā)現(xiàn)異常得高，如下圖：

阿里云 RDS 中 MySQL 的 IOPS 指標(biāo)

阿里云 RDS 機(jī)器的 IOPS 指標(biāo)

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

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

同期慢 SQL 統(tǒng)計(jì)

產(chǎn)生慢 SQL 的表，是一張業(yè)務(wù)明細(xì)表，每個(gè)用戶平均每天產(chǎn)生的數(shù)據(jù)量約 20 條，假如日活 5w 的話，每天的增量 100w，半年產(chǎn)生的數(shù)據(jù)約 2 個(gè)億，該業(yè)務(wù)已持續(xù)運(yùn)營(yíng) 一年以上。那么面對(duì)這樣的場(chǎng)景，我們?cè)撊绾味ㄎ辉颉⑷绾蝿?dòng)手優(yōu)化呢？

二、分析方法

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

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

1. MySQL 指標(biāo)

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

其中 innodb_rows_read 已經(jīng)達(dá)到 22w 以上，innodb_rows_updated 達(dá)到 1w 以上，相對(duì)來(lái)說(shuō)讀操作被放大了 22 倍，寫(xiě)操作被放大了 4 倍。

(1)  Redo 寫(xiě)次數(shù)

(2) Row Operations

(3) Buffer Pool 請(qǐng)求次數(shù)

(4) 慢 SQL

(5) 其他指標(biāo)

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

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

2. 機(jī)器I/O分析

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

(1) iostat

iostat -x

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

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

  %idle：CPU 空閑時(shí)間百分比。

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

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

• avgqu-sz: 平均 I/O 隊(duì)列長(zhǎng)度；

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

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

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

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

(2) iotop

iotop -oP

通過(guò)輸出結(jié)果，我們可以清晰地了解當(dāng)前哪些進(jìn)程在讀寫(xiě)磁盤(pán)，以及讀寫(xiě)速率和 IO 使用占比。

綜上，通過(guò) MySQL 指標(biāo)及機(jī)器運(yùn)行指標(biāo)分析當(dāng)前 MySQL 的 IO 健康狀態(tài)，以及 IO 負(fù)載過(guò)高時(shí)的慢 SQL，我們?cè)購(gòu)穆?SQL 來(lái)分析其執(zhí)行計(jì)劃，從而根據(jù)具體業(yè)務(wù)場(chǎng)景來(lái)制定優(yōu)化方案。

三、解決方案

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

1. 硬件升級(jí)

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

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

2. 存儲(chǔ)優(yōu)化

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

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

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

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

3. SQL優(yōu)化

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

4. 配置優(yōu)化

針對(duì)讀操作場(chǎng)景，我們可以使用 innodb_buffer_pool_size 來(lái)減少 I/O 負(fù)載。

• innodb_buffer_pool_size

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

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

針對(duì)寫(xiě)操作頻繁的場(chǎng)景，我們可以利用 undo/redo log 和 binlog 的寫(xiě)入磁盤(pán)機(jī)制，來(lái)分析和配置這些參數(shù)：

• innodb_flush_log_at_trx_commit

此項(xiàng)配置用來(lái)針對(duì) undo/redo log 的磁盤(pán)寫(xiě)入配置。有3個(gè)取值:

• • 0：會(huì)每隔1秒把緩存中的 undo/redo log 寫(xiě)入到磁盤(pán)；

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

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

     

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

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

• sync_binlog

此項(xiàng)配置用來(lái)針對(duì) binlog 的磁盤(pán)寫(xiě)入配置，可以用來(lái)配置合并多少條 binlog 一次性寫(xiě)入磁盤(pán)。

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

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

  • N（一般100-1000）：代表每N條后，合并寫(xiě)入磁盤(pán)。

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

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

四、總結(jié)

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

通常我們可以通過(guò)硬件升級(jí)、SQL 優(yōu)化、表結(jié)構(gòu)優(yōu)化、分庫(kù)分表、數(shù)據(jù)歸檔等方向去做優(yōu)化策略，適當(dāng)?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問(wèn)題的深入分析與優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL中IO問(wèn)題分析內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論