Redusing file system usage from large mysql table with no partition table

Ok, so you do not have table partition so when you want to do delete old data your file system is still used by large mysql table.

I will show you how to delete old data and clean your file system.

1. Backup your mysql database

#mysqldump --all-databases > mysql_30082013.dump

2. Delete old data from specific table. In my case I deleted data from table SystemEvents older than 5 days. In my case this means a reducing data for 95% of space.


mysql> DELETE FROM SystemEvents WHERE DeviceReportedTime <  DATE_SUB(NOW(),INTERVAL 5 DAY);

 3. So we deleted data from table but table is stil using file system. Mysql do not have automatic mechanism of optimising file system space when you delete data from table. Because of this we have to do myisamchk. In case that your use InnoDB tables, this utility wouldn't help you.

Small description from man pages of myisamchk command.

The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them.  myisamchk works with MyISAM tables

So what we are now doing repairing, checking and optimising of SystemEvents table. You will notice that every table is made of two files. One file is MYD and that is data part of table and second is MYI and that is index part of table.

MYI = index file
MYD = data file

So when we deleted data from data, we delete index from MYI file but MYD file did not change. With myisamchk data from MYD will synchronise with indexes from MYI file.


#cd /var/lib/mysql/database1
# myisamchk  -r SystemEvents.MYI
- recovering (with sort) MyISAM-table 'SystemEvents.MYI'
Data records: 1379674
- Fixing index 1
I after this, size of your MYD file should be much, much smaller.

Things that you should be aware of before you begin with myisamchk. Make shoore that you have enough space for this operation. During myisamchk file with extension TMD is created.

# ls |grep SystemEvents

SystemEvents.MYD
SystemEvents.MYI
SystemEvents.TMD

This is temporaly table created because what myisamchk is basically doing is this: create new TMD table, insert data from MYD with index from MYI and after this is done then do rename of that TMD table to MYD.

Size of this TMD table will depend of how much data you still have in MYD table. So if you table is 100GB big and you have left with 100MB space free it is almost certain that you wouldn't be able to do myisamchk repair. I will about how to solve this problem in my next post.