Compress MySQL Tables

I got this long running task that (fsck!) died almost at the end of the job, and it logs its operation in a MySQL table.

Since I want to keep those "partial" results for further inspection (even after I'll fix and let the tool run again until the end) I don't want to waste disk space (it's ~650mb), so why don't compress that table?

With MySQL (and MyISAM tables like mine), it's pretty easy: go into the directory of the db schema:

# cd /var/lib/mysql/tabs_compare/
# ls -l
total 659500
-rw-rw---- 1 mysql mysql        65 2008-09-21 00:35 db.opt
-rw-rw---- 1 mysql mysql     12840 2008-12-25 00:44 log_1230162297.frm
-rw-rw---- 1 mysql mysql 675299244 2009-01-01 20:34 log_1230162297.MYD
-rw-rw---- 1 mysql mysql      1024 2009-01-01 20:37 log_1230162297.MYI

Pretty big :) Now, check the table:

# myisamchk log_1230162297.MYI
Checking MyISAM file: log_1230162297.MYI
Data records: 4293747   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check record links

Hopefully there is no error, so we can compress that table:

# myisampack log_1230162297.MYI
Compressing log_1230162297.MYD: (4293747 records)
- Calculating statistics
- Compressing file
43.05%

And the result it's really nice:

# ls -l
total 375572
-rw-rw---- 1 mysql mysql        65 2008-09-21 00:35 db.opt
-rw-rw---- 1 mysql mysql     12840 2008-12-25 00:44 log_1230162297.frm
-rw-rw---- 1 mysql mysql 384558282 2009-01-01 20:34 log_1230162297.MYD
-rw-rw---- 1 mysql mysql      1024 2009-01-01 20:49 log_1230162297.MYI

The compress command didn't even take a full core on my machine and almost took 20secs to complete.

Now rebuild indexes (if any) and recheck the table to be error free:

# myisamchk -r log_1230162297.MYI
...
# myisamchk log_1230162297.MYI
...

now tell MySQL to reload those table with:

mysql> flush tables <schema>;

Keep always in mind that compressed tables are read-only, so no DML commands, while DDL are allowed (so you can DROP it, for example).