mysql keyblocksize_设置MySQL INNODB压缩KEY_BLOCK_SIZE

我最近开始使用允许压缩的Barracuda InnoDB /

MySQL表格式.

我通过运行压缩了我的一个表：

alter table pricing row_format = compressed,key_block_size = 8;

运行之后,我查看了压缩统计信息(我已经在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)

如果我将compress_ops_ok除以compress_ops,则为6352315/7029231 = 90.4％.我的理解是,基本上90.4％的页面从16 KB压缩到8KB,其余页面无法压缩2倍.

我已经读过这些压缩失败的页面损害了性能,但是成功压缩的90％以上应该会提高性能(通过降低I / O操作).是否有一个经验法则,应该压缩多少百分比的页面才能被认为是正常的？我的另一个选择可能就是禁用压缩.

我的净目标是减少I / O操作的数量,如果这会适得其反,我不想启用压缩.