MySQL Slow Query Log: Identify & Fix Performance Issues

Master database optimization: Understand, enable, and leverage the MySQL slow query log on Ubuntu to boost performance.

Estimated Time: Approximately 30 - 45 minutes (initial setup & basic analysis)

Overview: The Importance of the Slow Query Log

The MySQL slow query log is an incredibly valuable tool for identifying performance bottlenecks in your database. It records SQL queries that take longer than a specified amount of time to execute. By analyzing these "slow" queries, you can pinpoint inefficient operations and take steps to optimize them.

Why Monitor Slow Queries?

Performance Bottlenecks: Directly identifies queries that are consuming excessive resources (CPU, I/O) and slowing down your application.
Improved User Experience: Faster queries mean faster page loads and a more responsive application for your users.
Debugging & Optimization: Provides concrete data to guide your indexing strategies, query rewrites, and database schema adjustments.
Cost Savings: Efficient queries can reduce the need for expensive hardware upgrades.

This guide will take you from enabling the slow query log on your Ubuntu MySQL server to interpreting its output and implementing common optimization techniques.

Estimated Time

30 - 45 minutes

(For initial setup and basic analysis. Ongoing optimization is continuous.)

Experience Level

Intermediate

Assumes familiarity with basic MySQL queries, terminal commands, and server administration.

System Requirements & Prerequisites

Server: An Ubuntu 22.04 LTS or 20.04 LTS server with MySQL installed (e.g., via our LAMP stack guide ).
Sudo Privileges: Access to a terminal as a non-root user with sudo privileges.
MySQL Access: Ability to log into the MySQL command-line client (e.g., `mysql -u root -p`).
Free Disk Space: Be mindful of disk space. Slow logs can grow large on busy servers if `long_query_time` is set too low.

Important Considerations Before Enabling

While the slow log is powerful, there are important points to remember:

Performance Overhead: Enabling logging has a minor performance overhead. It's usually negligible, but be aware.
Disk Space Consumption: On busy servers, the slow log file can grow very quickly. Ensure you have a log rotation strategy (usually handled by `logrotate` in Ubuntu, but verify).
`long_query_time` Setting: Setting `long_query_time` to 0 (logging all queries) is *never* recommended on a production server, as it will quickly fill your disk and heavily impact performance.
Sensitive Data: Query logs might contain sensitive data if your queries include PII or other confidential information. Manage access to the log files carefully.

Use the slow log for diagnosis, not as a permanent "all queries" log on production.

Step-by-Step Instructions

Step 1: Check Current MySQL Slow Log Status

First, let's see if the slow query log is already enabled and what its current configuration is.

                                            
                                                sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; SHOW VARIABLES LIKE 'log_output';"

You'll likely see `slow_query_log` is `OFF`, `long_query_time` is `10` seconds, and `log_output` is `FILE`. `long_query_time` defines the threshold (in seconds) for a query to be considered "slow".

Step 2: Backup MySQL Configuration File

Always create a backup of your MySQL configuration file before making changes. The main configuration file on Ubuntu is typically `/etc/mysql/mysql.conf.d/mysqld.cnf`.

                                            
                                                sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

A backup is now saved at `/etc/mysql/mysql.conf.d/mysqld.cnf.bak`.

Step 3: Enable Slow Query Log in MySQL Configuration

Now, we'll edit the MySQL configuration file to enable the slow query log and set its parameters.


                                                sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Locate the `[mysqld]` section. Add or uncomment the following lines. We recommend a `long_query_time` of `1` or `2` seconds for initial diagnosis, which can be adjusted later.

# General and Slow logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

# Optional: Log queries that don't use indexes, even if fast. Use with caution!
# log_queries_not_using_indexes = 1

`slow_query_log = 1` turns the log on.
`slow_query_log_file` specifies the log file path.
`long_query_time = 1` logs queries taking longer than 1 second.
`log_output = FILE` ensures logs are written to a file. The `log_queries_not_using_indexes` option can be extremely noisy but useful for deep index optimization.

Save the file (`Ctrl+O`, `Enter`) and exit `nano` (`Ctrl+X`).

Step 4: Create Log File and Set Permissions

Ensure the directory and log file exist and have the correct permissions so MySQL can write to it.

                                            
                                                sudo mkdir -p /var/log/mysql
                                            
                                                sudo touch /var/log/mysql/mysql-slow.log
                                            
                                                sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
                                            
                                                sudo chmod 640 /var/log/mysql/mysql-slow.log

These commands create the directory (if it doesn't exist), create the log file, set MySQL as the owner, and restrict read/write access appropriately.

Step 5: Restart MySQL Service

For the configuration changes to take effect, you must restart the MySQL service.


                                                sudo systemctl restart mysql

Check MySQL status:


                                                sudo systemctl status mysql

Ensure MySQL is `active (running)`.

Step 6: Verify Slow Log is Active and Capturing Queries

Confirm the log is active and test it with a deliberately slow query.

1. Verify variables in MySQL:

                                            
                                                sudo mysql -u root -p -e "SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time';"

`slow_query_log` should now be `ON`, and `long_query_time` should be `1` (or your chosen value).

2. Run a test slow query:


                                                sudo mysql -u root -p -e "SELECT SLEEP(2);"

3. Check the slow log file:


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

You should see the `SELECT SLEEP(2);` query in the log output, indicating the log is working.

Step 7: Understanding Slow Log Entries

Each entry in the slow log provides crucial details about the slow query:

# Time: 2023-10-27T10:30:05.123456Z
# User@Host: someuser[someuser] @ localhost [127.0.0.1]  Id: 12345
# Query_time: 2.000010  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1698421805;
SELECT SLEEP(2);

`Time`: When the query was executed.
`User@Host`: Which user and host initiated the query.
`Query_time`: The total time (in seconds) the query took to execute. This is your primary metric.
`Lock_time`: How long the query waited for a lock (e.g., table lock). High `Lock_time` can indicate concurrency issues.
`Rows_sent`: The number of rows sent to the client.
`Rows_examined`: The number of rows MySQL had to examine to execute the query. A high ratio of `Rows_examined` to `Rows_sent` often indicates a missing index or inefficient query.
Actual Query: The SQL statement itself.

Step 8: Analyzing the Slow Log with `mysqldumpslow`

Manually sifting through a large slow log is impractical. `mysqldumpslow` is a command-line tool that summarizes slow log contents.

1. Get a summary sorted by average query time (top 10):


                                                sudo mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

`-s at`: Sorts by average time.
`-t 10`: Shows the top 10 unique queries.
`mysqldumpslow` automatically normalizes queries (replaces specific values with `N` or `S`), making it easier to group similar queries.

Other useful `mysqldumpslow` options:

`mysqldumpslow -s c -t 10`: Top 10 queries by count.
`mysqldumpslow -s r -t 10`: Top 10 queries by rows examined.
`mysqldumpslow -s l -t 10`: Top 10 queries by lock time.
`mysqldumpslow -a`: Don't group queries that only differ in their `SELECT *` or specific column lists.

Advanced Tool: For more in-depth analysis and reporting, consider Percona Toolkit's `pt-query-digest`, a powerful alternative to `mysqldumpslow`. Installation: `sudo apt install percona-toolkit`

Step 9: Possible Fixes and Optimization Strategies

Once you've identified slow queries, it's time to optimize! This often involves an iterative process of testing and refining.

A. Indexing: The Most Common Fix

Indexes allow MySQL to quickly locate rows without scanning the entire table. Use the `EXPLAIN` keyword to understand how MySQL executes your query.

Using `EXPLAIN`:


                                                EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Look for `type: ALL` (full table scan) or high `rows` values. `possible_keys` suggests indexes MySQL *could* use. `key` shows what index it *actually* used. A good `EXPLAIN` output often has `type: ref`, `eq_ref`, `range`, or `const`, and `key` being used.

Creating an index:


                                                CREATE INDEX idx_users_email ON users (email);

When to index:

Columns used in `WHERE` clauses.
Columns used in `JOIN` conditions.
Columns used in `ORDER BY` and `GROUP BY` clauses.
Columns with high cardinality (many unique values).

When not to index (or use with caution):

Small tables (full table scan is faster than index lookup).
Columns with low cardinality (e.g., a boolean `is_active` column).
Tables with very frequent `INSERT`, `UPDATE`, `DELETE` operations (indexes add overhead).

B. Query Rewriting and Optimization

Avoid `SELECT *`: Only select the columns you actually need. This reduces network traffic and I/O.
Optimize `LIKE` clauses: `LIKE 'value%'` can use an index, but `LIKE '%value'` or `LIKE '%value%'` cannot effectively. Consider full-text search for such needs.
Efficient `JOIN`s: Ensure `JOIN` conditions are indexed. Use `INNER JOIN` over `LEFT JOIN` if all rows from both tables are required. Review `EXPLAIN` output for join order.
Use `LIMIT` appropriately: Always use `LIMIT` with `ORDER BY` for predictable results and to restrict the number of rows processed.
Avoid functions on indexed columns: `WHERE YEAR(date_column) = 2023` prevents index usage. Instead, use `WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31'`.
Break down complex queries: Sometimes, splitting a very complex query into multiple simpler queries or using temporary tables can be more efficient.

C. MySQL Server Configuration Tuning

Adjusting MySQL's configuration can significantly impact performance, especially for InnoDB engines.

`innodb_buffer_pool_size`: This is the most critical setting for InnoDB. It's where MySQL caches data and indexes. Set it to 50-80% of your available RAM if MySQL is the primary application on the server.
`tmp_table_size` and `max_heap_table_size`: Increase these if your complex queries require large temporary tables, to keep them in memory rather than on disk.
`max_connections`: Adjust based on your application's concurrency needs to prevent connection errors.
`query_cache_size`: (Deprecated in MySQL 8.0) For older versions, this can help, but often causes contention on busy servers. Use with caution.

Caution: MySQL configuration tuning is complex. Always change one setting at a time, monitor the results, and have a backup of your `my.cnf`. Incorrect settings can degrade performance or prevent MySQL from starting.

D. Hardware & System Resources

Sometimes, the solution isn't software; it's hardware.

More RAM: If you're constantly hitting swap or your `innodb_buffer_pool_size` is constrained, more RAM is essential.
Faster Storage (SSD): For I/O-bound queries (many reads/writes), an SSD can dramatically improve performance compared to a traditional HDD.
Faster CPU: For CPU-bound queries (complex calculations, heavy sorting).

E. Application-Level Caching

For data that doesn't change frequently but is accessed heavily, caching at the application layer can offload the database entirely.

Implement caching solutions like Redis or Memcached for frequently requested data or query results.
Use query caching provided by your ORM (Object-Relational Mapper) or framework.

F. Database Design (Advanced)

Denormalization: In some cases, intentionally duplicating data to avoid complex joins can improve read performance at the cost of data redundancy and increased write complexity. Use cautiously.
Partitioning: For very large tables, dividing them into smaller, more manageable parts can improve query performance by allowing MySQL to scan only relevant partitions.

Final Verification Checklist

After implementing fixes, verify their effectiveness:

Slow Log Active: `SHOW VARIABLES LIKE 'slow_query_log';` is `ON`.
Check `EXPLAIN`: Re-run `EXPLAIN` on the optimized queries to confirm index usage and reduced `rows` examined.
Monitor Slow Log: Observe the slow log after a period of activity. Are the problematic queries still appearing, or have their `Query_time` values decreased?
Application Performance: Does your application feel faster? Monitor application response times.
Server Metrics: Use `top`, `htop`, `vmstat`, `iostat` to monitor CPU, memory, and I/O usage. Has the optimization reduced resource consumption?

Conclusion & Next Steps

You've successfully set up MySQL slow query logging, learned to analyze its output, and explored a range of powerful optimization techniques. Database performance tuning is an ongoing process—your application and data will evolve, and so too should your optimization efforts.

Keep these practices in mind for continuous improvement:

Continuous Monitoring: Regularly check your slow log, especially after code deployments or data growth. Consider automating this.
Iterative Optimization: Start with the biggest offenders, implement one change at a time, and measure the impact.
Learn More: Dive deeper into specific MySQL topics like `InnoDB` storage engine specifics, advanced indexing strategies (composite indexes, covering indexes), and query execution plans.
Regular Backups: Always ensure your databases are backed up before making significant schema or configuration changes.
Log Rotation: Ensure `logrotate` is configured for `/var/log/mysql/mysql-slow.log` to prevent it from filling your disk. (Usually automatic on Ubuntu).

Need Expert Database Performance Tuning or Optimization?