On Mon, 04 Feb 2013 15:32:19 +0000, Gordan Bobic <gordan@bobich.net> wrote:
Hi,
I've just tried testing InnoDB page compression, and I'm sure I must be doing something wrong because the disk space usage in my database directory is the same before and after the compression.
To compress the tables I am using:
ALTER TABLE $table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=$size;
with $size I tried being 8KB and 4KB, and in each case "du -h" on the DB directory is the same as before the compression.
innodb_file_per_table is set.
SHOW CREATE TABLE afterwards shows the ROW_FORMAT and KEY_BLOCK_SIZE to be as per the ALTER TABLE statement.
1) Am I doing it wrong? 2) Is the InnoDB compression feature available in MariaDB? 3) Is the compression on by default transparently? 4) Is there any other explanation for lack of effect?
As a cross-check, I tried taking my biggest .ibd file and compressing it with "lzop -1" and that compressed it from 80MB down to 22MB, which doesn't sound right if the data in it was already compressed in any way.
Has anybody got any possible explanations?
D'oh! I hadn't realized that default for innodb_file_format was still Antelope. I thought it's Barracuda by now. Works lovely with that fixed. Apologies for the noise. Another question - is it possible to change the engine of just one partition on a partitioned table, without rebuilding the entire table? The ALTER TABLE ... PARTITION ... syntax doesn't seem to cover anything of the sort. Gordan