Enabling InnoDB Compression on Tables With Heavy Rows
Recently I found a perfect use case to start using InnoDB compression on a single table. This table gets a few hundred inserts per day, and each row stores up to 350 KB of serialized JSON in a LONGTEXT column.
Since I use
innodb_file_per_table on MySQL 5.5, compressing the table was easy. I just executed 2 lines of DDL:
1 2 3 4 5 6
Of course I also added
innodb_file_format = BARRACUDA to
/etc/my.cnf to make the change permanent.
From my understanding, this change means that any newly created InnoDB tables will use the
BARRACUDA format, and all pre-existing tables will still be in
ANTELOPE format. I don’t think the format will matter to me unless I want to compress another table, but I’m not sure if rebuilding an existing table via
OPTIMIZE TABLE or
ALTER TABLE would cause it to be rebuilt in
BARRACUDA format or not.
The table is about 80% smaller on disk now that it is compressed, so this change is working as expected for me.