Server — MariaDB — Config

Most MariaDB configuration articles are written for dedicated database servers with 16 or 32 GB of RAM, handling hundreds of concurrent connections and thousands of queries per second. February is not that. February is a homelab server running MariaDB alongside PowerDNS, ChirpStack, Home Assistant, WireGuard, and everything else in this series. MariaDB is important infrastructure on February, but it is not the only thing running, and the configuration should reflect that.

The default MariaDB configuration installed by Ubuntu is conservative. The buffer pool is small, the connection limits are modest, and most things are tuned for minimal resource consumption rather than maximum throughput. For February’s workload, that baseline is actually closer to correct than the aggressive configurations most guides recommend. The adjustments here are targeted: close a few security gaps, enable the slow query log for debugging, and set the InnoDB buffer pool to something sensible for the available RAM. Nothing more.

Where configuration lives

On Ubuntu, MariaDB’s configuration is split across several files that are loaded in order. The main entry point is /etc/mysql/mariadb.cnf, which includes two directories:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Files in these directories are loaded alphabetically. The server configuration lives in /etc/mysql/mariadb.conf.d/50-server.cnf. That is the file to edit for server-level settings.

Do not edit /etc/mysql/mariadb.cnf directly. The include structure exists so that package upgrades can replace individual files without clobbering custom settings. If you want settings that survive upgrades cleanly, add a new file rather than editing 50-server.cnf. A file named /etc/mysql/mariadb.conf.d/99-february.cnf loaded after everything else will override any setting from earlier files.

For simplicity, this article edits 50-server.cnf directly. If you prefer the override file approach, the same settings apply; just move them there.

The configuration

Open /etc/mysql/mariadb.conf.d/50-server.cnf and confirm or set the following. Most of these will already be present as commented or uncommented lines; find each one and update it rather than duplicating entries.

Binding and network

Already confirmed in the install article, but worth including here for completeness:

bind-address = 127.0.0.1

February’s MariaDB serves only local applications. There is no scenario where it should accept connections from outside the machine.

Disable local infile

local-infile = 0

LOCAL INFILE allows SQL queries to read files from the client filesystem. Disabling it removes a class of potential data exfiltration attacks. No application on February needs it.

Character set

Already confirmed in the install article, but set it explicitly in the config file so it persists through package upgrades:

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

InnoDB buffer pool

The InnoDB buffer pool is the main memory cache for table data and indexes. The default is 128 MB, which is appropriate for a minimal system. For a server with 4 GB or more of RAM running MariaDB alongside other services, something between 256 MB and 512 MB is more appropriate.

A rough rule for a shared server: allocate no more than 25% of total RAM to the buffer pool, leaving the rest for the operating system, other services, and filesystem cache. On a 4 GB server, 512 MB is the upper bound. On an 8 GB server, 1 GB is reasonable.

innodb_buffer_pool_size = 256M

Adjust this to match February’s actual RAM. Do not copy the 1 GB or 2 GB values from guides aimed at dedicated database servers; those values will starve the other services running alongside MariaDB.

InnoDB flush behaviour

innodb_flush_log_at_trx_commit = 1

The default value of 1 means every transaction is flushed to disk on commit, which is the safest setting for data integrity. Changing it to 2 improves write performance at the cost of potentially losing up to one second of transactions on a crash. For a homelab database, the default of 1 is correct. Data integrity matters more than write throughput here.

Max connections

max_connections = 50

The default is 151. February runs a small number of applications, each with a small connection pool. Reducing max_connections explicitly reserves memory for other uses and makes the server configuration self-documenting: if you ever need more than 50 connections, something unexpected is happening and you want to know about it. Each idle connection consumes a small amount of RAM; 151 idle connections consume meaningfully more than 50.

Slow query log

Disabled by default. Enable it so you have something to look at when queries are slow:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2

A query taking more than two seconds on February’s workload is worth knowing about. The slow query log is not high volume on a lightly loaded server; it should be mostly empty. If it is not, that tells you something.

Error log

Should already be set, but confirm:

log_error = /var/log/mysql/error.log

The complete change set

For clarity, here is every setting that differs from the Ubuntu default, collected in one place. These go in the [mysqld] section of 50-server.cnf:

[mysqld]
bind-address            = 127.0.0.1
local-infile            = 0
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci
innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit = 1
max_connections         = 50
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time         = 2
log_error               = /var/log/mysql/error.log

Applying the changes

sudo systemctl restart mariadb

Confirm MariaDB came back up cleanly:

sudo systemctl status mariadb

Check the error log for anything unexpected:

sudo tail -n 20 /var/log/mysql/error.log

A clean restart will show a handful of startup messages and nothing marked ERROR. If MariaDB fails to start, the error log will tell you why. A common cause is a typo in the config file; MariaDB is strict about syntax in 50-server.cnf and will refuse to start if it cannot parse it.

Confirm the settings took effect:

sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo mariadb -e "SHOW VARIABLES LIKE 'max_connections';"
sudo mariadb -e "SHOW VARIABLES LIKE 'slow_query_log';"

Each should return the value you set.

What is not in here

Several things commonly appear in MariaDB configuration guides that are intentionally absent here.

Query cache. The query cache was removed in MariaDB 10.6 and is no longer a relevant setting. Guides that mention query_cache_size are out of date.

Binary logging. Binary logs are for replication and point-in-time recovery on dedicated database servers. February uses file-level backups via borgmatic and has no replicas. Binary logging adds disk I/O and storage overhead with no benefit for this setup. Leave it off.

Performance schema. Enabled by default in 10.11 and useful for diagnosing performance issues, but its memory overhead is noticeable on a constrained system. If February has less than 2 GB of RAM and memory is tight, disabling it with performance_schema = OFF is worth considering. On a server with 4 GB or more, leave it at the default.

innodb_log_file_size. The MariaDB 10.11 default is reasonable. Increasing it benefits high-write workloads; February’s workload does not qualify.

The goal of this configuration is a MariaDB instance that is secure, predictable, and does not interfere with the other services running on February. Aggressive tuning optimises for throughput at the expense of other things. That is the wrong tradeoff for this server.