MySQL View storage engine status show engine InnoDB status detailed

Known as one of the most popular open source database, MySQL is widely used in various scenarios, Alibaba Cloud provides high available ApsaraDB RDS for MySQL with enhanced MySQL service reduced enterprise’s database expenses, and helped enterprises utilize technology to fight against novel coronavirus.

First, let's look at the basics of the SHOW INNODB STATUS output, which prints a lot of counters, statistics, transactional information about INNODB internal performance. In MySQL 5, the performance statistics of InnoDB are also shown in the show STATUS results. Most of the other information is the same as SHOW INNODB STATUS, which is not available in the previous version.

Many of the stats in SHOW INNODB STATUS are updated every second, so it's best to count the results over time if you're going to take advantage of these stats. InnoDB first outputs the following information:

1.=====================================
2.060717  3:07:56 INNODB MONITOR OUTPUT
3.=====================================
4.Per second averages calculated from the last seconds

First make sure that this is a sample data that is at least 20-30 seconds in statistics. If the average statistic interval is 0 or 1 seconds, then the result is meaningless.
Honestly, I don't like the average value provided by INNODB, because it's hard to get a reasonable average interval statistic, and if you're writing a script to get the SHOW INNODB STATUS results, it's best to get a global statistic and then get the average. Of course, it is also useful to view the output information directly.

The next section shows information about the signal (semaphores):

1.----------
2.SEMAPHORES
3.----------
4.OS WAIT ARRAY info:reservation count 13569, signal count 11421
5.--thread 1152170336 have waited at./... /include/buf0buf.ic Line 630 for 0.00 seconds the semaphore:
6.Mutex at 0x2a957858b8 created file BUF0BUF.C line 517, lock var 0
7.waiters Flag 0
8.wait is ending
9.--thread 1147709792 have waited at./... /include/buf0buf.ic Line 630 for 0.00 seconds the semaphore:
10.Mutex at 0x2a957858b8 created file BUF0BUF.C line 517, lock var 0
11.waiters Flag 0
12.wait is ending
13.Mutex spin waits 5672442, Rounds 3899888, OS waits 4719
14.rw-shared spins 5920, OS waits 2918; Rw-excl spins 3463, OS waits 3163

This paragraph can be divided into 2 parts. Part of this is the current wait, which only contains all the records in a highly concurrent environment, so InnoDB often fall back to the system waiting. If the wait is resolved by a spin lock, then this information will not be displayed.

With this piece of information, you'll know where the hot spots are in the system load. But this needs to know the source-related knowledge-from the above information can be seen in which source file which line (different version results may be different), but from here do not see any information. Still, you can guess something from the file name-in this case, the filename "buf0buf.ic" is indicative of a relationship with some buffer pool contention. If you want to know more, see the source.

There are some more details about the wait. "Lock Var" represents the value of the current mutex object (locked = 1/release = 0), and "Waiters flag" indicates the current number of waits. Also, in this example, you can see the wait state information "Wait is ending", which means that the mutex has been freed, but the system dispatch thread is still processing.

The second block is event statistics-"Reservation count" and "signal count" show how often the InnoDB uses an internal synchronization array for activity-time slice (slot) allocations and how frequently the thread signal uses the synchronization array. These statistics can be used to indicate how often innodb fall back to the system wait. There is also direct information about the system wait, you can see the "OS Waits" Mutex (mutexes), as well as the read-write lock. mutexes and shared locks are displayed in this information. System wait and "hold (reservation)" is not exactly the same, before fallback to the complex wait mode with Sync_array, InnoDB will try to "output" to the system, I hope the next scheduled time object in the named thread has been released. System waits are relatively slow and may be problematic if tens of thousands of system waits per second occur. Another way to observe is to view the context Exchange frequency in the system state.

Another important piece of information is the number of "Spin Waits" and "Spin rounds". The spin lock is a low-cost wait compared to the system wait; but it is an active wait that wastes some CPU resources. So if you see a lot of spin waits and spin rotations, it's obvious that it wastes a lot of CPU resources. Wasting CPU time and unnecessary context switching can be balanced with innodb_sync_spin_loops.

The following section shows the deadlock condition:

1.------------------------
2.LATEST detected DEADLOCK
3.------------------------
4.060717 4:16:48
5.*** (1) TRANSACTION:
6.TRANSACTION 0 42313619, ACTIVE sec, Process no 10099, OS thread ID 3771312 starting index read
7.mysql tables in use 1, locked 1
8.LOCK WAIT 3 LOCK struct (s), Heap size 320
9.MySQL thread ID 30898, query id 100626 localhost root Updating
10.update iz set pad= ' a ' where i=2
11.*** (1) Waiting for this LOCK to be granted:
12.RECORD LOCKS Space ID 0 page no 16403 n bits index ' PRIMARY ' of table ' Test/iz ' Trx ID 0 42313619 lock_mode X LOCKS Rec but not gap waiting
13.Record Lock, Heap No 5 physical record:n_fields 4; Compact format; Info bits 0
0:len 4; Hex 80000002; ASC;; 1:len 6; Hex 00000285a78f; ASC;; 2:len 7; Hex 00000040150110; ASC @;; 3:len 10; Hex 61202020202020202020; ASC A;;
15.
16.*** (2) TRANSACTION:
17.TRANSACTION 0 42313620, ACTIVE sec, Process no 10099, OS thread ID 4078512 starting index read, thread declared Insi De InnoDB 500
18.mysql tables in use 1, locked 1
19.3 lock struct (s), Heap size 320
20.MySQL thread ID 30899, query id 100627 localhost root Updating
21.update iz set pad= ' a ' where i=1
22.*** (2) holds the LOCK (S):
23.RECORD LOCKS Space ID 0 page no 16403 n bits index ' PRIMARY ' of table ' Test/iz ' Trx ID 0 42313620 lock_mode X LOCKS Rec but not gap
24.Record Lock, Heap No 5 physical record:n_fields 4; Compact format; Info bits 0
0:len 4; Hex 80000002; ASC;; 1:len 6; Hex 00000285a78f; ASC;; 2:len 7; Hex 00000040150110; ASC @;; 3:len 10; Hex 61202020202020202020; ASC A;;
26.
27.*** (2) waiting for this LOCK to be granted:
28.RECORD LOCKS Space ID 0 page no 16403 n bits index ' PRIMARY ' of table ' Test/iz ' Trx ID 0 42313620 lock_mode X LOCKS Rec but not gap waiting
29.Record Lock, Heap No 4 physical record:n_fields 4; Compact format; Info bits 0
0:len 4; Hex 80000001; ASC;; 1:len 6; Hex 00000285a78e; ASC;; 2:len 7; Hex 000000003411d9; ASC 4;; 3:len 10; Hex 61202020202020202020; ASC A;;
31.
32.*** WE Roll Back TRANSACTION (2)

This shows that Innodb last detected a deadlock caused by a transaction, including the state at the time of the deadlock, what locks were added, what locks are waiting to be released, and INNODB determines which transaction will be rolled back. Note that the InnoDB only shows simple information about the lock that the transaction holds. And only the last statement executed by each transaction is displayed, and the record of the deadlock occurs because of these statements. Viewing the complex deadlock information also requires viewing the log file to find the statement that actually caused the conflict. In most cases, show INNODB STATUS shows enough information.

The following is information about the deadlock caused by a foreign KEY constraint:

1.------------------------
2.LATEST FOREIGN KEY ERROR
3.------------------------
4.060717 4:29:00 Transaction:
5.TRANSACTION 0 336342767, ACTIVE 0 sec, Process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500
6.mysql tables in use 1, locked 1
7.3 Lock struct (s), heap size 368, undo Log Entries 1
8.MySQL thread ID 9697561, query ID 188161264 localhost root update
9.insert into child values (2,2)
10.Foreign key constraint fails for table ' Test/child ':
11.,
CONSTRAINT ' Child_ibfk_1 ' FOREIGN KEY (' parent_id ') REFERENCES ' parent ' (' id ') on DELETE CASCADE
13.Trying to add in child table, in index ' par_ind ' tuple:
14.DATA tuple:2 fields;
0:len 4; Hex 80000002; ASC;; 1:len 6; Hex 000000000401; ASC;;
16.
17.But in the parent table ' test/parent ', in index ' PRIMARY ',
18.the closest match we can find is record:
19.PHYSICAL Record:n_fields 3; 1-byte offs TRUE; Info bits 0
0:len 4; Hex 80000001; ASC;; 1:len 6; Hex 0000140c2d8f; ASC-;; 2:len 7; Hex 80009c40050084; ASC @;;

The INNODB will display the statement that raised the error. FOREIGN KEY constraints define failures and define the most closely related parent table. Many of the embedded information is represented in 16 notation, but it is not very important for problem diagnosis, they are mainly used for INNODB developers to view or for debugging purposes.

The next step is to display the currently active transactions for INNODB:

1.------------
2.TRANSACTIONS
3.------------
4.Trx ID Counter 0 80157601
5.Purge done for Trx ' s N:o <0 80154573 undo N:o <0 0
6.History List Length 6
7.Total number of lock structs in row lock hash table 0
8.LIST of transactions for each SESSION:
9.---TRANSACTION 0 0, not started, process no 3396, OS thread ID 1152440672
10.MySQL thread ID 8080, query ID 728900 localhost root
11.show InnoDB Status
---TRANSACTION 0 80157600, ACTIVE 4 sec, Process no 3396, OS thread ID 1148250464, thread declared inside InnoDB 442
13.mysql tables in use 1, locked 0
14.MySQL thread ID 8079, query ID 728899 localhost root sending data
15.select sql_calc_found_rows * from B limit 5
16.Trx read view won't see Trx with id>= 0 80157601, sees <0 80157597
---TRANSACTION 0 80157599, ACTIVE 5 sec, Process no 3396, OS thread ID 1150142816 fetching rows, thread declared inside InnoDB 166
18.mysql tables in use 1, locked 0
19.MySQL thread ID 8078, query ID 728898 localhost root sending data
20.select sql_calc_found_rows * from B limit 5
21.Trx read view won't see Trx with id>= 0 80157600, sees <0 80157596
---TRANSACTION 0 80157598, ACTIVE 7 sec, Process no 3396, OS thread ID 1147980128 fetching rows, thread declared inside InnoDB 114
23.mysql tables in use 1, locked 0
24.MySQL thread ID 8077, query ID 728897 localhost root sending data
25.select sql_calc_found_rows * from B limit 5
26.Trx read view won't see Trx with id>= 0 80157599, sees <0 80157595
---TRANSACTION 0 80157597, ACTIVE 7 sec, Process no 3396, OS thread ID 1152305504 fetching rows, thread declared inside InnoDB 400
28.mysql tables in use 1, locked 0
29.MySQL thread ID 8076, query ID 728896 localhost root sending data
30.select sql_calc_found_rows * from B limit 5
31.Trx read view won't see Trx with id>= 0 80157598, sees <0 80157594

If the current connection is not many, the entire list of transactions is displayed, and if there is a large number of connections, INNODB will only display their number, reducing the output list information so that the output will not be too much.

The transaction ID is the identity of the current transaction, and the ID of the transaction is incremented each time. Purge done for Trx ' s N:o Refers to the number of transactions that the Purge (purge) thread has completed. InnoDB only clears older versions of data that are not needed by the current transaction. Old, uncommitted transactions can block cleansing threads and consume resources. You can see if this happens by looking at the difference in the number of cleared transactions by 2 times. In a few cases, the cleansing thread may be difficult to keep up with the update speed, and the difference between the 2 view values may be increasing; then Innodb_max_purge_lag will come in handy. "Undo N:o" Shows the rollback log number currently being processed by the cleansing thread, and its value is 0 if it is not currently active.

History list length 6 refers to the number of non-purged transactions in the rollback space. As the transaction commits, its value increases, and its value decreases as the thread is cleared to run.

Total number of the lock structs in row lock hash table refers to the sum of the row lock structures that the transaction has allocated. It is not necessarily equal to the total number of rows that have been locked, usually a lock structure corresponding to multiple rows of records.

In MySQL, if there is no active transaction for each connection, its state is not started, and if there is an active transaction, it is active. Note that although the transaction is active, the state of its connection may be "sleep"-if it is in a transaction with multiple statements. INNODB displays both the system's thread number and the process number, which can be useful for debugging or other similar purposes using GDB. In addition, the state of the transaction is displayed according to the current actual state, such as "read record (fetching rows)",em> "Update (updating)" and so on. "Thread declared inside InnoDB 400" means that the InnoDB kernel is running the thread and requires 400 more tickets. Innodb will limit the number of simultaneous threads to the innodb_thread_concurrency based on its value. If the thread is not currently running in the kernel of INNODB, its status may be "Waiting in Innodb queue" or "sleeping before joining Innodb queue". The latter state is a bit of a meaning-Innodb to avoid having too many threads at the same time rushing into the run queue, they try to get these threads into a wait state if there are not enough free slots (slots). This may cause the number of threads currently active in the Innodb kernel to be smaller than the innodb_thread_concurrency value. In some load environment, this may help reduce the time that threads enter the queue. Can be achieved by adjusting the Innodb_thread_sleep_delay, its unit is subtle.

MySQL tables in use 1, locked 0 refers to the number of data tables that have been used in a transaction (already visited), and the number of locks. Innodb generally does not lock the table, so the number of lock tables is generally 0, unless it is an ALTER table or other like a lock TABLES statement.

In addition to the specific information related to InnoDB, some basic information can be viewed through, for example, what statement is being executed, query ID number, query status, and so on.

The following section shows specific IO-related information:

1.--------
2.FILE I/O
3.--------
4.I/O thread 0 state:waiting for I/O request (insert buffer thread)
5.I/O thread 1 state:waiting for I/O request (log thread)
6.I/O thread 2 state:waiting for I/O request (read thread)
7.I/O thread 3 state:waiting for I/O request (write thread)
8.Pending normal Aio reads:0, Aio writes:0,
9. Ibuf Aio reads:0, log I/O ' s:0, sync I/O ' s:0
10.Pending Flushes (fsync) log:0; Buffer pool:0
11.17909940 os file reads, 22088963 OS file writes, 1743764 OS Fsyncs
12.0.20 reads/s, 16384 avg bytes/read, 5.00 writes/s, 0.80 fsyncs/s

This section shows the IO helper thread status-insert buffer thread, log thread, read, write thread. They correspond to insert buffer merge, asynchronous log refresh, read-ahead, and flush dirty data, respectively. The normal read originating from the query is performed by a running query. Under the Unix/linux platform, you can always see 4 threads, which are adjustable by innodb_file_io_threads on Windows. Each thread is ready to see its status: Waiting for I/O request or performing a specific operation.

Each thread shows the number of operations in progress-the number of operations that are being executed or executing. In addition, the number of fsync operations that are being performed is also displayed. When writing data, InnoDB need to make sure that the data is eventually written to disk, but it is not enough to put them in the system cache. This is usually done by calling Fsync (). If its value is always high, it means that the InnoDB may be at a higher IO load state. Note that the IO requests raised by the thread execution request are not counted, so although the system has a high IO load, they may have a value of 0.

The following shows the average statistics for IO operations, which are useful for graphical display or monitoring.
"16384 avg Bytes/read" is the average of read requests. Random io, the size of each page is 16K, and the pre-reading of a full-table scan or index Scan can cause this value to increase significantly. Therefore, it embodies the efficiency of pre-reading.

1.-------------------------------------
2.INSERT BUFFER and ADAPTIVE HASH INDEX
3.-------------------------------------
4.Ibuf for Space 0:size 1, free list len 887, seg size 889, was not empty
5.Ibuf for Space 0:size 1, free list len 887, seg size 889,
6.2431891 inserts, 2672643 merged RECs, 1059730 merges
7.Hash table size 8850487, used cells 2381348, node heap has 4091 buffer (s)
8.2208.17 Hash searches/s, 175.05 non-hash searches/s

This section shows the status of the insert buffer and the adaptive Hash index. The first line shows the status of the insert buffer-the size of the segment and the free list, as well as how many records are in the buffer. The following shows how many insertions have been completed in the buffer, how many records have been merged, and how many merges have been completed. The ratio of the number of merges divided by the number of insertions can reflect the efficiency of the insert buffer.

InnoDB uses a hash index to establish a memory page index to form an adaptive hash index rather than a b-tree index, which accelerates the retrieval of row Records to memory pages. This shows the size of the hash table, as well as how many cells and buffers are used by the adaptive Hash Index. You can understand the efficiency of a hash index by calculating the number of times it is retrieved using a hash index and the number of times it has not been retrieved.

Currently there is no way to adjust the adaptive hash index, mainly for viewing.

1.---
2.LOG
3.---
4.Log Sequence Number 84 3000620880
5.Log flushed up to   84 3000611265
6.Last checkpoint at  84 2939889199
7.0 Pending Log writes, 0 pending CHKP writes
8.14073669 log I/O ' s done, 10.90 log I/O ' S/second

The next step is to display information about the InnoDB log subsystem. You can see the current log sequence number-the equivalent of the total number of bytes that InnoDB has written to the log file since the table space began to be created. You can also see which point the log has been flushed to, and you can also calculate how many logs are not flushed to the file based on the last checkpoint. InnoDB uses a fuzzy checkpoint, so this line shows the log sequence number that has been flushed from the buffer pool to the file. Because a higher log sequence number may not be flushed to the log file immediately, the log sequence number cannot be overwritten. By monitoring the log sequence to which log is flushed, you can determine whether the innodb_log_buffer_size setting is reasonable, and if you see more than 30% of the log is not flushed to the log file, you need to consider increasing its value.

In addition, you can see the log writes and the number of checkpoints. Depending on the number of log I/O operations, you can partition the number of IO requests and log IO requests related to the tablespace, and you can determine exactly how many log files are required. Note that the value of innodb_flush_log_at_trx_commit can affect the high or low cost of log write operations. If innodb_flush_logs_at_trx_commit=2, the log is written to the system cache and then sequentially written to the log file, so it is relatively much faster.

1.----------------------
2.BUFFER POOL and MEMORY
3.----------------------
4.Total Memory Allocated 4648979546; In additional pool allocated 16773888
5.Buffer Pool Size   262144
6.Free buffers       0
7.Database pages     258053
8.Modified db pages  37491
9.Pending reads 0
10.Pending WRITES:LRU 0, flush list 0, single page 0
11.Pages read 57973114, created 251137, written 10761167
12.9.79 reads/s, 0.31 creates/s, 6.00 writes/s
13.Buffer Pool hits rate 999/1000

This section shows information about the utilization of the buffer pool and memory. You can see all the memory allocated by InnoDB (sometimes more than you can set), as well as additional memory pool allocations (you can check if it's the right size), how many memory pages there are in total, how many free memory pages there are, how many memory pages the database allocates, and how many dirty memory pages there are. From this information, you can determine whether the memory buffer pool is set reasonable, if there is always a large number of free memory pages, you do not need to set up so much memory, you can appropriately reduce a bit. If the free memory page is 0, in this case the database memory page does not necessarily match the total number of buffer pools, because the buffer pool also needs to hold information such as lock information, adaptive hash Index, and other system structures.

Read-write in wait refers to a memory buffer pool level request. InnoDB may merge multiple file-level requests onto one, so they are different. We can also see the pages in the various types of IO,LRU memory pages that InnoDB commits that need to be refreshed-dirty memory pages that are not accessed for long periods of time; Refresh List-
An old memory page that needs to be refreshed after the checkpoint process has finished processing, and a separate memory page-a separate write memory page.

We can also see how many times memory pages have been read and written. A memory page that has been created is an empty memory page that is created specifically for new data when the contents of the current memory page are not read into the memory buffer pool.

Finally we can see the buffer pool hit rate, which indicates the efficiency of the buffer pool. 1000/1000 equals 100% of the hit rate. However, it is hard to say that the buffer pool hit rate is high enough, depending on the load environment. Typically, 950/1000 is sufficient, and sometimes the hit rate may be 995/1000 in environments with high IO loads.

1.--------------
2.ROW OPERATIONS
3.--------------
4.0 queries inside InnoDB, 0 queries in queue
5.1 Read views open inside InnoDB
6.Main thread Process No. 10099, id 88021936, state:waiting for server activity
7.Number of rows inserted 143, updated 3000041, deleted 0, read 24865563
8.0.00 inserts/s, 0.00 updates/s, 0.00 DELETES/S, 0.00 READS/S

The last part shows the data row operations and some system information related situations.

The InnoDB thread queue status is displayed at the beginning-how many threads are waiting or active. How many read views are open inside the InnoDB-
This is the case when the transaction begins, but there is currently no active statement, the state of the INNODB main thread controls the number of system operation schedules-flush dirty memory pages, checkpoints, purge threads, flush logs, merge insert buffers, and so on. The value of "state" indicates the current status of the main thread.

You can then see the number of data row operations and their average values since the system started. They can be conveniently used to monitor and draw system state diagrams, and the number of data row operations can be a good measure of innodb load. Not all data row operations bring the same load, access to 10 bytes of the row is much smaller than the 10Mb rows, but the total number of queries compared to the information is more useful, the difference is very large.

It is also important to note that the SHOW INNODB STATUS is not immutable and may not match at some point in time. Show INNODB status results, different times may show different results, so sometimes conflicting information may be seen. This is due to the need for a global lock to provide consistent information at design time, resulting in significant overhead.

Note: This article is reproduced, if there is infringement, please contact me: [Email protected]

MySQL View storage engine status show engine InnoDB status detailed