InnoDB Storage Engine Status parameters

Mysql> Show engine InnoDB status \g
1. Row ***************************
Type:innodb
Name:
Status:
=====================================
2015-04-17 10:37:02 7f1fc1fe9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last seconds
-----------------
BACKGROUND THREAD
-----------------
Srv_master_thread loops:0 srv_active, 0 srv_shutdown, 562 srv_idle
Srv_master_thread Log Flush and writes:562
----------
Semaphores
----------
OS WAIT ARRAY info:reservation count 2
OS WAIT ARRAY info:signal count 2
Mutex Spin waits 0, rounds 0, OS waits 0
Rw-shared spins 2, rounds, OS waits 2
Rw-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait:0.00 mutex, 30.00 rw-shared, 0.00 RW-EXCL
------------
Transactions
------------
Trx ID Counter 3842
Purge done for Trx's N:o < 2856 undo N:o < 0 state:running but idle
History list Length 6
LIST of transactions for each SESSION:
---TRANSACTION 0, not started
MySQL thread ID 2, OS thread handle 0x7f1fc1fe9700, query ID 5 localhost root init
Show Engine InnoDB Status
--------
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)
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)
I/O thread 9 state:waiting for completed AIO requests (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
318 OS file reads, 5 OS file writes, 5 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 276671, node heap has 0 buffer (s)
0.00 Hash SEARCHES/S, 0.00 Non-hash SEARCHES/S
---
LOG
---
Log Sequence Number 1649686
Log flushed up to 1649686
Pages flushed up to 1649686
Last checkpoint at 1649686
0 Pending Log writes, 0 pending CHKP writes
8 Log I/O ' s done, 0.00 log I/O ' S/second
----------------------
BUFFER POOL and MEMORY
----------------------
Total memory Allocated 137363456; In additional pool allocated 0
Dictionary Memory Allocated 50138
Buffer Pool Size 8191
Free Buffers 8025
Database pages 166
Old database pages 0
Modified db pages 0
Pending reads 0
Pending WRITES:LRU 0, flush list 0, single page 0
Pages made young 0, isn't young 0
0.00 YOUNGS/S, 0.00 NON-YOUNGS/S
Pages Read 166, created 0, written 1
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:166, 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
Main thread Process No. 27472, id 139774264338176, 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
----------------------------
END of INNODB MONITOR OUTPUT
============================

1 row in Set (0.00 sec)

Mysql>

Detailed parameters:

First paragraph:

1. Row ***************************
Type:innodb--Storage engine
Name:
Status:
=====================================
2015-04-17 10:37:02 7f1fc1fe9700 INNODB Monitor output-time to monitor information output
=====================================
Per second averages calculated from the last seconds
-----------------

Second paragraph:

BACKGROUND THREAD
-----------------
Srv_master_thread loops:0 srv_active, 0 srv_shutdown, 562 srv_idle
Srv_master_thread Log Flush and writes:562
----------

Third paragraph:

Semaphores
----------
OS WAIT ARRAY info:reservation count 2
OS WAIT ARRAY info:signal count 2
Mutex Spin waits 0, rounds 0, OS waits 0
Rw-shared spins 2, rounds, OS waits 2
Rw-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait:0.00 mutex, 30.00 rw-shared, 0.00 RW-EXCL
------------

Fourth paragraph:

Transactions
------------
Trx ID Counter 3842
Purge done for Trx's N:o < 2856 undo N:o < 0 state:running but idle
History list length 6--record the number of undo logs
LIST of transactions for each SESSION:
---TRANSACTION 0, not started
MySQL thread ID 2, OS thread handle 0x7f1fc1fe9700, query ID 5 localhost root init
Show Engine InnoDB Status
--------

Fifth paragraph:

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)
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)
I/O thread 9 state:waiting for completed AIO requests (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
318 OS file reads, 5 OS file writes, 5 OS Fsyncs
0.00 READS/S, 0 avg bytes/read, 0.00 WRITES/S, 0.00 FSYNCS/S
-------------------------------------

Buffer thread: Inserting buffered threads

Log Thread: Journal threads

Read thread: Reading threads

Write Thread: Write threads

Sixth paragraph:

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 276671, node heap has 0 buffer (s)
0.00 Hash SEARCHES/S, 0.00 Non-hash SEARCHES/S
---

SEG size 2:insert buffer, 2x16kb

REE list len 0: Length of free lists

Ibuf:size 1: Number of merged record pages

Insert 0:insert BUFFER

Delete Mark 0:delete BUFFER

Delete 0:purge BUFFER

Discarded Operations:change buffer When the merge occurs, the table has been deleted, eliminating the need to merge records into the secondary index.

Seventh paragraph:

LOG
---
Log Sequence Number 1649686--current LSN
Log flushed up to 1649686--the LSN number after refresh
Pages flushed up to 1649686--refresh and the LSN number after the thing is committed
Last checkpoint at 1649686--LSN number after writing to disk
0 Pending Log writes, 0 pending CHKP writes
8 Log I/O ' s done, 0.00 log I/O ' S/second
----------------------

Eighth paragraph:

BUFFER POOL and MEMORY
----------------------
Total memory Allocated 137363456; In additional pool allocated 0
Dictionary Memory Allocated 50138
Buffer pool Size 8191--sizing of buffer pools
Free buffers 8025--remaining size
Database pages 166--Databases page
Old database pages 0--Legacy Databases page
Modified db pages 0
Pending reads 0
Pending WRITES:LRU 0, flush list 0, single page 0
Pages made young 0, isn't young 0
0.00 YOUNGS/S, 0.00 NON-YOUNGS/S
Pages Read 166, created 0, written 1
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:166, Unzip_lru len:0--lru (least recently used buffer pool), 166 pages in total, 0 pages in unzip list
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------

Nineth paragraph:

ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 Read views open inside InnoDB
Main thread Process No. 27472, id 139774264338176, 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
----------------------------
END of INNODB MONITOR OUTPUT
============================

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1633777

InnoDB Storage Engine Status parameters