[Maria-developers] InnoDB Compression
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? TIA Gordan
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
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
participants (2)
-
Gordan Bobic -
Sergei Petrunia