InnoDB Page Compression
MariaDB starting with 10.1
InnoDB Page Compression was added in MariaDB 10.1.0. Support for the snappy
compression algorithm was added in MariaDB 10.1.3.
Contents
- Overview
- Use Cases
- Differences Compared to the COMPRESSED Row Format
- Configuring the InnoDB Page Compression Algorithm
- Enabling InnoDB Page Compression
- Configuring the Compression Level
- Configuring the Failure Threshold and Padding
- Saving Storage Space with Sparse Files
- Configuring InnoDB Page Flushing
- Monitoring InnoDB Page Compression
- Compatibility with Backup Tools
- Acknowledgements
- See Also
Overview
InnoDB page compression provides a way to compress InnoDB tables.
Use Cases
- InnoDB page compression can be used on any storage device and any file system.
- InnoDB page compression is most efficient on file systems that support sparse files. See Saving Storage Space with Sparse Files for more information.
- InnoDB page compression is most beneficial on solid state drives (SSDs) and Non-Volatile Memory (NVM) storage devices, such as FusionIO atomic-series devices.
- InnoDB page compression performs best when your storage device and file system support atomic writes, since that allows the InnoDB doublewrite buffer to be disabled. See Atomic Write Support for more information.
Differences Compared to the COMPRESSED
Row Format
InnoDB page compression is a modern way to compress your InnoDB tables. It is similar to InnoDB's COMPRESSED
row format, but it has many advantages. Some of the differences are:
- With InnoDB page compression, compressed pages are immediately decompressed after being read from the tablespace file, and only uncompressed pages are stored in the buffer pool. In contrast, with InnoDB's
COMPRESSED
row format, compressed pages are decompressed immediately after they are read from the tablespace file, and both the uncompressed and compressed pages are stored in the buffer pool. This means that theCOMPRESSED
row format uses more space in the buffer pool than InnoDB page compression does. - With InnoDB page compression, pages are compressed just before being written to the tablespace file. In contrast, with InnoDB's
COMPRESSED
row format, pages are re-compressed immediately after any changes, and the compressed pages are stored in the buffer pool alongside the uncompressed pages. These changes are then occasionally flushed to disk. This means that theCOMPRESSED
row format re-compresses data more frequently than InnoDB page compression does. - With InnoDB page compression, multiple compression algorithms are supported. In contrast, with InnoDB's
COMPRESSED
row format,zlib
is the only supported compression algorithm. This means that theCOMPRESSED
row format has less compression options than InnoDB page compression does.
In general, InnoDB page compression is superior to the COMPRESSED
row format.
Configuring the InnoDB Page Compression Algorithm
There is not currently a table option to set different InnoDB page compression algorithms for individual tables.
However, the server-wide InnoDB page compression algorithm can be configured by setting the
innodb_compression_algorithm
system variable.
When this system variable is changed, the InnoDB page compression algorithm does not change for existing pages that were already compressed with a different InnoDB page compression algorithm. InnoDB is able to handle this situation without issues, because every page in an InnoDB tablespace contains metadata about the InnoDB page compression algorithm in the page header. This means that InnoDB supports having uncompressed pages and pages compressed with different InnoDB page compression algorithms in the same InnoDB tablespace at the same time.
This system variable can be set to one of the following values:
System Variable Value | Description |
---|---|
none | Pages are not compressed. This is the default value in MariaDB 10.2.3 and before, and MariaDB 10.1.21 and before. |
zlib | Pages are compressed using the bundled zlib compression algorithm. This is the default value in MariaDB 10.2.4 and later, and MariaDB 10.1.22 and later. |
lz4 | Pages are compressed using the lz4 compression algorithm. |
lzo | Pages are compressed using the lzo compression algorithm. |
lzma | Pages are compressed using the lzma compression algorithm. |
bzip2 | Pages are compressed using the bzip2 compression algorithm. |
snappy | Pages are compressed using the snappy algorithm. |
However, on many distributions, the standard MariaDB builds do not support all InnoDB page compression algorithms by default.
This system variable can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_algorithm='lzma';
This system variable can also be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_compression_algorithm=lzma
Checking Which InnoDB Page Compression Algorithms are Supported
On many distributions, the standard MariaDB builds may not support all InnoDB page compression algorithms by default. Therefore, if you want to use a specific InnoDB page compression algorithm, then you should check whether your MariaDB build supports it.
The zlib
compression algorithm is always supported.
A MariaDB build's support for other InnoDB page compression algorithms can be checked by querying the following status variables with SHOW GLOBAL STATUS
:
Status Variable | Description |
---|---|
Innodb_have_lz4 | Whether InnoDB supports the lz4 compression algorithm. |
Innodb_have_lzo | Whether InnoDB supports the lzo compression algorithm. |
Innodb_have_lzma | Whether InnoDB supports the lzma compression algorithm. |
Innodb_have_bzip2 | Whether InnoDB supports the bzip2 compression algorithm. |
Innodb_have_snappy | Whether InnoDB supports the snappy compression algorithm. |
For example:
SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_have_lz4', 'Innodb_have_lzo', 'Innodb_have_lzma', 'Innodb_have_bzip2', 'Innodb_have_snappy' ); +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | Innodb_have_lz4 | OFF | | Innodb_have_lzo | OFF | | Innodb_have_lzma | ON | | Innodb_have_bzip2 | OFF | | Innodb_have_snappy | OFF | +--------------------+-------+ 5 rows in set (0.001 sec)
Adding Support for an InnoDB Page Compression Algorithm
On many distributions, the standard MariaDB builds may not support all InnoDB page compression algorithms by default. Therefore, if you want to use certain InnoDB page compression algorithms, then you may need to do the following:
- Download the package for the desired compression library from the above links.
- Install the package for the desired compression library.
- Compile MariaDB from the source distribution.
The general steps for compiling MariaDB are:
- Download and unpack the source code distribution:
wget https://downloads.mariadb.com/MariaDB/mariadb-10.4.8/source/mariadb-10.4.8.tar.gz tar -xvzf mariadb-10.4.8.tar.gz cd mariadb-10.4.8/
- Configure the build using
cmake
:
cmake .
- Check
CMakeCache.txt
to confirm that it has found the desired compression library on your system.
- Compile the build:
make
- Either install the build:
make install
Or make a package to install:
make package
See Compiling MariaDB From Source for more information.
Enabling InnoDB Page Compression
InnoDB page compression 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 this feature.
InnoDB page compression is only supported by the Barracuda
file format. As a side effect, in MariaDB 10.1 and before, InnoDB page compression is only supported if the InnoDB file format is Barracuda
. Therefore, the innodb_file_format
system variable must be set to Barracuda
to use this feature in those versions.
InnoDB page compression is also only supported if the table's row format is COMPACT
or DYNAMIC
.
Enabling InnoDB Page Compression by Default
MariaDB starting with 10.2.3
The innodb_compression_default
system variable was first added in MariaDB 10.2.3.
InnoDB page compression will not be enabled for new InnoDB tables by default. In MariaDB 10.2.3 and later, InnoDB page compression can be enabled for all new InnoDB tables by default by setting the innodb_compression_default
system variable to ON
.
This system variable can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_default=ON;
This system variable's session value can be changed dynamically with SET SESSION
. For example:
SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_file_format='Barracuda'; SET GLOBAL innodb_default_row_format='dynamic'; SET GLOBAL innodb_compression_algorithm='lzma'; SET SESSION innodb_compression_default=ON; CREATE TABLE users ( user_id int not null, b varchar(200), primary key(user_id) ) ENGINE=InnoDB;
This system variable can also be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_compression_default=ON
Enabling InnoDB Page Compression for Individual Tables
InnoDB page compression can be enabled for individual tables by setting the PAGE_COMPRESSED
table option to 1
. For example:
SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_file_format='Barracuda'; SET GLOBAL innodb_default_row_format='dynamic'; SET GLOBAL innodb_compression_algorithm='lzma'; CREATE TABLE users ( user_id int not null, b varchar(200), primary key(user_id) ) ENGINE=InnoDB PAGE_COMPRESSED=1;
Configuring the Compression Level
The compression level determines whether the compression algorithm should favor speed or more compact data.
The compression level can be set to any value between 1
and 9
. The range goes from the fastest to the most compact, which means that 1
is the fastest and 9
is the most compact.
Only a subset of InnoDB page compression algorithms support compression levels. Currently, the following InnoDB page compression algorithms support them:
If an InnoDB page compression algorithm does not support compression levels, then it ignores any provided compression level value.
Configuring the Default Compression Level
The default compression level can be configured by setting the
innodb_compression_level
system variable.
This system variable's default value is 6
.
This system variable can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_level=9;
This system variable can also be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_compression_level=9
Configuring the Compression Level for Individual Tables
The compression level for individual tables can also be configured by setting the PAGE_COMPRESSION_LEVEL
table option for the table. For example:
SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_file_format='Barracuda'; SET GLOBAL innodb_default_row_format='dynamic'; SET GLOBAL innodb_compression_algorithm='lzma'; CREATE TABLE users ( user_id int not null, b varchar(200), primary key(user_id) ) ENGINE=InnoDB PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9;
Configuring the Failure Threshold and Padding
With InnoDB page compression, if InnoDB encounters a lot of compress failures, it is designed to pad the page with zeroed out bytes as a way to decrease the failure rate.
The failure threshold can be configured by setting the innodb_compression_failure_threshold_pct
system variable.
This system variable can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_failure_threshold_pct=10;
This system variable can also be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_compression_failure_threshold_pct=10
Once the failure threshold has been reached. InnoDB pads pages with zeroed out bytes in 128 byte increments until either the maximum pad percent has been reached or the failure rate falls below the failure threshold.
The maximum pad percent can be configured by setting the innodb_compression_pad_pct_max
system variable.
This system variable can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_pad_pct_max=75;
This system variable can also be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_compression_pad_pct_max=75
Saving Storage Space with Sparse Files
When InnoDB page compression is used, InnoDB may still write the compressed page to the tablespace file with the original size of the uncompressed page, which would be equivalent to the value of the innodb_page_size
system variable. This is done by design, because when InnoDB's I/O code needs to read the page from disk, it can only read the full page size. However, this is obviously not optimal.
On file systems that support sparse files, this problem is solved by writing the tablespace file as a sparse file using the punch hole technique. With the punch hole technique, InnoDB will only write the actual compressed page size to the tablespace file, aligned to sector size. The rest of the page is trimmed.
This punch hole technique allows InnoDB to read the compressed page from disk as the full page size, even though the compressed page really takes up less space on the file system.
Since the tablespace files are sparse files when this punch hole technique is used, this does mean that some utilities may require special options in order to handle them in an efficient manner.
In MariaDB 10.3 and later, this punch hole technique is used automatically when it is supported.
In MariaDB 10.2 and before, this punch hole technique can be enabled by configuring the innodb_use_trim
and innodb_use_fallocate
system variables. These system variables can be set in a server option group in an option file prior to starting up the server. For example:
[mariadb] ... innodb_use_trim=ON innodb_use_fallocate=ON
Sparse File Support on Linux
On Linux, the following file systems support sparse files:
ext3
ext4
xfs
btrfs
nvmfs
On Linux, file systems need to support the fallocate()
system call with the FALLOC_FL_PUNCH_HOLE
and FALLOC_FL_KEEP_SIZE
flags. For example:
fallocate(file_handle, FALLOC_FL_PUNCH_HOLE | FALLOC_FL_KEEP_SIZE, file_offset, remainder_len);
Some Linux utilities may require special options in order to work with sparse files efficiently. For example:
- The
ls
utility will report the non-sparse size of the tablespace file when executed with default behavior, butls -s
will report the actual amount of storage allocated for the tablespace file. - The
cp
utility is pretty good at auto-detecting sparse files, but it also provides thecp --sparse=always
andcp --sparse=never
options, if the auto-detection is not desired. - The
tar
utility will archive sparse files with their non-sparse size when executed with default behavior, buttar --sparse
will auto-detect sparse files, and archive them with their sparse size.
Sparse File Support on Windows
On Windows, the following file systems support sparse files:
NTFS
On Windows, file systems need to support the DeviceIoControl()
function with the FSCTL_SET_SPARSE
and FSCTL_SET_ZERO_DATA
control codes. For example:
DeviceIoControl(file_handle, FSCTL_SET_SPARSE, inbuf, inbuf_size, outbuf, outbuf_size, NULL, &overlapped) ... DeviceIoControl(file_handle, FSCTL_SET_ZERO_DATA, inbuf, inbuf_size, outbuf, outbuf_size, NULL, &overlapped)
Configuring InnoDB Page Flushing
With InnoDB page compression, pages are compressed when they are flushed to disk. Therefore, it can be helpful to optimize the configuration of InnoDB's page flushing. See InnoDB Page Flushing for more information.
Monitoring InnoDB Page Compression
InnoDB page compression can be monitored by querying the following status variables with SHOW GLOBAL STATUS
:
Status Variable | Description |
---|---|
Innodb_page_compression_saved | Bytes saved by compression |
Innodb_page_compression_trim_sect512 | Number of 512 sectors trimmed |
Innodb_page_compression_trim_sect1024 | Number of 1024 sectors trimmed |
Innodb_page_compression_trim_sect2048 | Number of 2048 sectors trimmed |
Innodb_page_compression_trim_sect4096 | Number of 4096 sectors trimmed |
Innodb_page_compression_trim_sect8192 | Number of 8192 sectors trimmed |
Innodb_page_compression_trim_sect16384 | Number of 16384 sectors trimmed |
Innodb_page_compression_trim_sect32768 | Number of 32768 sectors trimmed |
Innodb_num_pages_page_compressed | Number of pages compressed |
Innodb_num_page_compressed_trim_op | Number of trim operations |
Innodb_num_page_compressed_trim_op_saved | Number of trim operations saved |
Innodb_num_pages_page_decompressed | Number of pages decompressed |
Innodb_num_pages_page_compression_error | Number of compression errors |
With InnoDB page compression, a page is only compressed when it is flushed to disk. This means that if you are monitoring InnoDB page compression via these status variables, then the status variables values will only get incremented when the dirty pages are flushed to disk, which does not necessarily happen immediately. For example:
CREATE TABLE `tab` ( `id` int(11) NOT NULL, `str` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO tab VALUES (1, 'str1'); SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_num_pages_page_compressed | 0 | +----------------------------------+-------+ SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_file_format='Barracuda'; SET GLOBAL innodb_default_row_format='dynamic'; SET GLOBAL innodb_compression_algorithm='lzma'; ALTER TABLE tab PAGE_COMPRESSED=1; SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_num_pages_page_compressed | 0 | +----------------------------------+-------+ SELECT SLEEP(10); +-----------+ | SLEEP(10) | +-----------+ | 0 | +-----------+ SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed'; +----------------------------------+-------+ | Variable_name | Value | +----------------------------------+-------+ | Innodb_num_pages_page_compressed | 3 | +----------------------------------+-------+
Compatibility with Backup Tools
Mariabackup supports InnoDB page compression.
Percona XtraBackup does not support InnoDB page compression.
Acknowledgements
- InnoDB page compression was developed by collaborating with Fusion-io. Special thanks especially to Dhananjoy Das and Torben Mathiasen.