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
- 下一篇: 浙江卫健委推出可陪诊数字健康人“安诊儿”