Multi-Node S3

Multi-node MariaDB ColumnStore deployment with S3 object storage: HA topology with three or more nodes, MaxScale routing, CMAPI cluster management, and bulk data import.

This procedure describes the deployment of a Multi-Node ColumnStore topology with Object Storage (S3). This High Availability (HA) configuration delivers production-grade analytics with fault tolerance and scalable storage by leveraging S3-compatible object storage for data and a distributed proxy layer.

The topology consists of:

• One or more MariaDB MaxScale nodes to monitor health and route queries.

• An odd number of ColumnStore nodes (minimum of 3) running MariaDB Enterprise Server, ColumnStore, and the Cluster Management API (CMAPI).

• S3-compatible object storage for data.

• Shared local storage (e.g., NFS, EBS Multi-Attach) for the Storage Manager metadata directory.

The instructions were tested against ColumnStore 23.10.

Procedure

1

Prepare ColumnStore Nodes

MariaDB ColumnStore performs best with specific Linux kernel optimizations. Perform these actions on each ColumnStore node.

Optimize Linux Kernel Parameters

MariaDB ColumnStore performs best with Linux kernel optimizations.

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

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

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

# minimize swapping
vm.swappiness = 1

# Increase the TCP max buffer size
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

# Increase the TCP buffer limits
# min, default, and max number of bytes to use
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# don't cache ssthresh from previous connection
net.ipv4.tcp_no_metrics_save = 1

# for 1 GigE, increase this to 2500
# for 10 GigE, increase this to 30000
net.core.netdev_max_backlog = 2500

1. Use the sysctl command to set the kernel parameters at runtime

$ sudo sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf

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

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

To set SELinux to permissive mode:

1. Set SELinux to permissive mode:

$ sudo setenforce permissive

1. 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:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

1. Confirm that SELinux is in permissive mode:

$ sudo getenforce
Permissive

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 must be disabled before installing MariaDB Enterprise ColumnStore.

1. Disable AppArmor:

$ sudo systemctl disable apparmor

1. Reboot the system.

2. Confirm that no AppArmor profiles are loaded using aa-status:

$ sudo aa-status

apparmor module is loaded.
0 profiles are loaded.
0 profiles are in enforce mode.
0 profiles are in complain mode.
0 processes have profiles defined.
0 processes are in enforce mode.
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

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.

1. On RHEL 8, install additional dependencies:

$ sudo yum install glibc-locale-source glibc-langpack-en

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

$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8

Create an S3 Bucket

If you want to use S3-compatible storage, it is important to create the S3 bucket before you start ColumnStore. If you already have an S3 bucket, confirm that the bucket is empty.

S3 bucket configuration will be performed later in this procedure.

2

Install Enterprise ColumnStore

Retrieve your Customer Download Token from the MariaDB Customer Portal and perform these steps on each ColumnStore node.

Retrieve 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:

1. Navigate to https://customers.mariadb.com/downloads/token/

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

1. On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:

$ curl -LsSO https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup

$ echo "${checksum}  mariadb_es_repo_setup" \
      
 | sha256sum -c -

$ chmod +x mariadb_es_repo_setup

$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply \
      --skip-maxscale \
      --skip-tools \
      --mariadb-server-version="11.4"

Checksums of the various releases of the mariadb_es_repo_setup script can be found in the Versions section at the bottom of the MariaDB Package Repository Setup and Usage page. Substitute ${checksum} in the example above with the latest checksum.

Install Enterprise ColumnStore

Install additional dependencies:

Install on CentOS / RHEL (YUM)

$ sudo yum install epel-release

$ sudo yum install jemalloc

Install of Debian 10 and Ubuntu 20.04 (APT):

$ sudo apt install libjemalloc2

Install on Debian 9 and Ubuntu 18.04 (APT):

$ sudo apt install libjemalloc1

Install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:

Install on CentOS / RHEL (YUM):

$ sudo yum install MariaDB-server \
   MariaDB-backup \
   MariaDB-shared \
   MariaDB-client \
   MariaDB-columnstore-engine

Install on Debian / Ubuntu (APT):

$ sudo apt install mariadb-server \
   mariadb-backup \
   libmariadb3 \
   mariadb-client \
   mariadb-plugin-columnstore

3

Start and Configure Enterprise ColumnStore

Configure Enterprise ColumnStore

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

Connector	MariaDB Connector/R2DBC
character_set_server	Set this system variable to utf8
collation_server	Set this system variable to utf8_general_ci
loose-columnstore_use_import_for_batchinsert	Set this system variable to ALWAYS to always use cpimport for LOAD DATA INFILE and INSERT...SELECT statements.

The loose- prefix is required for ColumnStore system variables in the configuration file. Without it, MariaDB Server will fail to start if the ColumnStore plugin is not installed or has been removed.

Example Configuration

[mariadb]
log_error                              = mariadbd.err
character_set_server                   = utf8
collation_server                       = utf8_general_ci

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:

[ObjectStorage]
…
service = S3
…
[S3]
bucket                = your_columnstore_bucket_name
endpoint              = your_s3_endpoint
aws_access_key_id     = your_s3_access_key_id
aws_secret_access_key = your_s3_secret_key
# iam_role_name       = your_iam_role
# sts_region          = your_sts_region
# sts_endpoint        = your_sts_endpoint
# ec2_iam_mode        = enabled

[Cache]
cache_size = your_local_cache_size
path       = your_local_cache_path

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 iam_role_name, sts_region, and sts_endpoint.

• To use the IAM role assigned to an EC2 instance, you must uncomment ec2_iam_mode=enabled.

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:

$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb

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

$ sudo systemctl start mariadb-columnstore
$ sudo systemctl enable mariadb-columnstore

Create the Utility User

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

1. Create the user account with the CREATE USER statement:

CREATE USER 'util_user'@'127.0.0.1'
IDENTIFIED BY 'util_user_passwd';

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

GRANT SELECT, PROCESS ON *.*
TO 'util_user'@'127.0.0.1';

1. Configure Enterprise ColumnStore to use the utility user:

$ sudo mcsSetConfig CrossEngineSupport Host 127.0.0.1
$ sudo mcsSetConfig CrossEngineSupport Port 3306
$ sudo mcsSetConfig CrossEngineSupport User util_user

1. Set the password:

$ sudo mcsSetConfig CrossEngineSupport Password util_user_passwd

For details about how to encrypt the password, see "Credentials Management for MariaDB Enterprise ColumnStore".

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:

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

$ sudo yum install policycoreutils policycoreutils-python

On RHEL 8, install the following:

$ sudo yum install policycoreutils python3-policycoreutils policycoreutils-python-utils

1. Allow the system to run under load for a while to generate SELinux audit events.

2. After the system has taken some load, generate an SELinux policy from the audit events using audit2allow:

$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local

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

$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local

Nothing to do

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

$ sudo semodule -i mariadb_local.pp

1. 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:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

1. Set SELinux to enforcing mode:

$ sudo setenforce enforcing

Configure AppArmor (Ubuntu)

For information on how to create a profile, see How to create an AppArmor Profile on ubuntu.com.

4

Test Enterprise ColumnStore

Test S3 Connection

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

On each Enterprise ColumnStore node, test the S3 configuration:

$ sudo testS3Connection

StorageManager[26887]: Using the config file found at /etc/columnstore/storagemanager.cnf
StorageManager[26887]: S3Storage: S3 connectivity & permissions are OK
S3 Storage Manager Configuration OK

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

Test Local Connection

Connect to the server using MariaDB Client using the root@localhost user account:

$ sudo mariadb

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 11.4.5-3-MariaDB-Enterprise MariaDB Enterprise Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Test ColumnStore Plugin Status

Query information_schema.PLUGINS and confirm that the ColumnStore storage engine plugin is ACTIVE:

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_LIBRARY LIKE 'ha_columnstore%';

+---------------------+---------------+
| PLUGIN_NAME         | PLUGIN_STATUS |
+---------------------+---------------+
| Columnstore         | ACTIVE        |
| COLUMNSTORE_COLUMNS | ACTIVE        |
| COLUMNSTORE_TABLES  | ACTIVE        |
| COLUMNSTORE_FILES   | ACTIVE        |
| COLUMNSTORE_EXTENTS | ACTIVE        |
+---------------------+---------------+

Test ColumnStore Table Creation

1. Create a test database, if it does not exist:

CREATE DATABASE IF NOT EXISTS test;

1. Create a ColumnStore table:

CREATE TABLE IF NOT EXISTS test.contacts (
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   email VARCHAR(100)
) ENGINE=ColumnStore;

1. Add sample data into the table:

INSERT INTO test.contacts (first_name, last_name, email)
   VALUES
   ("Kai", "Devi", "[e-mail]"),
   ("Lee", "Wang", "[e-mail]");

1. Read data from table:

SELECT * FROM test.contacts;

+------------+-----------+----------------------+
| first_name | last_name | email                |
+------------+-----------+----------------------+
| Kai        | Devi      | [e-mail] |
| Lee        | Wang      | [e-mail] |
+------------+-----------+----------------------+

Test Cross Engine Join

1. Create an InnoDB table:

CREATE TABLE test.addresses (
   email VARCHAR(100),
   street_address VARCHAR(255),
   city VARCHAR(100),
   state_code VARCHAR(2)
) ENGINE = InnoDB;

1. Add data to the table:

INSERT INTO test.addresses (email, street_address, city, state_code)
   VALUES
   ("[e-mail]", "1660 Amphibious Blvd.", "Redwood City", "CA"),
   ("[e-mail]", "32620 Little Blvd", "Redwood City", "CA");

1. Perform a cross-engine join:

SELECT name AS "Name", addr AS "Address"
FROM (SELECT CONCAT(first_name, " ", last_name) AS name,
   email FROM test.contacts) AS contacts
INNER JOIN (SELECT CONCAT(street_address, ", ", city, ", ", state_code) AS addr,
   email FROM test.addresses) AS addr
WHERE  contacts.email = addr.email;

+----------+-----------------------------------------+
| Name     | Address                                 |
+----------+-----------------------------------------+
| Kai Devi | 1660 Amphibious Blvd., Redwood City, CA |
| Lee Wang | 32620 Little Blvd, Redwood City, CA     |
+----------+-----------------------------------------+

+-------------------+-------------------------------------+
| Name              | Address                             |
+-------------------+-------------------------------------+
| Walker Percy      | 500 Thomas More Dr., Covington, LA  |
| Flannery O'Connor | 300 Tarwater Rd., Milledgeville, GA |
+-------------------+-------------------------------------+

5

Bulk Import of Data

Import the Schema

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

On the primary server, create the schema:

1. For each database that you are importing, create the database with the CREATE DATABASE statement:

CREATE DATABASE inventory;

1. For each table that you are importing, create the table with the CREATE TABLE statement:

CREATE TABLE inventory.products (
   product_name VARCHAR(11) NOT NULL DEFAULT '',
   supplier VARCHAR(128) NOT NULL DEFAULT '',
   quantity VARCHAR(128) NOT NULL DEFAULT '',
   unit_cost VARCHAR(128) NOT NULL DEFAULT ''
) ENGINE=Columnstore DEFAULT CHARSET=utf8;

Import the Data

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

cpimport

MariaDB Enterprise ColumnStore includes cpimport, 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 cpimport:

$ sudo cpimport -s '\t' inventory products /tmp/inventory-products.tsv

LOAD DATA INFILE

When data is loaded with the LOAD DATA INFILE statement, MariaDB Enterprise ColumnStore loads the data using cpimport, 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:

LOAD DATA INFILE '/tmp/inventory-products.tsv'
INTO TABLE inventory.products;

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 SELECT statement, and then pipe the results into cpimport, 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:

$ mariadb --quick \
   --skip-column-names \
   --execute="SELECT * FROM inventory.products" \
   | cpimport -s '\t' inventory products

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}