一、InnoDB Monitor
在MySQL处理死锁问题时，由于show engine innodb status输出来的死锁日志无任务事务上下文，并不能很好地诊断相关事务所持有的所有锁信息，包括：锁个数、锁类型等。于是，需要能查看到更详细的事务锁占用情况。
MySQL提供一套InnoDB监控机制，用于周期性(每15钞)输出InnoDB运行相关状态(InnoDB运行状态、表空间状态、表状态等)到MySQLD服务标准错误输出。另外，InnoDB标准监控和锁监控，也可以通过命令：show engine innodb status输出到控制台，此部分内容一般输出到mysql error log里。
当你将InnoDB Monitors用于定期输出时，InnoDB将其输出写入MySQL服务器标准错误输出（stderr），在这种情况下，不会向客户端发送输出。打开InnoDB Monitors时，InnoDB会每隔15秒监控打印一次数据，服务器输出通常指向错误日志。该数据在性能调优中非常有用。
该类监控机制默认是关闭状态，分析问题需要查看监控日志时再开启。建议分析问题后，将监控关闭；否则，每15秒输出一次INNODB运行状态信息到错误日志，会使用日志变得特别大。
二、InnoDB Monitor Type
InnoDB监控机制目前主要提供如下四类监控：
- 标准监控（Standard InnoDB Monitor）：监视活动事务持有的表锁、行锁；事务锁等待；线程信号量等待；文件IO请求；buffer pool统计信息；InnoDB主线程purge和change buffer merge活动。
- 锁监控（InnoDB Lock Monitor）：提供额外的锁信息。
- 表空间监控（InnoDB Tablespace Monitor）：显示共享表空间中的文件段以及表空间数据结构配置验证。
- 表监控（InnoDB Table Monitor）：显示内部数据字典的内容。
关于四类监控开启与关闭方法，一言以蔽之，主要是通过创建系统可识读的特殊表名来完成。特别地，除表空间（InnoDB Tablespace Monitor）监控和表监控（InnoDB Table Monitor）外，其他二类监控还可能通过修改系统参数来完成。
三、Start InnoDB Monitor
基于系统表的方式和基于系统参数的方式，只要使用二者其中一种方式开启监控即可。
1. 开启标准监控（Standard InnoDB Monitor）
基于系统表：innodb_monitor
MySQL会通过检查是否存在名为innodb_monitor的数据表，来判断是否开启标准监控，并打印日志。需要开启，则创建表；需要关闭，则删除表。
|
1 2 |
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; DROP TABLE innodb_monitor; |
基于系统参数：innodb_status_output
自MySQL 5.6.16版本之后，可以直接通过设置系统参数（innodb_status_output）的方式开启或者关闭标准监控。
|
1 2 |
set GLOBAL innodb_status_output=ON; set GLOBAL innodb_status_output=OFF; |
2. 开启锁监控（InnoDB Lock Monitor）
基于系统表：innodb_lock_monitor
MySQL会通过检查是否存在名为innodb_lock_monitor的数据表，来判断是否开启锁监控，并打印日志。需要开启，则创建表；需要关闭，则删除表。
|
1 2 |
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; DROP TABLE innodb_lock_monitor; |
基于系统参数：innodb_status_output_locks
自MySQL 5.6.16版本之后，可以通过设置系统参数（innodb_status_output_locks）的方式开启或者关闭标准监控。
|
1 2 3 |
set GLOBAL innodb_status_output=ON; set GLOBAL innodb_status_output_locks=ON; set GLOBAL innodb_status_output_locks=OFF; |
注：前提需要开启 innodb_status_output。
3. 开启表空间监控（InnoDB Tablespace Monitor）
基于系统表：innodb_tablespace_monitor
MySQL会通过检查是否存在名为innodb_tablespace_monitor的数据表，来判断是否开启表空间监控，并打印日志。需要开启，则创建表；需要关闭，则删除表。
|
1 2 |
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB; DROP TABLE innodb_tablespace_monitor; |
注：表空间监控暂不支持通过参数方式配置，并且未来会被废弃。
4. 开启表监控（InnoDB Table Monitor）
MySQL会通过检查是否存在名为innodb_table_monitor的数据表，来判断是否开启表监控，并打印日志。需要开启，则创建表；需要关闭，则删除表。
|
1 2 |
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB; DROP TABLE innodb_table_monitor; |
四、InnoDB Standard Monitor and Lock Monitor Output
标准监视器与锁监视器相同，只是锁监视器包含其他锁信息。启用标准监视器进行周期性输出标准信息，但如果启用了锁监视器，则输出流会包含额外的锁信息。
标准监视器输出在使用SHOW ENGINE INNODB STATUS生成时限制为1MB 。
示例标准监视器输出：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 |
mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 38 srv_active, 0 srv_shutdown, 252 srv_idle srv_master_thread log flush and writes: 290 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 119 OS WAIT ARRAY INFO: signal count 103 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 38, rounds 76, OS waits 38 RW-excl spins 2, rounds 9383715, OS waits 3 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 mutex, 2.00 RW-shared, 4691857.50 RW-excl, 0.00 RW-sx ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2014-10-16 18:35:18 0x7fc2a95c1700 Transaction: TRANSACTION 1814, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3 MySQL thread id 2, OS thread handle 140474041767680, query id 74 localhost root update INSERT INTO child VALUES (NULL, 1) , (NULL, 2) , (NULL, 3) , (NULL, 4) , (NULL, 5) , (NULL, 6) Foreign key constraint fails for table `mysql`.`child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index par_ind tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000003; asc ;; 1: len 4; hex 80000003; asc ;; But in parent table `mysql`.`parent`, in index PRIMARY, the closest match we can find is record: PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 00000000070a; asc ;; 2: len 7; hex aa0000011d0134; asc 4;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2014-10-16 18:36:30 0x7fc2a95c1700 *** (1) TRANSACTION: TRANSACTION 1824, ACTIVE 9 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 3, OS thread handle 140474041501440, query id 80 localhost root updating DELETE FROM t WHERE i = 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table `mysql`.`t` trx id 1824 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000000200; asc ;; 1: len 6; hex 00000000071f; asc ;; 2: len 7; hex b80000012b0110; asc + ;; 3: len 4; hex 80000001; asc ;; *** (2) TRANSACTION: TRANSACTION 1825, ACTIVE 29 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 2, OS thread handle 140474041767680, query id 81 localhost root updating DELETE FROM t WHERE i = 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table `mysql`.`t` trx id 1825 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000000200; asc ;; 1: len 6; hex 00000000071f; asc ;; 2: len 7; hex b80000012b0110; asc + ;; 3: len 4; hex 80000001; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table `mysql`.`t` trx id 1825 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000000200; asc ;; 1: len 6; hex 00000000071f; asc ;; 2: len 7; hex b80000012b0110; asc + ;; 3: len 4; hex 80000001; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 1950 Purge done for trx's n:o < 1933 undo n:o < 0 state: running but idle History list length 23 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421949033065200, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421949033064280, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 1949, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 8 lock struct(s), heap size 1136, 1850 row lock(s), undo log entries 17415 MySQL thread id 4, OS thread handle 140474041235200, query id 176 localhost root update INSERT INTO `salaries` VALUES (55723,39746,'1997-02-25','1998-02-25') -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 224 OS file reads, 5770 OS file writes, 803 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 264.84 writes/s, 23.05 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 4425293, node heap has 444 buffer(s) 68015.25 hash searches/s, 106259.24 non-hash searches/s --- LOG --- Log sequence number 165913808 Log flushed up to 164814979 Pages flushed up to 141544038 Last checkpoint at 130503656 0 pending log flushes, 0 pending chkp writes 258 log i/o's done, 6.65 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2198863872 Dictionary memory allocated 776332 Buffer pool size 131072 Free buffers 124908 Database pages 5720 Old database pages 2071 Modified db pages 910 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 4, not young 0 0.10 youngs/s, 0.00 non-youngs/s Pages read 197, created 5523, written 5060 0.00 reads/s, 190.89 creates/s, 244.94 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5720, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 65536 Free buffers 62412 Database pages 2899 Old database pages 1050 Modified db pages 449 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3, not young 0 0.05 youngs/s, 0.00 non-youngs/s Pages read 107, created 2792, written 2586 0.00 reads/s, 92.65 creates/s, 122.89 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 2899, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 65536 Free buffers 62496 Database pages 2821 Old database pages 1021 Modified db pages 461 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1, not young 0 0.05 youngs/s, 0.00 non-youngs/s Pages read 90, created 2731, written 2474 0.00 reads/s, 98.25 creates/s, 122.04 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 2821, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=35909, Main thread ID=140471692396288, state: sleeping Number of rows inserted 1526363, updated 0, deleted 3, read 11 52671.72 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ |
标准监视器输出部分
有关标准监视器报告的每个度量的描述。
- Status
本节显示时间戳，监视器名称和每秒平均值所基于的秒数；秒数是当前时间和上一次InnoDB监视器输出打印时间之间的经过时间。
- BACKGROUND THREAD
srv_master_thread表示后台线程完成的工作。
- SEMAPHORES
本节报告等待信号的线程, 并统计线程需要旋转多少次或mutex或rw-lock信号的等待次数。InnoDB等待信号量的大量线程可能是磁盘I/O或其中的争用问题的结果。竞争可能是由于操作系统线程调度中查询的大量并行性或问题。将innodb_thread_concurrency系统变量设置为小于默认值可能有助于优化这种情况。该Spin rounds per wait行显示每个操作系统等待互斥锁的旋转锁圈数。
- LATEST FOREIGN KEY ERROR
本节提供有关最近的外键约束错误的信息，如果没有发生此类错误，则不存在。内容包括失败的语句以及有关失败的约束引用和引用表的信息。
- LATEST DETECTED DEADLOCK
本节提供有关最近的死锁的信息，如果没有发生死锁，则不存在。内容显示涉及到哪些事务，每个尝试执行的声明，他们拥有和需要的锁，哪个事务决定回滚以打破僵局。
- TRANSACTIONS
本节报告事务信息，如果此部分报告有锁等待，你的应用程序可能会遇到锁争用，输出还可以帮助跟踪事务死锁的原因。
- FILE I/O
本节提供有关InnoDB用于执行各种类型的I/O的线程信息。内容还显示待执行I/O操作的信息和I/O性能的统计信息。这些线程的数目由控制innodb_read_io_threads和innodb_write_io_threads参数。
- INSERT BUFFER AND ADAPTIVE HASH INDEX
本节显示InnoDB插入缓冲区（也称为更改缓冲区）和自适应散列索引的状态。有关相关信息，请参见第14.4.2节“插入缓冲区”和 第14.4.3节“自适应哈希索引”。
- LOG
本节显示有关InnoDB日志的信息，内容包括当前日志序列号，日志被刷新到磁盘的距离以及上次执行checkpoint的位置。该部分还显示有关待执行写入和写入性能统计信息的信息。
- BUFFER POOL AND MEMORY
本节给出了有关读取和写入页面的统计信息（BUFFER POOL），你可以从这些数字计算你的查询当前正在执行的数据文件I/O操作数。
- ROW OPERATIONS
本节显示主线程正在做什么，包括每种类型的行操作的数量和性能。
五、注意事宜
1. 监控复位
需要特别注意的一点是：MySQL服务重启后，需要重启开启相应监控，才会生效。换句话说，服务重启后，之前配置的所有监控都被复位，处于关闭状态。
基于系统表方式开启的监控，在MySQL服务重启后，即使表存在，监控也不会生效。需要重启drop表，再create表，才能使监控生效。
基于系统参数方式开启的监控，在MySQL服务重启后，相关系统参数值都是OFF。需要重启设置对应的参数，才能使用监控生效。
2. 错误日志大小
不在停机或重启情况下，mysql每15秒输出一次INNODB运行状态信息到错误日志。这会使用日志变得越来越大。建议在需要的时候开启，不需要的时候关闭掉。
3. 基于表方式将来会被废弃
基于表方式将来会被废弃，使用基于系统参数的方式开启。
Use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON.
4. 基于表方式无关表结构及内容
基于表方式，MySQL只检验表名被创建，则开启监控。至于，表创建到哪个数据库、表具体的数据结构、表里的内容都不关心，不会对监控开启有任何影响。
5. 日志状态输出时间
虽说状态日志是每15秒周期性输出一次，但是由于状态收集与输出也会占用一些时间，特别是表空间日志(INNODB TABLE MONITOR OUTPUT)和表日志(INNODB TABLESPACE MONITOR OUTPUT)。因此，两次日志时间并不是规律的间隔15秒，而是自上次输出后15秒加上收集输出监控日志的时间。
原文链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-monitors.html
