MariaDB Slow Query Diagnosis

How to enable, capture, aggregate, and act on slow-query data. Covers the slow query log, performance_schema digests, EXPLAIN / ANALYZE signals, and the file-sort versus index-sort distinction.

Quick Reference

• ALWAYS enable the slow query log in production. slow_query_log = ON with long_query_time = 0.5 (or smaller) catches sub-second issues that 10 (the default) hides.
• ALWAYS set log_slow_verbosity = query_plan,explain (10.5+) so each slow-log entry carries the optimizer plan and an inline EXPLAIN. Without this you see SQL text but cannot decide why it was slow.
• ALWAYS prefer pt-query-digest (Percona Toolkit) over mariadb-dumpslow for aggregation. pt-query-digest fingerprints by digest and gives 95th-percentile, mean, total time, and lock-time per query family. mariadb-dumpslow only counts.
• ALWAYS aggregate at the FINGERPRINT level. Two queries that differ only in literal values are the same slow-query family. pt-query-digest and events_statements_summary_by_digest normalise them already.
• NEVER rely on SHOW PROFILE / SHOW PROFILES. They are deprecated since MariaDB 5.6.7 / 10.0. Use performance_schema events tables instead.
• NEVER trust a single EXPLAIN timing as proof. EXPLAIN does not execute the query. ALWAYS use ANALYZE FORMAT=JSON SELECT ... (10.1+) to compare estimated rows against observed r_rows.
• NEVER treat Using filesort as a fatal signal on its own. It is fatal on millions of rows or hot OLTP paths ; it is fine on small result sets. ALWAYS combine the Using filesort flag with rows in EXPLAIN before deciding to redesign.
• NEVER log slow queries to TABLE in a production write-heavy workload. log_output = TABLE writes every entry to mysql.slow_log, doubling write amplification. Keep log_output = FILE (the default) and use file rotation.
• NEVER raise long_query_time to "reduce noise". Lower it instead, and use log_slow_rate_limit or min_examined_row_limit to thin the volume while still catching short-but-frequent killers.
• NOTE : From 10.11 the variables are renamed. slow_query_log is now log_slow_query, long_query_time is log_slow_query_time, slow_query_log_file is log_slow_query_file, min_examined_row_limit is log_slow_min_examined_row_limit. The old names remain as aliases. Use the new names in 10.11+ config ; both work.

Decision Tree