Deploy Multi-Node MariaDB Enterprise ColumnStore 1.4 with MariaDB Enterprise Server 10.4 on Ubuntu 20.04 LTS

These instructions detail the deployment of MariaDB Enterprise ColumnStore 1.4 with MariaDB Enterprise Server 10.4 on Ubuntu 20.04 LTS in a Multi-Node ColumnStore Deployment configuration.

These instructions detail how to deploy a multi-node columnar database, which is suited for an analytical or OLAP workload that requires high availability (HA).

MariaDB Platform Components

These instructions detail the deployment of the following MariaDB Platform components:

Component

Description

MariaDB Enterprise Server 10.4

  • It is a 100% Open Source modern SQL database.

MariaDB Enterprise ColumnStore 1.4

  • It is a columnar storage engine that provides distributed, columnar storage for scalable analytical processing and smart transactions.

  • It is the analytical component of MariaDB's single stack Hybrid Transactional/Analytical Processing (HTAP) solution.

MariaDB Enterprise Server Components

These instructions detail the deployment of the following MariaDB Enterprise Server components:

Component

Description

ColumnStore

  • It is a columnar storage engine that provides distributed, columnar storage for scalable analytical processing and smart transactions.

  • It is the analytical component of MariaDB's single stack Hybrid Transactional/Analytical Processing (HTAP) solution.

  • It is available as a plugin in MariaDB Enterprise Server 10.4.

Term Definitions

Term

Definition

columnar database

  • A database where the columns of each row are stored separately.

  • Best suited for analytical and OLAP workloads.

  • Also known as a "column-oriented database".

row database

  • A database where all columns of each row are stored together.

  • Best suited for transactional and OLTP workloads.

  • Also known as a "row-oriented database".

System Preparation

MariaDB ColumnStore installations for multi-node deployments are configured from the initial Performance Module. For these post-installation scripts to connect to and configure the other Servers, systems hosting ColumnStore Instances require some additional configuration.

Firewalls

MariaDB ColumnStore requires ports 3306, 8600 to 8630, 8700, and 8800. On some operating systems, this may require configuring the firewall to permit TCP traffic over these ports.

The postConfigure script runs on the initial Performance Module and from this system connects over the network to all other Performance Modules to configure them to operate as back-end storage to MariaDB ColumnStore. To avoid confusion and potential problems, disable the firewall during installation.

Warning

Be sure to re-enable the firewall once installation is complete.

SSH Configuration

The postConfigure script uses SSH to connect from the initial Performance Module (which ColumnStore identifies internally as pm1) to the other Servers in the deployment. For pm1 to connect to the other Servers, you need to generate an SSH key and share it with the other hosts in the deployment.

First, generate the SSH key:

$ sudo ssh-keygen

Then, copy the SSH key to each Server in your deployment:

$ sudo ssh-copy-id -i ~/.ssh/id_rsa.pub 192.0.2.2

Once this is done, restart the SSH daemon on each Server:

$ sudo systemctl restart sshd

You can test the connection using the SSH client from the initial system to connect to the others:

$ ssh 192.0.2.2

Storage Manager

MariaDB ColumnStore manages back-end storage using Performance Modules. When a MariaDB Enterprise Server goes down, the deployment loses access to the data stored in the local Performance Module.

Storage managers allow MariaDB ColumnStore to store data in network accessible file systems. When using a storage manager, the deployment can access a shared pool of Performance Modules, which remain accessible even if the Server goes down.

If you would like to use any sort of storage manager, you need to install and configure it prior to installing MariaDB ColumnStore.

MariaDB ColumnStore 1.4 supports three forms of external storage, configured by the postConfigure script:

Storage

Description

External

Mounted directory in the local file system, shared by all Servers.

GlusterFS

Network attached storage file system.

Amazon S3

Simple Storage Service from AWS, provides object storage to each Server for MariaDB ColumnStore.

Character Encoding

In multi-node ColumnStore, the system's character encoding can effect the behavior of the system. In general, it is recommended to use UTF-8. To set the locale, use localedef on all nodes:

$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8

Installation

MariaDB Corporation provides a APT package repository for Ubuntu 20.04 LTS.

MariaDB ColumnStore ships as a storage engine plugin for MariaDB Enterprise Server and a platform engine to handle back-end storage processes. MariaDB Enterprise Server 10.4 does not require any additional software to operate as an analytics database.

Install via APT (Debian/Ubuntu)

  1. Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for customer_download_token in the following directions.

  2. Configure the APT package repository.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "957bc29576e8fd320fa18e35fa49b5733f3c8eeb4ca06792fb1f05e089c810ff  mariadb_es_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_es_repo_setup
    
    $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \
       --mariadb-server-version="10.4"
    
    $ sudo apt update
    
  3. Install MariaDB ColumnStore and package dependencies:

    $ sudo apt install mariadb-server \
        mariadb-columnstore-platform mariadb-plugin-columnstore
    
  4. Stop the Server and disable the service after installing the packages:

    $ sudo systemctl stop mariadb
    $ sudo systemctl disable mariadb
    

    MariaDB ColumnStore post-installation scripts fail if they find MariaDB Enterprise Server running on the system.

  5. Configure MariaDB ColumnStore.

    Installation only loads MariaDB ColumnStore to the system. MariaDB ColumnStore requires configuration before the database server is ready for use.

Configuration

Server Configuration

MariaDB Enterprise Server can be configured in the following ways:

  • System variables and options can be set in a configuration file (such as /etc/my.cnf). MariaDB Enterprise Server must be restarted to apply changes made to the configuration file.

  • System variables and options can be set on the command-line.

  • If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.

Configuration Files

MariaDB's packages include several bundled configuration files. It is also possible to create custom configuration files.

On Debian and Ubuntu, MariaDB's packages bundle the following configuration files:

  • /etc/mysql/my.cnf

  • /etc/mysql/mariadb.cnf

  • /etc/mysql/mariadb.conf.d/50-client.cnf

  • /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

  • /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf

  • /etc/mysql/mariadb.conf.d/50-server.cnf

  • /etc/mysql/mariadb.conf.d/60-galera.cnf

  • /etc/mysql/mariadb.conf.d/mariadb-enterprise.cnf

And on Debian and Ubuntu, custom configuration files from the following directories are read by default:

  • /etc/mysql/conf.d/

  • /etc/mysql/mariadb.conf.d/

Configuring MariaDB

  1. Determine which system variables and options you need to configure.

  2. Choose a configuration file in which to configure your system variables and options.

    It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, then it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as z-.

    • On Debian and Ubuntu, a good custom configuration file would be: /etc/mysql/mariadb.conf.d/z-custom-my.cnf

  3. Set your system variables and options in the configuration file.

    They need to be set in a group that will be read by mariadbd, such as [mariadb] or [server].

    For example:

    [mariadb]
    log_error = mysqld.err
    

Cross Engine Joins

Cross Engine Joins use a TCP connection from the ExeMgr to the Server, using the root user with no password by default. MariaDB Enterprise Server 10.4 does not accept the default configuration. If you plan to use Cross Engine Joins, you must create a new user for this purpose and update the MariaDB Columnstore.xml configuration file.

Warning

Editing the Columnstore.xml is dangerous and can have unexpected results. Do not edit this configuration unless you specifically need support for Cross Engine Joins.

Edit the /etc/columnstore/Columnstore.xml file, modifying the <CrossEngineSupport> element to use the configured cross_engine user on Cross Engine Joins:

<CrossEngineSupport>
   <Host>127.0.0.1</Host>
   <Port>3306</Port>
   <User>cross_engine</User>
   <Password>cross_engine_passwd</Password>
</CrossEngineSupport>

The configuration sets the user and password MariaDB ColumnStore uses for Cross Engine Joins. For this user to work, you must also create the user and grant it the appropriate privileges. Create the relevant user once you have the instance online.

Post-Installation

Installation of MariaDB ColumnStore and Enterprise Server packages provides the necessary software to run the Server as a ColumnStore Instance, but additional steps are required to configure the Performance Modules, which handle back-end storage processes for MariaDB ColumnStore.

Post-Installation Script

Run the columnstore-post-install script on each ColumnStore Instance to provision the system to host the storage back-end:

$ sudo columnstore-post-install

Post-Configuration Script

MariaDB ColumnStore provides a post-configuration script to configure the ColumnStore Instance. Post-configuration is only required on the first Server in your deployment (called pm1 by convention). The postConfigure script connects over SSH to initialize Performance Modules on the other Servers.

Run the postConfigure script on the Server hosting the initial Performance Module (that is, pm1):

$ sudo postConfigure

The postConfigure script runs through a series of prompts to configure the ColumnStore deployment.

  • When prompted to configure the ColumnStore deployment for single- or multi-node, select multi-node.

  • Sets the system name.

  • Configures the storage mount to store data internally on the local file system, externally on a network mounted disk, or using an S3 or GlusterFS storage manager.

  • Configures the Performance Modules, setting the IP addresses and DBRoots for each.

  • Sets the password to use in establishing SSH connections to the Performance Modules.

Once postConfigure has the information it needs, it connects to each Server and sets up the Performance Module to match the configuration, then starts MariaDB ColumnStore.

Restart the System

When the postConfigure script is complete, use mcsadmin restartSystem to restart MariaDB ColumnStore to clear the cache:

$ sudo mcsadmin restartSystem y

Create the Cross Engine Join User

Configuring MariaDB ColumnStore to perform Cross Engine Joins sets the user and password the system uses when reading data for the operation, it does not create the user or grant it privileges to access the necessary data.

If you have configured your system to perform Cross Engine Joins, you must also create a user with sufficient privileges to access that data. To create a user with the necessary privileges, use the CREATE USER and GRANT statements with the relevant credentials:

CREATE USER 'cross_engine'@'127.0.0.1'
   IDENTIFIED BY "cross_engine_passwd";

GRANT SELECT ON *.*
   TO 'cross_engine'@'127.0.0.1';

Administration

MariaDB ColumnStore includes an administrative utility called mcsadmin, which you can use to start and stop the ColumnStore processes:

Operation

Command

Start

mcsadmin startSystem

Stop

mcsadmin shutdownSystem

Restart

mcsadmin restartSystem

Status

mcsadmin getSystemStatus

Testing

When you have MariaDB ColumnStore up and running, you should test it to ensure that it is in working order and that there were no issues during startup.

Checking Server Status

  1. 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: 10.4.13-7-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)]>
    

Checking System Status

  1. Execute the mcsadmin getSystemStatus command:

    $ sudo mcsadmin getSystemStatus
    getsystemstatus   Wed Jan  8 23:44:55 2020
    
    System columnstore-1
    
    System and Module statuses
    
    Component     Status                       Last Status Change
    ------------  --------------------------   ------------------------
    System        ACTIVE                       Wed Jan  8 23:14:14 2020
    
    Module pm1    ACTIVE                       Wed Jan  8 23:14:11 2020
    
    Module pm2    ACTIVE                       Wed Jan  8 23:14:11 2020
    

    Additional information is available in the MariaDB Knowledge Base.

Next steps: