A.2 SHOW INNODB STATUS

As noted in Chapter 1, the SHOW INNODB STATUS command produces detailed statistics about what's going on inside the InnoDB storage engine (far more detailed than anything in MyISAM). A detailed understanding of all the statistics InnoDB provides is beyond the scope of what most database administrators will ever need. Much of the information InnoDB presents is useful only in rare and very specific diagnostic activities, so we'll keep the discussion fairly basic here and focus on the more commonly used values.

Sample output from SHOW INNODB STATUS command is included at the end of this section. The output is broken up into several labeled groups. For most day to day use, the most informative sections are Transactions, Buffer Pool and Memory, and Row Operations.

Semaphores

This section details the various locks used inside InnoDB. Higher values here generally indicate a busy server with frequent contention inside InnoDB. They are cumulative statistics, however, so the longer your server has been up, the higher you can expect them to be.

Transactions

Each of the active or pending transactions is listed in this section. For each, InnoDB lists the MySQL thread ID as well as the IP address and MySQL username responsible for initiating the transaction. You may see indications of transactions waiting on locks here. If so, there's a good chance your application is encountering deadlocks.

File I/O

Here InnoDB lists the state of each file I/O thread and provides counts of other I/O-related activity.

Insert Buffer and Adaptive Hash Index

When records are added to InnoDB, they are first put into the insert buffer. From there InnoDB merges records into the tablespace. This section provides a few metrics generated during those operations.

Log

The transaction log statistics are presented here, including the current sequence number and the highest sequence numbers from the most recent log flush and checkpoint operations. InnoDB also provides average values for the number of log-related I/O operations per second.

Buffer Pool and Memory

This section tells you how well InnoDB is using the memory you've given it via the innodb_buffer_pool setting. The "buffer pool size" and "free buffers" values give you an idea of how much of that memory is in use. InnoDB also provides read/create/write-per-second statistics that indicate how quickly the database pages are changing.

Row Operations

Here you'll find some very useful high-level numbers that track the frequency of INSERTs, UPDATEs, DELETEs, and SELECTs as well as counting the number of rows affected by each.

Here's some sample output from a SHOW INNODB STATUS command:

mysql> SHOW INNODB STATUS \G
*************************** 1. row ***************************
Status:
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
= = = = = = =
031218  8:29:53 INNODB MONITOR OUTPUT
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 
= = = = = = =
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 6, OS waits 3; RW-excl spins 2, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 1039616
Purge done for trx's n:o < 0 454662 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 49162
MySQL thread id 16, query id 112 216.145.52.107 jzawodn
show innodb status
--------
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
155 OS file reads, 4 OS file writes, 4 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 for space 0: size 1, free list len 314, seg size 316,
0 inserts, 0 merged recs, 0 merges
Hash table size 138401, used cells 0, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 900654168
Log flushed up to   0 900654168
Last checkpoint at  0 900654168
0 pending log writes, 0 pending chkp writes
9 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 54384729; in additional pool allocated 1167488
Buffer pool size   2048
Free buffers       1983
Database pages     65
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 65, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 14344, state: waiting for server activity
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.09 sec)