Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 176.3Kb
Man Pages (Zip) - 286.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Enabling InnoDB Monitors

14.17.2 Enabling InnoDB Monitors

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.

Note

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.

    Note

    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.

Enabling the Standard InnoDB Monitor

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.

Enabling the InnoDB Lock Monitor

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 InnoDBLock 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.

Note

To enable the InnoDB Lock Monitor for SHOW ENGINE INNODB STATUS output, you are only required to enable innodb_status_output_locks.

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:

mysql> SHOW ENGINE INNODB STATUS\G

SHOW ENGINE INNODB STATUS output also includes InnoDB Lock Monitor data if the InnoDB Lock Monitor is enabled.

Directing Standard InnoDB Monitor Output to a Status File

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.pid in the data directory and writes output to it every 15 seconds, approximately.

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.pid file can become quite large over time.

Enabling the InnoDB Tablespace Monitor

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;
Note

The Tablespace Monitor is deprecated and will be removed in a future MySQL release.

Enabling the InnoDB Table Monitor

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;
Note

The Tablespace Monitor is deprecated and will be removed in a future MySQL release.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.