innodb monitor概述
innodb monitor是innodb内置的监控模块，用show engine innodb status就可以查看。
innodb monitor output可以输出在错误日志中
查看更细粒度的innodb锁信息，需要打开innodb_status_output_locks
innodb monitor的类型
innodb监控类型（innodb monitor）分2种：innodb标准监控和innodb锁监控
innodb标准监控-Standard InnoDB Monitor
innodb标准监控实际上就是show engine innodb status的默认输出。
要注意，innodb标准监控输出是有锁信息的，innodb锁监控只是在标准输出上输入了更多锁信息
mysql> show engine innodb status\G
=====================================
2021-05-10 09:04:13 0x7f8821084700 INNODB MONITOR OUTPUT --标准输出抬头，2021-05-10 09:04:13监控时间
=====================================
Per second averages calculated from the last 33 seconds --监控数据是近xx秒内的平均值
-----------------
BACKGROUND THREAD --srv_master_thread后台线程信息
-----------------
srv_master_thread loops: 66 srv_active, 0 srv_shutdown, 201928 srv_idle
srv_master_thread log flush and writes: 201964
----------
SEMAPHORES --等待mutex、lock的信号信息，线程spin信息
----------
OS WAIT ARRAY INFO: reservation count 200
OS WAIT ARRAY INFO: signal count 184
RW-shared spins 0, rounds 170, OS waits 88
RW-excl spins 0, rounds 91, OS waits 4
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 170.00 RW-shared, 91.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS --事务信息，包含锁的信息。锁信息默认是输出的
------------
Trx id counter 229376
Purge done for trx's n:o < 229374 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421697798973264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O --IO线程,aio等信息
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
910 OS file reads, 1607 OS file writes, 755 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX --change buffer和自适应索引
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 3 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG --innodb log信息
---
Log sequence number 67886692
Log flushed up to 67886692
Pages flushed up to 67886692
Last checkpoint at 67886683
0 pending log flushes, 0 pending chkp writes
312 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY --buffer pool和内存池
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 322053
Buffer pool size 8191
Free buffers 7581
Database pages 608
Old database pages 206
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 43, not young 156
0.00 youngs/s, 0.00 non-youngs/s
Pages read 872, created 413, written 955
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 608, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS --数据行操作统计信息
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=2134, Main thread ID=140222417331968, state: sleeping
Number of rows inserted 17905, updated 1, deleted 0, read 18652
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT --innodb monitor结尾
============================
innodb锁监控-InnoDB Lock Monitor
InnoDB Lock Monitor会在标准监控输出中输出更多的锁信息
开启InnoDB Lock Monitor
SET GLOBAL innodb_status_output_locks=ON;
innodb_status_output_locks是动态参数，默认是关闭的，如果想看更细粒度的锁信息，需要将此参数打开。
（注意innodb_status_output_locks=off时，show engine innodb status中仍然有锁信息，只是没有那么详细）
InnoDB Lock Monitor监控信息
对比标准监控和开启锁监控后的output，锁监控transaction这部分的锁信息明显更多
该测试中仅有2个会话争用行锁
innodb monitor输出持久化
输出到错误日志
innodb_status_output参数控制show engine innodb status是否输出到错误日志
该参数为动态参数，默认关闭。
SET GLOBAL innodb_status_output=ON;开启该功能后，innodb会每15秒输出一次mysqld的stderr到log_error中，实际上就是每15s做一次show engine innodb status写入log_error
如果想将额外锁监控也打印出来，需要将innodb_status_output_locks打开
注意：
1.innodb_status_output是控制show engine innodb status的结果打印到错误日志中
2.innodb_status_output_locks是将额外锁信息输出到show engine innodb status中
输出到innodb-status-file
innodb-status-file是mysqld的启动参数，--innodb-status-file控制每15s将show engine innodb status信息打印到innodb_status.pid文件
当mysql服务正常关闭时，innodb_status.pid文件会被删除，如果是非正常关闭，可能需要手动删除
show engine innodb status可以输出到错误日志或者innodb_status.pid文件，无论哪种方式都会消耗少量的性能，开启这些功能需要关注空间的使用。
其实写个脚本定时跑show engine innodb status也是差不多的效果
innodb monitor版本说明
在mysql5.7及以后是innodb monitor相关配置是不需要建表的
在mysql 5.6，开启innodb_status_output或者innodb_status_output_locks需要create table，
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;5.6还有Table Monitor和Tablespace Monitor，5.7也已经没有了
https://dev.mysql.com/doc/refman/5.7/en/innodb-enabling-monitors.html
https://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/
https://www.cnblogs.com/wangdong/p/9235249.html