Delete records does not reduce hard disk space

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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…

3

Thanks,

I got the idea. Just deleting will not free the disk space. Dropping partition is most suitable way.