November 19, 2009 Leave a comment
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.