Open New Support Ticket 
How to Enable the Slow Query Log in MySQL® or MariaDB
How to Enable the Slow Query Log in MySQL® or MariaDB
As databases grow and become a core component of web applications, performance tuning becomes critical. In MySQL® and MariaDB, one of the most effective ways to diagnose performance bottlenecks is by enabling the slow query log. This log helps identify queries that take too long to execute, allowing you to optimize them and improve the overall performance of your database.
In this guide, we’ll explain what the slow query log is, why it’s important, and how to enable and configure it in MySQL and MariaDB.
What is the Slow Query Log?
The slow query log is a log file that records SQL queries that take longer than a specified amount of time (the threshold) to execute. By default, this threshold is set to 10 seconds, but it can be customized. Queries that exceed this time limit are logged, along with information such as how long the query took and the date and time of execution. This is an essential tool for developers and database administrators to identify and optimize slow-running queries, helping improve database performance.
Why Enable the Slow Query Log?
Enabling the slow query log helps:
- Identify Performance Bottlenecks: Slow queries can significantly impact the performance of your application. The log helps you identify these problematic queries.
- Optimization: By examining the slow queries, you can identify areas where indexes, query restructuring, or caching could improve performance.
- Monitor Query Performance: For ongoing performance tuning, the slow query log provides insights into how query execution times change over time.
How to Enable the Slow Query Log in MySQL or MariaDB
Enabling the slow query log involves modifying the configuration file and adjusting some settings directly in the MySQL/MariaDB instance. Here’s how to do it step by step.
Step 1: Access MySQL/MariaDB Configuration File
The MySQL and MariaDB configuration files are usually located at:
- For MySQL: /etc/my.cnf or /etc/mysql/my.cnf
- For MariaDB: /etc/my.cnf.d/server.cnf or /etc/mysql/mariadb.cnf
Use your preferred text editor to open the configuration file. For example, with nano, you can run the following command:
sudo nano /etc/my.cnf
Step 2: Modify the Configuration File
In the configuration file, locate the [mysqld] section. If it doesn’t exist, you can create it. Add or modify the following lines to enable the slow query log:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2- slow_query_log = 1: Enables the slow query log.
- slow_query_log_file: Specifies the location where the slow query log file will be saved. You can set a custom path and filename, but ensure the directory has proper write permissions for MySQL/MariaDB.
- long_query_time = 2: Sets the threshold for what qualifies as a “slow” query. In this case, any query taking longer than 2 seconds will be logged. You can adjust this value depending on your performance needs.
Step 3: Restart MySQL/MariaDB Service
After saving the changes to the configuration file, restart the MySQL or MariaDB service for the changes to take effect.
- For systems using systemd:
sudo systemctl restart mysqld # For MySQL
sudo systemctl restart mariadb # For MariaDB
- For systems using init.d:
sudo service mysqld restart # For MySQL
sudo service mariadb restart # For MariaDB
Step 4: Verifying the Slow Query Log is Enabled
To verify that the slow query log is enabled, log into the MySQL/MariaDB client by running:
mysql -u root -p
Once logged in, run the following command:
SHOW VARIABLES LIKE 'slow_query_log';
You should see something like this:
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| slow_query_log | ON |
+-----------------+-------+
This confirms that the slow query log is enabled.
Step 5: Analyzing the Slow Query Log
The slow query log records queries in the file specified in the configuration. To view the content of the slow query log, you can use a text viewer like less, cat, or tail:
sudo less /var/log/mysql/slow-query.log
You will see entries similar to the following:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: root[root] @ localhost []
# Query_time: 4.561529 Lock_time: 0.000115 Rows_sent: 1 Rows_examined: 50000
SET timestamp=1697030723;
SELECT * FROM large_table WHERE column = 'value';
The log provides details such as the query execution time, locking time, the number of rows sent and examined, and the actual SQL query.
Optional Configuration for the Slow Query Log
There are additional configuration options you can set to tailor the slow query log for your specific needs.
1. Log Queries Without Indexes
You can also enable logging for queries that do not use indexes, which are often a source of performance issues. Add the following line to your MySQL/MariaDB configuration:
log_queries_not_using_indexes = 1
This will log any query that doesn’t use an index, helping you identify and optimize poorly performing queries.
2. Limit Log File Size
To prevent the slow query log from growing too large, you can rotate the logs or limit their size. On Linux systems, you can use logrotate to manage the size and rotation of MySQL/MariaDB log files.
Here’s a basic example for /etc/logrotate.d/mysql:
/var/log/mysql/slow-query.log {
daily
rotate 7
missingok
notifempty
compress
delaycompress
postrotate
/usr/bin/systemctl reload mysql >/dev/null 2>&1 || true
endscript
}
This configuration rotates the log file daily and keeps the last seven logs, ensuring the slow query log doesn’t consume too much disk space.
Step 6: Disabling the Slow Query Log
Once you have identified and optimized your slow queries, you may want to disable the slow query log to conserve resources. To do this, edit the configuration file again and set slow_query_log to 0:
[mysqld]
slow_query_log = 0Restart the MySQL or MariaDB service to apply the changes.
Conclusion
The slow query log is an invaluable tool for database performance tuning in MySQL and MariaDB. By logging queries that take too long to execute, you can identify bottlenecks and optimize query performance. Whether you are managing a small database or a large-scale enterprise application, enabling the slow query log is a proactive way to ensure your databases remain fast and efficient.
By following the steps in this guide, you should be able to enable, configure, and analyze the slow query log to optimize your MySQL or MariaDB database performance effectively.
