Upgrade a Single-Node MariaDB ColumnStore Deployment from 1.2 to 1.5 with MariaDB Community Server 10.5 on CentOS 7

These instructions detail the upgrade from MariaDB ColumnStore 1.2 to MariaDB ColumnStore 1.5 with MariaDB Community Server 10.5 on CentOS Linux 7 in a Single-node ColumnStore Deployment configuration.

In this upgrade process, the system is upgraded from MariaDB ColumnStore 1.2, which is bundled with a patched version of MariaDB Server, to MariaDB ColumnStore 1.5, which integrates with MariaDB Community Server 10.5 as a storage engine plugin.

When MariaDB ColumnStore is upgraded, the old version must be uninstalled and the new version installed. Additionally, the configuration of MariaDB ColumnStore 1.2 and earlier is not compatible with MariaDB ColumnStore 1.5. System variables and plugins have been renamed, which requires that you edit your configuration files to the new standard in addition to upgrading your software.

Data Backup

Occasionally, issues can be encountered during upgrades. These issues can even potentially corrupt the database's data files, preventing you from easily reverting to the old installation. We strongly advise performing a backup prior to upgrading. If an issue is encountered during the upgrade, you can use the backup to restore your MariaDB ColumnStore database to the old version. If the upgrade finishes without issue, the backup can be deleted.

  1. Run columnstoreBackup:

    $ columnstoreBackup -zv 192.0.2.1 /data/backups/pm1
    
  2. Confirm the successful completion of the backup operation.

  3. Test the backup.

Additional information on columnstoreBackup is available on the MariaDB Knowledge Base.

Uninstall Old Version

When upgrading MariaDB ColumnStore, ColumnStore plugins as well as the previous installation of MariaDB server must be uninstalled before attempting to install the new release.

Remove ColumnStore Plugins

MariaDB ColumnStore 1.5 includes several changes to the names of the plugins used by the storage engine. These changes make the plugin loads from MariaDB ColumnStore 1.2 incompatible with a 1.5 installation. Before removing the previous installation, these plugins must be removed. Removing these plugins ensures that when the upgraded Server starts, it doesn't raise errors attempting to load plugins under their old names.

  1. Connect to the Server through the ColumnStore 1.2 Client, mcsmysql:

    $ mcsmysql
    
  2. Execute the UNINSTALL PLUGIN to statement to remove the MariaDB ColumnStore plugins:

    UNINSTALL PLUGIN infinidb;
    UNINSTALL PLUGIN columnstore_tables;
    UNINSTALL PLUGIN columnstore_columns;
    UNINSTALL PLUGIN columnstore_extents;
    UNINSTALL PLUGIN columnstore_files;
    UNINSTALL PLUGIN columnstore;
    

Stop ColumnStore

Before the old version can be uninstalled, stop the current MariaDB ColumnStore process.

  1. Stop MariaDB ColumnStore with mcsadmin shutdownSystem:

    $ mcsadmin shutdownSystem y
    

Uninstall via YUM (RHEL/CentOS)

  1. Remove all MariaDB ColumnStore packages with the following command:

    $ sudo yum remove "mariadb-columnstore-*"
    

    Note that a wildcard character used in the command to ensure that all MariaDB ColumnStore packages are uninstalled. Confirm that this wildcard does not unintentionally refer to any of your custom applications.

  2. Verify that all MariaDB ColumnStore packages are uninstalled with the following command:

    $ rpm --query --all | grep -i -E "mariadb-columnstore"
    

    Note that the command should not return any results. If it does, remove the remaining packages individually.

System Preparation

Systems hosting ColumnStore Instances require some additional configuration 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.

Disabling the Linux Security Module with SELinux (RHEL/CentOS/SLES)

Prior to installing MariaDB Columnstore, it is necessary to set SELinux to permissive mode:

  1. Set SELinux to permissive mode by setting SELINUX=permissive 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=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
    

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
    

Installation

MariaDB Corporation provides a YUM package repository for CentOS Linux 7.

MariaDB Community Server 10.5 does not require additional software to operate as an analytics database with MariaDB ColumnStore.

Install via YUM (RHEL/CentOS)

  1. Configure the YUM package repository.

    To configure YUM package repositories:

    $ sudo yum install wget
    
    $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
    
    $ echo "2de6253842f230bc554d3f5ab0c0dbf717caffbf45ae6893740707961c8407b7 mariadb_repo_setup" \
        | sha256sum -c -
    
    $ chmod +x mariadb_repo_setup
    
    $ sudo ./mariadb_repo_setup \
       --mariadb-server-version="mariadb-10.5"
    
  2. Install MariaDB ColumnStore and package dependencies:

    $ sudo yum install MariaDB-server \
        MariaDB-columnstore-engine
    
  3. Install the EPEL repository.

  4. Install some optional dependencies for ColumnStore:

    $ sudo yum install jemalloc
    

    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.

Configuration

Configuration Compatibility

MariaDB ColumnStore 1.5 updates the prefix used in system variables. The infinidb_ prefix used in MariaDB ColumnStore 1.2 has been replaced with the columnstore_ prefix.

MariaDB ColumnStore 1.2 configuration files are not compatible with MariaDB ColumnStore 1.5.

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 RHEL, CentOS, and SLES, 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, CentOS, and SLES, custom configuration files from the following directories are read by default:

  • /etc/my.cnf.d/

Renamed Server Configuration

For platforms that use YUM or ZYpp as a package manager:

MariaDB Community Server's packages bundle several configuration files:

  • /etc/my.cnf

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

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

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

If your version of any of these configuration files contained any custom edits, then the package manager may save your edited version with the .rpmsave extension during the upgrade process. If you want to continue using your version with the custom edits, then you may need to move it back. For example, to move server.cnf back in place:

$ sudo mv /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.original
$ sudo mv /etc/my.cnf.d/server.cnf.rpmsave /etc/my.cnf.d/server.cnf

Configuring MariaDB

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

    Mandatory system variables and options for MariaDB ColumnStore include:

    System Variable/Option

    Description

    character_set_server

    Set this system variable to utf8

  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 RHEL, CentOS, and SLES, a good custom configuration file would be: /etc/my.cnf.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
    

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.

  1. Configure the Cross Engine Join credentials using 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
    
  2. The cross_engine@127.0.0.1 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:

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

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

Restore Data

MariaDB Community Server with MariaDB ColumnStore 1.5 uses a different directory structure from MariaDB ColumnStore 1.2. In order to restore your data, you need to reconfigure the Server to use the 1.2 directory locations.

Restoring Server Data

In MariaDB ColumnStore 1.2, MariaDB Server writes ColumnStore data to DBRoots and data from all other storage engines in a non-standard data directory located in root installations at /usr/local/mariadb/columnstore/mysql/db/. To restore the Server data from the 1.2 to the MariaDB Community Server 10.5 installation, you need to move the old data directory to the new location.

  1. Move the default data directory to an alternate location:

    $ sudo mv /var/lib/mysql /var/lib/mysql_default
    
  2. Move the data directory from MariaDB ColumnStore 1.2 to the new location:

    $ sudo mv /usr/local/mariadb/columnstore/mysql/db /var/lib/mysql
    

Restoring ColumnStore Data

In MariaDB ColumnStore 1.2, DBRoot files are located in the ColumnStore home directory. The convention for the file names is dataN:

  1. Identify the local DBRoots:

    $ sudo ls /usr/local/mariadb/columnstore | grep "data[0-9]+"
    data1
    
  2. For each DBRoot, move it from the directory used by the ColumnStore 1.2 installation to the one used by the ColumnStore 1.5 installation:

    $ sudo mv /usr/local/mariadb/columnstore/data1 /var/lib/columnstore/data1
    

Post Installation

Installing the MariaDB Community Server and MariaDB ColumnStore packages provides you with necessary packages to run the Server as a ColumnStore Instance, but only configures the Server as a Server. A few additional steps are needed to configure the MariaDB ColumnStore storage back-end.

Update the Bash Profile

MariaDB ColumnStore 1.2 shipped with a columnstoreAlias.sh script, which created a series of aliases for various commands.

Remove reference to this file from your .bash_profile and any copies in /etc/profile.d/ to ensure that the commands below call the MariaDB ColumnStore 1.5 binaries in /usr/bin and not the MariaDB ColumnStore 1.2 aliases.

Note, that to restore the environment variables, you will need to open a new shell after the above change.

Start the Server

The server and the ColumnStore processes can be started using the systemctl command.

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

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

    $ sudo systemctl start mariadb-columnstore
    $ sudo systemctl enable mariadb-columnstore
    

Update the Data Directory

  1. Execute mariadb-upgrade to update the data directory:

    $ sudo mariadb-upgrade
    

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'@'127.0.0.1'
       IDENTIFIED BY "cross_engine_passwd";
    
  3. Grant the user account SELECT privileges on all databases with the GRANT statement:

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

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.

Configuring the Linux Security Module with SELinux (RHEL/CentOS/SLES)

After installation, SELinux can be properly configured to handle ColumnStore. This can be done 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
    
  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, then 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, then 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
    

Administration

MariaDB Community 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

MariaDB ColumnStore also 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 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.4-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)]>