Abstract: What happens to MySQL when disk space is full? What should we do about it?

This article is shared by huawei cloud community “[MySQL database fire] – disk suddenly full handling method”, original author: technology torch bearer.

Most users habitually do not reserve large space for system disks when partitioning disks, which is not a good habit. Since system partitions do not install only an operating system as we might think, they still host most of the operating system’s main application installation tasks. So what happens to MySQL when disk space is full? What should we do about it?

The main cause of the disk explosion of the MySQL database

Large amount of data

One of the most common reasons for MySQL disk explosion is that the database explodes due to the sudden increase of data volume, which is often related to services. For example, an application may have a large amount of data in a short period of time. If a large amount of data is added suddenly, it may be due to some test case or pressure test. Database disk inflation caused by this problem can be resolved by cleaning up the data, such as clearing the data of a specific time period.

It is recommended to mark the data in the test environment when performing pressure tests or other automated tests. After the test is completed, the garbage data generated during the test is automatically cleared to avoid disk overcrowding day by day and affecting the database.

At the same time, determine whether to expand the disk capacity based on the daily use of the database. However, expanding the disk capacity only alleviates the problem, but does not cure the problem.

The log file

During the daily running of MySQL, a large number of log files are generated. These log files occupy disk space and may generate hundreds of GB of log files over a long period of time.

MySQL log files have the following types:

  • The bin log binary is usually the most space-intensive log type, depending on database usage.
  • Error Logs occupy a large space because they accumulate over a long period of time and are not cleared regularly. For example, a database that has been running for one or two years often generates large error logs.
  • Slow Query Logs Slow query logs do not occupy too much space. However, logs that are not cleared for a long time may occupy too much space. You need to clear logs periodically

The temporary file

Temporary files are temporary files generated when the database is running. Such temporary files will be automatically released after the operation. However, in some abnormal cases, if not released, serious consequences will be caused, such as a short slow query statement, which takes a long time to execute and contains a lot of data. MySQL temporarily stores intermediate results in a temporary directory in the database when memory is low.

Disk overflow caused by processing log files. Procedure

It is easy to clear disk overflow caused by log files. You can directly clear files on the premise that files are backed up and saved. You can run the echo ‘>> host-xxxxx. err command to clear these log files.

Do not delete the related log files directly

To check the disk usage of the database server, run the following command

You can use the DU directive to check the disk usage of files in a folder

# Switch to the desired path and use du-hlCopy the code

You can run the Linux command to cause disk overflow caused by log files

echo '' >> host-xxxxx.log  
Copy the code

Disk overflow caused by processing temporary files

View the temporary directory for Mysql configuration

The MySQL temporary directory is normally in the MySQL /temp directory. To view the configured parameters, run the show variableslike ‘tmpdir’ SQL command

show variables like 'tmpdir'
Copy the code

Causes of temporary files

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

Here is an example of a slow query resulting in a large number of files in a temporary directory.

Too many temporary files are generated. As a result, the disk is full and the database fails for a short time

The precursor of database crash is often a large amount of IO in a short time, CPU skyrocketing. When the above symptoms occur, the database is not far from crashing, as is the case below.

In this case, we found that at a certain point in time, the database CPU suddenly skyrocketed, and it continued to do so, and the disk I/O times increased, and the memory usage increased. At this time the basic can judge the database appeared a short time a large number of slow queries.

Why does slow query result in disk I/O Explosion? The reason is that the above mentioned, the database in the query of the use of temporary tables or temporary data if the memory is not enough to store the time, the database will be stored in the disk of these data, with more and more slow query will appear centralized stampede problem, the problem is getting worse, and eventually lead to database stuck or crash.

In the above example, by looking at the database execution record, we found that there was indeed a slow query backlog. The following SQL query can be used to query the status of the SQL currently being executed

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

Above, you can see that the database is executing SQL for more than 10W seconds, and there are several of them, so it’s not hard to understand why the database is experiencing short CPU and IO spikes.

The solution to these problems is to kill the current slow query. This works well, but make a note of the query statement and the login account and machine used for the query.

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 descCopy the code

After the generation of batch execution can be. After the query, the disk usage decreases from 96% to about 50.

Click to follow, the first time to learn about Huawei cloud fresh technology ~