mysql InnoDB Monitors 4种监控

监控monitors

• The standard InnoDB Monitor 标准监控
• The InnoDB Lock Monitor 锁监控，用于排查事务间锁问题
• The InnoDB Tablespace Monitor 表空间监控
• The InnoDB Table Monitor 表监控

触发条件

• To enable the standard InnoDB Monitor for periodic output, create a table named innodb_monitor .
• The InnoDB Lock Monitor prints additional lock information as part of the standard InnoDB Monitor output. To enable the InnoDB Lock Monitor, create a table named innodb_lock_monitor.
• The InnoDB Tablespace Monitor prints a list of file segments in the shared tablespace and validates the tablespace allocation data structures. To enable this Monitor for periodic output, create a table named innodb_tablespace_monitor .
• The InnoDB Table Monitor prints the contents of the InnoDB internal data dictionary. To enable this Monitor for periodic output, create a table named innodb_table_monitor .

To enable an InnoDB Monitor for periodic output, use a CREATE TABLE statement to create the table associated with the Monitor. For example, to enable the standard InnoDB Monitor, create the innodb_monitor table:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

To stop the Monitor, drop the table:

DROP TABLE innodb_monitor;

打开锁监控后，可以使用show engine innodb status来触发打印监控输出；

举例

三个事务，分别是t1,t2,t3

Transaction 1 Transaction 2 Transaction set autocommit=0; set autocommit=0; set autocommit=0; set session tx_isolation=’repeatable-read’; set session tx_isolation=’repeatable-read’; set session tx_isolation=’repeatable-read’; select * from tran_t1 where a = 59 for update; update tran_t1 set b= 20 where a = 59; waiting… delete tran_t1 where a = 59; waiting…

使用：

show engine innodb status

结果：

------------TRANSACTIONS------------//Trx id counter 1012022//Purge done for trx's n:o < 1012013 undo n:o < 0 state: //running but idle//History list length 188//Total number of lock structs in row lock hash table 3LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 1012021, ACTIVE 6 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)MySQL thread id 5, OS thread handle 0x129f43000, query id 180 localhost root updatingdelete from tran_t1 where a = 59 //##可以看出来这个是事务3------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`tran_t1` trx id 1012021 lock_mode X locks rec but not gap waiting ##在等待记录a=59上面的x record锁Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000003b; asc    ;;; 1: len 6; hex 0000000f6f3f; asc     o?;; 2: len 7; hex b3000001640110; asc     d  ;; 3: len 4; hex 80000001; asc     ;;------------------TABLE LOCK table `test`.`tran_t1` trx id 1012021 lock mode IX //##在给记录加X锁前，需要给表加上意向锁IXRECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`tran_t1` trx id 1012021 lock_mode X locks rec but not gap waitingRecord lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000003b; asc    ;;; 1: len 6; hex 0000000f6f3f; asc     o?;; 2: len 7; hex b3000001640110; asc     d  ;; 3: len 4; hex 80000001; asc     ;;---TRANSACTION 1012020, ACTIVE 14 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)MySQL thread id 4, OS thread handle 0x129e37000, query id 179 localhost root updatingupdate tran_t1 set b = 2 where a = 59 //##可以看出来是事务2------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`tran_t1` trx id 1012020 lock_mode X locks rec but not gap waiting //##等待记录a=59的record排他锁Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000003b; asc    ;;; 1: len 6; hex 0000000f6f3f; asc     o?;; 2: len 7; hex b3000001640110; asc     d  ;; 3: len 4; hex 80000001; asc     ;;------------------TABLE LOCK table `test`.`tran_t1` trx id 1012020 lock mode IX //##在加记录x锁前，先给表加上意向锁IXRECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`tran_t1` trx id 1012020 lock_mode X locks rec but not gap waitingRecord lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000003b; asc    ;;; 1: len 6; hex 0000000f6f3f; asc     o?;; 2: len 7; hex b3000001640110; asc     d  ;; 3: len 4; hex 80000001; asc     ;;---TRANSACTION 1012019, ACTIVE 28 sec2 lock struct(s), heap size 376, 1 row lock(s)MySQL thread id 3, OS thread handle 0x129df3000, query id 181 localhost root initshow engine innodb status //##当前执行的语句，可以看出来是事务1TABLE LOCK table `test`.`tran_t1` trx id 1012019 lock mode IX //##加记录锁前必须给表加上意向锁RECORD LOCKS space id 31 page no 3 n bits 80 index `PRIMARY` of table `test`.`tran_t1` trx id 1012019 lock_mode X locks rec but not gap //##拥有记录x锁Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8000003b; asc    ;;; 1: len 6; hex 0000000f6f3f; asc     o?;; 2: len 7; hex b3000001640110; asc     d  ;; 3: len 4; hex 80000001; asc     ;;--------

理解其中的中文说明。