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.

link|improve this question

25% accept rate
feedback

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
or
required, but never shown

Browse other questions tagged or ask your own question.