mysql block size_SettingMySQLINNODBCompressionKEY_BLOCK_SIZE

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.

mysql innodb compression

share|improve this question

asked Feb 9 '12 at 16:08

Nick 70110