InnoDB Monitors provide information about the
InnoDB internal state. This information is useful
for performance tuning. Each Monitor can be enabled by creating a
table with a special name, which causes
write Monitor output periodically. Output for the standard
InnoDB Monitor is also available on demand
INNODB STATUS SQL statement. Additionally, to assist with
InnoDB temporarily enables
InnoDB Monitor output under certain
conditions. For more information, see
Section 14.6.11, “InnoDB Troubleshooting”.
There are several types of
InnoDBMonitor displays the following types of information:
Table and record locks held by each active transaction.
Lock waits of a transaction.
Semaphore waits of threads.
Pending file I/O requests.
Buffer pool statistics.
Purge and insert buffer merge activity of the main
For a discussion of
InnoDBlock modes, see Section 22.214.171.124.1, “InnoDB Lock Modes”.
To enable the standard
InnoDBMonitor for periodic output, create a table named
innodb_monitor. To obtain Monitor output on demand, use the
SHOW ENGINE INNODB STATUSSQL statement to fetch the output to your client program. If you are using the mysql interactive client, the output is more readable if you replace the usual semicolon statement terminator with
SHOW ENGINE INNODB STATUS\G
InnoDBLock Monitor prints additional lock information as part of the standard
InnoDBMonitor output. To enable the
InnoDBLock Monitor, create a table named
InnoDBTablespace Monitor prints a list of file segments in the shared tablespace and validates the tablespace allocation data structures. To enable this Monitor for periodic output, create a table named
InnoDBTable Monitor prints the contents of the
InnoDBinternal data dictionary. To enable this Monitor for periodic output, create a table named
To enable an
InnoDB Monitor for periodic output,
CREATE TABLE statement to create the table
associated with the Monitor. For example, to enable the standard
InnoDB Monitor, create the
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
To stop the Monitor, drop the table:
DROP TABLE innodb_monitor;
CREATE TABLE syntax is just a way
to pass a command to the
InnoDB engine through
MySQL's SQL parser: The only things that matter are the table name
innodb_monitor and that it be an
InnoDB table. The structure of the table is not
relevant at all for the
InnoDB Monitor. If you
shut down the server, the Monitor does not restart automatically
when you restart the server. Drop the Monitor table and issue a new
CREATE TABLE statement to start the
Monitor. (This syntax may change in a future release.)
As of MySQL 5.1.24, the
privilege is required to start or stop the
When you enable
InnoDB Monitors for periodic
InnoDB writes their output to the
mysqld server standard error output
stderr). In this case, no output is sent to
clients. When switched on,
InnoDB Monitors print
data about every 15 seconds. Server output usually is directed to
the error log (see Section 5.2.2, “The Error Log”). This data is useful
in performance tuning. On Windows, start the server from a command
prompt in a console window with the
--console option if you want to
direct the output to the window rather than to the error log.
InnoDB sends diagnostic output to
stderr or to files rather than to
stdout or fixed-size memory buffers, to avoid
potential buffer overflows. As a side effect, the output of
SHOW ENGINE INNODB
STATUS is written to a status file in the MySQL data
directory every fifteen seconds. The name of the file is
pid is the server process ID.
InnoDB removes the file for a normal shutdown. If
abnormal shutdowns have occurred, instances of these status files
may be present and must be removed manually. Before removing them,
you might want to examine them to see whether they contain useful
information about the cause of abnormal shutdowns. The
file is created only if the configuration option
innodb-status-file=1 is set.
InnoDB Monitors should be enabled only when you
actually want to see Monitor information because output generation
does result in some performance decrement. Also, if you enable
monitor output by creating the associated table, your error log may
become quite large if you forget to remove the table later.
For additional information about
Mark Leith: InnoDB Table and Tablespace Monitors
Each monitor begins with a header containing a timestamp and the monitor name. For example:
===================================== 141017 9:57:58 INNODB MONITOR OUTPUT =====================================
The header for the standard Monitor (
OUTPUT) is also used for the Lock Monitor because the
latter produces the same output with the addition of extra lock
The following sections describe the output for each Monitor.