Compression of B-Tree Pages
Because they are frequently updated, B-tree pages require special treatment. It is important to
minimize the number of times B-tree nodes are split, as well as to minimize the need to uncompress
and recompress their content.
One technique MySQL uses is to maintain some system information in the B-tree node in
uncompressed form, thus facilitating certain in-place updates. For example, this allows rows to be
delete-marked and deleted without any compression operation.
In addition, MySQL attempts to avoid unnecessary uncompression and recompression of index pages
when they are changed. Within each B-tree page, the system keeps an uncompressed “modification
log” to record changes made to the page. Updates and inserts of small records may be written to this
modification log without requiring the entire page to be completely reconstructed.
When the space for the modification log runs out, InnoDB uncompresses the page, applies the
changes and recompresses the page. If recompression fails (a situation known as a compression
failure), the B-tree nodes are split and the process is repeated until the update or insert succeeds.
To avoid frequent compression failures in write-intensive workloads, such as for OLTP
applications, MySQL sometimes reserves some empty space (padding) in the page, so that the
modification log fills up sooner and the page is recompressed while there is still enough room
to avoid splitting it. The amount of padding space left in each page varies as the system keeps
track of the frequency of page splits. On a busy server doing frequent writes to compressed
tables, you can adjust the innodb_compression_failure_threshold_pct , and
innodb_compression_pad_pct_max  configuration options to fine-tune this mechanism.
Generally, MySQL requires that each B-tree page in an InnoDB table can accommodate at
least two records. For compressed tables, this requirement has been relaxed. Leaf pages of
B-tree nodes (whether of the primary key or secondary indexes) only need to accommodate
one record, but that record must fit, in uncompressed form, in the per-page modification log. If
innodb_strict_mode  is ON, MySQL checks the maximum row size during CREATE TABLE
... zobacz całą notatkę