[SOLVED] Deleted 25 million records from database, had no effect on disk space

[the182guy]

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

[bigtomrodney]

It's normal for databases to stay the same size after you delete data. You would usually run a maintenance task to shrink the actual database file, but in terms of performance it doesn't make sense for the file to grow and shrink with every transaction. Even in terms of growth they tend to reserve a certain amount ahead of using it, say 5% of their current size.

Database files can be consider a sort of virtual partition in itself which manage their own space within that.

I think MySQL is a bit tricky in this regard. I'm not sure there is a built in shrink maintenance tool and you may in fact have to dump the data, drop/recreate the tables and then reload the data.

[the182guy]

Thanks, I dropped the table and rectreated which freed up the space.