Occasionally, you have a database table that holds a lot of text data and it’s not accessed regularly (log tables for example). InnoDB provides an easy way to compress your tables on disk so they use less space.

Prerequisites

The most important things are that innodb_file_per_table must be set in your /etc/my.cnf file. If you don’t have this enabled already you’ll need to perform a mysqldump on the database, add the setting, and then restore the database. I haven’t been able to find a way around this so it’s of limited use on production servers. The innodb_file_per_table is a good setting to have turned on anyways so I would suggest you enable it.

The second most important think is the Barracuda file format must be enabled when MySQL was built. I think the majority of the packaged versions of MySQL do (I know Ubuntu and CentOS do).

Process

To compress your table you need to run the following for each table you want to compress:

ALTER TABLE emaillog ROW_FORMAT=compressed;

Depending on the size of your table this may take a long time as it has to rewrite the whole table. During this process your table will be inaccessible to others so it’s best to do this off hours.

Downside

The downside to this process is that because the table is compressed accessing the data may take more time. In our tests we didn’t see an large difference (~2%) searching 2 million rows of email log data but your mileage may vary.

Even if you don’t do this on your production server I’ve done it on development machines to save space when that was a factor.