前言

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

日常，我們可以通過 IOPS(Input/Output Per Second) 指標(biāo)來衡量 IO 是否處于健康的范圍。我們使用的阿里云 RDS 通常根據(jù)不同的規(guī)格做了不同的 IOPS 限制。如果短時(shí)間內(nèi)頻繁的操作，不管是 SELECT 帶來的讀磁盤操作，還是 INSERT、UPDATE、DELETE 帶來的寫磁盤操作，均可能會(huì)觸發(fā)最大 IOPS 限制。本文將從實(shí)際業(yè)務(wù)分析，探討根據(jù) IOPS、Redo 寫次數(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)品庫存。有段時(shí)間，監(jiān)控爆出了慢 SQL 的問題，但是通過監(jiān)控指標(biāo)觀測(cè) QPS 的時(shí)候，并沒有到達(dá)預(yù)想中的峰值，但是讀寫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ū)，限制的磁盤  IOPS。后面也咨詢了 DBA，說是 IOPS 其實(shí)沒辦法準(zhǔn)確限制。這到底是什么情況？我們接著往后看。

這時(shí)候會(huì)統(tǒng)計(jì)出來一些查詢類的慢 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)行異常問題。當(dāng)我們拿到異常的時(shí)候，首先肯定是通過監(jiān)控圖表觀測(cè)技術(shù)指標(biāo)，確定影響范圍，設(shè)計(jì)止血方案，然后才是定位問題，解決問題。

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

1. MySQL 指標(biāo)

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

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

(1)  Redo 寫次數(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)該是覺得目前的圖表已經(jīng)夠用了。這些指標(biāo)可通過登錄 RDS 執(zhí)行  show global status like '%innodb%read%'  查看，但是這類指標(biāo)一般是累計(jì)值，需要對(duì)比上一個(gè)取值時(shí)間的差值才能有比較實(shí)際的作用，通常也是用來判斷 MySQL 的讀寫比例用，結(jié)合上表的 pending 數(shù)據(jù)和其他的系統(tǒng)指標(biāo)來綜合判斷 IO 系統(tǒng)的負(fù)載。

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

一般情況，業(yè)務(wù)開發(fā)無法直接或者間接訪問 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 的值過高，則表示硬盤存在 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%，說明產(chǎn)生的 I/O 請(qǐng)求太多，I/O 系統(tǒng)已經(jīng)滿負(fù)荷，該磁盤可能存在瓶頸；若 svctm 比較接近 await，說明 I/O 幾乎不需要等待；若 await 遠(yuǎn)大于 svctm，說明 I/O 隊(duì)列太長(zhǎng)，I/O 響應(yīng)太慢，則需要進(jìn)行必要優(yōu)化；若 avgqu-sz 比較大，也表示有大量 IO 在等待。

(2) iotop

iotop -oP

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

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

三、解決方案

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

1. 硬件升級(jí)

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

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

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

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

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

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

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

3. SQL優(yōu)化

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

4. 配置優(yōu)化

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

• innodb_buffer_pool_size

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

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

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

• innodb_flush_log_at_trx_commit

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

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

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

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

     

如果我們可以在數(shù)據(jù)庫服務(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)配置用來針對(duì) binlog 的磁盤寫入配置，可以用來配置合并多少條 binlog 一次性寫入磁盤。

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

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

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

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

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

四、總結(jié)

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

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

最新評(píng)論