Rechercher dans le manuel MySQL
15.9.1.1 Overview of Table Compression
Because processors and cache memories have increased in speed more than disk storage devices, many workloads are disk-bound. Data compression enables smaller database size, reduced I/O, and improved throughput, at the small cost of increased CPU utilization. Compression is especially valuable for read-intensive applications, on systems with enough RAM to keep frequently used data in memory.
An InnoDB
table created with
ROW_FORMAT=COMPRESSED
can use a smaller
page size on disk than the
configured innodb_page_size
value. Smaller pages require less I/O to read from and write to
disk, which is especially valuable for
SSD devices.
The compressed page size is specified through the
CREATE TABLE
or
ALTER TABLE
KEY_BLOCK_SIZE
parameter. The different page
size requires that the table be placed in a
file-per-table
tablespace or general
tablespace rather than in the
system tablespace,
as the system tablespace cannot store compressed tables. For
more information, see
Section 15.6.3.2, “File-Per-Table Tablespaces”, and
Section 15.6.3.3, “General Tablespaces”.
The level of compression is the same regardless of the
KEY_BLOCK_SIZE
value. As you specify smaller
values for KEY_BLOCK_SIZE
, you get the I/O
benefits of increasingly smaller pages. But if you specify a
value that is too small, there is additional overhead to
reorganize the pages when data values cannot be compressed
enough to fit multiple rows in each page. There is a hard limit
on how small KEY_BLOCK_SIZE
can be for a
table, based on the lengths of the key columns for each of its
indexes. Specify a value that is too small, and the
CREATE TABLE
or
ALTER TABLE
statement fails.
In the buffer pool, the compressed data is held in small pages,
with a page size based on the KEY_BLOCK_SIZE
value. For extracting or updating the column values, MySQL also
creates an uncompressed page in the buffer pool with the
uncompressed data. Within the buffer pool, any updates to the
uncompressed page are also re-written back to the equivalent
compressed page. You might need to size your buffer pool to
accommodate the additional data of both compressed and
uncompressed pages, although the uncompressed pages are
evicted from the buffer
pool when space is needed, and then uncompressed again on the
next access.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-innodb-compression-background.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.