Compress database data with Mysql and Innodb plugin

Install a specific version of Mysql 5.1 that is binary compatible with the version of innodb plugin. They must match to the last digit in the version number!

Edit my.ini (of my.cnf if on Linux)

Comment out this row if it is InnoDb (and exists):

#default-storage-engine=INNODB

Make sure the default Innodb code delivered with Mysql is deactivated:

skip-innodb

Follow the installation instructions on http://www.innodb.com/doc/innodb_plugin-1.0/innodb-plugin-installation.html

Add the following and restart mysql

innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1

It is now possible to create a new table that is compressed with innodb plugin:

CREATE TABLE MYTABLE (
  TM timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  COMMAND varchar(20) DEFAULT NULL,
) ENGINE=innodb ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

Don't forget to benchmark! The KEY_BLOCK_SIZE can be 1, 2, 4, 8 and 16. I got the best compression with 2 for my data. The other sizes were not so good. Some values even made the table bigger then uncompressed!

Overall conclusion: The Innodb plugin compression can be useful for some. But it might be useful to look into other compression techniques as well (for example compress data in a single TEXT/BLOB column on the client).