Deploy Single-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 Single-node ColumnStore Deployment configuration.

These instructions detail how to deploy a single-node columnar database, which is suited for an analytical or OLAP workload that does not require high availability (HA). This deployment type is generally for non-production use cases, such as for development and testing.

MariaDB Platform Components

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



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:




  • 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



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 single-node deployments may require some additional configurations prior to installation.

Optimizing Linux Kernel Parameters

MariaDB ColumnStore performs best when certain Linux kernel parameters are optimized.

  1. Set the relevant kernel parameters in a sysctl configuration file. For proper change management, it is recommended to set them in a ColumnStore-specific configuration file.

    For example, create a /etc/sysctl.d/90-mariadb-columnstore.conf file with the following contents:

    # Increase the TCP max buffer size
    net.core.rmem_max = 16777216
    net.core.wmem_max = 16777216
    # Increase the TCP buffer limits
    # min, default, and max number of bytes to use
    net.ipv4.tcp_rmem = 4096 87380 16777216
    net.ipv4.tcp_wmem = 4096 65536 16777216
    # don't cache ssthresh from previous connection
    net.ipv4.tcp_no_metrics_save = 1
    # for 1 GigE, increase this to 2500
    # for 10 GigE, increase this to 30000
    net.core.netdev_max_backlog = 2500
    # optimize Linux to cache directories and inodes
    vm.vfs_cache_pressure = 10
    # minimize swapping
    vm.swappiness = 10
  2. Set the same kernel parameters at runtime using the sysctl command:

    $ sudo sysctl --load=/etc/sysctl.d/90-mariadb-columnstore.conf

Linux Security Module Considerations

It is recommended to disable the system's Linux Security Module (LSM) on each node during installation to avoid confusion and potential problems. The specific steps to disable the security module will depend on the platform.

In the Configuring the Linux Security Module section, we will configure the security module and restart it.

Character Encoding

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

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

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


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 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
    $ echo "957bc29576e8fd320fa18e35fa49b5733f3c8eeb4ca06792fb1f05e089c810ff  mariadb_es_repo_setup" \
        | sha256sum -c -
    $ chmod +x mariadb_es_repo_setup
    $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \
    $ sudo apt update
  3. Install MariaDB ColumnStore and package dependencies:

    $ sudo apt install mariadb-server \
        mariadb-columnstore-platform mariadb-plugin-columnstore
  4. Install some optional dependencies for ColumnStore.

    On Debian 10 and Ubuntu 20.04, install the following:

    $ sudo apt install libjemalloc2

    Note that jemalloc is not required, but it improves performance.

  5. Configure MariaDB ColumnStore.

    Installation only loads MariaDB ColumnStore to the system. MariaDB ColumnStore may require configuration and additional post-installation steps before the database server is ready for use.


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:

    log_error = mariadbd.err

Cross Engine Joins

When a cross engine join is executed, the ExeMgr process connects to the server using the root user with no password by default. MariaDB Enterprise Server 10.4 will reject this login attempt by default. If you plan to use Cross Engine Joins, you need to configure ColumnStore to use a different user account and password.

  1. Configure the Cross Engine Join credentials in the /etc/columnstore/Columnstore.xml file.

    The credentials are set in the modifying the child elements of the <CrossEngineSupport> element.

    For example, to configure ColumnStore to use the cross_engine user account to connect to the server at



    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.

  2. The cross_engine@ user account needs to be created on the server after it has been started. This step is described in the Create the Cross Engine Join User section.

S3 Storage Manager

MariaDB ColumnStore supports using S3-compatible storage. If you want to use S3-compatible storage, then you need to configure it.

  1. Edit /etc/columnstore/storagemanager.cnf:

    service = S3
    bucket = your_columnstore_bucket_name
    endpoint = your_s3_endpoint
    aws_access_key_id = your_s3_access_key_id
    aws_secret_access_key = your_s3_secret_key
    cache_size = your_local_cache_size
    path = your_local_cache_path
    • The default local cache size is 2 GB.

    • The default local cache path is /var/lib/columnstore/storagemanager/cache.

  2. Ensure that the local cache path has sufficient store space to store the local cache.


Installation of MariaDB ColumnStore and MariaDB 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.

  1. Stop the service:

    $ sudo systemctl stop mariadb
  2. Disable the service, so that it does not start up automatically:

    $ sudo systemctl disable mariadb

Post-Installation Script

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

  1. Run the postConfigure script on the Server:

    $ sudo postConfigure -qs
  2. When prompted, select "single" for a single-node deployment.

  3. When prompted, set the system name.

  4. When prompted, select "internal" to store data on the local file system.

  5. Once postConfigure has the information it needs, it starts MariaDB ColumnStore.

Restart the System

  1. Use mcsadmin restartSystem to restart MariaDB ColumnStore to clear the cache:

    $ sudo mcsadmin restartSystem y

Create the Cross Engine Join User

The credentials for cross engine joins were previously configured in the Cross Engine Joins section. The user account must also be created, and the user account must be granted the necessary privileges to access data.

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

    $ sudo mariadb
  2. Create the user account with the CREATE USER statement:

    CREATE USER 'cross_engine'@''
       IDENTIFIED BY "cross_engine_passwd";
  3. Grant the user account SELECT privileges on all databases with the GRANT statement:

       TO 'cross_engine'@'';

Configuring the Linux Security Module

If you stopped the Linux Security Module (LSM) on each node during installation, you can restart the module and configure it on each node.

The specific steps to configure the security module depend on the platform.


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




mcsadmin startSystem


mcsadmin shutdownSystem


mcsadmin restartSystem


mcsadmin getSystemStatus


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

  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