现象:
- 程序大量SQL运行超时;
- 数据库中大量SQL长时间处于Updating阶段;
初步检查:
- 查看系统状态,CPU有24个核,只有一个核user利用率100%,其余核的idle基本上100%;
- 内存、IO.util都正常;
- 查看processlist,所有UPDATE语句长时间处于Updating阶段,其它类型的SQL没有Block或者运行缓慢现象;
进一步排查:
- 分析运行缓慢的SQL,发现运行缓慢的Update语句操作的都是同一张表;
- 执行缓慢的Update语句总共有两类:一类是通过主键更新一行数据;另一类是根据状态字段批量更新数据;Schema和SQL示例如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
--
Schema
CREATE
TABLE
`
general_mail
`
(
`
id
`
bigint
(
20
)
unsigned
NOT
NULL
AUTO_INCREMENT
,
`
user_id
`
bigint
(
20
)
unsigned
NOT
NULL
DEFAULT
'0'
,
`
value
`
text
NOT
NULL
,
`
status
`
tinyint
(
4
)
unsigned
NOT
NULL
DEFAULT
'0'
,
`
type
`
tinyint
(
4
)
unsigned
NOT
NULL
DEFAULT
'0'
,
.
.
.
.
.
.
PRIMARY
KEY
(
`
id
`
)
,
KEY
`
idx_status_type
`
(
`
status
`
,
`
type
`
)
,
)
ENGINE
=
InnoDB
;
--
根据主键进行更新
UPDATE
tbl_A
SET
status
=
N
,
value
=
'.....'
,
user_id
=
N
WHERE
id
=
N
;
--
根据状态进行更新
UPDATE
tbl_A
SET
status
=
N
WHERE
(
status
=
N
or
status
=
N
)
and
user_id
=
N
limit
N
;
|
基本可以推测是根据状态字段更新数据的SQL导致的问题。进一步查看INNODB STATUS,发现如下记录:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
------------------
---TRANSACTION 43DF0254C, ACTIVE 51 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 118879, OS thread handle 0x7f51950b6700, query id 18995841 10.0.0.0 mysql_user Updating UPDATE tbl_A SET status=N, value='.....', user_id=N WHERE id = N ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2186 page no 188857 n bits 688 index `idx_status_type` of table `db`.`tbl_A` trx id 43DF0254C lock_mode X locks gap before rec insert intention waiting Record lock, heap no 497 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 02; asc ;; 1: len 1; hex 01; asc ;; 2: len 8; hex 00000000011c1f0f; asc ;; ------------------ ---TRANSACTION 43DF0216B, ACTIVE 65 sec fetching rows, thread declared inside InnoDB 155 mysql tables in use 1, locked 1 794767 lock struct(s), heap size 71694776, 13900037 row lock(s) MySQL thread id 118812, OS thread handle 0x7f51951dc700, query id 18995134 10.0.0.1 mysql_user Searching rows for update UPDATE tbl_A SET status=N WHERE (status=N or status=N) and user_id=N limit N ---TRANSACTION 43DF0254C, ACTIVE 51 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 118879, OS thread handle 0x7f51950b6700, query id 18995841 10.0.0.0 mysql_user Updating UPDATE tbl_A SET status=N, value='.....', user_id=N WHERE id = N ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2186 page no 188857 n bits 688 index `idx_status_type` of table `db`.`tbl_A` trx id 43DF0254C lock_mode X locks gap before rec insert intention waiting Record lock, heap no 497 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 02; asc ;; 1: len 1; hex 01; asc ;; 2: len 8; hex 00000000011c1f0f; asc ;; ------------------ ---TRANSACTION 43DF0216B, ACTIVE 65 sec fetching rows, thread declared inside InnoDB 155 mysql tables in use 1, locked 1 794767 lock struct(s), heap size 71694776, 13900037 row lock(s) MySQL thread id 118812, OS thread handle 0x7f51951dc700, query id 18995134 10.0.0.1 mysql_user Searching rows for update UPDATE tbl_A SET status=N WHERE (status=N or status=N) and user_id=N limit N |
这下可以解释为什么只有一个CPU核在运行了。第二条SQL通过索引加扫表的方式,寻找符合条件的数据,这条SQL消耗了一个CPU核。查找数据的同时,此SQL加了gap锁,导致其它更新同一张表的SQL一直在等待锁的释放,因此其它CPU核基本处于空闲状态。
解决
找到问题的根源,解决起来就比较简单了,分析一下数据,发现索引(user_id, status)的区分度还不错,新增索引(user_id, status)后,问题解决。