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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
