show engine innodb status 输出结果解读

基于MySQL 5.7.32
最近想整理一下show engine innodb status的解读，但是发现中文互联网上相关的信息要么是不完整，要么是有诸多错误，要么是版本过于老，很多新增条目并未包含，决定自己整理一份。
这里将 show engine innodb status 输出结果按照章节部分依次剖析含义。
主要参考自官方文档 via Dash，
部分参考了https://cloud.tencent.com/developer/article/1400095
最近有想将自己基于MySQL 5.7和8.0.18的知识大纲和笔记书公开出来，看看有没有时间。

show engine innodb status 输出结果解读

输出示例

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2021-04-22 11:33:11 0x7fe51c222700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 780 srv_active, 0 srv_shutdown, 12 srv_idle
srv_master_thread log flush and writes: 792

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1225
OS WAIT ARRAY INFO: signal count 1336
RW-shared spins 0, rounds 2286, OS waits 398
RW-excl spins 0, rounds 2371, OS waits 70
RW-sx spins 229, rounds 699, OS waits 8
Spin rounds per wait: 2286.00 RW-shared, 2371.00 RW-excl, 3.05 RW-sx

------------
TRANSACTIONS
------------
Trx id counter 15061670
Purge done for trx's n:o < 15061468 undo n:o < 0 state: running but idle
History list length 1107
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422101800984176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422101800982352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15061669, ACTIVE (PREPARED) 0 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 5, OS thread handle 140621995427584, query id 523581 42.192.58.85 kk starting
commit

--------
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)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
95343 OS file reads, 195458 OS file writes, 135366 OS fsyncs
49.90 reads/s, 16384 avg bytes/read, 241.48 writes/s, 169.18 fsyncs/s

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 192, seg size 194, 1122 merges
merged operations:
 insert 1226, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 315 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 307 buffer(s)
Hash table size 1182691, node heap has 403 buffer(s)
Hash table size 1182691, node heap has 293 buffer(s)
Hash table size 1182691, node heap has 290 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 298 buffer(s)
66.29 hash searches/s, 1026.70 non-hash searches/s

---
LOG
---
Log sequence number 14187726461
Log flushed up to   14187725424
Pages flushed up to 14142755783
Last checkpoint at  14142399692
1 pending log flushes, 0 pending chkp writes
92454 log i/o's done, 102.50 log i/o's/second

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 450320
Buffer pool size   262112
Free buffers       163810
Database pages     95770
Old database pages 35430
Modified db pages  43010
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 312, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95265, created 505, written 97170
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 988 / 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: 95770, unzip_LRU len: 0
I/O sum[0]:cur[792], unzip sum[0]:cur[0]

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
...
...
<same as BUFFER POOL AND MEMORY>
...

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17254, Main thread ID=140622046865152, state: sleeping
Number of rows inserted 52629, updated 104255, deleted 52135, read 156400
59.79 inserts/s, 115.49 updates/s, 59.39 deletes/s, 174.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

1.HEAD

Per second averages calculated from the last 10 seconds

表示本次报告内"每秒平均"是统计自最后10秒的情况做的计算。

2.BACKGROUND THREAD

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 780 srv_active, 0 srv_shutdown, 12 srv_idle
srv_master_thread log flush and writes: 792

srv_master_thread loops: 780 srv_active, 0 srv_shutdown, 12 srv_idle
srv_master_thread loops ： master线程的循环次数。
每次循环时会选择一种状态来执行（active、shutdown、idle）。
其中，active状态的数量增加与数据变化有关，与查询无关。
可以通过srv_activesrv_idle的差值来获得系统的整体负载情况。
active值越大表示系统越繁忙，当active远远高出idle时，说明这个数据库相对比较繁忙。

MySQL主要线程简介
- Master thread：主线程，主要负责异步刷新和数据一致性处理。
- IO thread：使用异步IO模型，负责处理不同类型的IO请求回调。
- Purge thread：事务提交后，回收undo。
- Page Cleaner thread：执行ibp的脏页刷新。
  而对于Page Cleaner thread,默认值为1，如果在MySQL日志中看到如下的信息，说明我们的Cleaner Thread需要调整一下了。
```
2019-02-14T23:50:00.501209Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 28469710ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.
```
srv_master_thread log flush and writes: 792

主线程日志刷新和写入次数。

3.SEMAPHORES

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.

Mutex metrics are reported by SHOW ENGINE INNODB MUTEX.

本节报告了线程等待信号量的统计信息，并统计线程需要spin或等待mutex或rw-lock信号量的次数。

较大的threads waiting for semaphores可能是磁盘I / O或InnoDB内部争用问题的结果。

争用可能是由于查询的高并行或操作系统线程调度中的问题所致。

在这种情况下，将innodb_thread_concurrency系统变量设置为小于默认值可能会有所帮助。

每个spin rounds per wait显示的是每个操作系统等待mutex的spinlock round数。

mutex度量标准由SHOW ENGINE INNODB MUTEX报告。

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1225
OS WAIT ARRAY INFO: signal count 1336
RW-shared spins 0, rounds 2286, OS waits 398
RW-excl spins 0, rounds 2371, OS waits 70
RW-sx spins 229, rounds 699, OS waits 8
Spin rounds per wait: 2286.00 RW-shared, 2371.00 RW-excl, 3.05 RW-sx

OS WAIT ARRAY INFO: reservation count 1225

OS的等待阵列信息。InnoDB分配槽的额度。
OS WAIT ARRAY INFO: signal count 1336

OS的等待阵列信息。衡量的是线程通过阵列得到的信号的频度。（没懂）
RW-shared spins 0, rounds 2286, OS waits 398

spin共享锁(shared)的计数器。
RW-excl spins 0, rounds 2371, OS waits 70

spin排它锁的计数器。
RW-sx spins 229, rounds 699, OS waits 8

spin共享写锁的计数器。

（猜测sx应该是shared x，共享写锁常出现在事务中的update操作，环境中正是进行着大量update。）
Spin rounds per wait: 2286.00 RW-shared, 2371.00 RW-excl, 3.05 RW-sx

每个spin rounds per wait显示的是每个操作系统等待mutex的spinlock round数。

InnoDB有一个多阶段的等待策略。
首先会对锁进行自旋（spin），如果经历了一个自旋周期后还没有持有锁，则进入到操作系统等待状态（os wait），等待被唤醒。
如果在一秒中看到几十万个spin wait，则需要关注show engine innodb mutex;

4.TRANSACTIONS

If this section reports lock waits, your applications might have lock contention.
The output can also help to trace the reasons for transaction deadlocks.

当前活跃事务列表。
如果此部分报告了 lock waits，则您的应用程序可能具有锁争用。
这部分信息还可以帮助跟踪事务死锁的原因。

Trx id counter 15061670
Purge done for trx's n:o < 15061468 undo n:o < 0 state: running but idle
History list length 1107
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422101800984176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422101800982352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15061669, ACTIVE (PREPARED) 0 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 5, OS thread handle 140621995427584, query id 523581 42.192.58.85 kk starting
commit

Trx id counter 15061670

每创建一个新事务就会累加。
Purge done for trx's n:o < 15061468 undo n:o < 0 state: running but idle

小于15061468这个ID的事务的历史数据都被清理掉了。

减少此类开销的最好办法就是确保事务已完成就立即提交，不要让事务长时间地处于打开状态，因为一个打开的事务即使不做任何操作，也会影响到innodb清理旧版本的行数据。

undo n:o < 0这个是innodb清理进程正在使用的撤销日志编号，为0 0时说明清理进程处于空闲状态。
History list length 1107

历史记录中包含多少个事务的列表长度。就是InnoDB数据文件undo里的未清除事务的数量。当一个事务执行了更新并提交后，这个数字就会增加。当清除进程移除一个旧版本数据时，它就会递减（purge thread也会更新purge done for …也就是上一行的数据。）。
---TRANSACTION 422101800984176, not started

每个session的事务情况。事务ID超级大的就是只读事务。一旦有写的动作，才会切换为读写事务，分配正常的事务ID。

not started表示这个事务已经提交并且没有再发起影响事务的语句，可能刚好空闲。看事务ID，这是一个查询事务。一旦事务落盘，就not started。
---TRANSACTION 15061669, ACTIVE (PREPARED) 0 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2

active 0 sec，活跃事务，持续了0秒。状态可能是not started，或者其他。如果有锁等待，在这里应该也会看到。
MySQL thread id 5, OS thread handle 140621995427584, query id 523581 42.192.58.85 kk starting

MySQL线程ID及其他信息。

5.FILE 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.

The number of these threads are controlled by the innodb_read_io_threads and innodb_write_io_threads parameters. See Section 14.14, “InnoDB Startup Options and System Variables”.

本节提供有关InnoDB用于执行各种类型的I / O的线程的信息。其中的前几个专用于常规InnoDB处理。内容还显示有关挂起的I / O操作的信息和有关I / O性能的统计信息。

这些线程的数量由innodb_read_io_threads和innodb_write_io_threads参数控制。请参见“ InnoDB启动选项和系统变量”。

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)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
95343 OS file reads, 195458 OS file writes, 135366 OS fsyncs
49.90 reads/s, 16384 avg bytes/read, 241.48 writes/s, 169.18 fsyncs/s

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

插入缓冲区线程。负责change buffer的合并。
I/O thread 1 state: waiting for completed aio requests (log thread)

日志线程。负责异步的日志刷新。
I/O thread 2 state: waiting for completed aio requests (read thread)

读线程，负责读前置的操作（read-ahead），预测InnoDB将要使用的数据并读入内存。

读线程的数量受参数 innodb_read_io_threads 控制。
I/O thread 6 state: waiting for completed aio requests (write thread)

写线程，刷新脏页。

写线程的数量受参数 innodb_write_io_threads 控制。
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] ,

读写线程挂起操作的数量。aio就是异步io。
ibuf aio reads:, log i/o's:, sync i/o's:

insert buffer thread挂起的fsync操作数量。

Pending flushes (fsync) log: 1; buffer pool: 0

log thread 挂起的fsync操作数量。
95343 OS file reads, 195458 OS file writes, 135366 OS fsyncs

读、写以及fsyncs操作的次数。
49.90 reads/s, 16384 avg bytes/read, 241.48 writes/s, 169.18 fsyncs/s

在头部区域“Per second averages calculated from the last 10 seconds ” 的时间段内，平均每秒的执行次数。

三行挂起读写线程、缓冲池线程、日志线程的统计信息的值(Pending normal aio reads、 ibuf aio reads、Pending flushes (fsync) log)是检测I/O受限的应用的一个好方法，如果这些I/O大部分有挂起操作，那么负载可能I/O受限。

在linux系统下使用参数：innodb_read_io_threads和innodb_write_io_threads两个变量来配置读写线程的数量，默认为各4个线程。
insert buffer thread：负责插入缓冲合并，如：记录被从插入缓冲合并到表空间中
log thread：负责异步刷事务日志
read thread：执行预读操作以尝试预先读取innodb预感需要的数据
write thread：刷新脏页缓冲

6.INSERT BUFFER AND ADAPTIVE HASH INDEX

This section shows the status of the InnoDB insert buffer (also referred to as the change buffer) and the adaptive hash index.

For related information, see Section 14.5.2, “Change Buffer”, and Section 14.5.3, “Adaptive Hash Index”.

此部分显示InnoDB插入缓冲区（也称为更改缓冲区）和自适应哈希索引的状态。有关相关信息，请参见第14.5.2节“更改缓冲区”和第14.5.3节“自适应哈希索引”。

Ibuf: size 1, free list len 192, seg size 194, 1122 merges
merged operations:
 insert 1226, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 315 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 307 buffer(s)
Hash table size 1182691, node heap has 403 buffer(s)
Hash table size 1182691, node heap has 293 buffer(s)
Hash table size 1182691, node heap has 290 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 298 buffer(s)
66.29 hash searches/s, 1026.70 non-hash searches/s

Ibuf: size 1, free list len 192, seg size 194, 1122 merges

size 1，已经合并记录页的数量。

Free list len，插入缓冲中空闲列表的长度。

Seg size，当前insert buffer的长度，单位为页（16k）。

merges，代表合并插入的次数。
merged operations:
insert 1226, delete mark 0, delete 0

三个项目分别表示merge操作合并了多少个insert buffer、delete buffer（标记删除）、purge buffer（真正删除）。

discarded operations:
insert 0, delete mark 0, delete 0

三个项目分别表示当change buffer发生merge时，表可能已经被删除了，就不再需要合并到辅助索引中了。

Hash table size 1182691, node heap has 315 buffer(s)

自适应哈希索引状态。

AHI大小，及AHI的使用情况（存有多少个buffer）。
66.29 hash searches/s, 1026.70 non-hash searches/s

根据头部的时间段，哈希索引查找次数和非哈希索引的查找次数。

哈希索引仅适用于等值查询。可以根据searches的比例来结合业务情况，了解是否需要继续启用AHI特性（默认启用）。

7.LOG

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 14.12.3, “InnoDB Checkpoints”.) The section also displays information about pending writes and write performance statistics.

本节显示有关InnoDB日志的信息。内容包括当前的日志序列号，将日志刷新到磁盘的距离以及InnoDB上次执行检查点的位置。（请参见第14.12.3节“ InnoDB检查点”。）本节还显示有关挂起写入和写入性能统计信息。

Log sequence number 14187726461
Log flushed up to   14187725424
Pages flushed up to 14142755783
Last checkpoint at  14142399692
1 pending log flushes, 0 pending chkp writes
92454 log i/o's done, 102.50 log i/o's/second

Log sequence number 14187726461

当前日志LSN
Log flushed up to 14187725424

日志已刷新落盘到的位置的LSN
Pages flushed up to 14142755783
pages flushed up to指的是下一次即将做checkpoint lsn 的位置；

在没有新数据的写入的情况下， pages flushed up to取的是Log sequence number（log_sys->lsn）；
- 在没数据写入的情况下，为什么last checkpoint point不等于pages flushed up to？是因为做checkpoint是同时redo日志会写MLOG_CHECKPOINT，而MLOG_CHECKPOINT占用九个字节，所以会出现pages flushed up to-last checkpoint point=9；
  
  https://blog.csdn.net/cuiyandan4490/article/details/100464485
Last checkpoint at 14142399692

最新ckpt的LSN
1 pending log flushes, 0 pending chkp writes

还未完成的日志操作及统计信息
92454 log i/o's done, 102.50 log i/o's/second

已经发生的日志操作统计信息

8.BUFFER POOL AND MEMORY / INDIVIDUAL BUFFER POOL INFO

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.

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 14.5.1, “Buffer Pool”.

本部分为您提供有关已读和已写页面的统计信息。您可以从这些数字中计算出查询当前正在执行多少个数据文件I / O操作。有关缓冲池统计信息的描述，请参阅使用InnoDB Standard Monitor监视缓冲池。有关缓冲池操作的更多信息，请参见第14.5.1节“缓冲池”。

InnoDB存储引擎读书笔记/05.LRU、页压缩、脏页、检查点.md

Total large memory allocated 4397727744
Dictionary memory allocated 450320
Buffer pool size   262112
Free buffers       163810
Database pages     95770
Old database pages 35430
Modified db pages  43010
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 312, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95265, created 505, written 97170
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 988 / 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: 95770, unzip_LRU len: 0
I/O sum[0]:cur[792], unzip sum[0]:cur[0]

Total large memory allocated 4397727744

分配给InnoDB Buffer Pool的总内存，单位bytes。
Dictionary memory allocated 450320

分配给InnoDB数据字典的内存，单位bytes。
Buffer pool size 262112

分配给IBP的内存，单位pages
Free buffers 163810

Buffer Pool Free List 总大小，单位pages
Database pages 95770

Buffer Pool LRU List 总大小，单位pages
Old database pages 35430

IBP old LRU 总大小，单位pages（冷端）
Modified db pages 43010

当前IBP中脏页的数量，单位pages
Pending reads 0

等待读入IBP的页数量，单位pages
Pending writes: LRU 0, flush list 0, single page 0

LRU，从LRU列表的底部开始写入的旧脏页数。（没懂）；flush list，ckpt期间要刷新的缓冲池页面数；single page，IBP中暂挂的独立页面写入数。
Pages made young 312, not young 0

LRU中被made young的页面数（LRU列表中页移动到前端的次数）；not young，LRU中保持在old 子列表中的页面数（没被移动到前端，因innodb_old_blocks_time设置。）
0.00 youngs/s, 0.00 non-youngs/s

youngs/s度量标准仅用于old pages，基于对page的访问次数，而不是页的数量。对页进行多次访问都会被计算。如果见到非常低的值，可能需要减小延迟或增加old page LRU list 的比例。增大后，页面需要更长的时间才会移动到尾部，这就增加了再次访问page，从而使他们made young的可能性增大。 Not young，如果在执行大表扫描时未看到较高的non-young和non-youngs/s，请增加innodb_old_blocks_time。
Pages read 95265, created 505, written 97170

从磁盘到缓冲池读取的页面总数。在缓冲池中创建的页面总数（从ibp分配，但并未从文件中读入数据的页面，可能属于一个被删除的表）。从缓冲池到磁盘写入的页面总数。
0.00 reads/s, 0.00 creates/s, 0.00 writes/s

平均每秒读的页数，创建的页数，写的页数。
Buffer pool hit rate 988 / 1000, young-making rate 0 / 1000 not 0 / 1000

buffer pool hit ，缓冲池的命中率，用来衡量innodb在缓冲池中查找到所需页的比例，（不确定是不是：它度量自上次Innodb状态输出后到本次输出这段时间内的命中率，因此，如果服务器自那以后一直很安静，你将会看到No buffer pool page gets since the last printout。它对于度量缓存池的大小并没有用处。）；young-making rate ，指对所有buffer pool的访问，不仅仅是old page LRU list。Young making和not young通常不会合计到整个IBP的命中率（因为old命中了就移动到new，而new的命中只有在与head有一定距离时才会移动到头部；not，因innodb_old_blocks_time设置而不满足移动到new，或命中new但是并没使其向头部移动的全ibp的命中率，不仅仅是old LRU list。
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

每秒平均预读操作次数；每秒因未被访问而被逐出ibp的页数；每秒平均随机预读次数。
LRU len: 95770, unzip_LRU len: 0

LRU len，IBP中LRU列表包含页面的总数（Database pages）；压缩页的unzip_LRU列表包含页面的总数（0则表示没有）。
I/O sum[0]:cur[792], unzip sum[0]:cur[0]

最近50秒内访问的缓冲池LRU列表页面的总数；已访问的缓冲池LRU列表页面的总数；已访问的缓冲池unzip_LRU列表页面的总数；已访问的缓冲池unzip_LRU列表页面的总数。

9.ROW OPERATIONS

This section shows what the main thread is doing, including the number and performance rate for each type of row operation.

本节显示了主线程在做什么，包括每种行操作的数量和性能比率。

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17254, Main thread ID=140622046865152, state: sleeping
Number of rows inserted 52629, updated 104255, deleted 52135, read 156400
59.79 inserts/s, 115.49 updates/s, 59.39 deletes/s, 174.88 reads/s

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17254, Main thread ID=140622046865152, state: sleeping
Number of rows inserted 52629, updated 104255, deleted 52135, read 156400

累计值。
59.79 inserts/s, 115.49 updates/s, 59.39 deletes/s, 174.88 reads/s

前面的统计时间，平均下来每秒值。

posted @ 2021-04-23 19:24 孔个个阅读(4900) 评论(0) 编辑收藏举报

刷新页面返回顶部

孔个个的菠萝歌

学习就是玩呀玩

show engine innodb status 输出结果解读

show engine innodb status 输出结果解读

输出示例

1.HEAD

2.BACKGROUND THREAD

3.SEMAPHORES

4.TRANSACTIONS

5.FILE I/O

6.INSERT BUFFER AND ADAPTIVE HASH INDEX

7.LOG

8.BUFFER POOL AND MEMORY / INDIVIDUAL BUFFER POOL INFO

9.ROW OPERATIONS

公告