MariaDB Columnstore Deep Dive: Architecture, Performance, and Deployment

Introduction

MariaDB has integrated InfiniDB as an independent storage engine called Columnstore , providing a column‑store analytical database capable of petabyte‑scale (P‑scale) big‑data analysis. With the rise of Hadoop and OLAP products such as Oracle 12c In‑Memory and SAP HANA, Columnstore adds a powerful open‑source OLAP option to the MySQL ecosystem.

Community and Release Status

MariaDB has merged InfiniDB into its codebase and recently released a GA version, indicating strong community support and future development potential.

Architecture

Columnstore supports distributed deployment, leveraging multiple servers, CPUs, and I/O channels for fast OLAP queries. The architecture is split into a compute layer and a storage layer, both horizontally scalable.

Compute Layer Processes

mysqld (MariaDB)

: SQL entry point that collects user requests and stores metadata. Execution Manager: Receives the syntax tree from mysqld, transforms it into a job list (including optimization, data retrieval, HASH‑JOIN, aggregation, grouping). Core of the UserModule. DMLProc: Executes DML statements on the designated Performance Module. DDLProc: Executes DDL statements on the designated Performance Module. Performance Module: Handles task scheduling from the Execution Manager, performs distributed scans, HASH‑JOIN, and aggregation.

Storage Layer

The storage layer currently supports local disks and shared storage, with plans to add HDFS and GlusterFS for higher reliability.

Performance Highlights

Percona’s performance tests show that InfiniDB outperforms traditional InnoDB in several dimensions:

Adding a column: ALTER TABLE t_innodb ADD c3 VARCHAR(64); takes ~15 min for 99 M rows, while ALTER TABLE t_InfiniDB ADD c51 VARCHAR(56); completes in ~12 s.

Bulk insert speed: InfiniDB inserts are up to hundreds of times faster than InnoDB for large datasets.

Query speed: Benchmarks report up to 150× faster query execution on billion‑row tables.

Space efficiency: Columnstore can reduce storage usage by 20‑50 % compared to InnoDB for wide tables.

Sample benchmark output:

mysql> ALTER TABLE t_innodb ADD c3 VARCHAR(64);
Query OK, 99,797,224 rows affected (15 min 19.50 sec)

mysql> ALTER TABLE t_InfiniDB ADD c51 VARCHAR(56);
Query OK, 0 rows affected (12.15 sec)

Installation and Kernel Optimization

Before installing Columnstore, adjust kernel parameters to improve network and I/O performance:

/sbin/ifconfig eth0 txqueuelen 10000
# /etc/sysctl.conf additions
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
# For 10 GigE use:
# net.core.netdev_max_backlog = 30000

Installation steps (run as root):

systemctl stop firewalld.service
systemctl disable firewalld.service
yum erase MariaDB-server MariaDB-libs -y
yum -y groupinstall "Development Tools"
yum -y install cmake systemd-libs ncurses-devel libxml++-devel readline-devel boost expect perl perl-DBI openssl zlib
HOME=/usr/local/
cd /root/infi/mysql
./configure --prefix=$HOME/Calpont/mysql
make && make install
cd /root/infi/InfiniDB
./configure --prefix=$HOME/Calpont
make && make install
/usr/local/Calpont/bin/post-install
/usr/local/Calpont/bin/postConfigure
/usr/local/Calpont/bin/calpontAlias

Start the service:

/usr/local/Calpont/mysql/mysql-Calpont start > /tmp/actionMysqlCalpont.log 2>&1
service InfiniDB status

Operational Commands

Columnstore provides a set of mcsadmin utilities for cluster management: mcsadmin adddbroot 2 – add a DBRoot. mcsadmin assignPmDBRootConfig 3 pm1 – assign DBRoot to a PM. mcsadmin addModule pm 2 MYHST1,MYHST2 mypwd – add a module. mcsadmin getSystemStatus – view overall system status. mcsadmin getProcessStatus – view process status. mcsadmin shutdownSystem / mcsadmin startSystem – control the whole cluster. mcsadmin getSystemNetworkConfig, mcsadmin getModuleConfig – retrieve configuration.

Configuration and Tuning

Key configuration parameters (found in Columnstore.xml) include:

InfiniDB_compression_type = n

AllowDiskBasedJoin

TempFileCompression

TempFilePath

– recommended on a dedicated partition. InfiniDB_um_mem_limit – maximum memory for joins. InfiniDB_use_import_for_batchinsert = 1 – enables faster batch inserts via LOAD DATA INFILE or cpimport.

Example batch import command:

cpimport dbName tblName [loadFile]
cpimport -m3 mytest mytable /home/mydata/mytable.tbl

Enable local PM reads: InfiniDB_local_query = 1 Retrieve partition information:

select calShowPartitionsByValue('tbl_name','col_name','start_value','end_value');

SQL compatibility mode can be set with:

set InfiniDB_vtable_mode = n

Common Pitfalls

Auto‑increment columns must be marked in the comment: COMMENT='autoincrement=orderkey,1'.

MySQL limits a single row to 65 536 bytes, which restricts the maximum number of columns.

BI Compatibility

Because Columnstore uses the standard MySQL protocol, popular BI tools such as Tableau can connect directly without additional drivers.

Conclusion

MariaDB Columnstore scales to hundreds of server nodes, supports petabyte‑scale data volumes, integrates seamlessly with mature BI platforms, allows DML updates, offers source‑level customization, and re‑introduces SQL/NoSQL capabilities to the OLAP landscape.