Rechercher dans le manuel MySQL

15.9.2 InnoDB Page Compression

Inhaltsverzeichnis

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.

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:

  • 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.

Inhaltsverzeichnis Haut

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.

Inhaltsverzeichnis Haut

Hole Punch Size on 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.

Inhaltsverzeichnis Haut

Hole Punch Size on 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:

Table 15.15 Windows NTFS Cluster Size and Compression Units

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.

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.

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.

Inhaltsverzeichnis Haut

Enabling Page Compression

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

  1. 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.

  1. ALTER TABLE t1 COMPRESSION="zlib";
  2. OPTIMIZE TABLE t1;

Inhaltsverzeichnis Haut

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.

  1. ALTER TABLE t1 COMPRESSION="None";
  2. OPTIMIZE TABLE t1;

Inhaltsverzeichnis Haut

Page Compression Metadata

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

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

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

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

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.

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

  1. # Create the employees table with Zlib page compression
  2.  
  3. CREATE TABLE employees (
  4.     emp_no      INT             NOT NULL,
  5.     birth_date  DATE            NOT NULL,
  6.     first_name  VARCHAR(14)     NOT NULL,
  7.     last_name   VARCHAR(16)     NOT NULL,
  8.     gender      ENUM ('M','F')  NOT NULL,  
  9.     hire_date   DATE            NOT NULL,
  10.     PRIMARY KEY (emp_no)
  11. ) COMPRESSION="zlib";
  12.  
  13. # Insert data (not shown)
  14.  
  15. # Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES
  16.  
  17. mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM
  18.        INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='employees/employees'\G
  19. *************************** 1. row ***************************
  20. SPACE: 45
  21. NAME: employees/employees
  22. FS_BLOCK_SIZE: 4096
  23. FILE_SIZE: 23068672
  24. 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.

Inhaltsverzeichnis Haut

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:

  1. mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
  2.        WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
  3. +------------+--------------+--------------------+
  4. | TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
  5. +------------+--------------+--------------------+
  6. | employees  | test         | COMPRESSION="zlib" |
  7. +------------+--------------+--------------------+

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

Inhaltsverzeichnis Haut

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, temporary tablespaces, 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.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-innodb-page-compression.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut