InnoDB Status

Description

The SHOW ENGINE INNODB STATUS command provides a text output of various internal Innodb Storage Engine Information.
A number of SHOW GLOBAL STATUS variables also present the same details.

Syntax

MySQL/MariaDB 

mysql> SHOW ENGINE INNODB STATUS;

Drizzle 

drizzle> SHOW ENGINE INNODB STATUS;

Notes

Example 

*************************** 1. row ***************************
Status:
=====================================
090127  7:43:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3379694, signal count 2214699
Mutex spin waits 0, rounds 102776302, OS waits 1958549
RW-shared spins 2071148, OS waits 908249; RW-excl spins 923899, OS waits 355163
------------
TRANSACTIONS
------------
Trx id counter 0 524274738
Purge done for trx's n:o < 0 524274382 undo n:o < 0 0
History list length 40
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13933, OS thread id 1167894864
MySQL thread id 2766, query id 12381459 localhost ronald
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 13933, OS thread id 1167092048 updating table statistics
MySQL thread id 2762, query id 12381436 localhost ronald
SHOW TABLE STATUS
---TRANSACTION 0 524274715, not started, process no 13933, OS thread id 1169099088
MySQL thread id 2751, query id 12381377 10.250.15.66 root
...
---TRANSACTION 0 524266196, not started, process no 13933, OS thread id 1171507536
MySQL thread id 73, query id 12344409 10.251.202.149 root
---TRANSACTION 0 524273285, not started, process no 13933, OS thread id 1170303312
MySQL thread id 67, query id 12375918 10.251.70.6 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
10382659 OS file reads, 370379 OS file writes, 150342 OS fsyncs
1 pending preads, 0 pending pwrites
6.07 reads/s, 18357 avg bytes/read, 11.19 writes/s, 3.83 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 93, free list len 59, seg size 153,
277134 inserts, 266884 merged recs, 66902 merges
Hash table size 3112859, used cells 2280051, node heap has 6053 buffer(s)
44.71 hash searches/s, 73.32 non-hash searches/s
---
LOG
---
Log sequence number 67 2251381164
Log flushed up to   67 2251381164
Last checkpoint at  67 2226642648
0 pending log writes, 0 pending chkp writes
139404 log i/o's done, 3.66 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1804864688; in additional pool allocated 16777216
Buffer pool size   96000
Free buffers       0
Database pages     89947
Modified db pages  3707
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages read 14062341, created 67343, written 314727
6.80 reads/s, 10.05 creates/s, 8.22 writes/s
Buffer pool hit rate 987 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 13933, id 1157658960, state: sleeping
Number of rows inserted 257337, updated 37306, deleted 11865, read 1357520394
2.15 inserts/s, 3.17 updates/s, 0.00 deletes/s, 47.95 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================