14.18.3 InnoDB 标准监视器和锁定监视器输出
锁定监视器与标准监视器相同，只是它包含其他锁定信息。为任一监视器启用定期输出会打开同一输出流，但是如果启用了锁定监视器，则该流将包含其他信息。例如，如果启用“标准监视器”和“锁定监视器”，则将打开单个输出流。在禁用锁定监视器之前，流中将包含其他锁定信息。
使用显示引擎的 INNODB 状态语句生成时，Standard Monitor 输出限制为 1MB。此限制不适用于写入到 tserver 标准错误输出(stderr)的输出。
标准监视器输出示例：
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 38 srv_active, 0 srv_shutdown, 252 srv_idle
srv_master_thread log flush and writes: 290
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 119
OS WAIT ARRAY INFO: signal count 103
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 38, rounds 76, OS waits 38
RW-excl spins 2, rounds 9383715, OS waits 3
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 2.00 RW-shared, 4691857.50 RW-excl,
0.00 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`:
,
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
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 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) 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)
------------
TRANSACTIONS
------------
Trx id counter 1950
Purge done for trx's n:o < 1933 undo n:o < 0 state: running but idle
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
8 lock struct(s), heap size 1136, 1850 row lock(s), undo log entries 17415
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
--------
FILE I/O
--------
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
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 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
---
LOG
---
Log sequence number 165913808
Log flushed up to 164814979
Pages flushed up to 141544038
Last checkpoint at 130503656
0 pending log flushes, 0 pending chkp writes
258 log i/o's done, 6.65 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size 131072
Free buffers 124908
Database pages 5720
Old database pages 2071
Modified db pages 910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
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
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
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]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
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
52671.72 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
标准监视器输出部分
有关标准监视器报告的每个度量的说明，请参阅Oracle Enterprise Manager for MySQL 数据库用户指南中的Metrics章。
Status
本节显示时间戳，监视器名称以及每秒平均值所基于的秒数。秒数是从当前时间到上一次打印InnoDB监视器输出为止的经过时间。
BACKGROUND THREAD
srv_master_thread行显示了主后台线程完成的工作。
SEMAPHORES
本节报告线程 awaitsignal 量的统计信息，并统计线程需要旋转或 await 互斥量或 rw-locksignal 量的次数。awaitsignal 量的大量线程可能是磁盘 I/O 或InnoDB内部争用的结果。争用可能是由于查询的高度并行性或 os 线程调度中的问题。在这种情况下，将innodb_thread_concurrency系统变量设置为小于默认值可能会有所帮助。 Spin rounds per wait行显示每个 osawait 互斥锁的自旋锁轮数。
互斥量 Metrics 由显示引擎 INNODB MUTEX报告。
LATEST FOREIGN KEY ERROR
本节提供有关最新外键约束错误的信息。如果没有发生此类错误，则不存在。内容包括失败的语句以及有关失败的约束以及所引用和引用 table 的信息。
LATEST DETECTED DEADLOCK
本节提供有关最新死锁的信息。如果没有发生死锁，则不存在。内容显示涉及哪些事务，每个尝试执行的语句，它们拥有和需要的锁，以及哪个事务InnoDB决定回滚以 break 死锁。 第 14.7.1 节“ InnoDB 锁定”中说明了本节中报告的锁定模式。
TRANSACTIONS
如果此部分报告锁定 await，则您的应用程序可能具有锁定争用。输出还可以帮助跟踪事务死锁的原因。
FILE I/O
本节提供有关InnoDB用于执行各种 I/O 的线程的信息。其中的前几个专用于常规InnoDB处理。内容还显示有关挂起的 I/O 操作的信息和有关 I/O 性能的统计信息。
这些线程的数量由innodb_read_io_threads和innodb_write_io_threads参数控制。参见第 14.15 节“ InnoDB 启动选项和系统变量”。
INSERT BUFFER AND ADAPTIVE HASH INDEX
此部分显示InnoDB插入缓冲区(也称为change buffer)和自适应哈希索引的状态。
有关相关信息，请参见第 14.5.2 节“更改缓冲区”和第 14.5.3 节“自适应哈希索引”。
LOG
本部分显示有关InnoDB日志的信息。内容包括当前日志序号，日志已刷新到磁盘的距离以及InnoDB最后一次检查点的位置。 (请参阅第 14.12.3 节“ InnoDB 检查点”。)该部分还显示有关挂起的写入和写入性能统计信息。
BUFFER POOL AND MEMORY
本节为您提供有关已读和已写页面的统计信息。您可以从这些数字中计算出查询当前正在执行多少个数据文件 I/O 操作。
有关缓冲池统计信息的描述，请参见使用 InnoDB 标准监视器监视缓冲池。有关缓冲池操作的其他信息，请参见第 14.5.1 节“缓冲池”。
ROW OPERATIONS
本节显示了主线程在做什么，包括每种行操作的数量和性能比率。
