Deploy Multi-Node MariaDB Enterprise ColumnStore 1.4 with MariaDB Enterprise Server 10.4 on RHEL 7¶
These instructions detail the deployment of MariaDB Enterprise ColumnStore 1.4 with MariaDB Enterprise Server 10.4 on Red Hat Enterprise Linux 7 in a Multi-Node ColumnStore Deployment configuration.
These instructions detail how to deploy a multi-node columnar database, which is suited for an analytical or OLAP workload that requires high availability (HA).
MariaDB Platform Components¶
These instructions detail the deployment of the following MariaDB Platform components:
MariaDB Enterprise Server Components¶
These instructions detail the deployment of the following MariaDB Enterprise Server components:
MariaDB ColumnStore installations for multi-node deployments are configured from the initial Performance Module. For these post-installation scripts to connect to and configure the other Servers, systems hosting ColumnStore Instances require some additional configuration.
MariaDB ColumnStore requires ports 3306, 8600 to 8630, 8700, and 8800. On some operating systems, this may require configuring the firewall to permit TCP traffic over these ports.
The postConfigure script runs on the initial Performance Module and from this system connects over the network to all other Performance Modules to configure them to operate as back-end storage to MariaDB ColumnStore. To avoid confusion and potential problems, disable the firewall during installation.
Be sure to re-enable the firewall once installation is complete.
Prior to installing MariaDB Columnstore, it is necessary to set SELinux to permissive mode:
Set SELinux to permissive mode by setting
# 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
Reboot the system.
Confirm that SELinux is in permissive mode using
$ sudo getenforce
The postConfigure script uses SSH to connect from the initial Performance Module (which ColumnStore identifies internally as
pm1) to the other Servers in the deployment. For
pm1 to connect to the other Servers, you need to generate an SSH key and share it with the other hosts in the deployment.
First, generate the SSH key:
$ sudo ssh-keygen
Then, copy the SSH key to each Server in your deployment:
$ sudo ssh-copy-id -i ~/.ssh/id_rsa.pub 192.0.2.2
Once this is done, restart the SSH daemon on each Server:
$ sudo systemctl restart sshd
You can test the connection using the SSH client from the initial system to connect to the others:
$ ssh 192.0.2.2
MariaDB ColumnStore manages back-end storage using Performance Modules. When a MariaDB Enterprise Server goes down, the deployment loses access to the data stored in the local Performance Module.
Storage managers allow MariaDB ColumnStore to store data in network accessible file systems. When using a storage manager, the deployment can access a shared pool of Performance Modules, which remain accessible even if the Server goes down.
If you would like to use any sort of storage manager, you need to install and configure it prior to installing MariaDB ColumnStore.
MariaDB ColumnStore 1.4 supports three forms of external storage, configured by the postConfigure script:
Mounted directory in the local file system, shared by all Servers.
Network attached storage file system.
Simple Storage Service from AWS, provides object storage to each Server for MariaDB ColumnStore.
In multi-node ColumnStore, the system's character encoding can effect the behavior of the system. In general, it is recommended to use UTF-8. To set the locale, use
localedef on all nodes:
$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8
MariaDB Corporation provides a YUM package repository for Red Hat Enterprise Linux 7.
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 YUM (RHEL/CentOS)¶
Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for
customer_download_tokenin the following directions.
Configure the YUM package repository.
To configure YUM package repositories:
$ sudo yum install wget $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup $ echo "957bc29576e8fd320fa18e35fa49b5733f3c8eeb4ca06792fb1f05e089c810ff 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.4"
Install MariaDB ColumnStore and package dependencies:
$ sudo yum install MariaDB-server \ MariaDB-columnstore-platform MariaDB-columnstore-engine
Stop the Server and disable the service after installing the packages:
$ sudo systemctl stop mariadb $ sudo systemctl disable mariadb
MariaDB ColumnStore post-installation scripts fail if they find MariaDB Enterprise Server running on the system.
Configure MariaDB ColumnStore.
Installation only loads MariaDB ColumnStore to the system. MariaDB ColumnStore requires configuration before the database server is ready for use.
MariaDB Enterprise Server can be configured in the following ways:
If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.
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:
And on RHEL, CentOS, and SLES, custom configuration files from the following directories are read by default:
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
On RHEL, CentOS, and SLES, a good custom configuration file would be:
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] log_error = mysqld.err
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 must create a new user for this purpose and update the MariaDB
Columnstore.xml configuration file.
Columnstore.xml is dangerous and can have unexpected results. Do not edit this configuration unless you specifically need support for Cross Engine Joins.
/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. 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 instance online.
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 Performance Modules, which handle back-end storage processes for MariaDB ColumnStore.
columnstore-post-install script on each ColumnStore Instance to provision the system to host the storage back-end:
$ sudo columnstore-post-install
MariaDB ColumnStore provides a post-configuration script to configure the ColumnStore Instance. Post-configuration is only required on the first Server in your deployment (called
pm1 by convention). The postConfigure script connects over SSH to initialize Performance Modules on the other Servers.
Run the postConfigure script on the Server hosting the initial Performance Module (that is,
$ sudo postConfigure
The postConfigure script runs through a series of prompts to configure the ColumnStore deployment.
When prompted to configure the ColumnStore deployment for single- or multi-node, select multi-node.
Sets the system name.
Configures the storage mount to store data internally on the local file system, externally on a network mounted disk, or using an S3 or GlusterFS storage manager.
Configures the Performance Modules, setting the IP addresses and DBRoots for each.
Sets the password to use in establishing SSH connections to the Performance Modules.
Once postConfigure has the information it needs, it connects to each Server and sets up the Performance Module to match the configuration, then starts MariaDB ColumnStore.
Restart the System¶
$ sudo mcsadmin restartSystem y
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 privileges to access the necessary data.
If you have configured your system to perform Cross Engine Joins, you must also create a user with sufficient privileges to access that data. To create a user with the necessary privileges, use the CREATE USER and GRANT statements with the relevant credentials:
CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY "cross_engine_passwd"; GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1';
After installation, SELinux can be properly configured to handle ColumnStore. This can be done while SELinux is still in permissive mode using the
To configure SELinux, you have to install the packages required for
For systems that use YUM, execute the following:
$ sudo yum install policycoreutils policycoreutils-python
Allow the system to run under load for a while to generate SELinux audit events.
After the system has taken some load, generate an SELinux policy from the audit events using
$ 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
If audit events were found, then the new SELinux policy can be loaded using
$ sudo semodule -i mariadb_local.pp
Set SELinux to enforcing mode by setting
# 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
Reboot the system.
Confirm that SELinux is in enforcing mode using
$ sudo getenforce
MariaDB ColumnStore includes an administrative utility called mcsadmin, which you can use to start and stop the ColumnStore processes:
When you have MariaDB ColumnStore up and running, you should test it to ensure that it is in working order and that there were no issues during startup.
Checking Server Status¶
Connect to the server using MariaDB Client using the
$ 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¶
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 Module pm2 ACTIVE Wed Jan 8 23:14:11 2020
Additional information is available in the MariaDB Knowledge Base.