概述

很多時(shí)候在MySQL處理死鎖問(wèn)題時(shí)，由于show engine innodb status輸出來(lái)的死鎖日志無(wú)任務(wù)事務(wù)上下文，并不能很好地診斷相關(guān)事務(wù)所持有的所有鎖信息，包括：鎖個(gè)數(shù)、鎖類(lèi)型等。

下面介紹如何開(kāi)啟鎖監(jiān)控來(lái)查看到更詳細(xì)的事務(wù)鎖占用情況。

1、INNODB監(jiān)控機(jī)制(InnoDB Monitors)

mysql提供一套INNODB監(jiān)控機(jī)制，用于周期性(每15鈔)輸出INNODB運(yùn)行相關(guān)狀態(tài)(INNODB運(yùn)行狀態(tài)、表空間狀態(tài)、表狀態(tài)等)到mysqld服務(wù)標(biāo)準(zhǔn)錯(cuò)誤輸出。另外，INNODB標(biāo)準(zhǔn)監(jiān)控和鎖監(jiān)控，也可以通過(guò)命令：show engine innodb status輸出到控制臺(tái)。

此部分內(nèi)容一般輸出到mysql error log里。

官方說(shuō)明：

When you enable InnoDB monitors for periodic output, InnoDB writes their output to the mysqld server standard error output (stderr). In this case, no output is sent to clients. When switched on, InnoDB monitors print data about every 15 seconds. Server output usually is directed to the error log (see Section 5.4.2, “The Error Log”). This data is 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 window rather than to the error log.

該類(lèi)監(jiān)控機(jī)制默認(rèn)是關(guān)閉狀態(tài)，分析問(wèn)題需要查看監(jiān)控日志時(shí)再開(kāi)啟。

建議分析問(wèn)題后，將監(jiān)控關(guān)閉；否則，每15秒輸出一次INNODB運(yùn)行狀態(tài)信息到錯(cuò)誤日志，會(huì)使用日志變得特別大。

2、開(kāi)啟狀態(tài)監(jiān)控

INNODB監(jiān)控機(jī)制目前主要提供如下四類(lèi)監(jiān)控：

標(biāo)準(zhǔn)監(jiān)控(Standard InnoDB Monitor)：監(jiān)視活動(dòng)事務(wù)持有的表鎖、行鎖；事務(wù)鎖等待；線程信號(hào)量等待；文件IO請(qǐng)求；buffer pool統(tǒng)計(jì)信息；InnoDB主線程purge和change buffer merge活動(dòng)。

鎖監(jiān)控(InnoDB Lock Monitor)：提供額外的鎖信息。

表空間監(jiān)控(InnoDB Tablespace Monitor)：顯示共享表空間中的文件段以及表空間數(shù)據(jù)結(jié)構(gòu)配置驗(yàn)證。

表監(jiān)控(InnoDB Table Monitor)：顯示內(nèi)部數(shù)據(jù)字典的內(nèi)容。

關(guān)于四類(lèi)監(jiān)控開(kāi)啟與關(guān)閉方法，主要是通過(guò)創(chuàng)建系統(tǒng)可識(shí)讀的特殊表名來(lái)完成。特別地，除表空間(InnoDB Tablespace Monitor)監(jiān)控和表監(jiān)控(InnoDB Table Monitor)外，其他二類(lèi)監(jiān)控還可能通過(guò)修改系統(tǒng)參數(shù)來(lái)完成。

基于系統(tǒng)表的方式和基于系統(tǒng)參數(shù)的方式，只要使用二者其中一種方式開(kāi)啟監(jiān)控即可。

2.1. 標(biāo)準(zhǔn)監(jiān)控(Standard InnoDB Monitor)

2.1.1、基于系統(tǒng)表：innodb_monitor

mysql會(huì)通過(guò)檢查是否存在名為innodb_monitor的數(shù)據(jù)表，來(lái)判斷是否開(kāi)啟標(biāo)準(zhǔn)監(jiān)控，并打印日志。

需要開(kāi)啟，則創(chuàng)建表；需要關(guān)閉，則刪除表。

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_monitor;

2.1.2、基于系統(tǒng)參數(shù)：innodb_status_output

自mysql 5.6.16版本之后，可以通過(guò)設(shè)置系統(tǒng)參數(shù)(innodb_status_output)的方式開(kāi)啟或者關(guān)閉標(biāo)準(zhǔn)監(jiān)控。

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output=OFF;

2.2. 開(kāi)啟鎖監(jiān)控(InnoDB Lock Monitor)

2.2.1、基于系統(tǒng)表：innodb_lock_monitor

mysql會(huì)通過(guò)檢查是否存在名為innodb_lock_monitor的數(shù)據(jù)表，來(lái)判斷是否開(kāi)啟鎖監(jiān)控，并打印日志。

需要開(kāi)啟，則創(chuàng)建表；需要關(guān)閉，則刪除表。

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_lock_monitor;

2.2.2、基于系統(tǒng)參數(shù)：innodb_status_output_locks

自mysql 5.6.16版本之后，可以通過(guò)設(shè)置系統(tǒng)參數(shù)(innodb_status_output_locks)的方式開(kāi)啟或者關(guān)閉標(biāo)準(zhǔn)監(jiān)控。

--前提需要開(kāi)啟 innodb_status_output
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
set GLOBAL innodb_status_output_locks=OFF;

2.3. 開(kāi)啟表空間監(jiān)控(InnoDB Tablespace Monitor)

基于系統(tǒng)表：innodb_tablespace_monitor

mysql會(huì)通過(guò)檢查是否存在名為innodb_tablespace_monitor的數(shù)據(jù)表，來(lái)判斷是否開(kāi)啟表空間監(jiān)控，并打印日志。

需要開(kāi)啟，則創(chuàng)建表；需要關(guān)閉，則刪除表。

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_tablespace_monitor;

注：表空間監(jiān)控暫不支持通過(guò)參數(shù)方式配置，并且未來(lái)會(huì)被廢棄。

2.4. 開(kāi)啟表監(jiān)控(InnoDB Table Monitor)

mysql會(huì)通過(guò)檢查是否存在名為innodb_table_monitor的數(shù)據(jù)表，來(lái)判斷是否開(kāi)啟表監(jiān)控，并打印日志。

需要開(kāi)啟，則創(chuàng)建表；需要關(guān)閉，則刪除表。

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_table_monitor;

注：表監(jiān)控暫不支持通過(guò)參數(shù)方式配置，并且未來(lái)會(huì)被廢棄。

3、注意事宜

3.1. 監(jiān)控復(fù)位

需要特別注意的一點(diǎn)是：mysql服務(wù)重啟后，需要重啟開(kāi)啟相應(yīng)監(jiān)控，才會(huì)生效。換句話說(shuō)，服務(wù)重啟后，之前配置的所有監(jiān)控都被復(fù)位，處于關(guān)閉狀態(tài)。

基于系統(tǒng)表方式開(kāi)啟的監(jiān)控，在mysql服務(wù)重啟后，即使表存在，監(jiān)控也不會(huì)生效。需要重啟drop表，再create表，才能使監(jiān)控生效。

基于系統(tǒng)參數(shù)方式開(kāi)啟的監(jiān)控，在mysql服務(wù)重啟后，相關(guān)系統(tǒng)參數(shù)值都是OFF。需要重啟設(shè)置對(duì)應(yīng)的參數(shù)，才能使用監(jiān)控生效。

3.2. 錯(cuò)誤日志大小

不在停機(jī)或重啟情況下，mysql每15秒輸出一次INNODB運(yùn)行狀態(tài)信息到錯(cuò)誤日志。

這會(huì)使用日志變得越來(lái)越大。建議在需要的時(shí)候開(kāi)啟，不需要的時(shí)候關(guān)閉掉。