MySQL错误日志文件突然暴涨的原因
小编:啊南 356阅读 2020.11.20
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 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 ============================
此内容是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)
说明确实已经开启了。
2. 关闭INNODB MONITOR可以在线关闭INNODB MONITOR,但是在关闭前建议先将原日志备份(重命名)
mv mysqld.log mysqld.log.20200708
再修改参数,关闭监控
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;
相关推荐
- MySQL|Aborted connection 日志分析 一 前言作为运维DBA,我们经常会在数据库的err.log中查看到如下种类的报错信息:[Warning] Aborted connection xx to db: db user: xxx host: hostname (Got an error reading communication packets)[Warning] Aborted connection xx to db:unconnected user: …
- 3DMAX提示和技巧 本主题标识使用 Civil View 的一些重要提示和技巧。常规使用屏幕分辨率至少为 1280x1024 的 Civil View。低于此分辨率时,一些面板将占用过多屏幕空间。 将视口设置为线框显示以达到最佳性能。 要尽可能简化用户界面,请在单个视口中工作并关闭 3ds Max 命令面…