InnoDB监视器
InnoDB监视器提供有关InnoDB内部状态的信息。此信息对性能调优很有用。
InnoDB监视器类型
InnoDB监视器有两种类型:
.标准的InnoDB Monitor显示以下类型的信息:
.主后台线程完成的工作
.线程的信号量等待
.关于最近的外键和死锁错误的数据
.锁等待事务
.由活动事务持有的表和记录锁
.待处理I/O操作及相关统计
.插入缓冲区和自适应哈希索引统计信息
.重做日志数据
.缓冲池统计信息
.行运算数据
.InnoDB锁监视器打印额外的锁信息作为标准InnoDB监视器输出的一部分。
启用InnoDB监视器
当InnoDB监控器为周期性输出启用时，InnoDB将输出写入mysqld服务器标准错误输出(stderr)。InnoDB将诊断输出发送到stderr，而不是stdout或固定大小的内存缓冲区，以避免潜在的缓冲区溢出。
在Windows上，stderr被定向到默认日志文件，除非另有配置。如果您希望将输出定向到控制台窗口而不是错误日志，请使用–console选项从控制台窗口的命令提示符启动服务器。
在Unix和类Unix系统上，stderr通常被定向到终端，除非另有配置。
当启用时，InnoDB监控大约每15秒打印一次数据。这些数据在性能调优中很有用。作为副作用，SHOW ENGINE INNODB STATUS的输出每15秒写入MySQL数据目录中的状态文件。文件名为innodb_status.pid，其中pid是服务器进程ID。InnoDB在服务器正常关闭时删除文件。如果发生了异常关机，则可能存在这些状态文件的实例，必须手动删除。在删除文件之前，检查它们是否包含有关异常关机原因的有用信息。一个innodb_status.pid只有启用了innodb-status-file配置选项，才会创建。默认关闭。
InnoDB监控器应该只在你真正想要查看监控器信息时才启用，因为输出的生成会导致一些性能下降。此外，如果监视器输出指向错误日志，如果您稍后忘记禁用监视器，则日志可能会变得相当大。
注意:
为了辅助故障诊断，在某些条件下，InnoDB临时启用了标准的InnoDB监视器输出。
InnoDB监视器输出以一个包含时间戳和监视器名称的报头开始。例如:
===================================== 2024-01-15 16:50:33 0x7f3e8c201700 INNODB MONITOR OUTPUT =====================================
标准InnoDB监控器的首部(InnoDB监控器输出)也用于锁监控器，因为后者产生了相同的输出，但添加了额外的锁信息。
innodb_status_output和innodb_status_output_locks系统变量用于启用标准的InnoDB监视器和InnoDB锁监视器。
启用或禁用InnoDB监视器需要PROCESS特权。
启用标准InnoDB监视器
通过将innodb_status_output系统变量设置为ON来启用标准InnoDB监视器。
mysql> SET GLOBAL innodb_status_output=ON; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%innodb_status_output%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_status_output | ON | | innodb_status_output_locks | OFF | +----------------------------+-------+ 2 rows in set (0.01 sec)
关闭标准的InnoDB Monitor，将innodb_status_output设置为OFF。
当您关闭服务器时，innodb_status_output变量被设置为默认的OFF值。
按需获取标准的InnoDB监控器输出
作为启用标准InnoDB监控器周期性输出的替代方案，您可以使用SHOW ENGINE INNODB STATUS SQL语句按需获取标准InnoDB监控器输出，该语句将输出获取到您的客户端程序。如果你使用的是mysql交互式客户端，将通常的分号语句结束符替换为\G，输出的可读性会更好:
mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2024-01-15 16:50:33 0x7f3e8c201700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 8 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 27 srv_active, 0 srv_shutdown, 427925 srv_idle srv_master_thread log flush and writes: 427940 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 15736 OS WAIT ARRAY INFO: signal count 1770 RW-shared spins 0, rounds 18523, OS waits 12422 RW-excl spins 0, rounds 32549, OS waits 1086 RW-sx spins 1768, rounds 50702, OS waits 1607 Spin rounds per wait: 18523.00 RW-shared, 32549.00 RW-excl, 28.68 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 5697073 Purge done for trx's n:o < 5697071 undo n:o < 0 state: running but idle History list length 70 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421390708152144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- 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 141 OS file reads, 4062 OS file writes, 3289 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 1714, seg size 1716, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 89430260246 Log flushed up to 89430260246 Pages flushed up to 89430260246 Last checkpoint at 89430260237 0 pending log flushes, 0 pending chkp writes 2662 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 8795455488 Dictionary memory allocated 234968 Buffer pool size 524224 Free buffers 523545 Database pages 685 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 96, created 589, written 893 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: 685, 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 65528 Free buffers 65456 Database pages 78 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 31, created 47, written 131 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: 78, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 65528 Free buffers 65391 Database pages 137 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 11, created 126, written 367 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: 137, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 65528 Free buffers 65413 Database pages 115 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 8, created 107, written 166 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: 115, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 65528 Free buffers 65406 Database pages 122 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 13, created 109, written 182 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: 122, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 65528 Free buffers 65463 Database pages 65 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 8, created 57, written 16 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: 65, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 65528 Free buffers 65469 Database pages 59 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 9, created 50, written 11 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: 59, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 65528 Free buffers 65470 Database pages 58 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 9, created 49, written 10 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: 58, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 65528 Free buffers 65477 Database pages 51 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 7, created 44, written 10 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: 51, 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=10759, Main thread ID=139906706892544, state: sleeping Number of rows inserted 5990, updated 0, deleted 0, read 1794460 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
如果启用了InnoDB锁监视器, SHOW ENGINE INNODB STATUS输出还包括InnoDB锁监视器数据。
启用InnoDB锁监视器
InnoDB锁监控器数据与InnoDB标准监控器输出一起打印。InnoDB标准监控器和InnoDB锁监控器都必须启用，才能定期打印InnoDB锁监控器数据。
开启InnoDB锁监控，设置innodb_status_output_locks系统变量为ON。InnoDB标准监控器和InnoDB锁监控器都必须启用，以便定期打印InnoDB锁监控器数据:
mysql> SET GLOBAL innodb_status_output=ON; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL innodb_status_output_locks=ON; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%innodb_status_output%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_status_output | ON | | innodb_status_output_locks | ON | +----------------------------+-------+ 2 rows in set (0.00 sec)
要禁用InnoDB锁监控，将innodb_status_output_locks设置为OFF。将innodb_status_output设置为OFF也禁用InnoDB标准监视器。
当你关闭服务器时，innodb_status_output和innodb_status_output_locks变量被设置为默认的OFF值。
注意:
为SHOW ENGINE INNODB STATUS输出启用InnoDB锁监视器，只需要启用innodb_status_output_locks。
InnoDB标准监视器和锁监视器输出
锁监视器与标准监视器相同，只是它包含了额外的锁信息。
为周期性输出启用任一监视器都会打开相同的输出流，但如果启用了Lock monitor，则该流将包含额外的信息。例如，如果您启用了标准监控和锁定监视器，它打开单个输出流。在禁用锁监视器之前，流包含额外的锁信息。
当使用SHOW ENGINE INNODB STATUS语句生成时，标准Monitor输出限制为1MB。此限制不适用于写入tserver标准错误输出(stderr)的输出。
标准监视器输出示例:
mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2024-01-15 17:08:03 0x7f3e8c201700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 27 srv_active, 0 srv_shutdown, 428975 srv_idle srv_master_thread log flush and writes: 428990 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 15736 OS WAIT ARRAY INFO: signal count 1770 RW-shared spins 0, rounds 18523, OS waits 12422 RW-excl spins 0, rounds 32549, OS waits 1086 RW-sx spins 1768, rounds 50702, OS waits 1607 Spin rounds per wait: 18523.00 RW-shared, 32549.00 RW-excl, 28.68 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 5697073 Purge done for trx's n:o < 5697071 undo n:o < 0 state: running but idle History list length 70 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421390708152144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- 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 141 OS file reads, 4062 OS file writes, 3289 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 1714, seg size 1716, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) Hash table size 2365241, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 89430260246 Log flushed up to 89430260246 Pages flushed up to 89430260246 Last checkpoint at 89430260237 0 pending log flushes, 0 pending chkp writes 2662 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 8795455488 Dictionary memory allocated 234968 Buffer pool size 524224 Free buffers 523545 Database pages 685 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 96, created 589, written 893 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: 685, 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 65528 Free buffers 65456 Database pages 78 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 31, created 47, written 131 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: 78, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 65528 Free buffers 65391 Database pages 137 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 11, created 126, written 367 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: 137, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 65528 Free buffers 65413 Database pages 115 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 8, created 107, written 166 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: 115, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 65528 Free buffers 65406 Database pages 122 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 13, created 109, written 182 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: 122, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 65528 Free buffers 65463 Database pages 65 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 8, created 57, written 16 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: 65, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 65528 Free buffers 65469 Database pages 59 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 9, created 50, written 11 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: 59, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 65528 Free buffers 65470 Database pages 58 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 9, created 49, written 10 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: 58, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 65528 Free buffers 65477 Database pages 51 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 7, created 44, written 10 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: 51, 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=10759, Main thread ID=139906706892544, state: sleeping Number of rows inserted 5990, updated 0, deleted 0, read 1794460 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
标准监视器输出部分
.Status(状态)
本节显示时间戳、监视器名称和每秒平均值所基于的秒数。秒数是当前时间和最后一次打印InnoDB Monitor输出之间经过的时间。
.BACKGROUND THREAD(后台线程)
srv_master_thread行显示了主后台线程完成的工作。
.SEMAPHORES(信号量)
这部分是报告等待信号量的线程，以及线程需要旋转或等待互斥量或rw-lock信号量的统计数据。大量线程等待信号量可能是磁盘I/O的结果，或者是InnoDB内部的争用问题。竞争可能是由于大量的并行查询或操作系统线程调度问题造成的。在这种情况下，将innodb_thread_concurrency系统变量设置为小于默认值可能会有所帮助。Spin rounds per wait line显示了每个操作系统等待互斥量的旋转轮数。
互斥参数由SHOW ENGINE INNODB MUTEX报告。
mysql> SHOW ENGINE INNODB MUTEX\G *************************** 1. row *************************** Type: InnoDB Name: rwlock: dict0dict.cc:2737 Status: waits=35 *************************** 2. row *************************** Type: InnoDB Name: rwlock: dict0dict.cc:1183 Status: waits=77 *************************** 3. row *************************** Type: InnoDB Name: rwlock: log0log.cc:838 Status: waits=32 *************************** 4. row *************************** Type: InnoDB Name: sum rwlock: buf0buf.cc:1460 Status: waits=13399 4 rows in set (0.04 sec)
.LATEST FOREIGN KEY ERROR(最新的外键错误)
本节提供有关最近的外键约束错误的信息。如果没有发生此类错误，则不存在该错误。内容包括失败的语句以及有关失败的约束以及被引用表和引用表的信息。
.LATEST DETECTED DEADLOCK(最近检测到的死锁)
本节提供有关最近死锁的信息。如果没有发生死锁，则不存在。内容显示了涉及到哪些事务，每个试图执行的语句，它们拥有和需要的锁，以及InnoDB决定回滚哪个事务以打破死锁。
.TRANSACTIONS(事务)
如果本节报告锁等待，则您的应用程序可能存在锁争用。输出还可以帮助跟踪事务死锁的原因。
.FILE I/O(文件IO)
本节提供了InnoDB用于执行各种类型I/O的线程的信息。前几个是专用于一般InnoDB处理的。内容还显示挂起的I/O操作信息和I/O性能统计信息。
这些线程的数量由innodb_read_io_threads和innodb_write_io_threads参数控制。
.INSERT BUFFER AND ADAPTIVE HASH INDEX
本节展示了InnoDB插入缓冲区(也称为更改缓冲区)和自适应哈希索引的状态。
.LOG
介绍InnoDB日志的相关信息。内容包括当前日志序列号，日志被刷新到磁盘的距离，以及InnoDB最后一次执行检查点的位置。该部分还显示有关挂起写和写性能统计信息的信息。
.BUFFER POOL AND MEMORY
本节提供了读取和写入页面的统计信息。您可以从这些数字中计算出查询当前正在执行多少数据文件I/O操作
.ROW OPERATIONS
本节显示主线程正在执行的操作，包括每种类型的行操作的数量和性能比率。