MySQL Slow Query Log: Identify & Fix Performance Issues

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.

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`.

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.

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

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.

Step 5: Restart MySQL Service

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

Check MySQL status:

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:

2. Run a test slow query:

3. Check the slow log file:

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):

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.

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`:

Creating an index:

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.

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.