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

数据库 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 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;

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

责任编辑:姜华 来源: 今日头条
相关推荐

2023-03-29 08:24:30

2020-02-18 11:19:36

物联网病毒物联网IOT

2021-06-04 11:10:04

JavaScript开发代码

2016-11-22 19:54:56

点击率预估推荐算法广告

2023-10-12 08:54:20

Spring事务设置

2013-04-18 09:56:05

2021-05-11 11:51:15

飞机Wi-Fi通信

2024-01-08 08:35:28

闭包陷阱ReactHooks

2019-07-23 15:34:29

MySQL存储引擎

2021-07-30 07:28:16

伪类伪元素CSS

2017-11-24 11:10:38

区块链矿工分叉

2022-12-13 08:36:42

D-SMARTOracle数据库

2021-10-15 21:16:00

手机内存漏洞

2022-04-15 08:54:39

PythonAsync代码

2021-07-31 19:21:34

Python下划线数值

2018-05-08 08:46:47

Linux内存释放

2021-08-10 09:28:10

ViteES Modules Dev Server

2009-11-13 13:42:38

ADO.NET数据服务

2019-02-17 10:05:24

TCPSocket网络编程

2015-03-25 14:44:05

DNS苹果
点赞
收藏

51CTO技术栈公众号