innodb_status_file innodb_status_output innodb_status_output_locks和innodb_show_verbose_locks

阅读量

innodb_status_file

这个参数官方文档https://dev.mysql.com/doc/refman/5.7/en/server-system-variable-reference.html 中没有

在https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html 中有

--innodb-status-file

Property Value
Command-Line Format `–innodb-status-file[={OFF ON}]`
Type Boolean
Default Value OFF

The --innodb-status-file startup option controls whether InnoDB creates a file named innodb_status.*pid* in the data directory and writes SHOW ENGINE INNODB STATUS output to it every 15 seconds, approximately.

The innodb_status.*pid* file is not created by default. To create it, start mysqld with the --innodb-status-file option. InnoDB removes the file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.

The --innodb-status-file option is intended for temporary use, as SHOW ENGINE INNODB STATUS output generation can affect performance, and the innodb_status.*pid* file can become quite large over time.

For related information, see Section 14.18.2, “Enabling InnoDB Monitors”.

Section 14.18.2, “Enabling InnoDB Monitors”.

Directing Standard InnoDB Monitor Output to a Status File

Standard InnoDB Monitor output can be enabled and directed to a status file by specifying the --innodb-status-file option at startup. When this option is used, InnoDB creates a file named innodb_status.*pid* in the data directory and writes output to it every 15 seconds, approximately.

InnoDB removes the status file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.

The --innodb-status-file option is intended for temporary use, as output generation can affect performance, and the innodb_status.*pid* file can become quite large over time.

非动态参数, 可以再my.cnf中添加innodb_status_file=1启用

开启后会在datadir下生成一个innodb_status.pid文件, 周期性15秒向这个文件输出show engine innodb status. 如果异常关闭数据库, 这个文件不会被删除

看文档意思theinnodb_status.pidfile can become quite large over time., 但我看不会表达, 他不是追加写入, 起码percona分支是这样, 上面的图是官方分支, 也没有追加

innodb_status_output

开启后会周期性向error log输出show engine innodb status

innodb_status_output_locks

单独开这个影响show engine innodb status. 这个参数参数配合innodb_status_file或innodb_status_output使用时, 当前两者开启, 则会向前两者输出位置输出锁信息

innodb_status_output_locks=off

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------------
TRANSACTIONS
------------
Trx id counter 5781
Purge done for trx's n:o < 5776 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421976761960688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421976761961816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5780, ACTIVE 36 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140502089910016, query id 257 localhost root

innodb_status_output_locks=on, innodb_show_verbose_locks=0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
------------
TRANSACTIONS
------------
Trx id counter 5781
Purge done for trx's n:o < 5776 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421976761960688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421976761961816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5780, ACTIVE 185 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140502089910016, query id 257 localhost root
TABLE LOCK table `fanboshi`.`t8` trx id 5780 lock mode IX
RECORD LOCKS space id 133 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5780 lock_mode X locks rec but not gap
RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `fanboshi`.`t8` trx id 5780 lock_mode X locks rec but not gap

innodb_status_output_locks=on, innodb_show_verbose_locks=1

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
------------
TRANSACTIONS
------------
Trx id counter 5781
Purge done for trx's n:o < 5776 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421976761960688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421976761961816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5780, ACTIVE 48 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140502089910016, query id 257 localhost root
TABLE LOCK table `fanboshi`.`t8` trx id 5780 lock mode IX
RECORD LOCKS space id 133 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5780 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 1; hex 80; asc  ;;
 3: len 8; hex 8000000000000001; asc         ;;

RECORD LOCKS space id 133 page no 3 n bits 80 index PRIMARY of table `fanboshi`.`t8` trx id 5780 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000001647; asc      G;;
 2: len 7; hex b6000000040110; asc        ;;
 3: len 1; hex 31; asc 1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 80; asc  ;;
 6: len 1; hex 61; asc a;;

innodb_show_verbose_locks

percona分支参数

  • variable innodb_show_verbose_locks

    Command Line:YesConfig File:YesScope:GlobalDynamic:YesVariable Type:ULONGDefault Value:0Range:0 - 1

Specifies to show records locked in SHOW ENGINE INNODB STATUS. The default is 0, which means only the higher-level information about the lock (which table and index is locked, etc.) is printed. If set to 1, then traditional InnoDB behavior is enabled: the records that are locked are dumped to the output.

官方分支没这个参数, 这个参数影响innodb_print_all_deadlocks和前面三个参数.

例如innodb_show_verbose_locks=0, innodb_print_all_deadlocks=1, 则error log中死锁信息为

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
2020-05-15T10:24:29.703878+08:00 4 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2020-05-15T10:24:29.703949+08:00 4 [Note] InnoDB: 
*** (1) TRANSACTION:

TRANSACTION 5127, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140073055278848, query id 26 localhost root statistics
select u_c from t8 where d_id='1' and b_id='1' and is_dropped=0 for update
2020-05-15T10:24:29.703987+08:00 4 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5127 lock_mode X locks rec but not gap waiting
2020-05-15T10:24:29.704003+08:00 4 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 5126, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 4, OS thread handle 140073055008512, query id 27 localhost root updating
update t8 set u_c='b' where d_id='1' and b_id='1'
2020-05-15T10:24:29.704016+08:00 4 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5126 lock_mode X locks rec but not gap
2020-05-15T10:24:29.704025+08:00 4 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5126 lock_mode X waiting
2020-05-15T10:24:29.704034+08:00 4 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

innodb_show_verbose_locks=1, innodb_print_all_deadlocks=1, 则error log中死锁信息为

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
2020-05-15T10:54:23.575868+08:00 4 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2020-05-15T10:54:23.575923+08:00 4 [Note] InnoDB: 
*** (1) TRANSACTION:

TRANSACTION 5130, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140073055278848, query id 38 localhost root statistics
select u_c from t8 where d_id='1' and b_id='1' and is_dropped=0 for update
2020-05-15T10:54:23.575945+08:00 4 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5130 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 1; hex 80; asc  ;;
 3: len 8; hex 8000000000000001; asc         ;;

2020-05-15T10:54:23.576779+08:00 4 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 5129, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 4, OS thread handle 140073055008512, query id 39 localhost root updating
update t8 set u_c='b' where d_id='1' and b_id='1'
2020-05-15T10:54:23.576798+08:00 4 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5129 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 1; hex 80; asc  ;;
 3: len 8; hex 8000000000000001; asc         ;;

2020-05-15T10:54:23.576847+08:00 4 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5129 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 1; hex 80; asc  ;;
 3: len 8; hex 8000000000000001; asc         ;;

2020-05-15T10:54:23.576896+08:00 4 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

innodb_status_output=1,innodb_show_verbose_locks=0或1, innodb_status_output_locks=off

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------------
TRANSACTIONS
------------
Trx id counter 5134
Purge done for trx's n:o < 5132 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421547726605656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421547726604528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5132, ACTIVE 93 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6, OS thread handle 140073054738176, query id 60 localhost root

innodb_status_output=1,innodb_show_verbose_locks=0, innodb_status_output_locks=on

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
------------
TRANSACTIONS
------------
Trx id counter 5134
Purge done for trx's n:o < 5132 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421547726605656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421547726604528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5132, ACTIVE 287 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6, OS thread handle 140073054738176, query id 60 localhost root
TABLE LOCK table `fanboshi`.`t8` trx id 5132 lock mode IX
RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5132 lock_mode X locks rec but not gap
RECORD LOCKS space id 128 page no 3 n bits 80 index PRIMARY of table `fanboshi`.`t8` trx id 5132 lock_mode X locks rec but not gap

innodb_status_output=1,innodb_show_verbose_locks=1, innodb_status_output_locks=on

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
------------
TRANSACTIONS
------------
Trx id counter 5134
Purge done for trx's n:o < 5132 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421547726605656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421547726604528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5132, ACTIVE 132 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6, OS thread handle 140073054738176, query id 60 localhost root
TABLE LOCK table `fanboshi`.`t8` trx id 5132 lock mode IX
RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5132 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 1; hex 80; asc  ;;
 3: len 8; hex 8000000000000001; asc         ;;

RECORD LOCKS space id 128 page no 3 n bits 80 index PRIMARY of table `fanboshi`.`t8` trx id 5132 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000000103f; asc      ?;;
 2: len 7; hex b0000000040110; asc        ;;
 3: len 1; hex 31; asc 1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 80; asc  ;;
 6: len 1; hex 61; asc a;;

对于status_file一样

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
status_file v0 v1 off
------------
TRANSACTIONS
------------
Trx id counter 5640
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421976761961816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5639, ACTIVE 301 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140502089639680, query id 23 localhost root

status_file v0 on
------------
TRANSACTIONS
------------
Trx id counter 5640
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421976761961816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5639, ACTIVE 901 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140502089639680, query id 23 localhost root
TABLE LOCK table `fanboshi`.`t8` trx id 5639 lock mode IX
RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5639 lock_mode X locks rec but not gap
RECORD LOCKS space id 128 page no 3 n bits 80 index PRIMARY of table `fanboshi`.`t8` trx id 5639 lock_mode X locks rec but not gap

status_file  v1 on
------------
TRANSACTIONS
------------
Trx id counter 5640
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421976761961816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5639, ACTIVE 561 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3, OS thread handle 140502089639680, query id 23 localhost root
TABLE LOCK table `fanboshi`.`t8` trx id 5639 lock mode IX
RECORD LOCKS space id 128 page no 4 n bits 80 index DealerAndBrokerAndDropped of table `fanboshi`.`t8` trx id 5639 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 1; hex 31; asc 1;;
 2: len 1; hex 80; asc  ;;
 3: len 8; hex 8000000000000001; asc         ;;

RECORD LOCKS space id 128 page no 3 n bits 80 index PRIMARY of table `fanboshi`.`t8` trx id 5639 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 00000000103f; asc      ?;;
 2: len 7; hex b0000000040110; asc        ;;
 3: len 1; hex 31; asc 1;;
 4: len 1; hex 31; asc 1;;
 5: len 1; hex 80; asc  ;;
 6: len 1; hex 61; asc a;;

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :