MySQL database firefighting: What should I do if the disk is full?

Summary: When the disk space is full, what will happen to MySQL? How should we deal with it?

This article is shared from the Huawei Cloud Community "160b845b8ec80c [MySQL Database Fire Fighting] ", the original author: Technical Torchbearer.

Most users habitually do not reserve a lot of space for the system disk when partitioning the disk. In fact, this is not a good habit. Because the system partition does not install only one operating system as we thought, most of the system partitions still carry the main application software installation tasks of the operating system. So what happens to MySQL when the disk space is full? How should we deal with it?

The main reason for MySQL database disk explosion

Big amount of data

One of the most common reasons for MySQL disk explosion is a sudden increase in the amount of data leading to a database explosion. This reason is often related to business. For example, the application generates a large amount of data in a short period of time. If a large amount of new data suddenly appears, it may also be caused by certain test cases or stress testing. The skyrocketing of database disks caused by this reason can be solved by clearing data, such as clearing data for a specific time period.

It is recommended to mark the data during stress testing or other automated testing in databases such as the test environment, and automatically clear the junk data generated by the test after the test is completed, so as to avoid the accumulation of disks that will cause the full disk to affect the use of the database.

At the same time, according to the daily use of the database to see whether the disk needs to be expanded, but the expansion can only ease, not cure.

Log file

MySQL generates a large number of log files during daily operation. These log files will also take up disk space. Over time, log files of hundreds of gigabytes may be generated.
MySQL log files mainly include the following:

• The bin log binary file is usually the log type that occupies the largest space, which is related to the usage of the database.
• Error The error log takes up a lot of space mainly due to the accumulation of time over a long period of time, and it is not cleared regularly. For example, a database that has been running for one or two years often generates a relatively large error log.
• Slow query log Slow query log usually does not take up too much space, but it will take up too much if it is not cleaned for a long time, and it needs to be cleaned regularly

Temporary Files

Temporary files are temporary files generated when the database is running. This kind of temporary files will be automatically released after the operation ends. However, if they are not released under certain abnormal conditions, they will cause serious consequences, such as short-term slow query statements. This kind of slow query execution time is huge, and the query data is a lot. MySQL temporarily stores the intermediate results in the temporary directory of the database when the memory is insufficient.

Disk full due to processing log files

It is easier to clean up the full disk caused by the log file. You can clean up the file directly under the premise of ensuring the backup and transfer of the file. Use commands such as echo'' >> host-XXXXX.err to clean up these log files directly.

Remember not to delete related log files directly

To view the disk usage of the current database server, you can use the following command

df -h

To view the file disk occupation in a folder, you can use du as a reference

# 切换到需要查看的路径下使用
du -hl

The full disk caused by the log file can be through the linux command

echo '' >> host-xxxxx.log

The disk is full due to the processing of temporary files

View the temporary directory configured by Mysql

Usually the temporary directory of MySQL is under the mysql/temp path. If you want to view the configured parameters, you can use the show variables like'tmpdir' SQL command

show variables like 'tmpdir'

Reasons for temporary files

The main reason for temporary files is that when MySQL is executing, when the memory cannot meet the storage requirements for some large data operations, MySQL will use temporary files, such as millions of data queried from a temporary table. The result set of a joint query, etc. Normally, the temporary files generated by MySQL are very small and will be released in time. However, some abnormal situations, such as slow queries, can cause a large number of temporary files to be generated in a short time.

The following is a case where a large number of files are generated in the temporary directory due to slow query.

Excessive generation of temporary files causes the disk to be full, causing a short-term database failure

The precursor of a database crash is often a large amount of IO in a short period of time, and the CPU skyrocketing. When the above signs appear, the database is not far away from the crash. This is the case below.

In this case, we found that the database suddenly skyrocketed at a certain point in time, and it was a continuous skyrocket. Corresponding to this, the number of disk IOs skyrocketed and memory usage skyrocketed. At this time, it can be basically judged that a large number of slow queries have appeared in the database in a short time.

Why does slow query cause a surge in disk IO? The reason is as mentioned above, if the temporary table or temporary data used by the database in the query is not enough to store the memory, the database will store the data on the disk. As the slow query becomes more and more, there will be concentrated trampling. The problem, the problem intensified, and eventually caused the database to freeze or crash.

In the above case, by viewing the database execution records, we found that there is indeed a backlog of slow queries. Use the following SQL query to query the current SQL status being executed

SELECT id, `state`, user,host,time,`INFO` FROM information_schema.processlist where 
state IS NOT NULL  and state <> "" ORDER BY time desc;

Above, you can see that the database is executing SQL for more than 10W seconds, and there are several, so it is not difficult to understand why the database has a short-term increase in CPU and IO.

The way to solve these problems is: kill the current slow query, this method is very useful, but it is necessary to record the query statement and the login account and machine used, in case you need to inquire.

The following statement can quickly generate batch kill statements.

SELECT concat('kill ', id, ';') FROM information_schema.processlist where user =
 'HispaceCMS' and  `COMMAND` = 'Query' and  state IS NOT NULL and state <> '' and DB is 
not null and time > 1000 ORDER BY time desc

After generation, it can be executed in batches. After executing the query, the disk usage dropped from 96% to about 50.

Click to follow, and learn about Huawei Cloud's fresh technology for the first time~