14.14.2 Enabling InnoDB Monitors

When you enable InnoDB monitors for periodic
output, 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
innodb_status.pid,
where 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
innodb_status.pid
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, your error log may become quite large if you
forget to disable it later.

The header for the standard InnoDB Monitor
(INNODB MONITOR OUTPUT) is also used for the
Lock Monitor because the latter produces the same output with the
addition of extra lock information.

Enabling an InnoDB monitor for periodic output
involves using a CREATE TABLE statement to
create a specially named InnoDB table that is
associated with the monitor. For example, to enable the standard
InnoDB Monitor, you would create an
InnoDB table named
innodb_monitor.

Using 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 and that it be an InnoDB table. The
structure of the table is not relevant. 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.

Obtaining Standard InnoDB Monitor Output On Demand

As an alternative to enabling the standard
InnoDB Monitor for periodic output, you can
obtain standard InnoDB Monitor output on demand
using the SHOW ENGINE
INNODB STATUS SQL statement, which fetches 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 \G:

InnoDBLock Monitor data is printed with the
standard InnoDB Monitor output. Both the
InnoDB standard Monitor and
InnoDB Lock Monitor must be enabled to have
InnoDBLock Monitor data printed periodically.

As of MySQL 5.7.4, you can also enable the
InnoDB Lock Monitor by setting the
innodb_status_output_locks system
variable to ON. As with the
CREATE TABLE method for enabling
InnoDB Monitors, both the
InnoDB standard Monitor and
InnoDB Lock Monitor must be enabled to have
InnoDBLock Monitor data printed periodically:

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;