XtraDB/InnoDB Storage Formats
The XtraDB/InnoDB storage engine supports four different table storage formats.
These are COMPACT, REDUNDANT, COMPRESSED and DYNAMIC, and they can be set with the ROW FORMAT option in the CREATE TABLE statement. COMPACT is the default format.
Compact is the default format, and suitable for general use.
Redundant is the old, non-compacted format supported by old versions of MySQL. It was replaced as the default in MySQL 5.0.3. It is recommended to set innodb_strict mode when using this format.
Dynamic tables contain records of a variable length, resulting in more efficient data storage than compact or redundant, especially for tables containing BLOBs, although less than the compressed format.
It can only be used with the newer XtraDB/Barracuda file format, and requires tables and indexes to be stored in their own tablespaces, so can only be enabled if the system variables innodb_file_per_table=1 and innodb_file_format=barracuda. It is also recommended to set innodb_strict mode when using this format.
The compressed format results in the smallest data size in most cases. It can only be used with the newer XtraDB/Barracuda file format, and requires tables and indexes to be stored in their own tablespaces, so can only be enabled if the system variables innodb_file_per_table=1 and innodb_file_format=barracuda. It is also recommended to set innodb_strict mode when using this format.
Using the compressed format also reduces the default KEY_BLOCK_SIZE. If KEY_BLOCK_SIZE is ommitted from the CREATE TABLE or ALTER TABLE statement, it will default to 8KB - usually it is 16 (see innodb_page_size). It is also possible to set KEY_BLOCK_SIZE to 1KB, 2KB, 4KB or 16KB. Setting to 16, the regular size, will usually result in minimal compression unless there are many long BLOB, TEXT or VARCHAR columns.
Note that specifying a KEY_BLOCK_SIZE in an InnoDB table definition will automatically result in the table being compressed - it is not necessary to specify
To avoid compressing and uncompressing pages too many times, XtraDB/InnoDB tries to keep in the buffer pool both compressed and uncompressed pages, when there is enough room. This results in a bigger cache. When there is not enough room, an adaptive LRU algorythm is used to decide whether compressed or uncompressed pages should be evicted from the buffer: for CPU-bound workloads, the compressed pages are evicted first; for I/O-bound workloads, the uncompressed pages are evicted first. Of course, when necessary, both the compressed and uncompressed version of the same data can be evicted from the buffer.
Also, XtraDB/InnoDB writes small changes to an uncompressed modification log. When the space in the modification log runs out, the page is uncompressed, changes are applied, and the page is recompressed again. This is done to avoid some unnecessary uncompressing and recompressing operations.
Sometimes a compression failure might happen, because the data has grown too much to fit the page. When this happens, the page (and the index node) is split into two different pages. This process can be repeated until the data fit the pages. This can be CPU-consuming on some busy servers which perform many write operations.
Before writing a compressed page into a data file, XtraDB/InnoDB writes it into the redo log. This ensures that the redo log can always be used to recover tables after a crash, even if the compression library is updated and some incompatibilities are introduced. But this also means that the redo log will grow faster and might need more space, or the frequency of checkpoints might need to increase.
INFORMATION_SCHEMA tables can be used to monitor the performances of XtraDB/InnoDB compressed tables:
- INNODB_CMP and INNODB_CMP_RESET
- INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET
- INNODB_CMPMEM and INNODB_CMPMEM_RESET
CREATE TABLE compressed_table (x INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;