本文主要是介绍show engine innodb status和innodb锁监控，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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://dev.mysql.com/doc/refman/8.0/en/error-log-destination-configuration.html#error-log-destination-configuration-unix

https://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/

https://www.cnblogs.com/wangdong/p/9235249.html

这篇关于show engine innodb status和innodb锁监控的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！