MySQL Slow Query Log Analysis for Performance
Finding bottlenecks in your database can be tough, but the MySQL slow query log is a goldmine. I'll walk through how I use it to pinpoint the queries slowing down my servers.
Finding bottlenecks in your database can be tough, but the MySQL slow query log is a goldmine. I'll walk through how I use it to pinpoint the queries slowing down my servers. It’s a skill I find myself using regularly, especially when debugging performance issues on client boxes I usually manage.
Enabling the Slow Query Log
First, you need to make sure the slow query log is enabled. I usually edit the my.cnf file. The location varies depending on your distribution — on Ubuntu 24.04, it’s often /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf. On AlmaLinux 9, it's typically /etc/my.cnf.d/mysql-server.cnf.
Add or modify these lines under the [mysqld] section:
long_query_time = 2 # Queries taking longer than 2 seconds are logged
slow_query_log = 1 # Enable the slow query log
slow_query_log_file = /var/log/mysql/mysql-slow.log # Where the log file is stored
log_output = FILE # Log to a file
Then, reload MySQL for the changes to take effect:
systemctl reload mysql # Or service mysql reload, depending on your distro```
## Analyzing the Slow Query Log
Once the log is enabled, queries exceeding `long_query_time` will be recorded. The raw log file is hard to read, so I use `mysqldumpslow` to summarize it. This tool is usually installed with the MySQL client package.
Here's how I use it. First, to get the top 10 slowest queries:
```bash
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log```
`-s t` sorts by total time, and `-t 10` shows the top 10. You can also sort by lock time (`-s l`) or number of rows examined (`-s r`). This gives you a quick overview of which queries are causing the most trouble. The output includes the number of times each query was executed, the average time it took, and the total time spent.
To get more detailed information about a specific query, use the `-d` option to specify a pattern. For example, to look at queries involving a table named `orders`:
```bash
mysqldumpslow -d
Related reading
Frequently asked questions
What is a slow query log?
It's a log file in MySQL/MariaDB that records queries taking longer than a specified time to execute. It helps identify inefficient queries impacting performance.
How do I enable the slow query log?
You'll need to modify your MySQL configuration file (usually my.cnf or my.ini). I'll show you the settings below. Don't forget to reload the server after making changes.
What's the difference between `long_query_time` and `slow_query_log`?
The slow_query_log setting enables or disables the log, while long_query_time defines the threshold (in seconds) for a query to be considered 'slow' and logged.
