Display engine InnoDB status explanation

A lot of people asked me to explain the output of show INNODB status , to see what the show INNODB status is outputting, and what information we can get from this information to improve MySQL performance.

First, let's look at the basics of show INNODB status output, which prints a lot about INNODB internal performance-related counters, statistics, transactional information, and so on. 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 OUTPUT3. =====================================4. The second mean calculated from the last 44 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, then it's good to get a global statistic, and then the average, of course, it's also useful to look directly at the output's result information.

The next section shows the signal (semaphore) related information:

1.----------2.semaphores3.----------4.OS Waiting information: appointment count 13569, signal number 114215.--Thread 1152170336 wait. /include/buf0buf.ic Line 630, Semaphore is 0.00 seconds: 6.Mutex Create file BUF0BUF.C line 517, lock var 07 in 0x2a957858b8. Wait for flag 0 to wait for end 9.-Thread 1147709792 wait. /include/buf0buf.ic Line 630 is 0.00 seconds Semaphore: 10.Mutex create file BUF0BUF.C line 517, lock var 0 wait for flag 0 to wait for end 13. The mutex rotates waiting for 5672442, turn 3899888, and the operating system waits 471914. RW shares rotation 5920, the operating system waits 2918; RW-EXCL rotation 3463, operating system wait 3163

This section 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-for example, in Chinese, the filename "Buf0buf.ic" indicates a relationship with some buffer pool contention if you want to know more, go to 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 status information "waiting to end", which means that the mutex has been freed, but the system scheduler thread is still processing.

The second block is event statistics-"Appointment count" and "signal count" show how active the InnoDB uses an internal synchronization array-time slice (slot) allocation and how frequently the thread signal uses a synchronous array these statistics can be used to indicate how often the InnoDB fallback system waits. There are also direct related information about the system wait, you can see the "OS Waits" mutex semaphore (mutexes), and the read-write lock shows that the mutex and the shared lock system wait and the "hold (Hold)" is not exactly the same, before fallback to the complex wait mode with Sync_array, InnoDB will try to "output" to the system, hoping that 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 see how often the context (context) is exchanged in the system state.

Another important piece of information is the number of "rotation waits" and "rotated circles". 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.------------------------The latest detection deadlock 3.------------------------4.060717 4:16:485. (1) Trades: 6. TRANSACTION 0 42313619,active 49 sec, process number 10099, OS thread number 3771312 start index read 7.mysql table used 1, lock 1 lock wait 3 lock structure, heap size 3209.MySQL thread number 30898, query ID 100626 localhost root update 10.update iz set pad = ' A ' where i = 211. (1) Wait for this lock to be granted: 12. Record locks Space ID 0 page no 16403 n bit 72 index ' PRIMARY ' table ' Test/iz ' Trx ID 0 42313619 lock_mode x Locks Rec But does not wait for Gap record lock, Heap does not have 5 physical record: N_fields 4; Compact format; Information bit 00:len 4; Hex 80000002;; 1:len 6; Hex 00000285a78f;; 2:len 7; Hex 00000040150110; ASC @;; 3:len 10; Hex 61202020202020202020;; 15. 16. (2) Trades: 17. TRANSACTION 0 42313620,active 24 seconds, process number 10099,os thread number 4078512 start index read, thread declaration internal InnoDB 50018.mysql table is using 1, Lock 119.3 Lock structure, heap size 32020.MySQL thread number 30899, query ID 100627 localhost root update 21.update iz set pad = ' a ' where i = 122. (2) DS The::::::::::23.record locks Space ID 0 page no 16403 n bit 72 index ' PRIMARY ' table ' Test/iz ' Trx ID 0 42313620 lock_mode x Lock Rec but not gap 24. Kee Record lock, Heap not 5 physical records: N_fields 4; Compact format; Information bit 00:len 4; Hex 80000002;; 1:len 6; Hex 00000285a78f;; 2:len 7; Hex 00000040150110; ASC @;; 3:len 10; Hex 61202020202020202020;; 26. 27. (2) Wait for this lock to be granted: 28. Record locks Space ID 0 page number 16403 N bit 72 index ' PRIMARY ' table ' Test/iz ' Trx ID 0 42313620 lock_mode x Lock Rec But no gap waits for record lock, Heap does not have 4 physical record: N_fields 4; Compact format; Information bit 00:len 4; Hex 80000001;; 1:len 6; Hex 00000285a78e;; 2:len 7; Hex 000000003411d9; ASC 4;; 3:len 10; Hex 61202020202020202020;; 31. 32. We roll back the deal (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 complex deadlock information you also need to look at the log file to find the statement that actually caused the conflict. In most cases, the show InnoDB status displays basically 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 Trade: 5. TRANSACTION 0 336342767,active 0 seconds, the process number 3946,os thread number 1151088992 is inserted, the thread declared in InnoDB 500 uses MySQL table 1, Lock 17.3 Lock Structure, heap size 368, undo log Entry 18.MySQL thread number 9697561, query ID 188161264 localhost root update 9. Insert sub-value (2,2) Table ' test/child ' FOREIGN KEY constraint failed: 11,12. CONSTRAINT ' Child_ibfk_1 ' FOREIGN KEY (' parent_id ') REFERENCES ' parent ' (' id ') on DELETE CASCADE13. Attempt to add ' Par_ind ' in the index to the child table Tuple: DATA tuple:2 field; 0:len 4; Hex 80000002;; 1:len 6; Hex 000000000401;; 16. In the parent table "Test/parent", in the Index "PRIMARY", we can find the closest match is the record: Physical record: N_fields 3; 1 bytes off true; Information bit 00:len 4; Hex 80000001;; 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.transactions3.------------4.Trx ID counter 0 801576015. For Trx N to complete: o <0 80154573 undo N:o <0 0 History list length 6 row lock total number of lock structures in the hash table 08. Transaction List per session: 9.---TRANSACTION 0 0, not started, process number 3396,os thread number 115244067210.MySQL thread number 8080, query ID 728900 The localhost root shows the InnoDB status.---TRANSACTION 0 80157600,active 4 seconds, process number 3396,os thread number 1148250464,innodb 442 internally declared thread 13.mysql table is using 1, lock 014.MySQL thread number 8079, query ID 728899 localhost root to send data 15. Select Sql_calc_found_rows * 516 from the B limit. TRX Read view does not see Trx with id = 80157601, see <0 8015759717.---TRANSACTION 0 80157599,active 5 sec, process number 3396, operating system thread number 1150142816 fetch rows, In-thread declaration InnoDB 16618.mysql is used in table 1, locking 019. The MySQL thread ID is 8078 and the query ID is 728898 localhost root to send data 20. Select Sql_calc_found_rows * 521.Trx from the B limit Read view does not see Trx with id> = 0 80157600, see <0 8015759622.---TRANSACTION 0 80157598,active 7 seconds, process number 3396,os thread number 1147980128 fetch line, thread in InnoDB 114 Internal Declaration 23.mysql table is using 1, lock 024.MySQL thread ID 8077, query ID 728897 localhost root to send data 25. Select Sql_calc_found_rows * 526 from the B limit. TRX Read view does not see Trx 80157599 with id> = 0, see <0 8015759527.---TRANSACTION 0 80157597,active 7 seconds, process number 3396,os lineProcess number 1152305504 fetch line, thread internal declaration InnoDB 40028. MySQL table is using 1, lock 029. MySQL thread number 8076, query ID 728896 localhost root sends data 30. Select Sql_calc_found_rows * 531.Trx from the B limit Read view does not see a string with id> = 0 0x157598, see <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. n Cleanup done for Trx: 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; thenInnodb_max_purge_lag will come in handy. "Undo N:o" shows the rollback log number currently being processed by the cleansing thread, and if it is not currently active, its value is 0.

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.

The total number of lock structures in the row lock hash table refers to the total number of 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, each connection if there is no active transaction, 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 (fetch row)",em> "Update (update)" and so on. "Threads within 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 parameter according to its value. If the thread is not currently running in the kernel of InnoDB, its state may be "waiting in InnoDB queue " or "InnoDB before Bedtime"in the queue. 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.

the MySQL table using 1,locked 0 refers to the number of data tables (already visited) that have been used in the transaction, 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/o3.--------4.i/o Thread 0 Status: Wait for I/O request (insert buffer thread) 5. I/O thread 1 status: Wait for I/O request (log thread) 6. I/O thread 2 status: Wait for I/O request (read thread) 7. I/O thread 3 status: Waiting for I/O request (write thread) normal AIO read: 0,aio write: 0,ibuf aio read: 0,log I/o ' s:0,sync I/o ' s:0 normal refresh (fsync) log:0; Buffer pool: 011.17909940 os file read, 22088963 os file write, 1743764 os fsyncs12.0.20 read/sec, 16384 avg Bytes/Read, 5.00 write/sec, 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 an 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 this innodb may be dealing with high IO load states. 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. Security buffer and adaptive hash exponent 3.-------------------------------------Space 0: Size 1, free list len 887, Segment size 889, not empty 5. Ibuf for space 0:size 1,free list len 887,seg size 889,6.2431891 Insert, 2672643 merge record, 1059730 merge 7. Table size is 8850487, cell 2381348 is used, node heap has 4,091 buffers 8.2208.17 hash Search/s,175.05 non-hash search/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, how many merges have been completed, and how 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. Login 3.---4. Serial number 84 30006208805. Record flushed to 84 3000611265 last Checkpoint is 84 29398891997.0 waiting for log write, 0 waiting for CHKP write 8.14073669 log I/O completion, 10.90 log I/O/sec

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 has not been 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. Data that is based on log I/O operations can be separated by the number of space-related IO requests and log IO requests, which in turn can determine how many log files are required. 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, It is then written sequentially to the log file, so it is relatively fast.

1.----------------------2. Buffer pool and memory 3.----------------------total memory allocation 4648979546; Allocate 16773888 buffer pool size in additional pool 262144 free buffer 0 Database page 2580538. Modify Database Page 374919. Pending reading 010. To be written: LRU 0, refresh List 0, page 011, read 57973114, created in 251137, Ref. 1076116712.9.79 Read/s,0.31 Create/sec, 6.00 Write/sec buffer pool hit 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 has allocated, 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 that InnoDB commits a variety of different types of IO,LRU memory pages that need to refresh pages-dirty memory pages, which are not accessed for long periods; Refresh List-
The old memory pages that need to be refreshed after the checkpoint process finishes processing; separate memory pages-separate write memory pages.

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 is equivalent to 100% of the hit ratio. 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. Operation 3.--------------the 4.0 queries in the InnoDB, 0 queries in the Queue 5.1 read the main thread procedure number of the Open view within InnoDB 10099,id 88021936, Status: Waiting for server activity 7. Insert Row number 143, update 3000041, delete 0, read 248655638.0.00 Insert/sec, 0.00 Update/S, 0.00 Delete/sec, 0.00 Read/sec

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 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-Refresh dirty memory pages, checkpoint, purge thread, refresh log, merge insert buffer, etc. "state" value represents the current state 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's load. Not all data row operations bring the same load, access to 10-byte rows is much smaller than the 10Mb rows, but this information is much more useful and different than the total number of queries.

It is also important to note that the SHOW INNODB status is not immutable and may not match at some point in time. is due to the need to provide consistent information at design time by a global lock, resulting in significant overhead.

Display engine InnoDB status explanation