前言
是什么原因导致线上数据库服务器磁盘I/O的util和iowait持续飚高?
1. 问题描述
朋友小明的线上数据库突发严重告警,业务方反馈写入数据一直堵住,很多锁超时回滚了,不知道怎么回事,就找到我了。
不管3721,先采集现场的必要信息再说。
a. 系统负载,主要是磁盘I/O的负载数据
该服务器的磁盘是由6块2T SSD硬盘组成的RAID-5阵列。从上面的截图来看,I/O %util已经基本跑满了,iowait也非常高,很明显磁盘I/O压力太大了。那就再查查什么原因导致的这么高压力。
b. 活跃事务列表
可以看到,有几个活跃的事务代价很高,锁定了很多行。其中有两个因为太久超时被回滚了。
再看一次活跃事务列表,发现有个事务锁定的行更多了,说明活跃业务SQL的效率不太好,需要进行优化。这个算是原因之一,先记下。
c. 查看InnoDB状态
执行 SHOW ENGINE INNODB STATUS\G
查看InnoDB状态,这里只展示了几个比较关键的地方:
...
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
### 这里很明显,发生死锁的事务之一持有很多行锁,需要优化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
...