MySQL | InnoDB Page Compression

2024-04-25

#mysql #innodb

InnoDB Page Compression#

InnoDB 支持对驻留在 file-per-table 表空间中的表进行页面级压缩 page-level compression。

此功能称为透明页面压缩 Transparent Page Compression。

通过在 CREATE TABLE 或 ALTER TABLE 中指定 COMPRESSION 属性，可启用页面压缩。

支持的压缩算法包括 Zlib 和 LZ4。

How Page Compression Works#

写入页面时，会使用指定的压缩算法对其进行压缩。压缩后的数据被写入磁盘，打孔机制会释放页面末尾的空块。如果压缩失败，数据将按原样写出。

Hole Punch Size on Linux#

在 Linux 系统中，文件系统块大小是用于打孔的单位大小。

因此，只有当页面数据被压缩到小于或等于 InnoDB 页面大小减去文件系统块大小时，页面压缩才会起作用。

例如，如果 innodb_page_size=16K，而文件系统块大小为 4K，则页面数据必须压缩到小于或等于 12K 才能打孔。

Enabling Page Compression#

要启用页面压缩，请在 CREATE TABLE 语句中指定 COMPRESSION 属性。例如

CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";

也可以在 ALTER 表语句中启用页面压缩。

但是，ALTER TABLE ... COMPRESSION 只更新表空间压缩属性。

设置新压缩算法后向表空间的写入将使用新设置，但要将新压缩算法应用到现有页面，必须使用 OPTIMIZE TABLE 重建表。

ALTER TABLE t1 COMPRESSION="zlib";
OPTIMIZE TABLE t1;

出现 Table does not support optimize, doing recreate + analyze instead 是正常的，参考https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

Disabling Page Compression#

要禁用页面压缩，请使用 ALTER 表设置 COMPRESSION=None。

设置 COMPRESSION=None 后，写入表空间时不再使用页面压缩。

要解压缩现有页面，必须在设置 COMPRESSION=None 后使用 OPTIMIZE TABLE 重建表。

ALTER TABLE t1 COMPRESSION="None";
OPTIMIZE TABLE t1;

Page Compression Metadata#

页面压缩元数据可在 Information Schema. INNODB_TABLESPACES 表的以下列中找到：

FS_BLOCK_SIZE 文件系统块大小，即用于打孔的单位大小。
FILE_SIZE 文件的表观大小，表示未压缩文件的最大大小。
ALLOCATED_SIZE 文件的实际大小，即在磁盘上分配的空间大小

Note#

在类 Unix 系统上，ls -l tablespace_name.ibd 显示的文件大小（相当于 FILE_SIZE）的字节数。要查看磁盘上分配的实际空间大小（相当于 ALLOCATED_SIZE），请使用 du --block-size=1tablespace_name.ibd。选项 --block-size=1 会以字节而非块为单位打印分配的空间，以便与 ls -l 输出进行比较。

使用 SHOW CREATE TABLE 查看当前页面压缩设置（Zlib、Lz4 或无）。一个表可能包含不同压缩设置的混合页面。

在下面的示例中，将从 Information Schema. INNODB_TABLESPACES 表中获取 Employees 表的页面压缩元数据。

# Create the employees table with Zlib page compression

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
) COMPRESSION="zlib";

# Insert data (not shown)

# Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM
       INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='employees/employees'\G
*************************** 1. row ***************************
SPACE: 45
NAME: employees/employees
FS_BLOCK_SIZE: 4096
FILE_SIZE: 23068672
ALLOCATED_SIZE: 19415040

Employees 表的页面压缩元数据显示，表观文件大小为 23068672 字节，而实际文件大小（使用页面压缩）为 19415040 字节。文件系统块大小为4096字节，这是用于打洞的块大小。

Identifying Tables Using Page Compression#

要识别已启用页面压缩的表，可以检查 Information Schema.TABLES 表的 CREATE_OPTIONS 列中是否有使用 COMPRESSION 属性定义的表：

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
       WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
+------------+--------------+--------------------+
| employees  | test         | COMPRESSION="zlib" |
+------------+--------------+--------------------+

Page Compression Limitations and Usage Notes#

如果文件系统块大小（或 Windows 上的压缩单元大小）* 2 > innodb_page_size，则会禁用页面压缩。
共享表空间（包括系统表空间、临时表空间和常规表空间）中的表不支持页面压缩。
undo log tablespaces不支持页面压缩。
redo log pages不支持页面压缩。
用于空间索引的 R 树页面不会被压缩。
属于压缩表（ROW_FORMAT=COMPRESSED）的页面保持不变。
在恢复过程中，更新的页面会以未压缩的形式写出。
在不支持所用压缩算法的服务器上加载页面压缩的表空间会导致 I/O 错误。
在降级到不支持页面压缩的早期 MySQL 版本之前，请解压缩使用页面压缩功能的表。要解压缩表，请运行 ALTER TABLE ... COMPRESSION=None 和 OPTIMIZE TABLE。
如果使用的压缩算法在 Linux 和 Windows 服务器上都可用，则页面压缩的表空间可在 Linux 和 Windows 服务器之间复制。f
在将页面压缩的表空间文件从一台主机移动到另一台主机时，需要一个能保留稀疏文件的实用程序来保留页面压缩。
与其他平台相比，在使用 NVMFS 的 Fusion-io 硬件上可以实现更好的页面压缩效果，因为 NVMFS 就是为利用打孔功能而设计的。
使用页面压缩功能时，如果 InnoDB 页面大小较大，而文件系统块大小相对较小，可能会导致写放大。例如，最大 InnoDB 页面大小为 64KB，文件系统块大小为 4KB，这可能会提高压缩率，但也可能会增加对缓冲池的需求，导致 I/O 增加和潜在的写放大。