InnoDB Page Compression

MariaDB starting with 10.1

InnoDB/XtraDB Page Compression was added in MariaDB 10.1.0 and support for the snappy compression algorithm in MariaDB 10.1.3


Page compression is an alternative way to compress your tables which is different (but similar) to the InnoDB COMPRESSED storage format. In page compression, only uncompressed pages are stored in the buffer pool. This approach differs significantly from legacy InnoDB compressed tables using row_format=compressed, where both uncompressed and compressed pages can be in the buffer pool. In page compression, pages are actually compressed just before being written to the filespace. Similarly, pages are uncompressed when they are read from tablespace before being placed in the buffer pool. Additionally, page compression supports different compression algorithms, not only zlib. For the moment, the only engines that fully support page compression are XtraDB and InnoDB. Page compression is available only for a given table if it uses a file-per-table tablespace and if the table was created with innodb-file-format=Barracuda set.

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.

Choosing Compression Algorithm

You specify which compression algorithm to use with the innodb-compression-algorithm startup option for MariaDB. The options are:

noneDefault. Data is not compressed.
zlibPages are compressed with bundled zlib compression method.
lz4Pages are compressed using compression method.
lzoPages are compressed using compression method.
lzmaPages are compressed using compression method.
bzip2Pages are compressed using compression method.
snappyPages are compressed using

Because all of these compression methods are not available by default on all distributions and MariaDB server does not bundle them, you may need to download the desired compression method package from the above links, install the package and finally recompile MariaDB server from the source distribution with:

cmake .
make install

After the first command above, please check that cmake has found the desired compression method from your system.

The compression method can be changed whenever needed. Currently the compression method is global (i.e. you can't specify compression method/table).

set global innodb_compression_algorithm=lz4;

From this point on page compressed tables will use the lz4 compression method. This setting does not change already compressed pages that were compressed with a different compression method. This is because MariaDB supports pages that are uncompressed, compressed with e.g. lzo and compressed with e.g. lz4 in same tablespace. This is possible because every page in InnoDB tablespace contains compression method information on page header metadata.

Creating Compressed Tables

By default, only tables that are specified to be compressed are actually compressed,. You can create a page compressed table by setting the PAGE_COMPRESSED table option to 1. For example:

CREATE TABLE users(user_id int not null, b varchar(200), primary key(user_id)) 

The innodb_compression_default system variable allows you to specify whether or not new InnoDB tables are compressed by default. It is off by default (no compression).

Configuring the Compression Level

You specify the default compression level to use with the innodb-compression-level startup option for MariaDB. Values are 0-9, default is 6. 0 is no compression, 1 gives the best speed through to 9 giving the best compression. Note that not all compression methods allow choosing the compression level and in those cases the compression level value is ignored.

You can also set a compression level for a specific table by setting the PAGE_COMPRESSION_LEVEL table option for the table, from 1-9. For example:

CREATE TABLE users(user_id int not null, b varchar(200), primary key(user_id)) 

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:


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 nameValuesDescription
Innodb_page_compression_saved0Bytes saved by compression
Innodb_page_compression_trim_sect5120Number of 512 sectors trimmed
Innodb_page_compression_trim_sect10240Number of 1024 sectors trimmed
Innodb_page_compression_trim_sect20480Number of 2048 sectors trimmed
Innodb_page_compression_trim_sect40960Number of 4096 sectors trimmed
Innodb_page_compression_trim_sect81920Number of 8192 sectors trimmed
Innodb_page_compression_trim_sect163840Number of 16384 sectors trimmed
Innodb_page_compression_trim_sect327680Number of 32768 sectors trimmed
Innodb_num_pages_page_compressed0Number of pages compressed
Innodb_num_page_compressed_trim_op0Number of trim operations
Innodb_num_page_compressed_trim_op_saved0Number of trim operations saved
Innodb_num_pages_page_decompressed0Number of pages decompressed
Innodb_num_pages_page_compression_error0Number of compression errors
Innodb_have_lz4ONDoes system have lz4 compression method available
Innodb_have_lzoONDoes system have lzo compression method available
Innodb_have_lzmaONDoes system have lzma compression method available
Innodb_have_bzip2ONDoes system have bzip2 compression method available
Innodb_have_snappyONDoes 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:

     `id` int(11) NOT NULL,
     `str` varchar(50) DEFAULT NULL,
     PRIMARY KEY (`id`)
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;

SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';
| Variable_name                    | Value |
| Innodb_num_pages_page_compressed | 0     |
| 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.


Note that Percona XtraBackup (as of 2.4) will not work with MariaDB compression. However, MariaDB's fork, MariaDB Backup, will work with compression.


  • Table compression was developed with cooperation by Fusion-io and especially Dhananjoy Das and Torben Mathiasen.

See Also


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.