Single-Node Localstorage

Single-node MariaDB ColumnStore deployment with local storage: minimal-footprint topology suitable for development and small production OLAP workloads, including data import.

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

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

Please read and understand this procedure before executing.

Customers can obtain support by submitting a support case.

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server

Modern SQL RDBMS with high availability, pluggable storage engines, hot online backups, and audit logging.

MariaDB Enterprise Server Components

Component

Description

MariaDB ColumnStore

Columnar Storage Engine
Optimized for Online Analytical Processing (OLAP) workloads

Topology

A single MariaDB Enterprise Server node running ColumnStore with local storage.

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

The ColumnStore node:

Receives queries from the application
Executes queries
Uses the local disk for storage.

High Availability

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

Requirements

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

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, ARM64)
Rocky Linux 8 (x86_64, ARM64)
Rocky Linux 9 (x86_64, ARM64)
Ubuntu 20.04 LTS (x86_64, ARM64)
Ubuntu 22.04 LTS (x86_64, ARM64)
Ubuntu 24.04 LTS (x86_64, ARM64)

Minimum Hardware Requirements

MariaDB 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 recommended hardware requirements instead.

The minimum hardware requirements are:

Component

CPU

Memory

ColumnStore node

4+ cores

16+ GB

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

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

Apr 30 21:54:35 a1ebc96a2519 PrimProc[1004]: 35.668435 |0|0|0| C 28 CAL0000: Error total memory available is less than 3GB.

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

ERROR 1815 (HY000): Internal error: System is not ready yet. Please try again.

Recommended Hardware Requirements

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

The recommended hardware requirements are:

Component

CPU

Memory

Enterprise ColumnStore node

64+ cores

128+ GB

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

Configuration File

Configuration files (such as /etc/my.cnf) can be used to set system variables and options. The server must be restarted to apply changes made to configuration files.

Command-line

The server can be started with command-line options that set system variables and options.

SQL

Users can set system variables that support dynamic changes on-the-fly using the SET statement.

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

CentOS
Red Hat Enterprise Linux (RHEL)

/etc/my.cnf.d/z-custom-mariadb.cnf

Debian
Ubuntu

/etc/mysql/mariadb.conf.d/z-custom-mariadb.cnf

MariaDB Enterprise Server Service Management

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

Operation

Command

Start

sudo systemctl start mariadb

Stop

sudo systemctl stop mariadb

Restart

sudo systemctl restart mariadb

Enable during startup

sudo systemctl enable mariadb

Disable during startup

sudo systemctl disable mariadb

Status

sudo systemctl status mariadb

Deployment Procedure

The instructions were tested against ColumnStore 23.10.

Prepare Systems for ColumnStore Nodes

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

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

Optimize Linux Kernel Parameters

MariaDB ColumnStore performs best with Linux kernel optimizations.

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

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

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

$ sudo setenforce permissive

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

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

AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.

Disable AppArmor:

$ sudo systemctl disable apparmor

Reboot the system.
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.

On RHEL 8, install additional dependencies:

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

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

Install ColumnStore

This step installs MariaDB Enterprise Server and MariaDB ColumnStore.

The instructions were tested against ColumnStore 23.10.

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

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:

Navigate to https://customers.mariadb.com/downloads/token/
Log in.
Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

On each 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

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:

$ sudo yum install epel-release
$ sudo yum install jemalloc

$ sudo apt install libjemalloc2

$ sudo apt install libjemalloc1

Install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore

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

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

Start and Configure ColumnStore

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

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

Configure ColumnStore

Mandatory system variables and options for Single-Node 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

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. By default, it connects to the server using the root user with no password. MariaDB Enterprise Server 10.6 will reject this login attempt by default, so you will need to configure Enterprise ColumnStore to use a different user account and password and create this user account on Enterprise Server.

On the Enterprise ColumnStore node, create the user account with the CREATE USER statement:

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

On the Enterprise ColumnStore node, grant the user account SELECT privileges on all databases with the GRANT statement:

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

Configure Enterprise ColumnStore to use the utility user:

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

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:

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

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:

$ 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

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

$ sudo semodule -i mariadb_local.pp

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

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.

Test ColumnStore

This step tests MariaDB Enterprise Server and MariaDB ColumnStore.

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

Create a test database, if it does not exist:

CREATE DATABASE IF NOT EXISTS test;

Create a ColumnStore table:

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

Add sample data into the table:

INSERT INTO test.contacts (first_name, last_name, email)
   VALUES
   ("Kai", "Devi", "kai.devi@example.com"),
   ("Lee", "Wang", "lee.wang@example.com");

Read data from table:

SELECT * FROM test.contacts;

+------------+-----------+----------------------+
| first_name | last_name | email                |
+------------+-----------+----------------------+
| Kai        | Devi      | kai.devi@example.com |
| Lee        | Wang      | lee.wang@example.com |
+------------+-----------+----------------------+

Test Cross Engine Join

Create an InnoDB table:

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

Add data to the table:

INSERT INTO test.addresses (email, street_address, city, state_code)
   VALUES
   ("kai.devi@example.com", "1660 Amphibious Blvd.", "Redwood City", "CA"),
   ("lee.wang@example.com", "32620 Little Blvd", "Redwood City", "CA");

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

Bulk Import of Data

This step bulk imports data to ColumnStore.

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 CREATE DATABASE statement:

CREATE DATABASE inventory;

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

Last updated 6 days ago

Was this helpful?