Flite Culture

Enabling InnoDB Compression on Tables With Heavy Rows

I’ve been interested in InnoDB compression for a while, and have been looking for a good use case at Flite where I could start using it.

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
SET GLOBAL innodb_file_format=BARRACUDA;

ALTER TABLE reg_version 
  ENGINE=InnoDB 
  ROW_FORMAT=COMPRESSED 
  KEY_BLOCK_SIZE=16;

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.

Comments

Comments