Rechercher dans le manuel MySQL
15.9.2 InnoDB Page Compression
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
All of the available file systems for a given Linux distribution may not support hole punching.
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
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.
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.
Enabling Page Compression
To enable page compression, specify the
COMPRESSION
attribute in the
CREATE TABLE
statement. For
example:
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
.
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
.
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.
On Unix-like systems, ls -l
shows
the apparent file size (equivalent to
tablespace_name.ibd
FILE_SIZE
) in bytes. To view the actual
amount of space allocated on disk (equivalent to
ALLOCATED_SIZE
), use du
--block-size=1
. The
tablespace_name.ibd
--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.
- # Create the employees table with Zlib page compression
- ) COMPRESSION="zlib";
- # Insert data (not shown)
- # Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES
- *************************** 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.
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:
- +------------+--------------+--------------------+
- | TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS |
- +------------+--------------+--------------------+
- | employees | test | COMPRESSION="zlib" |
- +------------+--------------+--------------------+
SHOW CREATE TABLE
also shows the
COMPRESSION
attribute, if used.
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
andOPTIMIZE 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 maximumInnoDB
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.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-page-compression.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.