How to Log Slow Queries in MariaDB

Galaxy Glossary

How do I find and fix slow queries in MariaDB?

MariaDB’s slow query log captures statements that exceed a configurable time threshold so you can diagnose and optimize performance bottlenecks.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why should I use the slow query log?

Slow queries block connections, waste CPU, and delay page loads. Capturing them lets you pinpoint the worst offenders and add indexes, rewrite joins, or cache results.

How do I enable the slow query log?

Turn on logging at runtime without a restart:

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- seconds SET GLOBAL log_queries_not_using_indexes = 'ON';

MariaDB starts writing to the file defined by slow_query_log_file (default: hostname-slow.log).

Persist settings across restarts?

Add lines to my.cnf:

[mysqld] slow_query_log=1 slow_query_log_file=/var/log/mysql/slow.log long_query_time=2 log_queries_not_using_indexes=1

How do I view and group slow queries?

Use built-in tools:

# Summarize mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # Rich analysis pt-query-digest /var/log/mysql/slow.log

Both commands aggregate similar statements, rank by count, time, or lock time, and suggest indexes.

How can I reproduce and profile a slow query?

Run EXPLAIN for the captured statement:

EXPLAIN SELECT p.name, SUM(oi.quantity) FROM OrderItems oi JOIN Products p ON p.id = oi.product_id GROUP BY p.name ORDER BY SUM(oi.quantity) DESC;

Look for full table scans, missing Using index, or high rows estimates, then add proper indexes.

When should I adjust `long_query_time`?

Start high (e.g., 2 s) to avoid noise, then lower gradually (e.g., 0.2 s) once the worst problems are fixed. Keep slow_query_log enabled permanently in production.

Best practices for minimizing slow queries

Index foreign keys (customer_id, product_id), avoid SELECT *, paginate results, archive historical rows, and cache aggregates.

Why How to Log Slow Queries in MariaDB is important

How to Log Slow Queries in MariaDB Example Usage


-- Query likely to appear in slow log without proper indexes
SELECT p.name,
       SUM(oi.quantity) AS units_sold
FROM   OrderItems oi
JOIN   Products p ON p.id = oi.product_id
GROUP  BY p.name
ORDER  BY units_sold DESC;

How to Log Slow Queries in MariaDB Syntax


-- Enable logging at runtime
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = <seconds>;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Disable
SET GLOBAL slow_query_log = 'OFF';

-- Check current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Example persistent configuration (my.cnf)
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2
log_queries_not_using_indexes=1

Common Mistakes

Only enabling slow_query_log without lowering long_query_time. Logging stays empty because the default 10 s threshold is higher than most queries. Set a realistic value like 2 s.
Disabling the log after troubleshooting. New queries or data growth can re-introduce slowness. Keep the log enabled and rotate files instead of turning it off.

Frequently Asked Questions (FAQs)

Does the slow query log hurt performance?

Overhead is minimal—usually <5%—because MariaDB writes after query completion. Use async disk or lower long_query_time if concerned.

Can I log only SELECT statements?

Yes. Set log_output=TABLE and filter by command_type='Query' in mysql.slow_log, or post-process the file with grep -E '^# User@Host.*SELECT'.

How do I rotate large slow logs?

Use FLUSH SLOW LOGS; or logrotate on Linux. Ensure the MariaDB user can reopen the file after rotation.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.