Does freeing up disk space improve the performance of MySQL?

Question

We have a MySQL master server here which was not set up with any kind of management for the binary logs. Over time, the disk space (1 TB) was slowly filling up and it was noticed that the performance of the MySQL server was becoming worse. At the time, I didn't make any connection between disk space issues and performance. I took it upon myself to clean this up, backing up old binary logs and freeing up space on the server.

Now that the transition is (mostly) complete and the server has much more free disk space, I've noticed that the performance has increased. I don't have any numbers to back this up, but one thing that I have noticed is that certain transactions in some of our apps which used to time out on a semi-regular basis are now proceeding smoothly.

I can imagine a couple of explanations for the improved performance:

• The disk controller is not having to search as hard for free space
• MySQL is better able to optimize tables, indices, etc.

but I really don't know why. So I really have two questions:

1. Can a lack of free disk space impact MySQL performance?
2. If so, why?

Answer 1

Can a lack of free disk space impact MySQL performance?

Certainly a lack of disk space at all can prevent writes from happening. Especially when storage is beginning to become full (>90%, as a minimal ballpark) and the disks are fragmented, it can take some time to search where writes can even occur when MySQL is trying to write to disk.

This would be true of any database platform or any application that writes to disk. This isn't so much a MySQL behavior as it is a storage behavior.

My suspicion is that you have different qualities of storage disks available to you filesystem(s) that contain the MySQL data. As your server became more full, you would be more frequently subject to the performance of your worst disks in terms of IO.

After purging your server of old binlogs you've likely been able to increase your storage-tier's performance with IO because it takes a shorter amount of time to find places to write. Shorter time to find places to write means faster transaction performance. Faster transaction performance means less timeouts and noticeable delays in application functionality.

Answer 2

Is the space used by database table files? if so, yes it could affect performance if the query must read data from disk. That is why it is always a good idea to defrag tables to free up unused space. Again the performance will be depended on what kind of disk your sever is using.

Answer 3

Well, I disagree with most of what has been said so far in the Q&A.

The OS, not the disk controller, decides where to put the next block. The OS's algorithms are pretty linear. The is a slight bit of overhead for huge tables versus tiny tables.

Fragmentation always happens. The OS and controllers, especially RAID controllers, mitigate the issues. Defragmentation is usually more trouble than it is worth.

Enterprise SSD devices employ "wear leveling" to prevent wearing out the drive too soon. When the disk is "nearly full", this task can slow down the drive in general. There is no corresponding problem with spinning drives or cheap SSDs.

Binlogs should be removed. I like using something like expire_logs_days = 14 (2 weeks).

If you need to ALTER a table, and it needs to copy the table, then you do temporarily need enough disk space for an entire copy of the table. So, keep the disk free space larger than the largest table.

"MySQL is better able to optimize tables, indices, etc." -- Aside from ALTER, I see no rationale for this comment.