MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
When InnoDB
monitors are enabled for periodic
output, InnoDB
writes the output to
mysqld server standard error output
(stderr
) every 15 seconds, approximately.
InnoDB
sends the monitor output to
stderr
rather than to stdout
or fixed-size memory buffers to avoid potential buffer overflows.
On Windows, stderr
is directed to the default
log file unless configured otherwise. If you want to direct the
output to the console window rather than to the error log, start
the server from a command prompt in a console window with the
--console
option. For more
information, see Section 5.4.2.1, “Error Logging on Windows”.
On Unix and Unix-like systems, stderr
is
typically directed to the terminal unless configured otherwise.
For more information, see Section 5.4.2.2, “Error Logging on Unix and Unix-Like Systems”.
InnoDB
monitors should only be enabled when you
actually want to see monitor information because output generation
causes some performance decrement. Also, if monitor output is
directed to the error log, the log may become quite large if you
forget to disable the monitor later by dropping the monitor table.
To assist with troubleshooting, InnoDB
temporarily enables standard InnoDB
Monitor
output under certain conditions. For more information, see
Section 14.21, “InnoDB Troubleshooting”.
Each monitor begins with a header containing a timestamp and the monitor name. For example:
===================================== 2014-10-16 16:28:15 7feee43c5700 INNODB MONITOR OUTPUT =====================================
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 the InnoDB
Standard Monitor and Lock
Monitor for periodic output may be performed in one of two ways:
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
. The Tablespace Monitor and
Table Monitor are also enabled using the CREATE
TABLE
method.
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 and the database where the
table is created are 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.
The CREATE TABLE
method of
enabling InnoDB
monitors is deprecated
and may be removed in a future release. As of MySQL 5.6.16,
you can enable the standard InnoDB
Monitor and InnoDB
Lock Monitor using the
innodb_status_output
and
innodb_status_output_locks
system variables.
Using the
innodb_status_output
and
innodb_status_output_locks
system variables, introduced in MySQL 5.6.16.
The PROCESS
privilege is required
to enable or disable InnoDB
Monitors.
To enable the standard InnoDB Monitor for periodic output, create
the innodb_monitor
table:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
To disable the standard InnoDB
Monitor, drop
the table:
DROP TABLE innodb_monitor;
As of MySQL 5.6.16, you can also enable the standard
InnoDB
Monitor by setting the
innodb_status_output
system
variable to ON
.
SET GLOBAL innodb_status_output=ON;
To disable the standard InnoDB
Monitor, set
innodb_status_output
to
OFF
.
When you shut down the server, the
innodb_status_output
variable is
set to the default OFF
value.
To enable the InnoDB
Lock Monitor for periodic
output, create the innodb_lock_monitor
table:
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
To disable the InnoDB
Lock Monitor, drop the
table:
DROP TABLE innodb_lock_monitor;
As of MySQL 5.6.16, 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
InnoDB
Lock Monitor data printed periodically:
SET GLOBAL innodb_status_output=ON; SET GLOBAL innodb_status_output_locks=ON;
When you shut down the server, the
innodb_status_output
and
innodb_status_output_locks
variables are set to the default OFF
value.
To disable the InnoDB
Lock Monitor, set
innodb_status_output_locks
to
OFF
. Set
innodb_status_output
to OFF to
also disable the standard InnoDB
Monitor.
To enable the InnoDB
Lock Monitor for
SHOW ENGINE INNODB
STATUS
output, you are only required to enable
innodb_status_output_locks
.
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
:
mysql> SHOW ENGINE INNODB STATUS\G
SHOW ENGINE INNODB
STATUS
output also includes InnoDB
Lock Monitor data if the InnoDB
Lock Monitor is
enabled.
Standard InnoDB
Monitor output can be enabled
and directed to a status file by specifying the
--innodb-status-file
option at startup. When this
option is used, InnoDB
creates a file named
innodb_status.
in the data directory and writes output to it every 15 seconds,
approximately.
pid
InnoDB
removes the status file when the server
is shut down normally. If an abnormal shutdown occurs, the status
file may have to be removed manually.
The --innodb-status-file
option is intended for
temporary use, as output generation can affect performance, and
the
innodb_status.
file can become quite large over time.
pid
To enable the InnoDB
Tablespace Monitor for
periodic output, create the
innodb_tablespace_monitor
table:
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
To disable the standard InnoDB
Tablespace
Monitor, drop the table:
DROP TABLE innodb_tablespace_monitor;
The Tablespace Monitor is deprecated; expect it to be removed in a future MySQL release.
To enable the InnoDB
Table Monitor for periodic
output, create the innodb_table_monitor
table:
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
To disable the InnoDB
Table Monitor, drop the
table:
DROP TABLE innodb_table_monitor;
The Table Monitor is deprecated; expect it to be removed in a future MySQL release.