Home » Performance » MySQL » Delete records does not reduce hard disk space () 1 Vote(s)
icon3.gif  Delete records does not reduce hard disk space [message #3682] Thu, 30 October 2008 01:59 Go to next message
cowboymathu  is currently offline cowboymathu
Messages: 7
Registered: October 2008
Junior Member
Hi All,

I am using MySQL 5.122, MyISAM partitioned table. Currently this table holds one month records with 120 GB hard disk space. So it takes 4 GB for one day records roughly. In this instance, when I delete 5 days records still the consumed HD capacity by this table is same. After I repaired the table only the HD capacity is freed by 20 GB (5 x 4 GB). So every time when I insert more records, I should have to repair the table to free the HD space.

Can someone please help me ASAP?

Thanks in advance.
Re: Delete records does not reduce hard disk space [message #3684 is a reply to message #3682 ] Sat, 01 November 2008 03:24 Go to previous messageGo to next message
sdussart  is currently offline sdussart
Messages: 1
Registered: November 2008
Location: FRANCE IDF
Junior Member
It is normal that you do not recover your space using DELETE.

You tell using Partitioning. It is a good thing. But using the DELETE instruction is not the best way to take advantage of partitioning.

If you want to get rid of your 5 days of data, you should then partition by day. And then DROP the corresponding partitions.

You will then see that :
1) you will recover your space without reoganizing the table
2) your deletion will be far more efficient and quick!

Moreover, do not focus on recovering this 20Go of space every week.
Endeed, as you still have a constant space :
1) Week1 - Day 5, you have 150Go of disk usage
2) Week1 - Day 6, you delete your five days
3) Week1 - Day 7, you reorganize the table to get down to 120Go
4) Week2 - Day 1 to 5, you grow until 150Go
And so on ....

Stop struggeling for this space : let your DB size be 150Go! The space left by the DELETE statement is not completely "lost" : it will used again for the next INSERT statements...




Re: Delete records does not reduce hard disk space [message #3692 is a reply to message #3684 ] Tue, 04 November 2008 07:41 Go to previous message
cowboymathu  is currently offline cowboymathu
Messages: 7
Registered: October 2008
Junior Member
Thanks,

I got the idea. Just deleting will not free the disk space. Dropping partition is most suitable way.
Previous Topic:ERROR 1041 (HY000) OutOfMemory
Next Topic:MySQL performance
Goto Forum:
  


Current Time: Thu Jul 9 21:43:02 EDT 2009

Total time taken to generate the page: 0.00461 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.