All about databases

Core hosts MariaDB and PostgreSQL databases for your sites and applications.

This article walks through creating one and the optimisation and backup toggles, which differ between the two. It also covers encrypting a MariaDB database on disk, and the utf8mb3 character-set error some .NET clients hit.

Where to find databases

The top-level 'Databases' page lists databases across your clusters. Click 'Create' to add one.

Creating one

Required fields

• 'Cluster' — clusters without database support are disabled in the dropdown.
• 'Name' — lowercase, digits, dash, underscore. Max 63 characters.
• 'Server Software' — 'MariaDB' or 'PostgreSQL'. Tiles are disabled when the cluster does not support that software.

'Create user and grant' (MariaDB only)

Toggle on to also create a database user with full privileges on the new database, which saves the separate user and grant steps. You are asked for a 'User password' of at least 24 characters.

Advanced options

Under 'Show advanced options':

• 'Optimising' (MariaDB only) — runs OPTIMIZE TABLE weekly, which defragments each table. As rows are deleted and updated, the table file on disk develops gaps where the freed slots are not reused. OPTIMIZE TABLE rebuilds each table, copying the live rows into a fresh, densely packed file. Result: a smaller file on disk and faster table scans, because the engine reads fewer pages when the rows are back to back.
• 'Backups' — daily automatic backups, on by default. Can only be turned off for MariaDB.

Server-software differences

Which server software you pick decides which toggles you get:

The create page greys out what is unavailable, so you don't have to remember this.

Backups: which kind?

The 'Backups' toggle here is the local-on-master daily rotation, the one Cyberfusion uses for fast restore.

For long-term retention, set up a Borg repository pointed at the database. See All about backups for the difference.

Keeping the database in memory

MariaDB caches table and index data in memory, in the InnoDB buffer pool. Once a database grows larger than that pool, queries start reading from disk and sites get noticeably slower.

The 'InnoDB' page under a cluster's 'Health' sidebar shows how much memory the cluster has for this and how much data it is trying to hold, per table. See InnoDB buffer pool.

Usage tracking

The cluster periodically measures each database's on-disk size and reports it to Core. That size is what the management interface shows on the database row, and what billing-style usage reports use. No configuration required.

MariaDB encryption

'MariaDB encryption keys' is a cluster-level feature that encrypts MariaDB data at rest — the database files on disk.

Enabling it restarts MariaDB on the cluster's database nodes, and cannot be undone. See Turning it on.

What it protects against

Anyone who gets hold of the disk — a stolen server, a copy of the disk, a drive that leaves the data centre without being wiped — cannot read your databases from those files.

It does not protect against anyone who can log in to MariaDB, such as an attacker who found your database credentials in a website's configuration file. MariaDB decrypts the data whenever it answers a query, so anyone allowed to query it sees the same content as before.

What gets encrypted

InnoDB tables only. InnoDB is the default storage engine for new MariaDB tables, so this covers virtually everything in practice.

Once encryption is on, every newly created InnoDB table is encrypted automatically. You do not add ENCRYPTED=YES to your CREATE TABLE statements — MariaDB applies it for you, using the first encryption key on the cluster (key ID 1).

What changes for you

Nothing in how you interact with the data:

• Queries, mysqldump dumps and restores work exactly as before.
• phpMyAdmin and any other client sees the same content.
• Backups read out the same content.

Encryption only applies to how the data sits on disk. As soon as MariaDB reads a row to serve a query, it is decrypted in memory.

Performance overhead

In steady state, roughly 3 to 5% lower throughput. Unnoticeable for most workloads.

For very read-heavy or write-heavy databases on shared clusters, measure it on a representative workload before enabling it on a busy production database.

Turning it on

Open the cluster, go to 'Encryption', and enable encryption for 'MariaDB Databases'.

What happens immediately: MariaDB restarts on the cluster's database nodes, because it only loads the encryption plugin at startup. Every database on the cluster is unreachable for a few seconds, and websites that query a database during those seconds show an error to their visitors.

What happens after: MariaDB encrypts all existing data on disk, table by table, in the background. While that pass runs:

• Cluster load is higher than usual.
• Per-query latency may be slightly elevated.
• Other tenants on the same cluster may also see slower queries.

How long it takes depends on how much data is on disk. A small database finishes in minutes; a multi-hundred-GB database can take hours.

Plan it for a quieter period if you have one.

Checking encryption progress

Run this as a MariaDB admin user:

SELECT st.SPACE, st.NAME, te.ENCRYPTION_SCHEME, te.ROTATING_OR_FLUSHING
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION te
JOIN information_schema.INNODB_SYS_TABLES st
   ON te.SPACE = st.SPACE \G

• ENCRYPTION_SCHEME = 1 — the table is encrypted.
• ROTATING_OR_FLUSHING > 0 — the background pass is working on this table right now.

Using multiple encryption keys (API only)

By default a cluster has one MariaDB encryption key, and every encrypted table uses it. From the API you can add more keys and encrypt different tables with different ones.

This is API-only. The management interface handles enabling encryption with a single key.

To create a table that uses a non-default key:

CREATE TABLE t (i INT PRIMARY KEY) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=2;

The key ID (2 here) must already exist as a MariaDB encryption key on the cluster, added via the API.

More on how MariaDB handles InnoDB encryption: the MariaDB InnoDB encryption documentation.

MariaDB character set: utf8mb3 and .NET clients

Fix: update MySQL Connector/NET. Support for the utf8mb3 name was added on 17 November 2021. Hitting this error now means the client install is on a very old Connector.

Why it happens

MariaDB exposes two related sets, utf8mb3 and utf8mb4. The encoding has not changed in years — only the name the server reports back to clients. Older MariaDB versions returned this set as utf8; current versions return the explicit utf8mb3.

Most clients accept either spelling. The exception is MySQL Connector/NET, used by PowerBI and other .NET tools. Old versions do not recognise utf8mb3.

The character set itself is unchanged from what these clients were happily talking to before. Only the name is different.