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 InnoDB 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, InnoDB 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, the B-tree nodes are split and the process is repeated until the update or insert succeeds.
Generally, InnoDB requires that each B-tree page 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. Starting
with InnoDB storage engine version 1.0.2, and if InnoDB strict
mode is ON
, the InnoDB storage engine checks the
maximum row size during CREATE TABLE
or CREATE INDEX
. If
the row does not fit, the following error message is issued:
ERROR HY000: Too big row
.
If you create a table when InnoDB strict mode is OFF, and a
subsequent INSERT
or
UPDATE
command attempts to create an index
entry that does not fit in the size of the compressed page,
the operation fails with ERROR 42000: Row size too
large
. Unfortunately, this error message does not
name the index for which the record is too large, or mention
the length of the index record or the maximum record size on
that particular index page. The only remedy is to rebuild the
table with ALTER TABLE
, in order to select a larger
compressed page size (KEY_BLOCK_SIZE
), to shorten any column
prefix indexes, or to disable compression entirely with
ROW_FORMAT=DYNAMIC
or ROW_FORMAT=COMPACT
.
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .