Infrastructure at your Service

In my previous blog, I talked about how to install the latest MariaDB Community Server 10.5.5 on CentOS 8 but now I will talk about the
latest version of MariaDB ColumnStore 1.5 which is not anymore a separate fork of MariaDB but rather a pluggable storage engine.  This allows
to move easily data from InnoDB to ColumnStore and it is also the first version to be released as a plugin for the community Server.

Preparation

MariaDB ColumnStore is a columnar storage engine enabling HTAP (Hybrid Transactionnal/Analytical Processing) and Cross-engine Queries.
MariaDB ColumnStore stores data by columns and not by rows. This enable quick analytical reporting over huge data volumes (Wharehouse).
As a plugin, the installation process has been really simplified. You deploy it by installing a single additional package.
But still some Linux kernel parameters, recommended by MariaDB, have to be changed to get the best performances with ColumnStore.
So, we create a dedicated sysctl settings file with the following content and load them.
[[email protected] ~]# vi /etc/sysctl.d/90-mariadb-columnstore.conf
# 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
[[email protected] ~]# sysctl --load=/etc/sysctl.d/90-mariadb-columnstore.conf
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_no_metrics_save = 1
net.core.netdev_max_backlog = 2500
vm.vfs_cache_pressure = 10
vm.swappiness = 10

One last thing before installing, we have to set SELinux to permissive mode if it is not already
[[email protected] ~]# sudo getenforce
Enforcing
[[email protected] ~]# sed -i 's/SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config
[[email protected] ~]# reboot
[[email protected] ~]# sudo getenforce
Permissive

Installation

We search first for the right package, install it and check again.
[[email protected] ~]# yum search mariadb-columnstore
Last metadata expiration check: 0:51:01 ago on Wed 09 Sep 2020 08:21:13 AM CEST.
======================================================================== Name & Summary Matched: mariadb-columnstore =====================================
MariaDB-columnstore-engine-debuginfo.x86_64 : Debug information for package MariaDB-columnstore-engine
============================================================================= Name Matched: mariadb-columnstore =====================================
MariaDB-columnstore-engine.x86_64 : MariaDB: a very fast and robust SQL database server
[[email protected] ~]# yum install MariaDB-columnstore-engine.x86_64
[[email protected] ~]# yum list installed|grep -i columnstore
MariaDB-columnstore-engine.x86_64 10.5.5-1.el8 @mariadb
MariaDB [(none)]> SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like '%columnstore%';
+---------------------+----------------+--------------------+--------------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_STATUS.     | PLUGIN_TYPE        |
+---------------------+----------------+--------------------+--------------------+
| Columnstore.        | 1.5            | ACTIVE             | STORAGE ENGINE.    |
| COLUMNSTORE_COLUMNS | 1.5            | ACTIVE             | INFORMATION SCHEMA |
| COLUMNSTORE_TABLES  | 1.5            | ACTIVE             | INFORMATION SCHEMA |
| COLUMNSTORE_FILES   | 1.5            | ACTIVE             | INFORMATION SCHEMA |
| COLUMNSTORE_EXTENTS | 1.5            | ACTIVE             | INFORMATION SCHEMA |
+---------------------+----------------+--------------------+--------------------+

Configuration

It is mandatory to set the system variable: character_set_server to utf8, so we insert it in the option file /etc/my.cnf.d/server.cnf and restart the MariaDB server.
MariaDB [(none)]> show variables like '%character_set_s%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
+--------------------------+----------------------------+
[[email protected] ~]# vi /etc/my.cnf.d/server.cnf
[mariadb-10.5] log_error = mariadbd.err
character_set_server = utf8
[[email protected] ~]# systemctl stop mariadb
[[email protected] ~]# systemctl start mariadb
MariaDB [(none)]> show variables like '%character_set_s%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
| character_set_system | utf8  |
+----------------------+-------+

Conclusion

Columnar database is a great database storage alternative to conventional database when you want to handle huge data volumes (VLDB).
It suits perfectly for analytical query processing, reporting, data mining & benchmarking.
MariaDB ColumnStore 1.5 is the Columnar Storage engine designed for these tasks, and as a storage engine plugin, the installation is quite easy.
So, if you plan to use a BI tool with an OLAP database and process big data, try MariaDB ColumnStore 1.5.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Saïd Mendi
Saïd Mendi

Consultant