Introduction

The slow query log is a vital feature in database management systems, particularly in MySQL and MariaDB, that helps identify queries taking longer than expected to execute. Enabling the slow query log allows database administrators and developers to monitor, analyze, and optimize inefficient queries, improving overall database performance and user experience.

In this comprehensive tutorial, you will learn what the slow query log is, why it is essential, and how to enable it step-by-step. Additionally, we will cover best practices, useful tools, real-world examples, and frequently asked questions to ensure you master this critical aspect of database optimization.

Step-by-Step Guide

Step 1: Understand the Slow Query Log

The slow query log records SQL queries that exceed a predefined execution time threshold. By default, many systems do not enable this log, so enabling it manually is necessary for performance monitoring.

Step 2: Check Current Slow Query Log Status

Before enabling, verify if the slow query log is active and where it is located. Execute the following SQL commands in your MySQL or MariaDB client:

SHOW VARIABLES LIKE 'slow_query_log';

This will return whether the slow query log is ON or OFF.

Also, check the current log file path:

SHOW VARIABLES LIKE 'slow_query_log_file';

Step 3: Enable Slow Query Log Temporarily

To enable the slow query log without restarting the database server (temporary until next restart), execute:

SET GLOBAL slow_query_log = 'ON';

You can also set the log file path if needed:

SET GLOBAL slow_query_log_file = '/path/to/slow-query.log';

Adjust the path according to your server’s filesystem permissions and preferences.

Step 4: Configure the Long Query Time Threshold

The long query time defines the threshold in seconds for considering a query as “slow.” The default is usually 10 seconds, which might be too high for many applications. To change it:

SET GLOBAL long_query_time = 1;

This example sets the threshold to 1 second, meaning queries running longer than 1 second are logged.

Step 5: Enable Logging of Queries Not Using Indexes (Optional)

Sometimes, you want to log queries that do not use indexes, as these can also degrade performance:

SET GLOBAL log_queries_not_using_indexes = ON;

Step 6: Make Configuration Persistent

Changes made using SET GLOBAL commands are lost after a server restart. To make slow query logging persistent, modify the MySQL or MariaDB configuration file, typically my.cnf or my.ini. Add the following under the [mysqld] section:

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON

Adjust paths and values to suit your environment. After saving the file, restart the database server to apply changes:

sudo systemctl restart mysql (Linux with systemd)

Step 7: Verify Logging

Run some queries expected to be slow or inefficient, then check the slow query log file for entries:

tail -f /var/log/mysql/slow-query.log

You should see logged queries along with their execution times and other details.

Best Practices

Set an Appropriate Long Query Time

Choosing a suitable threshold is crucial. Too low a value can flood logs with minor delays, while too high may miss important slow queries. Start with 1 second and adjust based on your application's performance requirements.

Rotate Slow Query Logs Regularly

Slow query logs can grow large quickly. Implement log rotation using tools like logrotate on Linux to prevent disk space exhaustion.

Analyze Logs Periodically

Regularly review slow query logs to identify and optimize problematic queries. Use tools or scripts to automate this analysis.

Use Indexes Effectively

Enable logging of queries not using indexes and review these logs to add missing indexes, which can dramatically improve query performance.

Limit Logging in Production

In high-load production environments, consider enabling slow query logging selectively or during off-peak hours to reduce performance impact.

Tools and Resources

MySQL Slow Query Log Analyzer

Tools like mysqldumpslow summarize slow query logs by grouping similar queries, making it easier to spot trends.

pt-query-digest

Part of Percona Toolkit, pt-query-digest provides in-depth analysis, ranking queries by impact and frequency.

MySQL Workbench

A GUI tool that includes query profiling features and integrates with slow query logs for visualization.

Official Documentation

Refer to the official MySQL or MariaDB documentation for detailed, up-to-date information on slow query logging options and configuration.

Real Examples

Example 1: Enabling Slow Query Log on a Default MySQL Installation

On a Linux server, edit /etc/mysql/my.cnf:

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

Then restart the MySQL service:

sudo systemctl restart mysql

Run queries and monitor the slow query log:

tail -f /var/log/mysql/mysql-slow.log

Example 2: Using pt-query-digest to Analyze Slow Queries

After collecting slow query logs, run:

pt-query-digest /var/log/mysql/mysql-slow.log

This produces a detailed report highlighting the queries with the biggest performance impact.

FAQs

What is the slow query log used for?

It is used to capture and analyze SQL queries that take longer than a defined threshold to execute, helping optimize database performance.

Does enabling slow query log affect database performance?

There is some overhead, but generally minimal. However, in very high-load environments, it should be used judiciously.

Can I enable slow query logging without restarting the server?

Yes, using SET GLOBAL slow_query_log = 'ON'; enables it temporarily until the next restart.

Where is the slow query log file stored?

The location is configurable, commonly in /var/log/mysql/ or /var/lib/mysql/. Use SHOW VARIABLES LIKE 'slow_query_log_file'; to find the current path.

How do I stop slow query logging?

Execute SET GLOBAL slow_query_log = 'OFF'; or remove the configuration from the my.cnf file and restart the server.

Conclusion

Enabling and effectively using the slow query log is a powerful way to gain insight into database query performance. By following the steps outlined in this tutorial, you can configure slow query logging tailored to your environment, analyze the data to pinpoint inefficiencies, and implement improvements that enhance both application speed and user satisfaction.

Remember to apply best practices such as setting appropriate thresholds, managing log files properly, and leveraging analysis tools to maximize the benefits of slow query logging. Whether you are a database administrator or a developer, mastering slow query log configuration is an essential skill for maintaining high-performance database systems.