log in
consulting hosting industries the daily tools about contact
← All posts
· 6 min read · MariaDB slow query log

MariaDB Slow Query Log in Production Without Wrecking Your Disk

The slow query log is one of the most useful tools in MariaDB, and one of the easiest to misconfigure into a disk I/O disaster. Here's how I actually run it.

The slow query log is the first thing I enable when a client's app starts feeling sluggish, and the first thing I disable when I forget to set a sane threshold. I've done both. This post is about doing the former without accidentally doing the latter.

What the Slow Query Log Actually Does

It's straightforward: MariaDB writes a log entry for every query that takes longer than a configurable threshold. That's it. No sampling, no agents, no SaaS dashboard required. Just a file you can grep.

The reason I keep coming back to it over fancier tools is that it's already there. No additional infrastructure, no per-query overhead on fast queries, and it captures exactly what executed — not what an ORM thinks it sent. I've caught more than one case where Laravel's query builder was generating something technically valid but structurally insane because of a conditional scope that stacked three left joins on a 2M-row table. The slow log showed me the actual SQL. Explain plan finished the story.

The problem is that people turn it on without thinking about thresholds or what happens to that log file over time, and then wonder why their IOPS graph looks like a cliff.

The Configuration That Won't Hurt You

Here's what I actually put in /etc/mysql/conf.d/slow-query.cnf on a production MariaDB instance:

[mysqld]
slow_query_log         = 1
slow_query_log_file    = /var/log/mysql/slow.log
long_query_time        = 1
log_queries_not_using_indexes = 0
min_examined_row_limit = 1000
log_slow_verbosity     = query_plan,explain

Let me explain each line.

long_query_time = 1 — one second. I know the MariaDB docs suggest you can go as low as 0, and yes, setting it to 0 will log everything. Don't do that in production unless you enjoy watching your disk fill up in under an hour on a busy app. One second is a reasonable floor. If you're hunting subtler regressions, drop it to 0.5 during a maintenance window, watch it for an hour, then bump it back up.

log_queries_not_using_indexes = 0 — this one trips people up. It sounds useful: log every query that skips an index. And it is useful — in development. In production it's a footgun. A query can skip an index legitimately (small table, full-scan is faster, optimizer knows this). If you have a handful of small lookup tables, this setting will spam your log with noise that buries the actual slow queries. Keep it off in prod, turn it on locally when you're auditing a schema.

min_examined_row_limit = 1000 — this is the underused one. Even with long_query_time = 1, a query that scans 12 rows and returns fast isn't interesting to me. This setting says: only log queries that examined at least 1000 rows. Combine it with the time threshold and you get the intersection of "took a while" and "touched real data." That's the signal I care about.

log_slow_verbosity = query_plan,explain — MariaDB-specific and worth having. It includes the query plan and an inline EXPLAIN in the log entry. Saves you the step of manually running EXPLAIN on the query after the fact, which matters when you're looking at log entries from 3am.

Dynamic Changes Without a Restart

You don't have to bounce MariaDB to toggle this. During an investigation I'll often dial thresholds down temporarily:

-- Check current state
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Temporarily lower the threshold for 30 minutes of observation
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;

-- Turn on index logging temporarily
SET GLOBAL log_queries_not_using_indexes = 1;

-- When done, restore production values
SET GLOBAL long_query_time = 1;
SET GLOBAL min_examined_row_limit = 1000;
SET GLOBAL log_queries_not_using_indexes = 0;

These survive until the next restart. They don't persist to your cnf file. That's intentional — I don't want a debugging session setting baked into production config permanently. Make the change, investigate, restore.

Log Rotation or It Will Eat Your Disk

A busy application on a one-second threshold can still generate hundreds of megabytes a day if you have genuine performance problems (which is exactly when you've turned this on). You need rotation.

logrotate handles this fine. Drop a file at /etc/logrotate.d/mysql-slow:

/var/log/mysql/slow.log {
    daily
    rotate 7
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        if [ -f /var/run/mysqld/mysqld.pid ]; then
            kill -HUP $(cat /var/run/mysqld/mysqld.pid)
        fi
    endscript
}

The kill -HUP is important. Without it, MariaDB keeps writing to the old file descriptor after rotation, and your "rotated" log is actually still receiving writes while the new file stays empty. The HUP signal tells MariaDB to close and reopen its log files.

Alternatively, from inside MariaDB:

FLUSH SLOW LOGS;

Same effect. I put that in the postrotate script on servers where I'd rather not touch the process directly.

On servers with very high write volume and marginal disk, I'll also monitor log size with a simple cron:

#!/bin/bash
# /etc/cron.hourly/check-slow-log
LOG=/var/log/mysql/slow.log
MAX_MB=500

if [ -f "$LOG" ]; then
    SIZE_MB=$(du -m "$LOG" | cut -f1)
    if [ "$SIZE_MB" -gt "$MAX_MB" ]; then
        echo "Slow query log is ${SIZE_MB}MB on $(hostname)" | \
            mail -s "MariaDB slow log oversized" ops@example.com
    fi
fi

Blunt, but it's saved me twice.

Actually Reading the Output

The raw log is readable but dense. mysqldumpslow ships with MariaDB and gives you an aggregated view:

# Top 10 queries by total time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 by average time (often more useful)
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# Top 10 by count — high-frequency mediocre queries
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

The count sort is the one people skip and shouldn't. A query that takes 800ms but runs 10,000 times an hour is a bigger problem than a 3-second report query that runs once a day. Total time (-s t) usually surfaces it, but average time can hide it if you have one catastrophic outlier skewing the numbers.

For more power, pt-query-digest from Percona Toolkit is worth installing:

pt-query-digest /var/log/mysql/slow.log > /tmp/digest.txt

It groups queries by fingerprint (normalizes literals so WHERE id = 1 and WHERE id = 2 are the same query), gives you percentile breakdowns, and shows you the worst offenders by multiple dimensions at once. I ran this on a log from a Seattle-area e-commerce client last spring and inside two minutes had identified a product search query that was doing a filesort on a 400k-row table because someone had added a LEFT JOIN to a feature-flag table without noticing it blew the composite index.

What I Do With the Findings

Once I have candidates, the workflow is:

-- Grab a specific slow query from the log, run EXPLAIN
EXPLAIN SELECT p.*, c.name AS category_name
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
WHERE p.status = 'active'
AND p.price BETWEEN 10.00 AND 50.00
ORDER BY p.created_at DESC
LIMIT 25;

-- Then check what indexes exist
SHOW INDEX FROM products;

-- And if log_slow_verbosity didn't already give me the plan,
-- use EXPLAIN FORMAT=JSON for the full optimizer trace
EXPLAIN FORMAT=JSON SELECT ...\G

From there it's usually one of three things: missing index, wrong index order on a composite key, or an N+1 that Laravel is quietly assembling into one enormous IN() clause. The slow log tells me which query. EXPLAIN tells me why. The fix is usually a migration or a query refactor.

When I'd Reach For This

Always. This is table stakes on any production database I manage. It's the first diagnostic I enable on a new server and the first place I look when a client says "the app feels slow."

I'd reach for it specifically when:

  • A new feature deployment is followed by performance complaints
  • Response times are creeping up but nothing obvious in APM
  • You're about to optimize and need to know what to actually optimize (not guess)
  • After a significant data growth milestone — queries that were fast at 100k rows aren't always fast at 2M

When I wouldn't lean on it alone: if you're seeing high CPU or memory pressure but not slow queries, the slow log won't tell you much. That's a different investigation — connection counts, buffer pool size, temp table spills. The slow log is specifically about query execution time, not server resource pressure.

Also: if your threshold is tuned right and the slow log is empty, that's good news. Don't go lowering it to zero just to feel like the tool is doing something.

The Bottom Line

The slow query log is one of those tools that's been around forever, works reliably, and costs nothing to run if you configure it correctly. Set a real threshold, add min_examined_row_limit, turn on rotation, and run pt-query-digest when you need answers. Don't overthink it. The fancier observability stacks have their place, but I've fixed more slow queries with this log and a text editor than with any dashboard.

mariadb mysql performance database devops slow-query-log

Need help shipping something like this? Get in touch.