Movatterモバイル変換

WhenInnoDB monitors are enabled for periodic output,InnoDB writes the output tomysqld server standard error output (stderr) every 15 seconds, approximately.

InnoDB sends the monitor output tostderr rather than tostdout 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, seeDefault Error Log Destination on Windows.

On Unix and Unix-like systems,stderr is typically directed to the terminal unless configured otherwise. For more information, seeDefault Error Log Destination 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.

InnoDB monitor output begins with a header containing a timestamp and the monitor name. For example:

=====================================2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT=====================================

The header for the standardInnoDB 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.

Theinnodb_status_output andinnodb_status_output_locks system variables are used to enable the standardInnoDB Monitor andInnoDB Lock Monitor.

ThePROCESS privilege is required to enable or disableInnoDB Monitors.

Enabling the Standard InnoDB Monitor

Enable the standardInnoDB Monitor by setting theinnodb_status_output system variable toON.

SET GLOBAL innodb_status_output=ON;

To disable the standardInnoDB Monitor, setinnodb_status_output toOFF.

When you shut down the server, theinnodb_status_output variable is set to the defaultOFF value.

Enabling the InnoDB Lock Monitor

InnoDB Lock Monitor data is printed with theInnoDB Standard Monitor output. Both theInnoDB Standard Monitor andInnoDB Lock Monitor must be enabled to haveInnoDB Lock Monitor data printed periodically.

To enable theInnoDB Lock Monitor, set theinnodb_status_output_locks system variable toON. Both theInnoDB standard Monitor andInnoDB Lock Monitor must be enabled to haveInnoDB Lock Monitor data printed periodically:

SET GLOBAL innodb_status_output=ON;SET GLOBAL innodb_status_output_locks=ON;

To disable theInnoDB Lock Monitor, setinnodb_status_output_locks toOFF. Setinnodb_status_output toOFF to also disable theInnoDB Standard Monitor.

When you shut down the server, theinnodb_status_output andinnodb_status_output_locks variables are set to the defaultOFF value.

Obtaining Standard InnoDB Monitor Output On Demand

As an alternative to enabling the standardInnoDB Monitor for periodic output, you can obtain standardInnoDB Monitor output on demand using theSHOW ENGINE INNODB STATUS SQL statement, which fetches the output to your client program. If you are using themysql 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 includesInnoDB Lock Monitor data if theInnoDB Lock Monitor is enabled.

Directing Standard InnoDB Monitor Output to a Status File

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