MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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
usual 16KB default. Smaller pages require less I/O to read from
and write to disk, which is especially valuable for
SSD devices.
The page size is specified through the
KEY_BLOCK_SIZE
parameter. The different page
size means the table must be in its own .ibd
file rather than in the
system tablespace,
which requires enabling the
innodb_file_per_table
option. 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 a 16KB 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.