The MariaDB slow query log is more of a diagnostic tool for performance bottlenecks than a simple record of "slow" queries.
Let’s see it in action. Imagine a web application that’s suddenly become sluggish. You suspect database queries are the culprit. You’ve enabled the slow query log, and after a while, you examine the log file. Here’s a snippet you might see:
# Time: 2023-10-27 10:30:15 123456
# User@Host: webuser[webuser] @ localhost []
# Query_time: 15.345678 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 500000
SET timestamp=1698388215;
SELECT * FROM orders WHERE customer_id = 12345 AND order_date BETWEEN '2023-01-01' AND '2023-10-27';
This entry tells us a query took over 15 seconds to execute, examined half a million rows, and returned 100. The Query_time is the critical metric here.
The Problem it Solves
The primary problem the slow query log addresses is identifying SQL statements that consume excessive resources (CPU, I/O, memory) or take an unacceptably long time to complete. Without it, you’re essentially blind to which specific queries are degrading your application’s responsiveness. You might see general database load, but pinpointing the exact statement causing the pain is nearly impossible.
How it Works Internally
MariaDB, when configured to log slow queries, monitors every query executed by the server. For each query, it measures:
- Query Time: The total time from when the query starts execution until it finishes.
- Lock Time: The time spent waiting for table or row locks.
- Rows Examined: The number of rows the query optimizer had to inspect to satisfy the query.
- Rows Sent: The number of rows returned to the client.
If a query’s Query_time exceeds a predefined threshold (e.g., 2 seconds), it’s written to the slow query log file.
The Levers You Control
The most crucial configuration parameter is long_query_time. This sets the threshold in seconds (or microseconds if using fractional seconds) above which a query is considered "slow."
You also control:
slow_query_log: A boolean (ONorOFF) to enable or disable the logging.slow_query_log_file: The path to the file where slow queries are logged.log_queries_not_using_indexes: A boolean to log queries that don’t use indexes, even if they are fast. This is invaluable for proactive optimization.log_output: Specifies whether to log to a file (FILE) or a table (TABLE). Logging to a table (mysql.slow_log) allows for easier querying and analysis using SQL.
To enable and configure these, you’d typically edit your my.cnf or my.ini file, or set them dynamically:
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2; -- Log queries longer than 2 seconds
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';
After making changes, you might need to restart the MariaDB service for file-based logging, or the changes take effect immediately for dynamic variables.
Analysis Tools
Once you have a slow query log, you don’t have to parse it manually. The mysqldumpslow utility is a command-line tool that summarizes the slow query log, grouping similar queries and providing aggregate statistics.
For example, to get a summary sorted by the average query time:
mysqldumpslow -s at /var/log/mysql/mariadb-slow.log
This will output something like:
Count: 1 Time=15.34s (15s) Lock=0.00s (0s) Rows_sent=100 (100) Rows_examined=500000 (500000) user[webuser] @ localhost []
This shows you the most frequent and slowest queries, along with their aggregated execution times, lock times, and row counts.
The slow query log is most effective when you couple it with EXPLAIN. For any query identified as slow, you run EXPLAIN on it to understand the execution plan.
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345 AND order_date BETWEEN '2023-01-01' AND '2023-10-27';
The output of EXPLAIN will reveal if the query is performing full table scans, not using indexes effectively, or using inefficient join strategies.
The most common reason for slow queries identified by the slow query log is a missing or poorly designed index. If EXPLAIN shows Rows_examined is very high and type is ALL (full table scan) or index (full index scan), you likely need an index. For the example query, an index on (customer_id, order_date) would significantly speed it up.
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
This index allows MariaDB to quickly locate rows matching the customer_id and then efficiently filter by order_date within that subset, drastically reducing Rows_examined.
A less obvious, but still common, cause is suboptimal query structure. Sometimes, even with good indexes, a query might be written in a way that forces MariaDB to do more work than necessary. For instance, functions applied to indexed columns in the WHERE clause (e.g., WHERE YEAR(order_date) = 2023) often prevent index usage. Rewriting it as WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' is usually much faster.
Another scenario is when long_query_time is set too low for legitimate, complex operations. If your application has reporting queries that inherently take a few seconds but are critical, you might need to adjust long_query_time upwards or exclude specific user/host combinations from logging.
Excessive locking can also cause queries to appear slow. If Lock_time is high, it indicates contention. This might require optimizing transactions, using more granular locks, or re-evaluating application logic that holds locks for extended periods.
Finally, consider network latency or client-side processing if Query_time is high but Rows_examined and Rows_sent are low. However, the slow query log primarily captures server-side execution time.
Once you’ve addressed all slow queries identified by the log and mysqldumpslow, you’ll start seeing different types of performance issues, such as connection pool exhaustion or application-level caching inefficiencies.