Improving MySQL out of disk space behaviour

Running out of disk space is something which, of course, should never happen as we all setup monitoring and alerting and only run well behaved applications. But when it does happen we want things to fail gracefully.

So what happens when mysqld runs out of disk space?
The answer is: It depends

1. It might start to wait until disk space becomes available.
2. It might crash intentionally after a 'long semaphore wait'
3. It might return an error to the client (e.g. 'table full')
4. It might skip writing to the binlog (see binlog_error_action )

What actually happens might depend on the filesystem and OS.

Fixing the disk space issue can be done by adding more space or cleaning up some space. The later can often be done without help of the administrator of the system.

So I wanted to change the behaviour so that it MySQL wouldn't crash or stop to respond to read queries. And to also make it possible for a user of the system to cleanup data to get back to a normal state.

So I wrote a audit plugin which does this:

1. The DBA sets the maxdiskusage_minfree variable to a threshold for the minimum amount of MB free.
2. If the amount of free disk space goes under this threshold:
1. Allow everything for users with the SUPER privilege
2. Allow SELECT and DELETE
3. Disallow INSERT
3. If the amount of free space goes back to normal: Allow everything again

This works, but only if you delete data and then run optimize table to actually make the free space available for the OS.

Note that DELETE can actually increase disk usage because of binlogs, undo, etc.

The code is available on github: https://github.com/dveeden/mysql_maxdiskusage