MariaDB Enterprise Server Configure InnoDB Page Compression

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

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;
   

3. 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

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