Managing MariaDB ColumnStore involves setup, configuration, and tools like mcsadmin and cpimport for efficient analytics.
Deployment
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.
Node Maintenance for MariaDB Enterprise Columnstore
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 6: Install MariaDB MaxScale
Step 6: Install MariaDB MaxScale
Overview
This page details step 6 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".
The instructions were tested against ColumnStore 23.10.
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.
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 thesection at the bottom of thepage. 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 Object Storage Topology":
This guide provides steps for deploying a single-node ColumnStore, setting up the environment, installing the software, and bulk importing data for online analytical processing (OLAP) workloads.
This procedure describes the deployment of the Single-Node Enterprise ColumnStore topology with Local storage.
MariaDB Enterprise ColumnStore is a columnar storage engine for MariaDB Enterprise Server 10.6. 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
Uses the local disk for storage.
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 .
Requirements
These requirements are for the Single-Node Enterprise ColumnStore, when deployed with MariaDB Enterprise Server 10.6 and MariaDB Enterprise 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)
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
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 Local storage deployment procedure:
This step installs 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.
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
Log in.
Copy the Customer Download Token.
Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.
Set Up Repository
On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web.
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:
Checksums of the various releases of the mariadb_es_repo_setup script can be found in thesection at the bottom of thepage. Substitute ${checksum} in the example above with the latest checksum.
Install Enterprise ColumnStore
Install additional dependencies:
Install on CentOS / RHEL (YUM)
Install of Debian 10 and Ubuntu 20.04 (APT):
Install on Debian 9 and Ubuntu 18.04 (APT):
Install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
Next Step
Navigation in the Single-Node Enterprise ColumnStore topology with Local storage deployment procedure:
This step bulk imports data to Enterprise ColumnStore.
The instructions were tested against ColumnStore 23.10.
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 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.
The instructions were tested against ColumnStore 23.10.
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
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 Object storage deployment procedure:
This page details step 9 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".
This step bulk imports data to Enterprise ColumnStore.
The instructions were tested against ColumnStore 23.10.
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 CREATE DATABASE statement:
For each table that you are importing, create the table with the CREATE TABLE statement:
Import the Data
Enterprise ColumnStore supports multiple methods to import data into ColumnStore tables.
Interface
Method
Benefits
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
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 , 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 procedure "Deploy ColumnStore Object Storage Topology":
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.
The instructions were tested against ColumnStore 23.10.
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 thesection at the bottom of thepage. 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".
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.
Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.
Set Up Repository
On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web.
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:
Checksums of the various releases of the mariadb_es_repo_setup script can be found in thesection at the bottom of thepage. Substitute ${checksum} in the example above with the latest checksum.
Install Enterprise Server and Enterprise ColumnStore
On each Enterprise ColumnStore node, install additional dependencies:
Install on CentOS and RHEL (YUM):
Install on Debian 9 and Ubuntu 18.04 (APT)
Install on Debian 10 and Ubuntu 20.04 (APT):
On each Enterprise ColumnStore node, install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
Next Step
Navigation in the procedure "Deploy ColumnStore Object Storage Topology".
The instructions were tested against ColumnStore 23.10.
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 CREATE DATABASE statement:
For each table that you are importing, create the table with the CREATE TABLE statement:
Import the Data
Enterprise ColumnStore supports multiple methods to import data into ColumnStore tables.
Interface
Method
Benefits
Shell
SQL access is not required
SQL
Shell access is not required
Remote Database
Use normal database client
Avoid dumping data to intermediate filed
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:
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:
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:
Next Step
Navigation in the procedure "Deploy ColumnStore Shared Local Storage Topology".
Before you determine a backup strategy for your Enterprise ColumnStore deployment, it is a good idea to determine the system of record for your Enterprise ColumnStore data.
A system of record is the authoritative data source for a given piece of information. Organizations often store duplicate information in several systems, but only a single system can be the authoritative data source.
Enterprise ColumnStore is designed to handle analytical processing for OLAP, data warehousing, DSS, and hybrid workloads on very large data sets. Analytical processing does not generally happen on the system of record. Instead, analytical processing generally occurs on a specialized database that is loaded with data from the separate system of record. Additionally, very large data sets can be difficult to back up. Therefore, it may be beneficial to only backup the system of record.
If Enterprise ColumnStore is not acting as the system of record for your data, you should determine how the system of record affects your backup plan:
If your system of record is another database server, you should ensure that the other database server is properly backed up and that your organization has procedures to reload Enterprise ColumnStore from the other database server.
If your system of record is a set of data files, you should ensure that the set of data files is properly backed up and that your organization has procedures to reload Enterprise ColumnStore from the set of data files.
Full Backup and Restore
MariaDB Enterprise ColumnStore supports full backup and restore for all storage types. A full backup includes:
This page details step 3 of the 9-step procedure "Deploy ColumnStore Shared Local Storage Topology".
This step installs MariaDB Enterprise Server, MariaDB Enterprise ColumnStore, CMAPI, and dependencies.
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
Log in.
Copy the Customer Download Token.
Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.
Set Up Repository
On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web.
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:
Checksums of the various releases of the mariadb_es_repo_setup script can be found in thesection at the bottom of thepage. Substitute ${checksum} in the example above with the latest checksum.
Install Enterprise Server and Enterprise ColumnStore
On each Enterprise ColumnStore node, install additional dependencies:
Install on CentOS and RHEL (YUM):
Install on Debian 9 and Ubuntu 18.04 (APT)
Install on Debian 10 and Ubuntu 20.04 (APT):
On each Enterprise ColumnStore node, install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
Next Step
Navigation in the procedure "Deploy ColumnStore Shared Local Storage Topology".
Step 1: Prepare Systems for Enterprise ColumnStore Nodes
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 Object storage.
This step prepares the system 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:
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:
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.
Next Step
Navigation in the Single-Node Enterprise ColumnStore topology with Object storage deployment procedure:
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.
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
Log in.
Copy the Customer Download Token.
Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.
Set Up Repository
On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web.
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:
Checksums of the various releases of the mariadb_es_repo_setup script can be found in thesection at the bottom of thepage. Substitute ${checksum} in the example above with the latest checksum.
Install Enterprise ColumnStore
Install additional dependencies:
Install on CentOS / RHEL (YUM)
Install of Debian 10 and Ubuntu 20.04 (APT):
Install on Debian 9 and Ubuntu 18.04 (APT):
Install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
Next Step
Navigation in the Single-Node Enterprise ColumnStore topology with Object storage deployment procedure:
This page details step 2 of the 9-step procedure "Deploy ColumnStore Shared Local Storage Topology".
This step configures shared local storage on systems hosting Enterprise ColumnStore.
Step 3: Start and Configure Enterprise ColumnStore
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.
Extent Map Backup & Recovery
Overview
MariaDB ColumnStore utilizes an Extent Map to manage data distribution across extents—logical blocks within physical segment files ranging from 8 to 64 MB. Each extent holds a consistent number of rows, with the Extent Map cataloging these extents, their corresponding block identifiers (LBIDs), and the minimum and maximum values for each column's data within the extent.
The primary node maintains the master copy of the Extent Map. Upon system startup, this map is loaded into memory and propagated to other nodes for redundancy and quick access. Corruption of the master Extent Map can render the system unusable and lead to data loss.
Apr 30 21:54:35 a1ebc96a2519 PrimProc[1004]: 35.668435 |0|0|0| C 28 CAL0000: Error total memory available is less than 3GB.
ERROR 1815 (HY000): Internal error: System is not ready yet. Please try again.
$ 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
$ 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)]>
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 |
+---------------------+---------------+
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE IF NOT EXISTS test.contacts (
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
) ENGINE=ColumnStore;
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");
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 |
+-------------------+-------------------------------------+
$ 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)]>
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 |
+---------------------+---------------+
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE IF NOT EXISTS test.contacts (
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
) ENGINE=ColumnStore;
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");
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 |
+-------------------+-------------------------------------+
ERROR 1815 (HY000): Internal error: CAL0009: Drop table failed due to IDB-2009: Unable to perform the drop table operation because cpimport with PID 16301 is currently holding the table lock for session -1.
viewtablelock
There is 1 table lock
Table LockID Process PID Session Txn CreationTime State DBRoots
hq_sales.invoices 1 cpimport 16301 BulkLoad n/a Wed April 7 14:20:42 2021 LOADING 1
viewtablelock hq_sales invoices
There is 1 table lock
Table LockID Process PID Session Txn CreationTime State DBRoots
hq_sales.invoices 1 cpimport 16301 BulkLoad n/a Wed April 7 14:20:42 2021 LOADING 1
Managing MariaDB ColumnStore means deploying its architecture, scaling modules, and maintaining performance through monitoring, optimization, and backups.
The instructions were tested against ColumnStore 23.10.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
Directories for Shared Local Storage
In a ColumnStore Object Storage topology, MariaDB Enterprise ColumnStore requires the Storage Manager directory to be located on shared local storage.
The Storage Manager directory is at the following path:
/var/lib/columnstore/storagemanager
The N in dataN represents a range of integers that starts at 1 and stops at the number of nodes in the deployment. For example, with a 3-node Enterprise ColumnStore deployment, this would refer to the following directories:
/var/lib/columnstore/data1
/var/lib/columnstore/data2
/var/lib/columnstore/data3
The DB Root directories must be mounted on every ColumnStore node.
Choose a Shared Local Storage Solution
Select a Shared Local Storage solution for the Storage Manager directory:
EBS (Elastic Block Store) Multi-Attach
EFS (Elastic File System)
Filestore
GlusterFS
NFS (Network File System)
For additional information, see "Shared Local Storage Options".
Configure EBS Multi-Attach
EBS is a high-performance block-storage service for AWS (Amazon Web Services). EBS Multi-Attach allows an EBS volume to be attached to multiple instances in AWS. Only clustered file systems, such as GFS2, are supported.
For Enterprise ColumnStore deployments in AWS:
EBS Multi-Attach is a recommended option for the Storage Manager directory.
Amazon S3 storage is the recommended option for data.
Consult the vendor documentation for details on how to configure EBS Multi-Attach.
Configure Elastic File System (EFS)
EFS is a scalable, elastic, cloud-native NFS file system for AWS (Amazon Web Services)
For deployments in AWS:
EFS is a recommended option for the Storage Manager directory.
Amazon S3 storage is the recommended option for data.
Consult the vendor documentation for details on how to configure EFS.
Configure Filestore
Filestore is high-performance, fully managed storage for GCP (Google Cloud Platform).
For Enterprise ColumnStore deployments in GCP:
Filestore is the recommended option for the Storage Manager directory.
Google Object Storage (S3-compatible) is the recommended option for data.
Consult the vendor documentation for details on how to configure Filestore.
Configure GlusterFS
GlusterFS is a distributed file system.
GlusterFS is a shared local storage option, but it is not one of the recommended options.
On each Enterprise ColumnStore node, install GlusterFS.
Install on CentOS / RHEL 8 (YUM):
Install on CentOS / RHEL 7 (YUM):
Install on Debian (APT):
Install on Ubuntu (APT):
Start the GlusterFS Daemon
Start the GlusterFS daemon:
Probe the GlusterFS Peers
Before you can create a volume with GlusterFS, you must probe each node from a peer node.
On the primary node, probe all of the other cluster nodes:
On one of the replica nodes, probe the primary node to confirm that it is connected:
On the primary node, check the peer status:
Number of Peers: 2
Configure and Mount GlusterFS Volumes
Create the GlusterFS volumes for MariaDB Enterprise ColumnStore. Each volume must have the same number of replicas as the number of Enterprise ColumnStore nodes.
On each Enterprise ColumnStore node, create the directory for each brick in the /brick directory:
On the primary node, create the GlusterFS volumes:
On the primary node, start the volume:
On each Enterprise ColumnStore node, create mount points for the volumes:
On each Enterprise ColumnStore node, add the mount points to /etc/fstab:
On each Enterprise ColumnStore node, mount the volumes:
Configure Network File System (NFS)
NFS is a distributed file system. NFS is available in most Linux distributions. If NFS is used for an Enterprise ColumnStore deployment, the storage must be mounted with the sync option to ensure that each node flushes its changes immediately.
For on-premises deployments:
NFS is the recommended option for the Storage Manager directory.
Any S3-compatible storage is the recommended option for data.
Consult the documentation for your NFS implementation for details on how to configure NFS.
ColumnStore's extent map is a smart structure that underpins its performance. By providing a logical partitioning scheme, it avoids the overhead associated with indexing and other common row-based database optimizations.
The primary node in a ColumnStore cluster holds the master copy of the extent map. Upon system startup, this master copy is read into memory and then replicated to all other participating nodes for high availability and disaster recovery. Nodes keep the extent map in memory for rapid access during query processing. As data within extents is modified, these updates are broadcast to all participating nodes to maintain consistency.
If the master copy of the extent map becomes corrupted, the entire system could become unusable, potentially leading to data loss. Having a recent backup of the extent map allows for a much faster recovery compared to reloading the entire database in such a scenario.
Backup Procedure
Note: MariaDB recommends implementing regular backups to ensure data integrity and recovery. A common default is to back up every 3 hours and retain backups for at least 10 days.
To safeguard against potential Extent Map corruption, regularly back up the master copy:
Lock Table:
Save BRM:
Create Backup Directory:
Copy Extent Map:
Unlock Tables:
Recovery Procedures
Single-Node System
Stop ColumnStore:
Rename Corrupted Map:
Clear Versioning Files:
Restore Backup:
Set Ownership:
Start ColumnStore:
Clustered System
Shutdown Cluster:
Rename Corrupted Map:
Clear Versioning Files:
Restore Backup:
Set Ownership:
Start Cluster:
Automation Recommendation
Incorporate the save_brm command into your data import scripts (e.g., those using cpimport) to automate Extent Map backups. This practice ensures regular backups without manual intervention.
Refer to the MariaDB ColumnStore Backup Script for an example implementation.
# 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
# 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
$ sudo getenforce
Permissive
$ sudo systemctl disable apparmor
$ 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.
As the second argument, provide maintenance as the state.
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
Set this system variable to utf8
Set this system variable to utf8_general_ci
columnstore_use_import_for_batchinsert
Set this system variable to ALWAYS to always use cpimport for and statements.
Example Configuration
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. 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 statement:
On the Enterprise ColumnStore node, grant the user account SELECT privileges on all databases with the GRANT statement:
Configure Enterprise ColumnStore to use the utility user:
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:
The ColumnStore engine does not fully support recursive Common Table Expressions (CTEs). Attempting to use recursive CTEs directly against ColumnStore tables typically results in an error.
The purpose of the following examples is to demonstrate three potential workarounds for this issue. The best fit for your organization will depend on your specific needs and ability to refactor queries and adjust your approach.
Setup: Simulating an Org Chart
It simulates a simple organizational chart with employees and managers to illustrate the problem and the workarounds.
First, an InnoDB table for comparison:
Next, the ColumnStore table, which is where the CTE issue arises:
Attempting to run a recursive CTE directly on the employees (ColumnStore) table:
This will result in the aforementioned error:
Workarounds
Here are three potential workarounds to address the recursive CTE limitation with MariaDB ColumnStore.
Option 1: Toggle ColumnStore Select Handler
You can temporarily bypass ColumnStore's SELECT handler by disabling it at the session level before executing your recursive CTE and then re-enabling it afterwards.
Note: This workaround may not always be effective, as its success can depend on the specific MariaDB server version and table definitions.
Option 2: Use Procedural Simulation via Temporary Table
If direct recursive CTEs fail or cause server crashes, you can simulate the recursive logic using a stored procedure and a temporary table. This approach iteratively populates the hierarchy.
First, create a temporary table to store the hierarchical data:
Next, create a stored procedure to iteratively populate the temp_org_chart table:
Finally, call the stored procedure and then select from the populated temporary table:
Option 3: Clone Data into InnoDB
Another robust workaround is to clone the structure and data of the ColumnStore table into an InnoDB table. Once the data resides in an InnoDB table, you can execute the recursive CTE as usual, as InnoDB fully supports them.
This approach involves a few steps, often executed via shell commands interacting with the MariaDB client:
Extract and Modify CREATE TABLE Statement: Use SHOW CREATE TABLE to get the definition of your ColumnStore table, then modify it to change the engine to InnoDB and give the new table a different name (e.g., employees2).
Create New Table and Copy Data: Execute the modified CREATE TABLE script to create the new InnoDB table, then insert all data from the original ColumnStore table into it.
Run Recursive CTE on the InnoDB Table: Now, with the data in employees2 (an InnoDB table), you can run your recursive CTE without issues.
This page details step 5 of the 9-step procedure "Deploy ColumnStore Shared Local Storage Topology".
This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.
Step 1: Prepare ColumnStore Nodes
Step 1: Prepare ColumnStore Nodes
Overview
This page details step 1 of the 9-step procedure "".
This step prepares systems to host MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.
Step 1: Prepare ColumnStore Nodes
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.
Step 7: Start and Configure MariaDB MaxScale
Step 7: Start and Configure MariaDB MaxScale
Overview
This page details step 7 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".
This step starts and configures MariaDB MaxScale 22.08.
Step 2: Configure Shared Local Storage
Step 2: Configure Shared Local Storage
Overview
This page details step 2 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".
This step configures shared local storage on systems hosting Enterprise ColumnStore.
Optimizing Linux Kernel Parameters for MariaDB ColumnStore
This page provides information on optimizing Linux kernel parameters for improved performance with MariaDB ColumnStore.
Introduction
MariaDB ColumnStore is a high-performance columnar database designed for analytical workloads. By optimizing the Linux kernel parameters, you can further enhance the performance of your MariaDB ColumnStore deployments.
Backup and Restore with Object Storage
Overview
MariaDB Enterprise ColumnStore supports backup and restore. If Enterprise ColumnStore uses for data and shared local storage for the , the S3 bucket, the Storage Manager directory, and the MariaDB data directory must be backed up separately.
Backup and Restore with Shared Local Storage
Overview
MariaDB Enterprise ColumnStore supports backup and restore. If Enterprise ColumnStore uses shared local storage for the DB Root directories, the DB Root directories and the MariaDB data directory must be backed up separately.
$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local
Nothing to do
$ sudo semodule -i mariadb_local.pp
# 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
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:
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.
Temporarily Configure Firewall for Installation
MariaDB Enterprise ColumnStore requires the following TCP ports:
TCP Ports
Description
3306
Port used for MariaDB Client traffic
8600-8630
Port range used for inter-node communication
8640
Port used by CMAPI
8700
Port used for inter-node communication
8800
Port used for inter-node communication
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.
Next Step
Navigation in the procedure "Deploy ColumnStore Shared Local Storage Topology".
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:
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.
Temporarily Configure Firewall for Installation
MariaDB Enterprise ColumnStore requires the following TCP ports:
TCP Ports
Description
3306
Port used for MariaDB Client traffic
8600-8630
Port range used for inter-node communication
8640
Port used by CMAPI
8700
Port used for inter-node communication
8800
Port used for inter-node communication
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":
CREATE TABLE employees_innodb (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT -- references employees.id (nullable for top-level)
);
INSERT INTO employees_innodb (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'VP of Sales', 1),
(3, 'Sales Rep A', 2),
(4, 'VP of Eng', 1),
(5, 'Eng A', 4),
(6, 'Eng B', 4);
CREATE TABLE employees (
id INT,
name VARCHAR(100),
manager_id INT -- references employees.id (nullable for top-level)
) engine=columnstore;
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'VP of Sales', 1),
(3, 'Sales Rep A', 2),
(4, 'VP of Eng', 1),
(5, 'Eng A', 4),
(6, 'Eng B', 4);
WITH RECURSIVE org_chart AS (
-- Anchor: start with the top-level manager (CEO)
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE id = 1
UNION ALL
-- Recursive step: find employees who report to the previous level
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;
ERROR 1178 (42000): The storage engine for the table doesn't support Recursive CTE
SET SESSION columnstore_select_handler=OFF;
WITH RECURSIVE org_chart AS (
-- Anchor: start with the top-level manager (CEO)
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE id = 1
UNION ALL
-- Recursive step: find employees who report to the previous level
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;
SET SESSION columnstore_select_handler=ON;
CREATE TABLE temp_org_chart (
id INT,
name VARCHAR(100),
manager_id INT,
level INT
);
-- Initialize the temporary table with the top-level employees
INSERT INTO temp_org_chart (id, name, manager_id, level)
SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL;
DELIMITER //
CREATE OR REPLACE PROCEDURE populate_org_chart()
BEGIN
DECLARE v_level INT DEFAULT 1;
DECLARE rows_inserted INT DEFAULT 1;
-- Loop until no more rows are inserted, indicating the hierarchy is fully traversed
WHILE rows_inserted > 0 DO
-- Insert employees who report to the previous level
INSERT INTO temp_org_chart (id, name, manager_id, level)
SELECT e.id, e.name, e.manager_id, v_level
FROM employees e
JOIN temp_org_chart t ON e.manager_id = t.id
WHERE t.level = v_level - 1
AND NOT EXISTS (
SELECT 1 FROM temp_org_chart x WHERE x.id = e.id
);
-- Get the number of rows inserted in the current iteration
SET rows_inserted = ROW_COUNT();
-- Increment the level for the next iteration
SET v_level = v_level + 1;
END WHILE;
END //
DELIMITER ;
CALL populate_org_chart();
SELECT * FROM temp_org_chart;
mariadb test < create_employees2.sql
mariadb test -e "INSERT INTO employees2 SELECT * FROM employees"
WITH RECURSIVE org_chart AS (
-- Anchor: start with the top-level manager (CEO)
SELECT id, name, manager_id, 0 AS level
FROM employees2
WHERE id = 1
UNION ALL
-- Recursive step: find employees who report to the previous level
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees2 e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;
# 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
# 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
$ sudo getenforce
Permissive
$ sudo systemctl disable apparmor
$ 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.
# 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
# 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
$ sudo getenforce
Permissive
$ sudo systemctl disable apparmor
$ 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.
The instructions were tested against ColumnStore 23.10.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
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 MariaDB Client 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 Shared Local Storage Topology".
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 maxctrl create 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).
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.
password='MAXSCALE_USER_PASSWORD' sets the password parameter to the password used by the database user account that MaxScale uses to monitor the ColumnStore nodes.
replication_user=REPLICATION_USER sets the replication_user parameter to the database user account that MaxScale uses to setup replication.
replication_password='REPLICATION_USER_PASSWORD' sets the replication_password parameter to the password used by the database user account that MaxScale uses to setup replication.
--servers sets the servers parameter to the set of nodes that MaxScale should monitor. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by mariadbmon in MaxScale 22.08 can also be specified.
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
Connection-based load balancing
Routes connections to Enterprise ColumnStore nodes designated as replica servers for a read-only pool
Routes connections to an Enterprise ColumnStore node designated as the primary server for a read-write pool.|
Query-based load balancing
Routes write queries to an Enterprise ColumnStore node designated as the primary server
Routes read queries to Enterprise ColumnStore node designated as replica servers
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.
password=MAXSCALE_USER_PASSWORD sets the password parameter to the password used by the database user account that MaxScale uses to connect to the ColumnStore nodes.
router_options=slave sets the router_options parameter to slave, so that MaxScale only routes connections to the replica nodes.
--servers sets the servers parameter to the set of nodes to which MaxScale should route connections. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by readconnroute in MaxScale 22.08 can also be specified.
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.
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.
protocol=MariaDBClient sets the protocol parameter.
Other Module Parameters supported by listeners in MaxScale 22.08 can also be specified.
Configure Read/Write Split Router for Queries
MaxScale Read/Write Split Router (readwritesplit) performs query-based load balancing. The router routes write queries to the primary and read queries to the replicas.
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.
password=MAXSCALE_USER_PASSWORD sets the password parameter to the password used by the database user account that MaxScale uses to connect to the ColumnStore nodes.
--servers sets the servers parameter to the set of nodes to which MaxScale should route queries. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by readwritesplit in MaxScale 22.08 can also be specified.
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.
EBS is a high-performance block-storage service for AWS (Amazon Web Services). EBS Multi-Attach allows an EBS volume to be attached to multiple instances in AWS. Only clustered file systems, such as GFS2, are supported.
For Enterprise ColumnStore deployments in AWS:
EBS Multi-Attach is a recommended option for the Storage Manager directory.
Amazon S3 storage is the recommended option for data.
Consult the vendor documentation for details on how to configure EBS Multi-Attach.
Configure Elastic File System (EFS)
EFS is a scalable, elastic, cloud-native NFS file system for AWS (Amazon Web Services)
For deployments in AWS:
EFS is a recommended option for the Storage Manager directory.
Amazon S3 storage is the recommended option for data.
Consult the vendor documentation for details on how to configure EFS.
Configure Filestore
Filestore is high-performance, fully managed storage for GCP (Google Cloud Platform).
For Enterprise ColumnStore deployments in GCP:
Filestore is the recommended option for the Storage Manager directory.
Google Object Storage (S3-compatible) is the recommended option for data.
Consult the vendor documentation for details on how to configure Filestore.
Configure GlusterFS
GlusterFS is a distributed file system. GlusterFS is a shared local storage option, but it is not one of the recommended options.
On each Enterprise ColumnStore node, install GlusterFS.
Install on CentOS / RHEL 8 (YUM):
Install on CentOS / RHEL 7 (YUM):
Install on Debian (APT):
Install on Ubuntu (APT):
Start the GlusterFS Daemon
Start the GlusterFS daemon:
Probe the GlusterFS Peers
Before you can create a volume with GlusterFS, you must probe each node from a peer node.
On the primary node, probe all of the other cluster nodes:
On one of the replica nodes, probe the primary node to confirm that it is connected:
On the primary node, check the peer status:
Configure and Mount GlusterFS Volumes
Create the GlusterFS volumes for MariaDB Enterprise ColumnStore. Each volume must have the same number of replicas as the number of Enterprise ColumnStore nodes.
On each Enterprise ColumnStore node, create the directory for each brick in the /brick directory:
On the primary node, create the GlusterFS volumes:
On the primary node, start the volume:
On each Enterprise ColumnStore node, create mount points for the volumes:
On each Enterprise ColumnStore node, add the mount points to /etc/fstab:
On each Enterprise ColumnStore node, mount the volumes:
Configure Network File System (NFS)
NFS is a distributed file system. NFS is available in most Linux distributions. If NFS is used for an Enterprise ColumnStore deployment, the storage must be mounted with the sync option to ensure that each node flushes its changes immediately.
For on-premises deployments:
NFS is the recommended option for the Storage Manager directory.
Any S3-compatible storage is the recommended option for data.
Consult the documentation for your NFS implementation for details on how to configure NFS.
Next Step
Navigation in the procedure "Deploy ColumnStore Object Storage Topology":
The following table lists the recommended optimized Linux kernel parameters for MariaDB ColumnStore:
For more information refer to .
Parameter
Recommended Value
Explanation
vm.overcommit_memory
1
Disables overcommitting of memory, ensuring sufficient memory is available for MariaDB ColumnStore.
vm.dirty_background_ratio
5
Sets the percentage of dirty memory that can be written back to disk in the background. A lower value reduces the amount of dirty memory, improving performance.
vm.dirty_ratio
10
Sets the percentage of dirty memory that can be written back to disk before the kernel starts to write out clean pages. A lower value reduces the amount of dirty memory, improving performance.
vm.vfs_cache_pressure
50
Configuration Example
To configure these parameters, you can add them to the /etc/sysctl.conf file. For example:
After making changes to the /etc/sysctl.conf file, you need to apply the changes by running the following command:
Increase the Limit for Memory-Mapped Areas
Common Use Cases
These optimized parameters are recommended for all MariaDB ColumnStore deployments, regardless of the specific workload. They can improve performance for various use cases, including:
By optimizing the Linux kernel parameters, you can significantly improve the performance of your MariaDB ColumnStore deployments. These recommendations provide a starting point for optimizing your system, and you may need to adjust the values based on your specific hardware and workload.
Any file can become corrupt due to hardware issues, crashes, power loss, and other reasons. If the Enterprise ColumnStore data or metadata become corrupt, Enterprise ColumnStore could become unusable, resulting in data loss.
If Enterprise ColumnStore is your system of record, it should be backed up regularly.
If Enterprise ColumnStore uses S3-compatible object storage for data and shared local storage for the Storage Manager directory, the following items must be backed up:
The MariaDB Data directory is backed up using .
The S3 bucket must be backed up using the vendor's snapshot procedure.
Ensure that all services are stopped on each node:
Restore the backup of the Storage Manager directory. By default, it is located at /var/lib/columnstore/storagemanager.
For example, to restore the backup with rsync:
Use to restore the backup of the MariaDB data directory:
Restore the snapshot of your S3-compatible storage to the new S3 bucket that you plan to use. Consult the storage vendor's manual for details on how to do this.
Update storagemanager.cnf to configure Enterprise ColumnStore to use the S3 bucket. By default, it is located at /etc/columnstore/storagemanager.cnf.
For example:
The default local cache size is 2 GB.
The default local cache path is /var/lib/columnstore/storagemanager/cache.
Ensure that the local cache path has sufficient store space to store the local cache.
The bucket option must be set to the name of the bucket that you created from your snapshot in the previous step.
To use an IAM role, you must also uncomment and set iam_role_name, sts_region, and sts_endpoint.
Any file can become corrupt due to hardware issues, crashes, power loss, and other reasons. If the Enterprise ColumnStore data or metadata become corrupt, Enterprise ColumnStore could become unusable, resulting in data loss.
If Enterprise ColumnStore is your system of record, it should be backed up regularly.
Determine which node is the primary server using curl to send the status command to the CMAPI Server:
The output will show dbrm_mode: master for the primary server:
Connect to the primary server using MariaDB Client as a user account that has privileges to lock the database:
Lock the database with the statement:
Ensure that the client remains connected to the primary server, so that the lock is held for the remaining steps.
Make a copy or snapshot of the Storage Manager directory. By default, it is located at /var/lib/columnstore/storagemanager.
For example, to make a copy of the directory with rsync:
Make a copy or snapshot of the DB Root directories. By default, they are located at /var/lib/columnstore/dataN, where the N in dataN represents a range of integers that starts at 1 and stops at the number of nodes in the deployment.
For example, to make a copy of the directories with rsync in a 3-node deployment:
Ensure that all services are stopped on each node:
Restore the backup of the Storage Manager director. By default, it is located at /var/lib/columnstore/storagemanager.
For example, to restore the backup with rsync:
Restore the backup of the DB Root directories. By default, they are located at /var/lib/columnstore/dataN, where the N in dataN represents a range of integers that starts at 1 and stops at the number of nodes in the deployment.
For example, to restore the backup with rsync in a 3-node deployment:
Use to restore the backup of the MariaDB data directory:
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.
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".
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.
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
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 details step 5 of the 9-step procedure "Deploy ColumnStore Object Storage Topology".
This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.
Upgrade Multi-Node MariaDB Enterprise ColumnStore from 6 to 23.10
These instructions detail the upgrade from MariaDB Enterprise ColumnStore 6 to MariaDB Enterprise ColumnStore 23.10 in a Multi-Node topology on a range of .
Set Replicas to Maintenance Mode
This action is performed for each replica server on the MaxScale node.
Prior to upgrading, the replica servers must be in MaxScale. The replicas can be set to maintenance mode in MaxScale using
$ systemctl status mariadb
$ sudo systemctl start mariadb
$ 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)]>
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 |
+---------------------+---------------+
password='MAXSCALE_USER_PASSWORD' sets the password parameter to the password used by the database user account that MaxScale uses to monitor the ColumnStore nodes.
replication_user=REPLICATION_USER sets the replication_user parameter to the database user account that MaxScale uses to setup replication.
replication_password='REPLICATION_USER_PASSWORD' sets the replication_password parameter to the password used by the database user account that MaxScale uses to setup replication.
--servers sets the servers parameter to the set of nodes that MaxScale should monitor. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by mariadbmon in MaxScale 22.08 can also be specified.
password=MAXSCALE_USER_PASSWORD sets the password parameter to the password used by the database user account that MaxScale uses to connect to the ColumnStore nodes.
router_options=slave sets the router_options parameter to slave, so that MaxScale only routes connections to the replica nodes.
--servers sets the servers parameter to the set of nodes to which MaxScale should route connections. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by readconnroute in MaxScale 22.08 can also be specified.
protocol=MariaDBClient sets the protocol parameter.
Other Module Parameters supported by listeners in MaxScale 22.08 can also be specified.
password=MAXSCALE_USER_PASSWORD sets the password parameter to the password used by the database user account that MaxScale uses to connect to the ColumnStore nodes.
--servers sets the servers parameter to the set of nodes to which MaxScale should route queries. All non-option arguments after --servers are interpreted as server names.
Other Module Parameters supported by readwritesplit in MaxScale 22.08 can also be specified.
protocol=MariaDBClient sets the protocol parameter.
Other Module Parameters supported by listeners in MaxScale 22.08 can also be specified.
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:
, the replicas can be set to maintenance mode using the set server command:
As the first argument, provide the name for the server
As the second argument, provide maintenance as the state
Confirm Maintenance Mode is Set for Replicas
This action is performed on the MaxScale node.
Confirm that the replicas are set to maintenance mode in MaxScale using MaxScale's REST API. If you are using MaxCtrl, the state of the replicas can be viewed using the list servers command:
If the node is properly in maintenance mode, then the State column will show Maintenance as one of the states.
Disable GTID Strict Mode
This action is performed on each replica server.
The system variable must be disabled for this upgrade procedure. If the gtid_strict_mode system variable is enabled in any configuration files, disable it temporarily until the upgrade procedure is complete.
You can check if the gtid_strict_mode system variable is set in a configuration file by executing my_print_defaults command with the mysqld option:
If the gtid_strict_mode system variable is set, you can temporarily disable it by adding # in front of it in the configuration file, so that it will be treated as a comment and ignored:
Shutdown ColumnStore
Prior to upgrading, MariaDB Enterprise ColumnStore must be shutdown.
Stop Services
This action is performed on each ColumnStore node.
Prior to upgrading, several services must be stopped on each ColumnStore node:
Enterprise ColumnStore 23.10 is included with MariaDB Enterprise Server 11.4. Pass the version to install using the --mariadb-server-version flag to .
To configure YUM package repositories:
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.
Update MariaDB Enterprise Server and package dependencies:
Enterprise ColumnStore 23.10 is included with MariaDB Enterprise Server 11.4. Pass the version to install using the --mariadb-server-version flag to mariadb_es_repo_setup.
To configure APT package repositories:
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.
Update MariaDB Enterprise Server and package dependencies.
The update command depends on the installed APT version, which can be determined by executing the following command:
For versions prior to APT 2.0, execute the following command:
For APT 2.0 and later, execute the following command:
Disable ColumnStore Service
This action is performed on each ColumnStore node.
After upgrading, the MariaDB Enterprise ColumnStore service should be stopped, since it will be controlled by CMAPI:
CMAPI disables the Enterprise ColumnStore service in a multi-node deployment. The Enterprise ColumnStore service will be started as-needed by the CMAPI service, so it does not need to start automatically upon reboot.
Start Services
This action is performed on each ColumnStore node.
After upgrading, the CMAPI service and the MariaDB Enterprise Server service must be started on each ColumnStore node:
Start the CMAPI service:
Start the MariaDB Enterprise Server service:
Write Binary Log
On the primary server, run mariadb-upgrade to upgrade the data directory with binary logging enabled to update the system tables:
Start ColumnStore
After upgrading, MariaDB Enterprise ColumnStore must be started.
Enable GTID Strict Mode
This action is performed on each replica server.
If you temporarily disabled the system variable in the Disable GTID Strict Mode step, it can be re-enabled. If the gtid_strict_mode system variable was temporarily disabled in any configuration files, re-enable it.
Confirm ColumnStore Version
This action is performed on each ColumnStore node.
After upgrading, it is recommended to confirm the Enterprise ColumnStore version on each ColumnStore node. Connect to the node using and query the Columnstore_version status variable with :
Confirm ES Version
This action is performed on each ColumnStore node.
After upgrading, it is recommended to confirm the ES version on each ColumnStore node. Connect to the node using and query the version system variable with :
Clear Maintenance Mode for Replicas
This action is performed for each replica server on the MaxScale node.
After the upgrade, maintenance mode for each replica has been cleared in MaxScale using . If you are using , maintenance mode can be cleared using the clear server command:
As the first argument, provide the name for the server
As the second argument, provide maintenance as the state
Confirm Maintenance Mode is Cleared for Replicas
This action is performed for each replica server on the MaxScale node.
Confirm that maintenance mode in MaxScale has been cleared for each replica using . If you are using , the state of the replicas can be viewed using the list servers command:
If the node is no longer in maintenance mode, then the State column will no longer show Maintenance as one of the states.
Sets the pressure level for the kernel's VFS cache. A lower value reduces the amount of memory used by the VFS cache, improving performance.
net.core.netdev_max_backlog
2500
Sets the maximum number of packets that can be queued for a network device. A higher value allows for more packets to be queued, improving performance.
net.core.rmem_max
16777216
Sets the maximum receive buffer size for TCP sockets. A higher value allows for larger buffers, improving performance.
net.core.wmem_max
16777216
Sets the maximum send buffer size for TCP sockets. A higher value allows for larger buffers, improving performance.
net.ipv4.tcp_max_syn_backlog
8192
Sets the maximum number of queued SYN requests. A higher value allows for more queued requests, improving performance.
net.ipv4.tcp_timestamps
0
Disables TCP timestamps, reducing overhead and improving performance.
vm.max_map_count
4,262,144
Increases the maximum number of memory map areas a process may have. The default is 65,530, which can be too low for workloads like MariaDB ColumnStore. Raising this prevents mapping errors for processes that need large address spaces.
kernel.pid_max
4,194,304
Defines the maximum process ID value. Older Linux versions defaulted to 32,768; newer versions default to 4,194,304. Raising this ensures support for systems running a very large number of processes concurrently.
kernel.threads-max
2,000,000
Specifies the maximum number of threads allowed on the system. The default varies depending on available RAM. A value of 2 million is suitable for systems with 32–64GB RAM. Increase further if running with more RAM or requiring more threads.
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.
