MySQL:解析SHOW ENGINE INNODB STATUS输出
MySQL:解析SHOW ENGINE INNODB STATUS输出
运维开发王义杰
发布于 2023-10-23 20:22:05
发布于 2023-10-23 20:22:05
文章被收录于专栏：运维开发王义杰
MySQL的SHOW ENGINE INNODB STATUS命令是一个强大的工具，它提供了InnoDB存储引擎的内部运行状态和性能信息。下面，我们将通过分析SHOW ENGINE INNODB STATUS的输出来理解InnoDB的各种关键属性和值的意义。
1. 背景线程
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 465 srv_idle
srv_master_thread log flush and writes: 0
srv_master_thread loops：主服务线程的循环状态，包括活动、关闭和空闲的循环次数。srv_master_thread log flush and writes：主服务线程完成的日志刷新和写操作的次数。
2. 信号量
----------
SEMAPHORES
----------
...
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
信号量是用于控制多个线程访问共享资源的同步原语。在这里，我们可以看到读写共享、读写排他和读写意向排他信号量的等待和旋转情况。
3. 事务
------------
TRANSACTIONS
------------
Trx id counter 1294
...
LIST OF TRANSACTIONS FOR EACH SESSION:
...
Trx id counter：下一个事务将获得的事务ID。LIST OF TRANSACTIONS FOR EACH SESSION：列出每个会话的事务详细信息，包括事务ID、状态、锁信息等。
4. 文件I/O
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
...
Pending flushes (fsync) log: 0; buffer pool: 0
...文件I/O部分显示了I/O线程的状态、待处理的I/O请求、已完成的文件读写和刷新操作等信息。
5. 插入缓冲区和自适应哈希索引
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
...
0.00 hash searches/s, 0.00 non-hash searches/s
这部分提供了插入缓冲区和自适应哈希索引的状态和性能统计，包括哈希搜索和非哈希搜索的速率。
6. 日志
---
LOG
---
Log sequence number 19430270
...
114 log i/o's done, 0.00 log i/o's/second
日志部分展示了InnoDB日志系统的状态，包括日志序列号、日志缓冲区分配、完成、写入、刷新的状态以及日志I/O操作的数量等。
7. 缓冲池和内存
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 520939
Buffer pool size 8192
...
No buffer pool page gets since the last printout
...
Total large memory allocated：已分配的大内存总量。Dictionary memory allocated：已分配给字典的内存量。Buffer pool size：缓冲池的大小，单位是页（每页通常为16KB）。Database pages、Old database pages和Modified db pages：数据库页、旧数据库页和已修改的数据库页的数量。Pages read、Pages written和Pages created：读取、写入和创建的页的数量。
8. 行操作
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
...
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s- 行操作部分展示了在InnoDB内部和队列中的查询数量，以及每秒插入、更新、删除和读取的行数。
9. 总结
通过分析SHOW ENGINE INNODB STATUS命令的输出，我们可以获得InnoDB存储引擎的许多内部运行状态和性能信息。这些信息对于理解InnoDB的运行机制和优化数据库性能来说非常重要。在面对数据库性能问题时，这个命令是一个非常有用的诊断工具。通过定期检查此命令的输出，并与MySQL的官方文档和社区资源一起使用，我们可以更好地理解和优化InnoDB的性能。
这篇文章仅仅触及了SHOW ENGINE INNODB STATUS命令输出中的一些基本信息，实际上，每个部分都包含了大量的详细信息，需要数据库管理员深入理解和分析，以便在日常运维和优化中做出正确的决策。
| InnoDB | |
=====================================
2023-10-17 22:15:21 140151032350464 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 465 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
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 1294
Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421626187074776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421626187073968, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421626187073160, 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
869 OS file reads, 415 OS file writes, 157 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 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 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 0 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)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 19430270
Log buffer assigned up to 19430270
Log buffer completed up to 19430270
Log written up to 19430270
Log flushed up to 19430270
Added dirty pages up to 19430270
Pages flushed up to 19430270
Last checkpoint at 19430270
Log minimum file id is 5
Log maximum file id is 5
114 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 520939
Buffer pool size 8192
Free buffers 7179
Database pages 1003
Old database pages 350
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 132, not young 3289
0.00 youngs/s, 0.00 non-youngs/s
Pages read 847, created 160, written 245
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: 1003, 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=9426, Main thread ID=140150710548224 , state=sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 442, updated 339, deleted 168, read 5468
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2023-10-17，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
