Enabling InnoDB Compression on MySQL 5.5

sugarcrmdevelopers —  November 30, 2012


I got a quick little lesson this morning in a few MySQL internals while trying to enable InnoDB compression. On the surface, it seems pretty easy. The MySQL documentation just says you can do:

ALTER TABLE table ROW_FORMAT=COMPRESSED;

However, for me this did nothing. The size of the table didn’t change at all. After a bit of digging, I figured out that you have to set the default file format for InnoDB to Barracuda which has compression enabled. This is disabled by default, according to the documentation:

Although Oracle recommends using the Barracuda format for new tables where practical, in MySQL 5.5 the default file format is still Antelope, for maximum compatibility with replication configurations containing different MySQL releases.

Pretty sneaky, but easy enough to fixup:

set global variable innodb_file_format = Barracuda;

add it to your my.cnf (/etc/my.cnf on most distros):

...
innodb_file_format = Barracuda
...

After that, the ALTER TABLE converted the table and cut the size on disk in half. Very nice.

Since this table is rarely read, and even more rarely written to, I didn’t end up doing much by way of performance testing or tuning. However, I should also note blog entry I found that seems to indicate that performance degrades as the size of the buffer pool increases when compression is enabled. The workaround is to use multiple buffer pools. Fortunately, we’re already did this as a workaround for issues with slow DROP TABLE performance on older versions of 5.5 when innodb_file_per_table is enabled.