show engine innodb status\G
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
131226 17:12:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds ----过去55秒数据库的状态
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 372007 1_second, 372007 sleeps, 36255 10_second, 10646 background, 10646 flush ----可反映数据库的压力情况
srv_master_thread log flush and writes: 373344
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9135562, signal count 21215755
Mutex spin waits 520177934, rounds 2648194228, OS waits 5360746
RW-shared spins 33278462, rounds 135599856, OS waits 1460221
RW-excl spins 1200397, rounds 36578622, OS waits 370830
Spin rounds per wait: 5.09 mutex, 4.07 RW-shared, 30.47 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 2193C7F93
Purge done for trx's n:o < 2193C7F82 undo n:o < 0
History list length 1640
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 998695, query id 2141628314 127.0.0.1 root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread) ----可以看到10个IO线程，分别是insert buffer thread、
I/O thread 1 state: waiting for i/o request (log thread) log thread、read thread、write thread
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
47691 OS file reads, 29911059 OS file writes, 23777623 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 4782, seg size 4784, 14474 merges ----seg size 4784表示当前插入缓冲的大小为4784*16K=75MB free list len 表示空闲列表的长度
merged operations: size表示已经合并记录页的数量
insert 11381, delete mark 784750, delete 14238 insert表示插入的记录数
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 42499631, node heap has 20599 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 19152346341 --表示当前的LSN
Log flushed up to 19152346341 --表示刷新到重做日志文件的LSN
Last checkpoint at 19152346341 --表示刷新到磁盘的LSN
0 pending log writes, 0 pending chkp writes
23272508 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY ----这下面的信息可以看到InnoDB存储引擎缓冲池的使用情况
----------------------
Total memory allocated 21978152960; in additional pool allocated 0
Dictionary memory allocated 532520
Buffer pool size 1310719 ----表示一共有1310719个缓冲帧，每个buffer frame为16K，即：1310719*16K/1024=20G
Free buffers 1070650 ----表示剩余空间的缓冲帧
Database pages 219470 ----表示已经使用的缓冲帧
Old database pages 80995
Modified db pages 0 ----表示脏页的数量
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 40256, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 48595, created 170875, written 10931522
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
LRU len: 219470, 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
1 read views open inside InnoDB
Main thread process no. 9823, id 140584323032832, state: waiting for server activity
Number of rows inserted 7012357, updated 9253488, deleted 2913883, read 214512661409
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
