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