On Mon, Feb 04, 2013 at 03:32:19PM +0000, Gordan Bobic wrote:
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?
I don't see anything apparently wrong.
2) Is the InnoDB compression feature available in MariaDB?
Yes.
3) Is the compression on by default transparently?
No, one needs to enable it manually. The way you did it seems to be correct.
4) Is there any other explanation for lack of effect?
Can you do: 1. Run these (they reset the compression stats) select * from information_schema.INNODB_CMP_RESET; select * from information_schema.INNODB_CMPMEM_RESET; 2. create table tmp like $table; insert into tmp select * from $table; 3. select * from information_schema.INNODB_CMP; select * from information_schema.INNODB_CMPMEM; This will show whether innodb has actually tried to compress data.
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.
InnoDB does per-page compression, which makes it uncompetitive with what one gets when compressing the whole file. But usually, it is able to achieve some compression. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog