MariaDB Enterprise ColumnStore Configure Compression

Overview

MariaDB Enterprise ColumnStore compresses every table using snappy compression by default. In MariaDB Enterprise ColumnStore 6, LZ4 compression is also supported.

Supported Compression Algorithms

MariaDB Enterprise ColumnStore supports the following compression algorithms:

Algorithm

Description

columnstore_compression_type Values

COMMENT 'compression=n' Values

LZ4

ColumnStore tables are compressed using LZ4 compression, which is supported in MariaDB Enterprise ColumnStore 6.

LZ4

3 (Enterprise ColumnStore 6)

None

ColumnStore tables are not compressed. Compression is mandatory in MariaDB Enterprise ColumnStore 6, so this option is not supported in that version.

NO_COMPRESSION

0 (Enterprise ColumnStore 5)

Snappy

ColumnStore tables are compressed using snappy compression. Snappy compression is the default in all versions of MariaDB Enterprise ColumnStore.

SNAPPY

  • 0, 1, or 2 (Enterprise ColumnStore 6)

  • 1 or 2 (Enterprise ColumnStore 5)

Global Compression Type

The global compression type applies to all sessions on the server.

Check Global Compression Type

The compression type used by MariaDB Enterprise ColumnStore is determined by the columnstore_compression_type system variable. The columnstore_compression_type system variable is set to SNAPPY by default. However, the value can be changed.

To check the compression type on your system, use SHOW GLOBAL VARIABLES:

SHOW GLOBAL VARIABLES
   LIKE 'columnstore_compression_type';

Set the Global Compression Type

You can configure the global compression type by setting the columnstore_compression_type system variable. To configure the global compression type:

  1. Set the columnstore_compression_type system variable in a configuration file:

    [mariadb]
columnstore_compression_type=LZ4

    When the columnstore_compression_type system variable is set in a configuration file, the server must be restarted to use the new value.

  2. To avoid a server restart, dynamically set the global value of the columnstore_compression_type system variable by executing SET GLOBAL:

    SET GLOBAL columnstore_compression_type='LZ4';

    When the global value of the columnstore_compression_type system variable is set dynamically at runtime, its value will be reset the next time the server restarts, so make sure to set it in a configuration file too.

Session Compression Type

The session compression type applies only to the current session.

Check Session Compression Type

The compression type used by MariaDB Enterprise ColumnStore is determined by the columnstore_compression_type system variable. The columnstore_compression_type system variable is set to SNAPPY by default. However, the value can be changed.

To check the session compression type on your system, use SHOW SESSION VARIABLES:

SHOW SESSION VARIABLES
   LIKE 'columnstore_compression_type';

Set the Session Compression Type

You can configure the session compression type by setting the columnstore_compression_type system variable.

To configure the session compression type, dynamically set the session value of the columnstore_compression_type system variable by executing SET SESSION:

SET SESSION columnstore_compression_type='LZ4';

When the session compression type is changed, all new ColumnStore tables created by that session will use the session compression type.

Table Compression Type

You can configure the compression type for a specific table by setting COMMENT 'compression=n'.

For a new table, you can specify COMMENT 'compression=n' in the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method VARCHAR(50)
) ENGINE=ColumnStore
COMMENT 'compression=3';

Column Compression Type

You can configure the compression type for a specific column by setting COMMENT 'compression=n'.

For a column in a new table, you can specify COMMENT 'compression=n' in the column definition in the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2) COMMENT 'compression=3',
   payment_method VARCHAR(50)
) ENGINE=ColumnStore;