17.17.2 Enabling InnoDB 监视器
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 Default Error Log Destination on Windows.
On Unix and Unix-like systems, stderr
is typically directed to the terminal unless configured otherwise. For more information, see Default 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.
To assist with troubleshooting, InnoDB
temporarily enables standard InnoDB
Monitor output under certain conditions. For more information, see Section 17.20, “InnoDB Troubleshooting”.
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 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.
The innodb_status_output
and innodb_status_output_locks
system variables are used to enable the standard InnoDB
Monitor and InnoDB
Lock Monitor.
The PROCESS
privilege is required to enable or disable InnoDB
Monitors.
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.
InnoDB
Lock Monitor data is printed with the InnoDB
Standard Monitor output. Both the InnoDB
Standard Monitor and InnoDB
Lock Monitor must be enabled to have InnoDB
Lock Monitor data printed periodically.
To enable the InnoDB
Lock Monitor, set the innodb_status_output_locks
system variable to ON
. 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;
To disable the InnoDB
Lock Monitor, set innodb_status_output_locks
to OFF
. Set innodb_status_output
to OFF
to also disable the InnoDB
Standard Monitor.
When you shut down the server, the innodb_status_output
and innodb_status_output_locks
variables are set to the default OFF
value.
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