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 ============================