Single ColumnStore 1.4 on Debian 10

These instructions detail the deployment of MariaDB ColumnStore 1.4 on Debian 10 in a Single ColumnStore Instance configuration.

MariaDB ColumnStore provides distributed, columnar storage for scalable analytical processing and is available as a plugin storage engine with MariaDB Enterprise Server 10.4.

MariaDB ColumnStore can be deployed as the analytics component of MariaDB's single stack Hybrid Transactional/Analytical Processing (HTAP) solution or as a standalone columnar database for interactive, ad hoc analytics at scale.

Installation

MariaDB Corporation provides a APT package repository for Debian 10.

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.

Retrieve your access token at https://customers.mariadb.com/downloads/token/ and substitute for customer_access_token in the following directions.

To configure APT package repositories:

$ sudo apt install wget

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

$ echo "fc429a488e372c0d714c705b1dceb245904bcba8e76516fc8504f7e57d3530fd  mariadb_es_repo_setup" \
    | sha256sum -c -

$ chmod +x mariadb_es_repo_setup

$ sudo ./mariadb_es_repo_setup --token="customer_access_token" --apply \
   --mariadb-server-version="10.4"

$ sudo apt update

To install MariaDB ColumnStore and package dependencies:

$ sudo apt install mariadb-server \
    mariadb-columnstore-platform mariadb-plugin-columnstore

Installation loads software to the system. This software requires post-install actions and configuration before the database server is ready for use.

Configuration

Server Configuration

MariaDB Enterprise Servers running as ColumnStore Instances 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.

For example, to configure MariaDB Enterprise Server via a configuration file:

[mariadb]
innodb_buffer_pool_size   = 20G

Useful system variables for MariaDB Enterprise Server include:

System Variable

Description

innodb_buffer_pool_size

Defines the amount of memory InnoDB reserves for the Buffer Pool. Set to no more than 16% of total memory on Servers that also run MariaDB ColumnStore.

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 need to 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. In order 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 upgraded 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 ColumnStore storage back-end.

Stop the Server

MariaDB ColumnStore requires that you stop MariaDB Enterprise Server before running the post-installation scripts.

$ sudo systemctl stop mariadb.service
$ sudo systemctl disable mariadb.service

Post-Installation Script

Run the columnstore-post-install script 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:

$ sudo postConfigure -qs

The postConfigure script performs the following configuration:

  • Configures ColumnStore Instance to operate in single-node mode.

  • Sets the system name.

  • Configures the storage mount to store data internally on the local file system.

  • Starts MariaDB ColumnStore.

Restart the System

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

$ sudo mcsadmin restartSystem

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 the privileges to access the necessary data.

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

GRANT SELECT ON *.* TO cross_engine@127.0.0.1 IDENTIFIED BY "cross_engine_passwd";

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 not any issues during startup.

Checking Server Status

To check the MariaDB Enterprise Server status, attempt to connect to it using the MariaDB Client on 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.11-5-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

To check the MariaDB ColumnStore system status, use mcsadmin to call the 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