☰

设想

tag-icon • tag-icon mysql innodb compression
设想

我最近开始使用允许压缩的 Barracuda InnoDB/MySQL 表格式。

我通过运行以下命令压缩了我的一个表：

alter table pricing row_format=compressed, key_block_size=8;

运行此命令后，我查看了压缩统计数据（我在 ALTER TABLE 之前清除了它们）：

mysql>从 INFORMATION_SCHEMA.INNODB_CMP 中选择*；
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 页面大小 | 压缩操作 | 压缩操作ok | 压缩时间 | 解压缩操作 | 解压缩时间 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 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 行 (0.00 秒)

mysql>从 INFORMATION_SCHEMA.INNODB_CMPMEM 中选择*；
+-----------+------------+------------+----------------+-----------------+
| 页面大小 | 已用页面数 | 空闲页面数 | 重定位操作 | 重定位时间 |
+-----------+------------+------------+----------------+-----------------+
| 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 行 (0.00 秒)

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

我读到过，这些压缩失败的页面会影响性能，但成功压缩的 90% 以上应该会大大提高性能（通过降低 I/O 操作）。有没有一个经验法则可以确定应该压缩多少百分比的页面才能算正常？我的另一个选择可能是禁用压缩。

我的最终目标是减少 I/O 操作的数量，如果这会适得其反，我就不想启用压缩。

答案1

即使运行了压缩，您仍然可能无法获得所需的性能。为什么？

InnoDB 具有缓冲池来加载数据页和索引页以完成查询。首次读取表及其索引时，必须解压缩压缩页。事实上，因此缓冲池中的数据量可能会增加一倍。

请注意，从MySQL 文档

压缩和 InnoDB 缓冲池

在压缩的 InnoDB 表中，每个压缩页面（无论是 1K、2K、4K 还是 8K）都对应一个 16K 字节的未压缩页面。要访问页面中的数据，如果压缩页面尚未位于缓冲池中，InnoDB 会从磁盘读取该页面，然后将该页面解压缩为其原始的 16K 字节形式。本节介绍 InnoDB 如何管理与压缩表页面相关的缓冲池。

为了最大限度地减少 I/O 并减少对页面进行解压的需要，缓冲池有时会同时包含数据库页面的压缩和未压缩形式。为了给其他所需的数据库页面腾出空间，InnoDB 可能会从缓冲池中“逐出”未压缩的页面，同时将压缩页面留在内存中。或者，如果某个页面在一段时间内未被访问，则可能会将该页面的压缩形式写入磁盘，以释放空间用于其他数据。因此，在任何给定时间，缓冲池可能同时包含页面的压缩和未压缩形式，或仅包含页面的压缩形式，或两者都不包含。

InnoDB 使用最近最少使用 (LRU) 列表来跟踪哪些页面应保留在内存中以及哪些页面应被逐出，这样“热门”或经常访问的数据就会倾向于留在内存中。访问压缩表时，InnoDB 使用自适应 LRU 算法来实现内存中压缩和未压缩页面的适当平衡。此自适应算法对系统是以 I/O 密集型还是 CPU 密集型方式运行很敏感。目标是避免在 CPU 繁忙时花费太多处理时间解压缩页面，并避免在 CPU 有空闲周期可用于解压缩压缩页面（可能已在内存中）时执行过多的 I/O。当系统是 I/O 密集型时，算法倾向于逐出页面的未压缩副本而不是两个副本，以便为其他磁盘页面驻留在内存中腾出更多空间。当系统受到 CPU 限制时，InnoDB 倾向于驱逐压缩和未压缩的页面，以便可以为“热”页面使用更多的内存，并减少仅以压缩形式解压缩内存中的数据的需要。

如果缓冲池中存在数据内容重复，则需要增加innodb_buffer_pool_size通过新压缩率的一个小线性因子。方法如下：

设想

  • 您有一个具有 8G 缓冲池的数据库服务器
  • 您使用以下方式进行压缩key_block_size=8
    • 8是50.00%​16
    • 50.00%的8G是4G
    • 提升innodb_buffer_pool_size至12G( 8G+ 4G)
  • 您使用以下方式进行压缩key_block_size=4
    • 4是25.00%​16
    • 25.00%的8G是2G
    • 提升innodb_buffer_pool_size至10G( 8G+ 2G)
  • 您使用以下方式进行压缩key_block_size=2
    • 2是12.50%​16
    • 12.50%的8G是1G
    • 提升innodb_buffer_pool_size至9G( 8G+ 1G)
  • 您使用以下方式进行压缩key_block_size=1
    • 1是06.25%​16
    • 06.25%的8G是0.5G( 512M)
    • 提升innodb_buffer_pool_size至8704M( 8G( 8192M) + 512M)

故事的道德启示：InnoDB 缓冲池在处理压缩数据和索引页时需要额外的喘息空间。

答案2

这些数据是从 ALTER TABLE 中收集的，ALTER TABLE 是一个不常用的语句，它会重写整个表。重要的是您的日常工作量，即应用程序在生产环境中执行的所有 INSERTS 和 UPDATES。根据 MySQL 手册：

“您可能会关闭表的压缩功能，这会导致应用程序中的“压缩失败”次数超过总数的 1％ 或 2％。（在数据加载等临时操作期间，这样的失败率可能是可以接受的）。”

相关内容