MySQL Performance: MySQL => Delete records does not reduce hard disk space

Members

Help

Home

Home » Performance » MySQL » Delete records does not reduce hard disk space ( 1 from 1 votes

) 1 Vote(s)

Show: Today's Messages :: Show Polls :: Message Navigator

Delete records does not reduce hard disk space [message #3682]

Thu, 30 October 2008 01:59

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.

Report message to a moderator

Re: Delete records does not reduce hard disk space [message #3684 is a reply to message #3682 ]

Sat, 01 November 2008 03:24

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...

Report message to a moderator

Re: Delete records does not reduce hard disk space [message #3692 is a reply to message #3684 ]

Tue, 04 November 2008 07:41

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.

Report message to a moderator

Previous Topic:	ERROR 1041 (HY000) OutOfMemory
Next Topic:	MySQL performance

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

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

Total time taken to generate the page: 0.00461 seconds

.:: Contact :: Home ::.

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.