System Variables for MariaDB Enterprise ColumnStore 22.08

Overview

MariaDB Enterprise ColumnStore 22.08 can be configured using system variables.

Reference material is available for MariaDB Enterprise ColumnStore 22.08.

MariaDB Enterprise ColumnStore 22.08 is included with MariaDB Enterprise Server 10.6.

USAGE

There are many different ways to use system variables.

Set in a Configuration File

If the specific system variable is not read-only, you can set it in a MariaDB configuration file (such as my.cnf):

[mariadb]
columnstore_import_for_batchinsert_delimiter=44

Read All Values from Configuration Files

All system variables and other mariadbd command-line options in the system's MariaDB configuration file (such as my.cnf) can be read using the my_print_defaults utility with the --mysqld option:

$ my_print_defaults --mysqld

Set on the Command-Line

If the specific system variable has a corresponding mariadbd command-line option, you can set it when the server is started:

$ mariadbd --columnstore_import_for_batchinsert_delimiter=44 [OPTION ...]

Set the Global Value Dynamically

If the specific system variable is dynamic, you can set its global value with the SET GLOBAL statement:

SET GLOBAL columnstore_import_for_batchinsert_delimiter=44;

Show All Global Values

All global system variables can be queried with the SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES;

Set the Session Value Dynamically

If the specific system variable supports session-specific values, you can set its session value with the SET SESSION statement:

SET SESSION columnstore_import_for_batchinsert_delimiter=44;

Show All Session Values

All system variables for the current session can be queried with the SHOW SESSION VARIABLES statement:

SHOW SESSION VARIABLES;

Show Values with a Filter

It is also possible to filter the output of SHOW VARIABLES, so that it does not return all values.

The SHOW VARIABLES statements can be filtered with a LIKE clause:

SHOW GLOBAL VARIABLES
   LIKE 'columnstore_%';

The SHOW VARIABLES statements can also be filtered with a WHERE clause:

SHOW GLOBAL VARIABLES
   WHERE Variable_name IN(
      'columnstore_import_for_batchinsert_delimiter',
      'columnstore_import_for_batchinsert_enclosed_by'
   );

Reference Values in a Query

A specific global or session system variable can be referenced in a query by prefixing the variable name with @@global. or @@session.:

SELECT @@session.columnstore_import_for_batchinsert_delimiter,
   @@session.columnstore_import_for_batchinsert_enclosed_by;

DETAILS

MariaDB Enterprise Server 10.6 supports many system variables. When MariaDB Enterprise ColumnStore 22.08 is deployed with MariaDB Enterprise Server 10.6, it adds several system variables of its own. The system variables implemented by Enterprise ColumnStore 22.08 have the columnstore_ prefix. However, the behavior of Enterprise ColumnStore 22.08 can also be affected by some standard system variables implemented by ES 10.6.

System Variables for MariaDB Enterprise ColumnStore 22.08

Variable

Description

The following System Variables are not present in MariaDB Enterprise ColumnStore 22.08.8 but are present in one or more older 22.08 versions. Click on an item to see its details, including when it was removed.

Variable

Description

columnstore_compression_type

Controls compression algorithm for create tables. Possible values are: NO_COMPRESSION segment files aren't compressed; SNAPPY segment files are Snappy compressed (default);

columnstore_decimal_scale

The default decimal precision for calculated column sub-operations

columnstore_derived_handler

Enable/Disable the MCS derived_handler

columnstore_diskjoin_bucketsize

The maximum size in MB of each 'small side' table in memory

columnstore_diskjoin_largesidelimit

The maximum amount of disk space in MB to use per join for storing 'large side' table data for a disk-based join. (0 = unlimited)

columnstore_diskjoin_smallsidelimit

The maximum amount of disk space in MB to use per query for storing 'small side' tables for a disk-based join. (0 = unlimited)

columnstore_double_for_decimal_math

Enable/disable for ColumnStore to replace DECIMAL with DOUBLE in arithmetic operation

columnstore_group_by_handler

Enable/Disable the MCS group_by_handler

columnstore_import_for_batchinsert_delimiter

ASCII value of the delimiter used by LDI and INSERT..SELECT

columnstore_import_for_batchinsert_enclosed_by

ASCII value of the quote symbol used by batch data ingestion

columnstore_local_query

Enable/disable the ColumnStore local PM query only feature

columnstore_orderby_threads

Number of parallel threads used by ORDER BY. (default to 16)

columnstore_ordered_only

Always use the first table in the from clause as the large side table for joins

columnstore_replication_slave

Allow this MariaDB server to apply replication changes to ColumnStore

columnstore_select_handler

Enables the ColumnStore select handler, which allows ColumnStore to plan its own queries

columnstore_string_scan_threshold

Max number of blocks in a dictionary file to be scanned for filtering

columnstore_stringtable_threshold

The minimum width of a string column to be stored in a string table

columnstore_um_mem_limit

Per user Memory limit(MB). Switch to disk-based JOIN when limit is reached

columnstore_use_decimal_scale

Enable/disable the MCS decimal scale to be used internally

columnstore_use_import_for_batchinsert

LOAD DATA INFILE and INSERT..SELECT will use cpimport internally

columnstore_varbin_always_hex

Always display/process varbinary columns as if they have been hexified

To see system variables supported in other versions, see "System Variables by MariaDB Enterprise ColumnStore Version".

System Variables for MariaDB Enterprise Server 10.6

Since MariaDB Enterprise ColumnStore 22.08 integrates with MariaDB Enterprise Server 10.6, some of the System Variables for MariaDB Enterprise Server 10.6 also apply to MariaDB Enterprise ColumnStore 22.08.