InnoDB Page Compression
MariaDB starting with 10.1
InnoDB Page Compression was added in MariaDB 10.1.0 and support for the snappy compression algorithm in MariaDB 10.1.3
Contents
Overview
InnoDB page compression provides a way to compress InnoDB tables.
InnoDB page compression can be used on any storage device and any file system, but it is most beneficial on SSDs and Non-Volatile Memory (NVM) devices, such as FusionIO atomic-series devices. It also performs best when your storage device and file system support atomic writes, since that allows the doublewrite buffer to be disabled. However, it still works on any device and file system, even without atomic write support and with the doublewrite buffer enabled.
Differences Compared to the COMPRESSED
Row Format
InnoDB page compression is an alternative way to compress your tables which is different (but similar) to InnoDB's COMPRESSED
row format. Some of the differences are:
- With InnoDB page compression, only uncompressed pages are stored in the buffer pool. In contrast, with InnoDB's
COMPRESSED
row format, both uncompressed and compressed pages can be in the buffer pool. - 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. - With InnoDB page compression, pages are decompressed right after they are read from tablespace file, but before being placed in the buffer pool. In contrast, with InnoDB's
COMPRESSED
row format, pages are decompressed after they are read from the tablespace file, but the compressed pages are stored in the buffer pool alongside the uncompressed pages. - With InnoDB page compression, multiple compression algorithms are supported. In contrast, with InnoDB's
COMPRESSED
row format, zlib is the only supported compression algorithm.
Choosing a Page Compression Algorithm
You specify which page compression algorithm to use by setting the
innodb_compression_algorithm
system variable. It can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_algorithm='lz4';
It 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=lz4
The system variable can be set to one of the following values:
Option | Description |
---|---|
none | Default. Data is not compressed. |
zlib | Pages are compressed with bundled zlib compression method. |
lz4 | Pages are compressed using https://code.google.com/p/lz4/ compression method. |
lzo | Pages are compressed using http://www.oberhumer.com/opensource/lzo/ compression method. |
lzma | Pages are compressed using http://tukaani.org/xz/ compression method. |
bzip2 | Pages are compressed using http://www.bzip.org/ compression method. |
snappy | Pages are compressed using http://google.github.io/snappy/. |
However, on some distributions, MariaDB may not support all page compression algorithms by default.
Currently, the page compression algorithm can only be set globally. It is not possible to use different algorithms for different tables.
Changing the page compression algorithm does not change existing pages that were already compressed with a different page compression algorithm. However, this is not a problem, because every page in an InnoDB tablespace contains metadata about the page compression algorithm in the page header. This means that MariaDB supports having uncompressed pages and pages compressed with different page compression algorithms in the same InnoDB tablespace at the same time.
Checking Which Page Compression Algorithms are Supported
On some distributions, MariaDB may not support all page compression algorithms by default. Therefore, if you want to use a specific page compression algorithm, then you should check whether your build supports it.
The easiest way to currently do that is to check your build's supported values for the innodb_compression_algorithm
system variable. This can be done by executing mysqld-options
with the --help
and --verbose
options. For example:
sudo mysqld --help --verbose ... --innodb-compression-algorithm[=name] Compression algorithm used on page compression. One of: none, zlib, lz4, lzo, lzma, or bzip2 ...
In the example output, we can see that snappy
is not listed, so we know that this specific build does not support that page compression algorithm.
Compiling MariaDB to Add Support for a Page Compression Algorithm
On some distributions, MariaDB may not support all page compression algorithms by default. Therefore, if you want to use certain 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.
- Recompile MariaDB Server from the source distribution.
If all dependencies are available, then the last step can be as easy as this:
cmake . make make install
After executing cmake
, please check CMakeCache.txt
to confirm that it has found the desired compression library on your system.
See Compiling MariaDB From Source for more information.
Using InnoDB Page Compression
With a default configuration, a table will only use InnoDB page compression if the PAGE_COMPRESSED
table option is set to 1
. The default behavior can be changed by setting the innodb_compression_default
system variable. If it is set to ON
, then new InnoDB tables will use InnoDB page compression by default.
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 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 also only supported if the table's row format is COMPACT
or DYNAMIC
.
For example:
SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_file_format='Barracuda'; SET GLOBAL innodb_default_row_format='dynamic'; CREATE TABLE users ( user_id int not null, b varchar(200), primary key(user_id) ) ENGINE=InnoDB PAGE_COMPRESSED=1;
Configuring the Compression Level
You can specify the default compression level by configuring the
innodb_compression_level
system variable. It can be changed dynamically with SET GLOBAL
. For example:
SET GLOBAL innodb_compression_level=9;
It 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
You can also set a compression level for a specific table 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'; CREATE TABLE users ( user_id int not null, b varchar(200), primary key(user_id) ) ENGINE=InnoDB PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=9;
The compression level can be set to any value between 0
and 9
. The default compression level is 6
. If it is set to 0
, then no compression is used. The range goes from the fastest to the most compact, which means that 1
is the fastest and 9
is the most compact.
Some page compression algorithms do not support compression levels. In those cases, the compression level value is ignored.
Persistent TRIM
Normally InnoDB always writes the full page i.e. by default 16K (innodb-page-size). However, when compression is used you may use a different approach. On file systems that support fallocate()
and creating sparse files
(e.g. ext3/4, xfs, nvmfs, etc) by using FALLOC_FL_PUNCH_HOLE
, InnoDB will only write the actual compressed
page size aligned to sector size. The rest of the page is trimmed using fallocate(file_handle, FALLOC_FL_PUNCH_HOLE | FALLOC_FL_KEEP_SIZE, file_offset, remainder_len);
. This is needed because InnoDB always reads the full page size (default 16K).
This feature can be configured by using innodb_use_trim and innodb_use_fallocate:
innodb_use_trim=ON innodb_use_fallocate=ON
Note that innodb_use_trim favors NVMFS (see InnoDB holepunch compression vs the filesystem in MariaDB 10.1).
Page Cleaners
MariaDB starting with 10.2.2
InnoDB Page Cleaners were introduced in version 10.2.2 of MariaDB.
Until MariaDB 10.2.1, MariaDB by default used a single thread to flush dirty pages from the buffer pool. From MariaDB 10.2.2, particularly when using compression on systems with multiple cores or CPU's and a fast I/O device, (such as an NVM device), MariaDB benefits from using Page Cleaners.
You can define the number of threads you want to use for page cleaning through the innodb_page_cleaners
system variables. It defaults to either 4 or the value given to the innodb_buffer_pool_instances
system variable, whichever is lower. If set to 1, MariaDB falls back to only using a single thread. Cleaner threads flush dirty pages from the buffer pool, performing flush list and LRU flushing.
innodb_page_cleaners = 8
MariaDB 10.1.0 - 10.3.2
Prior to version 10.3.2, users had access to the Multi-Threaded Flush mechanism for similar functionality. You can enable this feature using the innodb_use_mtflush
system variable and set the number of threads you want to use with innodb_mtflush_threads
. By default, it is set to 8. The current maximum is 64 threads. In multi-core systems a value similar to innodb_buffer_pool_instances
that is close to the number of cores has been shown to be effective. Use your own benchmarks to find a suitable value for your particular application.
This feature was deprecated in MariaDB 10.2.9 and removed from MariaDB 10.3.2. With newer versions of MariaDB, use innodb_page_cleaners
instead.
Monitoring Compression
SHOW STATUS contains new status variables that can be used to monitor compression
Status variable name | Values | Description |
---|---|---|
Innodb_page_compression_saved | 0 | Bytes saved by compression |
Innodb_page_compression_trim_sect512 | 0 | Number of 512 sectors trimmed |
Innodb_page_compression_trim_sect1024 | 0 | Number of 1024 sectors trimmed |
Innodb_page_compression_trim_sect2048 | 0 | Number of 2048 sectors trimmed |
Innodb_page_compression_trim_sect4096 | 0 | Number of 4096 sectors trimmed |
Innodb_page_compression_trim_sect8192 | 0 | Number of 8192 sectors trimmed |
Innodb_page_compression_trim_sect16384 | 0 | Number of 16384 sectors trimmed |
Innodb_page_compression_trim_sect32768 | 0 | Number of 32768 sectors trimmed |
Innodb_num_pages_page_compressed | 0 | Number of pages compressed |
Innodb_num_page_compressed_trim_op | 0 | Number of trim operations |
Innodb_num_page_compressed_trim_op_saved | 0 | Number of trim operations saved |
Innodb_num_pages_page_decompressed | 0 | Number of pages decompressed |
Innodb_num_pages_page_compression_error | 0 | Number of compression errors |
Innodb_have_lz4 | ON | Does system have lz4 compression method available |
Innodb_have_lzo | ON | Does system have lzo compression method available |
Innodb_have_lzma | ON | Does system have lzma compression method available |
Innodb_have_bzip2 | ON | Does system have bzip2 compression method available |
Innodb_have_snappy | ON | Does system have snappy compression method available |
Keep in mind that page compression is performed when InnoDB pages are flushed to disk, so if you are monitoring page compression via these status variables, the status variables values will get incremented when the pages are flushed, which does not necessarily happen immediately. Here's an example:
CREATE TABLE `tab` ( `id` int(11) NOT NULL, `str` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 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_compression_algorithm=zlib; 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 | +----------------------------------+-------+
Note also that the written *.ibd files are sparse files, so in order to see the effects of the compression, ls
will not be sufficient, and ls -s
or dd
should be used to view the file sizes instead.
XtraBackup
Note that Percona XtraBackup (as of 2.4) will not work with MariaDB compression. However, MariaDB's fork, MariaDB Backup, will work with compression.
Links
- https://blog.mariadb.org/significant-performance-boost-with-new-mariadb-page-compression-on-fusionio/
- https://mariadb.com/kb/en/mariadb/fusion-io-directfs-atomic-write-support/
- https://blog.mariadb.org/mariadb-introduces-atomic-writes/
- https://www.usenix.org/conference/inflow14/workshop-program/presentation/das
Thanks
- Table compression was developed with cooperation by Fusion-io http://fusionio.com and especially Dhananjoy Das and Torben Mathiasen.