I recently started using the Barracuda InnoDB/MySQL table format which allows compression.
I compressed one of my tables by running:
alter table pricing row_format=compressed, key_block_size=8;
After I ran this I viewed the compression statistics (I had cleared them right before the ALTER TABLE):
mysql> select * from INFORMATION_SCHEMA.INNODB_CMP; +-----------+--------------+-----------------+---------------+----------------+-----------------+ | page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time | +-----------+--------------+-----------------+---------------+----------------+-----------------+ | 1024 | 0 | 0 | 0 | 0 | 0 | | 2048 | 0 | 0 | 0 | 0 | 0 | | 4096 | 0 | 0 | 0 | 0 | 0 | | 8192 | 7029231 | 6352315 | 1437 | 339708 | 41 | | 16384 | 0 | 0 | 0 | 0 | 0 | +-----------+--------------+-----------------+---------------+----------------+-----------------+ 5 rows in set (0.00 sec) mysql> select * from INFORMATION_SCHEMA.INNODB_CMPMEM; +-----------+------------+------------+----------------+-----------------+ | page_size | pages_used | pages_free | relocation_ops | relocation_time | +-----------+------------+------------+----------------+-----------------+ | 128 | 11214 | 0 | 8434571 | 2 | | 256 | 0 | 37 | 0 | 0 | | 512 | 0 | 34 | 0 | 0 | | 1024 | 0 | 2 | 0 | 0 | | 2048 | 0 | 141 | 0 | 0 | | 4096 | 0 | 298 | 96657 | 0 | | 8192 | 15133 | 0 | 4121178 | 5 | | 16384 | 0 | 0 | 0 | 0 | +-----------+------------+------------+----------------+-----------------+ 8 rows in set (0.00 sec)
If I divide compress_ops_ok by compress_ops, that's 6352315/7029231 = 90.4%. My understanding is that basically 90.4% of the pages compressed from 16 KB to 8KB, and the remainder were not able to compress down by 2x.
I have read that these pages that fail compression hurt performance, but the over 90% that compressed successfully should improve performance quite a bit (by lowering I/O ops). Is there a rule of thumb of what percentage of pages should compress for this to be considered OK? My other option would probably be to just disable compression.
My net goal is to reduce the number of I/O operations, and I don't want to enable compression if this is going to be counterproductive.
Best Answer
Even after running the compression, you still may not get the performance you are looking for. Why ?
InnoDB has the Buffer Pool to load data pages and index pages read to fulfill queries. When reading a table and its indexes for the first time, the compressed page must be uncompressed. In fact, you may have twice as much data in the buffer pool as a result of this.
Note how this is the case from the MySQL Documentation
If this duplication of data content is going on in the Buffer Pool, you need to increase innodb_buffer_pool_size by a small linear factor of the new compression rate. Here is how:
SCENARIO
key_block_size=8
8
is50.00%
of16
50.00%
of8G
is4G
innodb_buffer_pool_size
to12G
(8G
+4G
)key_block_size=4
4
is25.00%
of16
25.00%
of8G
is2G
innodb_buffer_pool_size
to10G
(8G
+2G
)key_block_size=2
2
is12.50%
of16
12.50%
of8G
is1G
innodb_buffer_pool_size
to9G
(8G
+1G
)key_block_size=1
1
is06.25%
of16
06.25%
of8G
is0.5G
(512M
)innodb_buffer_pool_size
to8704M
(8G
(8192M
) +512M
)MORAL OF THE STORY : The InnoDB Buffer Pool just needs additional breathing room when handling compressed data and index pages.