MySQL is a powerful open-source relational database management system, and InnoDB is one of the most popular storage engines for MySQL. InnoDB provides a unique feature called the InnoDB Monitor, which generates reports that provide valuable insights into the performance and internal workings of the InnoDB storage engine. One such report is the SHOW ENGINE that can be used to generate the InnoDB Status Report.

In this blog post, we will delve into the various sections of the InnoDB Status Report and explain their significance. We will also use a specific report as an example to illustrate the information contained in each section.

The Purpose of the InnoDB Status Report

The InnoDB Status Report is a tool for database administrators to monitor the performance and health of the InnoDB storage engine. It provides detailed information about various aspects of InnoDB, such as transactions, semaphores, file I/O operations, buffer pool statistics, and more. This information can be used to identify potential issues, optimize database performance, and ensure the smooth operation of the database.

The Show Engine InnoDB Status Report Sections

The InnoDB Status Report is divided into several sections, each providing specific information about a particular aspect of the InnoDB storage engine. Let’s take a look at each of these sections using the following report as an example:

[Report]
=====================================
2023-05-23 13:03:50 0x7fd470e8e700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 30 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 22157 srv_active, 0 srv_shutdown, 109450 srv_idle
srv_master_thread log flush and writes: 131591
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 54425
OS WAIT ARRAY INFO: signal count 48612
RW-shared spins 42242, rounds 1161128, OS waits 38491
RW-excl spins 26544, rounds 142268, OS waits 300
RW-sx spins 7, rounds 210, OS waits 7
Spin rounds per wait: 27.49 RW-shared, 5.36 RW-excl, 30.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 4161696
Purge done for trx's n:o < 4161696 undo n:o < 0 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422025418118264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418114088, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418109912, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418080680, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418105736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418101560, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418097384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418093208, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418089032, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418084856, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418076504, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418072328, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418068152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422025418063976, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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
73893 OS file reads, 296298 OS file writes, 192935 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.33 writes/s, 1.20 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 45 merges
merged operations:
 insert 56, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.03 hash searches/s, 3.87 non-hash searches/s
---
LOG
---
Log sequence number 3909935287
Log flushed up to   3909935287
Pages flushed up to 3909935287
Last checkpoint at  3909935278
0 pending log flushes, 0 pending chkp writes
123352 log i/o's done, 0.80 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 170590208
Dictionary memory allocated 613536
Buffer pool size   8192
Free buffers       1024
Database pages     7151
Old database pages 2619
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 2988, not young 501751
0.00 youngs/s, 0.00 non-youngs/s
Pages read 73641, created 771, written 149793
0.00 reads/s, 0.00 creates/s, 0.40 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: 7151, unzip_LRU len: 0
I/O sum[21]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=834, Main thread ID=140549977794304, state: sleeping
Number of rows inserted 6761, updated 29723, deleted 212, read 10292734
0.00 inserts/s, 0.13 updates/s, 0.00 deletes/s, 0.27 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
============================

SHOW ENGINE INNODB STATUS

Obtaining the InnoDB Monitor Status Report is a straightforward process. You can generate this report by executing a specific SQL command in the MySQL client. Here are the steps:

  1. Access MySQL Client: First, you need to access the MySQL client. You can do this by opening your terminal or command prompt and typing the following command:
mysql -u yourusername -p

Replace yourusername with your MySQL username. After pressing enter, you’ll be prompted to enter your password. Once you’ve done that, you’ll be logged into the MySQL client.

  1. Use the Database: If you want to generate the InnoDB Status Report for a specific database, you need to use that database. You can do this by typing the following command:
USE yourdatabase;

Replace yourdatabase with the name of your database. If you want to generate the report for the entire MySQL instance, you can skip this step.

  1. Generate the InnoDB Status Report: Now, you can generate the InnoDB Status Report by executing the SHOW ENGINE INNODB STATUS command:
SHOW ENGINE INNODB STATUS\G

The \G at the end of the command is used to format the output in a more readable way. After executing this command, the InnoDB Status Report will be displayed in your terminal or command prompt.

Remember that you need to have the necessary privileges to execute the SHOW ENGINE INNODB STATUS command. If you’re getting a permission error, make sure that your MySQL user has the PROCESS or SUPER privilege.

INNODB Monitor Engine Status Report Sections

Background Thread

The Background Thread section provides information about the master thread’s activity. The master thread is responsible for various background tasks, such as flushing data to disk and merging insert buffers.

In our example report, we see the following:

srv_master_thread loops: 22157 srv_active, 0 srv_shutdown, 109450 srv_idle
srv_master_thread log flush and writes: 131591

This tells us that the master thread has been active 22157 times, idle 109450 times, and has not been in shutdown mode. It has also performed 131591 log flushes and writes.

Semaphores

Semaphores are synchronization primitives used to control access to shared resources. The Semaphores section provides information about the usage of semaphores in InnoDB.

In our example report, we see the following:

OS WAIT ARRAY INFO: reservation count 54425
OS WAIT ARRAY INFO: signal count 48612
RW-shared spins 42242, rounds 1161128, OS waits 38491
RW-excl spins 26544, rounds 142268, OS waits 300
RW-sx spins 7, rounds 210, OS waits 7
Spin rounds per wait: 27.49 RW-shared, 5.36 RW-excl, 30.00 RW-sx

This tells us that there have been 54425 semaphore reservations and 48612 semaphore signals. The RW-shared, RW-excl, and RW-sx values provide information about the usage of shared, exclusive, and shared-exclusive locks, respectively.

Transactions

The Transactions section provides information about the current transactions in InnoDB.

In our example report, we see the following:

Trx id counter 4161696
Purge done for trx's n:o < 4161696 undo n:o < 0 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422025418118264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)

This tells us that the current transaction ID counter is 4161696, and the purge operation has been completed for transactions with IDs less than 4161696. The history list length is 21, which indicates the number of undo logs that have not yet been purged. The list of transactions for each session

provides detailed information about each transaction, including its ID, status, and the number of locks it holds.

File I/O

The File I/O section provides information about the file input/output operations performed by InnoDB. This includes the state of the I/O threads, the number of pending reads and writes, and the number of file reads, writes, and fsyncs.

In our example report, we see the following:

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)
...
73893 OS file reads, 296298 OS file writes, 192935 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.33 writes/s, 1.20 fsyncs/s

This tells us that all the I/O threads are waiting for completed aio requests. It also provides the total number of file reads, writes, and fsyncs, as well as the average number of these operations per second.

Insert Buffer and Adaptive Hash Index

The Insert Buffer and Adaptive Hash Index section provides information about the insert buffer and the adaptive hash index, which are two important features of InnoDB that help to improve performance.

In our example report, we see the following:

Ibuf: size 1, free list len 0, seg size 2, 45 merges
merged operations:
 insert 56, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
...
0.03 hash searches/s, 3.87 non-hash searches/s

This tells us that the insert buffer size is 1, there are no free blocks in the insert buffer, and the segment size is 2. It also provides information about the number of merged and discarded operations, the size of the hash table, and the number of hash and non-hash searches per second.

Log

The Log section provides information about the InnoDB log, which is used to ensure data consistency and to recover from crashes.

In our example report, we see the following:

Log sequence number 3909935287
Log flushed up to   3909935287
Pages flushed up to 3909935287
Last checkpoint at  3909935278
0 pending log flushes, 0 pending chkp writes
123352 log i/o's done, 0.80 log i/o's/second

This tells us the current log sequence number, the point up to which the log has been flushed, the number of pages flushed, and the point of the last checkpoint. It also provides the number of pending log flushes and checkpoint writes, and the number of log I/O operations per second.

Buffer Pool and Memory

The Buffer Pool and Memory section provides information about the InnoDB buffer pool, which is a cache for data and indexes in memory.

In our example report, we see the following:

Total large memory allocated 170590208
Dictionary memory allocated 613536
Buffer pool size   8192
Free buffers       1024
Database pages     7151
Old database pages 2619
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 2988, not young 501751
0.00 youngs/s, 0.00 non-young

s/s
Pages read 73641, created 771, written 149793
0.00 reads/s, 0.00 creates/s, 0.40 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: 7151, unzip_LRU len: 0
I/O sum[21]:cur[0], unzip sum[0]:cur[0]

This section provides a wealth of information about the buffer pool, including the total memory allocated, the size of the buffer pool, the number of free buffers, the number of database pages, the number of old database pages, the number of modified database pages, the percentage of dirty pages, the maximum percentage of dirty pages, the number of pending reads and writes, the number of pages made young and not young, the buffer pool hit rate, and more.

Row Operations

The Row Operations section provides information about the row operations performed by InnoDB.

In our example report, we see the following:

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=834, Main thread ID=140549977794304, state: sleeping
Number of rows inserted 6761, updated 29723, deleted 212, read 10292734
0.00 inserts/s, 0.13 updates/s, 0.00 deletes/s, 0.27 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

This tells us the number of queries inside InnoDB and in the queue, the number of open read views, the process ID and main thread ID, the state of the main thread, and the number of rows inserted, updated, deleted, and read. It also provides the number of these operations per second.

Conclusion

The SHOW ENGINE INNODB STATUS is a InnoDB Montiro for Status Report being a powerful tool for monitoring the performance and health of the InnoDB storage engine.

By understanding the information provided in each section of the report, database administrators can identify potential issues, optimize database performance, and ensure the smooth operation of the database.