What happens to MySQL after disk space is full?

Most users are accustomed to partitioning the disk without reserving a lot of space for the system tray, which is not a good habit in fact. Because system partitioning is not as simple as we would like to install an operating system, most of the system partitions will still host operating system main application software installation tasks. So what happens to MySQL when disk space is full? And what should we do?

What's going to happen?

When disk space is full, MySQL cannot write any more data, including writing to the table data, and Binlog, Binlog-index, and so on.

Of course, because InnoDB can put dirty data in memory first, so it will not appear immediately unable to write, unless the Binlog is turned on, the write request will be blocked.

What happens when MySQL detects that disk space is full? Let's look at a specific example:

What does MySQL do after the disk is full?

Let's take a look at the official story.

In fact, MySQL itself does not do anything, such as the official document says, only check once per minute if there is free space, and 10 minutes to write the error log.

But again, because the disk is full, meaning that binlog can not be updated, Redolog can not be updated, all bufferpool data can not be flush, if the unfortunate server restarts, or the instance is killed, it will inevitably result in data loss, which is almost certain. Therefore, the problem of dealing with disk full is best to release a certain amount of space to let dirty data refresh.

The disk is full why does it cause the operation to hang?

1. Select

First of all, experience and actual testing, the select operation will not cause problems due to disk full, that is, all the select operation will work.

2. Insert

The test found that, when the disk is full, not the first insert will be stuck, but will appear after n stuck in the case.

By viewing the error log, it is found that there is a relationship between the stuck phenomenon and the operation of the brush disk.

In order to verify that the inference is correct, we set the Sync_binlog to 1, in which case the first insert is stuck, and the direct error in errorlog indicates that the write Binlog failed. It seems that the jammed is really related to the brush disk.

There are currently 3 parameters known to be related to the brush disk, namely Sync_binlog,innodb_flush_log_tr_commit, and Duoblewrite.

3, Showslavestatus

In the test from the library, the operation will be stuck, mainly because the execution showslavestatus need to obtain a lock_active_mi lock, and then lock the Mi->data_lock, but because the disk is full can not be io_ The data in thread is written to Relaylog, causing Io_thread to hold the Mi->data_lock lock, which results in a deadlock.

Therefore, this causes the showslavestatus operation to get stuck when the disk is full.

4, Showstatus

The test can operate normally, but if the showslavestatus operation is performed first, the showstatus will also be jammed. This is because the showstatus needs to lock the lock_status, and because the status state contains Slavestatus, it is necessary to lock the lock_active_mi. If Showslavestatus is restricted, the Io_thread will not release the LOCK_ACTIVE_MI lock due to the mi->data_lock deadlock problem. This caused showstatus and showslavestatus to scramble for the same lock_active_mi lock, which also formed a deadlock.

Therefore, in the case of disk full, if the first execution of Showslavestatus, after the execution of Showstatus, even an operation will be stuck.

What should I do

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

Per minute: Check that the space is freed to write new data. When there is space left, the data will continue to be written, as usual.

Every 10 minutes: if you still find no space left, a record is written in the log that reports that the disk is full (only a few bytes are written at this time).

Improve monitoring system detection frequency, prevent recurrence;

Delete unused files in time, free space;

If the thread due to disk full problem is blocked, can be killed first, wait for the next minute to re-detect it may be working again;

Some threads may be blocked due to disk full, causing other threads to be blocked, the thread that caused the blocking can be killed, and other blocked threads will be able to continue to work.

Exception

An exception to this situation is:

When you perform a repairtable or optimizetable operation, or when you bulk update the index after you finish Loaddatainfile or altertable, these operations create temporary files that mysqld discover when the disk space is full during these operations. It will mark the table as crashed, delete the temporary files (in addition to the altertable operation, MySQL will discard the operation being performed, remove temporary files, free disk space).

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

What happens to MySQL after disk space is full?