XtraDB/InnoDB Storage Formats

You are viewing an old version of this article. View the current version here.

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.

The SHOW TABLE STATUS statement can be used to see the storage format used by a table. Since MariaDB 10.0, this information is also in the Information Schema INNODB_SYS_TABLES table.

Compact

Compact is the default format, and suitable for general use.

Redundant

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

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.

Compressed

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 ROW_FORMAT=COMPRESSED.

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.

The following INFORMATION_SCHEMA tables can be used to monitor the performances of XtraDB/InnoDB compressed tables:

Example

CREATE TABLE compressed_table
 (x INT PRIMARY KEY) 
 ENGINE=InnoDB
 ROW_FORMAT=COMPRESSED 
 KEY_BLOCK_SIZE=4;

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.