14.15.3 InnoDB Standard Monitor and Lock Monitor Output

The Lock Monitor is the same as the standard Monitor except that
it includes additional lock information. Enabling either monitor
for periodic output turns on the same output stream, but the
stream includes extra information if the Lock Monitor is enabled.
For example, if you enable the standard InnoDB
Monitor and InnoDB Lock Monitor, that turns on
a single output stream. The stream includes extra lock information
until you disable the Lock Monitor.

Standard InnoDB Monitor output is limited to
1MB when produced using the
SHOW ENGINE INNODB
STATUS statement. This limit does not apply to output
written to the server's error output.

Some notes on the output sections:

Status

This section shows the timestamp, the monitor name, and the number
of seconds that per-second averages are based on. The number of
seconds is the elapsed time between the current time and the last
time InnoDB Monitor output was printed.

BACKGROUND
THREAD

The srv_master_thread lines shows work done by
the main background thread.

SEMAPHORES

This section reports threads waiting for a semaphore and
statistics on how many times threads have needed a spin or a wait
on a mutex or a rw-lock semaphore. A large number of threads
waiting for semaphores may be a result of disk I/O, or contention
problems inside InnoDB. Contention can be due
to heavy parallelism of queries or problems in operating system
thread scheduling. Setting the
innodb_thread_concurrency system
variable smaller than the default value might help in such
situations. The Spin rounds per wait line shows
the number of spinlock rounds per OS wait for a mutex.

LATEST FOREIGN KEY
ERROR

This section provides information about the most recent foreign
key constraint error. It is not present if no such error has
occurred. The contents include the statement that failed as well
as information about the constraint that failed and the referenced
and referencing tables.

LATEST DETECTED
DEADLOCK

This section provides information about the most recent deadlock.
It is not present if no deadlock has occurred. The contents show
which transactions are involved, the statement each was attempting
to execute, the locks they have and need, and which transaction
InnoDB decided to roll back to break the
deadlock. The lock modes reported in this section are explained in
Section 14.2.3, “InnoDB Lock Modes”.

TRANSACTIONS

If this section reports lock waits, your applications might have
lock contention. The output can also help to trace the reasons for
transaction deadlocks.

FILE I/O

This section provides information about threads that
InnoDB uses to perform various types of I/O.
The first few of these are dedicated to general
InnoDB processing. The contents also display
information for pending I/O operations and statistics for I/O
performance.

This section displays information about the
InnoDB log. The contents include the current
log sequence number, how far the log has been flushed to disk, and
the position at which InnoDB last took a
checkpoint. (See Section 14.10.3, “InnoDB Checkpoints”.) The
section also displays information about pending writes and write
performance statistics.

BUFFER POOL AND
MEMORY

This section gives you statistics on pages read and written. You
can calculate from these numbers how many data file I/O operations
your queries currently are doing.