SHOW ENGINE INNODB STATUS is a specific form of the SHOW ENGINE statement that displays extensive InnoDB information and 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 waitshows 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 XtraDB/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.