Deploy ColumnStore Shared Local Storage Topology with MariaDB Enterprise Server 10.6

This procedure describes the deployment of the ColumnStore Shared Local Storage topology with MariaDB Enterprise Server 10.6, MariaDB Enterprise ColumnStore 6, and MariaDB MaxScale 6.1.

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

This procedure has 9 steps, which are executed in sequence.

This procedure represents basic product capability and deploys 3 Enterprise ColumnStore nodes and 1 MaxScale node.

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

Please read and understand this procedure before executing.

Support

Customers can obtain support by submitting a support case.

Components

The following components are deployed during this procedure:

Component

Function

MariaDB Enterprise Server 10.6

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

MariaDB MaxScale 6.1

Database proxy that extends the availability, scalability, and security of MariaDB Enterprise Servers

MariaDB Enterprise Server Components

Component

Description

MariaDB Enterprise ColumnStore

  • Columnar storage engine

  • Highly available

  • Optimized for Online Analytical Processing (OLAP) workloads

  • Scalable query execution

  • Cluster Management API (CMAPI) provides a REST API for multi-node administration.

MariaDB MaxScale Components

Component

Description

Listener

Listens for client connections to MaxScale then passes them to the router service

MariaDB Monitor

Tracks changes in the state of MariaDB Enterprise Servers.

Read Connection Router

Routes connections from the listener to any available Enterprise ColumnStore node

Read/Write Split Router

Routes read operations from the listener to any available Enterprise ColumnStore node, and routes write operations from the listener to a specific server that MaxScale uses as the primary server

Server Module

Connection configuration in MaxScale to an Enterprise ColumnStore node

Topology

Enterprise ColumnStore Topology with Shared Local Storage

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

The topology consists of:

  • One or more MaxScale nodes

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

The MaxScale nodes:

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

  • Accept client and application connections

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

The ColumnStore nodes:

Requirements

These requirements are for the ColumnStore Shared Local Storage topology when deployed with MariaDB Enterprise Server 10.6, MariaDB Enterprise ColumnStore 6, and MariaDB MaxScale 6.1.

Node Count

  • MaxScale nodes, 1 or more are required.

  • Enterprise ColumnStore nodes, 3 or more are required for high availability.

Operating System

In alignment to the enterprise lifecycle, the ColumnStore Shared Local Storage topology with MariaDB Enterprise Server 10.6, MariaDB Enterprise ColumnStore 6, and MariaDB MaxScale 6.1 is provided for:

  • CentOS 7 (x86_64)

  • CentOS 8 (x86_64)

  • Debian 9 (x86_64)

  • Debian 10 (x86_64)

  • Red Hat Enterprise Linux 7 (x86_64)

  • Red Hat Enterprise Linux 8 (x86_64)

  • SUSE Linux Enterprise Server 12 (x86_64)

  • SUSE Linux Enterprise Server 15 (x86_64)

  • Ubuntu 18.04 (x86_64)

  • Ubuntu 20.04 (x86_64)

Minimum Hardware Requirements

MariaDB Enterprise ColumnStore's minimum hardware requirements are not intended for production environments, but the minimum hardware requirements can be appropriate for development and test environments. For production environments, see the recommended hardware requirements instead.

The minimum hardware requirements are:

Component

CPU

Memory

MaxScale node

4+ cores

4+ GB

Enterprise ColumnStore node

4+ cores

16+ GB

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

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

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

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

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

Storage Requirements

The ColumnStore Shared Local Storage topology requires the following storage types:

Storage Type

Description

Shared Local Storage

The ColumnStore Shared Local Storage topology uses shared local storage for the DB Root directories to store data and metadata.

Shared Local Storage Directories

The ColumnStore Shared Local Storage topology uses shared local storage for the DB Root directories to store data and metadata.

The DB Root directories is located at the following path by default:

  • /var/lib/columnstore/dataN

The N in dataN represents a range of integers that starts at 1 and stops at the number of nodes in the deployment. For example, with a 3-node Enterprise ColumnStore deployment, this would refer to the following directories:

  • /var/lib/columnstore/data1

  • /var/lib/columnstore/data2

  • /var/lib/columnstore/data3

To enable high availability for the DB Root directories, each directory should be mounted on every ColumnStore node using shared local storage.

For the most common shared local storage options, see "Shared Local Storage Options".

Shared Local Storage Options

The most common shared local storage options for the ColumnStore Shared Local Storage topology are:

Shared Local Storage

Common Usage

Description

EBS (Elastic Block Store) Multi-Attach

AWS

  • EBS is a high-performance block-storage service for AWS (Amazon Web Services).

  • EBS Multi-Attach allows an EBS volume to be attached to multiple instances in AWS. Only clustered file systems, such as GFS2, are supported.

EFS (Elastic File System)

AWS

  • EFS is a scalable, elastic, cloud-native NFS file system for AWS (Amazon Web Services).

Filestore

GCP

  • Filestore is high-performance, fully managed storage for GCP (Google Cloud Platform).

GlusterFS

On-premises

  • GlusterFS is a distributed file system.

  • GlusterFS supports replication and failover.

NFS (Network File System)

On-premises

  • NFS is a distributed file system.

  • If NFS is used, the storage should be mounted with the sync option to ensure that each node flushes its changes immediately.

Enterprise ColumnStore Management with CMAPI

Enterprise ColumnStore's CMAPI (Cluster Management API) is a REST API that can be used to manage a multi-node Enterprise ColumnStore cluster.

Many tools are capable of interacting with REST APIs. For example, the curl utility could be used to make REST API calls from the command-line.

Many programming languages also have libraries for interacting with REST APIs.

The examples below show how to use the CMAPI with curl.

URL Endpoint Format for REST API

https://{server}:{port}/cmapi/{version}/{route}/{command}

For example:

  • https://mcs1:8640/cmapi/0.4.0/cluster/shutdown

  • https://mcs1:8640/cmapi/0.4.0/cluster/start

  • https://mcs1:8640/cmapi/0.4.0/cluster/status

  • https://mcs1:8640/cmapi/0.4.0/cluster/node

Required Request Headers

  • 'x-api-key': '93816fa66cc2d8c224e62275bd4f248234dd4947b68d4af2b29671dd7d5532dd'

  • 'Content-Type': 'application/json'

x-api-key can be set to any value of your choice during the first call to the server. Subsequent connections will require this same key.

Get Status

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

Start Cluster

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

Stop Cluster

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

Add Node

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

Remove Node

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

Quick Reference

MariaDB Enterprise Server Configuration Management

Method

Description

Configuration File

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

Command-line

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

SQL

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

MariaDB Enterprise Server packages are configured to read configuration files from different paths, depending on the operating system. Making custom changes to Enterprise Server default configuration files is not recommended because custom changes may be overwritten by other default configuration files that are loaded later.

To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.

Distribution

Example Configuration File Path

  • CentOS

  • Red Hat Enterprise Linux (RHEL)

  • SUSE Linux Enterprise Server (SLES)

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

  • Debian

  • Ubuntu

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

MariaDB Enterprise Server Service Management

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

Operation

Command

Start

sudo systemctl start mariadb

Stop

sudo systemctl stop mariadb

Restart

sudo systemctl restart mariadb

Enable during startup

sudo systemctl enable mariadb

Disable during startup

sudo systemctl disable mariadb

Status

sudo systemctl status mariadb

MariaDB Enterprise Server Logs

MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.

Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the datadir system variable.

Log

System Variable/Option

Default Filename

MariaDB Error Log

log_error

<hostname>.err

MariaDB Enterprise Audit Log

server_audit_file_path

server_audit.log

Slow Query Log

slow_query_log_file

<hostname>-slow.log

General Query Log

general_log_file

<hostname>.log

Binary Log

log_bin

<hostname>-bin

Enterprise ColumnStore Service Management

The systemctl command is used to start and stop the ColumnStore service.

Operation

Command

Start

sudo systemctl start mariadb-columnstore

Stop

sudo systemctl stop mariadb-columnstore

Restart

sudo systemctl restart mariadb-columnstore

Enable during startup

sudo systemctl enable mariadb-columnstore

Disable during startup

sudo systemctl disable mariadb-columnstore

Status

sudo systemctl status mariadb-columnstore

In the ColumnStore Shared Local Storage topology, the mariadb-columnstore service should not be enabled. The CMAPI service restarts Enterprise ColumnStore as needed, so it does not need to start automatically upon reboot.

Enterprise ColumnStore CMAPI Service Management

The systemctl command is used to start and stop the CMAPI service.

Operation

Command

Start

sudo systemctl start mariadb-columnstore-cmapi

Stop

sudo systemctl stop mariadb-columnstore-cmapi

Restart

sudo systemctl restart mariadb-columnstore-cmapi

Enable during startup

sudo systemctl enable mariadb-columnstore-cmapi

Disable during startup

sudo systemctl disable mariadb-columnstore-cmapi

Status

sudo systemctl status mariadb-columnstore-cmapi

For additional information on endpoints, see "CMAPI".

MaxScale Configuration Management

MaxScale can be configured using several methods. These methods make use of MaxScale's REST API.

Method

Benefits

MaxCtrl

Command-line utility to perform administrative tasks through the REST API. See MaxCtrl Commands.

MaxGUI

MaxGUI is a graphical utility that can perform administrative tasks through the REST API.

REST API

The REST API can be used directly. For example, the curl utility could be used to make REST API calls from the command-line. Many programming languages also have libraries to interact with REST APIs.

The procedure on these pages configures MaxScale using MaxCtrl.

MaxScale Service Management

The systemctl command is used to start and stop the MaxScale service.

Operation

Command

Start

sudo systemctl start maxscale

Stop

sudo systemctl stop maxscale

Restart

sudo systemctl restart maxscale

Enable during startup

sudo systemctl enable maxscale

Disable during startup

sudo systemctl disable maxscale

Status

sudo systemctl status maxscale