MySQL日志文件突然暴涨是怎么回事？

1、日然暴现象

今天协助其他同学排查问题的志文涨时候，发现数据库错误日志文件已经有9G以上了，日然暴打开内容查看如下：

复制=====================================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 213360683OS WAIT ARRAY INFO: reservation count 218012898OS WAIT ARRAY INFO: reservation count 218624956OS WAIT ARRAY INFO: reservation count 223392430OS WAIT ARRAY INFO: reservation count 213358783OS WAIT ARRAY INFO: reservation count 217996917OS WAIT ARRAY INFO: reservation count 218627068OS WAIT ARRAY INFO: reservation count 223399094OS WAIT ARRAY INFO: reservation count 213372264OS WAIT ARRAY INFO: reservation count 217974752OS WAIT ARRAY INFO: reservation count 218606657OS WAIT ARRAY INFO: reservation count 223387430OS WAIT ARRAY INFO: reservation count 213382268OS WAIT ARRAY INFO: reservation count 218029924OS WAIT ARRAY INFO: reservation count 218619464OS WAIT ARRAY INFO: reservation count 223399870OS WAIT ARRAY INFO: signal count 2558329753RW-shared spins 0, rounds 2208700138, OS waits 822920663RW-excl spins 0, rounds 80631903713, OS waits 1603642807RW-sx spins 1202513351, rounds 33533328545, OS waits 959708531Spin 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 669572614313708.29 inserts/s, 34.97 updates/s, 573.43 deletes/s, 29898.10 reads/

s

----------------------------

END OF INNODB MONITOR OUTPUT

============================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.

此内容是InnoDB MONITOR的结果（和show engine innodb status结果一样），也就是日然暴说开启了InnoDB监视器。而且每15s刷新一次，站群服务器志文涨导致日志文件的日然暴增速较快。

InnoDB MONITOR主要涉及的志文涨参数有innodb_status_output 和innodb_status_output_locks ，这两个系统变量是日然暴用来启用标准innodb监控和 InnoDB锁监控的，查看本实例参数情况：

复制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)1.2.3.4.5.6.7.8.

说明确实已经开启了。志文涨

2、云服务器日然暴关闭INNODB MONITOR

可以在线关闭INNODB MONITOR，志文涨但是日然暴在关闭前建议先将原日志备份（重命名）

复制mv mysqld.log mysqld.log.202007081.

再修改参数，关闭监控

复制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;1.2.3.4.5.

时间有限，今天暂时就简单说明现象及引起日志暴涨的日然暴原因，后续有机会在关注日志内容及相关参数意义。