SHOW ENGINE INNODB STATUS Walk

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 (old name for the change 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:

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2020-12-25 18:45:59 0x7fbcca38c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9101767 srv_active, 0 srv_shutdown, 260 srv_idle
srv_master_thread log flush and writes: 9102027
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1063339384
OS WAIT ARRAY INFO: signal count 6191383907
RW-shared spins 0, rounds 4072890199, OS waits 538799136
RW-excl spins 0, rounds 13503360885, OS waits 312417535
RW-sx spins 15790334, rounds 344341865, OS waits 5753039
Spin rounds per wait: 4072890199.00 RW-shared, 13503360885.00 RW-excl, 21.81 RW-sx

------------
TRANSACTIONS
------------
Trx id counter 4155568249
Purge done for trx's n:o < 4155568078 undo n:o < 0 state: running but idle
History list length 164
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422037917105712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917101168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917085264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917082992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917092080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917080720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917100032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917087536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917081856, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917072768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917103440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422037917137520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 4155568168, ACTIVE 1 sec
6 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 4
MySQL thread id 161879215, OS thread handle 140447562884864, query id 30007739795 10.168.0.161 flipproxy
---TRANSACTION 4155561707, ACTIVE 163 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 406
MySQL thread id 161230513, OS thread handle 140449057949440, query id 30007742058 10.168.0.161 flipproxy
--------
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] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1131927901 OS file reads, 3739511583 OS file writes, 880003797 OS fsyncs
179.19 reads/s, 16384 avg bytes/read, 462.22 writes/s, 135.99 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 50, free list len 39658, seg size 39709, 54680983 merges
merged operations:
insert 75782086, delete mark 12490431, delete 2870714
discarded operations:
insert 116, delete mark 0, delete 0
Hash table size 26559067, node heap has 1144 buffer(s)
Hash table size 26559067, node heap has 141938 buffer(s)
Hash table size 26559067, node heap has 20101 buffer(s)
Hash table size 26559067, node heap has 22409 buffer(s)
Hash table size 26559067, node heap has 42761 buffer(s)
Hash table size 26559067, node heap has 106711 buffer(s)
Hash table size 26559067, node heap has 52188 buffer(s)
Hash table size 26559067, node heap has 3739 buffer(s)
51254.15 hash searches/s, 5758.65 non-hash searches/s
---
LOG
---
Log sequence number 7675479621545
Log flushed up to 7675479621432
Pages flushed up to 7675479431134
Last checkpoint at 7675478641960
Max checkpoint age 3478212404
Checkpoint age target 3369518267
Modified age 190411
Checkpoint age 979585
0 pending log flushes, 0 pending chkp writes
383081906 log i/o's done, 51.24 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 111778201600
Dictionary memory allocated 1922540
Internal hash tables (constant factor + variable factor)
Adaptive hash index 8105810496 (1699780288 + 6406030208)
Page hash 13280296 (buffer pool 0 only)
Dictionary cache 426867612 (424945072 + 1922540)
File system 976784 (812272 + 164512)
Lock system 295864008 (295674296 + 189712)
Recovery system 0 (0 + 0)
Buffer pool size 6552800
Buffer pool size, bytes 107361075200
Free buffers 8182
Database pages 6153627
Old database pages 2271394
Modified db pages 285165
Pending reads 0
Pending writes: LRU 2, flush list 29, single page 0
Pages made young 4385757132, not young 53079509851
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1131908969, created 111395654, written 3281462446
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6153627, unzip_LRU len: 0
I/O sum[189168]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1018
Database pages 769526
Old database pages 284046
Modified db pages 34591
Pending reads 0
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 564915022, not young 6702442236
0.00 youngs/s, 0.00 non-youngs/s
Pages read 145671145, created 14080842, written 491021270
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769526, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1023
Database pages 769043
Old database pages 283865
Modified db pages 35511
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 549810021, not young 6694044420
0.00 youngs/s, 0.00 non-youngs/s
Pages read 142799390, created 13967069, written 322269790
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769043, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1024
Database pages 769145
Old database pages 283902
Modified db pages 35712
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 553638170, not young 6847287984
0.00 youngs/s, 0.00 non-youngs/s
Pages read 141774563, created 13928430, written 324058782
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769145, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1024
Database pages 769233
Old database pages 283935
Modified db pages 35884
Pending reads 0
Pending writes: LRU 0, flush list 23, single page 0
Pages made young 551588620, not young 6607163567
0.00 youngs/s, 0.00 non-youngs/s
Pages read 141633931, created 13942444, written 517155076
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769233, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1024
Database pages 769137
Old database pages 283899
Modified db pages 37797
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 0
Pages made young 526444157, not young 6516655677
0.00 youngs/s, 0.00 non-youngs/s
Pages read 137371317, created 13624029, written 541266525
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769137, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1024
Database pages 769181
Old database pages 283915
Modified db pages 35316
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 0
Pages made young 543020376, not young 6584545896
0.00 youngs/s, 0.00 non-youngs/s
Pages read 139480525, created 13953554, written 406793857
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769181, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1023
Database pages 769093
Old database pages 283884
Modified db pages 35656
Pending reads 0
Pending writes: LRU 0, flush list 2, single page 0
Pages made young 552522104, not young 6604869490
0.00 youngs/s, 0.00 non-youngs/s
Pages read 142242185, created 13935986, written 323935865
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769093, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 819100
Buffer pool size, bytes 13420134400
Free buffers 1022
Database pages 769269
Old database pages 283948
Modified db pages 34698
Pending reads 0
Pending writes: LRU 1, flush list 0, single page 0
Pages made young 543818662, not young 6522500581
0.00 youngs/s, 0.00 non-youngs/s
Pages read 140935913, created 13963300, written 354961281
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 769269, unzip_LRU len: 0
I/O sum[23646]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
2 RW transactions active inside InnoDB
---OLDEST VIEW---
Read view low limit trx n:o 4155568078
Trx read view will not see trx with id >= 4155568078, sees < 4155561707
Read view individually stored trx ids:
Read view trx id 4155561707
Read view trx id 4155567631
Read view trx id 4155568065
-----------------
Process ID=24767, Main thread ID=140449197762304, state: sleeping
Number of rows inserted 74837634891, updated 494442761, deleted 60577147, read 64714676372399
11061.76 inserts/s, 65.84 updates/s, 0.08 deletes/s, 8205091.56 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================