Deploy MariaDB ColumnStore 5 with MariaDB Community Server 10.5

SELinux (RHEL, CentOS)

SELinux must be set to permissive mode before installing MariaDB ColumnStore.

1. Set SELinux to permissive mode by setting SELINUX=permissive in /etc/selinux/config.

   For example, the file will usually look like this after the change:

   # This file controls the state of SELinux on the system.
   # SELINUX= can take one of these three values:
   #     enforcing - SELinux security policy is enforced.
   #     permissive - SELinux prints warnings instead of enforcing.
   #     disabled - No SELinux policy is loaded.
   SELINUX=permissive
   # SELINUXTYPE= can take one of three values:
   #     targeted - Targeted processes are protected,
   #     minimum - Modification of targeted policy. Only selected processes are protected.
   #     mls - Multi Level Security protection.
   SELINUXTYPE=targeted
   

2. Reboot the system.

3. Confirm that SELinux is in permissive mode using getenforce:

   $ sudo getenforce
   

   Permissive
   

AppArmor (Debian, Ubuntu)

AppArmor must be disabled before installing MariaDB 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.
   

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 ColumnStore to use the S3 bucket later in the Configure the S3 Storage Manager section.

Install ColumnStore via YUM (RHEL, CentOS)

1. Configure the YUM package repository.

   MariaDB ColumnStore 5 is available on MariaDB Community Server 10.5.

   To configure YUM package repositories:

   $ sudo yum install curl
   

   $ curl -LsSO https://r.mariadb.com/downloads/mariadb_repo_setup
   

   $ echo "6083ef1974d11f49d42ae668fb9d513f7dc2c6276ffa47caed488c4b47268593 mariadb_repo_setup" \
       | sha256sum -c -
   

   $ chmod +x mariadb_repo_setup
   

   $ sudo ./mariadb_repo_setup \
      --mariadb-server-version="mariadb-10.5"
   

2. Install the EPEL repository:

   $ sudo yum install epel-release
   

3. Install some additional dependencies for ColumnStore:

   $ sudo yum install jemalloc
   

4. Install MariaDB ColumnStore and package dependencies:

   $ sudo yum install MariaDB-server MariaDB-backup \
      MariaDB-shared MariaDB-client \
      MariaDB-columnstore-engine
   

5. Configure MariaDB ColumnStore.

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

   See ColumnStore Configuration.

Install ColumnStore via APT (Debian, Ubuntu)

1. Configure the APT package repository.

   MariaDB ColumnStore 5 is available on MariaDB Community Server 10.5.

   To configure APT package repositories:

   $ sudo apt install curl
   

   $ curl -LsSO https://r.mariadb.com/downloads/mariadb_repo_setup
   

   $ echo "6083ef1974d11f49d42ae668fb9d513f7dc2c6276ffa47caed488c4b47268593 mariadb_repo_setup" \
       | sha256sum -c -
   

   $ chmod +x mariadb_repo_setup
   

   $ sudo ./mariadb_repo_setup \
      --mariadb-server-version="mariadb-10.5"
   

   $ sudo apt update
   

2. Install some additional dependencies for ColumnStore.

   On Debian 10 and Ubuntu 20.04, install the following:

   $ sudo apt install libjemalloc2
   

   On Debian 9 and Ubuntu 18.04, install the following:

   $ sudo apt install libjemalloc1
   

3. Install MariaDB ColumnStore and package dependencies:

   $ sudo apt install mariadb-server mariadb-backup \
      libmariadb3 mariadb-client \
      mariadb-plugin-columnstore
   

4. Configure MariaDB ColumnStore.

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

   See ColumnStore Configuration.

Community Server Configuration

MariaDB Community 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 Community 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 RHEL and CentOS, MariaDB's packages bundle the following configuration files:

• /etc/my.cnf

• /etc/my.cnf.d/client.cnf

• /etc/my.cnf.d/mysql-clients.cnf

• /etc/my.cnf.d/server.cnf

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

• /etc/my.cnf.d/

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

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 ColumnStore

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

   Mandatory system variables and options for single-node MariaDB 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 RHEL and CentOS, a good custom configuration file would be: /etc/my.cnf.d/z-custom-my.cnf

   • 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
   

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 Community Server 10.5 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. 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 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

Configure the S3 Storage Manager

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

To configure 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 ColumnStore Processes

The Community Server and 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 Community Server process and configure it to start automatically:

   $ sudo systemctl restart mariadb
   

   $ sudo systemctl enable mariadb
   

2. Start the MariaDB ColumnStore processes and configure them to start automatically:

   $ sudo systemctl restart mariadb-columnstore
   

   $ sudo systemctl enable mariadb-columnstore
   

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, PROCESS ON *.*
      TO 'cross_engine'@'127.0.0.1';
   

   GRANT SELECT, PROCESS ON *.*
      TO 'cross_engine'@'localhost';
   

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 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 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 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 SELinux (RHEL, CentOS)

We set SELinux to permissive mode in the SELinux section, but we have to create an SELinux policy for ColumnStore before re-enabling it. This will ensure that SELinux does not interfere with ColumnStore's functionality. A policy can be generated while SELinux is still in permissive mode using the audit2allow command.

1. To configure SELinux, you have to install the packages required for audit2allow.

   On RHEL 7 and CentOS 7, install the following:

   $ sudo yum install policycoreutils policycoreutils-python
   

   On RHEL 8, install the following:

   $ sudo yum install policycoreutils python3-policycoreutils policycoreutils-python-utils
   

2. Allow the system to run under load for a while to generate SELinux audit events.

3. After the system has taken some load, generate an SELinux policy from the audit events using audit2allow:

   $ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local
   

   If no audit events were found, this will print the following:

   $ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local
   Nothing to do
   

4. If audit events were found, the new SELinux policy can be loaded using semodule:

   $ sudo semodule -i mariadb_local.pp
   

5. Set SELinux to enforcing mode by setting SELINUX=enforcing in /etc/selinux/config:

   # This file controls the state of SELinux on the system.
   # SELINUX= can take one of these three values:
   #     enforcing - SELinux security policy is enforced.
   #     permissive - SELinux prints warnings instead of enforcing.
   #     disabled - No SELinux policy is loaded.
   SELINUX=enforcing
   # SELINUXTYPE= can take one of three values:
   #     targeted - Targeted processes are protected,
   #     minimum - Modification of targeted policy. Only selected processes are protected.
   #     mls - Multi Level Security protection.
   SELINUXTYPE=targeted
   

6. Reboot the system.

7. Confirm that SELinux is in enforcing mode using getenforce:

   $ sudo getenforce
   

Configure AppArmor (Debian, Ubuntu)

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

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

Community Server Administration

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

ColumnStore Administration

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

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.26-MariaDB MariaDB 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)]>