InnoDB Page Compression – MySQL 8 Compression

InnoDB, InnoDB file format, InnoDB Page Compression, MySQL, MySQL Performance

InnoDB Page Compression Explained 

We have several customers with multi-terabyte database infrastructure on SSDs, The SSDs are great investment for performance but they are also expensive with shorter lifespans so storage efficiency management is something we are very cautious about on SSDs, At MinervaDB Labs we spend considerable amount of time doing research on InnoDB page compressions benefits and most common mistakes. The compressed tables were first introduced in 2008  with InnoDB plugin for MySQL 5.1 . Facebook has been a major committer to this project and most of it were later implemented in upstream MySQL code as well. We can implement compression in InnoDB is two ways, Either by using Barracuda InnoDB file format or ROW_FORMAT=COMPRESSED

How InnoDB page compression works ?

When a page is written, It will be compressed applying the specific compression algorithm and written to disk, where the hole punching mechanism ( Several popular Linux file systems already support the hole punching feature. For example: XFS since Linux 2.6.38, ext4 since Linux3.0, tmpfs (/dev/shm  ) since Linux 3.5, and Btrfs since Linux 3.7.)releases empty blocks from the end of the page. If compression fails, data is written out as-is. MySQL implements compression with the help of the well-known zlib library, which implements the LZ77 compression algorithm. Some operating systems implement compression at the file system level. Files are typically divided into fixed-size blocks that are compressed into variable-size blocks, which easily leads into fragmentation.if innodb_page_size=16K and the file system block size is 4K, page data must compress to less than or equal to 12K to make hole punching possible so every time something inside a block is modified, the whole block is recompressed before it is written to disk. These properties make InnoDB compression technique unsuitable for use in an update-intensive database system.

InnoDB Page Compression Supported Platforms

InnoDB page compression requires sparse file and hole punching support, You can implement page compression on Windows with NTFS and following are the Linux platforms which support hole punching:

  • RHEL 7 and derived distributions that use kernel version 3.10.0-123 or higher
  • OEL 5.10 (UEK2) kernel version 2.6.39 or higher
  • OEL 6.5 (UEK3) kernel version 3.8.13 or higher
  • OEL 7.0 kernel version 3.8.13 or higher
  • SLE11 kernel version 3.0-x
  • SLE12 kernel version 3.12-x
  • OES11 kernel version 3.0-x
  • Ubuntu 14.0.4 LTS kernel version 3.13 or higher
  • Ubuntu 12.0.4 LTS kernel version 3.2 or higher
  • Debian 7 kernel version 3.2 or higher

InnoDB compression on Windows

The way NTFS clustering is designed we hardly get any benefit with InnoDB compression. The hole punch is done on a “compression unit” and this compression unit is derived from the cluster size (see the table below). This means that by default you cannot punch a hole if the cluster size >= 8K. Here’s a breakdown for smaller cluster sizes:

Cluster SizeCompression Unit
512 Bytes8 KB
1 KB16 KB
2 KB32 KB
4 KB64 KB

i.e. for page compression to work on Windows, the file system must be created with a cluster size smaller than 4K, and the innodb_page_size must be at least twice the size of the compression unit. For example, for page compression to work on Windows, you could build the file system with a cluster size of 512 Bytes (which has a compression unit of 8KB) and initialize InnoDB with an innodb_page_size value of 16K or greater.

How enable InnoDB compression ?

You can enabled page compression by specifying the COMPRESSION attribute in the CREATE TABLE statement, We have explained same below with example:

CREATE TABLE tab11 (col101 INT) COMPRESSION="zlib";

We can also enable page compression with ALTER TABLE statement. But ALTER TABLE … COMPRESSION updates only the tablespace compression attribute.  Writes to the tablespace that occur after setting the new compression algorithm use the new setting, To enable the new compression algorithm to existing pages, you must rebuild the table using OPTIMIZE TABLE.

Disabling InnoDB Page Compression

You can disable InnoDB page compression by setting COMPRESSION = None in ALTER TABLE statement. Writes to the tablespace that occur after setting COMPRESSION=None no longer use page compression. To uncompress existing pages, you must rebuild the table using OPTIMIZE TABLE after setting COMPRESSION=None.

Monitoring tables using Page Compression

You can monitor the tables with page compression enabled from INFORMATION_SCHEMA.TABLES CREATE_OPTIONS columns for tables defined with the COMPRESSION attribute:

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';

+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
+------------+--------------+--------------------+
| Ad_Clicks  | ADSE         | COMPRESSION="zlib" |
+------------+--------------+--------------------+

InnoDB Page Compression Limitations

  • Cross-platform dependency issues – We can copy the page-compressed tablespaces between Linux and Windows servers only if the compression algorithm is available on both servers
  • InnoDB page compression doesn’t work always as expected:
    • Page compression is not supported for tables that reside in shared tablespaces, which include the system tablespace, temporary tablespaces, and general tablespaces.
    • Page compression is not supported for undo log tablespaces.
    • Page compression is not supported for redo log pages.
    • During recovery, updated pages are written out in an uncompressed form.
    • R-tree pages, which are used for spatial indexes, are not compressed.
    • Pages that belong to compressed tables (ROW_FORMAT=COMPRESSED) are just left as-is.
  • During recovery, updated pages are written out in an uncompressed form.
  • Fragmentation issues – Since hole punching releasing blocks back to the file system free list, You can expect severe fragmentation in I/O intensive applications

References 

About MinervaDB Corporation 88 Articles
Independent and vendor neutral consulting, support, remote DBA services and training for MySQL, MariaDB, Percona Server, PostgreSQL and ClickHouse with core expertize in performance, scalability and high availability . We are an virtual corporation, all of us work from home on multiple timezones and stay connected via Email, Skype, Google Hangouts, Phone and IRC supporting over 250 customers worldwide
Contact: Website Facebook Twitter

MySQL, InnoDB and Oracle are registered trademarks of Oracle Corp. MariaDB is a trademark of Monty Program AB. All other trademarks are property of their respective owners. Other product or company names mentioned may be trademarks or trade names of their respective owner. Copyrights © 2010-2022 All Rights Reserved by MinervaDB®.

UA-155183614-1