SHOW ENGINE INNODB STATUS is a specific form of the SHOW
ENGINE statement that displays the InnoDB Monitor output,
which is extensive InnoDB information which can be useful in
diagnosing problems.
 
The following sections are displayed
Status: Shows the timestamp, monitor name and the number of
seconds, or the elapsed time between the current time and
the time the InnoDB Monitor output was last displayed. The
per-second averages are based upon this time.
BACKGROUND THREAD: srv_master_thread lines show work
performed by the main background thread.
SEMAPHORES: Threads waiting for a semaphore and stats on how
the number of times threads have needed a spin or a wait on
a mutex or rw-lock semaphore. If this number of threads is
large, there may be I/O or contention issues. Reducing the
size of the innodb_thread_concurrency system variable may
help if contention is related to thread scheduling. Spin
rounds per wait shows the number of spinlock rounds per OS
wait for a mutex. 
LATEST FOREIGN KEY ERROR: Only shown if there has been a
foreign key constraint error, it displays the failed
statement and information about the constraint and the
related tables.
LATEST DETECTED DEADLOCK: Only shown if there has been a
deadlock, it displays the transactions involved in the
deadlock and the statements being executed, held and
required locked and the transaction rolled back to.
TRANSACTIONS: The output of this section can help identify
lock contention, as well as reasons for the deadlocks.
FILE I/O: InnoDB thread information as well as pending I/O
operations and I/O performance statistics.
INSERT BUFFER AND ADAPTIVE HASH INDEX: InnoDB insert buffer
and adaptive hash index status information, including the
number of each type of operation performed, and adaptive
hash index performance.
LOG: InnoDB log information, including current log sequence
number, how far the log has been flushed to disk, the
position at which InnoDB last took a checkpoint, pending
writes and write performance statistics.
BUFFER POOL AND MEMORY: Information on buffer pool pages
read and written, which allows you to see the number of data
file I/O operations performed by your queries. See InnoDB
Buffer Pool for more. Similar information is also available
from the INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS table.
ROW OPERATIONS:Information about the main thread, including
the number and performance rate for each type of row
operation. 
 
If the innodb_status_output_locks system variable is set to
1, extended lock information will be displayed.
 
Example output:
 
=====================================
2016-09-12 04:42:15 7f226145fb00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 527
srv_idle
srv_master_thread log flush and writes: 527
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
Mutex spin waits 2, rounds 60, OS waits 2
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00
RW-excl
------------
TRANSACTIONS
------------
Trx id counter 2308
Purge done for trx's n:o < 0 undo n:o < 0 state: running
but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f226145fb00, query id
4 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
172 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
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1616829
Log flushed up to 1616829
Pages flushed up to 1616829
Last checkpoint at 1616829
Max checkpoint age 80826164
Checkpoint age target 78300347
Modified age 0
Checkpoint age 0
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 140771328; in additional pool
allocated 0
Total memory allocated by read views 88
Internal hash tables (constant factor + variable factor)
 Adaptive hash index 2217568 (2213368 + 4200)
 Page hash 139112 (buffer pool 0 only)
 Dictionary cache 630703 (554768 + 75935)
 File system 817648 (812272 + 5376)
 Lock system 333232 (332872 + 360)
 Recovery system 0 (0 + 0)
Dictionary memory allocated 75935
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 8037
Database pages 154
Old database pages 0
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 154, 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: 154, 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
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 3337, id 139784957703936, 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
Number of system 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
============================
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/show-engine-innodb-status/