mysql 磁盘i o 优化_经典案例：磁盘I/O巨高排查全过程
作者：葉金榮，知數(shù)堂聯(lián)合創(chuàng)始人，3306pai社區(qū)聯(lián)合創(chuàng)始人
前言是什么原因?qū)е戮€上數(shù)據(jù)庫服務(wù)器磁盤I/O的util和iowait持續(xù)飚高？
1. 問題描述
朋友小明的線上數(shù)據(jù)庫突發(fā)嚴(yán)重告警，業(yè)務(wù)方反饋寫入數(shù)據(jù)一直堵住，很多鎖超時回滾了，不知道怎么回事，就找到我了。
不管3721，先采集現(xiàn)場的必要信息再說。
a. 系統(tǒng)負(fù)載，主要是磁盤I/O的負(fù)載數(shù)據(jù)
該服務(wù)器的磁盤是由6塊2T SSD硬盤組成的RAID-5陣列。從上面的截圖來看，I/O %util已經(jīng)基本跑滿了，iowait也非常高，很明顯磁盤I/O壓力太大了。那就再查查什么原因?qū)е碌倪@么高壓力。
b. 活躍事務(wù)列表
可以看到，有幾個活躍的事務(wù)代價很高，鎖定了很多行。其中有兩個因為太久超時被回滾了。
再看一次活躍事務(wù)列表，發(fā)現(xiàn)有個事務(wù)鎖定的行更多了，說明活躍業(yè)務(wù)SQL的效率不太好，需要進(jìn)行優(yōu)化。這個算是原因之一，先記下。
c. 查看InnoDB狀態(tài)
執(zhí)行 SHOW ENGINE INNODB STATUS\G 查看InnoDB狀態(tài)，這里只展示了幾個比較關(guān)鍵的地方：
...
0x7f8f700e9700 INNODB MONITOR OUTPUT
...
LATEST DETECTED DEADLOCK
------------------------
...
*** (2) TRANSACTION:
TRANSACTION 52970892097, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
80249 lock struct(s), heap size 9691344, 351414 row lock(s),
undo log entries 30005
### 這里很明顯，發(fā)生死鎖的事務(wù)之一持有很多行鎖，需要優(yōu)化SQL
...
update a inner join b on a.uid=b.uid set a.kid=if(b.okid=0,b.kid,b.okid),a.aid=b.aid where a.date='2020-02-10'
...
TRANSACTIONS
------------
Trx id counter 52971738624
Purge done for trx's n:o < 52971738461 undo n:o < 0
state: running but idle
History list length 81
...
---TRANSACTION 52971738602, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s),
undo log entries 348
### 同樣滴，也是有很多行鎖
...
LOG
---
Log sequence number 565123876918590
Log flushed up to 565123858946703
Pages flushed up to 565121518602442
Last checkpoint at 565121518602442
...
### 注意到Last checkpoint和LSN之間的差距非常大，約為2249MB
### 說明redo log的checkpoint有延遲比較厲害，有可能是因為磁盤I/O太慢，
### 也有可能是因為產(chǎn)生的臟頁太多太快，來不及刷新
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 201200762880
Dictionary memory allocated 130361859
Internal hash tables (constant factor + variable factor) Adaptive hash index 3930999872 (3059599552 + 871400320)
Page hash 23903912 (buffer pool 0 only)
Dictionary cache 895261747 (764899888 + 130361859)
File system 16261960 (812272 + 15449688)
Lock system 478143288 (478120568 + 22720)
Recovery system 0 (0 + 0)
Buffer pool size 11795040
Buffer pool size, bytes 193249935360
Free buffers 7035886
Database pages 4705977O
ld database pages 1737005
Modified db pages 238613
### 臟頁比例約為2%，看著還好嘛，而且還有挺多free page的
...
d. 查看MySQL的線程狀態(tài)*
+---------+------+--------------+---------------------
| Command | Time | State | Info |
+---------+------+--------------+---------------------
| Query | 1 | update | insert xxx
| Query | 0 | updating | update xxx
| Query | 0 | updating | update xxx
| Query | 0 | updating | update xxx
| Query | 0 | updating | update xxx
+---------+------+--------------+---------------------
可以看到幾個事務(wù)都處于updating狀態(tài)。意思是正在掃描數(shù)據(jù)并準(zhǔn)備更新，肉眼可見這些事務(wù)狀態(tài)時，一般是因為系統(tǒng)負(fù)載比較高，所以事務(wù)執(zhí)行起來慢；或者該事務(wù)正等待行鎖釋放。
2. 問題分析及優(yōu)化工作
分析上面的各種現(xiàn)場信息，我們可以得到以下幾點結(jié)論：
a. 磁盤I/O壓力很大。先把陣列卡的cache策略改成WB，不過由于已經(jīng)是SSD盤，這個作用并不大，只能申請更換成RAID-10陣列的新機(jī)器了，還需等待資源調(diào)配。
b. 需要優(yōu)化活躍SQL，降低加鎖代價
[root@yejr.me]> desc select * from a inner join b on a.uid=b.uid where a.date='2020-02-10';
+-------+--------+------+---------+----------+-------+----------+-----------------------+
| table | type | key | key_len | ref | rows | filtered | Extra |
+-------+--------+------+---------+----------+-------+----------+-----------------------+
| a | ref | date | 3 | const | 95890 | 100.00 | NULL |
| b | eq_ref | uid | 4 | db.a.uid | 1 | 100.00 | Using index condition |
+-------+--------+------+---------+----------+-------+----------+-----------------------+
[root@yejr.me]> select count(*) from a inner join b on a.uid=b.uid where a.date='2020-02-10';
+----------+
| count(*) |
+----------+
| 40435 |
+----------+
1 row in set (0.22 sec)
執(zhí)行計劃看起來雖然能用到索引，但效率還是不高。檢查了下，發(fā)現(xiàn)a表的uid列竟然沒加索引，我汗。。。
c. InnoDB的redo log checkpoint延遲比較大，有2249MB之巨。先檢查redo log的設(shè)置：
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
這個問題就大了，redo log明顯太小，等待被checkpoint的redo都超過2G了，那肯定要瘋狂刷臟頁，所以磁盤I/O的寫入才那么高，I/O %util和iowait也很高。
建議把redo log size調(diào)整成4G、3組。
innodb_log_file_size = 4Ginnodb_log_files_in_group = 2
此外，也順便檢查了InnoDB其他幾個重要選項
innodb_thread_concurrency = 0
# 建議維持設(shè)置0不變
innodb_max_dirty_pages_pct = 50
# 由于這個實例每秒寫入量較大，建議先調(diào)整到75，降低刷臟頁的頻率，
# 順便緩解redo log checkpoint的壓力。
# 在本案例，最后我們把這個值調(diào)整到了90。
特別提醒
從MySQL 5.6版本起，修改redo log設(shè)置后，實例重啟時會自動完成redo log的再次初始化，不過前提是要先干凈關(guān)閉實例。因此建議在第一次關(guān)閉時，修改以下兩個選項：
innodb_max_dirty_pages_pct = 0
innodb_fast_shutdown = 0
并且，再加上一個新選項，防止實例啟動后，會有外部應(yīng)用連接進(jìn)來繼續(xù)寫數(shù)據(jù)：
skip-networking
在確保所有臟頁(上面看到的Modified db pages為0)都刷盤完畢后，并且redo log也都checkpoint完畢(上面看到的Log sequence number和Last checkpoint at**值相等)，此時才能放心的修改 innodb_log_file_size 選項配置并重啟。確認(rèn)生效后再關(guān)閉 skip-networking 選項對業(yè)務(wù)提供服務(wù)。
經(jīng)過一番優(yōu)化調(diào)整后，再來看下服務(wù)器和數(shù)據(jù)庫的負(fù)載。
可以看到，服務(wù)器的磁盤I/O壓力再也不會那么大了，數(shù)據(jù)庫中也不會頻繁出現(xiàn)大量行鎖等待或回滾的事務(wù)了。
[root@yejr.me]> SHOW ENGINE INNODB STATUS\g
Log sequence number 565749866400449
Log flushed up to 565749866400449
Pages flushed up to 565749866318224
Last checkpoint at 565749866315740
[root@yejr.me]> SHOW ENGINE INNODB STATUS\g
Log sequence number 565749866414660
Log flushed up to 565749866400449
Pages flushed up to 565749866320827
Last checkpoint at 565749866315740
[root@yejr.me]> SHOW ENGINE INNODB STATUS\g
Log sequence number 565749866414660
Log flushed up to 565749866414660
Pages flushed up to 565749866322135
Last checkpoint at 565749866315740
[root@yejr.me]> select (565749866400449-565749866315740)/1024;
+----------------------------------------+
| (565749866400449-565749866315740)/1024 |
+----------------------------------------+
| 82.7236 |
+----------------------------------------+
1 row in set (0.00 sec)
[root@yejr.me]> select (565749866414660-565749866315740)/1024;
+----------------------------------------+
| (565749866414660-565749866315740)/1024 |
+----------------------------------------+
| 96.6016 |
+----------------------------------------+
很明顯，redo log checkpoint lag幾乎沒有了。
完美搞定！
寫在最后
遇到數(shù)據(jù)庫性能瓶頸，負(fù)載飚高這類問題，我們只需要根據(jù)一套完整的方法論
延伸閱讀
總結(jié)
以上是生活随笔為你收集整理的mysql 磁盘i o 优化_经典案例：磁盘I/O巨高排查全过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 华硕发布 ProArt 系列 RTX 4
- 下一篇： 浙江卫健委推出可陪诊数字健康人“安诊儿”