InnoDB Page Compression

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, 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 the COMPRESSED 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 the COMPRESSED 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 the COMPRESSED row format has less compression options than InnoDB page compression does.

In general, InnoDB page compression is superior to the COMPRESSED row format.

Choosing an InnoDB Page Compression Algorithm

You can specify which InnoDB page compression algorithm to use by setting the innodb_compression_algorithm system variable. This system variable can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL innodb_compression_algorithm='lz4';

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=lz4

The system variable can be set to one of the following values:

However, on some distributions, MariaDB may not support all page compression algorithms by default.

Currently, the InnoDB page compression algorithm can only be set globally. It is not possible to use different algorithms for different tables.

Changing the InnoDB 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 InnoDB page compression algorithm in the page header. This means that MariaDB supports having uncompressed pages and pages compressed with different InnoDB page compression algorithms in the same InnoDB tablespace at the same time.

Checking Which InnoDB Page Compression Algorithms are Supported

On some distributions, MariaDB 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 build supports it.

The zlib compression algorithm is always supported.

For other InnoDB page compression algorithms, the supported ones can be checked by querying the following status variables with SHOW GLOBAL STATUS:

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 some distributions, MariaDB 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.
• 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

The default compression level by configured by setting the innodb_compression_level system variable. 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

The compression level for a specific table 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';

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 1 and 9. The default compression level is 6. 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 page compression algorithms support them:

• zlib
• lzma

If an InnoDB page compression algorithm does not support compression levels, then the compression level value is ignored.

Saving Storage Space by Punching Holes in Compressed Pages

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 the fallocate() system call (e.g. ext3, ext4, xfs, nvmfs, etc), 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 by using 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);

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.

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

Since the tablespace files are sparse files when this punch hole technique, this does mean that you can't always trust the reported size of the tablespace file. For example:

• The ls utility will report the non-sparse size of the tablespace file when executed with default behavior, but ls -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 the cp --sparse=always and cp --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, but tar --sparse will auto-detect sparse files, and archive them with their sparse size.

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 Page Compression

InnoDB page compressed can be monitored by querying the following new status variables with SHOW GLOBAL STATUS:

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 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     |
+----------------------------------+-------+

Compatibility with Backup Tools

[mariabackup|Mariabackup]] supports InnoDB page compression.

Percona XtraBackup does not support InnoDB page 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.

See Also

• InnoDB holepunch compression vs the filesystem in MariaDB 10.1