SHOW ENGINE INNODB STATUS 结果分析
0. 排查流程
- 计算后台线程活跃占比
搜索关键字 BACKGROUND THREAD
计算活跃占比: srv_active / (srv_active + srv_idle)
- < 5% 空闲
- 5% ~ 20% 正常
- 20% ~ 50% 繁忙
50% 高负载
- 锁的相关信息
搜索关键字 SEMAPHORES
- RW-excl spins/s 一般小于 10
- OS waits/s 一般小于 1
- Spin rounds per wait 一般小于 10
- 等待超过 1 秒的线程 一般是 0
- 事务相关
搜索关键字 TRANSACTIONS
判断事务 id 与 purge 线程处理的事务 id 的差值, 如果差值过大说明有问题 观察 History list length 的值,如果过大,说明有长时间未提交的事务 观察事务的状态
- 文件 I/O
搜索关键字 FILE I/O
查看 Pending 挂起的 I/O s 数量
- 插入缓冲区
搜索关键字 INSERT BUFFER AND ADAPTIVE HASH INDEX
1. BACKGROUND THREAD 后台线程
srv_master_thread loops: 180 srv_active, 0 srv_shutdown, 12258 srv_idle
srv_master_thread log flush and writes: 0
- srv_master_thread loops 主线程的循环次数
180 srv_active活跃循环次数 (每 1 秒 执行一次)0 srv_shutdown关闭循环次数12258 srv_idle空闲循环次数 (每 10 秒 执行一次)
- srv_master_thread log flush and writes: 0
- 主线程执行日志刷新和写入的次数
计算活跃占比: srv_active / (srv_active + srv_idle)
- < 5% 空闲
- 5% ~ 20% 正常
- 20% ~ 50% 繁忙
50% 高负载
2. SEMAPHORES 信号量
OS WAIT ARRAY INFO: reservation count 3535
OS WAIT ARRAY INFO: signal count 3055
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
OS WAIT ARRAY INFO操作系统等待数组- reservation count 线程进入等待数组的总次数(累计)
- signal count 线程被唤醒的总次数(累计)
- RW-shared 读写共享锁
- spins 自旋锁尝试次数
- rounds 自旋轮数
- OS waits 操作系统级别等待次数
- RW-excl 读 写排它锁
- spins 自旋锁尝试次数
- rounds 自旋轮数
- OS waits 操作系统级别等待次数
- RW-sx 读写 SX 锁
- spins 自旋锁尝试次数
- rounds 自旋轮数
- OS waits 操作系统级别等待次数
等待线程数组的信息, 目前有 3541 个线程在等待. 说明存在锁竞争
3. TRANSACTIONS 事务
Trx id counter 2863712
Purge done for trx's n:o < 2863581 undo n:o < 0 state: running but idle
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421583535199712, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535198904, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535197288, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535198096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
...
Trx id counter 2863712当前事务 id- Purge done for trx's n:o < 2863581 undo n:o < 0 state: running but idle
- Purge 线程已清理到事务 ID 2863581
- Undo 页号
- running but idle , Purge 线程运行中但空闲
History list length 2历史列表长度, 如果过大说明有长时间未提交的事务,会影响 MVCC 性能- 0-100 优秀
- 100-1000 良好
- 1000-10000 需关注
- 10000-100000 问题
- 100000+ 严重问题
- ---TRANSACTION 421583535196480, not started 状态为 未开始
4. FILE I/O 文件 I/O
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] 挂起 I/O 的数量,如果数值过大说明 I/O 压力大
5. INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 2669, seg size 2671, 24 merges
merged operations:
insert 70, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 13 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
67.09 hash searches/s, 316.22 non-hash searches/s
- 插入缓冲区使用情况
- size 当前缓冲区已使用的页数
- free list 空闲列表长度
- seg size 插入缓冲区段的总大小
- merges 自启动以来的合并次数
- 自适应哈希索引效率
6. LOG 日志
Log sequence number 5176849166
Log buffer assigned up to 5176849166
Log buffer completed up to 5176849166
Log written up to 5176849166
Log flushed up to 5176849166
Added dirty pages up to 5176849166
Pages flushed up to 5176849166
Last checkpoint at 5176849166
Log minimum file id is 1566
Log maximum file id is 1567
3895 log i/o's done, 0.00 log i/o's/second
- Log sequence number 当前日志序列号
- Last checkpoint 最后检查点, 如果与 Log sequence number 差距过大, 说明写入压力大
7. BUFFER POOL AND MEMORY 缓冲池
Total large memory allocated 0
Dictionary memory allocated 5873716
Buffer pool size 8192
Free buffers 1024
Database pages 7141
Old database pages 2616
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 189018, not young 4530445
0.09 youngs/s, 0.00 non-youngs/s
Pages read 49515, created 64903, written 82251
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: 7141, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
- Buffer pool size 缓冲池大小
- Free buffers 空闲缓冲区, 相当于总体目前只身下来了 1/8 , 需要适当调大 Buffer pool size
- Modified db pages 脏页数量(过多会影响性能)
- Buffer pool hit rate 缓存命中率
8. ROW OPERATIONS 行操作
0 queries inside InnoDB, 0 queries in queue
8 read views open inside InnoDB
Process ID=1, Main thread ID=140107998816000 , state=sleeping
Number of rows inserted 4506392, updated 26, deleted 1, read 67225896802
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 22805720.35 reads/s
Number of system rows inserted 1351, updated 1588, deleted 1042, read 183650
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
- queries inside InnoDB 当前执行的查询数
- queries in queue 队列中的查询数
- 8 read views open inside InnoDB 表示有 8 个活跃的读视图
- Number of rows inserted 4506392, updated 26, deleted 1, read 67225896802
- 表示自 mysql 启动以来, 插入了 4506392 数据, 修改了 26 数据, 删除了 1 数据, 读取了 67225896802 数据
- 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 22805720.35 reads/s
- 表示了此时的增删改查的速率, 可以看到每秒读取 22805720.35 行, 这个量级已经很高了.
- Number of system rows inserted 1351, updated 1588, deleted 1042, read 183650
- InnoDB 内部的操作
案例
=====================================
2025-12-29 13:26:54 140106715232000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 180 srv_active, 0 srv_shutdown, 12258 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3535
OS WAIT ARRAY INFO: signal count 3055
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2863712
Purge done for trx's n:o < 2863581 undo n:o < 0 state: running but idle
History list length 14
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421583535199712, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535198904, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535197288, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535198096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535193248, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535190824, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535190016, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535217488, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535214256, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535213448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535209408, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535202944, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535211024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535216680, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535215064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535208600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535206984, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535195672, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535203752, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535196480, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535194864, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535194056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535192440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535191632, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535189208, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535188400, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421583535187592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
I/O thread 1 state: waiting for completed aio requests (insert buffer 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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
52325 OS file reads, 87339 OS file writes, 5867 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 2669, seg size 2671, 24 merges
merged operations:
insert 70, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 13 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
67.09 hash searches/s, 316.22 non-hash searches/s
---
LOG
---
Log sequence number 5176849166
Log buffer assigned up to 5176849166
Log buffer completed up to 5176849166
Log written up to 5176849166
Log flushed up to 5176849166
Added dirty pages up to 5176849166
Pages flushed up to 5176849166
Last checkpoint at 5176849166
Log minimum file id is 1566
Log maximum file id is 1567
3895 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 5873716
Buffer pool size 8192
Free buffers 1024
Database pages 7141
Old database pages 2616
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 189018, not young 4530445
0.09 youngs/s, 0.00 non-youngs/s
Pages read 49515, created 64903, written 82251
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: 7141, 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
8 read views open inside InnoDB
Process ID=1, Main thread ID=140107998816000 , state=sleeping
Number of rows inserted 4506392, updated 26, deleted 1, read 67225896802
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 22805720.35 reads/s
Number of system rows inserted 1351, updated 1588, deleted 1042, read 183650
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================