show engine innodb status参数解读分析

最新推荐文章于 2024-04-27 10:45:58 发布
守望新华街
最新推荐文章于 2024-04-27 10:45:58 发布
阅读量556
点赞数
分类专栏： MySQL 文章标签： MySQL MySQL参数 innodb
本文链接：https://blog.csdn.net/weixin_41419379/article/details/103521943
版权
MySQL 专栏收录该内容
4 篇文章 0 订阅
订阅专栏
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-12-12 16:28:22 0x7f4376417700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 76355 srv_active, 0 srv_shutdown, 1224010 srv_idle
srv_master_thread log flush and writes: 1300274
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 84169
OS WAIT ARRAY INFO: signal count 89686
RW-shared spins 0, rounds 163251, OS waits 76444
RW-excl spins 0, rounds 169954, OS waits 3206
RW-sx spins 5500, rounds 104416, OS waits 1866
Spin rounds per wait: 163251.00 RW-shared, 169954.00 RW-excl, 18.98 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 277586568
History list length 16
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421438799918016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------                #后台IO线程，innodb后台线程分为4种，分别为Master Thread（后台核心线程，负责缓冲池数据同步到磁盘等核心任务），IO Thread（负责IO请求回调处理，使用innodb_read_io_threads和innodb_write_io_threads设置），
FILE I/O                #Purge Thread（回收事务提交后记录undolog使用过的undo数据页，使用innodb_purge_threads设置）和Page Clean Thread（脏页数据刷新）。                                                                                                 
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)       #insert buffer thread用来合并插入缓冲            
I/O thread 1 state: waiting for completed aio requests (log thread)                             #log thread同步事务日志
I/O thread 2 state: waiting for completed aio requests (read thread)                      #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)                 #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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,            #read thread和write thread挂起操作的数
 ibuf aio reads:, log i/o's:, sync i/o's:                                            #insert buffer thread挂起的fsync()操作数等
Pending flushes (fsync) log: 0; buffer pool: 0                                       #log thread挂起的操作数等
1226812 OS file reads, 1064429 OS file writes, 609327 OS fsyncs                #read thread和write thread操作执行数
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 93738, seg size 93740, 0 merges  //size表示已经合并的记录页数，seg size显示当前insert buffer的大小93740*16k，free list为当前空闲链表长度
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 9460543, node heap has 20245 buffer(s)
Hash table size 9460543, node heap has 0 buffer(s)
Hash table size 9460543, node heap has 0 buffer(s)
Hash table size 9460543, node heap has 0 buffer(s)
Hash table size 9460543, node heap has 832 buffer(s)
Hash table size 9460543, node heap has 9899 buffer(s)
Hash table size 9460543, node heap has 1 buffer(s)
Hash table size 9460543, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1118874928367
Log flushed up to   1118874928367
Pages flushed up to 1118874928367
Last checkpoint at  1118874928358
0 pending log flushes, 0 pending chkp writes
231387 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35181821952                                            #innodb分配的总内存，其中有非常小部分是额外内存池分配
Dictionary memory allocated 395682                                                        
Buffer pool size   2096896                                                                      #缓存池大小，以页为单位2096896*16K约32G
Free buffers       654592                                                                       #缓存池空闲大小，以页为单位
Database pages     1411325                                                                      #LRU链表大小，以页为单位，包含young区（热区）和old区（冷区）
Old database pages 521133                                                                       #LRU链表old区大小，以页为单位
Modified db pages  0                                                                            #脏数据页数
Pending reads      0                                                                            #挂起数据页数
Pending writes: LRU 0, flush list 0, single page 0                                  #挂起写数据页数
Pages made young 47, not young 0                                                          #前面是数据从old区移动到young区头部的次数，后面是因为没达到innodb_old_blocks_time的时间限制所以没有从old区移动到young区头部的次数
0.00 youngs/s, 0.00 non-youngs/s                                                          #表示每秒young和non-youngs这两类操作的次数
Pages read 1219252, created 192073, written 616536                                  #innodb的读取页（从磁盘读到缓冲池的数据），创建页（在缓冲池中分配但没有从数据文件中读取内容的页），写入页（或者从缓冲池写到磁盘中的数据）
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: 1411325, unzip_LRU len: 0                                                        #LRU链表数据总页数和压缩的数据页总页数（为0表示没有使用压缩页）
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0                                                                                #此处开始显示每个缓存池的具体信息，可通过innodb_buffer_pool_instances查看缓存池个数
Buffer pool size   262112
Free buffers       81044
Database pages     177196
Old database pages 65430
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 153010, created 24186, written 128435
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: 177196, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   262112
Free buffers       83279
Database pages     174960
Old database pages 64604
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 150664, created 24296, written 42182
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: 174960, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   262112
Free buffers       84503
Database pages     173737
Old database pages 64153
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 149834, created 23903, written 200088
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: 173737, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   262112
Free buffers       80063
Database pages     178176
Old database pages 65792
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 154117, created 24059, written 51589
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: 178176, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   262112
Free buffers       78649
Database pages     179592
Old database pages 66314
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 155093, created 24499, written 46780
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: 179592, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   262112
Free buffers       81791
Database pages     176450
Old database pages 65154
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 152631, created 23819, written 64964
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: 176450, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   262112
Free buffers       82466
Database pages     175772
Old database pages 64904
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 152233, created 23539, written 40319
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: 175772, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   262112
Free buffers       82797
Database pages     175442
Old database pages 64782
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 6, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 151670, created 23772, written 42179
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: 175442, 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=11177, Main thread ID=139927904962304, state: sleeping
Number of rows inserted 4785426, updated 1727156, deleted 31277, read 215364889
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================