This feature enables transparent page-level compression for tables using algorithms like LZ4 or Zlib, reducing storage requirements.
InnoDB page compression provides a way to compress InnoDB tables.
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 for more information.
InnoDB page compression is most beneficial on solid state drives (SSDs) and other flash storage. See for more information.
InnoDB page compression performs best when your storage device and file system support atomic writes, since that allows the to be disabled. See for more information.
COMPRESSED Row FormatInnoDB page compression is a modern way to compress your InnoDB tables. It is similar to InnoDB's 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 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 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 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 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
In general, InnoDB page compression is superior to the row format.
See .
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 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:
However, on many distributions, the standard MariaDB builds do not support all InnoDB page compression algorithms by default. From , algorithms can be .
This system variable can be changed dynamically with :
This system variable can also be set in a server in an prior to starting up the server:
On many distributions, the standard MariaDB builds do 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 compression algorithm is always supported. From , algorithms can be .
A MariaDB build's support for other InnoDB page compression algorithms can be checked by querying the following status variables with :
For example:
On many distributions, the standard MariaDB builds do not support all InnoDB page compression algorithms by default. From , algorithms can be , but in earlier versions, 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:
Configure the build using :
Check to confirm that it has found the desired compression library on your system.
Compile the build:
Either install the build:
Or make a package to install:
See for more information.
InnoDB page compression is not enabled by default. However, InnoDB page compression can be enabled for just individual InnoDB tables or it can be enabled for all new InnoDB tables by default.
InnoDB page compression is also only supported if the InnoDB table is in a tablespace. Therefore, the system variable must be set to ON to use InnoDB page compression.
InnoDB page compression is only supported if the InnoDB table uses the Barracuda .Therefore, in and before, the system variable must be set to Barracuda to use InnoDB page compression.
InnoDB page compression is also only supported if the InnoDB table's is or .
In and later, InnoDB page compression can be enabled for all new InnoDB tables by default by setting the system variable to ON.
This system variable can be set to one of the following values:
This system variable can be changed dynamically with :
This system variable's session value can be changed dynamically with :
This system variable can also be set in a server in an prior to starting up the server:
InnoDB page compression can be enabled for individual tables by setting the table option to 1:
Some InnoDB page compression algorithms support a compression level option, which configures how the InnoDB page compression algorithm will balance speed and compression.
The compression level's supported values range from 1 to 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 the following InnoDB page compression algorithms currently support compression levels:
If an InnoDB page compression algorithm does not support compression levels, then it ignores any provided compression level value.
The default compression level can be configured by setting the system variable.
This system variable's default value is 6.
This system variable can be changed dynamically with :
This system variable can also be set in a server in an prior to starting up the server:
The compression level for individual tables can also be configured by setting the table option for the table:
InnoDB page compression can encounter compression failures.
InnoDB page compression's failure threshold can be configured. If InnoDB encounters more compression failures than the failure threshold, then it pads pages with zeroed out bytes before attempting to compress them as a way to reduce failures. If the failure rate stays above the failure threshold, then InnoDB pads pages with more zeroed out bytes in 128 byte increments.
InnoDB page compression's maximum padding can also be configured.
The failure threshold can be configured by setting the system variable.
This system variable's supported values range from 0 to 100.
This system variable's default value is 5.
This system variable can be changed dynamically with :
This system variable can also be set in a server in an prior to starting up the server:
The maximum padding can be configured by setting the system variable.
This system variable's supported values range from 0 to 75.
This system variable's default value is 50.
This system variable can be changed dynamically with :
This system variable can also be set in a server in an prior to starting up the server:
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 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.
There are some potential disadvantages to using sparse files:
Some utilities may require special options in order to handle sparse files in an efficient manner.
Most existing file systems are slow to sparse files. As a consequence, if a tablespace file is a sparse file, then dropping the table can be very slow.
On Linux, the following file systems support sparse files:
ext3
ext4
xfs
btrfs
On Linux, file systems need to support the system call with the FALLOC_FL_PUNCH_HOLE and FALLOC_FL_KEEP_SIZE flags:
Some Linux utilities may require special options in order to work with sparse files efficiently:
The utility will report the non-sparse size of the tablespace file when executed with default behavior, but will report the actual amount of storage allocated for the tablespace file.
The utility is pretty good at auto-detecting sparse files, but it also provides the and options, if the auto-detection is not desired.
The utility will archive sparse files with their non-sparse size when executed with default behavior, but will auto-detect sparse files, and archive them with their sparse size.
On Windows, the following file systems support sparse files:
NTFS
On Windows, file systems need to support the function with the and control codes:
In and later, InnoDB uses the punch hole technique to create sparse files used automatically when the underlying file system supports sparse files.
In and before, InnoDB can be configured to use the punch hole technique to create sparse files by configuring the and system variables. These system variables can be set in a server in an prior to starting up the server:
InnoDB page compression was designed to be optimized on solid state drives (SSDs) and other flash storage.
InnoDB page compression was originally developed by collaborating with . As a consequence, it was originally designed to work best on using . has since been acquired by , and they have decided not to continue supporting .
However, InnoDB page compression is still likely to be most optimized on solid state drives (SSDs) and other flash storage.
InnoDB page compression works without any issues on hard disk drives (HDDs). However, since its compression relies on the use of sparse files, the data may be somewhat fragmented on disk. This fragmentation may hurt performance on HDDs, since they handle random reads and writes much more slowly than flash storage.
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 for more information.
InnoDB page compression can be monitored by querying the following status variables with :
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:
supports InnoDB page compression.
does not support InnoDB page compression.
InnoDB page compression was developed by collaborating with . Special thanks especially to Dhananjoy Das and Torben Mathiasen.
This page is licensed: CC BY-SA / Gnu FDL
snappy
Pages are compressed using the algorithm.
nvmfs
Number of 16384 sectors trimmed
Number of 32768 sectors trimmed
Number of pages compressed
Number of trim operations
Number of trim operations saved
Number of pages decompressed
Number of compression errors
none
Pages are not compressed. This is the default value in and before, and and before.
zlib
Pages are compressed using the bundled zlib compression algorithm. This is the default value in and later, and 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.
Whether InnoDB supports the lz4 compression algorithm.
Whether InnoDB supports the lzo compression algorithm.
Whether InnoDB supports the lzma compression algorithm.
Whether InnoDB supports the bzip2 compression algorithm.
Whether InnoDB supports the snappy compression algorithm.
OFF
New InnoDB tables do not use InnoDB page compression. This is the default value.
ON
New InnoDB tables use InnoDB page compression.
Bytes saved by compression
Number of 512 sectors trimmed
Number of 1024 sectors trimmed
Number of 2048 sectors trimmed
Number of 4096 sectors trimmed
Number of 8192 sectors trimmed
SET GLOBAL innodb_compression_algorithm='lzma';[mariadb]
...
innodb_compression_algorithm=lzmaSHOW 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 |
+--------------------+-------+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/cmake .makemake installmake packageSET GLOBAL innodb_compression_default=ON;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;[mariadb]
...
innodb_compression_default=ONSET 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;SET GLOBAL innodb_compression_level=9;[mariadb]
...
innodb_compression_level=9SET 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;SET GLOBAL innodb_compression_failure_threshold_pct=10;[mariadb]
...
innodb_compression_failure_threshold_pct=10SET GLOBAL innodb_compression_pad_pct_max=75;[mariadb]
...
innodb_compression_pad_pct_max=75fallocate(file_handle, FALLOC_FL_PUNCH_HOLE | FALLOC_FL_KEEP_SIZE, file_offset, remainder_len);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)[mariadb]
...
innodb_use_trim=ON
innodb_use_fallocate=ONCREATE 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 |
+----------------------------------+-------+