All about databases

A database in Core is a MariaDB or PostgreSQL database hosted on the cluster. This article walks through creating one and the optimisation and backup toggles, which behave differently per server software.

Where to find databases

In Core, the top-level 'Databases' page lists databases across your clusters. Click 'Create' to add one.

Creating a database

Required fields

• 'Cluster': which cluster the database lives on. Clusters without database support are disabled in the dropdown.
• 'Name': lowercase, digits, dash, underscore. Max 63 characters.
• 'Server Software': pick 'MariaDB' or 'PostgreSQL'. Tiles are disabled if the cluster doesn't support that software.

Auto-create user and grant (MariaDB only)

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

This shortcut isn't available for PostgreSQL.

Advanced options

Two more toggles live under 'Show advanced options':

• 'Optimising' (MariaDB only): runs OPTIMIZE TABLE on the database on a weekly schedule. This defragments each table. As rows are deleted and updated over time, the table file on disk develops gaps where the freed slots aren't reused. OPTIMIZE TABLE rebuilds each table from scratch, copying the live rows into a fresh, densely packed file. The result is 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 disabled for MariaDB databases.

Server-software differences

The optimising, backups, and auto-create toggles all behave differently per server software:

• MariaDB: optimising can be enabled, backups can be turned off, auto-create user is supported.
• PostgreSQL: optimising is not supported, backups are always on (the toggle is locked), auto-create is not supported.

The Create page reflects these constraints automatically. When you select PostgreSQL, the unavailable toggles grey out.

Backups: which kind?

The 'Backups' toggle here is the local-on-master daily backup 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 between the two.

Usage tracking

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

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's trying to hold, broken down per table. See InnoDB buffer pool.

MariaDB encryption

Cyberfusion offers 'MariaDB encryption keys', a cluster-level feature that encrypts MariaDB data at rest, meaning the database files on disk.

What it protects against

Encryption protects the data files themselves. Anyone who gets hold of the disk — a stolen server, a copy of the disk, or 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

Encryption applies to InnoDB tables only. InnoDB is the go-to storage engine for new MariaDB tables, so this covers virtually everything in practice.

Once encryption is enabled, every newly created InnoDB table is encrypted automatically. You don't need to 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 changes in how you interact with the data:

• Queries, dumps (mysqldump), and restores work exactly as before.
• phpMyAdmin and any other client sees the same content as before.
• 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's decrypted in memory.

Performance overhead

In steady state, expect roughly 3 to 5% lower throughput. For most workloads this is unnoticeable. 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

In the management interface, open the cluster, go to 'Encryption', and enable encryption for 'MariaDB Databases'.

Enabling encryption restarts MariaDB on the cluster's database nodes. MariaDB only loads the encryption plugin at startup, so the restart is required. While MariaDB restarts, every database on the cluster is unreachable for a few seconds. Websites and apps that query a database during those seconds show an error to their visitors.

After the restart, MariaDB starts encrypting all existing data on disk, table by table, in the background. While that initial pass is running:

• 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 the initial encryption takes depends on how much data is on disk. A small database finishes in minutes; a multi-hundred-GB database can take hours.

Plan to enable it during a quieter period if you have one.

Encryption cannot be turned off again: MariaDB encryption keys cannot be deleted, so once a cluster has one, its InnoDB tables stay encrypted.

Checking encryption progress

After enabling, you can check which tables have been encrypted and which the background pass is still working on. Run this query 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 means the table is encrypted.
• ROTATING_OR_FLUSHING > 0 means the background pass is currently working on this table.

Use this right after enabling to track how the initial encryption pass is progressing.

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 encryption keys to the cluster and encrypt different tables with different keys.

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

To create a new 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.

For more on how MariaDB handles InnoDB encryption under the hood, see the MariaDB InnoDB encryption documentation.

MariaDB character set: utf8mb3 and .NET clients

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

Most clients accept either spelling. The notable exception is MySQL Connector/NET, used by PowerBI and other .NET tools. Old Connector/NET versions don't recognise the utf8mb3 name and fail with:

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

To fix it, update the Connector. MySQL Connector/NET added support for the utf8mb3 name on 17 November 2021. If you're hitting this error now, the client install is on a very old Connector.