Server — MariaDB — Tuning

The configuration, InnoDB, Aria, and MyISAM articles established a baseline. The values there were chosen by reasoning about what February is and what it is not, rather than by measuring a running system. That reasoning is sound, but reasoning is not measurement. This article covers reading the status variables that tell you whether the configuration is working as intended, and using MySQLTuner to surface anything that reasoning missed.

One important caveat before anything else: status variables reflect the server’s experience since the last restart, and they need time to accumulate meaningful data. Running a tuner tool on a freshly restarted server, or on one that has only been running for an hour, produces recommendations based on almost no real workload. Run these checks after February has been running under normal conditions for at least 24 hours, ideally longer.

The status variables that matter

MariaDB exposes hundreds of status variables via SHOW GLOBAL STATUS. Most are noise for February’s use case. These are the ones worth checking periodically.

InnoDB buffer pool

These three queries, already introduced in the InnoDB article, form the core of performance monitoring:

# Buffer pool hit ratio — should be 99%+
sudo mariadb -e "
SELECT ROUND(
  (1 - (
    SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
  ) / NULLIF((
    SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
  ), 0)) * 100, 2
) AS buffer_pool_hit_pct;"

# Current dirty pages — should be low relative to total pages
sudo mariadb -e "SHOW STATUS LIKE 'Innodb_buffer_pool_pages_%';"

# Read/write rates
sudo mariadb -e "SHOW ENGINE INNODB STATUS\G" | grep -A 10 "BUFFER POOL AND MEMORY"

A hit ratio below 99% means the buffer pool is too small. Increase innodb_buffer_pool_size in 50-server.cnf and restart. The right increment depends on available RAM; add 64 MB at a time and recheck after another 24 hours.

Connections

sudo mariadb -e "
SELECT
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS
   WHERE VARIABLE_NAME = 'Max_used_connections') AS peak_connections,
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES
   WHERE VARIABLE_NAME = 'max_connections') AS max_connections;"

Max_used_connections is the highest concurrent connection count since the last restart. It should be well below max_connections, which is set to 50 in the config article. If peak connections are consistently close to 50, either a service is leaking connections or an application has a connection pool configured too aggressively.

Temporary tables

sudo mariadb -e "SHOW STATUS LIKE 'Created_tmp%';"

Three variables appear: Created_tmp_tables, Created_tmp_disk_tables, and Created_tmp_files. The ratio of disk tables to total tables is the signal. If Created_tmp_disk_tables is a significant fraction of Created_tmp_tables, queries are regularly spilling temporary results to disk because they exceed the in-memory size limit. This is controlled by tmp_table_size and max_heap_table_size, both of which default to 16 MB in Ubuntu’s MariaDB package.

For February’s workload, disk temporary tables should be rare. If they are not, something is generating large intermediate result sets and the slow query log is the right place to look for the cause.

Aria pagecache

sudo mariadb -e "SHOW STATUS LIKE 'Aria_pagecache%';"

As covered in the Aria article: Aria_pagecache_reads should be close to zero relative to Aria_pagecache_read_requests. If it is not, the pagecache is undersized.

Table open cache

sudo mariadb -e "SHOW STATUS LIKE 'Open%tables';"

Opened_tables counts how many times a table had to be opened from disk because it was not in the table cache. A value that grows rapidly relative to uptime means the table cache is too small. The default table_open_cache of 2000 is more than adequate for February’s handful of databases.

MySQLTuner

MySQLTuner is a Perl script that reads MariaDB’s status variables and configuration, applies a set of heuristics, and produces a list of recommendations. It does not make changes itself. It is a useful second opinion, particularly for catching things that the configuration articles did not explicitly address.

Install it:

sudo apt install mysqltuner

Run it after the server has been up for at least 24 hours:

sudo mysqltuner --user root

Since root uses unix_socket authentication on Ubuntu 24.04, no password prompt appears. The output is structured in sections: general statistics, storage engine metrics, security checks, and recommendations.

Reading the output

The recommendations section at the bottom is the one to act on. Each line starts with either [OK], [!!] (warning), or [--] (informational). Focus on the [!!] lines.

Common recommendations on a fresh February installation and how to evaluate them:

“Increase innodb_buffer_pool_size” — MySQLTuner calculates a recommended size based on the total data currently in InnoDB tables plus overhead. On a server that has only been running for a short time with little data, it may recommend a lower value than the configured 256 MB. After the databases have grown to their steady-state size, rerun MySQLTuner and compare. If it consistently recommends a higher value, increase the buffer pool.

“Query cache is disabled” — Ignore this. The query cache was removed in MariaDB 10.6 and MySQLTuner may flag its absence depending on the version. It is not applicable to 10.11.

“key_buffer_size is set to 0 or very low” — If you have set key_buffer_size = 64K as recommended in the Aria article, MySQLTuner may flag this. Since February has no application MyISAM tables, a low key_buffer_size is correct. The recommendation can be disregarded.

“Reduce max_connections” or “Increase max_connections” — If peak connections are well below 50 and MySQLTuner recommends reducing further, consider it. If they are close to 50, increase the limit. The current setting of 50 is intentionally conservative for a homelab server.

“Enable the slow query log” — Already done. If MySQLTuner flags this, confirm the setting is in 50-server.cnf and that the service restarted after the change.

Security recommendations — MySQLTuner runs a set of security checks alongside the performance checks. These overlap with what the install and network articles already covered: no anonymous users, no remote root access, no test database. These should all show [OK]. If any show [!!], address them.

What MySQLTuner does not know

MySQLTuner makes recommendations based on status variables and heuristics. It does not know that February is a shared server running multiple services, or that the InnoDB buffer pool is intentionally sized to leave room for other processes. Some of its recommendations will suggest higher values than are appropriate for a non-dedicated database server. Apply judgement: a recommendation to set the buffer pool to 80% of total RAM makes sense on a dedicated database server and makes no sense on February.

The complete configuration reference

After all the MariaDB articles in this series, the full set of non-default settings in 50-server.cnf for February looks like this:

[mysqld]
# Network
bind-address            = 127.0.0.1
local-infile            = 0

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

# Storage engine
default_storage_engine  = InnoDB

# InnoDB
innodb_buffer_pool_size        = 256M
innodb_flush_log_at_trx_commit = 1

# Aria and MyISAM
aria_pagecache_buffer_size = 128M
key_buffer_size            = 64K

# Connections
max_connections = 50

# Logging
log_error               = /var/log/mysql/error.log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time         = 2

# Audit plugin
plugin-load-add                = server_audit
server_audit_logging           = ON
server_audit_events            = CONNECT
server_audit_file_path         = /var/log/mysql/audit.log
server_audit_file_rotate_size  = 10485760
server_audit_file_rotations    = 5

# TLS
ssl-ca   = /etc/mysql/tls/ca-cert.pem
ssl-cert = /etc/mysql/tls/server-cert.pem
ssl-key  = /etc/mysql/tls/server-key.pem
tls_version = TLSv1.2,TLSv1.3

# Binary log
server_id              = 1
log_bin                = /var/log/mysql/mariadb-bin
binlog_format          = ROW
binlog_row_image       = FULL
expire_logs_days       = 7
max_binlog_size        = 100M

This is the canonical reference for February’s MariaDB configuration. Every setting here has been covered in a previous article in this sub-series. Nothing should be unfamiliar.

When to retune

Tuning is not a one-time exercise. The right values change as February’s databases grow, as services are added, and as workload patterns shift. A reasonable cadence for checking:

After any significant change to what is running on February: a new service added, an existing service upgraded, a database schema change. Run the status variable checks and rerun MySQLTuner.

After a few months of normal operation, when the databases have reached something close to their steady-state size. The buffer pool hit ratio and MySQLTuner recommendations will be more meaningful then than they are on a freshly set-up server.

When something is slow and the slow query log shows unexplained queries: investigate the status variables before adjusting configuration, because the problem is usually a missing index or a query pattern that configuration cannot fix.

The goal is a MariaDB instance that runs quietly in the background without requiring regular attention. That is achievable on February. This article is how you verify it is the case.