MySQL: Slow Query Log and Performance Diagnosis

Understanding Slow Queries

When your website feels sluggish but server CPU, memory, and disk look fine, the culprit is almost always slow database queries. The slow query log helps you identify which queries are bottlenecking your application.

Enable the Slow Query Log

Configuration File Method (Persistent)

Edit your MySQL configuration file:

nano /etc/mysql/my.cnf
# or for MariaDB
nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add these lines in the [mysqld] section:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Explanation:

• slow_query_log = 1: Enable logging
• slow_query_log_file: Where to save logs
• long_query_time = 1: Log queries taking longer than 1 second
• log_queries_not_using_indexes: Log queries that don't use indexes (useful for optimization)

Restart MySQL:

systemctl restart mysql
# or
systemctl restart mariadb

Runtime Method (Temporary)

If you need to enable immediately without restart:

mysql -u root -p

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;

Verify:

SHOW VARIABLES LIKE 'slow%';

Monitor Running Queries

While troubleshooting, see which queries are currently executing:

SHOW PROCESSLIST;

For more detail:

SHOW FULL PROCESSLIST;

Columns:

• Id: Query ID
• User: Database user
• Host: Connection source
• Database: Active database
• Command: Command type (Query, Sleep, etc.)
• Time: Seconds the query has been running
• State: Current state
• Info: The actual SQL query (truncated unless FULL)

Kill a Stuck Query

If a query is hanging and blocking others:

KILL QUERY <id>;

Replace <id> with the process ID from SHOW PROCESSLIST.

Analyze the Slow Query Log

Option 1: mysqldumpslow (Simple)

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

Parameters:

• -s t: Sort by query time (slowest first)
• -s c: Sort by count (most frequent)
• -s l: Sort by lock time
• -t 10: Show top 10 queries

Output shows query counts and average execution time.

Option 2: pt-query-digest (Advanced - Percona Toolkit)

Install:

apt-get install percona-toolkit

Run:

pt-query-digest /var/log/mysql/slow.log

Or analyze in real-time:

pt-query-digest --processlist h=localhost

This provides detailed analysis including:

• Query execution statistics
• Profile of each query pattern
• Recommendations for optimization

Optimize Slow Queries with EXPLAIN

Once you've identified a slow query, use EXPLAIN to understand how MySQL executes it:

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

Key columns to check:

• type: How the table is accessed (const, ref, range, index, ALL)
  • ALL = full table scan (slow), add an index
  • ref or const = index used (good)
• rows: Estimated number of rows examined
• Extra: Additional info ("Using filesort" = slow, "Using temporary" = slow)

Common Optimization Strategies

1. Add Missing Indexes

CREATE INDEX idx_email ON users(email);

2. Optimize JOIN Queries

-- Ensure tables are joined on indexed columns
EXPLAIN SELECT users.id, posts.title
        FROM users
        INNER JOIN posts ON users.id = posts.user_id
        WHERE users.status = 'active';

3. Avoid SELECT *

-- BAD: Fetches all columns
SELECT * FROM large_table;

-- GOOD: Only fetch needed columns
SELECT id, name, email FROM large_table;

4. Use LIMIT

-- Good: Fetch only what you need
SELECT * FROM logs LIMIT 1000;

Tune MySQL Configuration

innodb_buffer_pool_size

The most critical setting for performance. This is MySQL's cache for data and indexes. Set it to approximately 70% of available RAM on a dedicated database server:

# On a 32GB server:
# innodb_buffer_pool_size = 22G

Edit /etc/mysql/my.cnf:

[mysqld]
innodb_buffer_pool_size = 22G

Restart MySQL:

systemctl restart mysql

Other Important Tuning Parameters

[mysqld]
max_connections = 100              # Adjust based on expected connections
query_cache_type = 1               # Enable query cache (MariaDB/MySQL 5.7)
query_cache_size = 64M
innodb_flush_log_at_trx_commit = 2 # Balance safety vs performance

Check Table and Database Sizes

Find space-consuming tables:

SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

List all databases by size:

SELECT
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

Real-World Troubleshooting Example

Scenario: Your WordPress site is slow. CPU and memory are fine.

Step 1: Enable slow query log

slow_query_log = 1
long_query_time = 0.5

Step 2: Generate some traffic for a few minutes, then analyze:

mysqldumpslow -s t -t 5 /var/log/mysql/slow.log

Output shows:

Count: 150  Time=2.50s (375s)  Lock=0.01s (1s)  Rows_sent=1  Rows_examined=1000000
SELECT * FROM wp_postmeta WHERE post_id = ?

Step 3: Check the query:

EXPLAIN SELECT * FROM wp_postmeta WHERE post_id = 1;

Result shows ALL (full table scan).

Step 4: Add an index:

CREATE INDEX idx_postmeta_post_id ON wp_postmeta(post_id);

Step 5: Verify improvement:

EXPLAIN SELECT * FROM wp_postmeta WHERE post_id = 1;

Now shows ref (using index). Query time drops from 2.5s to 0.05s.

Quick Optimization Checklist

• Enable slow query log with long_query_time = 1
• Run mysqldumpslow or pt-query-digest to identify worst queries
• Use EXPLAIN on slow queries
• Add indexes to columns used in WHERE and JOIN clauses
• Avoid SELECT *: fetch only needed columns
• Check innodb_buffer_pool_size (should be ~70% of RAM)
• Use SHOW PROCESSLIST to monitor live queries
• Set up monitoring on slow query log size
• Review application code for unnecessary queries