Installing MariaDB ColumnStore on Ubuntu, for production

Let’s see how to install MariaDB ColumnStore (single node) on Ubuntu. Let’s also prepare the system to run ColumnStore with good performance, as we should always do in production.

The following steps should work on any modern Ubuntu version up to and including 24.04 LTS.

Installing and configuring MariaDB and ColumnStore

First, let’s configure the server parameters:

FILE=$( ls -1 /etc/sysctl.d/*.conf | tail -1 )
echo 'vm.swappiness=1' >> $FILE
echo 'net.core.netdev_max_backlog = 1000' >> $FILE
echo 'net.core.rmem_max = 212992' >> $FILE
echo 'net.core.wmem_max = 212992' >> $FILE
echo 'vm.vfs_cache_pressure = 100' >> $FILE

Also, change the system characterset to the only one that is supported by MariaDB ColumnStore:

localedef -i en_US -f UTF-8 en_US.UTF-8

apparmor can be disabled:

systemctl disable apparmor

Let’s install che dependencies. The following commands are suitable for any Ubuntu system, including minimal systems (e.g., containers).

export DEBIAN_FRONTEND=noninteractive
apt-get update -yq
apt-get install -yq \
    apt-transport-https \
    curl \
    ca-certificates \
    gpg \
    tzdata \
    jq

Install MariaDB signature and the official repository:

mkdir -p /etc/apt-get/keyrings
REPO_URL="deb [signed-by=/etc/apt/keyrings/mariadb-keyring.pgp] https://deb.mariadb.org/<mariadb_version>/ubuntu <ubuntu_codename> main"
REPO_FILE="/etc/apt/sources.list.d/mariadb.list"
curl -o ${KEY_FILE} 'https://mariadb.org/mariadb_release_signing_key.pgp'
echo ${REPO_URL} > ${REPO_FILE}

Remember to replace <mariadb_version> with the desired MariaDB version and <ubuntu_codename> with the codename of the Ubuntu version in use. For example:

deb [signed-by=/etc/apt/keyrings/mariadb-keyring.pgp] https://ftp.heanet.ie/mirrors/mariadb/repo/11.4/ubuntu noble main

If in doubt, check this tool to find out the proper version and codename.

Now let’s install MariaDB and MariaDB ColumnStore:

apt-get update -yq
apt-get install -yq \
    mariadb-server \
    mariadb-backup \
    mariadb-plugin-columnstore

jemalloc is already installed. Let’s configure the MariaDB service to use it. This is important with ColumnStore, to avoid some memory leaks. Locate jemalloc:

find /usr/lib -name "libjemalloc.so.*"

In the file /lib/systemd/system/mariadb.service, find the [Service] option group. If it doesn’t have a line setting LD_PRELOAD, add it, otherwise modify it to add jemalloc’s path:

Environment=LD_PRELOAD=<jemalloc_path>

Reload the configuration:

systemctl daemon-reload

Configure MariaDB as you like. An optimal configuration is outside of the scope of this article. But make sure that these variables are set as follows:

[mariadbd]
# required by ColumnStore
character_set_server= utf8
collation_server= utf8_general_ci
# later you'll be able to scale by adding replicas
# without a restart
log_bin= mariadb-bin
log_bin_index= mariadb-bin.index"
server_id=1 # or any other unique id

In ColumnStore, for now you simply want to make sure that the cache has an acceptable size. In the file /etc/columnstore/storagemanager.cnf, set:

cache_size = 2g

If you wish to use MariaDB functions that are aware of timezones, make sure that the timezone tables are loaded.

mariadb-tzinfo-to-sql /usr/share/zoneinfo | mariadb -D mysql

Now restart MariaDB. For some reason, apparently we also need to explicitly enable it.

systemctl enable mariadb
systemctl restart mariadb

Consider installing mycli, a CLI interface that is more advanced than MariaDB default client:

sudo pip3 install --no-input mycli

Verifying the instance

Verify that the ColumnStore engine is installed:

SHOW STATUS LIKE 'columnstore_version';

Verify that you’re able to write data in a ColumnStore table:

CREATE SCHEMA IF NOT EXISTS cs_test;
USE cs_test;
CREATE TABLE cst (i INT) ENGINE ColumnStore;
INSERT INTO cst VALUES (1);

If you didn’t get any error, your MariaDB ColumnStore instance is ready!

I’ll hold an in-depth 1-day MariaDB ColumnStore training on October 9. With the FEDERICO promo code, it will cost you only £100 instead of the ordinary price of £300. After the training, I’ll remain available to all attendees during 30 days in the chat to help out and answer the questions at no additional charge, this support is included in the price.

Federico Razzoli