MariaDB Enterprise Server Configure InnoDB Page Compression

Overview

In MariaDB Enterprise Server, InnoDB supports table compression using InnoDB Page Compression. InnoDB Page Compression supports any InnoDB row format.

About InnoDB Page Compression

InnoDB Page Compression:

  • Is the recommended compression method for InnoDB tables:

  • Supports the zlib compression algorithm.

  • Compresses each page immediately before writing it to the tablespace file.

  • Creates variable-sized compressed pages.

  • Saves storage space and decreases I/O by treating tablespace files as sparse files.

  • Decompresses each page immediately after reading it from the tablespace file.

  • Only stores decompressed pages in the InnoDB buffer pool.

Basic Usage

CREATE DATABASE hq_sales;

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
)
ENGINE = InnoDB
PAGE_COMPRESSED = 1;

SELECT NAME,
   IF(
      (FLAG & 128) = 128,
      'Compressed',
      'Not Compressed'
   ) AS Is_Compressed,
   (FLAG >> 8) AS Compression_Level
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+---------------+-------------------+
| NAME              | Is_Compressed | Compression_Level |
+-------------------+---------------+-------------------+
| hq_sales/invoices | Compressed    |                 9 |
+-------------------+---------------+-------------------+

InnoDB Page Compression vs. InnoDB's Compressed Row Format

InnoDB Page Compression works a bit differently than the Compressed row format:

Feature

InnoDB Page Compression

Compressed Row Format

Recommended

Yes

No

Efficiently utilizes buffer pool

Yes

No

Efficiently minimizes compression operations

Yes

No

Read/write

Yes

Not much longer*

Algorithms

zlib

zlib

Compressed page size

Variable (relies on sparse files)

Specified by the KEY_BLOCK_SIZE table option

Caches in buffer pool

Only decompressed pages

Both compressed and decompressed pages

Compression timing

Compresses each page immediately before writing it to the tablespace file

Compresses each page immediately after data on the page changes

Decompression timing

Decompresses each page immediately after reading it from the tablespace file

Decompresses each page immediately after reading it from the tablespace file

*:

ROW_FORMAT=COMPRESSED format tables are read-only by default in the latest releases, and the option to switch them back to read/write will be going away in a future release.

Create Compressed InnoDB Tables

Create Compressed InnoDB Tables using the innodb_compression_default System Variable

Compressed InnoDB tables can be created by setting the session value of the innodb_compression_default system variable to ON:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. In your current session, set the session value of the innodb_compression_default system variable to ON using the SET SESSION statement.

    For example:

    SET SESSION innodb_compression_default=ON;
    
  3. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. Create the table using the CREATE TABLE statement:

    CREATE TABLE hq_sales.invoices (
       invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
       branch_id INT NOT NULL,
       customer_id INT,
       invoice_date DATETIME(6),
       invoice_total DECIMAL(13, 2),
       payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
       PRIMARY KEY(invoice_id)
    );
    
  5. Confirm that the table uses InnoDB page compression by querying the information_schema.INNODB_SYS_TABLES table:

    SELECT NAME,
       IF(
          (FLAG & 128) = 128,
          'Compressed',
          'Not Compressed'
       ) AS Is_Compressed
    FROM information_schema.INNODB_SYS_TABLES
    WHERE NAME='hq_sales/invoices';
    
    +-------------------+---------------+
    | NAME              | Is_Compressed |
    +-------------------+---------------+
    | hq_sales/invoices | Compressed    |
    +-------------------+---------------+
    

Create Compressed InnoDB Tables using the PAGE_COMPRESSED Table Option

Compressed InnoDB tables can be created by providing the PAGE_COMPRESSED table option to the CREATE TABLE statement:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  3. Create the table using the CREATE TABLE statement, and enable InnoDB page compression using the PAGE_COMPRESSED table option:

    CREATE TABLE hq_sales.invoices (
       invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
       branch_id INT NOT NULL,
       customer_id INT,
       invoice_date DATETIME(6),
       invoice_total DECIMAL(13, 2),
       payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
       PRIMARY KEY(invoice_id)
    ) PAGE_COMPRESSED = 1;
    
  4. Confirm that the table uses InnoDB page compression by querying the information_schema.INNODB_SYS_TABLES table:

    SELECT NAME,
       IF(
          (FLAG & 128) = 128,
          'Compressed',
          'Not Compressed'
       ) AS Is_Compressed
    FROM information_schema.INNODB_SYS_TABLES
    WHERE NAME='hq_sales/invoices';
    
    +-------------------+---------------+
    | NAME              | Is_Compressed |
    +-------------------+---------------+
    | hq_sales/invoices | Compressed    |
    +-------------------+---------------+
    

Compression Level

Some compression algorithms have the concept of a "compression level." The compression level determines how the algorithm balances 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.

Configure the Compression Level using the innodb_compression_level System Variable

The compression level can be configured by setting the innodb_compression_level system variable:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. Set the innodb_compression_level system variable to the desired compression level using the SET GLOBAL statement.

    For example:

    SET GLOBAL innodb_compression_level=9;
    
  1. Choose a configuration file for custom changes to system variables and options.

    It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

    Ensure that your custom changes will be read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

    Some example configuration file paths for different distributions are shown in the following table:

    Distributions

    Example configuration file path

    CentOS
    RHEL
    Rocky Linux
    SLES

    /etc/my.cnf.d/z-custom-mariadb.cnf

    Debian
    Ubuntu

    /etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

  1. Set the innodb_compression_level system variable in the configuration file.

    It needs to be set in a group that will be read by MariaDB Server, such as [mariadb] or [server].

    For example:

    [mariadb]
    ...
    innodb_compression_level=9
    

Configure the Compression Level using the PAGE_COMPRESSION_LEVEL Table Option

The compression level for an InnoDB table can be set by providing the PAGE_COMPRESSION_LEVEL table option to the CREATE TABLE statement:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  3. Create the table using the CREATE TABLE statement, and set the compression level using the PAGE_COMPRESSION_LEVEL table option:

    CREATE TABLE hq_sales.invoices (
       invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
       branch_id INT NOT NULL,
       customer_id INT,
       invoice_date DATETIME(6),
       invoice_total DECIMAL(13, 2),
       payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
       PRIMARY KEY(invoice_id)
    )
    PAGE_COMPRESSED = 1
    PAGE_COMPRESSION_LEVEL=9;
    

Query an InnoDB Table's Compression Level

Query the compression level for an InnoDB table by querying the information_schema.INNODB_SYS_TABLES table:

SELECT NAME,
   IF(
      (FLAG & 128) = 128,
      'Compressed',
      'Not Compressed'
   ) AS Is_Compressed,
   (FLAG >> 8) AS Compression_Level
FROM information_schema.INNODB_SYS_TABLES
WHERE NAME='hq_sales/invoices';
+-------------------+---------------+-------------------+
| NAME              | Is_Compressed | Compression_Level |
+-------------------+---------------+-------------------+
| hq_sales/invoices | Compressed    |                 9 |
+-------------------+---------------+-------------------+

Finding Compressed InnoDB Tables

Let's find compressed InnoDB tables by querying the information_schema.INNODB_SYS_TABLES table:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. Find tables that use InnoDB page compression by querying the information_schema.INNODB_SYS_TABLES table:

    SELECT NAME,
       IF(
          (FLAG & 128) = 128,
          'Compressed',
          'Not Compressed'
       ) AS Is_Compressed,
       (FLAG >> 8) AS Compression_Level
    FROM information_schema.INNODB_SYS_TABLES
    WHERE NAME NOT LIKE 'SYS_%'
    AND NAME NOT LIKE 'mysql/%';
    
    +-------------------+---------------+-------------------+
    | NAME              | Is_Compressed | Compression_Level |
    +-------------------+---------------+-------------------+
    | hq_sales/invoices | Compressed    |                 9 |
    +-------------------+---------------+-------------------+