InnoDB存储引擎层体系结构图：
1.innodb基本信息
mysql> show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2017-01-03 15:34:17 7f916c114700 INNODB MONITOR OUTPUT --输出的时间
=====================================
Per second averages calculated from the last 38 seconds
-----------------
2.后台线程
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2323156 srv_active, 0 srv_shutdown, 2593442 srv_idle --主线程每秒loop循环的次数(2323156 激活的次数 0 停止的次数 2593442 等待的次数)
srv_master_thread log flush and writes: 4916598 --主线程日志刷新和写入的次数
----------
3.锁信息
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2882668 --全局等待数组信息(当数据组创建后预留的计数)
OS WAIT ARRAY INFO: signal count 59482367 --收到的通知次数
Mutex spin waits 57404086, rounds 70574291, OS waits 688683 --57404086表示互斥等待的个数,70574291表示循环迭带的个数,688683系统调用的等待个数
RW-shared spins 23269113, rounds 80987143, OS waits 1648604 --23269113表示共享读锁期间读写锁等待的个数,80987143表示循环迭带的个数,1648604系统调用的等待个数
RW-excl spins 3761579, rounds 143069955, OS waits 524639 --3761579表示排它写锁期间读写锁等待的个数,80987143表示循环迭带的个数,1648604系统调用的等待个数
Spin rounds per wait: 1.23 mutex, 3.48 RW-shared, 38.03 RW-excl
------------------------
4.事物信息
TRANSACTIONS
------------
Trx id counter 5381 --下一个事物号
Purge done for trx's n:o < 4553 undo n:o < 0 state: running but idle --所有编号小于4553的事物都已经从历史记录列表中清除了
History list length 75 --历史列表的长度
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2, OS thread handle 0x7f69957c9700, query id 3 localhost root init
show engine innodb status
--------
5.文件IO
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) --4个异步读线程
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) --8个异步写入线程
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)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] , --每个读写线程状态(4个读8个写)
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 --插入缓冲线程的状态,每秒日志IO状态,IO同步的状态
Pending flushes (fsync) log: 0; buffer pool: 0 --文件同步的日志状态,缓冲池的个数
72539 OS file reads, 27925279 OS file writes, 21542873 OS fsyncs --读了多少个文件,系统写了多少个文件,系统同步了多少个文件
0.00 reads/s, 0 avg bytes/read, 7.50 writes/s, 5.50 fsyncs/s --每秒读的信息,平均每秒读的字节数,每秒写入的信息,每秒同步的信息
-------------------------------------
6.插入缓冲和自适应哈稀索引
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 488, seg size 490, 3595 merges --1表示缓冲索引树的当前大小,488空闲列表的长度,490文件段中已分配段的个数,3595合并页的个数
merged operations:
insert 3553, delete mark 168, delete 0 --insert插入缓冲的次数,delete mark标记为已删除的次数,delete表示purge的次数(删除)
discarded operations:
insert 0, delete mark 0, delete 0 --有多少个insert buffer被丢弃,有多少个insert buffer被标记为已删除,purge多少个insert buffer等
Hash table size 9461329, node heap has 8274 buffer(s) --自适应哈稀索引单元格的数量与预留缓冲结构的数量
21976.71 hash searches/s, 1793.98 non-hash searches/s --使用哈稀索引的数量与不能使用哈稀索引时向下搜索B树索引的次数
---
7.重做日志信息
LOG
---
Log sequence number 24627115613 --当前重做日志序列号
Log flushed up to 24627115603 --刷新到重做日志文件的序列号
Pages flushed up to 24627105147 --刷新到磁盘的日志序列号
Last checkpoint at 24627105147 --下一个日志序列号
0 pending log writes, 0 pending chkp writes --挂起日志写入数,挂起检查点写入数
8868627 log i/o's done, 2.63 log i/o's/second --innodb启动后的io个数,最近一次显示之后的每秒io操作个数
----------------------
8.缓存池和内存
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4395565056; in additional pool allocated 0 --分配内存总数和额外的内存池
Dictionary memory allocated 961282 --数据字典占用的字节数
Buffer pool size 262134 --缓冲池的个数
Free buffers 142352 --剩余缓冲区的个数
Database pages 111508 --LRU中数据页的个数
Old database pages 41039 --LRU中旧数据页的个数
Modified db pages 37 --LRU中已修改的数据页个数
Pending reads 0 --挂起读操作的个数
Pending writes: LRU 0, flush list 0, single page 0 --通过使用LRU算法等待刷新的页数,在BUF_FLUSH_LIST列表等待刷新的页数,在BUF_FLUSH_SINGLE_PAGE等待刷新的页数
Pages made young 504766, not young 550 --504766第一次访问变成新页面的次数,550没有变成新页面的次数
0.05 youngs/s, 0.00 non-youngs/s --LRU中每秒变成新页面的速率,没有变成新页面的速率
Pages read 71261, created 40267, written 14051008 --71261读操作的页面个数,40267在缓冲池中创建了没有读取的页面个数,14051008写操作的页面个数
0.00 reads/s, 0.00 creates/s, 3.82 writes/s --LRU中每秒读取数据的页速率,每秒创建数据的页速率,每秒写入数据的页速率
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: 111508, unzip_LRU len: 0 --LRU列表长度,unzip_LRU列表长度
I/O sum[978]:cur[0], unzip sum[0]:cur[0] --LRU在LRU列表中I/O操作的次数,unzip在LRU列表中I/O操作的次数
----------------------
9.行操作
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue --有多少个正在查询操作个数
0 read views open inside InnoDB --显示只读视图的数量
Main thread process no. 9822, id 140262664857344, state: sleeping --显示主线程的ID及其状态
Number of rows inserted 2044551, updated 2917695, deleted 411325, read 319298239254 --从innodb存储引擎启动后插入,更新,删除,查询的行数
0.45 inserts/s, 0.79 updates/s, 0.11 deletes/s, 136529.62 reads/s --最近一次显示增,删,改,查的速率
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
mysql>