How to Read the MariaDB Slow Query Log in Enscale
A query that used to be quick just showed up in your slow query log. Nobody rewrote it. Nobody touched the schema. Yet there it is, slower than it was last month. That single log line is often the first hard evidence that your database is drifting out of alignment with how it's being used.
The trick is knowing how to read it, because the log tells you what is slow but leaves you to work out why.
Here's how to turn it on, read it properly, and spot the patterns that matter before they turn into a support ticket.
Key Takeaways
- Enable the slow query log with
slow_query_log = ONand along_query_timeof 1 second or lower. The 10-second default is too lax for most web apps.- The key signal in each entry is
Rows_examinedversusRows_sent: a huge gap usually means a full table scan and a missing index.- Aggregate the log with
mysqldumpslowto spot trends, rather than reading it one line at a time.- A query that slows down without being changed is performance drift showing up at the level of a single statement.
Turning the slow query log on
The slow query log records any statement that runs longer than a threshold you set. Three variables control it:
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- seconds; anything slower is logged
SET GLOBAL log_output = 'FILE'; -- or 'TABLE' to query it via SQLTwo things worth knowing. First, the default long_query_time is 10 seconds, which is far too lax for most web workloads; if a query takes even one second you usually want to know, so most people drop it to 1 or lower. When log_output = FILE you can use fractional values like 0.5 for sub-second resolution. Second, setting these with SET GLOBAL doesn't survive a restart, so once you've settled on a threshold, put it in your MariaDB configuration to make it stick.
There's also min_examined_row_limit, which tells MariaDB to ignore queries that examined fewer than N rows even if they were slow. Handy for filtering out noise once you're tuning in earnest.
Reading a log entry
A single slow query entry looks roughly like this:
# Time: 2026-07-23T09:14:02.123456Z
# User@Host: app_user[app_user] @ [10.0.0.14]
# Query_time: 3.478210 Lock_time: 0.000094 Rows_sent: 12 Rows_examined: 848213
SET timestamp=1753258442;
SELECT * FROM orders WHERE customer_id = 40219 ORDER BY created_at DESC;Every field is a clue:
- Query_time is the total wall-clock time, the headline number.
- Lock_time is how long the query waited on locks. If this is high while Query_time is high, your problem may be contention, not the query itself.
- Rows_sent is how many rows the query actually returned.
- Rows_examined is how many rows MariaDB had to inspect to find them.
The single most useful thing in that entry is the relationship between the last two. This query sent 12 rows but examined 848,213 to find them. That ratio is the smoking gun: MariaDB is reading almost a million rows to hand back a dozen, which nearly always means it's doing a full table scan instead of using an index. A well-indexed query has Rows_examined close to Rows_sent. When the gap between them is enormous, you've found real waste.
Why a query that was fine starts showing up
Queries rarely turn slow on their own. What changes is the world around them:
- the table grew, so a full scan that was cheap at 10,000 rows is painful at 10 million
- an index that used to be selective no longer is, because the data distribution shifted
- concurrency rose, so the same query now competes for memory and locks
- the working set outgrew the buffer pool, so reads that used to hit memory now hit disk
That's why the same SQL can post a 50ms Query_time in spring and a 3-second one by autumn. The query didn't change. Its conditions did. This is exactly what performance drift looks like at the level of a single statement.
Stop reading one line at a time, look at patterns
Individual entries are useful, but patterns are where the insight is. Reading a raw log by eye doesn't scale, so aggregate it. MariaDB ships mysqldumpslow, which groups similar queries and sorts them so you can see the worst offenders at a glance:
mysqldumpslow -s t -t 10 /var/log/mysql/slow.logThat sorts by total time (-s t) and shows the top 10 (-t 10). Percona's pt-query-digest does the same job in more depth if you have it available. What you're looking for across the aggregated view is:
- the same query appearing more and more frequently
Query_timefor a given query trending up week over weekRows_examinedgrowing whileRows_sentstays flat (an index quietly losing its edge)Lock_timerising under load
Those trends tell you how the system is evolving, which a single slow line never can.
Why the log is a signal, not a diagnosis
It's tempting to treat a slow query as a query problem and rewrite it. Sometimes that's the fix. Often it isn't, because the log entry is a symptom of something broader: configuration that no longer matches the workload, an index that stopped supporting the query pattern, schema that's grown awkward, or plain resource contention. Fix the one query and, if the underlying misalignment is still there, another slow query takes its place next month. You end up playing whack-a-mole with symptoms while the real gap keeps widening.
Where Releem fits
Reading the slow query log well is a genuinely useful skill, but doing it continuously, across every query, while also running the rest of your business, is where it falls apart for most people. That's the job Releem takes on.
Releem runs as an add-on inside your Enscale environment and analyses query behaviour alongside your configuration and schema, so instead of reacting to slow queries after they surface, you see the patterns forming and understand what's driving them. It's trained machine learning rather than an LLM, so its recommendations come from real patterns in your database, not predicted text, and it doesn't hallucinate a fix. Applying a change is your call: Releem recommends the fix and tells you why, and you apply it yourself, in a click in the dashboard or with your own database tools, once you've read it. Manual review is the default and how we'd recommend running it, and if you later decide you want Releem applying routine changes for you, that's your decision to make. And the longer it runs, the more its advice is shaped around your specific environment and the fixes that have already worked for you.
Frequently asked questions
How do I enable the slow query log in MariaDB?
Set slow_query_log = ON, choose a long_query_time threshold (most people use 1 second or lower rather than the 10-second default), and pick log_output of FILE or TABLE. Set these in your MariaDB configuration file so they survive a restart, since SET GLOBAL alone is reset when the server restarts.
What does Rows_examined mean in the slow query log?
It's the number of rows MariaDB inspected to produce the result. Compare it to Rows_sent (the rows actually returned). A big gap, examining hundreds of thousands of rows to return a handful, usually means the query is doing a full table scan and needs a better index.
What is a good value for long_query_time?
The default is 10 seconds, which is too high for most applications. For typical web workloads, 1 second is a common starting point, and you can go lower (fractional values work when logging to a file) once you've cleared the worst offenders.
How do I find my slowest MariaDB queries?
Aggregate the log rather than reading it line by line. mysqldumpslow -s t -t 10 /path/to/slow.log groups similar queries and shows the ten that consume the most total time. Percona's pt-query-digest gives a more detailed breakdown.
Why do queries get slower even when I haven't changed them?
Because their conditions change: tables grow, indexes lose selectivity, concurrency rises, or the working set outgrows the buffer pool so reads hit disk. The SQL stays the same while the cost of running it climbs, which is performance drift in action.
If your slow query log is filling up with queries that used to be fine, your database is already drifting. Releem watches query behaviour continuously and tells you what to change, while you keep the final say.
See how Releem works on Enscale, and install it on your database node →
Already an Enscale customer and ready to go? Install Releem now →
