mysql - MySQL 8 InnoDB 32KB 和 64KB 页面大小对 HDD 的好处

Question

MySQL页面大小文档说：

对于 MySQL 5.5 及之前的版本，每个 InnoDB 页面的大小固定为 16 KB。这个值代表了一种平衡：足够大以容纳大多数行的数据，但又足够小以最小化将不需要的数据传输到内存的性能开销。其他值未经测试或支持。

从 MySQL 5.6 开始，InnoDB 实例的页面大小可以是 4KB、8KB 或 16KB，由 innodb_page_size 配置选项控制。从 MySQL 5.7.6 开始，InnoDB 还支持 32KB 和 64KB页面大小。对于32KB 和 64KB的页面大小，不支持 ROW_FORMAT=COMPRESSED，最大记录大小为 16KB。

接着

较小的页面大小可以提高使用小块大小的存储设备的性能，特别是对于磁盘绑定工作负载中的 SSD 设备，例如 OLTP 应用程序。随着个别行的更新，更少的数据被复制到内存中、写入磁盘、重新组织、锁定等等。

使用比默认 16KB 更大（32KB 或 64KB）的页面大小如何？在什么情况下你应该这样做，你会得到什么好处？

我将使用传统 HDD 设置一个新的 MySQL 实例，我想知道更改默认 16KB 是否会对性能和存储利用率产生影响。

到目前为止，我只发现了使用 32KB 和 64KB 页面大小的缺点：

对于 32KB 和 64KB 的页面大小，不支持 ROW_FORMAT=COMPRESSED，最大记录大小为 16KB。

标签： mysqlinnodbmysql-8.0page-size

Answer 1

有些人达到了大约 8000 字节的最大行大小。切换到 32KB 页面会使该限制翻倍。但是，切换到 64KB 不会超过 16KB 的限制。

由于 InnoDB 块往往分散在磁盘周围，因此拥有更大的块将稍微节省 HDD 上的手臂运动。我希望这只是个位数的百分比改进。它会根据活动的类型而有所不同。一张新装的桌子可能没有任何改善；有很多流失的表可能会显示一些。

如果您的数据集适合buffer_pool，则无需执行太多 I/O，因此块大小不会发挥太大作用。

优化磁盘操作顺序的驱动程序或控制器在一定程度上降低了手臂运动的成本。带有缓存的 RAID 做得特别好，它可以使写入几乎是即时的。“打孔”可能会增加手臂运动的频率。经典权衡：“速度与空间”。

如果您的数据集太大而无法放入 RAM，并且如果您执行大量“点查询”，那么较小的块大小会更好。但是，如果您进行大量表（或索引）扫描，那么较大的块大小会带来一点好处。

请记住，所有数据必须使用相同的块大小。

在我见过的数千个论坛问题中，我认为没有任何改变块大小。你将置身于未知的水域。

另请注意，虽然ROW_FORMAT=COMPRESSED节省了一些磁盘空间，但它会占用一些 RAM - 这是因为一些（全部？）块都保存在 RAM 中，既压缩又未压缩。

我看到的 row_format 的数字只有 50%。任何体面的压缩算法几乎可以将任何类型的文本压缩大约 2/3。因此，如果我想使用压缩，我会压缩可压缩的列（例如TEXT，但不是 jpgs），并在客户端中执行此操作，从而减轻服务器的 CPU 工作量。我相信（没有任何确凿证据）这是压缩数据的更好方法。另外，我几乎从不使用BIGINT.

（我在这里所说的一切都是理论上的，基于非常有限的 MySQL 文档或 HDD 的原理。）

我有一个优化的经验法则。“如果粗略计算估计改进不到 10%，那么继续前进——寻找其他可以优化的东西。”

所以，我说“继续”。