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
\nlatest version of MariaDB ColumnStore 1.5 which is not anymore a separate fork of MariaDB but rather a pluggable storage engine.\u00a0 This allows
\nto move easily data from InnoDB to ColumnStore and it is also the first version to be released as a plugin for the community Server.
\n<\/p>\n
MariaDB ColumnStore is a columnar storage engine enabling HTAP (Hybrid Transactionnal\/Analytical Processing) and Cross-engine Queries. We search first for the right package, install it and check again. It is mandatory to set the system variable: character_set_server<\/strong> to utf8<\/strong>, so we insert it in the option file \/etc\/my.cnf.d\/server.cnf and restart the MariaDB server. Columnar database is a great database storage alternative to conventional database when you want to handle huge data volumes (VLDB). 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.\u00a0 This allows to move easily data from InnoDB […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[2108],"type_dbi":[],"class_list":["post-14708","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-olap-mariadb-columnstore-storage-engine-htap-centos8"],"acf":[],"yoast_head":"\n
\nMariaDB ColumnStore stores data by columns and not by rows. This enable quick analytical reporting over huge data volumes (Wharehouse).
\nAs a plugin, the installation process has been really simplified. You deploy it by installing a single additional package.
\nBut still some Linux kernel parameters, recommended by MariaDB, have to be changed to get the best performances with ColumnStore.
\nSo, we create a dedicated sysctl settings file with the following content and load them.
\n[root@CentOs8 ~]# vi \/etc\/sysctl.d\/90-mariadb-columnstore.conf
\n# Increase the TCP max buffer size
\nnet.core.rmem_max = 16777216
\nnet.core.wmem_max = 16777216
\n# Increase the TCP buffer limits
\n# min, default, and max number of bytes to use
\nnet.ipv4.tcp_rmem = 4096 87380 16777216
\nnet.ipv4.tcp_wmem = 4096 65536 16777216
\n# don't cache ssthresh from previous connection
\nnet.ipv4.tcp_no_metrics_save = 1
\n# for 1 GigE, increase this to 2500
\n# for 10 GigE, increase this to 30000
\nnet.core.netdev_max_backlog = 2500
\n# optimize Linux to cache directories and inodes
\nvm.vfs_cache_pressure = 10
\n# minimize swapping
\nvm.swappiness = 10
\n[root@CentOs8 ~]# sysctl --load=\/etc\/sysctl.d\/90-mariadb-columnstore.conf
\nnet.core.rmem_max = 16777216
\nnet.core.wmem_max = 16777216
\nnet.ipv4.tcp_rmem = 4096 87380 16777216
\nnet.ipv4.tcp_wmem = 4096 65536 16777216
\nnet.ipv4.tcp_no_metrics_save = 1
\nnet.core.netdev_max_backlog = 2500
\nvm.vfs_cache_pressure = 10
\nvm.swappiness = 10<\/code>
\nOne last thing before installing, we have to set SELinux to permissive mode if it is not already
\n[root@CentOs8 ~]# sudo getenforce
\nEnforcing
\n[root@CentOs8 ~]# sed -i 's\/SELINUX=enforcing\/SELINUX=permissive\/' \/etc\/selinux\/config
\n[root@CentOs8 ~]# reboot
\n[root@CentOs8 ~]# sudo getenforce
\nPermissive<\/code><\/p>\nInstallation<\/h2>\n
\n[root@CentOs8 ~]# yum search mariadb-columnstore
\nLast metadata expiration check: 0:51:01 ago on Wed 09 Sep 2020 08:21:13 AM CEST.
\n======================================================================== Name & Summary Matched: mariadb-columnstore =====================================
\nMariaDB-columnstore-engine-debuginfo.x86_64 : Debug information for package MariaDB-columnstore-engine
\n============================================================================= Name Matched: mariadb-columnstore =====================================
\nMariaDB-columnstore-engine.x86_64 : MariaDB: a very fast and robust SQL database server
\n[root@CentOs8 ~]# yum install MariaDB-columnstore-engine.x86_64
\n[root@CentOs8 ~]# yum list installed|grep -i columnstore
\nMariaDB-columnstore-engine.x86_64 10.5.5-1.el8 @mariadb
\nMariaDB [(none)]> SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE FROM INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like '%columnstore%';
\n+---------------------+----------------+--------------------+--------------------+
\n| PLUGIN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PLUGIN_VERSION | PLUGIN_STATUS. \u00a0 \u00a0 | PLUGIN_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n+---------------------+----------------+--------------------+--------------------+
\n| Columnstore.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1.5 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 | ACTIVE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | STORAGE ENGINE.\u00a0\u00a0\u00a0 |
\n| COLUMNSTORE_COLUMNS | 1.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ACTIVE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | INFORMATION SCHEMA |
\n| COLUMNSTORE_TABLES\u00a0 | 1.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ACTIVE \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | INFORMATION SCHEMA |
\n| COLUMNSTORE_FILES\u00a0\u00a0 | 1.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ACTIVE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | INFORMATION SCHEMA |
\n| COLUMNSTORE_EXTENTS | 1.5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | ACTIVE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | INFORMATION SCHEMA |
\n+---------------------+----------------+--------------------+--------------------+<\/code><\/p>\nConfiguration<\/h2>\n
\nMariaDB [(none)]> show variables like '%character_set_s%';
\n+--------------------------+----------------------------+
\n| Variable_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 | Value \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 |
\n+--------------------------+----------------------------+
\n| character_set_server \u00a0 \u00a0 | latin1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |
\n| character_set_system \u00a0 \u00a0 | utf8 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n+--------------------------+----------------------------+
\n[root@CentOs8 ~]# vi \/etc\/my.cnf.d\/server.cnf
\n[mariadb-10.5]
\nlog_error = mariadbd.err
\ncharacter_set_server = utf8
\n[root@CentOs8 ~]# systemctl stop mariadb
\n[root@CentOs8 ~]# systemctl start mariadb
\nMariaDB [(none)]> show variables like '%character_set_s%';
\n+----------------------+-------+
\n| Variable_name \u00a0 \u00a0 \u00a0\u00a0 | Value |
\n+----------------------+-------+
\n| character_set_server | utf8\u00a0 |
\n| character_set_system | utf8\u00a0 |
\n+----------------------+-------+<\/code><\/p>\nConclusion<\/h2>\n
\nIt suits perfectly for analytical query processing, reporting, data mining & benchmarking.
\nMariaDB ColumnStore 1.5 is the Columnar Storage engine designed for these tasks, and as a storage engine plugin, the installation is quite easy.
\nSo, if you plan to use a BI tool with an OLAP database and process big data, try MariaDB ColumnStore 1.5.<\/p>\n","protected":false},"excerpt":{"rendered":"