Deep Dive: MariaDB – InnoDB, Temporary Tables in RAM, and a Database That Ran Unconfined for Five Weeks

🌐 Also available in: Deutsch · Français · Español

Deep Dive, part of the series around the Ansible Tour. Level: advanced Linux administration. The tour explains the roles; the deep dives explain the technology underneath them – how it works, why it is here, what exactly this setup does with it, how to run it day to day, and what else there would have been.

Both applications here stand on MariaDB. The blog runs it as a pod next to WordPress (part 3), the two Nextcloud servers run it directly on the host (part 4). This post explains what InnoDB does with memory and disk, which handful of settings matter for these workloads, what the measurements say – and how a single line in a systemd unit made sure that SELinux did not protect the database for five weeks.

🧭 How MariaDB and InnoDB work

The buffer pool: the database lives in RAM

InnoDB, the storage engine behind every table here, reads and writes data in pages of 16 KB. Pages it has read stay in the buffer pool, a large block of memory; the next read of the same row costs no disk access. Changes are made in the buffer pool first and written to the data files later. The most important single number of a MariaDB server is therefore the share of page requests the buffer pool can answer – the hit rate.

Redo log and doublewrite: why a crash loses nothing

Before a changed page is written to its data file, the change is recorded in the redo log, a sequential journal. After a crash InnoDB replays it and every committed transaction is back. The doublewrite buffer protects against a page that was only half written when the power went: each page goes first into a separate area, then into its real place, so a torn page can be repaired from the copy.

One setting decides how often the redo log is forced to disk. With innodb_flush_log_at_trx_commit = 1 (default) every commit waits for an fsync. With 2 the log is written at commit but synced only once per second – much faster for many small writes, at the risk of losing up to one second of commits on a hard crash of the whole machine (not of MariaDB alone).

Temporary tables: memory or disk

Queries with GROUP BY, DISTINCT, UNION or some sorts need an internal temporary table. It is created in memory up to tmp_table_size (and max_heap_table_size); a larger one – or one with TEXT/BLOB columns, which the memory engine cannot hold – goes to disk into tmpdir. WordPress and Nextcloud both use TEXT columns generously, so disk temporary tables are not a sign of bad tuning but unavoidable. The question is only where “disk” is.

Connections, threads, the slow log

Every client connection gets a thread and its own per-connection buffers; max_connections limits how many there can be and with it the worst-case memory use. The slow query log records every statement slower than long_query_time – the first place to look when a page is slow.

🛡️ Why it is here

• Both applications are built and tested against it. WordPress supports nothing else officially; Nextcloud recommends MySQL or MariaDB.
• An LTS series. 10.11 receives fixes until February 2028; updates within the series are small and predictable.
• Community governance. MariaDB is developed by the MariaDB Foundation and the company, not by a single vendor with its own priorities.

🔧 What this repository does

Two ways to run the same database

Why two ways? The blog was built “everything in the cluster” from the start. For Nextcloud the database is the heaviest and most precious component, and on the host it gets the full systemd sandbox, its own SELinux domain and the backup tooling of the distribution without a container in between.

The settings that matter

[mysqld]
innodb_buffer_pool_size        = 2G        # per host (512M on the small one)
innodb_flush_method            = O_DIRECT  # no double caching in the page cache
innodb_flush_log_at_trx_commit = 2         # sync the redo log once per second
innodb_linux_aio               = aio       # io_uring is disabled in the kernel
max_connections                = 50        # measured peak: 15
tmp_table_size                 = 32M
max_heap_table_size            = 32M
slow_query_log                 = 1
long_query_time                = 1
skip_name_resolve              = 1         # pod IPs cannot be resolved anyway

• innodb_linux_aio = aio: AlmaLinux disables io_uring (kernel.io_uring_disabled = 2). The default “auto” tried it first, logged an EPERM warning on every start and then fell back to classic Linux AIO anyway.
• skip_name_resolve: every connection from a pod used to trigger a reverse DNS lookup for a 10.42.x address that can never succeed – latency on connect and a warning per connection in the log. Safe because no account is granted by host name.
• max_connections = 50: the measured peak was 15 and 10 connections; 50 leaves room and bounds the worst-case memory.

Temporary tables in RAM

Four to six percent of all temporary tables go to disk on these servers – TEXT columns, see above. Since 16 October 2026 that “disk” is RAM: the systemd drop-in gives MariaDB its own /tmp as a tmpfs (TemporaryFileSystem=/tmp:size=4G), the blog pod mounts a memory-backed volume there. A tmpfs only uses memory while files exist; 4 GB leaves room for the sort files of an index rebuild during a Nextcloud upgrade. One trap on the way: combined with PrivateTmp=yes, the private directory on disk wins and the tmpfs is silently ignored.

The sandbox – and five weeks without SELinux

The drop-in 99-hardening.conf takes almost everything away the database does not need: no capabilities at all (a server on port 3306 needs none), NoNewPrivileges, a read-only file system except the data directory and the log directory, a system call filter, no access to kernel tunables, modules or logs, MemoryDenyWriteExecute.

The distribution’s unit starts MariaDB through /bin/sh -c "… galera_recovery …; exec mariadbd". With NoNewPrivileges set, SELinux refuses the domain transition from the shell (initrc_t) into mysqld_t, unless the policy explicitly allows it – and it does not. Result: from 11 September to 16 October 2026 MariaDB ran as initrc_t, a domain SELinux barely restricts, while every check said “enforcing”. The fix is one line: ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS, started directly by systemd, without the shell that only exists for Galera clusters. The SELinux deep dive tells the whole story; the lesson for any hardening is to check ps -eZ, not only the mode.

Accounts

• The application user may only connect from the pod network (10.42.%), not from anywhere.
• Anonymous accounts, the test database and a host-name proxy grant that skip_name_resolve made meaningless are removed.
• Each exporter has its own least-privilege user (PROCESS, BINLOG MONITOR, SELECT) – on the Nextcloud hosts the exporter used to reuse root’s password, so its config file effectively stored the root password.
• Root on the host authenticates through /root/.my.cnf; in the pod the root password comes from a Kubernetes Secret mounted as a file, not as an environment variable.

📊 Measured

These databases are small. The whole Nextcloud database of the larger server fits five times into its buffer pool, and the hit rate after a few hours is already above 99 % – the misses are almost all the first read after the morning reboot. No query took longer than a second. Two conclusions: tuning beyond what is done here would gain nothing measurable, and the buffer pools are generous; on the larger server it could shrink to 1 GB and give memory back – with 23 GB of RAM there is no reason to.

🧰 Day-to-day administration

# host (Nextcloud): root via /root/.my.cnf
mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'"
mariadb -e "SHOW ENGINE INNODB STATUS\G" | less
tail -f /var/log/mariadb/slow.log

# pod (blog): root password from the mounted Secret
k3s kubectl -n wordpress exec -it deploy/mariadb -c mariadb -- \
  sh -c 'MYSQL_PWD="$(cat /etc/secrets/db-root-password)" mariadb -uroot'

# is it confined?
ps -eZ | grep mariadbd

# a consistent dump without locking the application
mariadb-dump --single-transaction --quick --routines --events nextcloud | gzip > nc.sql.gz

Backups

A backup machine pulls a dump of every database each night over SSH (--single-transaction takes a consistent snapshot without locking InnoDB tables), keeps the last 14 after checking that each one is complete, and versions everything for 365 days. Restoring is gunzip < dump | mariadb nextcloud into an empty database – tested, not assumed.

Upgrades

Within the series, the host package comes from the pinned repository and dnf-automatic installs security fixes; the blog pod follows the image pin in versions.yml. A change of series – to the next LTS – is a deliberate step: dump first, change the repository or the image, start, run mariadb-upgrade, check the application.

Passwords

All database passwords live in the Ansible vault. Rotating one means: change it in the vault, run the playbook – it sets the new password in MariaDB and in the application configuration in the same run.

🆘 Cheat sheet: symptom → command

The short version of this article for the moment something is wrong. Run on the host as root; <…> are placeholders.

🔭 What comes next

• Point-in-time recovery. With the binary log enabled, a restore could go to any second between two nightly dumps. With these write volumes the nightly dump is enough; it would be the first change if that ever stops being true.
• The next LTS series well before February 2028, tested on the smaller Nextcloud server first.
• Query analysis with the performance schema in Grafana – today there is nothing slow to analyse.

⚖️ Alternatives – and why MariaDB

MariaDB is the least exciting component in this setup, and that is a compliment. The interesting findings were not in the database at all, but around it: in the system unit that started it, in the kernel that disabled one I/O interface, in the directory where temporary tables were written. The database itself just did its job – with no query slower than a second.