MySQL :: MySQL 8.4 参考手册 :: 17.17.3 InnoDB 标准监视器和锁定监视器输出

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 17 InnoDB 存储引擎 / 17.17 InnoDB 监视器 / 17.17.3 InnoDB 标准监视器和锁定监视器输出

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

17.17.3 InnoDB 标准监视器和锁监视器输出17.17.3 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.

使用 SHOW ENGINE INNODB STATUS 语句生成的标准监视器输出被限制为 1MB。此限制不适用于写入到服务器标准错误输出 (stderr) 的输出。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 server standard error output (stderr).

标准监视器输出示例：Example Standard Monitor output:

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-04-12 15:14:08 0x7f971c063700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 1122 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 24
RW-shared spins 4, rounds 8, OS waits 4
RW-excl spins 2, rounds 60, OS waits 2
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 2.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, 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 8, OS thread handle 140289365317376, query id 14 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 `test`.`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 `test`.`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 000000001e19; asc       ;;
 2: len 7; hex 81000001110137; asc       7;;

------------
TRANSACTIONS
------------
Trx id counter 7748
Purge done for trx's n:o < 7747 undo n:o < 0 state: running but idle
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421764459790000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 7747, ACTIVE 23 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 9, OS thread handle 140286987249408, query id 51 localhost root updating
DELETE FROM t WHERE i = 1
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
trx id 7747 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000202; asc       ;;
 1: len 6; hex 000000001e41; asc      A;;
 2: len 7; hex 820000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

------------------
TABLE LOCK table `test`.`t` trx id 7747 lock mode IX
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
trx id 7747 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000202; asc       ;;
 1: len 6; hex 000000001e41; asc      A;;
 2: len 7; hex 820000008b0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (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
833 OS file reads, 605 OS file writes, 208 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 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 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 1 buffer(s)
Hash table size 553253, node heap has 3 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          19643450
Log buffer assigned up to    19643450
Log buffer completed up to   19643450
Log written up to            19643450
Log flushed up to            19643450
Added dirty pages up to      19643450
Pages flushed up to          19643450
Last checkpoint at           19643450
129 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 409606
Buffer pool size   131072
Free buffers       130095
Database pages     973
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 810, created 163, written 404
0.00 reads/s, 0.00 creates/s, 0.00 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: 973, 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       65043
Database pages     491
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 411, created 80, written 210
0.00 reads/s, 0.00 creates/s, 0.00 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: 491, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65536
Free buffers       65052
Database pages     482
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 399, created 83, written 194
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: 482, 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=5772, Main thread ID=140286437054208 , state=sleeping
Number of rows inserted 57, updated 354, deleted 4, read 4421
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

标准监视器输出部分Standard Monitor Output Sections

有关标准监视器报告的每个指标的说明，请参考指标(Metrics) 章节，位于 MySQL 数据库 Oracle 企业管理器用户指南中。For a description of each metric reported by the Standard Monitor, refer to the Metrics chapter in the Oracle Enterprise Manager for MySQL Database User's Guide.

StatusStatus
本节显示时间戳、监视器名称以及用于计算每秒平均值的秒数。秒数是当前时间与上次 InnoDB 监视器输出打印之间的经过时间。This section shows the timestamp, the monitor name, and the number of seconds that per-second averages are based on. The number of seconds is the elapsed time between the current time and the last time InnoDB Monitor output was printed.
BACKGROUND THREADBACKGROUND THREAD
srv_master_thread 行显示主后台线程完成的工作。The srv_master_thread lines shows work done by the main background thread.
SEMAPHORESSEMAPHORES
本节报告等待信号量的线程以及线程在互斥锁或读写锁信号量上需要自旋或等待的次数统计。大量线程等待信号量可能是由于磁盘 I/O，或 InnoDB 内部的争用问题。争用可能是由于查询的高并行性或操作系统线程调度的问题。在这种情况下，将系统变量 innodb_thread_concurrency 设置为比默认值更小可能会有所帮助。Spin rounds per wait 行显示每次操作系统等待互斥锁的自旋轮次。This section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or a rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside InnoDB. Contention can be due to heavy parallelism of queries or problems in operating system thread scheduling. Setting the innodb_thread_concurrency system variable smaller than the default value might help in such situations. The Spin rounds per wait line shows the number of spinlock rounds per OS wait for a mutex.
互斥锁指标由 SHOW ENGINE INNODB MUTEX 报告。Mutex metrics are reported by SHOW ENGINE INNODB MUTEX.
LATEST FOREIGN KEY ERRORLATEST FOREIGN KEY ERROR
本节提供有关最近外键约束错误的信息。如果没有发生此类错误，则不会显示。本节内容包括失败的语句，以及失败约束的信息和引用表与被引用表的信息。This section provides information about the most recent foreign key constraint error. It is not present if no such error has occurred. The contents include the statement that failed as well as information about the constraint that failed and the referenced and referencing tables.
LATEST DETECTED DEADLOCKLATEST DETECTED DEADLOCK
本节提供有关最近死锁的信息。如果没有发生死锁，则不会显示。本节内容显示涉及的事务、每个事务尝试执行的语句、它们持有和需要的锁，以及 InnoDB 为解除死锁而决定回滚的事务。本节报告的锁模式在第 17.7.1 节，“InnoDB 锁定” 中有解释。This section provides information about the most recent deadlock. It is not present if no deadlock has occurred. The contents show which transactions are involved, the statement each was attempting to execute, the locks they have and need, and which transaction InnoDB decided to roll back to break the deadlock. The lock modes reported in this section are explained in Section 17.7.1, “InnoDB Locking”.
TRANSACTIONSTRANSACTIONS
如果本节报告了锁等待，您的应用程序可能存在锁竞争。输出还可以帮助追踪事务死锁的原因。If this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.
FILE I/OFILE I/O
本节提供了关于 InnoDB 用于执行各种类型 I/O 的线程的信息。其中最前面的几个线程专门用于一般的 InnoDB 处理。内容还显示了待处理 I/O 操作的信息以及 I/O 性能的统计数据。This section provides information about threads that InnoDB uses to perform various types of I/O. The first few of these are dedicated to general InnoDB processing. The contents also display information for pending I/O operations and statistics for I/O performance.
这些线程的数量由 innodb_read_io_threads 和 innodb_write_io_threads 参数控制。请参见第 17.14 节，“InnoDB 启动选项和系统变量”。The number of these threads are controlled by the innodb_read_io_threads and innodb_write_io_threads parameters. See Section 17.14, “InnoDB Startup Options and System Variables”.
INSERT BUFFER AND ADAPTIVE HASH INDEXINSERT BUFFER AND ADAPTIVE HASH INDEX
本节显示 InnoDB 插入缓冲区（也称为变更缓冲）和自适应哈希索引的状态。This section shows the status of the InnoDB insert buffer (also referred to as the change buffer) and the adaptive hash index.
有关相关信息，请参见第 17.5.2 节，“Change Buffer” 和第 17.5.3 节，“Adaptive Hash Index”。For related information, see Section 17.5.2, “Change Buffer”, and Section 17.5.3, “Adaptive Hash Index”.
LOGLOG
本节显示有关 InnoDB 日志的信息。内容包括当前日志序列号、日志刷新到磁盘的进度，以及 InnoDB 上次进行检查点的位置。（参见第 17.11.3 节，“InnoDB Checkpoints”。）本节还显示有关待处理写入和写入性能统计的信息。This section displays information about the InnoDB log. The contents include the current log sequence number, how far the log has been flushed to disk, and the position at which InnoDB last took a checkpoint. (See Section 17.11.3, “InnoDB Checkpoints”.) The section also displays information about pending writes and write performance statistics.
BUFFER POOL AND MEMORYBUFFER POOL AND MEMORY
本节为您提供已读和已写页面的统计信息。您可以根据这些数据计算查询当前进行了多少数据文件 I/O 操作。This section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.
有关缓冲池统计描述，请参阅使用 InnoDB 标准监视器监控缓冲池。有关缓冲池操作的更多信息，请参阅第 17.5.1 节，“缓冲池”。For buffer pool statistics descriptions, see Monitoring the Buffer Pool Using the InnoDB Standard Monitor. For additional information about the operation of the buffer pool, see Section 17.5.1, “Buffer Pool”.
ROW OPERATIONSROW OPERATIONS
本节显示主线程正在执行的操作，包括每种类型行操作的数量和性能速率。This section shows what the main thread is doing, including the number and performance rate for each type of row operation.

首页上一级下一节