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
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 the original non-compacted 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.
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.
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;
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.
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.
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.
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;
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.
In the DYNAMIC
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 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. 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.
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;
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.
In the COMPRESSED
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 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. 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.
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.
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;
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
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.