Re: How to stop a database growing in size on the disk

[Darren White]

I've tested this in practice now - seems Phillip Ward who posted earlier here was wrong.

Using MySql server 5.6 I found dropping and recreating partition DOES free space back to the operating system.

I had 60 tables (growing a row per second), monthly partitions, and a script to drop/recreate leaving only ever the last two months of data.

I disabled the script and let the tables grow to 14 million rows (over several months) - about 50GB. Then I ran a script to drop/recreate the partitions, ie. like this:

"...alter table log_01 drop partition p1;
alter table log_01 add partition ( partition p1 values in (1));..."

I watched this run and 14million rows in 60 tables were gone in about a second and I watched 50GB reappear in Windows explorer (Server 2008).