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 JOINs: 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.