MySQL · 案例分析 · 大事务导致 binlog 暴增

发表于 | 更新于 | 分类于 | 阅读次数：

问题现象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[root@cosmoim-db04 data]# ll -h
total 819G
....
-rw-rw---- 1 mysql mysql  214 Nov  6 21:53 relay-log.001998
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:54 relay-log.001999
-rw-rw---- 1 mysql mysql  29M Nov  6 21:54 relay-log.002000
-rw-rw---- 1 mysql mysql  214 Nov  6 21:54 relay-log.002001
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:55 relay-log.002002
-rw-rw---- 1 mysql mysql  29M Nov  6 21:55 relay-log.002003
-rw-rw---- 1 mysql mysql  214 Nov  6 21:55 relay-log.002004
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:55 relay-log.002005
-rw-rw---- 1 mysql mysql  29M Nov  6 21:55 relay-log.002006
-rw-rw---- 1 mysql mysql  214 Nov  6 21:55 relay-log.002007
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:56 relay-log.002008
-rw-rw---- 1 mysql mysql  29M Nov  6 21:56 relay-log.002009
-rw-rw---- 1 mysql mysql  214 Nov  6 21:56 relay-log.002010
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:56 relay-log.002011
-rw-rw---- 1 mysql mysql  29M Nov  6 21:56 relay-log.002012
-rw-rw---- 1 mysql mysql  214 Nov  6 21:56 relay-log.002013
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:57 relay-log.002014
-rw-rw---- 1 mysql mysql  29M Nov  6 21:57 relay-log.002015
-rw-rw---- 1 mysql mysql  214 Nov  6 21:57 relay-log.002016
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:58 relay-log.002017
-rw-rw---- 1 mysql mysql  29M Nov  6 21:58 relay-log.002018
-rw-rw---- 1 mysql mysql  214 Nov  6 21:58 relay-log.002019
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:58 relay-log.002020
-rw-rw---- 1 mysql mysql  29M Nov  6 21:58 relay-log.002021
-rw-rw---- 1 mysql mysql  214 Nov  6 21:58 relay-log.002022
-rw-rw---- 1 mysql mysql 1.1G Nov  6 21:59 relay-log.002023
-rw-rw---- 1 mysql mysql  29M Nov  6 21:59 relay-log.002024
-rw-rw---- 1 mysql mysql  214 Nov  6 21:59 relay-log.002025
-rw-rw---- 1 mysql mysql 1.1G Nov  6 22:00 relay-log.002026
-rw-rw---- 1 mysql mysql  29M Nov  6 22:00 relay-log.002027
-rw-rw---- 1 mysql mysql  214 Nov  6 22:00 relay-log.002028
....

原因分析

解析 relaylog 日志，发现执行的是一个 update 大事务

1
mysqlbinlog --base64-output=decode-rows --verbose relay-log.001999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
#191106 21:53:01 server id 13823283  end_log_pos 342 CRC32 0x50ae56cd 	Table_map: `cosmo-aps`.`bns_aps_versiontolineinfo` mapped to number 417
# at 505
#191106 21:53:01 server id 13823283  end_log_pos 8504 CRC32 0xc3c33b83 	Update_rows: table id 417
# at 8667
#191106 21:53:01 server id 13823283  end_log_pos 16666 CRC32 0xc7ccd23c 	Update_rows: table id 417
# at 16829
#191106 21:53:01 server id 13823283  end_log_pos 24828 CRC32 0x870a1ba0 	Update_rows: table id 417
# at 24991
#191106 21:53:01 server id 13823283  end_log_pos 32990 CRC32 0x310a7ba8 	Update_rows: table id 417
# at 33153
#191106 21:53:01 server id 13823283  end_log_pos 41152 CRC32 0x84202102 	Update_rows: table id 417
# at 41315
#191106 21:53:01 server id 13823283  end_log_pos 49314 CRC32 0xaaf45c6e 	Update_rows: table id 417
# at 49477
#191106 21:53:01 server id 13823283  end_log_pos 57476 CRC32 0x3e307bcb 	Update_rows: table id 417
# at 57639
#191106 21:53:01 server id 13823283  end_log_pos 65638 CRC32 0x4832b399 	Update_rows: table id 417
# at 65801
#191106 21:53:01 server id 13823283  end_log_pos 73800 CRC32 0x941f87fe 	Update_rows: table id 417
# at 73963
#191106 21:53:01 server id 13823283  end_log_pos 81962 CRC32 0xbacbfa92 	Update_rows: table id 417
......

#191106 21:53:01 server id 13823283  end_log_pos 38061530 CRC32 0x15876ee5 	Update_rows: table id 417 flags: STMT_END_F
### UPDATE `cosmo-aps`.`bns_aps_versiontolineinfo`
### WHERE
###   @1='be2080a3-3815-48ee-b73e-2f18cbe3b4a8'
###   @2=NULL
###   @3='9790'
###   @4=''
###   @5='CE0EQ8000'
###   @6='XQG52-Q718(灰)'
###   @7=''
###   @8=NULL
###   @9='N500'
###   @10='30'
###   @11='T6'
###   @12=''
###   @13=1
###   @14=''
###   @15='在产'
###   @16=''
###   @17=''
###   @18=''
###   @19='管理员'
###   @20='2018-08-31 18:03:59'
###   @21='管理员'
###   @22='2019-11-06 21:53:00'
###   @23=0
###   @24=0
###   @25='1'
###   @26='00'
### SET
###   @1='be2080a3-3815-48ee-b73e-2f18cbe3b4a8'
###   @2=NULL
###   @3='9790'
###   @4=''
###   @5='CE0EQ8000'
###   @6='XQG52-Q718(灰)'
###   @7=''
###   @8=NULL
###   @9='N500'
###   @10='30'
###   @11='T6'
###   @12=''
###   @13=1
###   @14=''
###   @15='在产'
###   @16=''
###   @17=''
###   @18=''
###   @19='管理员'
###   @20='2018-08-31 18:03:59'
###   @21='管理员'
###   @22='2019-11-06 21:53:01'
###   @23=0
###   @24=0
###   @25='1'
###   @26='00'