Slow MySQL or MariaDB database: diagnose with the slow query log and MySQLTuner

"10 settings to speed up MySQL" articles share one flaw: they hand out values without knowing your workload. A slow database is rarely slow because of a single setting. Most of the time, a handful of queries account for most of the time spent, because an index is missing or a query written for 1,000 rows now runs on 10 million.

The approach comes down to five steps, in this order: find the expensive queries, read the server's global indicators, adjust the structural settings, fix queries and indexes, and check that the problem really is the database.

MySQLTuner is an open source Perl script that reads server variables and counters, then produces a diagnosis and recommendations. It supports MySQL, MariaDB, Percona Server and Galera. It changes nothing: it reads and comments.

wget https://mysqltuner.pl/ -O mysqltuner.pl
# without --pass, the script prompts for the password
perl mysqltuner.pl --host 127.0.0.1 --user dba

Essential prerequisite: at least 24 hours of uptime since the last restart. Its ratios rely on counters accumulated since startup (or since the last FLUSH STATUS). Right after a restart, caches are cold and the numbers mean nothing. The script's authors say so themselves.

Its most useful sections: theoretical maximum memory usage (an approximation: roughly global buffers plus per-connection buffers multiplied by max_connections), the share of reads served by the InnoDB buffer pool, the proportion of temporary tables created on disk, aborted connections, and security warnings (password-less accounts, remote root access).

Once the expensive queries are identified, EXPLAIN shows the plan chosen by the optimizer: index used or not, estimated row count, temporary table, sort. EXPLAIN ANALYZE (MySQL 8.0.18+) and ANALYZE (MariaDB) go further: they run the query and compare the estimate with reality. A large gap between estimated and actual rows points to stale statistics or a poorly chosen index.

-- MySQL 8.0.18+: runs the query and times each step
EXPLAIN ANALYZE SELECT ...;

-- MariaDB: same idea, actual vs estimated plan
ANALYZE FORMAT=JSON SELECT ...;

-- MySQL, MariaDB 10.6+ (performance_schema enabled): indexes with no observed use
SELECT * FROM sys.schema_unused_indexes;

-- MariaDB without performance_schema: userstat can be enabled live,
-- indexes missing from INDEX_STATISTICS after a full cycle are suspects
SET GLOBAL userstat = 1;
SELECT * FROM information_schema.INDEX_STATISTICS;

The sys schema, available on MySQL and bundled with MariaDB since 10.6, provides ready-made views: indexes with no observed use (candidates for removal after a full activity cycle, and only if no constraint relies on them), full-scan queries, the busiest tables. They rely on performance_schema, disabled by default on MariaDB: enabling it requires a restart. On older versions, the same information lives in performance_schema, with more effort.

Typical fixes: add a composite index with the right column order, rewrite an OR or a correlated subquery, avoid a function applied to an indexed column in the WHERE, paginate without a huge OFFSET. On a large production table, adding an index needs preparation: an online schema change tool, a test on a copy.

This method is often enough to find the first gains. A MySQL / MariaDB audit runs it systematically on your real workload: slow query log analysis over a representative period, schema and index review, configuration read against your hardware, replication and backup checks, with optional Signal18 co-review.

The deliverable is a list of actions ranked by impact and risk, not a miracle configuration. If you'd rather delegate day-to-day operations, MariaDB / MySQL Remote DBA includes this follow-up over time.