MariaDB ColumnStore

For fast, ad hoc analytics at scale, MariaDB ColumnStore is a powerful columnar database that can be deployed as a standalone analytics solution or integrated with MariaDB Enterprise Server to act as a powerful query accelerator. It stores data in a columnar format and can be distributed across a cluster of servers, allowing it to execute complex queries in parallel on petabytes of data.

This integration allows you to access your InnoDB data in near-real time, processing it directly in the ColumnStore engine to run fast, parallel OLAP queries straight from your transactional data. This eliminates the need to maintain a separate pipeline or use delayed batch inserts to analyze your live data.

MariaDB Exa

For the ultimate in analytical performance, the joint solution between MariaDB and Exasol connects your mission-critical transactional data to the world’s fastest analytics engine. Available on-premise or in the cloud on platforms like AWS and Microsoft Azure, this solution brings high-speed analytics to any environment.

MariaDB Exa erases the barrier between live operational data and high-speed analytics, leveraging Exasol’s massively parallel processing (MPP) and in-memory engine. It is the ideal solution for powering your most demanding analytics and AI/ML workloads with unmatched speed and efficiency.

Overview

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.

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.

See Also

• MariaDB ColumnStore Minimum Hardware Specification Documentation

• MariaDB ColumnStore Overview

• MariaDB documentation: MariaDB ColumnStore

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

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 .

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.

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

Performing Switchover in MaxScale

The primary node can be switched in MaxScale using :

• Use or another supported REST client.

• Call a module command using the call command command.

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

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

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

For example:

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

Checking the Replication Status with MaxScale

MaxScale is capable of checking the status of using :

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

maxctrl list servers

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

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

1. About CVEs "About CVEs"

2. CVEs fixed in ColumnStore "CVEs fixed in ColumnStore"

About CVEs

CVE® stands for "Common Vulnerabilities and Exposures". It is a publicly available and free-to-use database of known software vulnerabilities maintained at

CVEs fixed in ColumnStore

The appropriate release notes listed document CVEs fixed within a given release. Additional information can also be found at Security Vulnerabilities Fixed in MariaDB.

There are no known CVEs on ColumnStore-specific infrastructure outside of the MariaDB server at this time.

Overview

In MariaDB Enterprise ColumnStore 6, the ExeMgr process uses optimizer statistics in its query planning process.

ColumnStore uses the optimizer statistics to add support for queries that contain circular inner joins.

In Enterprise ColumnStore 5 and before, ColumnStore would raise the following error when a query containing a circular inner join was executed:

ERROR 1815 (HY000): Internal error: IDB-1003: Circular joins are not supported.

The optimizer statistics store each column's NDV (Number of Distinct Values), which can help the ExeMgr process choose the optimal join order for queries with circular joins. When Enterprise ColumnStore executes a query with a circular join, the query's execution can take longer if ColumnStore chooses a sub-optimal join order. When you collect optimizer statistics for your ColumnStore tables, the ExeMgr process is less likely to choose a sub-optimal join order.

Enterprise ColumnStore's optimizer statistics can be collected for ColumnStore tables by executing :

[[analyze-table|ANALYZE TABLE]] columnstore_tab;

Enterprise ColumnStore's optimizer statistics are not updated automatically. To update the optimizer statistics for a ColumnStore table, must be re-executed.

Enterprise ColumnStore does not implement an interface to show optimizer statistics.

Overview

The ColumnStore storage engine uses a ColumnStore Execution Plan (CSEP) to represent a query plan internally.

When the select handler receives the SELECT_LEX object, it transforms it into a CSEP as part of the query planning and optimization process. For additional information, see "MariaDB Enterprise ColumnStore Query Evaluation."

Viewing the CSEP

The CSEP for a given query can be viewed by performing the following:

1. Calling the calSetTrace(1) function:

SELECT calSetTrace(1);

1. Executing the query:

SELECT column1, column2
FROM columnstore_tab
WHERE column1 > '2020-04-01'
AND column1 < '2020-11-01';

1. Calling the calGetTrace() function:

SELECT calGetTrace();

ColumnStore provides several mechanisms to ingest data:

• cpimport provides the fastest performance for inserting data and ability to route data to particular PrimProc nodes. Normally, this should be the default choice for loading data .

• LOAD DATA INFILE provides another means of bulk inserting data.

  • By default, with autocommit on, it internally streams the data to an instance of the cpimport process.

  • In transactional mode, DML inserts are performed, which is significantly slower and also consumes both binlog transaction files and ColumnStore VersionBuffer files.

• DML, i.e. INSERT, UPDATE, and DELETE, provide row-level changes. ColumnStore is optimized towards bulk modifications, so these operations are slower than they would be in, for instance, InnoDB.

  • Currently ColumnStore does not support operating as a replication replica target.

  • Bulk DML operations will in general perform better than multiple individual statements.

    • INSERT INTO SELECT with autocommit behaves similarly to LOAD DATE INFILE because, internally, it is mapped to cpimport for higher performance.

    • Bulk update operations based on a join with a small staging table can be relatively fast, especially if updating a single column.

• Using ColumnStore Bulk Write SDK or ColumnStore Streaming Data Adapters.

Hardware (On Premises)

• Quantum ActiveScale

• IBM Cloud Object Storage (Formerly known as CleverSafe)

• DELL EMC

Cloud (IaaS)

• AWS S3

• Google GCS

Software-Based

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.

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 2T RAM.

• Faster network will allow data to flow faster between PrimProc 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.

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.

Overview

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

This step bulk imports data to Enterprise ColumnStore.

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

Import the Schema

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

On the primary server, create the schema:

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

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

Import the Data

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

cpimport

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

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

LOAD DATA INFILE

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

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

Import from Remote Database

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

To import your data from a remote MariaDB database:

Next Step

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

This page was step 5 of 5.

This procedure is complete.

Overview

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

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

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

Retrieve Customer Download Token

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

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

To retrieve the token for your account:

1. Navigate to

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

Install on Debian / Ubuntu (APT):

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

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

Install MaxScale

On the MaxScale node, install MariaDB MaxScale.

Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

Next Step

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

This page was step 6 of 9.

Overview

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

This step installs MariaDB MaxScale 22.08.

ColumnStore Object Storage requires 1 or more MaxScale nodes.

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

Retrieve Customer Download Token

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

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

To retrieve the token for your account:

1. Navigate to

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

Install on Debian / Ubuntu (APT):

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

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

Install MaxScale

On the MaxScale node, install MariaDB MaxScale.

Install on CentOS / RHEL (YUM):

Install on Debian / Ubuntu (APT):

Next Step

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

This page was step 6 of 9.

.

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

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

• Row size limit of tables. This varies, depending on the storage engine you're using. For example, which indirectly limits the number of columns.

• Size limit of .frm files. Those files hold the column description of tables. Column descriptions vary in length. Once all column descriptions combined reach a length of 64KB, the table's .frm file is full, limiting the number of columns you can have in a table.

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

MariaDB ColumnStore is a columnar storage engine that utilizes a massively parallel distributed data architecture. It's a columnar storage system built by porting InfiniDB 4.6.7 to MariaDB and released under the GPL license.

is available as a storage engine for MariaDB Server. Before then, it is available as a separate download.

It is designed for big data scaling to process petabytes of data, linear scalability, and exceptional performance with real-time response to analytical queries. It leverages the I/O benefits of columnar storage, compression, just-in-time projection, and horizontal and vertical partitioning to deliver tremendous performance when analyzing large data sets.

Links:

• .

• A Google Group exists for MariaDB ColumnStore that can be used to discuss ideas and issues and communicate with the community: Send email to mariadb-columnstore@googlegroups.com or use the

• Bugs can be reported in MariaDB Jira: (see ). Please file bugs under the MCOL project and include the output from the if possible.

MariaDB ColumnStore is released under the GPL license.

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

Performing Rejoin in MaxScale

The node can be configured to rejoin in MaxScale using :

• Use or another supported REST client.

• Call a module command using the call command command.

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

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

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

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

For example:

Checking Replication Status with MaxScale

MaxScale is capable of checking the status of using :

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

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

AWS IAM Role Configuration

From Columnstore 5.5.2, you can use AWS IAM roles in order to connect to S3 buckets without explicitly entering credentials into the storagemanager.cnf config file.

You need to modify the IAM role of your Amazon EC2 instance to allow for this. Please follow the AWS before beginning this process.

It is important to note that you must update the AWS S3 endpoint based on your chosen region; otherwise, you might face delays in propagation as discussed and .

For a complete list of AWS service endpoints, visit the AWS .

Sample Configuration

Edit your Storage Manager configuration file located at /etc/columnstore/storagemanager.cnf in order to look similar to the example below (replacing those in the [S3] section with your own custom variables):

Overview

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

This step installs MariaDB Enterprise Server and MariaDB Enterprise ColumnStore 23.10.

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

Retrieve Download Token

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

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

To retrieve the token for your account:

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

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

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

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

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

$ chmod +x mariadb_es_repo_setup

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

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

Install Enterprise ColumnStore

Install additional dependencies:

Install on CentOS / RHEL (YUM)

$ sudo yum install epel-release

$ sudo yum install jemalloc

Install of Debian 10 and Ubuntu 20.04 (APT):

$ sudo apt install libjemalloc2

Install on Debian 9 and Ubuntu 18.04 (APT):

$ sudo apt install libjemalloc1

Install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:

Install on CentOS / RHEL (YUM):

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

Install on Debian / Ubuntu (APT):

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

Next Step

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

This page was step 2 of 5.

Next: Step 3: Start and Configure MariaDB Enterprise ColumnStore.

Overview

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

This step installs MariaDB Enterprise Server, MariaDB Enterprise ColumnStore 23.10, CMAPI, and dependencies.

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

Retrieve Download Token

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

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

To retrieve the token for your account:

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

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

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

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

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

$ chmod +x mariadb_es_repo_setup

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

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

Install Enterprise Server and Enterprise ColumnStore

1. On each Enterprise ColumnStore node, install additional dependencies:

Install on CentOS and RHEL (YUM):

$ sudo yum install jemalloc jq curl

Install on Debian 9 and Ubuntu 18.04 (APT)

$ sudo apt install libjemalloc1 jq curl

Install on Debian 10 and Ubuntu 20.04 (APT):

$ sudo apt install libjemalloc2 jq curl

1. On each Enterprise ColumnStore node, install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:

Install on CentOS / RHEL (YUM):

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

Install on Debian / Ubuntu (APT):

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

Next Step

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

This page was step 3 of 9.

Next: Step 4: Start and Configure MariaDB Enterprise Server.

Overview

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

This step installs MariaDB Enterprise Server, MariaDB Enterprise ColumnStore 23.10, CMAPI, and dependencies.

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

Retrieve Download Token

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

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

To retrieve the token for your account:

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

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

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

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

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

$ chmod +x mariadb_es_repo_setup

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

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

Install Enterprise Server and Enterprise ColumnStore

1. On each Enterprise ColumnStore node, install additional dependencies:

Install on CentOS and RHEL (YUM):

$ sudo yum install jemalloc jq curl

Install on Debian 9 and Ubuntu 18.04 (APT)

$ sudo apt install libjemalloc1 jq curl

Install on Debian 10 and Ubuntu 20.04 (APT):

$ sudo apt install libjemalloc2 jq curl

1. On each Enterprise ColumnStore node, install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:

Install on CentOS / RHEL (YUM):

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

Install on Debian / Ubuntu (APT):

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

Next Step

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

This page was step 3 of 9.

Next: Step 4: Start and Configure MariaDB Enterprise Server.

Overview

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

This step installs MariaDB Enterprise Server and MariaDB Enterprise ColumnStore 23.10.

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

Retrieve Download Token

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

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

To retrieve the token for your account:

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

2. Log in.

3. Copy the Customer Download Token.

Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.

Set Up Repository

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

$ sudo yum install curl

Install on Debian / Ubuntu (APT):

$ sudo apt install curl apt-transport-https

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

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

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

$ chmod +x mariadb_es_repo_setup

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

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

Install Enterprise ColumnStore

Install additional dependencies:

Install on CentOS / RHEL (YUM)

$ sudo yum install epel-release

$ sudo yum install jemalloc

Install of Debian 10 and Ubuntu 20.04 (APT):

$ sudo apt install libjemalloc2

Install on Debian 9 and Ubuntu 18.04 (APT):

$ sudo apt install libjemalloc1

Install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:

Install on CentOS / RHEL (YUM):

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

Install on Debian / Ubuntu (APT):

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

Next Step

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

This page was step 2 of 5.

Next: Step 3: Start and Configure MariaDB Enterprise ColumnStore.

Overview

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

This step bulk imports data to Enterprise ColumnStore.

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

Import the Schema

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

On the primary server, create the schema:

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

CREATE DATABASE inventory;

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

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

Import the Data

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

cpimport

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

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

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

LOAD DATA INFILE

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

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

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

Import from Remote Database

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

To import your data from a remote MariaDB database:

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

Next Step

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

This page was step 5 of 5.

This procedure is complete.

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

Setting the Server State in MaxScale

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

• Use or another supported REST client.

• 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

Confirming Maintenance Mode is Set with MaxScale

Confirm the state of the server object in MaxScale using :

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

maxctrl list servers

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

Performing Maintenance

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

While the server is in maintenance mode:

• MaxScale doesn't route traffic to the node.

• MaxScale doesn't 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 :

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

Confirming Maintenance Mode is Cleared with MaxScale

Confirm the state of the server object in MaxScale using :

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

maxctrl list servers

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

Overview

MariaDB ColumnStore utilizes an Extent Map to manage data distribution across extents—logical blocks within physical segment files ranging from 8 to 64 MB. Each extent holds a consistent number of rows, with the Extent Map cataloging these extents, their corresponding block identifiers (LBIDs), and the minimum and maximum values for each column's data within the extent.​

The primary node maintains the master copy of the Extent Map. Upon system startup, this map is loaded into memory and propagated to other nodes for redundancy and quick access. Corruption of the master Extent Map can render the system unusable and lead to data loss.​

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

Overview

Starting with MariaDB Enterprise ColumnStore 6.2.3, ColumnStore supports encryption for user passwords stored in Columnstore.xml:

• Encryption keys are created with the cskeys utility

• Passwords are encrypted using the cspasswd utility

Compatibility

• MariaDB Enterprise ColumnStore 6

• MariaDB Enterprise ColumnStore 22.08

• MariaDB Enterprise ColumnStore 23.02

Encryption Keys

MariaDB Enterprise ColumnStore stores its password encryption keys in the plain-text file /var/lib/columnstore/.secrets.

The encryption keys are not created by default, but can be generated by executing the cskeys utility:

$ cskeys

In a multi-node Enterprise ColumnStore cluster, every ColumnStore node should have the same encryption keys. Therefore, it is recommended to execute cskeys on the primary server and then copy /var/lib/columnstore/.secrets to every other ColumnStore node and fix the file's permissions:

$ scp 192.0.2.1:/var/lib/columnstore/.secrets /var/lib/columnstore/.secrets
$ sudo chown mysql:mysql /var/lib/columnstore/.secrets
$ sudo chmod 0400 /var/lib/columnstore/.secrets

Encrypt a Password

To encrypt a password:

Generate an encrypted password using the cspasswd utility:

$ cspasswd util_user_passwd

• If the --interactive command-line option is specified, cspasswd prompts for the password.

Set the encrypted password in Columnstore.xml using the mcsSetConfig utility:

$ sudo mcsSetConfig CrossEngineSupport Password util_user_encrypted_passwd

Decrypt a Password

To decrypt a password, execute the cspasswd utility and specify the --decrypt command-line option:

$ cspasswd --decrypt util_user_encrypted_passwd

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:

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.

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.

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.

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.

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.

Basic Usage

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

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

Inserting Data

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

Querying Data

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

See Also

MariaDB products can be deployed in many different topologies. The topologies described in this section are representative of the overall structure. MariaDB products can be deployed to form other topologies, leverage advanced product capabilities, or combine the capabilities of multiple topologies.

Topologies are the arrangements of nodes and links to achieve a purpose. This documentation describes a few of the many topologies that can be deployed using MariaDB database products.

We group topologies by workload (transactional, analytical, or hybrid) and technologies (Enterprise Spider). Single-node topologies are listed separately.

To help you select the correct topology:

• Each topology is named, and this name is used consistently throughout the documentation.

• A thumbnail diagram provides a small-scale summary of the topology's architecture.

• Finally, we provide a list of the benefits of the topology.

Although multiple topologies are listed on this page, the listed topologies are not the only options. MariaDB products are flexible, configurable, and extensible, so it is possible to deploy different topologies that combine the capabilities of multiple topologies listed on this page. The topologies listed on this page are primarily intended to be representative of the most commonly requested use cases.

Transactional (OLTP)

Primary/Replica Topology

Galera Cluster Topology

Analytical (OLAP, Data Warehousing, DSS)

ColumnStore Shared Local Storage Topology

ColumnStore Object Storage Topology

Hybrid Workloads

HTAP Topology

Overview

The Read Replicas feature in MariaDB ColumnStore enables horizontal scaling of read performance by incorporating read-only nodes into a multi-node cluster. These replicas differ from standard ColumnStore nodes, in that they don't run the WriteEngineServer process. This means Read Replica nodes cannot handle write operations directly — instead, any write queries attempted on a replica are automatically forwarded to a read-write (RW) node.

Replicas utilize shared storage with other nodes in the cluster, ensuring data consistency without duplication. A key requirement is maintaining at least one RW node — a cluster consisting solely of read replicas is not operational and cannot process reads or writes.

Key Features

• Horizontal Read Scaling: Adds compute power for handling more read-intensive queries without impacting write performance.

• Write Forwarding: Ensures writes on replicas are redirected to RW nodes, maintaining data integrity.

• Shared Storage: Replicas access the same DBRoots as RW nodes, promoting efficiency and reducing storage overhead.

Key Commands

• Add Read Replica. To introduce a read-only node for scaling reads, run this command:

• Remove Node. To safely remove any node (RW or replica) from the cluster, run this command:

This reassigns resources as needed without cluster disruption.

• Verify Status. To monitor the cluster's health and node roles, issue:

Limitations

• Node addition is restricted to private IPs only.

• Incompatible with S3 storage, limiting use to shared file systems.

• No automatic failover or promotion mechanism if the sole RW node goes down, requiring manual recovery.

• At least one RW node must always be present for the cluster to function properly, supporting both read and write operations.

How-To

Prerequisites

Refer to for exact mount points details.

Installation and Setup

Overview

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

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

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

Optimize Linux Kernel Parameters

MariaDB Enterprise ColumnStore performs best with Linux kernel optimizations.

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

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

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

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

Temporarily Configure Linux Security Modules (LSM)

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

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

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

CentOS / RHEL Stop SELinux

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

To set SELinux to permissive mode:

1. Set SELinux to permissive mode:

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

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

1. Confirm that SELinux is in permissive mode:

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

Debian / Ubuntu AppArmor

AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.

1. Disable AppArmor:

1. Reboot the system.

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

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

Configure Character Encoding

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

1. On RHEL 8, install additional dependencies:

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

Next Step

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

This page was step 1 of 5.

Overview

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

This step bulk imports data to Enterprise ColumnStore.

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

Import the Schema

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

On the primary server, create the schema:

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

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

Import the Data

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

cpimport

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

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

LOAD DATA INFILE

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

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

Import from Remote Database

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

To import your data from a remote MariaDB database:

Next Step

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

This page was step 9 of 9.

This procedure is complete.

Overview

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

This step bulk imports data to Enterprise ColumnStore.

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

Import the Schema

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

On the primary server, create the schema:

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

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

Import the Data

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

cpimport

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

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

LOAD DATA INFILE

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

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

Import from Remote Database

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

To import your data from a remote MariaDB database:

Next Step

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

This page was step 9 of 9.

This procedure is complete.

Overview

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

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

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

Optimize Linux Kernel Parameters

MariaDB Enterprise ColumnStore performs best with Linux kernel optimizations.

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

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

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

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

Temporarily Configure Linux Security Modules (LSM)

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

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

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

CentOS / RHEL Stop SELinux

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

To set SELinux to permissive mode:

1. Set SELinux to permissive mode:

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

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

1. Confirm that SELinux is in permissive mode:

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

Debian / Ubuntu AppArmor

AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.

1. Disable AppArmor:

1. Reboot the system.

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

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

Configure Character Encoding

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

1. On RHEL 8, install additional dependencies:

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

Create an S3 Bucket

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

S3 bucket configuration will be performed later in this procedure.

Next Step

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

This page was step 1 of 5.

.

This page provides a major release upgrade procedure for MariaDB Enterprise ColumnStore. A major release upgrade is an upgrade from an older major release to a newer major release, such as an upgrade from MariaDB Enterprise ColumnStore 5 to MariaDB Enterprise ColumnStore 22.08.

Compatibility

• Enterprise ColumnStore 5

• Enterprise ColumnStore 6

• Enterprise ColumnStore 22.08

Prerequisites

This procedure assumes that the new Enterprise ColumnStore version will be installed onto new servers.

To reuse existing servers for the new Enterprise ColumnStore version, you must adapt the procedure detailed below. After step 1, confirm all data has been backed-up and verify backups. The old version of Enterprise ColumnStore should then be uninstalled, and all Enterprise ColumnStore files should be deleted before continuing with step 2.

Step 1: Backup/Export Schemas and Data

On the old ColumnStore cluster, perform a full backup.

MariaDB recommends backing up the table schemas to a single SQL file and backing up the table data to table-specific CSV files.

1. For each table, obtain the table's schema by executing the SHOW CREATE TABLE :

   Backup the table schemas by copying the output to an SQL file. This procedure assumes that the SQL file is named schema-backup.sql.

2. For each table, backup the table data to a CSV file using the SELECT .. INTO OUTFILE :

3. Copy the SQL file containing the table schemas and the CSV files containing the table data to the primary node of the new ColumnStore cluster.

Step 2: Install New Major Release

On the new ColumnStore cluster, follow the deployment instructions of the desired topology for the new ColumnStore version.

For deployment instructions, see "".

Step 3: Restore/Import Data

On the new ColumnStore cluster, restore the table schemas and data.

1. Restore the schema backup using :

   • HOST and PORT should refer to the following:

     • If you are connecting with MaxScale as a proxy, they should refer to the host and port of the MaxScale listener

     • If you are connecting directly to a multi-node ColumnStore cluster, they should refer to the host and port of the primary ColumnStore node

     • If you are connecting directly to single-node ColumnStore, they should refer to the host and port of the ColumnStore node

   • When the command is executed, mariadb client prompts for the user password

2. For each table, restore the data from the table's CSV file by executing the on the primary ColumnStore node:

Step 4: Test

On the new ColumnStore cluster, verify that the table schemas and data have been restored.

1. For each table, verify the table's definition by executing the SHOW CREATE TABLE statement:

2. For each table, verify the number of rows in the table by executing SELECT COUNT(*):

3. For each table, verify the data in the table executing the statement.

   If the table is very large, you can limit the number of rows in the result set by adding a LIMIT clause:

MariaDB ColumnStore

Introduction

A number of system configuration variables exist to allow fine tuning of the system to suit the physical hardware and query characteristics. In general the default values will work relatively well for many cases.

The configuration parameters are maintained in the /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 affect.

Convenience utility programs getConfig and setConfig are available to safely update the Columnstore.xml without needing to be comfortable with editing XML files. The -h argument will display usage information.

Memory Management

NumBlocksPct

TotalUmMemory

Memory Requirements

In a single server or combined deployment, the sum of NumBlocksPct and TotalUmMemory should typically not exceed 75% of physical memory. With very large memory servers this could be raised but the key point is to leave enough memory for other processes including mariadbd.

Query Concurrency - MaxOutstandingRequests

ColumnStore handles concurrent query execution by managing the rate of concurrent batch primitive steps. This is configured using the MaxOutstandingRequests parameter and has a default value of 20. Each batch primitive step is executed within the context of 1 extent column according to this high level process:

• ColumnStore issues up to MaxOutstandingRequests number of batch primitive steps.

• PrimProc processes the request, using many threads and returns its response. These generally take a fraction of a second up to a low number of seconds depending on the amount of Physical I/O and the performance of that storage.

• ColumnStore issues new requests as prior requests complete maintaining the maximum number of outstanding requests.

This scheme allows for large queries to use all available resources when not otherwise being consumed and for smaller queries to execute with minimal delay. Lower values optimize for higher throughput of smaller queries while a larger value optimizes for response time of that query. The default value should work well under most circumstances however the value should be increased as the number of nodes is increased.

How many Queries are running and how many queries are currently in the queue can be checked with

Join Tuning - PmMaxMemorySmallSide

ColumnStore maintains statistics for table and utilizes this to determine which is the larger table of the two. This is based both on the number of blocks in that table and estimation of the predicate cardinality. The first step is to apply any filters as appropriate to the smaller table and returning this data set to memory. The size of this data set is compared against the configuration parameter PmMaxMemorySmallSide which has a default value of 64 (MB). This value can be set all the way up to 4GB. This default allows for approximately 1M rows on the small table side to be joined against billions (or trillions) on the large table side. If the size of the small data set is less than PmMaxMemorySmallSide the dataset is sent to PrimProc for creation of a distributed hashmap. Thus this setting is important to tuning of joins and whether the operation can be distributed or not. This should be set to support your largest expected small table join size up to available memory:

• Although this will increase the size of data between nodes to support the join, it means that the join and subsequent aggregates are pushed down, scaled out, and a smaller data set is returned back.

• In a multiple server deployment, the sizing should be based from available physical memory on the servers, how much memory to reserve for block caching, and the number of simultaneous join operations that can be expected to run times the average small table join data size.

Multi-Table Join Tuning

The above logic for a single table join extrapolates out to multi table joins where the small table values are precalculated and performed as one single scan against the large table. This works well for the typical star schema case joining multiple dimension tables with a large fact table. For some join scenarios it may be necessary to sequence joins to create the intermediate datasets for joining, this would happen for instance with a snowflake schema structure. In some extreme cases it may be hard for the optimizer to be able to determine the most optimal join path. In this case a hint is available to force a join ordering. The INFINIDB_ORDERED hint will force the first table in the from clause to be considered the largest table and override any statistics based decision, for example:

use set infinidb_ordered_only=ON;

and for 1.4 set columnstore_ordered_only=ON;

Disk-Based Joins - AllowDiskBasedJoin

When a join is very large and exceeds the PmMaxMemorySmallSide setting, it is performed in memory. For very large joins, this could exceed the available memory, in which case this is detected and a query error reported. Several configuration parameters are available to enable and configure usage of disk overflow should this occur:

• AllowDiskBasedJoin – Controls the option to use disk Based joins or not. Valid values are Y (enabled) or N (disabled). By default, this option is disabled.

• TempFileCompression – Controls whether the disk join files are compressed or noncompressed. Valid values are Y (use compressed files) or N (use non-compressed files).

• TempFilePath – The directory path used for the disk joins. By default, this path is the tmp directory for your installation (i.e., /tmp/columnstore_tmp_files/). Files (named infinidb-join-data*) in this directory will be created and cleaned on an as-needed basis. The entire directory is removed and recreated by ExeMgr at startup. It is strongly recommended that this directory be stored on a dedicated partition.

A MariaDB global or session variable is available to specify a memory limit at which point the query is switched over to disk-based joins:

• infinidb_um_mem_limit - Memory limit in MB per user (i.e., switch to disk-based join if this limit is exceeded). By default, this limit is not set (value of 0).

Overview

When Enterprise ColumnStore executes a query, the 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 to 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.

For additional information, see ".".

Batch Primitive Step (BPS)

Enterprise ColumnStore defines a batch primitive step to handle many types of tasks, such as scanning/filtering columns, JOIN operations, aggregation, functional filtering, and projecting (putting values into a SELECT list).

In calGetTrace() output, a batch primitive step is abbreviated BPS.

Batch primitive steps are evaluated on multiple nodes in parallel. The PrimProc process on each node evaluates the batch primitive step to one extent at a time. The PrimProc process uses a thread pool to operate on individual blocks within the extent in parallel.

Cross Engine Step (CES)

Enterprise ColumnStore defines a cross-engine step to perform cross-engine joins, in which a ColumnStore table is joined with a table that uses a different storage engine.

In calGetTrace() output, a cross-engine step is abbreviated CES.

Cross-engine steps are evaluated locally by the ExeMgr process on the initiator/aggregator node.

Enterprise ColumnStore can perform cross-engine joins when the mandatory utility user is properly configured.

For additional information, refer to the ""

Dictionary Structure Step (DSS)

Enterprise ColumnStore defines a dictionary structure step to scan the dictionary extents that ColumnStore uses to store variable-length string values.

In calGetTrace() output, a dictionary structure step is abbreviated DSS.

Dictionary structure steps are evaluated on multiple nodes in parallel. The PrimProc process on each node evaluates the dictionary structure step to one extent at a time. It uses a thread pool to operate on individual blocks within the extent in parallel.

Dictionary structure steps can require a lot of I/O for a couple of reasons:

• Dictionary structure steps do not support extent elimination, so all extents for the column must be scanned.

• Dictionary structure steps must read the column extents to find each pointer and the dictionary extents to find each value, so it doubles the number of extents to scan.

It is generally recommended to avoid queries that will cause dictionary scans.

For additional information, see "Avoid Creating Long String Columns".

Hash Join Step (HJS)

Enterprise ColumnStore defines a hash join step to perform a hash join between two tables.

In calGetTrace() output, a hash join step is abbreviated HJS.

Hash join steps are evaluated locally by the ExeMgr process on the initiator/aggregator node.

Enterprise ColumnStore performs the hash join in memory by default. If you perform large joins, you may be able get better performance by changing some configuration defaults with mcsSetConfig:

• Enterprise ColumnStore can be configured to use more memory for in-memory hash joins.

• Enterprise ColumnStore can be configured to use disk-based joins.

For additional information, see "" and "".

Having Step (HVS)

Enterprise ColumnStore defines a having step to evaluate a HAVING clause on a result set.

In calGetTrace() output, a having step is abbreviated HVS.

Subquery Step (SQS)

Enterprise ColumnStore defines a subquery step to evaluate a subquery.

In calGetTrace() output, a subquery step is abbreviated SQS.

Tuple Aggregation Step (TAS)

Enterprise ColumnStore defines a tuple aggregation step to collect intermediate aggregation prior to the final aggregation and evaluation of the results.

In calGetTrace() output, a tuple aggregation step is abbreviated TAS.

Tuple aggregation steps are primarily evaluated by the ExeMgr process on the initiator/aggregator node. However, the PrimProc process on each node also plays a role, since the PrimProc process on each node provides the intermediate aggregation results to the ExeMgr process on the initiator/aggregator node.

Tuple Annexation Step (TNS)

Enterprise ColumnStore defines a tuple annexation step to perform the final aggregation and evaluation of the results.

In calGetTrace() output, a tuple annexation step is abbreviated TNS.

Tuple annexation steps are evaluated locally by the ExeMgr process on the initiator/aggregator node.

Enterprise ColumnStore 5 performs aggregation operations in memory. As a consequence, more complex aggregation operations require more memory in that version.

In Enterprise ColumnStore 6, disk-based aggregations can be enabled.

For additional information, see "".

Tuple Union Step (TUS)

Enterprise ColumnStore defines a tuple union step to perform a union of two subqueries.

In calGetTrace() output, a tuple union step is abbreviated TUS.

Tuple union steps are evaluated locally by the ExeMgr process on the initiator/aggregator node.

Tuple Constant Step (TCS)

Enterprise ColumnStore defines a tuple constant step to evaluate constant values.

In calGetTrace() output, a tuple constant step is abbreviated TCS.

Tuple constant steps are evaluated locally by the ExeMgr process on the initiator/aggregator node.

Window Function Step (WFS)

Enterprise ColumnStore defines a window function step to evaluate window functions.

In calGetTrace() output, a window function step is abbreviated WFS.

Window function steps are evaluated locally by the on the initiator/aggregator node.

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

The ColumnStore storage engine has the following features:

Examples

Creating a ColumnStore Table

To create a ColumnStore table, use the 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

Overview

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

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

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

Configure Enterprise ColumnStore

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

Example Configuration

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

Start the Enterprise ColumnStore Services

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

$ sudo systemctl start mariadb

$ sudo systemctl enable mariadb

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

$ sudo systemctl start mariadb-columnstore

$ sudo systemctl enable mariadb-columnstore

Create the Utility User

Enterprise ColumnStore requires a mandatory utility user account. By default, it connects to the server using the root user with no password. MariaDB Enterprise Server 10.6 will reject this login attempt by default, so you will need to configure Enterprise ColumnStore to use a different user account and password and create this user account on Enterprise Server.

1. On the Enterprise ColumnStore node, create the user account with the statement:

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

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

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

1. Configure Enterprise ColumnStore to use the utility user:

$ sudo mcsSetConfig CrossEngineSupport Host 127.0.0.1

$ sudo mcsSetConfig CrossEngineSupport Port 3306

$ sudo mcsSetConfig CrossEngineSupport User util_user

1. Set the password:

$ sudo mcsSetConfig CrossEngineSupport Password util_user_passwd

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

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

Configure Linux Security Modules (LSM)

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

Configure SELinux (CentOS, RHEL)

Configure SELinux for Enterprise ColumnStore:

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

$ sudo yum install policycoreutils policycoreutils-python

On RHEL 8, install the following:

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

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

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

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

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

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

Nothing to do

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

$ sudo semodule -i mariadb_local.pp

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

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

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

1. Set SELinux to enforcing mode:

$ sudo setenforce enforcing

Configure AppArmor (Ubuntu)

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

Next Step

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

This page was step 3 of 5.

Next: Step 4: Test MariaDB Enterprise ColumnStore.

Overview

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

This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore 23.10.

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

Test Local Connection

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

$ sudo mariadb

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

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

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

MariaDB [(none)]>

Test ColumnStore Plugin Status

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

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

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

Test ColumnStore Table Creation

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

CREATE DATABASE IF NOT EXISTS test;

1. Create a ColumnStore table:

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

1. Add sample data into the table:

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

1. Read data from table:

SELECT * FROM test.contacts;

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

Test Cross Engine Join

1. Create an InnoDB table:

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

1. Add data to the table:

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

1. Perform a cross-engine join:

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

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

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

Next Step

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

This page was step 4 of 5.

Next: Step 5: Bulk Import of Data.

Overview

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

This step tests MariaDB Enterprise Server and MariaDB Enterprise ColumnStore 23.10.

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

Test S3 Connection

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

On each Enterprise ColumnStore node, test the S3 configuration:

$ sudo testS3Connection

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

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

Test Local Connection

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

$ sudo mariadb

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

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

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

MariaDB [(none)]>

Test ColumnStore Plugin Status

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

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

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

Test ColumnStore Table Creation

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

CREATE DATABASE IF NOT EXISTS test;

1. Create a ColumnStore table:

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

1. Add sample data into the table:

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

1. Read data from table:

SELECT * FROM test.contacts;

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

Test Cross Engine Join

1. Create an InnoDB table:

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

1. Add data to the table:

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

1. Perform a cross-engine join:

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

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

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

Next Step

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

This page was step 4 of 5.

Next: Step 5: Bulk Import of Data.

Introduction

MariaDB ColumnStore is a high-performance columnar database designed for analytical workloads. By optimizing the Linux kernel parameters, you can further enhance the performance of your MariaDB ColumnStore deployments.

Recommended Parameters

The following table lists the recommended optimized Linux kernel parameters for MariaDB ColumnStore:

For more information refer to .

Configuration Example

To configure these parameters, you can add them to the /etc/sysctl.conf file. For example:

vm.overcommit_memory=1 
vm.dirty_background_ratio=5 
vm.dirty_ratio=10 
vm.vfs_cache_pressure=50 
net.core.netdev_max_backlog=2500 
net.core.rmem_max=16777216 
net.core.wmem_max=16777216 
net.ipv4.tcp_max_syn_backlog=8192 
net.ipv4.tcp_timestamps=0

After making changes to the /etc/sysctl.conf file, you need to apply the changes by running the following command:

sudo sysctl -p

Increase the Limit for Memory-Mapped Areas

cat /proc/sys/kernel/threads-max
cat /proc/sys/kernel/pid_max
cat /proc/sys/vm/max_map_count


# Rhel /etc/sysctl.conf
sudo echo "vm.max_map_count=4262144" >> /etc/sysctl.conf
sudo echo "kernel.pid_max = 4194304" >> /etc/sysctl.conf
sudo echo "kernel.threads-max = 2000000" >> /etc/sysctl.conf

# There may be a file called 50-pid-max.conf or perhaps something similar. If so, modify it 
sudo echo "vm.max_map_count=4262144" > /usr/lib/sysctl.d/50-max_map_count.conf
sudo echo "kernel.pid_max = 4194304" > /usr/lib/sysctl.d/50-pid-max.conf
sudo sysctl -p

Common Use Cases

These optimized parameters are recommended for all MariaDB ColumnStore deployments, regardless of the specific workload. They can improve performance for various use cases, including:

• Large-scale data warehousing

• Real-time analytics

• Business intelligence

• Machine learning

Related Links

• MariaDB ColumnStore Documentation

• Linux Kernel Documentation

• MCOL-5165: Add optimized Linux kernel parameters for MariaDB ColumnStore

Conclusion

By optimizing the Linux kernel parameters, you can significantly improve the performance of your MariaDB ColumnStore deployments. These recommendations provide a starting point for optimizing your system, and you may need to adjust the values based on your specific hardware and workload.

When tuning queries for MariaDB Enterprise ColumnStore, there are some important details to consider.

Avoid Selecting Unnecessary Columns

Enterprise ColumnStore only reads the columns that are necessary to resolve a query.

For example, the following query selects every column in the table:

SELECT * FROM tab;

Whereas the following query only selects two columns in the table, so it requires less I/O:

SELECT col1, col2 FROM tab;

For best performance, only select the columns that are necessary to resolve a query.

Avoid Large Sorts

When Enterprise ColumnStore performs ORDER BY and LIMIT operations, the operations are performed in a single-threaded manner after the rest of the query processing has been completed, and the full unsorted result-set has been retrieved. For large data sets, the performance overhead can be significant.

Avoid Excessive Aggregations

When Enterprise ColumnStore 5 performs aggregations (i.e., DISTINCT, GROUP BY, COUNT(*), etc.), all of the aggregation work happens in-memory by default. As a consequence, more complex aggregation operations require more memory in that version.

For example, the following query could require a lot of memory in Enterprise ColumnStore 5, since it has to calculate many distinct values in memory:

SELECT DISTINCT col1 FROM tab LIMIT 10000;

Whereas the following query could require much less memory in Enterprise ColumnStore 5, since it has to calculate fewer distinct values:

SELECT DISTINCT col1 FROM tab LIMIT 100;

In Enterprise ColumnStore 6, disk-based aggregations can be enabled.

For best performance, avoid excessive aggregations or enable disk-based aggregations.

For additional information, see "Configure Disk-Based Aggregations".

Avoid Non-Distributed Functions

When Enterprise ColumnStore evaluates built-in functions and aggregate functions, it can often evaluate the function in a distributed manner. Distributed evaluation of functions can significantly improve performance.

Enterprise ColumnStore supports distributed evaluation for some built-in functions. For other built-in functions, the function must be evaluated serially on the final result set.

Enterprise ColumnStore also supports distributed evaluation for user-defined functions developed with ColumnStore's User-Defined Aggregate Function (UDAF) C++ API. For functions developed with Enterprise Server's standard User-Defined Function (UDF) API, the function must be evaluated serially on the final result set.

For best performance, avoid non-distributed functions.

Optimize Large Joins

By default, Enterprise ColumnStore performs all joins as in-memory hash joins.

If the joined tables are very large, the in-memory hash join can require too much memory for the default configuration. There are a couple options to work around this:

• Enterprise ColumnStore can be configured to use more memory for in-memory hash joins.

• Enterprise ColumnStore can be configured to use disk-based joins.

• Enterprise ColumnStore can use optimizer statistics to better optimize the join order.

For additional information, see "Configure In-Memory Joins", "Configure Disk-Based Joins", and "Optimizer Statistics".

Load Ordered Data in Proper Order

Enterprise ColumnStore uses extent elimination to optimize queries. Extent elimination uses the minimum and maximum values in the extent map to determine which extents can be skipped for a query.

When data is loaded into Enterprise ColumnStore, it appends the data to the latest extent. When an extent reaches the maximum number of column values, Enterprise ColumnStore creates a new extent. As a consequence, if ordered data is loaded in its proper order, then similar values will be clustered together in the same extent. This can improve query performance, because extent elimination performs best when similar values are clustered together.

For example, if you expect to query a table with a filter on a timestamp column, you should sort the data using the timestamp column before loading it into Enterprise ColumnStore. Later, when the table is queried with a filter on the timestamp column, Enterprise ColumnStore would be able to skip many extents using extent elimination.

For best performance, load ordered data in proper order.

Enable Decimal Overflow Checks

When Enterprise ColumnStore performs mathematical operations with very big values using the , , and data types, the operation can sometimes overflow ColumnStore's maximum precision or scale. The maximum precision and scale depend on the version of Enterprise ColumnStore:

• In Enterprise ColumnStore 6, the maximum precision (M) is 38, and the maximum scale (D) is 38.

• In Enterprise ColumnStore 5, the maximum precision (M) is 18, and the maximum scale (D) is 18.

In Enterprise ColumnStore 6, applications can configure Enterprise ColumnStore to check for decimal overflows by setting the columnstore_decimal_overflow_check system variable, but only when the column has a decimal precision that is 18 or more:

SET SESSION columnstore_decimal_overflow_check=ON;

SELECT (big_decimal1 * big_decimal2) AS product
FROM columnstore_tab;

When decimal overflow checks are enabled, math operations have extra overhead.

When the decimal overflow check fails, MariaDB Enterprise ColumnStore raises an error with the ER_INTERNAL_ERROR error SQL, and it writes detailed information about the overflow check failure to the ColumnStore system logs.

User-Defined Aggregate Function (UDAF) C++ API

MariaDB Enterprise ColumnStore supports Enterprise Server's standard User-Defined Function (UDF) API. However, UDFs developed using that API cannot be executed in a distributed manner.

To support distributed execution of custom SQL, MariaDB Enterprise ColumnStore supports a Distributed User Defined Aggregate Functions (UDAF) C++ API:

• The Distributed User Defined Aggregate Functions (UDAF) C++ API allows anyone to create aggregate functions of arbitrary complexity for distributed execution in the ColumnStore storage engine.

• These functions can also be used as Analytic (Window) functions just like any built-in aggregate function.

Overview

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

This step configures shared local storage on systems hosting Enterprise ColumnStore 23.10.

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

Directories for Shared Local Storage

In a ColumnStore Object Storage topology, MariaDB Enterprise ColumnStore requires the Storage Manager directory to be located on shared local storage.