InnoDB Compression

I had a few hours to spare a couple of days ago and decided to check InnoDB Plugin 1.0’s support for data compression.

In a project I work on from time to time, there is a table which contains three blobs which contains text data. To store the data efficiently I was using the COMPRESS() function in MySQL and doing a “CONVERT(UNCOMPRESS(text) AS utf8)” to uncompress the data and present it as utf8. No problems there, but with the recent move to the InnoDB Plugin 1.0 in MySQL 5.1 there was an opportunity to push that down the stack.

I ran a few benchmarks and it turned out that using 8K pages was the optimal trade-off between space and time. Using 16K pages did not compress the data very well, and using pages smaller than 8K increased the time needed to store the data. I should note that 8K is also the default.

There are some interesting wrinkles in all this, innodb_file_per_table needs to be enabled and I think the innodb_file_format needs to be set to ‘barracuda’ thought I am not sure about that.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: