Deploy Single-Node MariaDB Enterprise ColumnStore 5.5 with MariaDB Enterprise Server 10.5 on Ubuntu 18.04 LTS

These instructions detail the deployment of MariaDB Enterprise ColumnStore 5.5 with MariaDB Enterprise Server 10.5 on Ubuntu 18.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:

Component

Description

MariaDB Enterprise Server 10.5

  • A 100% Open Source modern SQL database.

MariaDB Enterprise ColumnStore 5.5

  • A columnar storage engine for MariaDB Enterprise Server that provides distributed, columnar storage for scalable analytical processing and smart transactions (HTAP).

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

High Availability

Single-node Enterprise ColumnStore 5.5 does not support high availability.

If you want high availability, then deploy multi-node Enterprise ColumnStore 5.5 instead.

System Preparation

Systems hosting Enterprise ColumnStore deployments require some additional configuration prior to installation:

  1. Optimize Linux kernel parameters.

  2. Disable the Linux security module.

  3. Configure the character encoding.

  4. Optionally configure S3-compatible storage.

Optimize Linux Kernel Parameters

MariaDB Enterprise 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, we recommend setting them in an Enterprise ColumnStore-specific configuration file.

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

    # minimize swapping
    vm.swappiness = 10
    
    # optimize Linux to cache directories and inodes
    vm.vfs_cache_pressure = 10
    
    # 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
    
  2. Set the same kernel parameters at runtime using the sysctl command:

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

Disable the Linux Security Module

To avoid confusion and potential problems, we recommend disabling the system's Linux Security Module (LSM) during installation. The specific steps to disable the security module will depend on the platform.

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

Disable AppArmor (Debian/Ubuntu/SLES)

AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.

  1. Disable AppArmor:

    $ sudo systemctl disable apparmor
    
  2. Reboot the system.

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

    $ sudo aa-status
    

    Example output:

    apparmor module is loaded.
    0 profiles are loaded.
    0 profiles are in enforce mode.
    0 profiles are in complain mode.
    0 processes have profiles defined.
    0 processes are in enforce mode.
    0 processes are in complain mode.
    0 processes are unconfined but have a profile defined.
    

Note

Remember to configure and re-enable AppArmor after the installation is complete.

Configure the Character Encoding

When using MariaDB Enterprise 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
    

S3-Compatible Storage

MariaDB Enterprise ColumnStore can use S3-compatible storage to store its data. However, this functionality is optional.

We would recommend S3-compatible storage for all Enterprise ColumnStore users, because it can provide many benefits:

  • Secure: Most S3-compatible storage is encrypted-at-rest.

  • Scalable: Most S3-compatible storage is highly optimized for read and write scaling.

  • Resilient: Most S3-compatible storage is very low maintenance and highly available, since it relies on resilient cloud infrastructure.

  • Economical: Most S3-compatible storage is very inexpensive.

When S3-compatible storage is used, Enterprise ColumnStore caches data locally to improve performance.

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.

We will configure Enterprise ColumnStore to use the S3 bucket later in the Configure the S3 Storage Manager section.

Note

The specific steps to create the S3 bucket will depend on what S3-compatible storage you are using.

Enterprise ColumnStore Installation

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

MariaDB Enterprise 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.5 does not require any additional software to operate as a single-node analytics database.

Install Enterprise ColumnStore 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.

    MariaDB Enterprise ColumnStore 5.5 is available on MariaDB Enterprise Server 10.5.

    To configure APT package repositories:

    $ sudo apt install wget
    
    $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
    
    $ echo "c78db828709d94876406a0ea346f13fbc38e73996795903f40e3c21385857dd4  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.5"
    
    $ sudo apt update
    
  3. Install some additional dependencies for Enterprise ColumnStore.

    On Debian 9, Ubuntu 16.04 and Ubuntu 18.04, install the following:

    $ sudo apt install libjemalloc1
    
  4. Install MariaDB Enterprise ColumnStore and package dependencies:

    $ sudo apt install mariadb-server mariadb-backup \
       libmariadb3 mariadb-client \
       mariadb-plugin-columnstore
    
  5. Configure MariaDB Enterprise ColumnStore.

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

Enterprise ColumnStore Configuration

MariaDB Enterprise ColumnStore requires configuration after it is installed. The configuration file location depends on your operating system.

Enterprise 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 for Enterprise ColumnStore

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

    Mandatory system variables and options for single-node MariaDB Enterprise ColumnStore include:

    System Variable/Option

    Description

    character_set_server

    Set this system variable to utf8

    collation_server

    Set this system variable to utf8_general_ci

    columnstore_use_import_for_batchinsert

    Set this system variable to ALWAYS to always use cpimport for LOAD DATA INFILE and INSERT...SELECT statements.

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

    We recommend not making custom changes to one of the bundled configuration files. Instead, 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, 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                              = mariadbd.err
    character_set_server                   = utf8
    collation_server                       = utf8_general_ci
    columnstore_use_import_for_batchinsert = ALWAYS
    

Configure 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.5 will reject this login attempt by default. If you plan to use Cross Engine Joins, you need to configure Enterprise ColumnStore to use a different user account and password. These directions are for configuring the cross engine join user. Directions for creating the cross engine join user are in the Create the Cross Engine Join User section.

To configure cross engine joins, perform the following steps, use the mcsSetConfig command.

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

$ 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

Note

Please choose a password that meets 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.

By default, MariaDB Enterprise Server installs the simple_password_check plugin. Its requirements are configured by the simple_password_check_digits, simple_password_check_letters_same_case, simple_password_check_minimal_length, and simple_password_check_other_characters system variables.

Configure the S3 Storage Manager

MariaDB Enterprise ColumnStore can use S3-compatible storage, but it is not required. S3-compatible storage must be configured before it can be used.

To configure Enterprise ColumnStore to use S3-compatible storage, edit /etc/columnstore/storagemanager.cnf:

[ObjectStorage]

service = S3

[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
# iam_role_name = your_iam_role
# sts_region = your_sts_region
# sts_endpoint = your_sts_endpoint

[Cache]
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.

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

  • The bucket option must be set to the name of the bucket that you created in the Create an S3 Bucket step.

  • To use an IAM role, you must also uncomment and set iam_role_name, sts_region, and sts_endpoint.

Start the Enterprise ColumnStore Processes

The Enterprise Server and Enterprise ColumnStore processes can be started using the systemctl command. In case the processes were started during the installation process, use the restart command to ensure that the processes pick up the new configuration. Perform the following procedure.

  1. Start the MariaDB Enterprise Server process and configure it to start automatically:

    $ sudo systemctl restart mariadb
    $ sudo systemctl enable mariadb
    
  2. Start the MariaDB Enterprise ColumnStore processes and configure them to start automatically:

    $ sudo systemctl restart mariadb-columnstore
    $ sudo systemctl enable mariadb-columnstore
    

Create User Accounts

For single-node Enterprise ColumnStore deployments, only a single user account needs to be created.

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 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'@'127.0.0.1'
       IDENTIFIED BY "cross_engine_passwd";
    
    CREATE USER 'cross_engine'@'localhost'
       IDENTIFIED BY "cross_engine_passwd";
    

    Note

    Please choose the same user name and password that was configured in the Cross Engine Joins section.

  3. Grant the user account SELECT privileges on all databases with the GRANT statement:

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

Bulk Import Data

Now that the Enterprise ColumnStore system is running, you can bulk import your data.

Import the Schema

Before data can be imported into the tables, the schema needs to be created.

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

    $ sudo mariadb
    
  2. For each database that you are importing, create the database with the CREATE DATABASE statement:

    CREATE DATABASE inventory;
    
  3. For each table that you are importing, create the table with the CREATE TABLE 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;
    

cpimport

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

To import your data from a TSV (tab-separated values) file with 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 that is designed to efficiently load data in bulk.

To import your data from a TSV (tab-separated values) file with 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 SELECT statement, and then pipe the results into cpimport, which is a command-line utility that is designed to efficiently load data in bulk.

To import your data from a remote MariaDB database:

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

Configure the Linux Security Module

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

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

Configure AppArmor (Debian/Ubuntu/SLES)

We disabled AppArmor in the Disable AppArmor section, but we have to create an AppArmor profile for Enterprise ColumnStore before re-enabling it. This will ensure that AppArmor does not interfere with Enterprise ColumnStore's functionality.

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

Administration

Enterprise ColumnStore has several components. Each of those components needs to be administered.

Enterprise Server Administration

MariaDB Enterprise Server uses systemctl to start and stop the server processes:

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

Enterprise ColumnStore Administration

MariaDB Enterprise ColumnStore uses systemctl to start and stop the ColumnStore processes:

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

Testing

When you have MariaDB Enterprise 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.5.8-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)]>