Mysql – Does freeing up disk space improve the performance of MySQL

MySQLoptimizationperformance

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?

Best Answer

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.

Related Question