Upgrade a Single-Node MariaDB ColumnStore Deployment from 1.2 to 5.5 with MariaDB Community Server 10.5 on SLES 12
Topics on This Page:
These instructions detail the upgrade from MariaDB ColumnStore 1.2 to MariaDB ColumnStore 5.5 with MariaDB Community Server 10.5 on SUSE Linux Enterprise Server 12 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 5.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 5.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.
Community Server Components
These instructions detail the deployment of the following MariaDB Community Server components:
Component |
Description |
---|---|
|
Term Definitions
Term |
Definition |
---|---|
columnar database |
|
row database |
|
High Availability
Single-node ColumnStore 5.5 does not support high availability.
If you want high availability, then upgrade to multi-node Enterprise ColumnStore 5.5 instead.
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. It is generally best to perform a backup prior to upgrading. If an issue is encountered during the upgrade, you can use the backup to restore your old data with MariaDB ColumnStore 1.2. If the upgrade finishes without issue, the backup can be deleted.
Run
columnstoreBackup
:$ columnstoreBackup -zv 192.0.2.1 /data/backups/pm1
Confirm the successful completion of the backup operation.
Test the backup.
Additional information on columnstoreBackup
is available on the MariaDB Knowledge Base.
Dump ColumnStore Tables
When upgrading from MariaDB ColumnStore 1.2 to MariaDB ColumnStore 5.5, it is best to dump your data from the old version and reload your data into the new version. Before you start the upgrade, you should dump your ColumnStore tables.
Connect to the Server through the MariaDB ColumnStore 1.2 client, which is called
mcsmysql
:$ mcsmysql
For each table that you want to migrate, dump the table's schema.
The SHOW CREATE TABLE statement can be used:
SHOW CREATE TABLE inventory.products;
For each table that you want to migrate, dump the table's data.
The SELECT statement can be used with the
INTO OUTFILE
clause to dump the data to a TSV (tab-separated values) file:SELECT * FROM inventory.products INTO OUTFILE '/tmp/inventory-products.tsv';
Note
The dumps will be imported in the Bulk Import Data section.
Uninstall Old Version
When upgrading to MariaDB ColumnStore 5.5, the MariaDB ColumnStore 1.2 packages must be uninstalled before attempting to install the new release.
Stop ColumnStore
Before the old version can be uninstalled, stop the current MariaDB ColumnStore 1.2 process.
Stop MariaDB ColumnStore with mcsadmin shutdownSystem:
$ mcsadmin shutdownSystem y
Uninstall ColumnStore 1.2 via ZYpp (SLES)
Remove all MariaDB ColumnStore 1.2 packages with the following command:
$ sudo zypper remove "mariadb-columnstore-*"
Note that a wildcard character used in the command to ensure that all MariaDB ColumnStore 1.2 packages are uninstalled. Confirm that this wildcard does not unintentionally refer to any of your custom applications.
Verify that all MariaDB ColumnStore 1.2 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 deployments require some additional configuration prior to installation:
Optimize Linux Kernel Parameters
MariaDB ColumnStore performs best when certain Linux kernel parameters are optimized.
Set the relevant kernel parameters in a sysctl configuration file. For proper change management, we recommend setting them in a ColumnStore-specific configuration file.
For example, create a
/etc/sysctl.d/90-mariadb-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
Set the same kernel parameters at runtime using the
sysctl
command:$ sudo sysctl --load=/etc/sysctl.d/90-mariadb-columnstore.conf
LSM Configuration for Install
To avoid confusion and potential problems, we recommend configuring the system's Linux Security Module (LSM) during installation. The specific steps to configure 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.
SELinux (RHEL/CentOS/SLES)
SELinux must be set to permissive
mode before installing MariaDB ColumnStore.
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
Reboot the system.
Confirm that SELinux is in
permissive
mode usinggetenforce
:$ sudo getenforce Permissive
Note
Remember to configure and re-enable SELinux after the installation is complete.
AppArmor (Debian/Ubuntu/SLES)
AppArmor must be disabled before installing MariaDB ColumnStore.
Disable AppArmor:
$ sudo systemctl disable apparmor
Reboot the system.
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 ColumnStore, it is recommended to set the system's locale to UTF-8.
Set the system's locale to
en_US.UTF-8
by executinglocaledef
:$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8
S3-Compatible Storage
MariaDB ColumnStore can use S3-compatible storage to store its data. However, this functionality is optional.
We would recommend S3-compatible storage for all 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, 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 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.
ColumnStore Installation
MariaDB Corporation provides a ZYpp package repository for SUSE Linux Enterprise Server 12.
MariaDB ColumnStore ships as a storage engine plugin for MariaDB Community Server and a platform engine to handle back-end storage processes. MariaDB Community Server 10.5 does not require any additional software to operate as a single-node analytics database.
Install ColumnStore via ZYpp (SLES)
Configure the ZYpp package repository.
MariaDB ColumnStore
5.5
is available on MariaDB Community Server10.5
.To configure ZYpp package repositories:
$ sudo zypper install wget $ wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup $ echo "7a24f5580421fd353dc22c5439001bdaec86c54ed911c80e5482f62921125ac8 mariadb_repo_setup" \ | sha256sum -c - $ chmod +x mariadb_repo_setup $ sudo ./mariadb_repo_setup \ --mariadb-server-version="mariadb-10.5"
Install some additional dependencies for ColumnStore:
$ sudo zypper install jemalloc
Install MariaDB ColumnStore and package dependencies:
$ sudo zypper install MariaDB-server MariaDB-backup \ MariaDB-shared MariaDB-client \ MariaDB-columnstore-engine
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.
ColumnStore Configuration
MariaDB ColumnStore requires configuration after it is installed. The configuration file location depends on your operating system.
Configuration Compatibility
MariaDB ColumnStore 5.5 changes 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 5.5.
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, 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/
Configuring MariaDB for ColumnStore
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
Set this system variable to
utf8
Set this system variable to
utf8_general_ci
Set this system variable to
ALWAYS
to always use cpimport for LOAD DATA INFILE and INSERT...SELECT statements.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, CentOS, and SLES, a good custom configuration file would be:
/etc/my.cnf.d/z-custom-my.cnf
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 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
Note
Please choose a password that meets your organization's password policies. If your MariaDB Community Server instance has a password validation plugin installed, then the password should also meet the configured requirements.
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
, andsts_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.
Start the MariaDB Community Server process and configure it to start automatically:
$ sudo systemctl restart mariadb $ sudo systemctl enable mariadb
Start the MariaDB ColumnStore processes and configure them to start automatically:
$ sudo systemctl restart mariadb-columnstore $ sudo systemctl enable mariadb-columnstore
Create User Accounts
For single-node 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.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
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.
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 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.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
For each database that you are importing, create the database with the CREATE DATABASE statement:
CREATE DATABASE inventory;
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 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 SELinux (RHEL/CentOS/SLES)
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.
To configure SELinux, you have to install the packages required for
audit2allow
.On SLES, install the following:
$ sudo zypper install policycoreutils python3-policycoreutils
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
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
If audit events were found, the new SELinux policy can be loaded using
semodule
:$ sudo semodule -i mariadb_local.pp
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
Reboot the system.
Confirm that SELinux is in enforcing mode using
getenforce
:$ sudo getenforce
Configure AppArmor (Debian/Ubuntu/SLES)
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.
Administration
ColumnStore has several components. Each of those components needs to be administered.
Community Server Administration
MariaDB Community Server uses systemctl
to start and stop the server processes:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
ColumnStore Administration
MariaDB ColumnStore uses systemctl
to start and stop the ColumnStore processes:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
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
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.9-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)]>