Hi all,

I have a dedicated CentOS server running MySQL. The disk space was filling up due to a large database. I checked the disk space using this command:

df -h

I also ran a command to find all files on the server that are greater than 20mb in size, in order to find the source of the disk usage. This confirmed it was a table in the database using all the disk space, the command I used is:

find / -type f -size +20000k -exec ls -lh {} \; | awk '{ print $NF ": " $5 }'

I then proceeded to delete 25 million records from this table in a MySQL database stored on the server, I was expecting a lot of disk space to be free after doing that, but when I run the command again it shows exactly the same disk usage as before.

I am guessing I need to restart either MySQL, Apache or the entire server for the disk space changes to be shown using "df -h". Is this true?

If so, should I expect it to take some time to restart MySQL because of cleaning up the database files?

I can't restart anything yet due to causing downtime for the website that is hosted on the server, but I will later when the traffic is low.

Thanks in advance

The rows are deleted, but the space is reserved for subsequent INSERT statements. To make the file smaller, look at MySQL's OPTIMISE-TABLE function.

The table is locked while it's being optimised, so you'll likely end up with some downtime.

My expertise is with Microsoft SQL Server, not MySQL, but professional database products generally don't give back disk space that they aren't using any more, even after rebooting, unless you specifically tell them to. The reasoning is that you'll probably want to store more data later, and releasing and reclaiming disk space will lead to fragmentation of the database, as it uses whatever disk space was available at the time it was needed. So instead, the space is held onto.

I found one apparent description of how to make MySQL release disk space but it sounds rather radical. Look at
http://forums.mysql.com/read.php?35,...886#msg-121886
and see whether it makes sense to you. If not, I suppose you should keep looking for more. But I do expect it doesn't just happen, you need to force it.

In Microsoft SQL Server, each "database" (or "catalog") has its own data files (with many tables in one file), so you can design your application to release disk space just by putting data in a separately created database and then dropping the database after use. The free space on the disk probably still contains the data, but if that's a problem (for confidentiality) then it's a separate one. MS also has separate commands to reorganise and release disk space in each database's files.

Thanks for the responses....


What about if I drop the table in question? Then I could just recreate it. Would that free up the space?

That is often the fastest way.

Thanks all, I dropped the table which freed up the space