View lock information (turn on InnoDB monitoring)

Current MySQL version: 5.6.211, background

When the deadlock problem is handled by MySQL, the deadlock show engine innodb status log output does not have a task transaction context, and it is not a good way to diagnose all the lock information held by the relevant transaction, including: Number of locks, type of lock, etc.

Therefore, you need to be able to see more detailed transaction lock occupancy.

Second, InnoDB monitoring mechanism (InnoDB Monitors)

MySQL provides a set of innodb monitoring mechanisms for periodic (every 15 banknotes) output InnoDB run-related status (InnoDB running state, tablespace status, table state, etc.) to MYSQLD service standard error output. In addition, INNODB standard monitoring and lock monitoring can also be done via command: show engine innodb status output to the console.
This section is generally output to MySQL error log (find the log location, see "Supplemental Knowledge").

Official instructions (see Reference Document 1) are as follows:

1

When you enable InnoDB monitors for periodic output, InnoDB writes their output to the MYSQLD server standard error output (stderr). In this case, the no output is sent to clients. When switched in, InnoDB monitors print data about every seconds. Server output usually is directed to the error log (see section 5.4.2, "the error log"). This data was 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 windows rather than to the error log.

This type of monitoring mechanism is turned off by default, and analysis issues need to be turned on when viewing the monitoring log.
It is recommended that after analyzing the problem, the monitoring is turned off; otherwise, the InnoDB run state information is output once every 15 seconds to the error log, and the log becomes particularly large.

Third, the open State monitoring

InnoDB Monitoring mechanism currently provides the following four types of monitoring:

• Standard InnoDB Monitor: monitors the table locks, row locks, transaction lock waits, thread semaphore waits, file IO requests, buffer pool statistics, InnoDB main thread purge and change buffer held by the active transaction; Merge activity.
• Lock monitoring (InnoDB lock Monitor): Provides additional lock information.
• Tablespace monitoring (InnoDB tablespace Monitor): Displays file segments in shared tablespace and table space data structure configuration validation.
• Table Monitor (InnoDB table Monitor): Displays the contents of the internal data dictionary.

On the four types of monitoring open and close methods, word, mainly by creating a system readable special table name to complete. In particular, in addition to the table space (InnoDB tablespace monitor) monitoring and table monitoring (InnoDB table monitor), other two types of monitoring may also be done by modifying the system parameters.
Based on the way the system tables and system parameters are based, only one of the two ways to turn on monitoring can be used.

1. Standard InnoDB monitor based on system table: Innodb_monitor

MySQL checks whether innodb_monitor standard monitoring is turned on and prints the logs by checking for the existence of a data table named.
You need to turn on, create a table, or delete the table if you need to close it.

12	CREATE TABLE innodb_monitor (a INT) engine=InnoDB; DROP TABLE innodb_monitor;

Based on system parameters: Innodb_status_output

Since MySQL 5.6.16, innodb_status_output standard monitoring can be turned on or off by setting the system parameters ().

12	set GLOBAL innodb_status_output= on; set GLOBAL innodb_status_output=OFF;

2. Open Lock Monitor (InnoDB lock monitor) based on system table: Innodb_lock_monitor

MySQL innodb_lock_monitor determines whether to turn on lock monitoring and print logs by checking for the existence of a data table named.
You need to turn on, create a table, or delete the table if you need to close it.

12	CREATE TABLE innodb_lock_monitor (a INT) engine=InnoDB; DROP TABLE innodb_lock_monitor;

Based on system parameters: Innodb_status_output_locks

Since MySQL 5.6.16, innodb_status_output_locks standard monitoring can be turned on or off by setting the system parameters ().

1234	set GLOBAL innodb_status_output= on; set GLOBAL innodb_status_output_locks= on; set GLOBAL innodb_status_output_locks=OFF;

Note : The prerequisites need to be openedinnodb_status_output

3. Turn on table space Monitoring (InnoDB tablespace Monitor) based on system table: Innodb_tablespace_monitor

MySQL will innodb_tablespace_monitor determine whether to turn on tablespace monitoring and print the logs by checking for the existence of a data table named.
You need to turn on, create a table, or delete the table if you need to close it.

12	CREATE TABLE innodb_tablespace_monitor (a INT) engine=InnoDB; DROP TABLE innodb_tablespace_monitor;

Note : Table space monitoring is not supported by parameter configuration and will be deprecated in the future.

4. Turn on Watch monitoring (InnoDB table Monitor)

MySQL will innodb_table_monitor determine whether to turn on table monitoring and print the logs by checking for the existence of a data table named.
You need to turn on, create a table, or delete the table if you need to close it.

12	CREATE TABLE innodb_table_monitor (a INT) engine=InnoDB; DROP TABLE innodb_table_monitor;

Note : Table monitoring is not supported by parameter configuration and will be deprecated in the future.

Iv. Matters of note 1. Monitoring reset

It is important to note that after the MySQL service restarts, it will need to be restarted to turn on the monitoring before it takes effect. In other words, after the service restarts, all previously configured monitoring is reset and turned off.

Monitoring based on system table mode, monitoring will not take effect after the MySQL service restarts, even if the table exists. The drop table needs to be restarted before the CREATE table can be made for monitoring to take effect.

Monitoring based on the system parameter mode, after the MySQL service restarts, the relevant system parameter values are off. You need to restart the settings for the corresponding parameters in order to use monitoring to take effect.

2. Error log size

Without downtime or restart, MySQL outputs InnoDB run status information to the error log every 15 seconds.
This will use the log to become more and more large. It is recommended to turn it on when needed and off when it is not needed.

3. Table-based methods will be discarded in the future

The table-based approach will be discarded in the future and opened using system-based parameters.

Use INFORMATION_SCHEMA or Performance_schema tables or SET GLOBAL innodb_status_output=on.

4. Table-Independent table structure and content

Based on the table, MySQL only verifies that the table name is created, then turn on monitoring.
As for, the database to which the table is created, the data structure of the table, the contents of the tables do not care, will not have any impact on monitoring.

5. Log Status Output time

Although the status log is periodically output every 15 seconds, the state collection and output also takes some time, especially the Tablespace log (INNODB table monitor Output) and the table log (INNODB tablespace monitor Output). Therefore, the two log time is not the regular interval of 15 seconds, but the time since the last output 15 seconds plus the collection output monitoring log.

V. Supplementary knowledge 1. View error Log output location

123456

MySQL [email protected]:test> select @@ log_error; +----------------------------------------+| @@ Log_error || ----------------------------------------|| /usr/Local/mysql/data/mysqld.local.err | +----------------------------------------+

2. View history log on status and output location

1234567

MySQL [email protected]:test> show VARIABLES like ' general% '; +------------------+---------------------------------------+| variable_name | Value | | ------------------+---------------------------------------|| General_Log | On || General_log_file |/usr/local/mysql/data/yerba-buena.log | +------------------+---------------------------------------+

3. Monitoring Log Interpretation

See reference Document 2 and Reference document 5

Vi. reference Documentation

1. Enabling InnoDB Monitors
2. InnoDB standard monitor and Lock monitor Output
3. How to debug InnoDB lock waits
4. How to find out who's locking a table in MySQL
5. InnoDB Monitor
6. InnoDB Monitoring Switch

View lock information (turn on InnoDB monitoring)