InnoDB Storage Formats
Contents
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
DYNAMIC
COMPRESSED
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 the original non-compacted row format.
This row format was the only available row format before MySQL 5.0.3. In that release, this row format was retroactively named the REDUNDANT
row format. In the same release, the COMPACT
row format was introduced as the new default row format.
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;
Index Prefixes with the REDUNDANT
Row Format
The REDUNDANT
row format supports index prefixes up to 767 bytes.
Maximum Row Size with the REDUNDANT
Row Format
In the REDUNDANT
row format variable-length columns, such as columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types, can be partially stored in overflow pages. InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size
. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size
. Each overflow page is the size of innodb_page_size
.
For VARBINARY
, VARCHAR
, BLOB
and TEXT
columns, only values longer than 767 bytes are considered for for storage on overflow pages. Bytes that are stored to track a value's length do not count towards this limit. This limit is only based on the length of the actual column's data.
Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size
. Even though a column using the CHAR
data type can hold at most 255 characters, a CHAR
column can still exceed 767 bytes in some cases. For example, a char(255)
column can exceed 767 bytes if the character set is utf8mb4
.
If a column is chosen to be stored on an overflow page, then the first 767 bytes of the column's value and a 20-byte pointer to the overflow page are stored on the main page.
Each overflow page contains part of the data and a 20-byte pointer to the next page, if a next page exists.
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 that requires about 20% less storage.
This row format was originally introduced in MySQL 5.0.3.
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;
Index Prefixes with the COMPACT
Row Format
The COMPACT
row format supports index prefixes up to 767 bytes.
Maximum Row Size with the COMPACT
Row Format
In the COMPACT
row format variable-length columns, such as columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types, can be partially stored in overflow pages. InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size
. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size
. Each overflow page is the size of innodb_page_size
.
For VARBINARY
, VARCHAR
, BLOB
and TEXT
columns, only values longer than 767 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards this limit. This limit is only based on the length of the actual column's data.
Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size
. Even though a column using the CHAR
data type can hold at most 255 characters, a CHAR
column can still exceed 767 bytes in some cases. For example, a char(255)
column can exceed 767 bytes if the character set is utf8mb4
.
If a column is chosen to be stored on an overflow page, then the first 767 bytes of the column's value and a 20-byte pointer to the overflow page are stored on the main page.
Each overflow page contains part of the data and a 20-byte pointer to the next page, if a next page exists.
DYNAMIC
MariaDB starting with 10.2.2
In MariaDB 10.2.2 and later, the default row format is DYNAMIC
.
The DYNAMIC
row format is similar to the COMPACT
row format, but tables using the DYNAMIC
row format can store even more data on overflow pages than tables using the COMPACT
row format. This results in more efficient data storage than tables using the COMPACT
row format, 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.
This row format was originally introduced in MySQL 5.5 and MariaDB 5.5.
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;
Index Prefixes with the DYNAMIC
Row Format
The DYNAMIC
row format supports index prefixes up to 3072 bytes. In MariaDB 10.2 and before, the innodb_large_prefix
system variable is used to configure the maximum index prefix length. In these versions, if innodb_large_prefix
is set to ON
, then the maximum prefix length is 3072 bytes, and if it is set to OFF
, then the maximum prefix length is 767 bytes.
Maximum Row Size with the DYNAMIC
Row Format
In the DYNAMIC
row format variable-length columns, such as columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types, can be completely stored in overflow pages. InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size
. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size
. Each overflow page is the size of innodb_page_size
.
For BLOB
and TEXT
columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY
and VARCHAR
columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data. These limits differ from the limits for the COMPACT
row format, where the limit is 767 bytes for all types.
Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size
. Even though a column using the CHAR
data type can hold at most 255 characters, a CHAR
column can still exceed 767 bytes in some cases. For example, a char(255)
column can exceed 767 bytes if the character set is utf8mb4
.
If a column is chosen to be stored on an overflow page, then the entire value of the column is stored on the overflow page, and only a 20-byte pointer to the overflow page is stored on the main page. This behavior differs from the behavior of the COMPACT
row format, which always stores the column prefix on the main page. This allows tables using the DYNAMIC
row format to contain a high number of columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types.
Each overflow page contains part of the data and a 20-byte pointer to the next page, if a next page exists.
COMPRESSED
The COMPRESSED
row format is similar to the COMPACT
row format, but tables using the COMPRESSED
row format can store even more data on overflow pages than tables using the COMPACT
row format. This results in more efficient data storage than tables using the COMPACT
row format, especially for tables containing columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types.
This row format was originally introduced in MySQL 5.5 and MariaDB 5.5.
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;
Compression with the COMPRESSED
Row Format
The COMPRESSED
row format also supports compression of all data and index pages.
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.
Monitoring Performance of the COMPRESSED
Row Format
The following 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
Index Prefixes with the COMPRESSED
Row Format
The COMPRESSED
row format supports index prefixes up to 3072 bytes. In MariaDB 10.2 and before, the innodb_large_prefix
system variable is used to configure the maximum index prefix length. In these versions, if innodb_large_prefix
is set to ON
, then the maximum prefix length is 3072 bytes, and if it is set to OFF
, then the maximum prefix length is 767 bytes.
Maximum Row Size with the COMPRESSED
Row Format
In the COMPRESSED
row format variable-length columns, such as columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types, can be completely stored in overflow pages. InnoDB only considers using overflow pages if the table's row size is greater than half of innodb_page_size
. If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of innodb_page_size
. Each overflow page is the size of innodb_page_size
.
For BLOB
and TEXT
columns, only values longer than 40 bytes are considered for storage on overflow pages. For VARBINARY
and VARCHAR
columns, only values longer than 255 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards these limits. These limits are only based on the length of the actual column's data. These limits differ from the limits for the COMPACT
row format, where the limit is 767 bytes for all types.
Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of innodb_page_size
. Even though a column using the CHAR
data type can hold at most 255 characters, a CHAR
column can still exceed 767 bytes in some cases. For example, a char(255)
column can exceed 767 bytes if the character set is utf8mb4
.
If a column is chosen to be stored on an overflow page, then the entire value of the column is stored on the overflow page, and only a 20-byte pointer to the overflow page is stored on the main page. This behavior differs from the behavior of the COMPACT
row format, which always stores the column prefix on the main page. This allows tables using the COMPRESSED
row format to contain a high number of columns using the VARBINARY
, VARCHAR
, BLOB
and TEXT
data types.
Each overflow page contains part of the data and a 20-byte pointer to the next page, if a next page exists.
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.
However, the maximum row size for an InnoDB table is more complicated.
The maximum amount of data that an InnoDB table can store in a row's main data page depends on the value of the innodb_page_size
system variable. At most, the data that a single row can consume on the row's main data page is half of the value of the innodb_page_size
system variable. With the default value of 16k
, that would mean that a single row can consume at most around 8 KB on the row's main data page.
However, the limit on the row's main data page is not the absolute limit on the row's size. The value of the table's ROW_FORMAT
table option also helps determine the maximum size of an InnoDB row. All InnoDB row formats can store certain kinds of data in overflow pages, but some row formats can store more data in overflow pages than others. For example, the DYNAMIC
and COMPRESSED
row formats can store the most data in overflow pages.
To see how to determine the maximum row size for the various InnoDB row formats, see the following sections: