InnoDB/XtraDB Page Compression
MariaDB starting with 10.1.0
InnoDB/XtraDB Page Compression was added in MariaDB 10.1.0
Contents
Overview
Page compression is alternative way to compress you tables. In page compression, only uncompressed pages are stored on buffer pool. This approach differs significantly from legacy InnoDB compressed tables using row_format=compressed
, where both uncompressed and compressed pages can be in buffer pool. In page compression, page is actually compressed just before page is written to filespace. Similarly, page is uncompressed when it is read from tablespace before it is placed on 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 if innodb-file-format=Barracuda and innodb-file-per-table=1
Page compression can be used on any file system but most beneficial it is on SSDs and Non-Volatile Memory (NVM) devices like FusionIO atomic-series http://www.fusionio.com/products/atomic-series/. Page compression design works also with double-write enabled, but for best performance is reached if double-write is disabled (i.e. innodb-doublewrite=0
) and atomic writes are enabled (innodb-use-atomic-writes=1
). This naturally requires that used file system and storage device supports atomic writes (https://blog.mariadb.org/mariadb-introduces-atomic-writes/).
Choosing compression algorithm
You specify which compression method to use with the
--innodb-compression-algorithm=
startup option for MariaDB. The options are:
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</a> |
snappy | Pages are compressed using https://code.google.com/p/snappy/. MariaDB starting with 10.1.3Support for snappy compression method was added in MariaDB 10.1.3 |
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 desired compression method package from above links, install the package and finally recompile MariaDB server from source distribution with:
cmake . make make install
After the first command above, please check that cmake has found the desired compression method from your system.
Compression method can be changed whenever needed. Currently compression method is global (i.e. you can't specify compression method/table).
set global innodb_compression_method=lz4;
From this point on page compressed tables will use lz4 compression method. This setting does not change already compressed pages that were compressed on 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 on InnoDB tablespace contains compression method information on page header metadata.
Choosing compression level
You specify default compression level to use with the
--innodb-compression-level=
startup option for MariaDB values are 0-9. Note that not all compression methods allow choosing compression level and in those cases compression level value is ignored.
Creating compressed tables
Only tables that are specified to be compressed are actually compressed, you can create page compressed table with:
create table users(user_id int not null, b name varchar(200), primary key(user_id)) engine=innodb page_compressed=1;
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 different approach. On file systems that support fallocate()
and creathing sparse files
(e.g. ext3/4, xfs, nvmfs, etc) by using FALLOC_FL_PUNCH_HOLE
, InnoDB will write only actual compressed
page size alligned to sector size. 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 full page size
(default 16K).
This feature can be configured by using:
innodb_use_trim=ON
Multi-threaded flush
Normally MariaDB uses a single thread to flush dirty pages out of the buffer pool. However, when compression is used and system has multiple-cores/CPUs and fast I/O device (like NVM device), you may benefit by using multi-threaded flush mechanism. This feature has two configuration variables at startup time:
--innodb-use-mtflush=
startup option for MariaDB to enable multi-threaded flush and by default this is 8. You may configure the number of used threads by --innodb-mtflush-threads
and maximum currently is 64. In multi-core systems value similar to innodb-buffer-pool-instances
that is close to number of cores have shown to be effective. Naturally, you should benchmark the suitable value using your own application as a workload.
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 |
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.