InnoDB Storage Formats

The InnoDB storage engine supports four different row formats.

Setting a Table's Row Format

One way to specify an InnoDB table's row format is by setting the ROW_FORMAT table option to the relevant row format in a CREATE TABLE or ALTER TABLE statement.

In MariaDB 10.2.2 and later, the innodb_default_row_format system variable can be used to set the default row format for InnoDB tables.

In MariaDB 10.2.1 and before, the default row format is COMPACT. In MariaDB 10.2.2 and later, the default row format is DYNAMIC.

In MariaDB 10.1 and before, InnoDB can silently ignore and override some row format choices if you do not have the innodb_file_format system variable set to Barracuda and the innodb_file_per_table system variable set to ON.

Supported Row Formats

The InnoDB storage engine supports four different row formats:

  • REDUNDANT
  • COMPACT
  • COMPRESSED
  • DYNAMIC

In MariaDB 10.1 and before, the latter two row formats are only supported if the InnoDB file format is Barracuda. Therefore, the innodb_file_format system variable must be set to Barracuda to use these row formats in those versions.

In MariaDB 10.1 and before, the latter two row formats are also only supported if the table is in a file per-table tablespace. Therefore, the innodb_file_per_table system variable must be set to ON to use these row formats in those versions.

REDUNDANT

The REDUNDANT row format is an old non-compacted row format.

In the REDUNDANT row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, are partly stored in the row page. The first 767 bytes of these variable-length columns are stored in the row. If any variable-length columns exceed this, then the "overflow" for each column is stored in overflow pages. Fixed-length columns can be stored in overflow pages too, if they exceed the row page's size limit. Each overflow page is the size of innodb_page_size.

It was the only available format before MySQL 5.0, and was replaced as the default in MySQL 5.0.3.

Using the REDUNDANT Row Format

The easiest way to create an InnoDB table that uses the REDUNDANT row format is by setting the ROW_FORMAT table option to REDUNDANT in a CREATE TABLE or ALTER TABLE statement.

It is recommended to set the innodb_strict_mode system variable to ON when using this format.

The REDUNDANT row format is supported by both the Antelope and the Barracuda file formats, so tables with this row format can be created regardless of the value of the innodb_file_format system variable.

For example:

SET SESSION innodb_strict_mode=ON;

CREATE TABLE tab (
   id int,
   str varchar(50)
) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;

COMPACT

MariaDB until 10.2.1

In MariaDB 10.2.1 and before, the default row format is COMPACT.

The COMPACT row format is similar to the REDUNDANT row format, but it stores data in a more compact manner.

In the COMPACT row format variable-length columns, such as columns using the VARBINARY, VARCHAR, BLOB and TEXT data types, are partly stored in the row page. The first 767 bytes of these variable-length columns are stored in the row. If any variable-length columns exceed this, then the "overflow" for each column is stored in overflow pages. Fixed-length columns can be stored in overflow pages too, if they exceed the row page's size limit. Each overflow page is the size of innodb_page_size.

It was originally introduced in MySQL 5.0.

Using the COMPACT Row Format

In MariaDB 10.2.2 and later, the easiest way to create an InnoDB table that uses the COMPACT row format is by setting the ROW_FORMAT table option to to COMPACT in a CREATE TABLE or ALTER TABLE statement.

In MariaDB 10.2.1 and before, the default row format is COMPACT. Therefore, in these versions, the easiest way to create an InnoDB table that uses the COMPACT row format is by not setting the ROW_FORMAT table option at all in the CREATE TABLE or ALTER TABLE statement.

The COMPACT row format is supported by both the Antelope and the Barracuda file formats, so tables with this row format can be created regardless of the value of the innodb_file_format system variable.

For example:

CREATE TABLE tab (
   id int,
   str varchar(50)
) ENGINE=InnoDB ROW_FORMAT=COMPACT;

DYNAMIC

MariaDB starting with 10.2.2

In MariaDB 10.2.2 and later, the default row format is DYNAMIC.

InnoDB tables using the DYNAMIC row format are able to store records in a variable length format. This results in more efficient data storage than tables using the COMPACT or REDUNDANT row formats, especially for tables containing columns using the VARBINARY, VARCHAR, BLOB and TEXT data types. However, InnoDB tables using the COMPRESSED row format are more efficient.

The DYNAMIC row format is similar to the COMPACT row format. However, tables using the DYNAMIC row format store columns using the VARBINARY, VARCHAR, BLOB and TEXT data types differently from tables using the COMPACT row format. If the values cannot be entirely contained in the row page, only a pointer to a dedicated page is stored. Only values longer than 40 bytes are considered for external page storage. Each external page contains part of the data and a pointer to the next page, if any. Pointers are 20 bytes. This allows a high number of columns using the VARBINARY, VARCHAR, BLOB and TEXT data types to be stored in a table using the DYNAMIC row format.

Using the DYNAMIC Row Format

In MariaDB 10.2.2 and later, the default row format is DYNAMIC, as long as the innodb_default_row_format system variable has not been modified. Therefore, in these versions, the easiest way to create an InnoDB table that uses the DYNAMIC row format is by not setting the ROW_FORMAT table option at all in a CREATE TABLE or ALTER TABLE statement.

In MariaDB 10.2.1 and before, the easiest way to create an InnoDB table that uses the DYNAMIC row format is by setting the ROW_FORMAT table option to to DYNAMIC in a CREATE TABLE or ALTER TABLE statement.

The DYNAMIC row format is only supported by the Barracuda file format. As a side effect, in MariaDB 10.1 and before, the DYNAMIC row format is only supported if the InnoDB file format is Barracuda. Therefore, the innodb_file_format system variable must be set to Barracuda to use these row formats in those versions.

In MariaDB 10.1 and before, the DYNAMIC row format is also only supported if the table is in a file per-table tablespace. Therefore, the innodb_file_per_table system variable must be set to ON to use these row formats in those versions.

It is also recommended to set the innodb_strict_mode system variable to ON when using this row format.

For example:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

CREATE TABLE tab (
   id int,
   str varchar(50)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

COMPRESSED

InnoDB tables using the COMPRESSED row format store data in the most efficient manner in most cases.

The COMPRESSED row format is similar to the COMPACT row format. However, tables using the COMPRESSED row format store columns using the VARBINARY, VARCHAR, BLOB and TEXT data types differently from tables using the COMPACT row format. If the values cannot be entirely contained in the row page, only a pointer to a dedicated page is stored. Only values longer than 40 bytes are considered for external page storage. Each external page contains part of the data and a pointer to the next page, if any. Pointers are 20 bytes. This allows a high number of columns using the VARBINARY, VARCHAR, BLOB and TEXT data types to be stored in a table using the COMPRESSED row format.

To avoid compressing and uncompressing pages too many times, InnoDB tries to keep both compressed and uncompressed pages in the buffer pool when there is enough room. This results in a bigger cache. When there is not enough room, an adaptive LRU algorithm 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.

Each compressed page has an uncompressed modification log, stored within the page itself. InnoDB writes small changes into it. 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 decompression and compression 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 recursively 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, 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.

MariaDB starting with 10.1

In MariaDB 10.1 and later, an alternative way to compress InnoDB tables is by using InnoDB Page Compression.

Using the COMPRESSED Row Format

An InnoDB table that uses the COMPRESSED row format can be created by setting the ROW_FORMAT table option to COMPRESSED and by setting the KEY_BLOCK_SIZE table option to one of the following values in a CREATE TABLE or ALTER TABLE statement, where the units are in KB:

  • 1
  • 2
  • 4
  • 8
  • 16

16k is the default value of the innodb_page_size system variable, so using 16 will usually result in minimal compression unless there are many columns using the VARBINARY, VARCHAR, BLOB and TEXT data types.

In MariaDB 10.1 and later, the value of the innodb_page_size system variable can be set to 32k and 64k. This is especially useful because the larger page size permits more columns using the VARBINARY, VARCHAR, BLOB and TEXT data types. Regardless, even when the value of the innodb_page_size system variable is set to some value higher than 16k, 16 is still the maximum value for the KEY_BLOCK_SIZE table option for InnoDB tables using the COMPRESSED row format.

The COMPRESSED row format cannot be set as the default row format with the innodb_default_row_format system variable.

The COMPRESSED row format is only supported by the Barracuda file format. As a side effect, in MariaDB 10.1 and before, the COMPRESSED row format is only supported if the InnoDB file format is Barracuda. Therefore, the innodb_file_format system variable must be set to Barracuda to use these row formats in those versions.

In MariaDB 10.1 and before, the COMPRESSED row format is also only supported if the table is in a file per-table tablespace. Therefore, the innodb_file_per_table system variable must be set to ON to use these row formats in those versions.

It is also recommended to set the innodb_strict_mode system variable to ON when using this row format.

InnoDB automatically uses the COMPRESSED row format for a table if the KEY_BLOCK_SIZE table option is set to some value in a CREATE TABLE or ALTER TABLE statement. For example:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

CREATE TABLE tab (
   id int,
   str varchar(50)
) ENGINE=InnoDB KEY_BLOCK_SIZE=4;

If the KEY_BLOCK_SIZE table option is not set to some value, but the ROW_FORMAT table option is set to COMPRESSED in a CREATE TABLE or ALTER TABLE statement, then InnoDB uses a default value of 8 for the KEY_BLOCK_SIZE table option. For example:

SET SESSION innodb_strict_mode=ON;

SET GLOBAL innodb_file_per_table=ON;

SET GLOBAL innodb_file_format='Barracuda';

CREATE TABLE tab (
   id int,
   str varchar(50)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

Monitoring Performance of the COMPRESSED Row Format

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

Checking a Table's Row Format

The SHOW TABLE STATUS statement can be used to see the row format used by a table. For example:

SHOW TABLE STATUS FROM db1 WHERE Name='tab'\G
*************************** 1. row ***************************
           Name: tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-04-18 20:24:04
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment:

In MariaDB 10.0 and later, the information_schema.INNODB_SYS_TABLES table can also be queried to see the row format used by a table. For example:

SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name='db1/tab'\G
*************************** 1. row ***************************
     TABLE_ID: 42
         NAME: db1/tab
         FLAG: 33
       N_COLS: 4
        SPACE: 27
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

A table's tablespace is tagged with the lowest InnoDB file format that supports the table's row format. So, even if the Barracuda file format is enabled, tables that use the COMPACT or REDUNDANT row formats will be tagged with the Antelope file format in the information_schema.INNODB_SYS_TABLES table.

Maximum Row Size

MariaDB's maximum row size for all storage engines is 65,535 bytes. BLOB and TEXT columns are stored separately from the main row data, so for these columns, only the information stored with the actual row counts towards this limit.

However, the maximum row size for an InnoDB table depends on the value of the innodb_page_size system variable and the value of the ROW_FORMAT table option for the table. At most, the data that a single row consumes on a page can consist of half of the value of the innodb_page_size system variable. However, some InnoDB row formats can store some data off of the main page, so this is not an absolute limit for all InnoDB row formats.

The REDUNDANT and COMPACT row formats store so much data on the main page, so the maximum row sizes for these row formats is only about half the value of the innodb_page_size system variable.

Comments

Comments loading...