InnoDB page compression--InnoDB页面压缩

最新推荐文章于 2023-07-11 19:00:21 发布

置顶阳光的毛毛

最新推荐文章于 2023-07-11 19:00:21 发布

阅读量384

点赞数

分类专栏： mysql 文章标签： mysql page compression

原文链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

14.9.2 InnoDB Page Compression

14.9.2 InnoDB页面压缩

InnoDB supports page-level compression for tables that reside in file-per-table tablespaces. This feature is referred to as Transparent Page Compression. Page compression is enabled by specifying the COMPRESSION attribute with CREATE TABLE or ALTER TABLE. Supported compression algorithms include Zlib and LZ4.

InnoDB支持驻留在file-per-table 空间中的表的页面级压缩。此功能称为透明页面压缩。通过CREATE TABLE或ALTER TABLE时指定COMPRESSION属性来启用页面压缩。支持的压缩算法包括Zlib和 LZ4。

Supported Platforms

Page compression requires sparse file and hole punching support. Page compression is supported on Windows with NTFS, and on the following subset of MySQL-supported Linux platforms where the kernel level provides hole punching support:

页面压缩需要稀疏文件和打孔支持。具有NTFS的Windows以及以下受MySQL支持的Linux平台的子集（在其中内核级别提供打孔支持）中支持页面压缩：

RHEL 7 and derived distributions that use kernel version 3.10.0-123 or higher
OEL 5.10 (UEK2) kernel version 2.6.39 or higher
OEL 6.5 (UEK3) kernel version 3.8.13 or higher
OEL 7.0 kernel version 3.8.13 or higher
SLE11 kernel version 3.0-x
SLE12 kernel version 3.12-x
OES11 kernel version 3.0-x
Ubuntu 14.0.4 LTS kernel version 3.13 or higher
Ubuntu 12.0.4 LTS kernel version 3.2 or higher
Debian 7 kernel version 3.2 or higher

Note

All of the available file systems for a given Linux distribution may not support hole punching.

给定Linux发行版的所有可用文件系统可能不支持打孔。

How Page Compression Works

页面压缩如何工作

When a page is written, it is compressed using the specified compression algorithm. The compressed data is written to disk, where the hole punching mechanism releases empty blocks from the end of the page. If compression fails, data is written out as-is.

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

Hole Punch Size on Linux

Linux上的打孔尺寸

On Linux systems, the file system block size is the unit size used for hole punching. Therefore, page compression only works if page data can be compressed to a size that is less than or equal to the InnoDB page size minus the file system block size. For example, if innodb_page_size=16K and the file system block size is 4K, page data must compress to less than or equal to 12K to make hole punching possible.

在Linux系统上，文件系统块大小是用于打孔的单位大小。因此，仅当页面数据可以压缩到小于或等于InnoDB页面大小减去文件系统块大小的大小时，页面压缩才起作用。例如，如果 innodb_page_size=16K文件系统块大小为4K，则页面数据必须压缩到小于或等于12K才能打孔。

Hole Punch Size on Windows

Windows上的打孔尺寸

On Windows systems, the underlying infrastructure for sparse files is based on NTFS compression. Hole punching size is the NTFS compression unit, which is 16 times the NTFS cluster size. Cluster sizes and their compression units are shown in the following table:

在Windows系统上，稀疏文件的基础结构基于NTFS压缩。打孔大小是NTFS压缩单位，是NTFS群集大小的16倍。下表显示了群集大小及其压缩单位：

Table 14.8 Windows NTFS Cluster Size and Compression Units

表14.8 Windows NTFS群集大小和压缩单位

Cluster Size	Compression Unit
512 Bytes	8 KB
1 KB	16 KB
2 KB	32 KB
4 KB	64 KB

Page compression on Windows systems only works if page data can be compressed to a size that is less than or equal to the InnoDB page size minus the compression unit size.

仅当页面数据可以压缩到小于或等于InnoDB页面大小减去压缩单位大小的大小时，Windows系统上的页面压缩才有效。

The default NTFS cluster size is 4KB, for which the compression unit size is 64KB. This means that page compression has no benefit for an out-of-the box Windows NTFS configuration, as the maximum innodb_page_size is also 64KB.

默认的NTFS群集大小为4KB，其压缩单位大小为64KB。这意味着页面压缩对于现成的Windows NTFS配置没有好处，因为最大配置 innodb_page_size也是64KB。

For page compression to work on Windows, the file system must be created with a cluster size smaller than 4K, and the innodb_page_size must be at least twice the size of the compression unit. For example, for page compression to work on Windows, you could build the file system with a cluster size of 512 Bytes (which has a compression unit of 8KB) and initialize InnoDB with an innodb_page_size value of 16K or greater.

为了使页面压缩在Windows上正常运行，必须以小于4K的群集大小创建文件系统，并且文件系统 innodb_page_size的大小至少应为压缩单位的两倍。例如，要使页面压缩在Windows上运行，您可以使用512字节的群集大小构建文件系统（其压缩单位为8KB），并InnoDB使用innodb_page_size16K或更大的值进行初始化。

Enabling Page Compression

启用页面压缩

To enable page compression, specify the COMPRESSION attribute in the CREATE TABLE statement. For example:

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

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

You can also enable page compression in an ALTER TABLE statement. However, ALTER TABLE ... COMPRESSION only updates the tablespace compression attribute. Writes to the tablespace that occur after setting the new compression algorithm use the new setting, but to apply the new compression algorithm to existing pages, you must rebuild the table using OPTIMIZE TABLE.

您还可以在ALTER TABLE语句中启用页面压缩。但是， ALTER TABLE ... COMPRESSION仅更新表空间压缩属性。在使用新设置写入设置新压缩算法后发生的表空间，但是要将新压缩算法应用于现有页面，必须使用重建表OPTIMIZE TABLE。

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

Disabling Page Compression

禁用页面压缩

To disable page compression, set COMPRESSION=None using ALTER TABLE. Writes to the tablespace that occur after setting COMPRESSION=None no longer use page compression. To uncompress existing pages, you must rebuild the table using OPTIMIZE TABLE after setting COMPRESSION=None.

要禁用页面压缩，请COMPRESSION=None使用设置 ALTER TABLE。写入设置后COMPRESSION=None不再使用页面压缩的表空间。要解压缩现有页面，设置重建表COMPRESSION=None后必须使用OPTIMIZE TABLE。

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

Page Compression Metadata

页面压缩元数据

Page compression metadata is found in the INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES table, in the following columns:

在INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 表的以下列中找到页面压缩元数据：

FS_BLOCK_SIZE: The file system block size, which is the unit size used for hole punching.

FS_BLOCK_SIZE：文件系统块大小，是用于打孔的单位大小。

FILE_SIZE: The apparent size of the file, which represents the maximum size of the file, uncompressed.

FILE_SIZE：文件的视在大小，代表未压缩的文件的最大大小。

ALLOCATED_SIZE: The actual size of the file, which is the amount of space allocated on disk.

ALLOCATED_SIZE：文件的实际大小，即磁盘上分配的空间量。

Note

On Unix-like systems, ls -l tablespace_name.ibd shows the apparent file size (equivalent to FILE_SIZE) in bytes. To view the actual amount of space allocated on disk (equivalent to ALLOCATED_SIZE), use du --block-size=1 tablespace_name.ibd. The --block-size=1 option prints the allocated space in bytes instead of blocks, so that it can be compared to ls -l output.

Use SHOW CREATE TABLE to view the current page compression setting (Zlib, Lz4, or None). A table may contain a mix of pages with different compression settings.

在类Unix系统上，以字节为单位显示表观文件大小（等于）。要查看磁盘上实际分配的空间量（等于），请使用。该选项以字节而不是块为单位打印分配的空间，以便可以将其与输出进行比较。 ls -l tablespace_name.ibdFILE_SIZEALLOCATED_SIZEdu --block-size=1 tablespace_name.ibd--block-size=1ls -l

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

In the following example, page compression metadata for the employees table is retrieved from the INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES table.

在以下示例中，从表中检索了employees表的页面压缩元数据 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 。

# 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_SYS_TABLESPACES mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='employees/employees'\G *************************** 1. row *************************** SPACE: 45 NAME: employees/employees FS_BLOCK_SIZE: 4096 FILE_SIZE: 23068672 ALLOCATED_SIZE: 19415040

Page compression metadata for the employees table shows that the apparent file size is 23068672 bytes while the actual file size (with page compression) is 19415040 bytes. The file system block size is 4096 bytes, which is the block size used for hole punching.

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

Identifying Tables Using Page Compression

使用页面压缩识别表

To identify tables for which page compression is enabled, you can query the INFORMATION_SCHEMA.TABLES CREATE_OPTIONS column for tables defined with the COMPRESSION attribute:

要标识启用了页面压缩的表，您可以查询该列中使用属性定义的表： INFORMATION_SCHEMA.TABLES CREATE_OPTIONSCOMPRESSION

SHOW CREATE TABLE also shows the COMPRESSION attribute, if used.

如果COMPRESSION使用的话，SHOW CREATE TABLE还会显示该属性。

Page Compression Limitations and Usage Notes

页面压缩限制和使用说明

Page compression is disabled if the file system block size (or compression unit size on Windows) * 2 > innodb_page_size.
Page compression is not supported for tables that reside in shared tablespaces, which include the system tablespace, the temporary tablespace, and general tablespaces.
Page compression is not supported for undo log tablespaces.
Page compression is not supported for redo log pages.
R-tree pages, which are used for spatial indexes, are not compressed.
Pages that belong to compressed tables (ROW_FORMAT=COMPRESSED) are left as-is.
During recovery, updated pages are written out in an uncompressed form.
Loading a page-compressed tablespace on a server that does not support the compression algorithm that was used causes an I/O error.
Before downgrading to an earlier version of MySQL that does not support page compression, uncompress the tables that use the page compression feature. To uncompress a table, run ALTER TABLE ... COMPRESSION=None and OPTIMIZE TABLE.
Page-compressed tablespaces can be copied between Linux and Windows servers if the compression algorithm that was used is available on both servers.
Preserving page compression when moving a page-compressed tablespace file from one host to another requires a utility that preserves sparse files.
Better page compression may be achieved on Fusion-io hardware with NVMFS than on other platforms, as NVMFS is designed to take advantage of punch hole functionality.
Using the page compression feature with a large InnoDB page size and relatively small file system block size could result in write amplification. For example, a maximum InnoDB page size of 64KB with a 4KB file system block size may improve compression but may also increase demand on the buffer pool, leading to increased I/O and potential write amplification.

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