Databases Azure

MariaDB ColumnStore on Ubuntu 24.04 on Azure User Guide

Last updated: 2026-07-26 | Product: MariaDB ColumnStore on Ubuntu 24.04 LTS on Azure

Overview

This guide covers the deployment and operation of MariaDB ColumnStore on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

ColumnStore is a columnar storage engine for MariaDB. It stores each column separately and scans them in parallel, which turns aggregate queries over large fact tables into a fraction of the I/O a row-store would need — while keeping the interface plain SQL. You do not learn a new query language, a new driver, or a new client: anything that already speaks MariaDB speaks ColumnStore, and you choose columnar storage per table with ENGINE=ColumnStore.

ColumnStore is an engine, not a server, so it cannot run on its own. This image therefore ships MariaDB Server 11.8 LTS together with the vendor-built mariadb-plugin-columnstore package from MariaDB's official repository, which hard-depends on the exact matching server version. Server and engine can never drift apart, and neither is compiled from source.

Row-store and columnar tables live side by side in the same server. A typical shape is InnoDB for the small dimension tables you update constantly, ColumnStore for the large append-mostly fact tables you aggregate over.

What is included:

MariaDB Server 11.8 LTS from MariaDB's official APT repository
MariaDB ColumnStore 25.10 columnar storage engine, vendor-paired to that server version
The full ColumnStore engine stack: PrimProc, ExeMgr, controllernode, workernode, DMLProc, DDLProc and WriteEngineServer
ColumnStore SQL extensions installed and verified, including the mcsgetversion() and calsettrace() UDFs
cpimport bulk loader for high-throughput ingest
mariadb, mariadb-admin, mariadb-dump clients
Per-VM root and application passwords generated on first boot, never baked into the image
An empty cloudimg database and matching application user, ready for your schema
MariaDB bound to loopback, with the ColumnStore internal daemon ports contained by a host firewall
Ubuntu 24.04 LTS base with all security patches applied at build time
Azure Linux Agent for SSH key injection and cloud integration
24/7 cloudimg support with a guaranteed 24 hour response SLA

Prerequisites

An active Azure subscription
A subscription to the MariaDB ColumnStore on Ubuntu 24.04 listing on Azure Marketplace
An SSH public key for virtual machine authentication
A virtual network and subnet in the target region

Choosing a virtual machine size

ColumnStore is an analytics engine, and its appetite for RAM and cores scales with the data you put in it rather than with the software itself. MariaDB's ColumnStore Hardware Guide asks for 8 CPU cores and 32 GB of RAM for development work and 64 cores with 128 GB for production; its single-node install guides quote lower floors of 4 cores with 4 to 16 GB.

The practical reading:

Workload	Suggested size	Notes
Evaluation, functional testing, small marts	`Standard_D4s_v5` (4 vCPU, 16 GB)	Comfortable for getting to know the engine
Development and departmental analytics	`Standard_D8s_v5` (8 vCPU, 32 GB)	Matches MariaDB's documented development minimum
Production analytics	`Standard_E16s_v5` or larger	Memory-optimised; size to your working set

The image itself is deliberately frugal — it idles at well under 1 GB — so it starts and runs correctly on small sizes. Query capacity, not startup, is what larger sizes buy you. ColumnStore returns a clear error when a join or aggregate exceeds its memory budget, and the tuning section below shows how to raise that budget once you move to a larger virtual machine.

Data volume grows on the OS disk under /var/lib/columnstore. Attach and mount a larger data disk before loading a substantial dataset.

Step 1: Deploy from the Azure Portal

Navigate to Marketplace in the Azure Portal, search for MariaDB ColumnStore, and select the cloudimg publisher entry. Click Create.

On the Networking tab, attach a network security group that allows inbound TCP 22 from your management IP range only. No database port needs to be opened. MariaDB on this image listens on loopback, and the documented remote access path is an SSH tunnel — so the database is reachable only by someone who can already authenticate to the virtual machine over SSH.

Click Review + create, wait for validation to pass, then Create. Deployment takes around two minutes.

To deploy from the Azure CLI instead, replace the placeholders and run:

az vm create \
  --resource-group <your-resource-group> \
  --name mariadb-columnstore-01 \
  --image cloudimg:mariadb-columnstore-ubuntu-24-04:default:latest \
  --size Standard_D8s_v5 \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_rsa.pub \
  --public-ip-sku Standard

Step 2: Connect over SSH

Connect as azureuser with the key you supplied at deployment:

ssh azureuser@<public-ip>

First boot takes roughly 30 to 60 seconds after the virtual machine reports Running. During that window mariadb-columnstore-firstboot.service generates this machine's credentials and starts the engine.

Step 3: Confirm first boot has completed

The first-boot service writes a sentinel when it has finished. Check it before anything else:

sudo systemctl is-active mariadb-columnstore-firstboot.service
sudo test -f /var/lib/cloudimg/mariadb-columnstore-firstboot.done && echo "first boot complete"

Expected output:

active
first boot complete

Now confirm the server and the engine are both up:

sudo systemctl is-active mariadb mariadb-columnstore

Expected output:

active
active

Service status after first boot

Step 4: Retrieve your per-VM credentials

Passwords are generated on this machine at first boot. They are not present in the published image, and no two deployments share them.

sudo cat /stage/scripts/mariadb-columnstore-credentials.log

The file is mode 0600 and owned by root, so it must be read with sudo. It contains:

ROOT_PASSWORD — the MariaDB administrative password for this machine
CLOUDIMG_USER and CLOUDIMG_PASSWORD — an application account scoped to the cloudimg database
CLOUDIMG_DATABASE — the empty database created for you

Per-VM credentials written at first boot

Record the values somewhere safe. As the machine's administrator you can always connect without a password over the local socket, which is the recovery path if the file is lost:

sudo mariadb -uroot -e "SELECT CURRENT_USER()"

Step 5: Verify the engine

Check the server version and the engine version. These are two separate things and both matter — the plugin is built against one specific server release:

sudo mariadb -uroot -e "SELECT VERSION() AS mariadb_server"
sudo mariadb -uroot -e "SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME='Columnstore'"

Expected output:

+------------------------+
| mariadb_server         |
+------------------------+
| 11.8.8-MariaDB-ubu2404 |
+------------------------+
+-------------+----------------+---------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS |
+-------------+----------------+---------------+
| Columnstore | 25.10          | ACTIVE        |
+-------------+----------------+---------------+

Confirm the engine is offered to SQL, alongside the row-store default:

sudo mariadb -uroot -e "SELECT ENGINE, SUPPORT, COMMENT FROM information_schema.ENGINES WHERE ENGINE IN ('Columnstore','InnoDB')"

Expected output:

+-------------+---------+----------------------------------------------------------------------------------------+
| ENGINE      | SUPPORT | COMMENT                                                                                |
+-------------+---------+----------------------------------------------------------------------------------------+
| Columnstore | YES     | ColumnStore storage engine                                                             |
| InnoDB      | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables       |
+-------------+---------+----------------------------------------------------------------------------------------+

SUPPORT of YES means the engine is available for CREATE TABLE. The ColumnStore SQL extensions are installed too:

sudo mariadb -uroot -e "SELECT mcsgetversion() AS columnstore_engine"

Finally, confirm every engine daemon is running. ColumnStore is a small distributed system even on one machine:

sudo systemctl is-active mcs-primproc mcs-controllernode mcs-workernode@1 mcs-writeengineserver mcs-dmlproc mcs-ddlproc

Expected output:

active
active
active
active
active
active

MariaDB and ColumnStore engine versions

Step 6: Create your first columnar table

Create a database and a fact table stored columnar. The only difference from any other MariaDB table is ENGINE=ColumnStore:

sudo mariadb -uroot -e "CREATE DATABASE IF NOT EXISTS analytics"
sudo mariadb -uroot analytics -e "
CREATE TABLE IF NOT EXISTS web_events (
    event_day  DATE,
    country    VARCHAR(2),
    device     VARCHAR(16),
    revenue    DECIMAL(12,2)
) ENGINE=ColumnStore"

Confirm the table really is columnar rather than having silently fallen back to InnoDB:

sudo mariadb -uroot analytics -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='analytics'"

Expected output:

+------------+-------------+
| TABLE_NAME | ENGINE      |
+------------+-------------+
| web_events | Columnstore |
+------------+-------------+

This check is worth keeping in your own deployment scripts. CREATE TABLE ... AS SELECT is not supported by ColumnStore and silently creates the table in the default engine instead, so verifying ENGINE after creation is the only way to be certain.

Columnar table design rules

ColumnStore deliberately does not implement several row-store features, because they do not fit a columnar layout:

No indexes. There is no PRIMARY KEY, UNIQUE or secondary index — column storage plus extent elimination replaces them. Too many keys specified; max 0 keys allowed means you left an index on the definition.
No foreign keys. Enforce referential integrity in your loading process.
AUTO_INCREMENT is accepted but not applied. Generate keys upstream.
Do not create ColumnStore tables in mysql, information_schema, calpontsys or test. Use your own database, as above.

Step 7: Load data and run an analytic query

Insert some rows. A multi-row INSERT is routed through ColumnStore's bulk write path automatically:

sudo mariadb -uroot analytics -e "
INSERT INTO web_events VALUES
  ('2026-07-01','GB','mobile', 1240.50),
  ('2026-07-01','US','desktop',3180.00),
  ('2026-07-02','GB','desktop', 960.75),
  ('2026-07-02','DE','mobile', 2015.20),
  ('2026-07-03','US','mobile', 4420.10),
  ('2026-07-03','GB','mobile', 1875.40)"

Now the point of the whole exercise — an aggregate that reads only the columns it needs:

sudo mariadb -uroot analytics -e "
SELECT country, device, COUNT(*) AS events, SUM(revenue) AS total_revenue
FROM web_events
GROUP BY country, device
ORDER BY total_revenue DESC"

Expected output:

+---------+---------+--------+---------------+
| country | device  | events | total_revenue |
+---------+---------+--------+---------------+
| US      | mobile  |      1 |       4420.10 |
| US      | desktop |      1 |       3180.00 |
| GB      | mobile  |      2 |       3115.90 |
| DE      | mobile  |      1 |       2015.20 |
| GB      | desktop |      1 |        960.75 |
+---------+---------+--------+---------------+

A time-series rollup over the same table:

sudo mariadb -uroot analytics -e "
SELECT event_day, SUM(revenue) AS daily_revenue, ROUND(AVG(revenue),2) AS avg_event
FROM web_events
GROUP BY event_day
ORDER BY event_day"

Expected output:

+------------+---------------+-----------+
| event_day  | daily_revenue | avg_event |
+------------+---------------+-----------+
| 2026-07-01 |       4420.50 |   2210.25 |
| 2026-07-02 |       2975.95 |   1487.97 |
| 2026-07-03 |       6295.50 |   3147.75 |
+------------+---------------+-----------+

Columnar table and analytic query results

Note that information_schema.TABLES.TABLE_ROWS reports an estimate for ColumnStore tables, not an exact count. Use SELECT COUNT(*) when you need the true figure:

sudo mariadb -uroot analytics -e "SELECT COUNT(*) AS exact_rows FROM analytics.web_events"

Step 8: Bulk loading with cpimport

INSERT is fine for thousands of rows. For millions, use cpimport, which writes ColumnStore extents directly and bypasses the SQL layer.

Export a table to a delimited file and load it back to see the round trip:

sudo mariadb -uroot analytics -N -B -e "SELECT event_day, country, device, revenue FROM web_events" > /tmp/web_events.tsv
sudo head -3 /tmp/web_events.tsv

Load a delimited file into a table with cpimport, giving the database, the table and the field separator:

sudo cpimport -s '\t' analytics web_events /tmp/web_events.tsv

cpimport reports the rows it read and inserted. Confirm the table grew:

sudo mariadb -uroot analytics -e "SELECT COUNT(*) AS rows_after_load FROM web_events"

For production loads, cpimport also reads standard input, so you can stream straight from another query or an object-storage download without staging a file on disk.

Step 9: Connect from your workstation over an SSH tunnel

MariaDB listens on 127.0.0.1:3306 only. Rather than exposing the port, forward it over SSH from your own machine:

ssh -L 3306:127.0.0.1:3306 azureuser@<public-ip>

Leave that session open and, in a second terminal on your workstation, connect any MariaDB or MySQL client to 127.0.0.1:3306 using CLOUDIMG_USER and CLOUDIMG_PASSWORD from Step 4. Business-intelligence tools that speak the MySQL wire protocol — Metabase, Superset, Tableau, Power BI via the MySQL connector — all work unchanged.

The application account is scoped to the cloudimg database. Verify it authenticates over TCP — on the virtual machine itself you can read the password straight out of the credentials file rather than retyping it:

CLOUDIMG_PASSWORD=$(sudo grep '^CLOUDIMG_PASSWORD=' /stage/scripts/mariadb-columnstore-credentials.log | cut -d= -f2-)
mariadb -h 127.0.0.1 -P 3306 -u cloudimg -p"${CLOUDIMG_PASSWORD}" cloudimg -e "SELECT CURRENT_USER() AS connected_as, DATABASE() AS current_db"

Expected output:

+----------------------+------------+
| connected_as         | current_db |
+----------------------+------------+
| cloudimg@127.0.0.1   | cloudimg   |
+----------------------+------------+

From your workstation, use the same user name with the password value you copied in Step 4.

Note that the account answers to cloudimg@127.0.0.1 rather than cloudimg@localhost. The server runs with skip-name-resolve, so a tunnelled TCP connection is never rewritten to localhost; both host forms are granted, so either path works.

To let that account reach the analytics database created above, grant it explicitly:

sudo mariadb -uroot -e "GRANT ALL PRIVILEGES ON analytics.* TO 'cloudimg'@'127.0.0.1', 'cloudimg'@'localhost'; FLUSH PRIVILEGES"

If you must expose the port instead

Only do this inside a private virtual network, never to the internet. Change the bind address, restart, and open the port to a specific source range in your network security group:

sudo sed -i 's/^bind-address.*/bind-address = 0.0.0.0/' /etc/mysql/mariadb.conf.d/99-cloudimg-columnstore.cnf
sudo systemctl restart mariadb
sudo mariadb -uroot -e "CREATE USER 'analyst'@'<your-mgmt-cidr>' IDENTIFIED BY '<new-password>'"

You will also need to grant privileges to that account and add an inbound rule for TCP 3306 scoped to the same range.

Step 10: Tuning ColumnStore for your virtual machine size

The image ships a memory budget sized for the smallest virtual machine it can run on. When you deploy on something larger, raise it — this is the single most effective tuning step.

Inspect the current budget:

sudo mcsGetConfig HashJoin TotalUmMemory
sudo mcsGetConfig HashJoin PmMaxMemorySmallSide
sudo mcsGetConfig DBBC NumBlocksPct

The three settings that matter:

Setting	What it controls	Guidance
`HashJoin TotalUmMemory`	Memory for joins and aggregates	Percentage of RAM. Raise toward 50 percent on a large machine
`HashJoin PmMaxMemorySmallSide`	Per-join cap for the smaller side	Raise in step with the above
`DBBC NumBlocksPct`	Block cache for scanned column data	Percentage of RAM. The single biggest lever on repeat-query speed

Keep the combined total under roughly 75 percent of system memory, which is MariaDB's documented ceiling for a ColumnStore node. To change a value, set it and restart the engine:

sudo mcsSetConfig HashJoin TotalUmMemory '50%'
sudo mcsSetConfig DBBC NumBlocksPct '35'
sudo systemctl restart mariadb-columnstore

Do not set these values to an empty string. An empty TotalUmMemory makes ColumnStore fall back to a hard-coded 8 GB assumption rather than to a small safe default. And do not lower them aggressively on a small machine — below roughly 50 percent, the engine's own internal catalogue queries can exceed the budget.

If a query outgrows the budget you will see a clear error such as MCS-2001: Join or subselect exceeds memory limit or ERR_AGGREGATION_TOO_BIG. Those are the signal to raise the budget or move to a larger virtual machine.

Check the current footprint at any time:

free -h

Step 11: Security notes

The image is configured so that nothing is reachable from the network except SSH.

Confirm MariaDB is bound to loopback:

sudo mariadb -uroot -e "SELECT @@bind_address AS bind_address, @@port AS port"

Expected output:

+--------------+------+
| bind_address | port |
+--------------+------+
| 127.0.0.1    | 3306 |
+--------------+------+

ColumnStore's internal daemons communicate over TCP in the 8600 to 8800 range. Upstream binds them to all interfaces, because the engine is designed for a trusted private cluster network — so this image ships an nftables guard that drops off-host traffic to that range while leaving loopback untouched. Inspect it:

sudo nft list table inet cloudimg_mcs

Expected output:

table inet cloudimg_mcs {
    chain input {
        type filter hook input priority filter; policy accept;
        iif "lo" accept
        ct state established,related accept
        tcp dport 8600-8800 drop
        udp dport 8600-8800 drop
    }
}

Keep this ruleset in place. Removing it exposes unauthenticated internal engine ports to anything that can route to the virtual machine. If you later build a multi-node ColumnStore cluster, narrow the rule to accept from your peer nodes' addresses rather than deleting it.

Review the account list at any time:

sudo mariadb -uroot -e "SELECT user, host FROM mysql.user ORDER BY user, host"

Every account is scoped to localhost or 127.0.0.1 — none is reachable from off the machine. There are no anonymous accounts and no test database.

Cross-engine join support is deliberately left unconfigured, because MariaDB's stock configuration points it at root with an empty password in a world-readable file. If you need joins between ColumnStore and InnoDB tables, create a dedicated least-privilege account and set it explicitly with mcsSetConfig CrossEngineSupport User and ... Password.

Step 12: Backups

mariadb-dump works against ColumnStore tables and produces portable SQL:

sudo mariadb-dump -uroot --databases analytics > /tmp/analytics-backup.sql
sudo ls -lh /tmp/analytics-backup.sql

For large datasets, exporting each table to a delimited file and reloading with cpimport is considerably faster than replaying SQL. Whichever you choose, copy the output off the machine — to Azure Blob Storage or an Azure Files share — and take periodic managed-disk snapshots for point-in-time recovery of the whole virtual machine.

Troubleshooting

ERROR 1815 (HY000): Internal error: System is not ready yet — the engine daemons are still starting, or one has stopped. Check them and restart the stack if needed:

sudo systemctl is-active mcs-primproc mcs-controllernode mcs-dmlproc mcs-ddlproc

Lost connection to DDLProc — the DDL daemon exited, most often because a memory budget is set too low for the engine's internal catalogue queries. Raise HashJoin TotalUmMemory per Step 10, then restart with sudo systemctl restart mariadb-columnstore.

A query fails with MCS-2001 or ERR_AGGREGATION_TOO_BIG — the query needs more memory than the current budget allows. This is a clean, expected error, not a fault. Raise the budget or use a larger virtual machine.

Too many keys specified; max 0 keys allowed — the CREATE TABLE statement carries an index or primary key. ColumnStore does not use indexes; remove them from the definition.

A table was created in the wrong engine — CREATE TABLE ... AS SELECT is unsupported and falls back silently. Create the table explicitly with ENGINE=ColumnStore, then INSERT ... SELECT into it.

First boot did not finish — inspect the service log:

sudo journalctl -u mariadb-columnstore-firstboot.service --no-pager -n 50

Engine logs live under /var/log/mariadb/columnstore/, with crit.log holding the highest-severity messages.

Support

cloudimg provides 24/7 support with a guaranteed 24 hour response SLA for all Azure Marketplace images. Contact support@cloudimg.co.uk with your virtual machine's resource ID and the output of sudo systemctl is-active mariadb mariadb-columnstore.

Upstream MariaDB ColumnStore documentation is at mariadb.com/docs and the engine source is at github.com/mariadb-corporation/mariadb-columnstore-engine.