What happens to MySQL when the disk space is full?

What happens to MySQL when the disk space is full?

Most users do not reserve much space for the system disk when partitioning the disk. In fact, this is not a good habit. Because system partitions do not just install an operating system as we imagine, most of the system partitions still carry the main application software installation tasks of the operating system. What happens to MySQL when the disk space is full? What should I do?

What will happen

When the disk space is full, MySQL cannot write any data, including writing data to the table and binlog, binlog-index, and other files.

Of course, because InnoDB can put dirty data in the memory first, it does not immediately show that it cannot be written, unless binlog is enabled, write requests will be blocked.

What happens when MySQL detects that the disk space is full? Here is a specific example:

What Will MySQL do when the disk is full?

Let's take a look at the official statement.

In fact, MySQL itself does not perform any operations. As the official document says, it only checks whether there is free space every minute and writes error logs once every 10 minutes.

However, when the disk is full, the binlog cannot be updated, and the redolog cannot be updated. Data in all bufferpool cannot be flushed. If the server is restarted or the instance is killed, this will inevitably cause data loss, which is almost certain. Therefore, it is best to release a certain amount of space before refreshing dirty data.

Why does the hang operation occur when the disk is full?

1. select

First, after experience and practical tests, the select operation will not be caused by the full disk, that is, all select operations will run normally.

2. insert

After a failed test, it is found that when the disk is full, it will not be stuck after the first insert, but will be stuck after n.

By checking the error log, it is found that the problem of getting stuck is related to disk flushing.

To verify whether the inference is correct, we set sync_binlog to 1. In this case, the first insert entry gets stuck, and an error is reported in errorlog to indicate that the binlog writing fails. It seems that the problem is indeed related to disk flushing.

Currently, three parameters are known to be related to disk flushing, namely sync_binlog, innodb_flush_log_tr_commit, and duoblewrite.

3. showslavestatus

After testing the slave database, the Operation will get stuck. This is mainly because the LOCK_active_mi lock needs to be obtained to execute showslavestatus, and then the mi-> data_lock, however, when the disk is full, the data in io_thread cannot be written to relaylog. As a result, io_thread holds the mi-> data_lock lock, which leads to a deadlock.

Therefore, when the disk is full, the showslavestatus operation will get stuck.

4. showstatus

The test is normal, but if the showslavestatus operation is performed first, the showstatus will also be stuck. This is because LOCK_status needs to be locked for showstatus execution, and LOCK_active_mi must be locked because the status contains slavestatus. If showslavestatus is restricted, the LOCK_active_mi lock will not be released by io_thread due to the mi-> data_lock deadlock problem. At this time, the showstatus and showslavestatus compete for the same LOCK_active_mi lock, which also forms a deadlock.

Therefore, if showslavestatus is executed first and then showstatus is executed when the disk is full, all the operations will get stuck.

What should I do?

So what should we do when we find that the disk space is full? we suggest:

Per minute: Check whether the space is released to write new data. When the remaining space is found, the data will continue to be written.

Every 10 minutes: If no space is found, a record is written into the log to report that the disk space is full (at this time, only a few bytes are enough ).

Increase the frequency of monitoring system detection to prevent recurrence;

Delete unnecessary files in time to release space;

If a thread is blocked because the disk is full, it can be killed first. It may work again when it is detected again in the next minute;

Some threads may be blocked because the disk is full, and other threads may be blocked. You can kill the thread that causes blocking, and other blocked threads will continue to work.

Exceptions

An exception is:

When the REPAIRTABLE or OPTIMIZETABLE operation is performed, or the index is updated in batches after LOADDATAINFILE or ALTERTABLE is executed, these operations create temporary files. During these operations, mysqld finds that the disk space is full, the related table will be marked as crashed and temporary files will be deleted (in addition to the ALTERTABLE operation, MySQL will discard ongoing operations, delete temporary files, and release disk space ).

Note: When the mysqld process is accidentally killed during execution of these commands, the generated temporary files will not be automatically deleted and need to be deleted manually to release the disk space.