MySQL logs suddenly soared

Time:2021-3-4

1. Phenomenon

Today, when helping other students to check the problem, we found that the database error log file has more than 9g. Open the content to see as follows:

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

This content is the result of InnoDB monitor (the same as the result of show engine InnoDB status), that is, InnoDB monitor is turned on.

The main parameters involved are InnoDB_ status_ Output and InnoDB_ status_ output_ Lock, the two system variables are used to enable standard InnoDB monitoring and InnoDB lock monitoring

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)

That means it’s turned on.

 

2. Close InnoDB monitor

InnoDB monitor can be shut down online, but it is recommended to back up (rename) the original log before shutting down

mv  mysqld.log   mysqld.log.20200708

Then modify the parameters and turn off the monitoring

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;

Time is limited. Today, I will briefly explain the phenomenon and the reasons for the surge of logs, and I will have the opportunity to pay attention to the contents of logs and the significance of relevant parameters.

For more information or technical communication, you can focus on WeChat public database, dry cargo shop or official account.