1、現(xiàn)象
今天協(xié)助其他同學(xué)排查問題的時(shí)候，發(fā)現(xiàn)數(shù)據(jù)庫(kù)錯(cuò)誤日志文件已經(jīng)有9G以上了，打開內(nèi)容查看如下：
=====================================
2020-07-08 13:47:43 0x7fe3723ff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 28112548 srv_active, 0 srv_shutdown, 18948137 srv_idle
srv_master_thread log flush and writes: 47060685
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 213360683
OS WAIT ARRAY INFO: reservation count 218012898
OS WAIT ARRAY INFO: reservation count 218624956
OS WAIT ARRAY INFO: reservation count 223392430
OS WAIT ARRAY INFO: reservation count 213358783
OS WAIT ARRAY INFO: reservation count 217996917
OS WAIT ARRAY INFO: reservation count 218627068
OS WAIT ARRAY INFO: reservation count 223399094
OS WAIT ARRAY INFO: reservation count 213372264
OS WAIT ARRAY INFO: reservation count 217974752
OS WAIT ARRAY INFO: reservation count 218606657
OS WAIT ARRAY INFO: reservation count 223387430
OS WAIT ARRAY INFO: reservation count 213382268
OS WAIT ARRAY INFO: reservation count 218029924
OS WAIT ARRAY INFO: reservation count 218619464
OS WAIT ARRAY INFO: reservation count 223399870
OS WAIT ARRAY INFO: signal count 2558329753
RW-shared spins 0, rounds 2208700138, OS waits 822920663
RW-excl spins 0, rounds 80631903713, OS waits 1603642807
RW-sx spins 1202513351, rounds 33533328545, OS waits 959708531
Spin rounds per wait: 2208700138.00 RW-shared, 80631903713.00 RW-excl, 27.89 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-04-21 19:50:05 0x7fe28a7fd700
...
...
...
Process ID=54642, Main thread ID=140614440048384, state: sleeping
Number of rows inserted 5475421722, updated 433989820, deleted 4122238559, read 669572614313
708.29 inserts/s, 34.97 updates/s, 573.43 deletes/s, 29898.10 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
此內(nèi)容是InnoDB MONITOR的結(jié)果（和show engine innodb status結(jié)果一樣），也就是說開啟了InnoDB監(jiān)視器。而且每15s刷新一次，導(dǎo)致日志文件的增速較快。
InnoDB MONITOR主要涉及的參數(shù)有innodb_status_output 和innodb_status_output_locks ，這兩個(gè)系統(tǒng)變量是用來(lái)啟用標(biāo)準(zhǔn)innodb監(jiān)控和 InnoDB鎖監(jiān)控的，查看本實(shí)例參數(shù)情況：
mysql> show global variables like '%innodb_status%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output | ON |
| innodb_status_output_locks | ON |
+----------------------------+-------+
2 rows in set (0.01 sec)
說明確實(shí)已經(jīng)開啟了。
2、關(guān)閉INNODB MONITOR
可以在線關(guān)閉INNODB MONITOR，但是在關(guān)閉前建議先將原日志備份（重命名）
mv mysqld.log mysqld.log.20200708
再修改參數(shù)，關(guān)閉監(jiān)控
mysql> set global innodb_status_output='OFF';
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_status_output_locks='OFF';
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
時(shí)間有限，今天暫時(shí)就簡(jiǎn)單說明現(xiàn)象及引起日志暴漲的原因，后續(xù)有機(jī)會(huì)在關(guān)注日志內(nèi)容及相關(guān)參數(shù)意義。
