1 of 58

Managing ColumnStore

Managing MariaDB ColumnStore involves setup, configuration, and tools like mcsadmin and cpimport for efficient analytics.

Deployment

MariaDB ColumnStore Hardware Guide Installing ColumnStore Upgrading ColumnStore

Installing ColumnStore

This section provides instructions for installing and configuring MariaDB ColumnStore. It covers various deployment scenarios, including single- and multi-node setups with both local and S3 storage.

MariaDB ColumnStore Hardware Guide Single-Node Localstorage Single-Node Localstorage Single-Node S3 Multi-Node S3

Step 1: Prepare Systems for Enterprise ColumnStore Nodes

Overview

This page details step 1 of a 5-step procedure for deploying Single-Node Enterprise ColumnStore with Local storage.

This step prepares the system to host MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Optimize Linux Kernel Parameters

MariaDB Enterprise ColumnStore performs best with Linux kernel optimizations.

On each server to host an Enterprise ColumnStore node, optimize the kernel:

Set the relevant kernel parameters in a sysctl configuration file. To ensure proper change management, use an Enterprise ColumnStore-specific configuration file.

Create a /etc/sysctl.d/90-mariadb-enterprise-columnstore.conf file:

Use the sysctl command to set the kernel parameters at runtime

Temporarily Configure Linux Security Modules (LSM)

The Linux Security Modules (LSM) should be temporarily disabled on each Enterprise ColumnStore node during installation.

The LSM will be configured and re-enabled later in this deployment procedure.

The steps to disable the LSM depend on the specific LSM used by the operating system.

CentOS / RHEL Stop SELinux

SELinux must be set to permissive mode before installing MariaDB Enterprise ColumnStore.

To set SELinux to permissive mode:

Set SELinux to permissive mode:

Set SELinux to permissive mode by setting SELINUX=permissive in /etc/selinux/config.

For example, the file will usually look like this after the change:

Confirm that SELinux is in permissive mode:

SELinux will be configured and re-enabled later in this deployment procedure. This configuration is not persistent. If you restart the server before configuring and re-enabling SELinux later in the deployment procedure, you must reset the enforcement to permissive mode.

Debian / Ubuntu AppArmor

AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.

Disable AppArmor:

Reboot the system.
Confirm that no AppArmor profiles are loaded using aa-status:

AppArmor will be configured and re-enabled later in this deployment procedure.

Configure Character Encoding

When using MariaDB Enterprise ColumnStore, it is recommended to set the system's locale to UTF-8.

On RHEL 8, install additional dependencies:

Set the system's locale to en_US.UTF-8 by executing localedef:

Next Step

Navigation in the Single-Node Enterprise ColumnStore topology with Local storage deployment procedure:

This page was step 1 of 5.

Step 2: Install Enterprise ColumnStore

Overview

This page details step 2 of a 5-step procedure for deploying .

This step installs MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Step 3: Start and Configure Enterprise ColumnStore

Overview

This page details step 3 of a 5-step procedure for deploying .

This step starts and configures MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Step 4: Test Enterprise ColumnStore

Overview

This page details step 4 of a 5-step procedure for deploying .

This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Step 5: Bulk Import of Data

Overview

This page details step 5 of a 5-step procedure for deploying Single-Node Enterprise ColumnStore with Local storage.

This step bulk imports data to Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Import the Schema

Before data can be imported into the tables, create a matching schema.

On the primary server, create the schema:

For each database that you are importing, create the database with the statement:

For each table that you are importing, create the table with the statement:

Import the Data

Enterprise ColumnStore supports multiple methods to import data into ColumnStore tables.

cpimport

MariaDB Enterprise ColumnStore includes , which is a command-line utility designed to efficiently load data in bulk. Alternative methods are available.

To import your data from a TSV (tab-separated values) file, on the primary server run :

LOAD DATA INFILE

When data is loaded with the statement, MariaDB Enterprise ColumnStore loads the data using , which is a command-line utility designed to efficiently load data in bulk. Alternative methods are available.

To import your data from a TSV (tab-separated values) file, on the primary server use statement:

Import from Remote Database

MariaDB Enterprise ColumnStore can also import data directly from a remote database. A simple method is to query the table using the statement, and then pipe the results into , which is a command-line utility that is designed to efficiently load data in bulk. Alternative methods are available.

To import your data from a remote MariaDB database:

Next Step

Navigation in the Single-Node Enterprise ColumnStore topology with Local storage deployment procedure:

This page was step 5 of 5.

This procedure is complete.

Step 6: Install MariaDB MaxScale

Overview

This page details step 6 of the 9-step procedure "Deploy ColumnStore Shared Local Storage Topology".

This step installs MariaDB MaxScale 22.08. ColumnStore Object Storage requires 1 or more MaxScale nodes.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Retrieve Customer Download Token

MariaDB Corporation provides package repositories for CentOS / RHEL (YUM) and Debian / Ubuntu (APT). A download token is required to access the MariaDB Enterprise Repository.

Customer Download Tokens are customer-specific and are available through the MariaDB Customer Portal.

To retrieve the token for your account:

Navigate to
Log in.
Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

On the MaxScale node, install the prerequisites for downloading the software from the Web. Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

On the MaxScale node, configure package repositories and specify MariaDB MaxScale 22.08:

Checksums of the various releases of the mariadb_es_repo_setup script can be found in the section at the bottom of the page. Substitute ${checksum} in the example above with the latest checksum.

Install MaxScale

On the MaxScale node, install MariaDB MaxScale.

Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

Next Step

Navigation in the procedure "Deploy ColumnStore Shared Local Storage Topology".

This page was step 6 of 9.

Step 6: Install MariaDB MaxScale

Overview

This page details step 6 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".

This step installs MariaDB MaxScale 22.08.

ColumnStore Object Storage requires 1 or more MaxScale nodes.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Step 2: Install Enterprise ColumnStore

Overview

This page details step 2 of a 5-step procedure for deploying Single-Node Enterprise ColumnStore with Object storage.

This step installs MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Step 5: Bulk Import of Data

Overview

This page details step 5 of a 5-step procedure for deploying Single-Node Enterprise ColumnStore with Object storage.

This step bulk imports data to Enterprise ColumnStore.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Import the Schema

Before data can be imported into the tables, create a matching schema.

On the primary server, create the schema:

For each database that you are importing, create the database with the statement:

For each table that you are importing, create the table with the statement:

Import the Data

Enterprise ColumnStore supports multiple methods to import data into ColumnStore tables.

cpimport

MariaDB Enterprise ColumnStore includes , which is a command-line utility designed to efficiently load data in bulk. Alternative methods are available.

To import your data from a TSV (tab-separated values) file, on the primary server run :

LOAD DATA INFILE

When data is loaded with the LOAD DATA INFILE statement, MariaDB Enterprise ColumnStore loads the data using , which is a command-line utility designed to efficiently load data in bulk. Alternative methods are available.

To import your data from a TSV (tab-separated values) file, on the primary server use LOAD DATA INFILE statement:

Import from Remote Database

To import your data from a remote MariaDB database:

Next Step

Navigation in the Single-Node Enterprise ColumnStore topology with Object storage deployment procedure:

This page was step 5 of 5.

This procedure is complete.

Upgrading ColumnStore

Upgrade Multi-Node MariaDB Enterprise ColumnStore from 6 to 23.10 Major Release Upgrades for MariaDB Enterprise ColumnStore

Managing ColumnStore Database Environment

Managing MariaDB ColumnStore means deploying its architecture, scaling modules, and maintaining performance through monitoring, optimization, and backups.

Node Maintenance for MariaDB Enterprise Columnstore

Rejoining a Node

To rejoin a node with Enterprise ColumnStore, perform the following procedure.

Performing Rejoin in MaxScale

The node can be configured to rejoin in MaxScale using :

Use or another supported REST client.
Call a module command using the call command command.
As the first argument, provide the name for the module, which is .
As the second argument, provide the module command, which is rejoin .
As the third argument, provide the name of the monitor.
As the fourth argument, provide the name of the server.

For example:

Checking Replication Status with MaxScale

MaxScale is capable of checking the status of using :

List the servers using the list servers command, like this:

If the node properly rejoined, the State column of the node shows Slave, Running.

Setting a Node to Maintenance Mode

To set a node to maintenance mode with Enterprise ColumnStore, perform the following procedure.

Setting the Server State in MaxScale

The server object for the node can be set to maintenance mode in MaxScale using :

Use or another supported REST client.

Switchover of the Primary Node

To switchover to a new primary node with Enterprise ColumnStore, perform the following procedure.

Performing Switchover in MaxScale

The primary node can be switched in MaxScale using :

Use or another supported REST client.
Call a module command using the call command command.
As the first argument, provide the name for the module, which is .
As the second argument, provide the module command, which is switchover .
As the third argument, provide the name of the monitor.

For example:

With the above syntax, MaxScale will choose the most up-to-date replica to be the new primary.

If you want to manually select a new primary, provide the server name of the new primary as the fourth argument:

Checking the Replication Status with MaxScale

MaxScale is capable of checking the status of using :

List the servers using the list servers command, like this:

If switchover was properly performed, the State column of the new primary shows Master, Running.

View and Clear Table Locks

MariaDB Enterprise ColumnStore acquires table locks for some operations, and it provides utilities to view and clear those locks.

MariaDB Enterprise ColumnStore acquires table locks for some operations, such as:

DDL statements
DML statements
Bulk data loads

If an operation fails, the table lock does not always get released. If you try to access the table, you can see errors like the following:

To solve this problem, MariaDB Enterprise ColumnStore provides two utilities to view and clear the table locks:

cleartablelock
viewtablelock

Viewing Table Locks

The viewtablelock utility shows table locks currently held by MariaDB Enterprise ColumnStore:

To view all table locks:

To view table locks for a specific table, specify the database and table:

Clearing Table Locks

The cleartablelock utility clears table locks currently held by MariaDB Enterprise ColumnStore.

To clear a table lock, specify the lock ID shown by the viewtablelock utility:

Backup & Restore

MariaDB ColumnStore backup and restore manage distributed data using snapshots or tools like mariadb-backup, with restoration ensuring cluster sync via cpimport or file system recovery.

ColumnStore Table Size Limitations

MariaDB ColumnStore has a hard limit of 4096 columns per table.

However, it's likely that you run into other limitations before hitting that limit, including:

Row size limit of tables. This varies, depending on the storage engine you're using. For example, which indirectly limits the number of columns.
Size limit of .frm files. Those files hold the column description of tables. Column descriptions vary in length. Once all column descriptions combined reach a length of 64KB, the table's .frm file is full, limiting the number of columns you can have in a table.

Given that, the maximum number of columns a ColumnStore table can effectively have is around 2000 columns.

Step 7: Start and Configure MariaDB MaxScale

Overview

This page details step 7 of the 9-step procedure "Deploy ColumnStore Shared Local Storage Topology".

This step starts and configures MariaDB MaxScale 22.08.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Replace the Default Configuration File

MariaDB MaxScale installations include a configuration file with some example objects. This configuration file should be replaced.

On the MaxScale node, replace the default /etc/maxscale.cnf with the following configuration:

For additional information, see "Global Parameters".

Restart MaxScale

On the MaxScale node, restart the MaxScale service to ensure that MaxScale picks up the new configuration:

For additional information, see "Start and Stop Services".

Configure Server Objects

On the MaxScale node, use to create a server object for each Enterprise ColumnStore node:

Configure MariaDB Monitor

MaxScale uses monitors to retrieve additional information from the servers. This information is used by other services in filtering and routing connections based on the current state of the node. For MariaDB Enterprise ColumnStore, use the MariaDB Monitor (mariadbmon).

On the MaxScale node, use to create a MariaDB Monitor:

In this example:

columnstore_monitor is an arbitrary name that is used to identify the new monitor.
mariadbmon is the name of the module that implements the MariaDB Monitor.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to monitor the ColumnStore nodes.

Choose a MaxScale Router

Routers control how MaxScale balances the load between Enterprise ColumnStore nodes. Each router uses a different approach to routing queries. Consider the specific use case of your application and database load and select the router that best suits your needs.

Router

Configuration Procedure

Description

Configure Read Connection Router

Use to route connections to replica servers for a read-only pool.

On the MaxScale node, use to create a router:

In this example:

connection_router_service is an arbitrary name that is used to identify the new service.
readconnroute is the name of the module that implements the Read Connection Router.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to connect to the ColumnStore nodes.

Configure Listener for the Read Connection Router

These instructions reference TCP port 3308. You can use a different TCP port. The TCP port used must not be bound by any other listener.

On the MaxScale node, use the command to configure MaxScale to use a listener for the :

In this example:

connection_router_service is the name of the readconnroute service that was previously created.
connection_router_listener is an arbitrary name that is used to identify the new listener.
3308 is the TCP port.

Configure Read/Write Split Router for Queries

MaxScale performs query-based load balancing. The router routes write queries to the primary and read queries to the replicas.

On the MaxScale node, use the maxctrl create service command to configure MaxScale to use the :

In this example:

query_router_service is an arbitrary name that is used to identify the new service.
readwritesplit is the name of the module that implements the Read/Write Split Router.
user=MAXSCALE_USER sets the user parameter to the database user account that MaxScale uses to connect to the ColumnStore nodes.

Configure a Listener for the Read/Write Split Router

These instructions reference TCP port 3307. You can use a different TCP port. The TCP port used must not be bound by any other listener.

On the MaxScale node, use the command to configure MaxScale to use a listener for the :

In this example:

query_router_service is the name of the readwritesplit service that was previously created.
query_router_listener is an arbitrary name that is used to identify the new listener.
3307 is the TCP port.
protocol=MariaDBClient

Start Services

To start the services and monitors, on the MaxScale node use :

Next Step

Navigation in the procedure "Deploy ColumnStore Shared Local Storage Topology".

This page was step 7 of 9.

Step 1: Prepare ColumnStore Nodes

Overview

This page details step 1 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".

This step prepares systems to host MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Optimize Linux Kernel Parameters

MariaDB Enterprise ColumnStore performs best with Linux kernel optimizations.

On each server to host an Enterprise ColumnStore node, optimize the kernel:

Set the relevant kernel parameters in a sysctl configuration file. To ensure proper change management, use an Enterprise ColumnStore-specific configuration file.

Create a /etc/sysctl.d/90-mariadb-enterprise-columnstore.conf file:

Use the sysctl command to set the kernel parameters at runtime

Temporarily Configure Linux Security Modules (LSM)

The Linux Security Modules (LSM) should be temporarily disabled on each Enterprise ColumnStore node during installation.

The LSM will be configured and re-enabled later in this deployment procedure.

The steps to disable the LSM depend on the specific LSM used by the operating system.

CentOS / RHEL Stop SELinux

SELinux must be set to permissive mode before installing MariaDB Enterprise ColumnStore.

To set SELinux to permissive mode:

Set SELinux to permissive mode:

Set SELinux to permissive mode by setting SELINUX=permissive in /etc/selinux/config.

For example, the file will usually look like this after the change:

Confirm that SELinux is in permissive mode:

Debian / Ubuntu AppArmor

AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.

Disable AppArmor:

Reboot the system.
Confirm that no AppArmor profiles are loaded using aa-status:

AppArmor will be configured and re-enabled later in this deployment procedure.

Temporarily Configure Firewall for Installation

MariaDB Enterprise ColumnStore requires the following TCP ports:

TCP Ports

Description

The firewall should be temporarily disabled on each Enterprise ColumnStore node during installation.

The firewall will be configured and re-enabled later in this deployment procedure.

The steps to disable the firewall depend on the specific firewall used by the operating system.

CentOS / RHEL Stop firewalld

Check if the firewalld service is running:

If the firewalld service is running, stop it:

Firewalld will be configured and re-enabled later in this deployment procedure.

Ubuntu Stop UFW

Check if the UFW service is running:

If the UFW service is running, stop it:

UFW will be configured and re-enabled later in this deployment procedure.

Configure the AWS Security Group

To install Enterprise ColumnStore on Amazon Web Services (AWS), the security group must be modified prior to installation.

Enterprise ColumnStore requires all internal communications to be open between Enterprise ColumnStore nodes. Therefore, the security group should allow all protocols and all ports to be open between the Enterprise ColumnStore nodes and the MaxScale proxy.

Configure Character Encoding

When using MariaDB Enterprise ColumnStore, it is recommended to set the system's locale to UTF-8.

On RHEL 8, install additional dependencies:

Set the system's locale to en_US.UTF-8 by executing localedef:

Configure DNS

MariaDB Enterprise ColumnStore requires all nodes to have host names that are resolvable on all other nodes. If your infrastructure does not configure DNS centrally, you may need to configure static DNS entries in the /etc/hosts file of each server.

On each Enterprise ColumnStore node, edit the /etc/hosts file to map host names to the IP address of each Enterprise ColumnStore node:

Replace the IP addresses with the addresses in your own environment.

Create an S3 Bucket

With the ColumnStore Object Storage topology, it is important to create the S3 bucket before you start ColumnStore. All Enterprise ColumnStore nodes access data from the same bucket.

If you already have an S3 bucket, confirm that the bucket is empty.

S3 bucket configuration will be performed later in this procedure.

Next Step

Navigation in the procedure "Deploy ColumnStore Object Storage Topology":

This page was step 1 of 9.

Step 3: Start and Configure Enterprise ColumnStore

Overview

This page details step 3 of a 5-step procedure for deploying Single-Node Enterprise ColumnStore with Object storage.

This step starts and configures MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Configure Enterprise ColumnStore

Mandatory system variables and options for Single-Node Enterprise ColumnStore include:

Connector

MariaDB Connector/R2DBC

Example Configuration

Configure the S3 Storage Manager

Configure Enterprise ColumnStore S3 Storage Manager to use S3-compatible storage by editing the /etc/columnstore/storagemanager.cnf configuration file:

The S3-compatible object storage options are configured under [S3]:

The bucket option must be set to the name of the bucket that you created in "Create an S3 Bucket".
The endpoint option must be set to the endpoint for the S3-compatible object storage.
The aws_access_key_id and aws_secret_access_key options must be set to the access key ID and secret access key for the S3-compatible object storage.

The local cache options are configured under [Cache]:

The cache_size option is set to 2 GB by default.
The path option is set to /var/lib/columnstore/storagemanager/cache by default.

Ensure that the specified path has sufficient storage space for the specified cache size.

Start the Enterprise ColumnStore Services

Start and enable the MariaDB Enterprise Server service, so that it starts automatically upon reboot:

Start and enable the MariaDB Enterprise ColumnStore service, so that it starts automatically upon reboot:

Create the Utility User

Enterprise ColumnStore requires a mandatory utility user account to perform cross-engine joins and similar operations.

Create the user account with the statement:

Grant the user account SELECT privileges on all databases with the statement:

Configure Enterprise ColumnStore to use the utility user:

Set the password:

For details about how to encrypt the password, see "".

Passwords should meet your organization's password policies. If your MariaDB Enterprise Server instance has a password validation plugin installed, then the password should also meet the configured requirements.

Configure Linux Security Modules (LSM)

The specific steps to configure the security module depend on the operating system.

Configure SELinux (CentOS, RHEL)

Configure SELinux for Enterprise ColumnStore:

To configure SELinux, you have to install the packages required for audit2allow. On CentOS 7 and RHEL 7, install the following:

On RHEL 8, install the following:

Allow the system to run under load for a while to generate SELinux audit events.
After the system has taken some load, generate an SELinux policy from the audit events using audit2allow:

If no audit events were found, this will print the following:

If audit events were found, the new SELinux policy can be loaded using semodule:

Set SELinux to enforcing mode by setting SELINUX=enforcing in /etc/selinux/config.

For example, the file will usually look like this after the change:

Set SELinux to enforcing mode:

Configure AppArmor (Ubuntu)

For information on how to create a profile, see on ubuntu.com.

Next Step

Navigation in the Single-Node Enterprise ColumnStore topology with Object storage deployment procedure:

This page was step 3 of 5.

ColumnStore Partition Management

Introduction

MariaDB ColumnStore automatically creates logical horizontal partitions across every column. For ordered or semi-ordered data fields such as an order date this will result in a highly effective partitioning scheme based on that column. This allows for increased performance of queries filtering on that column since partition elimination can be performed. It also allows for data lifecycle management as data can be disabled or dropped by partition cheaply. Caution should be used when disabling or dropping partitions as these commands are destructive.

It is important to understand that a Partition in ColumnStore terms is actually 2 extents (16 million rows) and that extents & partitions are created according to the following algorithm in 1.0.x:

Create 4 extents in 4 files
When these are filled up (after 32M rows), create 4 more extents in the 4 files created in step 1.
When these are filled up (after 64M rows), create a new partition.

Managing Partitions by Partition Number

Displaying Partitioning Information

Information about all partitions for a given column can be retrieved using the calShowPartitions stored procedure which takes either two or three mandatory parameters: [database_name], table_name, and column_name. If two parameters are provided the current database is assumed. For example:

Enabling Partitions

The calEnablePartitions stored procedure allows for enabling of one or more partitions. The procedure takes the same set of parameters as calDisablePartitions.

For example:

The result showing the first partition has been enabled:

Disabling Partitions

The calDisablePartitions stored procedure allows for disabling of one or more partitions. A disabled partition still exists on the file system (and can be enabled again at a later time) but will not participate in any query, DML or import activity. The procedure takes either two or three mandatory parameters: [database_name], table_name, and partition_numbers separated by commas. If two parameters are provided the current database is assumed.

For example:

The result showing the first partition has been disabled:

Dropping Partitions

The calDropPartitions stored procedure allows for dropping of one or more partitions. Dropping means that the underlying storage is deleted and the partition is completely removed. A partition can be dropped from either enabled or disabled state. The procedure takes the same set of parameters as calDisablePartitions. Extra caution should be used with this procedure since it is destructive and cannot be reversed.

For example:

The result showing the first partition has been dropped:

Managing Partitions by Column Value

Displaying Partitioning Information

Information about a range of parititions for a given column can be retrieved using the calShowPartitionsByValue stored procedure. This procedure takes either four or five mandatory parameters: [database_name], table_name,`` column_name,`` start_value, and`` end_value. If four parameters are provided, the current database is assumed. Only casual partition column types (, , , , up to 8 bytes and up to 7 bytes) are supported for this function.

The function returns a list of partitions whose minimum and maximum values for the column col_name fall completely within the range of start_value and end_value.

For example:

Enabling Partitions

The calEnablePartitionsbyValue stored procedure allows for enabling of one or more partitions by value. The procedure takes the same set of arguments as calShowPartitionsByValue.

A good practice is to use calShowPartitionsByValue to identify the partitions to be enabled and then the same argument values used to construct the calEnablePartitionsbyValue call.

For example:

The result showing the first partition has been enabled:

Disabling Partitions

The calDisablePartitionsByValue stored procedure allows for disabling of one or more partitions by value. A disabled partition still exists on the file system (and can be enabled again at a later time) but will not participate in any query, DML or import activity. The procedure takes the same set of arguments as calShowPartitionsByValue.

A good practice is to use calShowPartitionsByValue to identify the partitions to be disabled and then the same argument values used to construct the calDisablePartitionsByValue call. For example:

The result showing the first partition has been disabled:

Dropping Partitions

The calDropPartitionsByValue stored procedure allows for dropping of one or more partitions by value. Dropping means that the underlying storage is deleted and the partition is completely removed. A partition can be dropped from either enabled or disabled state. The procedure takes the same set of arguments as calShowPartitionsByValue. A good practice is to use calShowPartitionsByValue to identify the partitions to be enabled and then the same argument values used to construct the calDropPartitionsByValue call. Extra caution should be used with this procedure since it is destructive and cannot be reversed.

For example:

The result showing the first partition has been dropped:

Dropping Data Outside of Partitions

Since the partitioning scheme is system-maintained, the minimum and maximum values are not directly specified, but influenced by the order of data loading. If you want to drop a specific date range, additional deletes are required to achieve this. The following cases may occur:

For semi-ordered data, there may be overlap between minimum and maximum values between partitions.
As in the example above, the partition ranges from 1992-01-01 to 1998-08-02. It may be desirable to drop the remaining 1998 rows.

A bulk-delete statement can be used to delete the remaining rows that do not fall exactly within partition ranges. The partition drops will be fastest; however, the system optimizes bulk-delete statements to delete by block internally. This is still relatively fast.

Step 5: Test MariaDB Enterprise Serverd

Step 5: Test MariaDB Enterprise Server

Overview

This page details step 5 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".

This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Test S3 Connection

MariaDB Enterprise ColumnStore 23.10 includes a testS3Connection command to test the S3 configuration, permissions, and connectivity.

This action is performed on each Enterprise ColumnStore node.

Test the S3 configuration by executing the following:

If the testS3Connection command does not return OK, investigate the S3 configuration.

Test Enterprise Server Service

Use Systemd to test whether the MariaDB Enterprise Server service is running.

This action is performed on each Enterprise ColumnStore node.

Check if the MariaDB Enterprise Server service is running by executing the following:

If the service is not running on any node, start the service by executing the following on that node:

Test Local Client Connections

Use to test the local connection to the Enterprise Server node.

This action is performed on each Enterprise ColumnStore node:

The sudo command is used here to connect to the Enterprise Server node using the root@localhost user account, which authenticates using the unix_socket authentication plugin. Other user accounts can be used by specifying the --user and --password command-line options.

Test ColumnStore Storage Engine Plugin

Query the table to confirm that the ColumnStore storage engine is loaded.

This action is performed on each Enterprise ColumnStore node.

Execute the following query:

The PLUGIN_STATUS column for each ColumnStore-related plugin should contain ACTIVE.

Test CMAPI Service

Use Systemd to test whether the CMAPI service is running.

This action is performed on each Enterprise ColumnStore node.

Check if the CMAPI service is running by executing the following:

If the service is not running on any node, start the service by executing the following on that node:

Test ColumnStore Status

Use CMAPI to request the ColumnStore status. The API key needs to be provided as part of the X-API-key HTML header.

This action is performed with the CMAPI service on the primary server.

Check the ColumnStore status using curl by executing the following:

Test DDL

Use MariaDB Client to test DDL.

On the primary server, use the MariaDB Client to connect to the node:

Create a test database and ColumnStore table:

On each replica server, use the MariaDB Client to connect to the node:

Confirm that the database and table exist:

If the database or table do not exist on any node, then check the replication configuration.

Test DML

Use MariaDB Client to test DML.

On the primary server, use the MariaDB Client to connect to the node:

Insert sample data into the table created in the DDL test:

On each replica server, use the MariaDB Client to connect to the node:

Execute a query to retrieve the data:

If the data is not returned on any node, check the ColumnStore status and the storage configuration.

Next Step

Navigation in the procedure 'Deploy ColumnStore Object Storage Topology".

This page was step 5 of 9.

Multi-Node S3

This guide provides steps for deploying a multi-node S3 ColumnStore, setting up the environment, installing the software, and bulk importing data for online analytical processing (OLAP) workloads.

Overview

This procedure describes the deployment of the Single-Node Enterprise ColumnStore topology with Object storage.

MariaDB Enterprise ColumnStore is a columnar storage engine for MariaDB Enterprise Server and Enterprise ColumnStore is best suited for Online Analytical Processing (OLAP) workloads.

This procedure has 5 steps, which are executed in sequence.

The instructions were tested against ColumnStore 23.10.

This page provides an overview of the topology, requirements, and deployment procedures.

Please read and understand this procedure before executing.

Procedure Steps

Step

Description

Support

Customers can obtain support by .

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server Components

Component

Description

Topology

The Single-Node Enterprise ColumnStore topology provides support for Online Analytical Processing (OLAP) workloads to MariaDB Enterprise Server.

The Enterprise ColumnStore node:

Receives queries from the application
Executes queries
Use for data

High Availability

Single-Node Enterprise ColumnStore does not provide high availability (HA) for Online Analytical Processing (OLAP). If you would like to deploy Enterprise ColumnStore with high availability, see Enterprise ColumnStore with Object storage.

Requirements

These requirements are for the Single-Node Enterprise ColumnStore, when deployed with MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.

Operating System

Debian 11 (x86_64, ARM64)
Debian 12 (x86_64, ARM64)
Red Hat Enterprise Linux 8 (x86_64, ARM64)
Red Hat Enterprise Linux 9 (x86_64, PPC64LE, ARM64)

Minimum Hardware Requirements

MariaDB Enterprise ColumnStore's minimum hardware requirements are not intended for production environments, but the minimum hardware requirements can be appropriate for development and test environments. For production environments, see the instead.

The minimum hardware requirements are:

Component

CPU

Memory

MariaDB Enterprise ColumnStore will refuse to start if the system has less than 3 GB of memory.

If Enterprise ColumnStore is started on a system with less memory, the following error message will be written to the ColumnStore system log called crit.log:

And the following error message will be raised to the client:

Recommended Hardware Requirements

MariaDB Enterprise ColumnStore's recommended hardware requirements are intended for production analytics.

The recommended hardware requirements are:

Component

CPU

Memory

Storage Requirements

Single-node Enterprise ColumnStore with Object Storage requires the following storage type:

Storage Type

Description

S3-Compatible Object Storage Requirements

Single-node Enterprise ColumnStore with Object Storage uses S3-compatible object storage to store data.

Many S3-compatible object storage services exist. MariaDB Corporation cannot make guarantees about all S3-compatible object storage services, because different services provide different functionality.

For the preferred S3-compatible object storage providers that provide cloud and hardware solutions, see the following sections:

The use of non-cloud and non-hardware providers is at your own risk.

If you have any questions about using specific S3-compatible object storage with MariaDB Enterprise ColumnStore, .

Preferred Object Storage Providers: Cloud

Amazon Web Services (AWS) S3
Google Cloud Storage
Azure Storage
Alibaba Cloud Object Storage Service

Preferred Object Storage Providers: Hardware

Cloudian HyperStore
Dell EMC
Seagate Lyve Rack
Quantum ActiveScale

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

MariaDB Enterprise Server packages are configured to read configuration files from different paths, depending on the operating system. Making custom changes to Enterprise Server default configuration files is not recommended because custom changes may be overwritten by other default configuration files that are loaded later.

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

MariaDB Enterprise Server Service Management

The systemctl command is used to start and stop the MariaDB Enterprise Server service.

Operation

Command

Next Step

Navigation in the Single-Node Enterprise ColumnStore topology with Object storage deployment procedure:

ColumnStore System Variables

Variables

columnstore_diskjoin_force_run

Controls whether disk joins are forced to run even if they are not estimated to be the most efficient execution plan. This can be useful for debugging purposes or for situations where the optimizer's estimates are not accurate.
Scope: global, session
Data type:
Default value: OFF
Range: ON, OFF
Introduced in: MariaDB Enterprise Server 10.6

columnstore_diskjoin_max_partition_tree_depth

Sets the maximum depth of the partition tree that can be used for disk joins. A higher value allows for more complex joins, but may also increase the memory usage and execution time.
Scope: global, session
Data type:
Default value: 10

columnstore_max_allowed_in_values

Sets the maximum number of values that can be used in an IN predicate on a Columnstore table. This limit helps to prevent performance issues caused by queries with a large number of IN values.
Scope: global, session
Data type:
Default value: 10000

columnstore_max_pm_join_result_count

Sets the maximum number of rows that can be returned by a parallel merge join on a Columnstore table. This limit helps to prevent memory issues caused by joins that return a large number of rows.
Scope: global, session
Data type:
Default value: 1000000

Command line: Yes
Scope: global, session
Data type:
Default value: 2

Command line: Yes
Scope: global, session
Data type:
Default value: 8

Command line: Yes
Scope: global, session
Data type:
Default value: 100

Command line: Yes
Scope: global, session
Data type:
Default value: 0

Command line: Yes
Scope: global, session
Data type:
Default value: 0

Command line: Yes
Scope: global, session
Data type:
Default value: OFF

Command line: Yes
Scope: global, session
Data type:
Default value: 7

Command line: Yes
Scope: global, session
Data type:
Default value: 17

Command line: Yes
Scope: global, session
Data type:
Default value: 0

infinidb_ordered_only

Command line: Yes
Scope: global, session
Data type:
Default value: OFF

infinidb_string_scan_threshold

Command line: Yes
Scope: global, session
Data type:
Default value: 10

infinidb_stringtable_threshold

Command line: Yes
Scope: global, session
Data type:
Default value: 20

Command line: Yes
Scope: global, session
Data type:
Default value: 0

Command line: Yes
Scope: global, session
Data type:
Default value: OFF

Command line: Yes
Scope: global, session
Data type:
Default value: ON

infinidb_varbin_always_hex

Command line: Yes
Scope: global, session
Data type:
Default value: ON

Command line: Yes
Scope: global, session
Data type:
Default value: 1

Compression Mode

MariaDB ColumnStore has the ability to compress data. This is controlled through a compression mode, which can be set as a default for the instance or set at the session level.

To set the compression mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance:

where n is:

compression is turned off. Any subsequent table create statements run will have compression turned off for that table unless any statement overrides have been performed. Any alter statements run to add a column will have compression turned off for that column unless any statement override has been performed.
compression is turned on. Any subsequent table create statements run will have compression turned on for that table unless any statement overrides have been performed. Any alter statements run to add a column will have compression turned on for that column unless any statement override has been performed. ColumnStore uses snappy compression in this mode.

ColumnStore Decimal-to-Double Math

MariaDB ColumnStore has the ability to change intermediate decimal mathematical results from decimal type to double. The decimal type has approximately 17-18 digits of precision, but a smaller maximum range. Whereas the double type has approximately 15-16 digits of precision, but a much larger maximum range.

In typical mathematical and scientific applications, the ability to avoid overflow in intermediate results with double math is likely more beneficial than the additional two digits of precisions. In banking applications, however, it may be more appropriate to leave in the default decimal setting to ensure accuracy to the least significant digit.

Enable/Disable Decimal-to-Double Math

The infinidb\_double\_for\_decimal\_math variable is used to control the data type for intermediate decimal results. This decimal for double math may be set as a default for the instance, set at the session level, or at the statement level by toggling this variable on and off.

To enable/disable the use of the decimal to double math at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance:

where n is:

off (disabled, default)
on (enabled)

ColumnStore Decimal Scale

ColumnStore has the ability to support varied internal precision on decimal calculations. infinidb_decimal_scale is used internally by the ColumnStore engine to control how many significant digits to the right of the decimal point are carried through in suboperations on calculated columns. If, while running a query, you receive the message ‘aggregate overflow’, try reducing infinidb_decimal_scale and running the query again.

Note that, as you decrease infinidb_decimal_scale, you may see reduced accuracy in the least significant digit(s) of a returned calculated column. infinidb_use_decimal_scale is used internally by the ColumnStore engine to turn the use of this internal precision on and off. These two system variables can be set as a default for the instance or at session level.

Enable/Disable Decimal Scale

To enable/disable the use of the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance:

where n is off (disabled) or on (enabled).

Set Decimal Scale Level

To set the decimal scale at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is the amount of precision desired for calculations.

Disk-Based Joins

Introduction

Joins are performed in memory. When a join operation exceeds the memory allocated for query joins, the query is aborted with an error code IDB-2001.

Disk-based joins enable such queries to use disk for intermediate join data in case when the memory needed for join exceeds the memory limit. Although slower in performance as compared to a fully in-memory join, and bound by the temporary space on disk, it does allow such queries to complete.

Disk-based joins does not include aggregation and DML joins.

The following variables in the HashJoin element in the Columnstore.xml configuration file relate to disk-based joins. Columnstore.xml resides in /usr/local/mariadb/columnstore/etc/.

AllowDiskBasedJoin – Option to use disk-based joins. Valid values are Y (enabled) or N (disabled). Default is disabled.
TempFileCompression – Option to use compression for disk join files. Valid values are Y (use compressed files) or N (use non-compressed files).
TempFilePath – The directory path used for the disk joins. By default, this path is the tmp directory for your installation (i.e., /usr/local/mariadb/columnstore/tmp). Files (named infinidb-join-data*) in this directory will be created and cleaned on an as needed basis. The entire directory is removed and recreated by ExeMgr at startup.)

When using disk-based joins, it is strongly recommended that the TempFilePath reside on its own partition as the partition may fill up as queries are executed.

Per user join memory limit

In addition to the system wide flags, at SQL global and session level, the following system variables exists for managing per user memory limit for joins.

infinidb_um_mem_limit - A value for memory limit in MB per user. When this limit is exceeded by a join, it will switch to a disk-based join. By default, the limit is not set (value of 0).

For modification at the global level: In my.cnf file (typically /usr/local/mariadb/columnstore/mysql):

where value is the value in MB for in memory limitation per user.

For modification at the session level, before issuing your join query from the SQL client, set the session variable as follows.

Batch Insert Mode for INSERT Statements

Introduction

MariaDB ColumnStore has the ability to utilize the cpimport fast data import tool for non-transactional and SQL statements. Using this method results in a significant increase in performance in loading data through these two SQL statements. This optimization is independent of the storage engine used for the tables in the select statement.

Enable/Disable Using cpimport for Batch Insert

The infinidb_use_import_for_batchinsert variable is used to control if cpimport is used for these statements. This variable may be set as a default for the instance, set at the session level, or at the statement level by toggling this variable on and off.

To enable/disable the use of the use cpimport for batch insert at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is:

0 (disabled)
1 (enabled)

Changing Default Delimiter for INSERT SELECT

The infinidb_import_for_batchinsert_delimiter variable is used internally by MariaDB ColumnStore on a non-transactional INSERT INTO SELECT FROM statement as the default delimiter passed to the cpimport tool. With a default value ascii 7, there should be no need to change this value unless your data contains ascii 7 values.

To change this variable value at the at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where ascii_value is an ASCII value representation of the delimiter desired.

Note that this setting may cause issues with multi byte character set data. It is recommended to utilize UTF8 files directly with cpimport.

Version Buffer File Management

If the following error is received, most likely with a transaction LOAD DATA INFILE or INSERT INTO SELECT, it is recommended to break up the load into multiple smaller chunks, increase the VersionBufferFileSize setting, consider a nontransactional LOAD DATA INFILE, or use cpimport.

The VersionBufferFileSize setting is updated in the ColumnStore.xml typically located under /usr/local/mariadb/columnstore/etc. This dictates the size of the version buffer file on disk which provides DML transactional consistency. The default value is '1GB' which reserves up to a 1 Gigabyte file size. Modify this on the primary node and restart the system if you require a larger value.

Local PrimProc Query Mode

MariaDB ColumnStore has the ability to query data from just a single node instead of the whole cluster. In order to accomplish this, the infinidb_local_query variable in the my.cnf configuration file is used and maybe set as a default at system wide or set at the session level.

Enable Local PrimProc Query During Installation

Local PrimProc query can be enabled system wide during the install process when running the install script postConfigure. Answer 'y' to this prompt during the install process:

Enable Local PrimProc Query System-Wide

To enable the use of the local PrimProc query at the instance level, specify infinidb_local_query =1 (enabled) in the my.cnf configuration file at /usr/local/mariadb/columnstore/mysql. The default is 0 (disabled).

Enable/Disable Local PrimProc Query at the Session Level

To enable/disable the use of the local PrimProc query at the session level, the following statement is used. Once the session has ended, any subsequent session will return to the default for the instance:

where n is:

0 (disabled)
1 (enabled)

At the session level, this variable applies only to executing a query on an individual . The PrimProc must be set up with the local query option during installation.

Local PrimProc Query Examples

Example 1 - SELECT from a single table on local PrimProc to import back on local PrimProc:

With the infinidb_local_query variable set to 1 (default with local PrimProc Query):

Example 2 - SELECT involving a join between a fact table on the PrimProc node and dimension table across all the nodes to import back on local PrimProc:

With the infinidb_local_query variable set to 0 (default with local PrimProc Query):

Create a script (i.e., extract_query_script.sql in our example) similar to the following:

The infinidb_local_query is set to 0 to allow query across all PrimProc nodes.

The query is structured so PrimProc gets the fact table data locally from the PrimProc node (as indicated by the use of the function), while the dimension table data is extracted from all the PrimProc nodes.

Then you can execute the script to pipe it directly into cpimport:

Operating Mode

ColumnStore has the ability to support full MariaDB query syntax through an operating mode. This operating mode may be set as a default for the instance or set at the session level. To set the operating mode at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.

where n is:

a generic, highly compatible row-by-row processing mode. Some WHERE clause components can be processed by ColumnStore, but joins are processed entirely by MySQL using a nested loop join mechanism.
(the default) query syntax is evaluated by ColumnStore for compatibility with distributed execution and incompatible queries are rejected. Queries executed in this mode take advantage of distributed execution and typically result in higher performance.
auto-switch mode: ColumnStore will attempt to process the query internally, if it cannot, it will automatically switch the query to run in row-by-row mode.

Step 4: Start and Configure MariaDB Enterprise Server

Overview

This page details step 4 of the 9-step procedure "Deploy ColumnStore Shared Local Storage Topology".

This step starts and configures MariaDB Enterprise Server, and MariaDB Enterprise ColumnStore.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Stop the Enterprise ColumnStore Services

The installation process might have started some of the ColumnStore services. The services should be stopped prior to making configuration changes.

On each Enterprise ColumnStore node, stop the MariaDB Enterprise Server service:

On each Enterprise ColumnStore node, stop the MariaDB Enterprise ColumnStore service:

On each Enterprise ColumnStore node, stop the CMAPI service:

Configure Enterprise ColumnStore

On each Enterprise ColumnStore node, configure Enterprise Server.

Connector

MariaDB Connector/R2DBC

Mandatory system variables and options for ColumnStore Object Storage include:

Example Configuration

Start the Enterprise ColumnStore Services

On each Enterprise ColumnStore node, start and enable the MariaDB Enterprise Server service, so that it starts automatically upon reboot:

On each Enterprise ColumnStore node, stop the MariaDB Enterprise ColumnStore service:

After the CMAPI service is installed in the next step, CMAPI will start the Enterprise ColumnStore service as needed on each node. CMAPI disables the Enterprise ColumnStore service to prevent systemd from automatically starting Enterprise ColumnStore upon reboot.

On each Enterprise ColumnStore node, start and enable the CMAPI service, so that it starts automatically upon reboot:

For additional information, see "Start and Stop Services".

Create User Accounts

The ColumnStore Object Storage topology requires several user accounts. Each user account should be created on the primary server, so that it is replicated to the replica servers.

Create the Utility User

Enterprise ColumnStore requires a mandatory utility user account to perform cross-engine joins and similar operations.

On the primary server, create the user account with the CREATE USER statement:

On the primary server, grant the user account SELECT privileges on all databases with the GRANT statement:

On each Enterprise ColumnStore node, configure the ColumnStore utility user:

On each Enterprise ColumnStore node, set the password:

For details about how to encrypt the password, see "".

Create the Replication User

ColumnStore Object Storage uses MariaDB Replication to replicate writes between the primary and replica servers. As MaxScale can promote a replica server to become a new primary in the event of node failure, all nodes must have a replication user.

The action is performed on the primary server.

Create the replication user and grant it the required privileges:

Use the CREATE USER statement to create replication user.

Replace the referenced IP address with the relevant address for your environment.

Ensure that the user account can connect to the primary server from each replica.

Grant the user account the required privileges with the GRANT statement.

Create MaxScale User

ColumnStore Object Storage 23.10 uses MariaDB MaxScale 22.08 to load balance between the nodes.

This action is performed on the primary server.

Use the statement to create the MaxScale user:

Replace the referenced IP address with the relevant address for your environment.

Ensure that the user account can connect from the IP address of the MaxScale instance.

Use the statement to grant the privileges required by the router:

Use the statement to grant privileges required by the MariaDB Monitor.

Configure MariaDB Replication

On each replica server, configure MariaDB Replication:

Use the CHANGE MASTER TO statement to configure the connection to the primary server:

Start replication using the START REPLICA statement:

Confirm that replication is working using the SHOW REPLICA STATUS statement:

Ensure that the replica server cannot accept local writes by setting the read_only system variable to ON using the SET GLOBAL statement:

Initiate the Primary Server with CMAPI

Initiate the primary server using CMAPI.

Create an API key for the cluster. This API key should be stored securely and kept confidential, because it can be used to add cluster nodes to the multi-node Enterprise ColumnStore deployment.

For example, to create a random 256-bit API key using openssl rand:

This document will use the following API key in further examples, but users should create their own:

Use CMAPI to add the primary server to the cluster and set the API key. The new API key needs to be provided as part of the X-API-key HTML header.

For example, if the primary server's host name is mcs1 and its IP address is 192.0.2.1, use the following node command:

Use CMAPI to check the status of the cluster node:

Add Replica Servers with CMAPI

Add the replica servers with CMAPI:

For each replica server, use to add the replica server to the cluster. The previously set API key needs to be provided as part of the X-API-key HTML header.

For example, if the primary server's host name is mcs1 and the replica server's IP address is 192.0.2.2, use the following node command:

After all replica servers have been added, use CMAPI to confirm that all cluster nodes have been successfully added:

Configure Linux Security Modules (LSM)

The specific steps to configure the security module depend on the operating system.

Configure SELinux (CentOS, RHEL)

Configure SELinux for Enterprise ColumnStore:

To configure SELinux, you have to install the packages required for audit2allow. On CentOS 7 and RHEL 7, install the following:

On RHEL 8, install the following:

Allow the system to run under load for a while to generate SELinux audit events.
After the system has taken some load, generate an SELinux policy from the audit events using audit2allow:

If no audit events were found, this will print the following:

If audit events were found, the new SELinux policy can be loaded using semodule:

Set SELinux to enforcing mode:

Set SELinux to enforcing mode by setting SELINUX=enforcing in /etc/selinux/config.

For example, the file will usually look like this after the change:

Confirm that SELinux is in enforcing mode:

Configure AppArmor (Ubuntu)

For information on how to create a profile, see on Ubuntu.com.

Configure Firewalls

The specific steps to configure the firewall service depend on the platform.

Configure firewalld (CentOS, RHEL)

Configure firewalld for Enterprise Cluster on CentOS and RHEL:

Check if the firewalld service is running:

If the firewalld service was stopped to perform the installation, start it now:

For example, if your cluster nodes are in the 192.0.2.0/24 subnet:

Open up the relevant ports using firewall-cmd:

Reload the runtime configuration:

Configure UFW (Ubuntu)

Configure UFW for Enterprise ColumnStore on Ubuntu:

Check if the UFW service is running:

If the UFW service was stopped to perform the installation, start it now:

Open up the relevant ports using ufw.

For example, if your cluster nodes are in the 192.0.2.0/24 subnet in the range 192.0.2.1 - 192.0.2.3:

Reload the runtime configuration:

Next Step

Navigation in the procedure "Deploy ColumnStore Shared Local Storage Topology".

This page was step 4 of 9.

Multi-Node Localstorage

This guide provides steps for deploying a multi-node ColumnStore, setting up the environment, installing the software, and bulk importing data for online analytical processing (OLAP) workloads.

Overview

Software Version

Diagram

Features

Enterprise Server 10.5
Enterprise Server 10.6
Enterprise Server 11.4

This procedure describes the deployment of the ColumnStore Shared Local Storage topology with MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5, and MariaDB MaxScale 2.5.

MariaDB Enterprise ColumnStore 5 is a columnar storage engine for MariaDB Enterprise Server 10.5. Enterprise ColumnStore is suitable for Online Analytical Processing (OLAP) workloads.

This procedure has 9 steps, which are executed in sequence.

This procedure represents basic product capability and deploys 3 Enterprise ColumnStore nodes and 1 MaxScale node.

This page provides an overview of the topology, requirements, and deployment procedures.

Please read and understand this procedure before executing.

Procedure Steps

Step

Description

Support

Customers can obtain support by submitting a support case.

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server Components

Component

Description

MariaDB MaxScale Components

Component

Description

Topology

The MariaDB Enterprise ColumnStore topology with Object Storage delivers production analytics with high availability, fault tolerance, and limitless data storage by leveraging S3-compatible storage.

The topology consists of:

One or more MaxScale nodes
An odd number of ColumnStore nodes (minimum of 3) running ES, Enterprise ColumnStore, and CMAPI

The MaxScale nodes:

Monitor the health and availability of each ColumnStore node using the MariaDB Monitor (mariadbmon)
Accept client and application connections
Route queries to ColumnStore nodes using the Read/Write Split Router (readwritesplit)

The ColumnStore nodes:

Receive queries from MaxScale
Execute queries
- Use for the

Requirements

These requirements are for the ColumnStore Object Storage topology when deployed with MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5, and MariaDB MaxScale 2.5.

Node Count
Operating System
Minimum Hardware Requirements
Recommended Hardware Requirements

Node Count

MaxScale nodes, 1 or more are required.
Enterprise ColumnStore nodes, 3 or more are required for high availability. You should always have an odd number of nodes in a multi-node ColumnStore deployment to avoid split brain scenarios.

Operating System

In alignment to the , the ColumnStore Object Storage topology with MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5, and MariaDB MaxScale 2.5 is provided for:

CentOS Linux 7 (x86_64)
Debian 10 (x86_64)
Red Hat Enterprise Linux 7 (x86_64)
Red Hat Enterprise Linux 8 (x86_64)

Minimum Hardware Requirements

The minimum hardware requirements are:

Component

CPU

Memory

MariaDB Enterprise ColumnStore will refuse to start if the system has less than 3 GB of memory.

If Enterprise ColumnStore is started on a system with less memory, the following error message will be written to the ColumnStore system log called crit.log:

And the following error message will be raised to the client:

Recommended Hardware Requirements

MariaDB Enterprise ColumnStore's recommended hardware requirements are intended for production analytics.

The recommended hardware requirements are:

Component

CPU

Memory

Storage Requirements

The ColumnStore Object Storage topology requires the following storage types:

Storage Type

Description

Shared Local Storage Directories

The ColumnStore Object Storage topology uses shared local storage for the Storage Manager directory to store metadata.

The Storage Manager directory is located at the following path by default:

/var/lib/columnstore/storagemanager

Shared Local Storage Options

The most common shared local storage options for the ColumnStore Object Storage topology are:

Shared Local Storage

Common Usage

Description

Enterprise ColumnStore Management with CMAPI

Enterprise ColumnStore's CMAPI (Cluster Management API) is a REST API that can be used to manage a multi-node Enterprise ColumnStore cluster.

Many tools are capable of interacting with REST APIs. For example, the curl utility could be used to make REST API calls from the command-line.

Many programming languages also have libraries for interacting with REST APIs.

The examples below show how to use the CMAPI with curl.

URL Endpoint Format for REST API

For example:

Required Request Headers

'x-api-key': '93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd'
'Content-Type': 'application/json'

x-api-key can be set to any value of your choice during the first call to the server. Subsequent connections will require this same key.

Get Status

Start Cluster

Stop Cluster

Add Node

Remove Node

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

MariaDB Enterprise Server Service Management

The systemctl command is used to start and stop the MariaDB Enterprise Server service.

Operation

Command

For additional information, see "".

MariaDB Enterprise Server Logs

MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.

Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the datadir system variable.

Log

System Variable/Option

Default Filename

Enterprise ColumnStore Service Management

The systemctl command is used to start and stop the ColumnStore service.

Operation

Command

In the ColumnStore Object Storage topology, the mariadb-columnstore service should not be enabled. The CMAPI service restarts Enterprise ColumnStore as needed, so it does not need to start automatically upon reboot.

Enterprise ColumnStore CMAPI Service Management

The systemctl command is used to start and stop the CMAPI service.

Operation

Command

For additional information on endpoints, see "CMAPI".

MaxScale Configuration Management

MaxScale can be configured using several methods. These methods make use of MaxScale's .

Method

Benefits

The procedure on these pages configures MaxScale using MaxCtrl.

MaxScale Service Management

The systemctl command is used to start and stop the MaxScale service.>

Operation

Command

For additional information, see "Start and Stop Services".

Next Step

Navigation in the procedure Shared Local Storage topology

Step 4: Start and Configure MariaDB Enterprise Server

Overview

This page details step 4 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".

This step starts and configures MariaDB Enterprise Server, and MariaDB Enterprise ColumnStore.

The instructions were tested against ColumnStore 23.10.

Interactive commands are detailed. Alternatively, the described operations can be performed using automation.

Stop the Enterprise ColumnStore Services

The installation process might have started some of the ColumnStore services. The services should be stopped prior to making configuration changes.

On each Enterprise ColumnStore node, stop the MariaDB Enterprise Server service:

On each Enterprise ColumnStore node, stop the MariaDB Enterprise ColumnStore service:

On each Enterprise ColumnStore node, stop the CMAPI service:

Configure Enterprise ColumnStore

On each Enterprise ColumnStore node, configure Enterprise Server.

Connector

MariaDB Connector/R2DBC

Mandatory system variables and options for ColumnStore Object Storage include:

Example Configuration

Configure the S3 Storage Manager

On each Enterprise ColumnStore node, configure S3 Storage Manager to use S3-compatible storage by editing the /etc/columnstore/storagemanager.cnf configuration file:

The S3-compatible object storage options are configured under [S3]:

The bucket option must be set to the name of the bucket that you created in "Create an S3 Bucket".
The endpoint option must be set to the endpoint for the S3-compatible object storage.
The aws_access_key_id and aws_secret_access_key options must be set to the access key ID and secret access key for the S3-compatible object storage.
To use a specific IAM role, you must uncomment and set

The local cache options are configured under [Cache]:

The cache_size option is set to 2 GB by default.
The path option is set to /var/lib/columnstore/storagemanager/cache by default.

Ensure that the specified path has sufficient storage space for the specified cache size.

Start the Enterprise ColumnStore Services

On each Enterprise ColumnStore node, start and enable the MariaDB Enterprise Server service, so that it starts automatically upon reboot:

On each Enterprise ColumnStore node, stop the MariaDB Enterprise ColumnStore service:

On each Enterprise ColumnStore node, start and enable the CMAPI service, so that it starts automatically upon reboot:

For additional information, see "".

Create User Accounts

The ColumnStore Object Storage topology requires several user accounts. Each user account should be created on the primary server, so that it is replicated to the replica servers.

Create the Utility User

Enterprise ColumnStore requires a mandatory utility user account to perform cross-engine joins and similar operations.

On the primary server, create the user account with the CREATE USER statement:

On the primary server, grant the user account SELECT privileges on all databases with the GRANT statement:

On each Enterprise ColumnStore node, configure the ColumnStore utility user:

On each Enterprise ColumnStore node, set the password:

For details about how to encrypt the password, see "".

Create the Replication User

The action is performed on the primary server.

Create the replication user and grant it the required privileges:

Use the CREATE USER statement to create replication user.

Replace the referenced IP address with the relevant address for your environment.

Ensure that the user account can connect to the primary server from each replica.

Grant the user account the required privileges with the GRANT statement.

Create MaxScale User

ColumnStore Object Storage 23.10 uses MariaDB MaxScale 22.08 to load balance between the nodes.

This action is performed on the primary server.

Use the statement to create the MaxScale user:

Replace the referenced IP address with the relevant address for your environment.

Ensure that the user account can connect from the IP address of the MaxScale instance.

Use the statement to grant the privileges required by the router:

Use the statement to grant privileges required by the MariaDB Monitor.

Configure MariaDB Replication

On each replica server, configure MariaDB Replication:

Use the CHANGE MASTER TO statement to configure the connection to the primary server:

Start replication using the START REPLICA statement:

Confirm that replication is working using the SHOW REPLICA STATUS statement:

Ensure that the replica server cannot accept local writes by setting the read_only system variable to ON using the SET GLOBAL statement:

Initiate the Primary Server with CMAPI

Initiate the primary server using CMAPI.

Create an API key for the cluster. This API key should be stored securely and kept confidential, because it can be used to add cluster nodes to the multi-node Enterprise ColumnStore deployment.

For example, to create a random 256-bit API key using openssl rand:

This document will use the following API key in further examples, but users should create their own:

Use CMAPI to add the primary server to the cluster and set the API key. The new API key needs to be provided as part of the X-API-key HTML header.

For example, if the primary server's host name is mcs1 and its IP address is 192.0.2.1, use the following node command:

Use CMAPI to check the status of the cluster node:

Add Replica Servers with CMAPI

Add the replica servers with CMAPI:

For each replica server, use to add the replica server to the cluster. The previously set API key needs to be provided as part of the X-API-key HTML header.

For example, if the primary server's host name is mcs1 and the replica server's IP address is 192.0.2.2, use the following node command:

After all replica servers have been added, use CMAPI to confirm that all cluster nodes have been successfully added:

Configure Linux Security Modules (LSM)

The specific steps to configure the security module depend on the operating system.

Configure SELinux (CentOS, RHEL)

Configure SELinux for Enterprise ColumnStore:

To configure SELinux, you have to install the packages required for audit2allow. On CentOS 7 and RHEL 7, install the following:

On RHEL 8, install the following:

Allow the system to run under load for a while to generate SELinux audit events.
After the system has taken some load, generate an SELinux policy from the audit events using audit2allow:

If no audit events were found, this will print the following:

If audit events were found, the new SELinux policy can be loaded using semodule:

Set SELinux to enforcing mode:

Set SELinux to enforcing mode by setting SELINUX=enforcing in /etc/selinux/config.

For example, the file will usually look like this after the change:

Confirm that SELinux is in enforcing mode:

Configure AppArmor (Ubuntu)

For information on how to create a profile, see on Ubuntu.com.

Configure Firewalls

The specific steps to configure the firewall service depend on the platform.

Configure firewalld (CentOS, RHEL)

Configure firewalld for Enterprise Cluster on CentOS and RHEL:

Check if the firewalld service is running:

If the firewalld service was stopped to perform the installation, start it now:

For example, if your cluster nodes are in the 192.0.2.0/24 subnet:

Open up the relevant ports using firewall-cmd:

Reload the runtime configuration:

Configure UFW (Ubuntu)

Configure UFW for Enterprise ColumnStore on Ubuntu:

Check if the UFW service is running:

If the UFW service was stopped to perform the installation, start it now:

Open up the relevant ports using ufw.

For example, if your cluster nodes are in the 192.0.2.0/24 subnet in the range 192.0.2.1 - 192.0.2.3:

Reload the runtime configuration:

Next Step

Navigation in the procedure "Deploy ColumnStore Object Storage Topology":

This page was step 4 of 9.

MCS backup and restore commands

This page documents how to create and restore MariaDB Enterprise ColumnStore backups using the mcs CLI.

The mcs backup and mcs restore commands support the same workflows as the mcs_backup_manager.sh script, including:

Full and incremental backups
Local/shared storage and S3 storage topologies
Optional compression and parallelism
Separate DBRM (metadata) backup/restore workflows

The examples in this page assume the mcs command is available on the host and you run the backup/restore operations as root.

Before You Start

Identify Your Storage Topology

On a ColumnStore node, determine which StorageManager service is configured:

Example output:

service = LocalStorage
service = S3

Use service = LocalStorage when ColumnStore data lives on local/shared storage, and service = S3 when ColumnStore data is stored in object storage.

Estimate Backup Size

LocalStorage:

S3:

Backups

LocalStorage Topology Backups

Instructions

Run mcs backup as root on each node, starting with the primary node.
Use the same backup location on each node.

List Your Backups

Example output:

Quick Examples

Full backup:

Parallel backup:

Compressed backup:

Incremental backup (auto-select most recent full backup):

Save the backup to a remote host (SCP):

Online Backup Example

When you run a backup, by default the tooling performs polling checks and attempts to obtain a consistent point-in-time backup by:

checking for active writes
checking for running cpimport jobs
issuing write locks
saving BRM prior to copying

You can skip these safety mechanisms with:

--skip-polls
--skip-locks
--skip-save-brm

Skipping polls/locks/BRM saving can be useful for certain workflows, but it increases the risk of capturing a partially-written state that complicates restore.

Incremental Backup Example

Before you can run an incremental backup, you need a full backup taken.

Then taking an incremental backup you need to define the full backup name to increment via flag --incremental xxxxx.

Incremental backups add ColumnStore deltas to an existing full backup. You can either:

specify the full backup folder name explicitly, or
use auto_most_recent will select the most recent directory defined in --backup-location to apply the incremental backup to the most recent full backup

Apply to the most recent full backup:

Apply to a specific full backup folder:

Cron Backup Example

Create a cron job (run as root) that takes periodic backups and appends logs:

Every Night Full Backup retaining the last 14 days:

Full backup once a week (Saturday night) w/ incremental backups all the other nights (keep 21 days)

LocalStorage Backup Flags

The most commonly used options are:

Flag / Option

Description

Notes

S3 Topology Backups

Instructions

Ensure the node has access to your S3 endpoint and credentials.
Run mcs backup with --storage S3 and a backup bucket (--backup-bucket).
Run it as root on each node, starting with the primary node.

If you're using an on-premise S3-compatible solution, you may need --endpoint-url (and sometimes --no-verify-ssl).

Quick Examples

Full backup:

Compressed backup (and skip copying bucket data if you only want local artifacts):

Incremental backup:

On-premise S3 endpoint: Key Flags for on premise buckets are the following:

-url - the local/ip address of the S3 provider. For example, minio defaults to port 9000, 127.0.0.1 would be used if minio is installed on the same machine running columnstore
--no-verify-ssl - used when ssl certs are not used/defined for the S3 provider/endpoint

Cron Backup Example

As with LocalStorage, you can schedule mcs backup in cron. Consider including --name-backup to avoid collisions.

S3 Backup Flags

The most commonly used S3-specific options are:

Flag / Option

Description

Notes

Restore

LocalStorage Topology Restore

Instructions

If Backup made only on Primary node on Clusters that do NOT save the backup to an NFS share, copy the primary nodes backup mysql & configs directory to all nodes.

List backups to find the folder name you want.
Restore on each node, starting with the primary node.

When running a columnstore backup, a restore.job file is created with a command compatible to run on each node to restore the backup.

List Your Backups to Restore

Quick Examples

Standard restore:

Compressed backup restore:

LocalStorage Restore Flags

Common options:

Flag / Option

Description

Notes

S3 Topology Restore

Instructions

Use the same backup bucket that contains the backup.
Restore on each node, starting with the primary node.

When running a columnstore backup, a restoreS3.job file is created with a command compatible to run on each node to restore the backup.

Quick Examples

Standard Restore:

On-premise S3 Endpoint:

Restoring to a New Bucket:

Key Flags for restoring to a new bucket

-nb - the name of the new bucket to copy the backup into and configure columnstore to use post restore
-nr - the name of the region of the new bucket to configure columnstore to use post restore
-nk - the key of the new bucket to configure columnstore to use post restore

S3 Restore Flags

Common options:

Flag / Option

Description

Notes

DBRM Backups

Both S3 and LocalStorage use the same commands for dbrm backups

DBRM backups are intended for backing up internal ColumnStore metadata only.

Instructions

Run mcs dbrm_backup as root with the appropriate flags as you need ONLY on the primary node

List Your dbrm Backups

Quick Examples

Standard `dbrm_backup`:

dbrm_backup before upgrade:

dbrm_backup Flags

Common options:

Flag / Option

Description

Notes

DBRM Restore

Instructions

Both S3 and LocalStorage use the same commands for dbrm restore.

DBRM backups are intended for backing up internal ColumnStore metadata only.

List available DBRM backups.
Restore from the selected folder.

List Your dbrm Restore Options

Quick Examples

Standard `dbrm_restore`:

`dbrm_restore` Flags

Common options:

Flag / Option

Description

Notes

┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ Service             │ query_router_service                                        │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router              │ readwritesplit                                              │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ State               │ Started                                                     │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Started At          │ Sat Aug 28 21:41:16 2021                                    │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Current Connections │ 0                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Total Connections   │ 0                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Max Connections     │ 0                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Cluster             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Servers             │ mcs1                                                        │
│                     │ mcs2                                                        │
│                     │ mcs3                                                        │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Services            │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Filters             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Parameters          │ {                                                           │
│                     │     "auth_all_servers": false,                              │
│                     │     "causal_reads": "false",                                │
│                     │     "causal_reads_timeout": "10000ms",                      │
│                     │     "connection_keepalive": "300000ms",                     │
│                     │     "connection_timeout": "0ms",                            │
│                     │     "delayed_retry": false,                                 │
│                     │     "delayed_retry_timeout": "10000ms",                     │
│                     │     "disable_sescmd_history": false,                        │
│                     │     "enable_root_user": false,                              │
│                     │     "idle_session_pool_time": "-1000ms",                    │
│                     │     "lazy_connect": false,                                  │
│                     │     "localhost_match_wildcard_host": true,                  │
│                     │     "log_auth_warnings": true,                              │
│                     │     "master_accept_reads": false,                           │
│                     │     "master_failure_mode": "fail_instantly",                │
│                     │     "master_reconnection": false,                           │
│                     │     "max_connections": 0,                                   │
│                     │     "max_sescmd_history": 50,                               │
│                     │     "max_slave_connections": 255,                           │
│                     │     "max_slave_replication_lag": "0ms",                     │
│                     │     "net_write_timeout": "0ms",                             │
│                     │     "optimistic_trx": false,                                │
│                     │     "password": "*****",                                    │
│                     │     "prune_sescmd_history": true,                           │
│                     │     "rank": "primary",                                      │
│                     │     "retain_last_statements": -1,                           │
│                     │     "retry_failed_reads": true,                             │
│                     │     "reuse_prepared_statements": false,                     │
│                     │     "router": "readwritesplit",                             │
│                     │     "session_trace": false,                                 │
│                     │     "session_track_trx_state": false,                       │
│                     │     "slave_connections": 255,                               │
│                     │     "slave_selection_criteria": "LEAST_CURRENT_OPERATIONS", │
│                     │     "strict_multi_stmt": false,                             │
│                     │     "strict_sp_calls": false,                               │
│                     │     "strip_db_esc": true,                                   │
│                     │     "transaction_replay": false,                            │
│                     │     "transaction_replay_attempts": 5,                       │
│                     │     "transaction_replay_max_size": 1073741824,              │
│                     │     "transaction_replay_retry_on_deadlock": false,          │
│                     │     "type": "service",                                      │
│                     │     "use_sql_variables_in": "all",                          │
│                     │     "user": "mxs",                                          │
│                     │     "version_string": null                                  │
│                     │ }                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router Diagnostics  │ {                                                           │
│                     │     "avg_sescmd_history_length": 0,                         │
│                     │     "max_sescmd_history_length": 0,                         │
│                     │     "queries": 0,                                           │
│                     │     "replayed_transactions": 0,                             │
│                     │     "ro_transactions": 0,                                   │
│                     │     "route_all": 0,                                         │
│                     │     "route_master": 0,                                      │
│                     │     "route_slave": 0,                                       │
│                     │     "rw_transactions": 0,                                   │
│                     │     "server_query_statistics": []                           │
│                     │ }                                                           │
└─────────────────────┴─────────────────────────────────────────────────────────────┘

┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ Service             │ query_router_service                                        │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router              │ readwritesplit                                              │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ State               │ Started                                                     │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Started At          │ Sat Aug 28 21:41:16 2021                                    │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Current Connections │ 0                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Total Connections   │ 0                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Max Connections     │ 0                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Cluster             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Servers             │ mcs1                                                        │
│                     │ mcs2                                                        │
│                     │ mcs3                                                        │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Services            │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Filters             │                                                             │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Parameters          │ {                                                           │
│                     │     "auth_all_servers": false,                              │
│                     │     "causal_reads": "false",                                │
│                     │     "causal_reads_timeout": "10000ms",                      │
│                     │     "connection_keepalive": "300000ms",                     │
│                     │     "connection_timeout": "0ms",                            │
│                     │     "delayed_retry": false,                                 │
│                     │     "delayed_retry_timeout": "10000ms",                     │
│                     │     "disable_sescmd_history": false,                        │
│                     │     "enable_root_user": false,                              │
│                     │     "idle_session_pool_time": "-1000ms",                    │
│                     │     "lazy_connect": false,                                  │
│                     │     "localhost_match_wildcard_host": true,                  │
│                     │     "log_auth_warnings": true,                              │
│                     │     "master_accept_reads": false,                           │
│                     │     "master_failure_mode": "fail_instantly",                │
│                     │     "master_reconnection": false,                           │
│                     │     "max_connections": 0,                                   │
│                     │     "max_sescmd_history": 50,                               │
│                     │     "max_slave_connections": 255,                           │
│                     │     "max_slave_replication_lag": "0ms",                     │
│                     │     "net_write_timeout": "0ms",                             │
│                     │     "optimistic_trx": false,                                │
│                     │     "password": "*****",                                    │
│                     │     "prune_sescmd_history": true,                           │
│                     │     "rank": "primary",                                      │
│                     │     "retain_last_statements": -1,                           │
│                     │     "retry_failed_reads": true,                             │
│                     │     "reuse_prepared_statements": false,                     │
│                     │     "router": "readwritesplit",                             │
│                     │     "session_trace": false,                                 │
│                     │     "session_track_trx_state": false,                       │
│                     │     "slave_connections": 255,                               │
│                     │     "slave_selection_criteria": "LEAST_CURRENT_OPERATIONS", │
│                     │     "strict_multi_stmt": false,                             │
│                     │     "strict_sp_calls": false,                               │
│                     │     "strip_db_esc": true,                                   │
│                     │     "transaction_replay": false,                            │
│                     │     "transaction_replay_attempts": 5,                       │
│                     │     "transaction_replay_max_size": 1073741824,              │
│                     │     "transaction_replay_retry_on_deadlock": false,          │
│                     │     "type": "service",                                      │
│                     │     "use_sql_variables_in": "all",                          │
│                     │     "user": "mxs",                                          │
│                     │     "version_string": null                                  │
│                     │ }                                                           │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ Router Diagnostics  │ {                                                           │
│                     │     "avg_sescmd_history_length": 0,                         │
│                     │     "max_sescmd_history_length": 0,                         │
│                     │     "queries": 0,                                           │
│                     │     "replayed_transactions": 0,                             │
│                     │     "ro_transactions": 0,                                   │
│                     │     "route_all": 0,                                         │
│                     │     "route_master": 0,                                      │
│                     │     "route_slave": 0,                                       │
│                     │     "rw_transactions": 0,                                   │
│                     │     "server_query_statistics": []                           │
│                     │ }                                                           │
└─────────────────────┴─────────────────────────────────────────────────────────────┘

Single-Node S3

This guide provides steps for deploying a single-node S3 ColumnStore, setting up the environment, installing the software, and bulk importing data for online analytical processing (OLAP) workloads.

Overview

Enterprise Server 10.5
Enterprise Server 10.6
Enterprise Server 11.4

Columnar storage engine with S3-compatible object storage

Highly available
Automatic failover via MaxScale and CMAPI
Scales read via MaxScale
Bulk data import

This procedure describes the deployment of the ColumnStore Object Storage topology with MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5, and MariaDB MaxScale 2.5.

MariaDB Enterprise ColumnStore 5 is a columnar storage engine for MariaDB Enterprise Server 10.5. Enterprise ColumnStore is suitable for Online Analytical Processing (OLAP) workloads.

This procedure has 9 steps, which are executed in sequence.

This procedure represents basic product capability and deploys 3 Enterprise ColumnStore nodes and 1 MaxScale node.

This page provides an overview of the topology, requirements, and deployment procedures.

Please read and understand this procedure before executing.

Procedure Steps

Step

Description

Support

Customers can obtain support by submitting a support case.

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server Components

Component

Description

MariaDB MaxScale Components

Component

Description

Topology

The MariaDB Enterprise ColumnStore topology with Object Storage delivers production analytics with high availability, fault tolerance, and limitless data storage by leveraging S3-compatible storage.

The topology consists of:

One or more MaxScale nodes
An odd number of ColumnStore nodes (minimum of 3) running ES, Enterprise ColumnStore, and CMAPI

The MaxScale nodes:

Monitor the health and availability of each ColumnStore node using the MariaDB Monitor (mariadbmon)
Accept client and application connections
Route queries to ColumnStore nodes using the Read/Write Split Router (readwritesplit)

The ColumnStore nodes:

Receive queries from MaxScale
Execute queries
Use for data
Use shared local storage for the Storage Manager directory

Requirements

These requirements are for the ColumnStore Object Storage topology when deployed with MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5, and MariaDB MaxScale 2.5.

Node Count
Operating System
Minimum Hardware Requirements
Recommended Hardware Requirements

Node Count

MaxScale nodes, 1 or more are required.
Enterprise ColumnStore nodes, 3 or more are required for high availability. You should always have an odd number of nodes in a multi-node ColumnStore deployment to avoid split brain scenarios.

Operating System

In alignment to the , the ColumnStore Object Storage topology with MariaDB Enterprise Server 10.5, MariaDB Enterprise ColumnStore 5, and MariaDB MaxScale 2.5 is provided for:

CentOS Linux 7 (x86_64)
Debian 10 (x86_64)
Red Hat Enterprise Linux 7 (x86_64)
Red Hat Enterprise Linux 8 (x86_64)

Minimum Hardware Requirements

The minimum hardware requirements are:

Component

CPU

Memory

MariaDB Enterprise ColumnStore will refuse to start if the system has less than 3 GB of memory.

If Enterprise ColumnStore is started on a system with less memory, the following error message will be written to the ColumnStore system log called crit.log:

And the following error message will be raised to the client:

Recommended Hardware Requirements

MariaDB Enterprise ColumnStore's recommended hardware requirements are intended for production analytics.

The recommended hardware requirements are:

Component

CPU

Memory

Storage Requirements

The ColumnStore Object Storage topology requires the following storage types:

Storage Type

Description

S3-Compatible Object Storage Requirements

The ColumnStore Object Storage topology uses S3-compatible object storage to store data.

For the preferred S3-compatible object storage providers that provide cloud and hardware solutions, see the following sections:

The use of non-cloud and non-hardware providers is at your own risk.

If you have any questions about using specific S3-compatible object storage with MariaDB Enterprise ColumnStore, contact us.

Preferred Object Storage Providers: Cloud

Amazon Web Services (AWS) S3
Google Cloud Storage
Azure Storage
Alibaba Cloud Object Storage Service

Preferred Object Storage Providers: Hardware

Cloudian HyperStore
Cohesity S3
Dell EMC
IBM Cloud Object Storage

Shared Local Storage Directories

The ColumnStore Object Storage topology uses shared local storage for the to store metadata.

The Storage Manager directory is located at the following path by default:

/var/lib/columnstore/storagemanager

Shared Local Storage Options

The most common shared local storage options for the ColumnStore Object Storage topology are:

Shared Local Storage

Common Usage

Description

Recommended Storage Options

For best results, MariaDB Corporation would recommend the following storage options:

Environment

Object Storage For Data

Shared Local Storage For Storage Manager

Enterprise ColumnStore Management with CMAPI

Enterprise ColumnStore's CMAPI (Cluster Management API) is a REST API that can be used to manage a multi-node Enterprise ColumnStore cluster.

Many tools are capable of interacting with REST APIs. For example, the curl utility could be used to make REST API calls from the command-line.

Many programming languages also have libraries for interacting with REST APIs.

The examples below show how to use the CMAPI with curl.

URL Endpoint Format for REST API

For example:

https://mcs1:8640/cmapi/0.4.0/cluster/shutdown
https://mcs1:8640/cmapi/0.4.0/cluster/start
https://mcs1:8640/cmapi/0.4.0/cluster/status

With CMAPI 1.4 and later:

https://mcs1:8640/cmapi/0.4.0/cluster/node

With CMAPI 1.3 and earlier:

https://mcs1:8640/cmapi/0.4.0/cluster/add-node
https://mcs1:8640/cmapi/0.4.0/cluster/remove-node

Required Request Headers

'x-api-key': '93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd'
'Content-Type': 'application/json'

x-api-key can be set to any value of your choice during the first call to the server. Subsequent connections will require this same key.

Get Status

curl examples remain valid but are now considered legacy.

$ mcs cluster status

Start Cluster

$ mcs cluster start --timeout 20

Stop Cluster

$ mcs cluster shutdown --timeout 20

Add Node

With CMAPI 1.4 and later:

With CMAPI 1.3 and earlier:

Remove Node

With CMAPI 1.4 and later:

With CMAPI 1.3 and earlier:

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

MariaDB Enterprise Server Service Management

The systemctl command is used to start and stop the MariaDB Enterprise Server service.

Operation

Command

For additional information, see "".

MariaDB Enterprise Server Logs

MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.

Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the datadir system variable.

Log

System Variable/Option

Default Filename

Enterprise ColumnStore Service Management

The systemctl command is used to start and stop the ColumnStore service.

Operation

Command

Enterprise ColumnStore CMAPI Service Management

The systemctl command is used to start and stop the CMAPI service.

Operation

Command

For additional information on endpoints, see "CMAPI".

MaxScale Configuration Management

MaxScale can be configured using several methods. These methods make use of MaxScale's .

Method

Benefits

The procedure on these pages configures MaxScale using MaxCtrl.

MaxScale Service Management

The systemctl command is used to start and stop the MaxScale service.

Operation

Command

For additional information, see "".

Next Step

Navigation in the procedure "Deploy ColumnStore Object Storage Topology":

Managing ColumnStore

Deployment

Installing ColumnStore

Step 1: Prepare Systems for Enterprise ColumnStore Nodes

Overview

Optimize Linux Kernel Parameters

Temporarily Configure Linux Security Modules (LSM)

CentOS / RHEL Stop SELinux

Debian / Ubuntu AppArmor

Configure Character Encoding

Next Step

Step 2: Install Enterprise ColumnStore

Overview

Step 3: Start and Configure Enterprise ColumnStore

Overview

Step 4: Test Enterprise ColumnStore

Overview

Step 5: Bulk Import of Data

Overview

Import the Schema

Import the Data

cpimport

LOAD DATA INFILE

Import from Remote Database

Next Step

Step 6: Install MariaDB MaxScale

Overview

Retrieve Customer Download Token

Set Up Repository

Install MaxScale

Next Step

Step 6: Install MariaDB MaxScale

Overview

Step 2: Install Enterprise ColumnStore

Overview

Step 5: Bulk Import of Data

Overview

Import the Schema

Import the Data

cpimport

LOAD DATA INFILE

Import from Remote Database

Next Step

Upgrading ColumnStore

Managing ColumnStore Database Environment

Node Maintenance for MariaDB Enterprise Columnstore

Rejoining a Node

Performing Rejoin in MaxScale

Checking Replication Status with MaxScale

Setting a Node to Maintenance Mode

Setting the Server State in MaxScale

Switchover of the Primary Node

Performing Switchover in MaxScale

Checking the Replication Status with MaxScale

View and Clear Table Locks

Viewing Table Locks

Clearing Table Locks

Backup & Restore

ColumnStore Table Size Limitations

Deployment

Managing ColumnStore

Installing ColumnStore

Upgrading ColumnStore

Managing ColumnStore Database Environment

Step 4: Test Enterprise ColumnStore

Overview

Step 2: Install Enterprise ColumnStore

Overview

Node Maintenance for MariaDB Enterprise Columnstore

Test ColumnStore Plugin Status

Test ColumnStore Table Creation

Test Cross Engine Join

Next Step

Set Up Repository

Install Enterprise ColumnStore

Next Step

Step 1: Prepare Systems for Enterprise ColumnStore Nodes

Overview

Optimize Linux Kernel Parameters

Temporarily Configure Linux Security Modules (LSM)