maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

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