Quickstart Guide: MariaDB ColumnStore

MariaDB ColumnStore is a specialized columnar storage engine designed for high-performance analytical processing and big data workloads. Unlike traditional row-based storage engines, ColumnStore organizes data by columns, which is highly efficient for analytical queries that often access only a subset of columns across vast datasets.

1. What is MariaDB ColumnStore?

MariaDB ColumnStore is a columnar storage engine that integrates with MariaDB Server. It employs a massively parallel distributed data architecture, making it ideal for processing petabytes of data with linear scalability. It was originally ported from InfiniDB and is released under the GPL license.

2. Key Benefits

• Exceptional Analytical Performance: Delivers superior performance for complex analytical queries (OLAP) due to its columnar nature, which minimizes disk I/O by reading only necessary columns.

• High Data Compression: Columnar storage allows for much higher compression ratios compared to row-based storage, reducing disk space usage and improving query speed.

• Massive Scalability: Designed to scale horizontally across multiple nodes, processing petabytes of data with ease.

• Just-in-Time Projection: Only the required columns are processed and returned, further optimizing query execution.

• Real-time Analytics: Capable of handling real-time analytical queries efficiently.

3. Architecture Concepts (Simplified)

MariaDB ColumnStore utilizes a distributed architecture with different components working together:

• User Module (UM): Handles incoming SQL queries, optimizes them for columnar processing, and distributes tasks.

• Performance Module (PM): Manages data storage, compression, and execution of query fragments on the data segments.

• Data Files: Data is stored in column-segments across the nodes, highly compressed.

4. Installation Overview

MariaDB ColumnStore is installed as a separate package that integrates with MariaDB Server. The exact installation steps vary depending on your operating system and desired deployment type (single server or distributed cluster).

General Steps (conceptual):

1. Install MariaDB Server: Ensure you have a compatible MariaDB Server version installed (e.g., MariaDB 10.5.4 or later).

2. Install ColumnStore Package: Download and install the specific MariaDB ColumnStore package for your OS. This package includes the ColumnStore storage engine and its associated tools.

   • Linux (e.g., Debian/Ubuntu): You would typically add the MariaDB repository configured for ColumnStore and then install mariadb-plugin-columnstore.

   • Single Server vs. Distributed: For a single-server setup, you install all ColumnStore components on one machine. For a distributed setup, you install and configure components across multiple machines.

3. Configure MariaDB: After installation, you might need to adjust your MariaDB server configuration (my.cnf or equivalent) to properly load and manage the ColumnStore engine.

4. Initialize ColumnStore: Run a specific columnstore-setup or post-install script to initialize the ColumnStore environment.

5. Basic Usage

Once MariaDB ColumnStore is installed and configured, you can create and interact with ColumnStore tables using standard SQL.

a. Create a ColumnStore Table:

Specify ENGINE=ColumnStore when creating your table. Note that ColumnStore tables do not support primary keys in the same way as InnoDB, as their primary focus is analytical processing.

CREATE TABLE sales_data (
    sale_id INT,
    product_name VARCHAR(255),
    category VARCHAR(100),
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
) ENGINE=ColumnStore;

b. Insert Data:

You can insert data using standard INSERT statements. For large datasets, bulk loading utilities (e.g., LOAD DATA INFILE) are highly recommended for performance.

INSERT INTO sales_data (sale_id, product_name, category, sale_date, quantity, price) VALUES
(1, 'Laptop', 'Electronics', '2023-01-15', 1, 1200.00),
(2, 'Mouse', 'Electronics', '2023-01-15', 2, 25.00),
(3, 'Keyboard', 'Electronics', '2023-01-16', 1, 75.00);

c. Query Data:

Perform analytical queries. ColumnStore will efficiently process these, often leveraging its columnar nature and parallelism.

-- Get total sales per category
SELECT category, SUM(quantity * price) AS total_sales
FROM sales_data
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY category
ORDER BY total_sales DESC;

-- Count distinct products
SELECT COUNT(DISTINCT product_name) FROM sales_data;

Further Resources:

• MariaDB ColumnStore Overview

• MariaDB documentation: MariaDB ColumnStore

• DigitalOcean: How to Install MariaDB ColumnStore on Ubuntu 20.04

Quickstart Guide: MariaDB ColumnStore Minimum Hardware Specifications

MariaDB ColumnStore is designed for analytical workloads and scales linearly with hardware resources. While the performance generally improves with more CPU cores, memory, and servers, understanding the minimum hardware specifications is crucial for successful deployment, especially in development and production environments.

General Principle

MariaDB ColumnStore's performance directly benefits from additional hardware resources. More CPU cores enable greater parallel processing, increased memory allows for more data caching (reducing I/O), and more servers enable a larger distributed architecture.

Minimum Hardware Recommendations

The specifications differentiate between a basic development environment and a production-ready setup:

1. For Development Environments:

• CPU: A minimum of 8 CPU cores.

• Memory (RAM): A minimum of 32 GB.

• Storage: Local disk storage is acceptable for development purposes.

2. For Production Environments:

• CPU: A minimum of 64 CPU cores.

  • Note: This recommendation underscores the highly parallel nature of ColumnStore, which can effectively utilize a large number of cores for analytical processing.

• Memory (RAM): A minimum of 128 GB.

  • Note: Adequate memory is critical for caching data and intermediate results, directly impacting query performance.

• Storage: StorageManager (S3) is recommended.

  • Note: This implies leveraging cloud-object storage (like AWS S3 or compatible services) for scalable and durable data persistence in production.

Network Interconnectivity (for Multi-Server Deployments)

• Minimum Network: For multi-server ColumnStore deployments, a minimum of a 1 Gigabit (1G) network is recommended.

  • Note: This facilitates efficient data transfer between nodes via TCP/IP for replication and query processing across the distributed architecture. For optimal performance in heavy-load scenarios, higher bandwidth (e.g., 10G or more) is highly beneficial.

Adhering to these minimum specifications will provide a baseline for ColumnStore functionality. For specific workload requirements, it's always advisable to conduct performance testing and scale hardware accordingly.

Further Resources:

• MariaDB ColumnStore Minimum Hardware Specification Documentation

• MariaDB ColumnStore Overview

• MariaDB documentation: MariaDB ColumnStore

MariaDB ColumnStore enhances MariaDB Enterprise Server with a columnar engine for OLAP and HTAP workloads, using MPP for scalability. It supports cross-engine JOINs, integrates with S3 storage, and provides high-speed bulk loading with multi-node management via REST API.

MariaDB ColumnStore is a columnar storage engine designed for distributed massively parallel processing (MPP), such as for big data analysis. Deployments can be composed of several MariaDB servers or just one, each running several subprocess working together to provide linear scalability and exceptional performance with real-time response to analytical queries.

It provides a highly available, fault tolerant, and performant columnar storage engine for MariaDB Enterprise Server. MariaDB Enterprise ColumnStore is designed for data warehousing, decision support systems (DSS), online analytical processing (OLAP), and hybrid transactional-analytical processing (HTAP).

Benefits

• Columnar storage engine that enables MariaDB Enterprise Server to perform new workloads

• Optimized for online analytical process (OLAP) workloads including data warehousing, decision support systems, and business intelligence

• Single-stack solution for hybrid transactional-analytical workloads to eliminate barriers and prevent data silos

• Implements cross-engine JOINs to join Enterprise ColumnStore tables with tables using row-based storage engines, such as InnoDB

• Smart storage engine that plans and optimizes its own queries using a custom select handler

• Scalable query execution using massively parallel processing (MPP) strategies, parallel query execution, and distributed function evaluation

• S3-compatible object storage can be used for highly available, low-cost, multi-regional, resilient, scalable, secure, and virtually limitless data storage

• High availability and automatic failover by leveraging MariaDB MaxScale

• REST API for multi-node administration with the Cluster Management API (CMAPI) server

• Connectors for popular BI platforms such as Microsoft Power BI and Tableau

• High-speed bulk data loading that bypasses the SQL layer and does not block concurrent read-only queries

Topologies

MariaDB Enterprise ColumnStore supports multiple topologies. Several options are described below. MariaDB Enterprise ColumnStore can be deployed in other topologies. The topologies on this page are representative of basic product capabilities.

MariaDB products can be deployed to form other topologies that leverage advanced product capabilities and combine the capabilities of multiple topologies.

Enterprise ColumnStore with Object Storage

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

The topology consists of:

• One or more MaxScale nodes

• An odd number of ColumnStore nodes (minimum of 3) running ES, Enterprise ColumnStore, and CMAPI

The MaxScale nodes:

• Monitor the health and availability of each ColumnStore node using the MariaDB Monitor (mariadbmon)

• Accept client and application connections

• Route queries to ColumnStore nodes using the Read/Write Split Router (readwritesplit)

The ColumnStore nodes:

• Receive queries from MaxScale

• Execute queries

• Use S3-compatible object storage for data

• Use Shared Local Storage for the Storage Manager directory.

Enterprise ColumnStore with Shared Local Storage

The MariaDB Enterprise ColumnStore topology with Shared Local Storage delivers production analytics with high availability and fault tolerance by leveraging shared local storage, such as NFS.

The topology consists of:

• One or more MaxScale nodes

• An odd number of ColumnStore nodes (minimum of 3) running ES, Enterprise ColumnStore, and CMAPI

The MaxScale nodes:

• Monitor the health and availability of each ColumnStore node using the MariaDB Monitor (mariadbmon)

• Accept client and application connections

• Route queries to ColumnStore nodes using the Read/Write Split Router (readwritesplit)

The ColumnStore nodes:

• Receive queries from MaxScale

• Execute queries

• Use Shared Local Storage for the DB Root directories.

Software Architecture

MariaDB Enterprise ColumnStore

MariaDB Enterprise ColumnStore is the columnar storage engine that handles data storage and query optimization/execution.

MariaDB Enterprise ColumnStore is a columnar storage engine that is optimized for analytical or online analytical processing (OLAP) workloads, data warehousing, and DSS. MariaDB Enterprise ColumnStore can be used for hybrid transactional-analytical processing (HTAP) workloads when paired with a row-based storage engine, like InnoDB.

MariaDB Enterprise Server

MariaDB Enterprise ColumnStore is built on top of MariaDB Enterprise Server. MariaDB Enterprise ColumnStore 5 is included with the standard MariaDB Enterprise Server 10.5 releases, while MariaDB Enterprise ColumnStore 6 is included with the standard MariaDB Enterprise Server 10.6 releases.

Enterprise ColumnStore interfaces with the Enterprise Server SQL engine through the ColumnStore storage engine plugin.

MariaDB has been continually improving the integration of MariaDB Enterprise ColumnStore with MariaDB Enterprise Server:

• MariaDB ColumnStore required special custom-built releases of MariaDB Server.

• MariaDB Enterprise ColumnStore was included with the standard MariaDB Enterprise Server 10.5 releases up to ES 10.5.5-3. It was the first release to replace the Operations/Administration/Maintenance (OAM) API with the more modern Cluster Management API (CMAPI), which is still in use.

• Starting with ES 10.5.6-4, MariaDB Enterprise ColumnStore is included with the standard MariaDB Enterprise Server 10.5 releases.

ColumnStore Storage Engine Plugin

MariaDB Enterprise ColumnStore integrates with MariaDB Enterprise Server using the ColumnStore storage engine plugin. The ColumnStore storage engine plugin enables MariaDB Enterprise Server to interact with ColumnStore tables.

The ColumnStore storage engine plugin is a smart storage engine that implements a custom select handler to fully take advantage of Enterprise ColumnStore's capabilities, such as:

• Using a custom query planner

• Selecting data by column instead of by row

• Parallel query evaluation

• Distributed aggregations

• Distributed functions

• Extent elimination

As a smart storage engine, the ColumnStore storage engine plugin tightly integrates Enterprise ColumnStore with ES, but it has enough independence to efficiently execute analytical queries using a completely unique approach.

For additional information, see "ColumnStore Storage Engine".

Cluster Management API (CMAPI) Server

The Cluster Management API (CMAPI) server provides a REST API that can be used to configure and manage Enterprise ColumnStore.

CMAPI must run on every ColumnStore node in a multi-node deployment but is not required in a single-node deployment.

The REST API can be used to perform multiple operations:

• Add ColumnStore nodes

• Remove ColumnStore nodes

• Start Enterprise ColumnStore

• Shutdown Enterprise ColumnStore

• Check the status of Enterprise ColumnStore

MariaDB MaxScale

MariaDB Enterprise ColumnStore leverages MariaDB MaxScale as an advanced database proxy and query router.

Multi-node Enterprise ColumnStore deployments must have one or more MaxScale nodes. MaxScale performs many different roles:

• Routing writes queries to the primary server

• Load balancing read queries on replica servers

• Monitoring node health

• Performing automatic failover if a node fails

Storage Architecture

MariaDB Enterprise ColumnStore's storage architecture provides a columnar storage engine with high availability, fault tolerance, compression, and automatic partitioning for production analytics and data warehousing.

For additional information, see "MariaDB Enterprise ColumnStore and ColumnStore Storage Architecture".

Columnar Storage Engine

MariaDB Enterprise ColumnStore is a columnar storage engine for MariaDB Enterprise Server. MariaDB Enterprise ColumnStore enables ES to perform analytical workloads, including online analytical processing (OLAP), data warehousing, decision support systems (DSS), and hybrid transactional-analytical processing (HTAP) workloads.

Most traditional relational databases use row-based storage engines. In row-based storage engines, all columns for a table are stored contiguously. Row-based storage engines perform very well for transactional workloads but are less performant for analytical workloads.

Columnar storage engines store each column separately. Columnar storage engines perform very well for analytical workloads. Analytical workloads are characterized by ad hoc queries on very large data sets by relatively few users.

MariaDB Enterprise ColumnStore automatically partitions each column into extents, which helps improve query performance without using indexes.

S3-Compatible Object Storage

MariaDB Enterprise ColumnStore supports S3-compatible object storage.

S3-compatible object storage is optional, but highly recommended. If S3-compatible object storage is used, Enterprise ColumnStore requires the Storage Manager directory to use Shared Local Storage (such as NFS) for high availability.

S3-compatible object storage is:

• Compatible: Many object storage services are compatible with the Amazon S3 API.

• Economical: S3-compatible object storage is often very low cost.

• Flexible: S3-compatible object storage is available for both cloud and on-premises deployments.

• Limitless: S3-compatible object storage is often virtually limitless.

• Resilient: S3-compatible object storage is often low maintenance and highly available, since many services use resilient cloud infrastructure.

• Scalable: S3-compatible object storage is often highly optimized for read and write scaling.

• Secure: S3-compatible object storage is often encrypted-at-rest.

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

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

Shared Local Storage

MariaDB Enterprise ColumnStore can use shared local storage.

Shared local storage is required for high availability. The specific Shared Local Storage requirements depend on whether Enterprise ColumnStore is configured to use S3-compatible object storage:

• When S3-compatible object storage is used, Enterprise ColumnStore requires the Storage Manager directory to use shared local storage for high availability.

• When S3-compatible object storage is not used, Enterprise ColumnStore requires the DB Root directories to use shared local storage for high availability.

The most common shared local storage options for on-premises and cloud deployments are:

• NFS (Network File System)

• GlusterFS

The most common shared local storage options for AWS (Amazon Web Services) deployments are:

• EBS (Elastic Block Store) Multi-Attach

• EFS (Elastic File System)

The most common shared local storage option for GCP (Google Cloud Platform) deployments is:

• Filestore

Query Evaluation Architecture

MariaDB Enterprise ColumnStore uses distributed query execution and massively parallel processing (MPP) techniques to achieve vertical and horizontal scalability for production analytics and data warehousing.

For additional information, see "MariaDB Enterprise ColumnStore Query Evaluation".

Extent Elimination

MariaDB Enterprise ColumnStore uses extent elimination to scale query evaluation as the table size increases.

Most databases are row-based, utilizing manually created indexes to achieve high performance on large tables. This works well for transactional workloads. However, analytical queries tend to have very low selectivity, so traditional indexes are not typically effective for analytical queries.

Enterprise ColumnStore uses extent elimination to achieve high performance, without requiring manually created indexes. Enterprise ColumnStore automatically partitions all data into extents. Enterprise ColumnStore stores the minimum and maximum values for each extent in the extent map. Enterprise ColumnStore uses the minimum and maximum values in the extent map to perform extent elimination.

When Enterprise ColumnStore performs extent elimination, it compares the query's join conditions and filter conditions (i.e., WHERE clause) to the minimum and maximum values for each extent in the extent map. If the extent's minimum and maximum values fall outside the bounds of the query's conditions, Enterprise ColumnStore skips that extent for the query.

Extent elimination is automatically performed for every query. It can significantly decrease I/O for columns with clustered values. For example, extent elimination works effectively for series, ordered, patterned, and time-based data.

Custom Select Handler

The ColumnStore storage engine plugin implements a custom select handler to fully take advantage of Enterprise ColumnStore's capabilities.

All storage engines interact with ES using an internal handler API, which is highly extensible. Storage engines can implement different features by implementing different methods within the handler API.

For select statements, the handler API transforms each query into a SELECT_LEX object, which is provided to the select handler.

The generic select handler is not optimal for Enterprise ColumnStore, because:

• Enterprise ColumnStore selects data by column, but the generic selects handler selects data by row.

• Enterprise ColumnStore supports parallel query evaluation, but the generic select handler does not.

• Enterprise ColumnStore supports distributed aggregations, but the generic select handler does not.

• Enterprise ColumnStore supports distributed functions, but the generic select handler does not.

• Enterprise ColumnStore supports extent elimination, but the generic select handler does not.

• Enterprise ColumnStore has its own query planner, but the generic select handler cannot use it.

Smart Storage Engine

The ColumnStore storage engine plugin is known as a smart storage engine, because it implements a custom select handler. MariaDB Enterprise ColumnStore integrates with MariaDB Enterprise Server using the ColumnStore storage engine plugin. The ColumnStore storage engine plugin enables MariaDB Enterprise Server to interact with ColumnStore tables.

If a storage engine implements a custom select handler, it is known as a smart storage engine.

As a smart storage engine, the ColumnStore storage engine plugin tightly integrates Enterprise ColumnStore with ES, but it has enough independence to efficiently execute analytical queries using a completely unique approach.

Query Planning

The ColumnStore storage engine plugin is a smart storage engine, so MariaDB Enterprise ColumnStore to plan its queries using the custom select handler.

MariaDB Enterprise ColumnStore's query planning is divided into two steps:

1. ES provides the query's SELECT_LEX object to the custom select handler. The custom selects handler builds a ColumnStore Execution Plan (CSEP).

2. The custom select handler provides the CSEP to the ExeMgr process on the same node. The ExeMgr process performs extent elimination and creates a job list.

Job Steps

When Enterprise ColumnStore executes a query, the ExeMgr process on the initiator/aggregator node translates the ColumnStore execution plan (CSEP) into a job list. A job list is a sequence of job steps.

Enterprise ColumnStore uses many different types of job steps that provide different scalability benefits:

• Some types of job steps perform operations in a distributed manner using multiple nodes to operate on different extents. Distributed operations provide horizontal scalability.

• Some types of job steps perform operations in a multi-threaded manner using a thread pool. Performing multi-threaded operations provides vertical scalability.

As you increase the number of ColumnStore nodes or the number of cores on each node, Enterprise ColumnStore can use those resources to more efficiently execute job steps.

High Availability and Failover

MariaDB Enterprise ColumnStore leverages common technologies to provide highly available production analytics with automatic failover:

Shared Local Storage

MariaDB Enterprise ColumnStore can use shared local storage.

Shared local storage is required for high availability. The specific Shared Local Storage requirements depend on whether Enterprise ColumnStore is configured to use S3-compatible object storage:

• When S3-compatible object storage is used, Enterprise ColumnStore requires the Storage Manager directory to use shared local storage for high availability.

• When S3-compatible object storage is not used, Enterprise ColumnStore requires the DB Root directories to use Shared Local Storage for high availability.

The most common shared local storage options for on-premises and cloud deployments are:

• NFS (Network File System)

• GlusterFS

The most common shared local storage options for AWS (Amazon Web Services) deployments are:

• EBS (Elastic Block Store) Multi-Attach

• EFS (Elastic File System)

The most common shared local storage option for GCP (Google Cloud Platform) deployments is:

• Filestore

MariaDB Replication

MariaDB Enterprise ColumnStore requires MariaDB Replication to synchronize various database objects on multiple nodes for high availability.

MariaDB replication synchronizes:

• The schemas for all ColumnStore tables on all nodes

• The schemas and data for all non-ColumnStore tables on all nodes

• All other databases objects (stored procedures, stored functions, user accounts, and other objects) on all nodes

MaxScale

MariaDB Enterprise ColumnStore requires MariaDB MaxScale to achieve high availability, automatic failover, and load balancing.

MariaDB Monitor (mariadbmon) in MaxScale monitors the health of each Enterprise ColumnStore node.

MaxScale provides load balancing by routing queries and/or connections to healthy nodes by:

• Providing query-based routing using Read/Write Split Router (readwritesplit).

• Providing connection-based routing using Read Connection Router (readconnroute).

When MaxScale's MariaDB Monitor notices the primary node fail, MariaDB Monitor performs automatic failover by:

• Promoting a replica node to become the new primary node.

• Re-configuring all replica nodes to replicate from the new primary node.

Cluster Management API (CMAPI) Server

MariaDB Enterprise ColumnStore requires the Cluster Management API (CMAPI) Server for high availability.

The CMAPI server provides a REST API that can be used to manage and configure Enterprise ColumnStore.

The CMAPI server has a role in automatic failover. After MaxScale performs automatic failover, the CMAPI server detects the topology change and automatically re-configures the roles of each Enterprise ColumnStore node.

Data Loading

MariaDB Enterprise ColumnStore performs bulk data loads very efficiently using a variety of mechanisms including the cpimport tool, specialized handling of certain SQL statements, and minimal locking during data import.

For additional information, see "MariaDB Enterprise ColumnStore Data Loading".

cpimport

MariaDB Enterprise ColumnStore includes a bulk data loading tool called cpimport, which provides several benefits:

• Bypasses the SQL layer to decrease overhead

• Does not block read queries

• Requires a write metadata lock on the table, which can be monitored with the METADATA_LOCK_INFO plugin.

• Appends the new data to the table. While the bulk load is in progress, the newly appended data is temporarily hidden from queries. After the bulk load is complete, the newly appended data is visible to queries.

• Inserts each row in the order the rows are read from the source file. Users can optimize data loads for Enterprise ColumnStore's automatic partitioning by loading presorted data files. For additional information, see "Load Ordered Data in Proper Order".

• Supports parallel distributed bulk loads

• Imports data from text files

• Imports data from binary files

• Imports data from standard input (stdin)

Batch Insert Mode

MariaDB Enterprise ColumnStore enables batch insert mode by default.

When batch insert mode is enabled, MariaDB Enterprise ColumnStore, MariaDB Enterprise ColumnStore has special handling for the following statements:

• LOAD DATA INFILE

• INSERT INTO .. SELECT FROM .

Enterprise ColumnStore uses the following rules:

• If the statement is executed outside of a transaction, Enterprise ColumnStore loads the data using cpimport, which is a command-line utility that is designed to efficiently load data in bulk. Enterprise ColumnStore executes cpimport using a wrapper called cpimport.bin.

• If the statement is executed inside of a transaction, Enterprise ColumnStore loads the data using the DML interface, which is slower.

Batch insert mode can be disabled by setting the columnstore_use_import_for_batchinsert system variable. When batch insert mode is disabled, Enterprise ColumnStore executes the statements using the DML interface, which is slower.

Locking

MariaDB Enterprise ColumnStore requires a write metadata lock (MDL) on the table when a bulk data load is performed with cpimport.

When a bulk data load is running:

• Read queries will not be blocked.

• Write queries and concurrent bulk data loads on the same table will be blocked until the bulk data load operation is complete, and the write metadata lock on the table has been released.

• The write metadata lock (MDL) can be monitored with the METADATA_LOCK_INFO plugin.

Backup and Restore

MariaDB Enterprise ColumnStore supports backup and restore using well-known tools and methods.

For additional information, see "MariaDB Enterprise ColumnStore Backup and Restore".

S3-Compatible Object Storage

MariaDB Enterprise ColumnStore can leverage S3 snapshots to backup S3-compatible object storage when it is used for Enterprise ColumnStore's data.

The S3-compatible object storage can be backed up by:

1. Locking the database on the primary node

2. Performing an S3 snapshot using the vendor's standard snapshot functionality

Shared Local Storage

MariaDB Enterprise ColumnStore can leverage file system snapshots or file copy tools (such as rsync) to backup shared local storage when it is used for the Storage Manager directory or the DB Root directories.

The shared local storage can be backed up by:

1. Locking the database on the primary node

2. Performing a file system snapshot or using a file copy tool (such as rsync) to copy the contents of the Storage Manager directory or the DB Root directories.

Enterprise Server Data Directory

MariaDB Enterprise ColumnStore can leverage the standard MariaDB Enterprise Backup utility to back up the Enterprise Server data directory.

The backup contains:

• All ColumnStore schemas

• All non-ColumnStore schemas and data

• All other database objects

It does not contain:

• ColumnStore data

_{This page is licensed: CC BY-SA / Gnu FDL}

Clients issue a query to the MariaDB Server, which has the ColumnStore storage engine installed. MariaDB Server parses the SQL, identifies the involved ColumnStore tables, and creates an initial logical query execution plan.

Using the ColumnStore storage engine interface (ha_columnstore), MariaDB Server converts involved table references into ColumnStore internal objects. These are then handed off to the ExeMgr, which is responsible for managing and orchestrating query execution across the cluster.

The ExeMgr analyzes the query plan and translates it into a distributed ColumnStore execution plan. It determines the necessary query steps and the execution order, including any required parallelization.

The ExeMgr then references the extent map to identify which PrimProc instances hold the relevant data segments. It applies extent elimination to exclude any PrimProc nodes whose extents do not match the query’s filter criteria.

The ExeMgr dispatches commands to the selected PrimProc instances to perform data block I/O operations.

The PrimProc components perform operations such as

• Predicate filtering

• Join processing

• Initial aggregation

• Data retrieval from local disk or external storage (e.g., S3 or cloud object storage)

They then return intermediate result sets to the ExeMgr.

The ExeMgr handles:

• Final-stage aggregation

• Window function evaluation

• Result-set sorting and shaping

The completed result set is returned to the MariaDB Server, which performs any remaining SQL operations like ORDER BY, LIMIT, or computed expressions in the SELECT list.

Finally, the MariaDB Server returns the result set to the client.

Overview

MariaDB Enterprise ColumnStore's storage architecture is designed to provide great performance for analytical queries.

Columnar Storage Engine

MariaDB Enterprise ColumnStore is a columnar storage engine for MariaDB Enterprise Server. MariaDB Enterprise ColumnStore enables ES to perform analytical workloads, including online analytical processing (OLAP), data warehousing, decision support systems (DSS), and hybrid transactional-analytical processing (HTAP) workloads.

Most traditional relational databases use row-based storage engines. In row-based storage engines, all columns for a table are stored contiguously. Row-based storage engines perform very well for transactional workloads but are less performant for analytical workloads.

Columnar storage engines store each column separately. Columnar storage engines perform very well for analytical workloads. Analytical workloads are characterized by ad hoc queries on very large data sets by relatively few users.

MariaDB Enterprise ColumnStore automatically partitions each column into extents, which helps improve query performance without using indexes.

OLAP Workloads

MariaDB Enterprise ColumnStore enables MariaDB Enterprise Server to perform analytical or online analytical processing (OLAP) workloads.

OLAP workloads are generally characterized by ad hoc queries on very large data sets. Some other typical characteristics are:

• Each query typically reads a subset of columns in the table

• Most activity typically consists of read-only queries that perform aggregations, window functions, and various calculations

• Analytical applications typically require only a few concurrent queries

• Analytical applications typically require the scalability of large, complex queries

• Analytical applications typically require efficient bulk loads of new data

OLAP workloads are typically required for:

• Business intelligence (BI)

• Health informatics

• Historical data mining

Row-based storage engines have a disadvantage for OLAP workloads. Indexes are not usually very useful for OLAP workloads, because the large size of the data set and the ad hoc nature of the queries preclude the use of indexes to optimize queries.

Columnar storage engines are much better suited for OLAP workloads. MariaDB Enterprise ColumnStore is a columnar storage engine that is designed for OLAP workloads:

• When a query reads a subset of columns in the table, Enterprise ColumnStore can reduce I/O by reading those columns and ignoring all others, because each column is stored separately

• When most activity consists of read-only queries that perform aggregations, window functions, and various calculations, Enterprise ColumnStore is able to efficiently execute those queries using extent elimination, distributed query execution, and massively parallel processing (MPP) techniques

• When only a few concurrent queries are required, Enterprise ColumnStore is able to maximize the use of system resources by using multiple threads and multiple nodes to perform work for each query

• When scalability of large, complex queries is required, Enterprise ColumnStore is able to achieve horizontal and vertical scalability using distributed query execution and massively parallel processing (MPP) techniques

• When efficient bulk loads of new data are required, Enterprise ColumnStore is able to bulk load new data without affecting existing data using automatic partitioning with the extent map

OLTP Workloads

MariaDB Enterprise Server has had excellent performance for transactional or online transactional processing (OLTP) workloads since the beginning.

OLTP workloads are generally characterized by a fixed set of queries using a relatively small data set. Some other typical characteristics are:

• Each query typically reads and/or writes many columns in the table.

• Most activity typically consists of small transactions that only read and/or write a small number of rows.

• Transactional applications typically require many concurrent transactions.

• Transactional applications typically require a fast response time and low latency.

• Transactional applications typically require ACID properties to protect data.

OLTP workloads are typically required for:

• Financial transactions performed by financial institutions and e-commerce sites.

• Store inventory changes performed by brick-and-mortar stores and e-commerce sites.

• Account metadata changes performed by many sites that stores personal data.

Row-based storage engines have several advantages for OLTP workloads:

• When a query reads and/or writes many columns in the table, row-based storage engines can find all columns on a single page, so the I/O costs of the operation are low.

• When a transaction reads/writes a small number of rows, row-based storage engines can use an index to find the page for each row without a full table scan.

• When many concurrent transactions are operating, row-based storage engines can implement transactional isolation by storing multiple versions of changed rows.

• When a fast response time and low latency are required, row-based storage engines can use indexes to optimize the most common queries.

• When ACID properties are required, row-based storage engines can implement consistency and durability with fewer performance trade-offs, since each row's columns are stored contiguously.

InnoDB is ES's default storage engine, and it is a highly performant row-based storage engine.

Hybrid Workloads

MariaDB Enterprise ColumnStore enables MariaDB Enterprise Server to function as a single-stack solution for Hybrid transactional-analytical processing (HTAP) workloads.

Hybrid workloads are characterized by a mix of transactional and analytical queries. Hybrid workloads are also known as "Smart Transactions", "Augmented Transactions" "Translytical", or "Hybrid Operational-Analytical Processing (HOAP)".

Hybrid workloads are typically required for applications that require real-time analytics that lead to immediate action:

• Financial institutions use transactional queries to handle financial transactions and analytical queries to analyze the transactions for business intelligence.

• Insurance companies use transactional queries to accept/process claims and analytical queries to analyze those claims for business opportunities or risks.

• Health providers use transactional queries to track electronic health records (EHR) and analytical queries to analyze the EHRs to discover health trends or prevent adverse drug interactions.

MariaDB Enterprise Server provides multiple components to perform hybrid workloads:

• For analytical queries, the Enterprise ColumnStore storage engine can be used.

• For transactional queries, row-based storage engines, such as InnoDB, can be used.

• For queries that reference both analytical and transactional data, ES's cross-engine join functionality can be used to join Enterprise ColumnStore tables with InnoDB tables.

• MariaDB MaxScale is a high-performance database proxy that can dynamically route analytical queries to Enterprise ColumnStore and transactional queries to the transactional storage engine.

Storage Options

MariaDB Enterprise ColumnStore supports multiple storage types:

Deployment with S3-Compatible Storage

Deployment with Shared Storage

S3-Compatible Object Storage

MariaDB Enterprise ColumnStore supports S3-compatible object storage.

S3-compatible object storage is optional, but highly recommended. If S3-compatible object storage is used, Enterprise ColumnStore requires the Storage Manager directory to use Shared Local Storage (such as NFS) for high availability.

S3-compatible object storage is:

• Compatible: Many object storage services are compatible with the Amazon S3 API.

• Economical: S3-compatible object storage is often very low cost.

• Flexible: S3-compatible object storage is available for both cloud and on-premises deployments.

• Limitless: S3-compatible object storage is often virtually limitless.

• Resilient: S3-compatible object storage is often low maintenance and highly available, since many services use resilient cloud infrastructure.

• Scalable: S3-compatible object storage is often highly optimized for read and write scaling.

• Secure: S3-compatible object storage is often encrypted-at-rest.

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

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

S3 API

MariaDB Enterprise ColumnStore can use any object store that is compatible with the Amazon S3 API.

Many object storage services are compatible with the Amazon S3 API, and compatible object storage services are available for cloud deployments and on-premises deployments, so vendor lock-in is not a concern.

Storage Manager

MariaDB Enterprise ColumnStore's Storage Manager enables remote S3-compatible object storage to be efficiently used. The Storage Manager uses a persistent local disk cache for read/write operations, so that network latency has minimal performance impact on Enterprise ColumnStore. In some cases, it will even perform better than local disk operations.

Enterprise ColumnStore only uses the Storage Manager when S3-compatible storage is used for data.

Storage Manager is configured using storagemanager.cnf.

Storage Manager Directory

MariaDB Enterprise ColumnStore's Storage Manager directory is at the following path by default:

/var/lib/columnstore/storagemanager

To enable high availability when S3-compatible object storage is used, the Storage Manager directory should use Shared Local Storage and be mounted on every ColumnStore node.

Configure the S3 Storage Manager

When you want to use S3-compatible storage for Enterprise ColumnStore, you must configure Enterprise ColumnStore's S3 Storage Manager to use S3-compatible storage.

To configure Enterprise ColumnStore to use S3-compatible storage, edit /etc/columnstore/storagemanager.cnf:

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

[Cache]
cache_size = your_local_cache_size
path = your_local_cache_path

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

• The bucket option must be set to the name of the bucket.

• The endpoint option must be set to the endpoint for the S3-compatible object storage.

• The aws_access_key_id and aws_secret_access_key options must be set to the access key ID and secret access key for the S3-compatible object storage.

• To use a specific IAM role, you must uncomment and set iam_role_name, sts_region, and sts_endpoint.

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

• To use a non-default port number, you must set port_number to the desired port.

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.

Shared Local Storage

MariaDB Enterprise ColumnStore can use shared local storage.

Shared local storage is required for high availability. The specific Shared Local Storage requirements depend on whether Enterprise ColumnStore is configured to use S3-compatible object storage:

When S3-compatible object storage is used, Enterprise ColumnStore requires the Storage Manager directory to use shared local storage for high availability.

When S3-compatible object storage is not used, Enterprise ColumnStore requires the DB Root directories to use shared local storage for high availability.

The most common shared local storage options for on-premises and cloud deployments are:

• NFS (Network File System)

• GlusterFS

The most common shared local storage options for AWS (Amazon Web Services) deployments are:

• EBS (Elastic Block Store) Multi-Attach

• EFS (Elastic File System)

The most common shared local storage option for GCP (Google Cloud Platform) deployments is:

• Filestore

Shared Local Storage Options

The most common options for shared local storage are:

Directories Requiring Shared Local Storage for HA

Multi-node MariaDB Enterprise ColumnStore requires some directories to use shared local storage for high availability. The specific requirements depend on if MariaDB Enterprise ColumnStore is configured to use S3-compatible object storage:

Recommended Storage Options

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

Storage Format

MariaDB Enterprise ColumnStore's storage format is optimized for analytical queries.

DB Root Directories

MariaDB Enterprise ColumnStore stores data in DB Root directories when S3-compatible object storage is not configured.

In a multi-node Enterprise ColumnStore, each node has its own DB Root directory.

The DB Root directories are at the following path by default:

• /var/lib/columnstore/dataN

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

To enable high availability for the DB Root directories, each directory should be mounted on every ColumnStore node using Shared Local Storage.

Extents

Each column in a table is stored in units called extents.

By default, each extent contains the column values for 8 million rows. The physical size of each extent can range from 8 MB to 64 MB. When an extent reaches the maximum number of column values, Enterprise ColumnStore creates a new extent.

Each extent is stored in 8 KB blocks, and each block has a logical block identifier (LBID).

If a string column is longer than 8 characters, the value is stored in a separate dictionary file, and a pointer to the value is stored in the extent.

Segment Files

A segment file is used to store Enterprise ColumnStore data within a DB Root directory.

A segment file always contains two extents. When a segment file reaches its maximum size, Enterprise ColumnStore creates a new segment file.

The relevant configuration options are:

For example, to configure Enterprise ColumnStore to store more extents in each segment file using the mcsSetConfig utility:

$ mcsSetConfig ExtentMap ExtentsPerSegmentFile 4

Column Partitions

Enterprise ColumnStore automatically groups a column's segment files into column partitions.

On disk, each column partition is represented by a directory in the DB Root. The directory contains the segment files for the column partition.

By default, a column partition can contain four segment files, but you can configure Enterprise ColumnStore to store more segment files in each column partition. When a column partition reaches the maximum number of segment files, Enterprise ColumnStore creates a new column partition.

The relevant configuration options are:

For example, to configure Enterprise ColumnStore to store more segment files in each column partition using the mcsSetConfig utility:

$ mcsSetConfig ExtentMap FilesPerColumnPartition 8

Extent Map

Enterprise ColumnStore maintains an Extent Map to determine which values are located in each extent.

The Extent Map identifies each extent using its logical block identifier (LBID) values, and it maintains the minimum and maximum values within each extent.

The Extent Map is used to implement a performance optimization called Extent Elimination.

The primary node has a master copy of the Extent Map. When Enterprise ColumnStore is started, the primary node copies the Extent Map to the replica nodes.

While Enterprise ColumnStore is running, each node maintains a copy of the Extent Map in its main memory, so that it can be accessed quickly without additional I/O.

If the Extent Map gets corrupted, the mcsRebuildEM utility can rebuild the Extent Map from the contents of the database file system. The mcsRebuildEM utility is available starting in MariaDB Enterprise ColumnStore 6.2.2.

Compression

Enterprise ColumnStore automatically compresses all data on disk using either Snappy or LZ4 compression. See the columnstore_compression_type system variable for how to select the desired compression type.

Since Enterprise ColumnStore stores a single column's data in each segment file, the data in each segment file tends to be very similar. Similar data usually allows for excellent compressibility. However, the specific data compression ratio will depend on a lot of factors, such as the randomness of the data and the number of distinct values.

Enterprise ColumnStore's compression strategy is tuned to optimize the performance of I/O-bound queries, because the decompression rate is optimized to maximize read performance.

Version Buffer

Enterprise ColumnStore uses the version buffer to store blocks that are being modified.

The version buffer is used for multiple tasks:

• It is used to roll back a transaction.

• It is used for multi-version concurrency control (MVCC). With MVCC, Enterprise ColumnStore can implement read snapshots, which allows a statement to have a consistent view of the database, even if some of the underlying rows have changed. The snapshot for a given statement is identified by the system change number (SCN).

The version buffer is split between data structures that are in-memory and on-disk.

The in-memory data structures are hash tables that keep track of in-flight transaction. The hash tables store the LBIDs for each block that is being modified by a transaction. The in-memory hash tables start at 4 MB, and they grow as-needed. The size of the hash tables increases as the number of modified blocks increases.

An on-disk version buffer file is stored in each DB Root. By default, the on-disk version buffer file is 1 GB, but you can configure Enterprise ColumnStore to use a different file size. The relevant configuration options are:

For example, to configure Enterprise ColumnStore to use a larger on-disk version buffer file using the mcsSetConfig utility:

$ mcsSetConfig VersionBuffer VersionBufferFileSize 2GB

Extent Elimination

Using the Extent Map, ColumnStore can perform logical range partitioning and only retrieve the blocks needed to satisfy the query. This is done through Extent Elimination, the process of eliminating Extents from the results that don't meet the given join and filter conditions of the query, which reduces the overall I/O operations.

In Extent Elimination, ColumnStore scans the columns in join and filter conditions. It then extracts the logical horizontal partitioning information of each extent along with the minimum and maximum values for the column to further eliminate Extents. To eliminate an Extent when a column scan involves a filter, that filter is compared to the minimum and maximum values stored in each extent for the column. If the filter value is outside the Extents minimum and maximum value range, ColumnStore eliminates the Extent.

This behavior is automatic and well suited for series, ordered, patterned and time-based data, where the data is loaded frequently and often referenced by time. Any column with clustered values is a good candidate for Extent Elimination.

_{This page is licensed: CC BY-SA / Gnu FDL}

When using ColumnStore, MariaDB Server creates a series of system databases used for operational purposes.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

MariaDB Enterprise ColumnStore minimizes locking for analytical workloads, bulk data loads, and online schema changes.

Lockless Reads

MariaDB Enterprise ColumnStore supports lockless reads.

Locking for Writes

MariaDB Enterprise ColumnStore requires a table lock for write operations.

Locking for Data Loading

MariaDB Enterprise ColumnStore requires a write metadata lock (MDL) on the table when a bulk data load is performed with cpimport.

When a bulk data load is running:

• Read queries will not be blocked.

• Write queries and concurrent bulk data loads on the same table will be blocked until the bulk data load operation is complete, and the write metadata lock on the table has been released.

• The write metadata lock (MDL) can be monitored with the METADATA_LOCK_INFO plugin.

For additional information, see "MariaDB Enterprise ColumnStore Data Loading".

Online Schema Changes

MariaDB Enterprise ColumnStore supports online schema changes, so that supported DDL operations can be performed without blocking reads. The supported DDL operations only require a write metadata lock (MDL) on the target table.

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

Overview

MariaDB Enterprise ColumnStore is a smart storage engine designed to efficiently execute analytical queries using distributed query execution and massively parallel processing (MPP) techniques.

Scalability

MariaDB Enterprise ColumnStore is designed to achieve vertical and horizontal scalability for production analytics using distributed query execution and massively parallel processing (MPP) techniques.

Enterprise ColumnStore evaluates each query as a sequence of job steps using sophisticated techniques to get the best performance for complex analytical queries. Some types of job steps are designed to scale with the system's resources. As you increase the number of ColumnStore nodes or the number of cores on each node, Enterprise ColumnStore can use those resources to more efficiently execute those types of job steps.

Enterprise ColumnStore stores each column on disk in extents. The storage format is designed to maintain scalability, even as the table grows. If an operation does not read parts of a large table, I/O costs are reduced. Enterprise ColumnStore uses a technique called extent elimination that compares the maximum and minimum values in the extent map to the query's conditions, and it avoids scanning extents that don't satisfy the conditions.

Enterprise ColumnStore provides exceptional scalability for analytical queries. Enterprise ColumnStore's design supports targeted scale-out to address increased workload requirements, whether it is a larger query load or increased storage and query processing capacity.

Horizontal Scalability

MariaDB Enterprise ColumnStore provides horizontal scalability by executing some types of job steps in a distributed manner using multiple nodes.

When Enterprise ColumnStore is evaluating a job step, the ExeMgr process or facility on the initiator/aggregator node requests the PrimProc process on each node to perform the job step on different extents in parallel. As more nodes are added, Enterprise ColumnStore can perform more work in parallel.

Enterprise ColumnStore also uses massively parallel processing (MPP) techniques to speed up some types of job steps. For some types of aggregation operations, each node can perform an initial local aggregation, and then the initiator/aggregator node only needs to combine the local results and perform a final aggregation. This technique can be very efficient for some types of aggregation operations, such as for queries that use the AVG(), COUNT(), or SUM() aggregate functions.

Vertical Scalability

MariaDB Enterprise ColumnStore provides vertical scalability by executing some types of job steps in a multi-threaded manner using a thread pool.

When the PrimProc process on a node receives work, it executes the job step on an extent in a multi-threaded manner using a thread pool. Each thread operates on a different block within the extent. As more CPUs are added, Enterprise ColumnStore can work on more blocks in parallel.

Extent Elimination

MariaDB Enterprise ColumnStore uses extent elimination to scale query evaluation as table size increases.

Most databases are row-based databases that use manually-created indexes to achieve high performance on large tables. This works well for transactional workloads. However, analytical queries tend to have very low selectivity, so traditional indexes are not typically effective for analytical queries.

Enterprise ColumnStore uses extent elimination to achieve high performance, without requiring manually created indexes. Enterprise ColumnStore automatically partitions all data into extents. Enterprise ColumnStore stores the minimum and maximum values for each extent in the extent map. Enterprise ColumnStore uses the minimum and maximum values in the extent map to perform extent elimination.

When Enterprise ColumnStore performs extent elimination, it compares the query's join conditions and filter conditions (i.e., WHERE clause) to the minimum and maximum values for each extent in the extent map. If the extent's minimum and maximum values fall outside the bounds of the query's conditions, Enterprise ColumnStore skips that extent for the query.

Extent elimination is automatically performed for every query. It can significantly decrease I/O for columns with clustered values. For example, extent elimination works effectively for series, ordered, patterned, and time-based data.

Custom Select Handler

The ColumnStore storage engine plugin implements a custom select handler to fully take advantage of Enterprise ColumnStore's capabilities.

All storage engines interact with ES using an internal handler API, which is highly extensible. Storage engines can implement different features by implementing different methods within the handler API.

For select statements, the handler API transforms each query into a SELECT_LEX object, which is provided to the select handler.

The generic select handler is not optimal for Enterprise ColumnStore, because:

• Enterprise ColumnStore selects data by column, but the generic selects handler selects data by row

• Enterprise ColumnStore supports parallel query evaluation, but the generic select handler does not

• Enterprise ColumnStore supports distributed aggregations, but the generic select handler does not

• Enterprise ColumnStore supports distributed functions, but the generic select handler does not

• Enterprise ColumnStore supports extent elimination, but the generic select handler does not

• Enterprise ColumnStore has its own query planner, but the generic select handler cannot use it

Smart Storage Engine

The ColumnStore storage engine plugin is known as a smart storage engine, because it implements a custom select handler. MariaDB Enterprise ColumnStore integrates with MariaDB Enterprise Server using the ColumnStore storage engine plugin. The ColumnStore storage engine plugin enables MariaDB Enterprise Server to interact with ColumnStore tables.

If a storage engine implements a custom select handler, it is known as a smart storage engine.

As a smart storage engine, the ColumnStore storage engine plugin tightly integrates Enterprise ColumnStore with ES, but it has enough independence to efficiently execute analytical queries using a completely unique approach.

Configure the Select Handler

The ColumnStore storage engine can use either the custom select handler or the generic select handler. The select handler can be configured using the columnstore_select_handler system variable:

Joins

MariaDB Enterprise ColumnStore performs join operations using hash joins.

By default, hash joins are performed in memory.

Configure In-Memory Joins

MariaDB Enterprise ColumnStore can be configured to allocate more memory for hash joins.

The relevant configuration options are:

For example, to configure Enterprise ColumnStore to use more memory for hash joins using the mcsSetConfig utility:

$ mcsSetConfig HashJoin PmMaxMemorySmallSide 2G
$ mcsSetConfig HashJoin TotalUmMemory '40%'

Configure Disk-Based Joins

MariaDB Enterprise ColumnStore can be configured to perform disk-based joins.

The relevant configuration options are:

For example, to configure Enterprise ColumnStore to perform disk-based joins using the mcsSetConfig utility:

$ mcsSetConfig HashJoin AllowDiskBasedJoin Y
$ mcsSetConfig HashJoin TempFileCompression Y
$ mcsSetConfig SystemConfig SystemTempFileDir /mariadb/tmp

Aggregations

MariaDB Enterprise ColumnStore performs aggregation operations on all nodes in a distributed manner, and then all nodes send their results to a single node, which combines the results and performs the final aggregation.

By default, aggregation operations are performed in memory.

Configure Disk-Based Aggregations

In Enterprise ColumnStore 5.6.1 and later, disk-based aggregations can be configured.

The relevant configuration options are:

For example, to configure Enterprise ColumnStore to perform disk-based aggregations using the mcsSetConfig utility:

$ mcsSetConfig RowAggregation AllowDiskBasedAggregation Y
$ mcsSetConfig RowAggregation Compression SNAPPY
$ mcsSetConfig SystemConfig SystemTempFileDir /mariadb/tmp

Query Planning

The ColumnStore storage engine plugin is a smart storage engine, so MariaDB Enterprise ColumnStore to plan its own queries using the custom select handler.

MariaDB Enterprise ColumnStore's query planning is divided into two steps:

• ES provides the query's SELECT_LEX object to the custom select handler. The custom select handler builds a ColumnStore Execution Plan (CSEP).

• The custom select handler provides the CSEP to the ExeMgr process or facility on the same node. ExeMgr performs extent elimination and creates a job list.

ExeMgr Process/Facility

The ColumnStore storage engine provides the CSEP to the ExeMgr process or facility on the same node, which will act as the initiator/aggregator node for the query.

Starting with MariaDB Enterprise ColumnStore 22.08, the ExeMgr facility has been integrated into the PrimProc process, so it is no longer a separate process.

ExeMgr performs multiple tasks:

• Performs extent elimination.

• Views the optimizer statistics.

• Transforms the CSEP to a job list, which consists of job steps.

• Assigns distributed job steps to the PrimProc process on each node.

• Evaluates non-distributed job steps itself.

• Provides final query results to ES.

Query Evaluation Process

When Enterprise ColumnStore executes a query, it goes through the following process:

1. The client or application sends the query to MariaDB MaxScale's listener port.

2. The query is processed by the Read/Write Split Router (readwritesplit) service associated with the listener.

3. The service routes the query to the ES TCP port on a ColumnStore node.

4. MariaDB Enterprise Server (ES) evaluates the query using the handler interface.

• The handler interface builds a SELECT_LEX object to represent the query.

• The handler interface provides the SELECT_LEX object to the ColumnStore storage engine's select handler.

• The select handler transforms the SELECT_LEX object into a ColumnStore Execution Plan (CSEP).

• The select handler provides the CSEP to the ExeMgr facility on the same node, which will act as the initiator/aggregator node for the query.

1. ExeMgr transforms the CSEP into a job list, which consists of job steps.

2. ExeMgr evaluates each job step sequentially.

• If it is a non-distributed job step, ExeMgr evaluates the job step itself.

• If it is a distributed job step, ExeMgr provides the job step to the PrimProc process on each node. The PrimProc process on each node evaluates the job step in a multi-threaded manner using a thread pool. After the PrimProc process on each node evaluates its job step, the results are returned to ExeMgr on the initiator/aggregator node as a Row Group.

1. After all job steps are evaluated, ExeMgr returns the results to ES.

2. ES returns the results to MaxScale.

3. MaxScale returns the results to the client or application.

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

Overview

MariaDB Enterprise ColumnStore integrates with MariaDB Enterprise Server using the ColumnStore storage engine plugin. The ColumnStore storage engine plugin enables MariaDB Enterprise Server to interact with ColumnStore tables.

For deployment instructions and available documentation, see "MariaDB Enterprise ColumnStore".

Feature Summary The ColumnStore storage engine has the following features:

Examples

Creating a ColumnStore Table

To create a ColumnStore table, use the CREATE TABLE statement with the ENGINE=ColumnStore option:

CREATE DATABASE columnstore_db;

CREATE TABLE columnstore_db.analytics_test (
   id INT,
   str VARCHAR(50)
) ENGINE = ColumnStore;

Multi-Node Configuration

To deploy a multi-node Enterprise ColumnStore deployment, a configuration similar to below is required:

[mariadb]
log_error                              = mariadbd.err
character_set_server                   = utf8
collation_server                       = utf8_general_ci
log_bin                                = mariadb-bin
log_bin_index                          = mariadb-bin.index
relay_log                              = mariadb-relay
relay_log_index                        = mariadb-relay.index
log_slave_updates                      = ON
gtid_strict_mode                       = ON

# This must be unique on each cluster node
server_id                              = 1

Configure the Mandatory Utility User Account

To configure the mandatory utility user account, use the mcsSetConfig command:

$ sudo mcsSetConfig CrossEngineSupport Host 127.0.0.1
$ sudo mcsSetConfig CrossEngineSupport Port 3306
$ sudo mcsSetConfig CrossEngineSupport User cross_engine
$ sudo mcsSetConfig CrossEngineSupport Password cross_engine_passwd

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

The following table outlines the minimum recommended production server specifications which can be followed for both on premise and cloud deployments:

Per Server

Network

Details

These are minimum recommendations and in general the system will perform better with more hardware:

• More CPU cores and servers will improve query processing response time.

• More memory will allow the system to cache more data blocks in memory. We have users running system with anywhere from 64G RAM to 512 G RAM for UM and 32 to 64 G RAM for PM.

• Faster network will allow data to flow faster between UM and PM nodes.

• SSD's may be used, however the system is optimized towards block streaming which may perform well enough with HDD's for lower cost.

• Where it is an option, it is recommended to use bare metal servers for additional performance since ColumnStore will fully consume CPU cores and memory.

• In general it makes more sense to use a higher core count / higher memory server for single server or 2 server combined deployments.

• In a deployment with multiple UM nodes the system will round robin requests from the mysqld handling the query to any ExeMgr in the cluster for load balancing. A higher bandwidth network such as 10g or 40g will be of benefit for large result set queries.

AWS instance sizes

For AWS our own internal testing generally uses m4.4xlarge instance types as a cost effective middle ground. The R4.8xlarge has also been tested and performs about twice as fast for about twice the price.

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Introduction "Introduction"

2. Managing partitions by partition number "Managing partitions by partition number"

3. Displaying partition information "Displaying partition information"

4. Disabling partitions "Disabling partitions"

5. Enabling partitions "Enabling partitions"

6. Dropping partitions "Dropping partitions"

7. Managing partitions by column value "Managing partitions by column value"

8. Displaying partition information "Displaying partition information"

9. Disabling partitions "Disabling partitions"

10. Enabling partitions "Enabling partitions"

11. Dropping partitions "Dropping partitions"

12. Dropping data not wholly within one partition "Dropping data not wholly within one partition"

Introduction

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

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

1. Create 4 extents in 4 files

2. When these are filled up (after 32M rows), create 4 more extents in the 4 files created in step 1.

3. When these are filled up (after 64M rows), create a new partition.

Managing partitions by partition number

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

select calShowPartitions('orders','orderdate');
+-----------------------------------------+
| calShowPartitions('orders','orderdate') |
+-----------------------------------------+
| Part# Min        Max        Status
  0.0.1 1992-01-01 1998-08-02 Enabled
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+-----------------------------------------+

1 row in set (0.05 sec)

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:

select calDisablePartitions('orders','0.0.1');
+----------------------------------------+
| calDisablePartitions('orders','0.0.1') |
+----------------------------------------+
| Partitions are disabled successfully.  |
+----------------------------------------+
1 row in set (0.28 sec)

The result showing the first partition has been disabled:

select calShowPartitions('orders','orderdate');
+-----------------------------------------+
| calShowPartitions('orders','orderdate') |
+-----------------------------------------+
| Part# Min        Max        Status
  0.0.1 1992-01-01 1998-08-02 Disabled
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+-----------------------------------------+
1 row in set (0.05 sec)

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:

select calEnablePartitions('orders', '0.0.1');
+----------------------------------------+
| calEnablePartitions('orders', '0.0.1') |
+----------------------------------------+
| Partitions are enabled successfully.   |
+----------------------------------------+
1 row in set (0.28 sec)

The result showing the first partition has been enabled:

select calShowPartitions('orders','orderdate');
+-----------------------------------------+
| calShowPartitions('orders','orderdate') |
+-----------------------------------------+
| Part# Min        Max        Status
  0.0.1 1992-01-01 1998-08-02 Enabled
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+-----------------------------------------+
1 rows in set (0.05 sec)

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:

select calDropPartitions('orders', '0.0.1');
+--------------------------------------+
| calDropPartitions('orders', '0.0.1') |
+--------------------------------------+
| Partitions are enabled successfully  |
+--------------------------------------+
1 row in set (0.28 sec)

The result showing the first partition has been dropped:

select calShowPartitions('orders','orderdate');
+-----------------------------------------+
| calShowPartitions('orders','orderdate') |
+-----------------------------------------+
| Part# Min        Max        Status
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+-----------------------------------------+
1 row in set (0.05 sec)

Managing partitions by column value

Displaying partition 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 (INTEGER, DECIMAL, DATE, DATETIME, CHAR up to 8 bytes and VARCHAR 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:

select calShowPartitionsByValue('orders','orderdate', '1992-01-01', '2010-07-24');
+----------------------------------------------------------------------------+
| calShowPartitionsbyvalue('orders','orderdate', '1992-01-02', '2010-07-24') |
+----------------------------------------------------------------------------+
| Part# Min        Max        Status
  0.0.1 1992-01-01 1998-08-02 Enabled
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+----------------------------------------------------------------------------+
1 row in set (0.05 sec)

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:

select calDisablePartitionsByValue('orders','orderdate', '1992-01-01', '1998-08-02');
+---------------------------------------------------------------------------------+
| caldisablepartitionsbyvalue ('orders', 'o_orderdate','1992-01-01','1998-08-02') |
+---------------------------------------------------------------------------------+
| Partitions are disabled successfully                                            |
+---------------------------------------------------------------------------------+
1 row in set (0.28 sec)

The result showing the first partition has been disabled:

select calShowPartitionsByValue('orders','orderdate', '1992-01-01', '2010-07-24');
+----------------------------------------------------------------------------+
| calShowPartitionsbyvalue('orders','orderdate', '1992-01-02','2010-07-24’ ) |
+----------------------------------------------------------------------------+
| Part# Min        Max        Status
  0.0.1 1992-01-01 1998-08-02 Disabled
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+----------------------------------------------------------------------------+
1 row in set (0.05 sec)

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:

select calEnablePartitionsByValue('orders','orderdate', '1992-01-01', '1998-08-02');
+--------------------------------------------------------------------------------+
| calenablepartitionsbyvalue ('orders', 'o_orderdate','1992-01-01','1998-08-02') |
+--------------------------------------------------------------------------------+
| Partitions are enabled successfully                                            |
+--------------------------------------------------------------------------------+
1 row in set (0.28 sec)

The result showing the first partition has been enabled:

select calShowPartitionsByValue('orders','orderdate', '1992-01-01', '2010-07-24');
+----------------------------------------------------------------------------+
| calShowPartitionsbyvalue('orders','orderdate', '1992-01-02','2010-07-24' ) |
+----------------------------------------------------------------------------+
| Part# Min        Max        Status
  0.0.1 1992-01-01 1998-08-02 Enabled
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+----------------------------------------------------------------------------+
1 rows in set (0.05 sec)

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:

select calDropPartitionsByValue('orders','orderdate', '1992-01-01', '1998-08-02');
+------------------------------------------------------------------------------+
| caldroppartitionsbyvalue ('orders', 'o_orderdate','1992-01-01','1998-08-02') |
+------------------------------------------------------------------------------+
| Partitions are enabled successfully.                                         |
+------------------------------------------------------------------------------+
1 row in set (0.28 sec)

The result showing the first partition has been dropped:

select calShowPartitionsByValue('orders','orderdate', '1992-01-01', '2010-07-24');
+----------------------------------------------------------------------------+
| calShowPartitionsbyvalue('orders','orderdate', '1992-01-02','2010-07-24' ) |
+----------------------------------------------------------------------------+
| Part# Min        Max        Status
  0.1.2 1998-08-03 2004-05-15 Enabled
  0.2.3 2004-05-16 2010-07-24 Enabled |
+----------------------------------------------------------------------------+
1 row in set (0.05 sec)

Dropping data not wholly within one partition

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

• For semi-ordered data, there may be overlap between min and max values between partitions.

• As in the example above, the partition ranged from 1992-01-01 to 1998-08-02. Potentially 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 so are still relatively fast.

DELETE FROM orders WHERE orderdate <= '1998-12-31';

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Introduction "Introduction"

2. Installation "Installation"

3. Enabling the audit plugin "Enabling the audit plugin"

Introduction

MariaDB server includes an optional Audit Plugin that enables logging and tracking of all user access and statements. This is included and can be enabled for ColumnStore

Installation

To enable the audit plugin for the currently running instance (but not across restarts) run the following as mcsmysql with the default root account:

INSTALL PLUGIN server_audit 
SONAME 'server_audit.so';

To have this persist across restarts, edit the ColumnStore my.cnf file (example shown for root install):

$ vi /usr/local/mariadb/columnstore/mysql/my.cnf
[mysqld]
... 
plugin_load=server_audit=server_audit.so

For more details, see the audit plugin installation guide

Enabling the audit plugin

To enable audit logging the following global variable must be set to ON:

SET GLOBAL server_audit_logging=ON;

To ensure this persists across restarts, edit the ColumnStore my.cnf file (example shown for root install):

$ vi /usr/local/mariadb/columnstore/mysql/my.cnf
[server]
... 
server_audit_logging=ON

This will enable logging to the file /usr/local/mariadb/columnstore/mysql/db/server_audit.log.

For example:

20170914 17:31:24,centos,root,localhost,11,114,QUERY,loans,'SELECT grade, AVG(loan_amnt) avg, FROM loanstats GROUP BY grade ORDER BY grade',0

To have the log entries written to syslog the global variable server_audit_output_type should be changed from 'file' to 'syslog'. In this case the syslog_info entry contains the ColumnStore server instance name, for example:

Sep 14 17:46:51 centos mysql-server_auditing: columnstore-1 centos,root,localhost,11,117,QUERY,loans,'SELECT grade, AVG(loan_amnt) avg,FROM loanstats GROUP BY grade ORDER BY grade',0

For additional configuration and customization options see the Audit Plugin documentation.

_{This page is licensed: CC BY-SA / Gnu FDL}

In case where an entry in the MariaDB ColumnStore's configuration needs to be updated and distributed, this can be done from the command line from Performance Module #1. All changes made to MariaDB ColumnStore's configuration file need to be applied on PM1.

NOTE: 'mcsadmin distributeconfigfile' only needs to be run if the system is Active. If the system is down, then just make the change, and when the system is started, the update will get distributed.

Here is an example

/usr/local/mariadb/columnstore/bin/configxml.sh setconfig SystemConfig SystemName mcs-1
mcsadmin distributeconfigfile

In the cases where MariaDB ColumnStore's configuration files get out of sync with the PM1 copy, run the following to get MariaDB ColumnStore's configuration file redistribute to the other nodes.

To do this run the following on PM1:

mscadmin distributeconfigfile

_{This page is licensed: CC BY-SA / Gnu FDL}

Introduction

When new PM nodes are added to a running instance it may be desirable to redistribute the data in current nodes across all of the nodes. This is not strictly required as ongoing data ingestion will prioritize the new empty nodes for data loading to rebalance the system.

An important point is that the operation works at Partition granularity, so a minimal data set is 64M rows in a table for this to run.

Usage

A command redistributeData is available in the admin console to initiate a data distribution:

mcsadmin> redistributeData start
redistributedata   Tue Dec 13 04:42:31 2016
redistributeData START
Source dbroots: 1 2
Destination dbroots: 1 2

WriteEngineServer returned status 1: Cleared.
WriteEngineServer returned status 2: Redistribute is started.

The command has 3 possible options:

• Start: start a new redistribution to redistribute data equally amongst the current set of DBRoots in the system.

• Stop: abort a redistribution, leaving the system in a usable state.

• Status: return status information on an active redistribution.

The start command can take an option of Remove. The Start Remove option is used to remove all data from the enumerated list of dbroots and redistribute the data to the remaining dbroots. This should be done before taking a dbroot out of service in order to preserve the data on that dbroot. The dbroot list is a space-delimited list of integers representing the dbroots to be emptied.

As mentioned above, the operation works at partition granularity, which means that a minimal move is 64 million rows. Any table smaller than that will not be redistributed, and there may be as much as one full Partition difference in the resulting balance. The redistribute logic does not currently consolidate individually deleted records.

Redistribute can take a long time. During this time, it is required that all data manipulation, including bulk inserts, are suspended. SuspendDatabaseWrites must be called before RedistributeData and ResumeDatabaseWrites should be called when the redistribution is complete.

If redistributeData stop is called, all processing stops where it's at, but in a usable state. redistributeData status can be used to see how much has been done. A further redistributeData start will start overusing the new state of the system. This may lead to a less optimal distribution, so stop-start sequences aren't recommended.

While the system is working, redistributeData status can be called to see what's happening. a -r option can be used on the status command line to repeat the call and act as a monitor.

To see how much data resides on any given DBRoot for a table, you can use a query like:

SELECT COUNT(*) FROM <table> WHERE idbdbroot(<any column>) = <dbrootnum>;

_{This page is licensed: CC BY-SA / Gnu FDL}

1. Introduction "Introduction"

2. System monitoring configuration "System monitoring configuration"

3. Module heartbeats "Module heartbeats"

4. Disk threshold "Disk threshold"

5. Memory utilization "Memory utilization"

6. Viewing storage configuration "Viewing storage configuration"

7. Module monitoring configuration "Module monitoring configuration"

8. Alarm trigger count threshold "Alarm trigger count threshold"

9. Clearing alarms "Clearing alarms"

10. Automated restart based on excessive swapping "Automated restart based on excessive swapping"

11. Logging level management "Logging level management"

Introduction

ColumnStore is designed to be somewhat self managing and healing. The following 2 processes help achieve this:

• ProcMon runs on each node and is responsible for ensuring that the other required ColumnStore processes are started and automatically restarted as appropriate on that server. This in turn is started and monitored by the run.sh shell script which ensures it is restarted should it be killed. The run.sh script is invoked and automatically started by the columnstore systemd service at bootup time. This can also be utilized to restart the service on an individual node though generally it is preferred to use the mcsadmin stop, shutdown, and start commands from the PM1 node.

• ProcMgr runs on each PM node with only one taking an active role at a time, the others remaining in warm standby mode. This process manager is responsible for overall system health, resource monitoring, and PM node failover management.

To provide additional monitoring guarantees, an external monitoring tool should monitor the health of these 3 processes and potentially all. If the run.sh process fails then the system is at potential risk of not being able to self heal.

System monitoring configuration

A number of system configuration variables exist to allow fine tuning of the system monitoring capabilities. In general, the default values will work relatively well for many cases.

The configuration parameters are maintained in the /usr/local/mariadb/columnstore/etc/Columnstore.xml file. In a multiple server deployment, these should only be edited on the PM1 server as this will be automatically replicated to other servers by the system. A system restart will be required for the configuration change to take effect.

Convenience utility programs getConfig and setConfig are available to safely update the Columnstore.xml file without requiring familiarity with editing XML files. The -h argument will display usage information. The section value will be SystemConfig for all settings in this document.

For example:

# ./setConfig SystemConfig ModuleHeartbeatPeriod 5
# ./getConfig SystemConfig ModuleHeartbeatPeriod
5

Module heartbeats

Heartbeat monitoring occurs between modules (both UM and PM) to determine the module is up and functioning. The module heartbeat settings are the same for all modules.

1. ModuleHeartbeatPeriod refers to how often the heartbeat test is performed. For example, if you set the period to 5, then the heartbeat test is performed every 5 seconds. The initial default value is 1. To disable heartbeat monitoring set the value to -1.

2. ModuleHeartbeatCount refers to how many failures in a row must take place before a fault is processed. The initial default value is 3.

Disk threshold

Thresholds can be set to trigger a local alert when file system usage crosses a specified percentage of a file system on a server. Critical, Major or Minor thresholds can be set for the disk usage for each server. However, it is recommended to use an external system monitoring tool configured to monitor for free disk space to perform proactive external alerting or paging. Actual columnstore data is stored within the data directories of the installation and MariaDB DB files are stored under the mysql/db directory.

1. ExternalMinorThreshold - Percentage threshold for when a minor local alarm is triggered. Default value is 70.

2. ExternalMajorThreshold - Percentage threshold for when a minor local alarm is triggered. Default value is 80.

3. ExternalCriticalThreshold - Percentage threshold for when a minor local alarm is triggered. Default value is 90.

The value is a numeric percentage value between 0 and 100. To disable a particular threshold, use value 0. To disable a threshold alarm, set it to 0.

Memory utilization

A couple of mcsadmin commands provide convenience functions for monitoring memory utilization across nodes. getSystemMemory returns server level memory statistics and getSystemMemoryUsers shows the top 5 processes by server.

The following examples are for a 2-server combined setup:

mcsadmin> getSystemMemory
getsystemmemory   Tue Nov 29 11:14:21 2016

System Memory Usage per Module (in K bytes)

Module  Mem Total  Mem Used  Cache    Mem Usage %  Swap Total  Swap Used  Swap Usage % 
------  ---------  --------  -------  -----------  ----------  ---------  ------------ 
pm1     7979488    1014772   6438432      12       3145724     0               0
pm2     3850724    632712    1134324      16       3145724     0               0

mcsadmin> getSystemMemoryUsers
getsystemmemoryusers   Tue Nov 29 11:41:10 2016

System Process Top Memory Users per Module

Module 'pm1' Top Memory Users (in bytes)

Process             Memory Used  Memory Usage %
-----------------   -----------  --------------
mysqld              19621              3
PrimProc            18990              3
gnome-shell         10192              2
systemd-journald    4236               1
DDLProc             3004               1

Module 'pm2' Top Memory Users (in bytes)

Process             Memory Used  Memory Usage %
-----------------   -----------  --------------
mysqld              19046              5
PrimProc            18891              5
ProcMon             2343               1
workernode          1806               1
WriteEngineServ     1507               1

Viewing Storage Configuration

To view the storage configuration, use the getStorageConfig command in mcsadmin, or simply use mcsadmin getStorageConfig from the operating system prompt. This will provide information on DBRoots and which PM they are assigned to, if any.

Example:

# mcsadmin getstorageconfig Wed Mar 28 10:40:34 2016

System Storage Configuration

Storage Type = internal
System DBRoot count = 6
DBRoot IDs assigned to 'pm1' = 1
DBRoot IDs assigned to 'pm2' = 2
DBRoot IDs assigned to 'pm3' = 3
DBRoot IDs assigned to 'pm4' = 4
DBRoot IDs assigned to 'pm5' = 5
DBRoot IDs assigned to 'pm6' = 6

Module monitoring configuration

An internal alarm system is used to keep track of internal notable events as a convenience or reference point. It is recommended to use a dedicated system monitoring tool for more proactive alerting of critical CPU, memory, or disk utilization issues for each of the servers.

Alarms are logged to the /var/log/mariadb/columnstore/alarm.log file and a summary is displayed in mcsadmin. The getActiveAlarms command in mcsadmin can be used to retrieve current alarm conditions.

For each module (PM and UM), the following resource monitoring parameters can be configured:

Alarm trigger count threshold

For an alarm, a threshold can be set for how many times the alarm can be triggered in 30 minutes. The default threshold is 100.

setAlarmConfig (alarmID#) Threshold n

(where n= maximum number of times an alarm can be triggered in 30 minutes),

Example to change Alarm ID 22's threshold to 50:

# mcsadmin setAlarmConfig 22 Threshold 50

Clearing alarms

The resetAlarm command is used to clear and acknowledge the issue is resolved. The resetAlarm command can be invoked with the argument ALL to clear all outstanding local alarms.

Automated restart based on excessive swapping

ColumnStore by default has behavior that will restart a server should swap space utilization exceed the configured module swap major threshold (default is 80%). At this point, the system will likely be near unusable and so this is an attempt to recover from very large queries or data loads. The behavior of this is configured by the SystemConfig section configuration variable SwapAction which contains the oam command to be run if the threshold is exceeded. The default value is restartSystem but it can be set to 'none' to disable this behavior. The fact that this has happened can be determined by the following log entry:

Nov 01 11:23:13 [ServerMonitor] 13.306324 |0|0|0| C 09 CAL0000: Swap Space usage over Major threashold, perform OAM command restartSystem

Logging level management

There are five levels of logging in MariaDB ColumnStore.

• Critical

• Error

• Warning

• Info

• Debug

Application log files are written to /var/log/mariadb/columnstore on each server and log rotation / archiving is configured to manage these automatically.

To get details about current logging configuration:

# mcsadmin getlogconfig
getlogconfig   Wed Oct 19 06:58:47 2016

MariaDB Columnstore System Log Configuration Data

System Logging Configuration File being used: /etc/rsyslog.d/49-columnstore.conf

Module    Configured Log Levels
------    ---------------------------------------
pm1       Critical Error Warning Info

The system logging configuration file referenced is a standard syslog configuration file and may be edited to enable and or disable specific levels, for example to disable debug logging and to only log at the specific level in each file:

# cat /etc/rsyslog.d/49-columnstore.conf
# MariaDb Columnstore Database Platform Logging
local1.=crit -/var/log/mariadb/columnstore/crit.log
local1.=err -/var/log/mariadb/columnstore/err.log
local1.=warning -/var/log/mariadb/columnstore/warning.log
local1.=info -/var/log/mariadb/columnstore/info.log

After making changes to this restart the syslog process, e.g:

# systemctl restart rsyslog

Log rotation and archiving are also configured by the installer and the settings for this may be found and managed similarly in the file /etc/logrotate.d/columnstore. If the current log files are manually deleted restart the syslog process to resume logging.

_{This page is licensed: CC BY-SA / Gnu FDL}

To add a new node to Enterprise ColumnStore, perform the following procedure.

Deploy Enterprise ColumnStore

Before you can add a node to Enterprise ColumnStore, confirm that the Enterprise ColumnStore software has been deployed on the node in the desired topology.

For additional information, see "Topologies".

Backup MariaDB Data Directory on the Primary Server

Before the new node can be added, its MariaDB data directory must be consistent with the Primary Server. To ensure that it is consistent, take a backup of the Primary Server:

The instructions below show how to perform a backup using MariaDB Backup.

1. On the Primary Server, take a full backup:

   Copy

   sudo mariadb-backup --backup \
         --user=mariabackup_user \
         --password=mariabackup_passwd \
         --target-dir=/data/backup/replica_backup

   Confirm successful completion of the backup operation.

2. On the Primary Server, prepare the backup:

   Copy

   sudo mariadb-backup --prepare \
         --target-dir=/data/backup/replica_backup

   Confirm successful completion of the prepare operation.

Restore the Backup on the New Node

To make the new node consistent with the Primary Server, restore the new backup on the new node:

1. On the Primary Server, copy the backup to the new node:

   Copy

   sudo rsync -av /data/backup/replica_backup 192.0.2.3:/data/backup/

2. On the new node, restore the backup using MariaDB Backup.

   Copy

   sudo mariadb-backup --copy-back \
      --target-dir=/data/backup/replica_backup

3. On the new node, fix the file permissions of the restored backup:

   Copy

   sudo chown -R mysql:mysql /var/lib/mysql

Start the Enterprise ColumnStore Services

The Enterprise Server. Enterprise ColumnStore, and CMAPI services can be started using the systemctl command. In case the services were started during the installation process, use the restart command.

Perform the following procedure on the new node:

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

   Copy

   sudo systemctl restart mariadb
   sudo systemctl enable mariadb

2. Start and disable the MariaDB Enterprise ColumnStore service, so that it does not start automatically upon reboot:

   Copy

   sudo systemctl restart mariadb-columnstore
   sudo systemctl disable mariadb-columnstore

   Note

   The Enterprise ColumnStore service should not be enabled in a multi-node deployment. The Enterprise ColumnStore service will be started as-needed by the CMAPI service, so it does not require starting automatically upon reboot.

3. Start and enable the CMAPI service, so that it starts automatically upon reboot:

   Copy

   sudo systemctl restart mariadb-columnstore-cmapi
   sudo systemctl enable mariadb-columnstore-cmapi

Configure MariaDB Replication

MariaDB Enterprise ColumnStore requires MariaDB Replication, which must be configured.

1. Get the GTID position that corresponds to the restored backup.

   If the backup was taken with MariaDB Backup, this position will be located in xtrabackup_binlog_info:

   Copy

   cat xtrabackup_binlog_info
   mariadb-bin.000096 568 0-1-2001,1-2-5139

   The GTID position from the above output is 0-1-2001,1-2-5139.

2. Connect to the Replica Server using MariaDB Client using the root@localhost user account:

   Copy

   sudo mariadb

3. Set the gtid_slave_pos system variable to the GTID position:

   Copy

   SET GLOBAL gtid_slave_pos='0-1-2001,1-2-5139';

4. Execute the CHANGE MASTER TO statement to configure the new node to connect to the Primary Server at this position:

   Copy

   CHANGE MASTER TO
      MASTER_USER = "repl",
      MASTER_HOST = "192.0.2.1",
      MASTER_PASSWORD = "repl_passwd",
      MASTER_USE_GTID=slave_pos;

   The above statement configures the Replica Server to connect to a Primary Server located at 192.0.2.1 using the repl user account.

5. Start replication using the START SLAVE command:

   Copy

   START SLAVE;

   The above statement configures the new node to connect to the Primary Server to retrieve new binary log events and replicate them into the local database.

Add the Node to Enterprise ColumnStore

The new node must be added to Enterprise ColumnStore using CMAPI:

• Add the node using the add-node endpoint path

• Use a supported REST client, such as curl

• Format the JSON output using jq for enhanced readability

• Authenticate using the configured API key

• Include the required headers

For example, if the primary node's host name is mcs1 and the new node's IP address is 192.0.2.3:

• In ES 10.5.10-7 and later:

  Copy

  curl -k -s -X PUT https://mcs1:8640/cmapi/0.4.0/cluster/node \
     --header 'Content-Type:application/json' \
     --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
     --data '{"timeout":20, "node": "192.0.2.3"}' \
     | jq .

• In ES 10.5.9-6 and earlier:

  Copy

  curl -k -s -X PUT https://mcs1:8640/cmapi/0.4.0/cluster/add-node \
     --header 'Content-Type:application/json' \
     --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
     --data '{"timeout":20, "node": "192.0.2.3"}' \
     | jq .

Example output:

{
  "timestamp": "2020-10-28 00:39:14.672142",
  "node_id": "192.0.2.3"
}

Check Enterprise ColumnStore Status

To confirm that the node was properly added, the status of Enterprise ColumnStore should be checked using CMAPI:

• Check the status using the status endpoint path

For example, if the primary node's host name is mcs1:

curl -k -s https://mcs1:8640/cmapi/0.4.0/cluster/status \
   --header 'Content-Type:application/json' \
   --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
   | jq .

Example output:

{
  "timestamp": "2020-12-15 00:40:34.353574",
  "192.0.2.1": {
    "timestamp": "2020-12-15 00:40:34.362374",
    "uptime": 11467,
    "dbrm_mode": "master",
    "cluster_mode": "readwrite",
    "dbroots": [
      "1"
    ],
    "module_id": 1,
    "services": [
      {
        "name": "workernode",
        "pid": 19202
      },
      {
        "name": "controllernode",
        "pid": 19232
      },
      {
        "name": "PrimProc",
        "pid": 19254
      },
      {
        "name": "ExeMgr",
        "pid": 19292
      },
      {
        "name": "WriteEngine",
        "pid": 19316
      },
      {
        "name": "DMLProc",
        "pid": 19332
      },
      {
        "name": "DDLProc",
        "pid": 19366
      }
    ]
  },
  "192.0.2.2": {
    "timestamp": "2020-12-15 00:40:34.428554",
    "uptime": 11437,
    "dbrm_mode": "slave",
    "cluster_mode": "readonly",
    "dbroots": [
      "2"
    ],
    "module_id": 2,
    "services": [
      {
        "name": "workernode",
        "pid": 17789
      },
      {
        "name": "PrimProc",
        "pid": 17813
      },
      {
        "name": "ExeMgr",
        "pid": 17854
      },
      {
        "name": "WriteEngine",
        "pid": 17877
      }
    ]
  },
  "192.0.2.3": {
    "timestamp": "2020-12-15 00:40:34.428554",
    "uptime": 11437,
    "dbrm_mode": "slave",
    "cluster_mode": "readonly",
    "dbroots": [
      "2"
    ],
    "module_id": 2,
    "services": [
      {
        "name": "workernode",
        "pid": 17789
      },
      {
        "name": "PrimProc",
        "pid": 17813
      },
      {
        "name": "ExeMgr",
        "pid": 17854
      },
      {
        "name": "WriteEngine",
        "pid": 17877
      }
    ]
  },
  "num_nodes": 3
}

Add a Server to MaxScale

A server object for the new node must also be added to MaxScale using MaxScale's REST API:

• Use MaxCtrl or another supported REST client

• Add the server object using the create server command

• As the first argument, provide a name for the server

• As the second argument, provide the IP address for the node

For example:

maxctrl create server \
   mcs3 \
   192.0.2.3

Check the Server in MaxScale

To confirm that the server object was properly added, the server objects should be checked using MaxScale's REST API:

• Show the server objects using the show servers command

For example:

maxctrl show servers

Link to Monitor in MaxScale

The server object for the new node must be linked to the monitor using MaxScale's REST API:

• Link a server object to the monitor using the link monitor command

• As the first argument, provide the name of the monitor

• As the second argument, provide the name of the server

maxctrl link monitor \
   mcs_monitor \
   mcs3

Check the Monitor in MaxScale

To confirm that the server object was properly linked to the monitor, the monitor should be checked using MaxScale's REST API:

• Show the monitors using the show monitors command

For example:

maxctrl show monitors

Link to Service in MaxScale

The server object for the new node must be linked to the service using MaxScale's REST API:

• Link the server object to the service using the link service command

• As the first argument, provide the name of the service

• As the second argument, provide the name of the server

maxctrl link service \
   mcs_service \
   mcs3

Check the Service in MaxScale

To confirm that the server object was properly linked to the service, the service should be checked using MaxScale's REST API:

• Show the services using the show services command

For example:

maxctrl show services

Check Replication Status with MaxScale

MaxScale is capable of checking the status of MariaDB Replication using MaxScale's REST API:

• List the servers using the list servers command

For example:

maxctrl list servers

If the new node is properly replicating, then the State column will show Slave, Running.

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

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

Perform Rejoin in MaxScale

The node can be configured to rejoin in MaxScale using MaxScale's REST API:

• Use MaxCtrl or another supported REST client

• Call a module command using the call command command

• As the first argument, provide the name for the module, which is mariadbmon

• As the second argument, provide the module command, which is rejoin

• As the third argument, provide the name of the monitor

• As the fourth argument, provide the name of the server

For example:

maxctrl call command \
   mariadbmon \
   rejoin \
   mcs_monitor \
   mcs3

Check Replication Status with MaxScale

MaxScale is capable of checking the status of MariaDB Replication using MaxScale's REST API:

• List the servers using the list servers command

For example:

maxctrl list servers

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

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

To remove a node from Enterprise ColumnStore, perform the following procedure.

Unlink from Service in MaxScale

The server object for the node must be unlinked from the service using MaxScale's REST API:

• Unlink the server object from the service using the unlink service command

• As the first argument, provide the name of the service

• As the second argument, provide the name of the server

maxctrl unlink service \
   mcs_service \
   mcs3

Check the Service in MaxScale

To confirm that the server object was properly unlinked from the service, the service should be checked using MaxScale's REST API:

• Show the services using the show services command

For example:

maxctrl show services

Unlink from Monitor in MaxScale

The server object for the node must be unlinked from the monitor using MaxScale's REST API:

• Unlink a server object from the monitor using the unlink monitor command

• As the first argument, provide the name of the monitor

• As the second argument, provide the name of the server

maxctrl unlink monitor \
   mcs_monitor \
   mcs3

Check the Monitor in MaxScale

To confirm that the server object was properly unlinked from the monitor, the monitor should be checked using MaxScale's REST API:

• Show the monitors using the show monitors command

For example:

maxctrl show monitors

Remove the Server from MaxScale

The server object for the node must also be removed from MaxScale using MaxScale's REST API:

• Use MaxCtrl or another supported REST client

• Remove the server object using the destroy server command

• As the first argument, provide the name for the server

For example:

maxctrl destroy server \
   mcs3

Check the Server in MaxScale

To confirm that the server object was properly removed, the server objects should be checked using MaxScale's REST API:

• Show the server objects using the show servers command

For example:

maxctrl show servers

Stop the Enterprise ColumnStore Services

The Enterprise Server. Enterprise ColumnStore, and CMAPI services can be stopped using the systemctl command.

Perform the following procedure on the node:

1. Stop the MariaDB Enterprise Server service:

   Copy

   sudo systemctl stop mariadb

2. Stop the MariaDB Enterprise ColumnStore service:

   Copy

   sudo systemctl stop mariadb-columnstore

3. Stop the CMAPI service:

   Copy

   sudo systemctl stop mariadb-columnstore-cmapi

Remove the Node from Enterprise ColumnStore

The node must be removed from Enterprise ColumnStore using CMAPI:

• Remove the node using the remove-node endpoint path

• Use a supported REST client, such as curl

• Format the JSON output using jq for enhanced readability

• Authenticate using the configured API key

• Include the required headers

For example, if the primary node's host name is mcs1 and the IP address for the node to remove is 192.0.2.3:

• In ES 10.5.10-7 and later:

  Copy

  curl -k -s -X DELETE https://mcs1:8640/cmapi/0.4.0/cluster/node \
     --header 'Content-Type:application/json' \
     --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
     --data '{"timeout":20, "node": "192.0.2.3"}' \
     | jq .

• In ES 10.5.9-6 and earlier:

  Copy

  curl -k -s -X PUT https://mcs1:8640/cmapi/0.4.0/cluster/remove-node \
     --header 'Content-Type:application/json' \
     --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
     --data '{"timeout":20, "node": "192.0.2.3"}' \
     | jq .

Example output:

{
  "timestamp": "2020-10-28 00:39:14.672142",
  "node_id": "192.0.2.3"
}

Check Enterprise ColumnStore Status

To confirm that the node was properly removed, the status of Enterprise ColumnStore should be checked using CMAPI:

• Check the status using the status endpoint path

For example, if the primary node's host name is mcs1:

curl -k -s https://mcs1:8640/cmapi/0.4.0/cluster/status \
   --header 'Content-Type:application/json' \
   --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
   | jq .

Example output:

{
  "timestamp": "2020-12-15 00:40:34.353574",
  "192.0.2.1": {
    "timestamp": "2020-12-15 00:40:34.362374",
    "uptime": 11467,
    "dbrm_mode": "master",
    "cluster_mode": "readwrite",
    "dbroots": [
      "1"
    ],
    "module_id": 1,
    "services": [
      {
        "name": "workernode",
        "pid": 19202
      },
      {
        "name": "controllernode",
        "pid": 19232
      },
      {
        "name": "PrimProc",
        "pid": 19254
      },
      {
        "name": "ExeMgr",
        "pid": 19292
      },
      {
        "name": "WriteEngine",
        "pid": 19316
      },
      {
        "name": "DMLProc",
        "pid": 19332
      },
      {
        "name": "DDLProc",
        "pid": 19366
      }
    ]
  },
  "192.0.2.2": {
    "timestamp": "2020-12-15 00:40:34.428554",
    "uptime": 11437,
    "dbrm_mode": "slave",
    "cluster_mode": "readonly",
    "dbroots": [
      "2"
    ],
    "module_id": 2,
    "services": [
      {
        "name": "workernode",
        "pid": 17789
      },
      {
        "name": "PrimProc",
        "pid": 17813
      },
      {
        "name": "ExeMgr",
        "pid": 17854
      },
      {
        "name": "WriteEngine",
        "pid": 17877
      }
    ]
  },
  "num_nodes": 2
}

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

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

Set the Server State in MaxScale

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

• Use MaxCtrl or another supported REST client

• Set the server object 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

For example:

maxctrl set server \
   mcs3 \
   maintenance

If the specified server is a primary server, then MaxScale will allow open transactions to complete before closing any connections.

If you would like MaxScale to immediately close all connections, the --force option can be provided as a third argument:

maxctrl set server \
   mcs3 \
   maintenance \
   --force

Confirm Maintenance Mode is Set with MaxScale

Confirm the state of the server object in MaxScale using MaxScale's REST API:

• List the servers using the list servers command

For example:

maxctrl list servers

If the node is properly in maintenance mode, then the State column will show Maintenance as one of the states.

Perform Maintenance

Now that the server is in maintenance mode in MaxScale, you can perform your maintenance.

While the server is in maintenance mode:

• MaxScale will not route traffic to the node

• MaxScale will not select the node to be primary during failover

• The node can be rebooted

• The node's services can be restarted

Clear the Server State in MaxScale

Maintenance mode for the server object for the node can be cleared in MaxScale using MaxScale's REST API:

• Use MaxCtrl or another supported REST client

• Clear the server object's state using the clear server command

• As the first argument, provide the name for the server

• As the second argument, provide maintenance as the state

For example:

maxctrl clear server \
   mcs3 \
   maintenance

Confirm Maintenance Mode is Cleared with MaxScale

Confirm the state of the server object in MaxScale using MaxScale's REST API:

• List the servers using the list servers command

For example:

maxctrl list servers

If the node is no longer in maintenance mode, then the State column will no longer show Maintenance as one of the states.

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

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

Perform Switchover in MaxScale

The primary node can be switched in MaxScale using MaxScale's REST API:

• Use MaxCtrl or another supported REST client

• Call a module command using the call command command

• As the first argument, provide the name for the module, which is mariadbmon

• As the second argument, provide the module command, which is switchover

• As the third argument, provide the name of the monitor

For example:

maxctrl call command \
   mariadbmon \
   switchover \
   mcs_monitor

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

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

maxctrl call command \
   mariadbmon \
   switchover \
   mcs_monitor \
   mcs2

Check Replication Status with MaxScale

MaxScale is capable of checking the status of MariaDB Replication using MaxScale's REST API:

• List the servers using the list servers command

For example:

maxctrl list servers

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

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

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

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

• DDL statements

• DML statements

• Bulk data loads

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

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.

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

• cleartablelock

• viewtablelock

Viewing Table Locks

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

To view all table locks:

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

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

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

Clearing Table Locks

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

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

cleartablelock 1

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

Overview

MariaDB Enterprise ColumnStore supports backup and restore.

System of Record

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:

• Enterprise ColumnStore's data and metadata

With S3: an S3 snapshot of the S3-compatible object storage and a file system snapshot or copy of the Storage Manager directory Without S3: a file system snapshot or copy of the DB Root directories.

• The MariaDB data directory from the primary node

To see the procedure to perform a full backup and restore, choose the storage type:

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

Overview

MariaDB Enterprise ColumnStore supports backup and restore. If Enterprise ColumnStore uses S3-compatible object storage for data and shared local storage for the Storage Manager directory, the S3 bucket, the Storage Manager directory, and the MariaDB data directory must be backed up separately.

Recovery Planning

MariaDB Enterprise ColumnStore supports multiple storage options.

This page discusses how to backup and restore Enterprise ColumnStore when it uses S3-compatible object storage for data and shared local storage (such as NFS) for the Storage Manager directory.

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 mariadb-backup.

• The S3 bucket must be backed up using the vendor's snapshot procedure.

• The Storage Manager directory must be backed up.

See the instructions below for more details.

Backup

Use the following process to take a backup:

1. Determine which node is the primary server using curl to send the status command to the CMAPI Server:

$ curl -k -s https://mcs1:8640/cmapi/0.4.0/mcs cluster status \
   --header 'Content-Type:application/json' \
   --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
   | jq .

The output will show "dbrm_mode": "master" for the primary server:

{
  "timestamp": "2020-12-15 00:40:34.353574",
  "192.0.2.1": {
    "timestamp": "2020-12-15 00:40:34.362374",
    "uptime": 11467,
    "dbrm_mode": "master",
    "cluster_mode": "readwrite",
    "dbroots": [
      "1"
    ],
    "module_id": 1,
    "services": [
      {
        "name": "workernode",
        "pid": 19202
      },
      {
        "name": "controllernode",
        "pid": 19232
      },
      {
        "name": "PrimProc",
        "pid": 19254
      },
      {
        "name": "ExeMgr",
        "pid": 19292
      },
      {
        "name": "WriteEngine",
        "pid": 19316
      },
      {
        "name": "DMLProc",
        "pid": 19332
      },
      {
        "name": "DDLProc",
        "pid": 19366
      }
    ]
  }

1. Connect to the primary server using MariaDB Client as a user account that has privileges to lock the database:

$ mariadb --host=192.0.2.1 \
   --user=root \
   --password

1. Lock the database with the FLUSH TABLES WITH READ LOCK statement:

FLUSH TABLES WITH READ LOCK;

Ensure that the client remains connected to the primary server, so that the lock is held for the remaining steps.

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

$ sudo mkdir -p /backups/columnstore/202101291600/
$ sudo rsync -av /var/lib/columnstore/storagemanager /backups/columnstore/202101291600/

1. Use MariaDB Backup to backup the MariaDB data directory:

$ sudo mkdir -p /backups/mariadb/202101291600/
$ sudo mariadb-backup --backup \
   --target-dir=/backups/mariadb/202101291600/ \
   --user=mariadb-backup \
   --password=mbu_passwd

1. Use MariaDB Backup to prepare the backup:

$ sudo mariadb-backup --prepare \
   --target-dir=/backups/mariadb/202101291600/

1. Create a snapshot of the S3-compatible storage. Consult the storage vendor's manual for details on how to do this.

2. Ensure that all previous operations are complete.

3. In the original client connection to the primary server, unlock the database with the UNLOCK TABLES statement:

UNLOCK TABLES;

Restore

Use the following process to restore a backup:

1. Deploy Enterprise ColumnStore, so that you can restore the backup to an empty deployment.

2. Ensure that all services are stopped on each node:

$ sudo systemctl stop mariadb-columnstore-cmapi
$ sudo systemctl stop mariadb-columnstore
$ sudo systemctl stop mariadb

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

$ sudo rsync -av /backups/columnstore/202101291600/storagemanager/ /var/lib/columnstore/storagemanager/
$ sudo chown -R mysql:mysql /var/lib/columnstore/storagemanager

1. Use MariaDB Backup to restore the backup of the MariaDB data directory:

$ sudo mariadb-backup --copy-back \
   --target-dir=/backups/mariadb/202101291600/
$ sudo chown -R mysql:mysql /var/lib/mysql

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

2. Update storagemanager.cnf to configure Enterprise ColumnStore to use the S3 bucket. By default, it is located at /etc/columnstore/storagemanager.cnf.

For example:

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

[Cache]
cache_size = your_local_cache_size
path = your_local_cache_path

• 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.

1. Start the services on each node:

$ sudo systemctl start mariadb
$ sudo systemctl start mariadb-columnstore-cmapi

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

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.

Recovery Planning

MariaDB Enterprise ColumnStore supports multiple storage options.

This page discusses how to backup and restore Enterprise ColumnStore when it uses shared local storage (such as NFS) for the DB Root directories.

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 shared local storage for the DB Root directories, the following items must be backed up:

• The MariaDB Data directory is backed up using MariaDB Backup

• The Storage Manager directory must be backed up

• Each DB Root directories must be backed up

See the instructions below for more details.

Backup

Use the following process to take a backup:

1. Determine which node is the primary server using curl to send the status command to the CMAPI Server:

$ curl -k -s https://mcs1:8640/cmapi/0.4.0/cluster/status \
   --header 'Content-Type:application/json' \
   --header 'x-api-key:93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd' \
   | jq .

The output will show dbrm_mode: master for the primary server:

{
  "timestamp": "2020-12-15 00:40:34.353574",
  "192.0.2.1": {
    "timestamp": "2020-12-15 00:40:34.362374",
    "uptime": 11467,
    "dbrm_mode": "master",
    "cluster_mode": "readwrite",
    "dbroots": [
      "1"
    ],
    "module_id": 1,
    "services": [
      {
        "name": "workernode",
        "pid": 19202
      },
      {
        "name": "controllernode",
        "pid": 19232
      },
      {
        "name": "PrimProc",
        "pid": 19254
      },
      {
        "name": "ExeMgr",
        "pid": 19292
      },
      {
        "name": "WriteEngine",
        "pid": 19316
      },
      {
        "name": "DMLProc",
        "pid": 19332
      },
      {
        "name": "DDLProc",
        "pid": 19366
      }
    ]
  }

1. Connect to the primary server using MariaDB Client as a user account that has privileges to lock the database:

$ mariadb --host=192.0.2.1 \
   --user=root \
   --password

1. Lock the database with the FLUSH TABLES WITH READ LOCK statement:

FLUSH TABLES WITH READ LOCK;

Ensure that the client remains connected to the primary server, so that the lock is held for the remaining steps.

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

$ sudo mkdir -p /backups/columnstore/202101291600/
$ sudo rsync -av /var/lib/columnstore/storagemanager /backups/columnstore/202101291600/

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

$ sudo rsync -av /var/lib/columnstore/data1 /backups/columnstore/202101291600/
$ sudo rsync -av /var/lib/columnstore/data2 /backups/columnstore/202101291600/
$ sudo rsync -av /var/lib/columnstore/data3 /backups/columnstore/202101291600/

1. Use MariaDB Backup to backup the Storage Manager directory:

$ sudo mkdir -p /backups/mariadb/202101291600/
$ sudo mariadb-backup --backup \
   --target-dir=/backups/mariadb/202101291600/ \
   --user=mariadb-backup \
   --password=mbu_passwd

1. Use MariaDB Backup to prepare the backup:

$ sudo mariadb-backup --prepare \
   --target-dir=/backups/mariadb/202101291600/

1. Ensure that all previous operations are complete.

2. In the original client connection to the primary server, unlock the database with the UNLOCK TABLES statement:

UNLOCK TABLES;

Restore

Use the following process to restore a backup:

1. Deploy Enterprise ColumnStore, so that you can restore the backup to an empty deployment.

2. Ensure that all services are stopped on each node:

$ sudo systemctl stop mariadb-columnstore-cmapi
$ sudo systemctl stop mariadb-columnstore
$ sudo systemctl stop mariadb

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

$ sudo rsync -av /backups/columnstore/202101291600/storagemanager/ /var/lib/columnstore/storagemanager/
$ sudo chown -R mysql:mysql /var/lib/columnstore/storagemanager

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

$ sudo rsync -av /backups/columnstore/202101291600/data1/ /var/lib/columnstore/data1/
$ sudo rsync -av /backups/columnstore/202101291600/data2/ /var/lib/columnstore/data2/
$ sudo rsync -av /backups/columnstore/202101291600/data3/ /var/lib/columnstore/data3/
$ sudo chown -R mysql:mysql /var/lib/columnstore/data1
$ sudo chown -R mysql:mysql /var/lib/columnstore/data2
$ sudo chown -R mysql:mysql /var/lib/columnstore/data3

1. Use MariaDB Backup to restore the backup of the MariaDB data directory:

$ sudo mariadb-backup --copy-back \
   --target-dir=/backups/mariadb/202101291600/
$ sudo chown -R mysql:mysql /var/lib/mysql

1. Start the services on each node:

$ sudo systemctl start mariadb
$ sudo systemctl start mariadb-columnstore-cmapi

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

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.​

Purpose

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

To safeguard against potential Extent Map corruption, regularly back up the master copy:

1. Lock Table:

mariadb -e "FLUSH TABLES WITH READ LOCK;"

1. Save BRM:

save_brm

1. Create Backup Directory:

mkdir -p /extent_map_backup

1. Copy Extent Map:

cp -f /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_em /extent_map_backup

1. Unlock Tables:

mariadb -e "UNLOCK TABLES;"

Recovery Procedures

Single-Node System

1. Stop ColumnStore:

systemctl stop mariadb-columnstore

1. Rename Corrupted Map:

mv /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_em /tmp/BRM_saves_em.bad

1. Clear Versioning Files:

> /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_vbbm > /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_vss

1. Restore Backup:

cp -f /extent_map_backup/BRM_saves_em /var/lib/columnstore/data1/systemFiles/dbrm/

1. Set Ownership:

chown -R mysql:mysql /var/lib/columnstore/data1/systemFiles/dbrm/

1. Start ColumnStore:

systemctl start mariadb-columnstore

Clustered System

1. Shutdown Cluster:

curl -s -X PUT https://127.0.0.1:8640/cmapi/0.4.0/cluster/shutdown \ --header 'Content-Type:application/json' \ --header 'x-api-key:your_api_key' \ --data '{"timeout":60}' -k

1. Rename Corrupted Map:

mv /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_em /tmp/BRM_saves_em.bad

1. Clear Versioning Files:

> /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_vbbm > /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves_vss

1. Restore Backup:

mv cp -f /extent_map_backup/BRM_saves_em /var/lib/columnstore/data1/systemFiles/dbrm/

1. Set Ownership:

chown -R mysql:mysql /var/lib/columnstore/data1/systemFiles/dbrm

1. Start Cluster:

curl -s -X PUT https://127.0.0.1:8640/cmapi/0.4.0/cluster/start \ --header 'Content-Type:application/json' \ --header 'x-api-key:your_api_key' \ --data '{"timeout":60}' -k

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.​

_{This page is licensed: CC BY-SA / Gnu FDL}

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:

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);

Next, the ColumnStore table, which is where the CTE issue arises:

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);

Attempting to run a recursive CTE directly on the employees (ColumnStore) table:

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;

This will result in the aforementioned error:

ERROR 1178 (42000): The storage engine for the table doesn't support Recursive CTE

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.

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;

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:

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;

Next, create a stored procedure to iteratively populate the temp_org_chart table:

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 ;

Finally, call the stored procedure and then select from the populated temporary table:

CALL populate_org_chart();
SELECT * FROM temp_org_chart;