MySQL5.7 InnoDB Standard Monitor and Lock Monitor Output
程序员文章站
2022-05-08 09:25:53
...
导读：以下输出信息为MySQL5.7 InnoDB Standard Monitor and Lock Monitor Output，只是一点个人简单理解。如有不对之处，欢迎拍砖
官方介绍：
The Lock Monitor is the same as the Standard Monitor except that it includes additional lock information. Enabling either monitor for periodic output turns on the same output stream, but the stream includes extra information if the Lock Monitor is enabled. For example, if you enable the Standard Monitor and Lock Monitor, that turns on a single output stream. The stream includes extra lock information until you disable the Lock Monitor.
Standard Monitor output is limited to 1MB when produced using the SHOW ENGINE INNODB STATUS statement. This limit does not apply to output written to the server's error output.
Example Standard Monitor output:
aaa@qq.com [(none)]>show engine innodb status\G
*************************** 1. row ***************************
【第一部分：声明输出信息】
Type: InnoDB
Name:
Status:
=====================================
##显示的是当前时间
2016-12-26 10:41:12 0x7fde953c9700 INNODB MONITOR OUTPUT
=====================================
##距上次复位时常
Per second averages calculated from the last 1 seconds
-----------------
【第二部分：从innodb1.0.x开始，可以查看master thread的状态信息】
##backgroup 线程
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12032 srv_active, 0 srv_shutdown, 10080118 srv_idle
srv_master_thread log flush and writes: 10092150
----------
【第三部分：如果有性能上的瓶颈，可以使用这些信息来找出瓶颈】
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 604456
OS WAIT ARRAY INFO: signal count 553707
RW-shared spins 0, rounds 1054096, OS waits 501522
RW-excl spins 0, rounds 1391796, OS waits 36382
RW-sx spins 13501, rounds 242119, OS waits 2963
Spin rounds per wait: 1054096.00 RW-shared, 1391796.00 RW-excl, 17.93 RW-sx
------------------------
【第四部分：下面这种错误，一般不会发生，除非服务器上有外键错误。以下信息的输出对于查找外键错误非常有帮助】
LATEST FOREIGN KEY ERROR
------------------------
2014-10-16 18:35:18 0x7fc2a95c1700 Transaction:
TRANSACTION 1814, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 2, OS thread handle 140474041767680, query id 74 localhost
root update
INSERT INTO child VALUES
(NULL, 1)
, (NULL, 2)
, (NULL, 3)
, (NULL, 4)
, (NULL, 5)
, (NULL, 6)
Foreign key constraint fails for table `mysql`.`child`:
##下面部分显示发生错误时，innodb正尝试修改的准确数据
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent`
(`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000003; asc ;;
But in parent table `mysql`.`parent`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 00000000070a; asc ;;
2: len 7; hex aa0000011d0134; asc 4;;
------------------------
【第五部分：死锁】
LATEST DETECTED DEADLOCK
------------------------
2014-10-16 18:36:30 0x7fc2a95c1700
*** (1) TRANSACTION:
TRANSACTION 1824, ACTIVE 9 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 140474041501440, query id 80 localhost
root updating
##这个事务涉及的SQL
DELETE FROM t WHERE i = 1
##下面显示当死锁发生时，第一个事务正在等待的锁信息。下面这行表示第一个事务正在等待锁被授予
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table
`mysql`.`t` trx id 1824 lock_mode X waiting
##显示record lock的heap no的位置
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
0: len 6; hex 000000000200; asc ;;
1: len 6; hex 00000000071f; asc ;;
2: len 7; hex b80000012b0110; asc + ;;
3: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1825, ACTIVE 29 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 2, OS thread handle 140474041767680, query id 81 localhost
root updating
DELETE FROM t WHERE i = 1
##这显示的是事务2持有的锁信息，从下面两行来看，这个锁就是事务1等待的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table
`mysql`.`t` trx id 1825 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info
bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000200; asc ;;
1: len 6; hex 00000000071f; asc ;;
2: len 7; hex b80000012b0110; asc + ;;
3: len 4; hex 80000001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table
`mysql`.`t` trx id 1825 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
0: len 6; hex 000000000200; asc ;;
1: len 6; hex 00000000071f; asc ;;
2: len 7; hex b80000012b0110; asc + ;;
3: len 4; hex 80000001; asc ;;
*** WE ROLL BACK TRANSACTION (1)
------------
【第六部分：innodb的事务信息】
TRANSACTIONS
------------
Trx id counter 1950
##前面这个是innodb清除MVCC所用的事务ID，将这个值和当前事务ID进行比较，就可以知道有多少老版本的数据未被清除。这个数字多大才可以安全的取值没有硬性和速成的规定，如果数据没做过任何更新，那么一个巨大的数字也不意味着有未清除的数据，因为实际上所有事务在数据库里查看的都是同一个版本的数据（此时只是事务ID在增加，而数据没有变更），另一方面，如果有很多行被更新，那每一行就会有一个或多个版本留在内存里，减少此类开销的最好办法就是确保事务已完成就立即提交，不要让它长时间地处于打开状态，因为一个打开的事务即使不做任何操作，也会影响到innodb清理旧版本的行数据。
Purge done for trx's n:o < 1933 undo n:o < 0 state: running but idle
##历史记录的长度，即位于innodb数据文件的撤销空间里的页面的数目，如果事务执行了更新并提交，这个数字就会增加，而当清理进程移除旧版本数据时，它就会减少，清理进程也会更新Purge done for.....这行中的数值。
History list length 23
##事务列表
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421949033065200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421949033064280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1949, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
##该事务用到的表数和涉及表锁的表数，Innodb一般不会锁定表，但对有些语句会锁定，如果mysql服务器在高于innodb层之上将表锁定，这里也是能够显示出来的，如果事务已经锁定了几行数据，这里将会有一行信息显示出锁定结构的数目
8 lock struct(s), heap size 1136, 1850 row lock(s), undo log entries 17415
##下面这行显示事务线程信息，MySQL thread id <数字>部分和用show full processlist;命令看到的id列相同。紧随其后的是一个内部查询id和一些连接信息，这些信息同样与show full processlist中的输出相同。
MySQL thread id 4, OS thread handle 140474041235200, query id 176 localhost
root update
##下面显示的是事务的完整语句
INSERT INTO `salaries` VALUES (55723,39746,'1997-02-25','1998-02-25'),
(55723,40758,'1998-02-25','1999-02-25'),(55723,44559,'1999-02-25','2000-02-25'),
(55723,44081,'2000-02-25','2001-02-24'),(55723,44112,'2001-02-24','2001-08-16'),
(55724,46461,'1996-12-06','1997-12-06'),(55724,48916,'1997-12-06','1998-12-06'),
(55724,51269,'1998-12-06','1999-12-06'),(55724,51932,'1999-12-06','2000-12-05'),
(55724,52617,'2000-12-05','2001-12-05'),(55724,56658,'2001-12-05','9999-01-01'),
(55725,40000,'1993-01-30','1994-01-30'),(55725,41472,'1994-01-30','1995-01-30'),
(55725,45293,'1995-01-30','1996-01-30'),(55725,473
--------
【第七部分：下面显示的是I/O辅助线程的状态，还有性能计数器的状态】
FILE I/O
--------
## #insert buffer thread
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
## log thread
I/O thread 1 state: waiting for completed aio requests (log thread)
##以下为默认的4个read 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)
##以下为默认的4个write 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)
##读线程和写线程挂起操作的数目等，aio的意思是异步I/O
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
##insert buffer thread挂起的fsync()操作数目等
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
## log thread挂起的fsync()操作数目等
Pending flushes (fsync) log: 0; buffer pool: 0
##这行显示了读，写和fsync()调用执行的数目，在你的机器环境负载下这些绝对值可能会有所不同，因此更重要的是监控它们过去一段时间内是如何改变的。
224 OS file reads, 5770 OS file writes, 803 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 264.84 writes/s, 23.05 fsyncs/s
-------------------------------------
【第八部分：这部分显示了insert buffer和adaptive hash index两个部分的结构的状态】
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 444 buffer(s)
68015.25 hash searches/s, 106259.24 non-hash searches/s
---
【第九部分：这部分显示innodb事务日志子系统的统计】
LOG
---
Log sequence number 165913808
Log flushed up to 164814979
Pages flushed up to 141544038
Last checkpoint at 130503656
##这行显示了当前挂起的日志读写操作，可以将这行的值与第7部分FILE I/O对应的值做比较，以了解你的I/O有多少是由于日志系统引起的。
0 pending log flushes, 0 pending chkp writes
##这行显示了日志操作的统计和每秒日志I/O数
258 log i/o's done, 6.65 log i/o's/second
----------------------
【第九部分：这部分显示了关于innodb缓冲池及其如何使用内存的统计】
BUFFER POOL AND MEMORY
----------------------
##这两行显示了由innodb分配的总内存，以及字典分配内存
Total large memory allocated 2198863872
Dictionary memory allocated 776332
##从这行开始的下面4行显示缓冲池度量值，以页为单位，度量值有总的缓冲池大小，空闲页数，分配用来存储数据库页的页数，以及脏数据库页数。
Buffer pool size 131072
##这行显示缓冲池空闲页数
Free buffers 124908
##这行显示了分配用来存储数据库页的页数，即，表示LRU列表中页的数量,包含young sublist和old sublist
Database pages 5720
##这行显示了LRU中的old sublist部分页的数量
Old database pages 2071
Modified db pages 910
##这行显示了挂起读的数量
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
##这行显示了LRU列表中页移动到LRU首部的次数，因为该服务器在运行阶段改变没有达到innodb_old_blocks_time阀值的值，因此not young为0
Pages made young 4, not young 0
##表示每秒young和non-youngs这两类操作的次数
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
##这行显示了对应上面一行的每秒read，create，write的页数
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
##这行显示了页面预读，随机预读的每秒页数
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
##innodb1.0.x开始支持压缩页的功能，将原来16K的页压缩为1K，2K，4K，8K，而由于页的大小发生了变化，LRU列表也有了些改变，对于非16K的页，是通过unzip_LRU列表进行管理的，可以看到unzip_LRU len为0表示没有使用压缩页.
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
【第十部分：.如果innodb buffer pool使用参数innodb_buffer_pool_instances=num设置了大于1个缓冲池实例，那么就会按照这个参数把innodb_buffer_pool_size=xxx平分为num份。每份的信息显示类似如下】
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 65536
Free buffers 62412
Database pages 2899
Old database pages 1050
Modified db pages 449
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.05 youngs/s, 0.00 non-youngs/s
Pages read 107, created 2792, written 2586
0.00 reads/s, 92.65 creates/s, 122.89 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 2899, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65536
Free buffers 62496
Database pages 2821
Old database pages 1021
Modified db pages 461
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.05 youngs/s, 0.00 non-youngs/s
Pages read 90, created 2731, written 2474
0.00 reads/s, 98.25 creates/s, 122.04 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 2821, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
【第十一部分：这部分显示了innodb其他信息】
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
##这行显示了有多少打开的innodb读视图，读视图是包含事务开始点的数据库内容的MVCC快照，你可以看看某特定事务在第6部分TRANSACTIONS是否有读视图
0 read views open inside InnoDB
##这行显示了内核的主线程状态
Process ID=35909, Main thread ID=140471692396288, state: sleeping
##这行显示了多少行被插入，更新和删除，读取
Number of rows inserted 1526363, updated 0, deleted 3, read 11
##这行显示了对应上面一行的每秒平均值，如果想查看innodb有多少工作量在进行，那么这两行是很好的参考值
52671.72 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
##要注意了，如果看不到这行输出，可能是有大量事务或者是有一个大的死锁截断了输出信息
END OF INNODB MONITOR OUTPUT
============================为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧
转载于:https://my.oschina.net/u/3023401/blog/812884