How to Monitor MySQL Slow Queries and Performance

When your application starts lagging, the database is often the first suspect. MySQL is powerful, but poorly optimized queries can silently drain resources and frustrate users. MySQL slow query monitoring helps you find these problem queries before they become outages.

This guide walks you through every practical method: enabling and reading the slow query log, using performance_schema, running EXPLAIN, and choosing the right monitoring tool for your environment. Whether you run MySQL on bare metal, Amazon RDS, Google Cloud SQL, or in a container, the same core principles apply.

What Counts as a Slow MySQL Query?

MySQL considers a query slow when its execution time exceeds the value set in long_query_time. The default is 10 seconds, which is far too high for most web applications. Many teams set this to 1 second or even 0.5 seconds to catch more queries early.

A query can be slow for several reasons:

• Full table scans because no index exists or the optimizer cannot use one
• Lock contention from concurrent writes
• Sorting and grouping large result sets without indexes
• N+1 query patterns from ORM frameworks
• Insufficient memory forcing disk-based temp tables

A useful signal: compare rows_examined to rows_sent in the slow query log. If a query examines 500,000 rows but only returns 10, something is wrong. This ratio is one of the clearest indicators of a missing index or a poorly written query.

Method 1: Enable and Use the MySQL Slow Query Log

The slow query log is MySQL’s built-in mechanism for recording queries that exceed your defined time threshold. It is the most widely used and simplest way to start mysql slow query monitoring.

Enabling via the Configuration File (Persistent)

Edit your my.cnf or my.ini file and add these lines under the [mysqld] section:

[mysqld]slow_query_log            = 1slow_query_log_file       = /var/log/mysql/mysql-slow.loglong_query_time           = 1log_queries_not_using_indexes = 1min_examined_row_limit    = 100

[mysqld]slow_query_log            = 1slow_query_log_file       = /var/log/mysql/mysql-slow.loglong_query_time           = 1log_queries_not_using_indexes = 1min_examined_row_limit    = 100

Restart MySQL for the changes to take effect:

sudo systemctl restart mysql

sudo systemctl restart mysql

Enabling at Runtime (No Restart Required)

If you cannot restart MySQL, enable the slow log live from a MySQL session:

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

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

These settings take effect immediately but will be lost on server restart unless you also update my.cnf.

Key Configuration Parameters

Reading the Slow Query Log

A typical entry in the slow query log looks like this:

# Time: 2025-12-01T14:32:01.123456Z# User@Host: app_user[app_user] @ localhost []  Id: 42# Query_time: 4.832150  Lock_time: 0.000321  Rows_sent: 10  Rows_examined: 872541SET timestamp=1701441121;SELECT * FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC;

# Time: 2025-12-01T14:32:01.123456Z# User@Host: app_user[app_user] @ localhost []  Id: 42# Query_time: 4.832150  Lock_time: 0.000321  Rows_sent: 10  Rows_examined: 872541SET timestamp=1701441121;SELECT * FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC;

Key fields to pay attention to:

• Query_time: total execution time in seconds
• Lock_time: time spent waiting for a lock (high values indicate write contention)
• Rows_examined vs Rows_sent: a large gap means the query scanned many rows it did not need

Method 2: Analyze Logs with mysqldumpslow and pt-query-digest

Raw log files grow fast. On busy servers, you need tools to aggregate and rank queries by impact.

Using mysqldumpslow

mysqldumpslow is included with every MySQL installation. It groups similar queries (replacing literal values with N or S) and sorts them by execution time:

# Show the 10 slowest queriesmysqldumpslow -t 10 /var/log/mysql/mysql-slow.log
# Sort by total time, show top 5mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log

# Show the 10 slowest queriesmysqldumpslow -t 10 /var/log/mysql/mysql-slow.log
# Sort by total time, show top 5mysqldumpslow -s t -t 5 /var/log/mysql/mysql-slow.log

Using pt-query-digest (Percona Toolkit)

pt-query-digest from the Percona Toolkit is more powerful. It produces a report ranking queries by total execution time, count, and average time:

# Install Percona Toolkit (Ubuntu/Debian)sudo apt-get install percona-toolkit
# Analyze the slow query logpt-query-digest /var/log/mysql/mysql-slow.log

# Install Percona Toolkit (Ubuntu/Debian)sudo apt-get install percona-toolkit
# Analyze the slow query logpt-query-digest /var/log/mysql/mysql-slow.log

The output shows each query fingerprint ranked by total time consumed. Focus on the top 3-5 queries by total time: fixing them usually delivers the biggest performance gains.

Method 3: Use performance_schema for Real-Time Monitoring

performance_schema is an in-memory storage engine that captures detailed runtime statistics about MySQL execution. Unlike the slow query log (which writes to disk), performance_schema works in memory and is available by default in MySQL 5.6 and later.

Check that it is enabled:

SHOW VARIABLES LIKE 'performance_schema';-- Expected output:-- +--------------------+-------+-- | Variable_name      | Value |-- +--------------------+-------+-- | performance_schema | ON    |-- +--------------------+-------+

SHOW VARIABLES LIKE 'performance_schema';-- Expected output:-- +--------------------+-------+-- | Variable_name      | Value |-- +--------------------+-------+-- | performance_schema | ON    |-- +--------------------+-------+

Finding the Slowest Queries

Query the events_statements_summary_by_digest table to find queries by total execution time:

SELECT  DIGEST_TEXT,  COUNT_STAR                       AS exec_count,  ROUND(SUM_TIMER_WAIT / 1e12, 2)  AS total_time_sec,  ROUND(AVG_TIMER_WAIT / 1e12, 4)  AS avg_time_sec,  SUM_ROWS_EXAMINED,  SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestORDER BY total_time_sec DESCLIMIT 10;

SELECT  DIGEST_TEXT,  COUNT_STAR                       AS exec_count,  ROUND(SUM_TIMER_WAIT / 1e12, 2)  AS total_time_sec,  ROUND(AVG_TIMER_WAIT / 1e12, 4)  AS avg_time_sec,  SUM_ROWS_EXAMINED,  SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestORDER BY total_time_sec DESCLIMIT 10;

Finding Queries Not Using Indexes

SELECT  DIGEST_TEXT,  COUNT_STAR,  SUM_NO_INDEX_USED,  SUM_NO_GOOD_INDEX_USEDFROM performance_schema.events_statements_summary_by_digestWHERE SUM_NO_INDEX_USED > 0ORDER BY SUM_NO_INDEX_USED DESCLIMIT 10;

SELECT  DIGEST_TEXT,  COUNT_STAR,  SUM_NO_INDEX_USED,  SUM_NO_GOOD_INDEX_USEDFROM performance_schema.events_statements_summary_by_digestWHERE SUM_NO_INDEX_USED > 0ORDER BY SUM_NO_INDEX_USED DESCLIMIT 10;

Any query showing a high SUM_NO_INDEX_USED count should be reviewed with EXPLAIN immediately.

Method 4: Use EXPLAIN to Understand Why a Query Is Slow

Once you have identified a problem query, EXPLAIN tells you how MySQL plans to execute it. This is the most important step in diagnosing and fixing a slow query.

EXPLAIN SELECT * FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC;

EXPLAIN SELECT * FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC;

Key EXPLAIN Output Columns

Using EXPLAIN ANALYZE (MySQL 8.0+)

EXPLAIN ANALYZE actually runs the query and shows real execution times for each step:

EXPLAIN ANALYZESELECT * FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC;

EXPLAIN ANALYZESELECT * FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC;

This is the most accurate way to measure query performance because it shows actual row counts and time spent at each stage of execution.

Method 5: Monitor MySQL Performance Metrics in Real Time

The slow query log captures problems after they happen. To monitor MySQL health proactively, you should also track key server-level metrics.

Key Metrics to Monitor

Checking the Current Slow Query Rate

-- Total slow queriesSHOW GLOBAL STATUS LIKE 'Slow_queries';
-- Current long_query_time settingSHOW VARIABLES LIKE 'long_query_time';

-- Total slow queriesSHOW GLOBAL STATUS LIKE 'Slow_queries';
-- Current long_query_time settingSHOW VARIABLES LIKE 'long_query_time';

MySQL Slow Query Monitoring on Cloud Databases

Cloud-managed MySQL instances handle the underlying server but you still need to configure slow query logging through their respective interfaces.

Amazon RDS for MySQL

On Amazon RDS, you cannot edit my.cnf directly. Instead, use a custom DB parameter group:

• Set slow_query_log = 1 in your RDS parameter group
• Set long_query_time to your threshold (start with 1)
• Set log_output = FILE to integrate with Amazon CloudWatch Logs
• Use the RDS Database Insights dashboard to view slow SQL queries in a visual timeline

Google Cloud SQL for MySQL

On Cloud SQL, configure slow query logging using gcloud CLI flags or the Cloud Console:

gcloud sql instances patch my-mysql-instance \  --database-flags \  slow_query_log=on,\  long_query_time=1,\  log_output=FILE

gcloud sql instances patch my-mysql-instance \  --database-flags \  slow_query_log=on,\  long_query_time=1,\  log_output=FILE

Logs integrate with Cloud Logging (formerly Stackdriver), giving you search, filtering, and alerting. You can set a fractional threshold (e.g., 0.5) to capture sub-second queries. 

Azure Database for MySQL

Azure exposes the slow query log as the Query Performance Insight blade in the Azure portal. You can configure slow_query_log and long_query_time through Server Parameters in the portal without touching a config file. 

Fixing Slow Queries: Common Solutions

Finding slow queries is only half the work. Here are the most effective fixes, in order of impact:

1. Add Missing Indexes

If EXPLAIN shows type: ALL or key: NULL, the query is doing a full table scan. Add an index on the columns in your WHERE, ORDER BY, and JOIN clauses:

-- Check existing indexesSHOW INDEX FROM orders;
-- Add a composite indexALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

-- Check existing indexesSHOW INDEX FROM orders;
-- Add a composite indexALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

2. Rewrite the Query

Avoid SELECT * and retrieve only the columns you need. Replace correlated subqueries with JOINs. Break complex queries into smaller, indexed lookups.

3. Optimize Schema Design

Use appropriate data types (INT instead of VARCHAR for numeric IDs). Normalize data to reduce duplication. Add covering indexes for your most frequent query patterns.

4. Check InnoDB Buffer Pool

If your working dataset fits in memory, MySQL can serve most queries from the buffer pool without disk I/O. A common guideline is to set innodb_buffer_pool_size to 70-80% of available RAM on a dedicated database server:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Check buffer pool hit rate (should be above 99%)SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Check buffer pool hit rate (should be above 99%)SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

Conclusion

Disclaimer: This article is for informational purposes only. MySQL configurations and cloud platform interfaces may change over time, so always refer to official documentation before making changes to a production environment.

FAQs