MySQL slow queries: from Slow_queries to the slow log to the fix

When Slow_queries climbs but the slow log is empty, or long_query_time is still at the default 10 seconds, you have an instrumentation gap. Slow_queries increments regardless of whether slow_query_log is ON, so a rising counter with no log detail is a dead end.

This guide moves from the status counter to slow log configuration, then to performance_schema digests, and finally to resource signals that explain why queries slowed. The goal is to help you decide in minutes whether you face a query plan regression, lock contention, buffer pool pressure, or a configuration gap.

What this means

Slow_queries is a cumulative counter that increments for every statement whose execution time exceeds long_query_time, even when slow_query_log is OFF or log_output excludes FILE. The default long_query_time of 10 seconds is too high for OLTP; a one-second query already hurts user experience but stays invisible unless you lower the threshold.

Because the counter is cumulative, the absolute value is meaningless without normalizing against query volume. The operational signal is the Slow_queries rate divided by the Questions rate. A sudden step-change in that ratio correlates with a deployment, schema change, or infrastructure event.

flowchart TD
    A[Slow_queries rising] --> B{long_query_time <= 1s?}
    B -->|No| C[Lower threshold and retest]
    B -->|Yes| D{Ratio step-change?}
    D -->|Yes| E[Check digest table]
    D -->|No| F[Check resource signals]
    E --> G{Digest degraded?}
    G -->|Yes| H[EXPLAIN and fix plan]
    G -->|No| F
    F --> I{Lock waits up?}
    I -->|Yes| J[Kill blocker or add index]
    I -->|No| K{Buffer pool reads up?}
    K -->|Yes| L[Kill scan query or grow pool]
    K -->|No| M[Check tmp tables and indexes]

Common causes

CauseWhat it looks likeFirst thing to check
Slow log misconfiguration (long_query_time too high or logging disabled)Counter climbs but log is empty or has only extreme outliers; sub-threshold latency is ignored.SHOW GLOBAL VARIABLES LIKE 'long_query_time'; and SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
Query plan regression after deploy or upgradeOne digest in events_statements_summary_by_digest shows a latency jump; Handler_read_rnd_next or Select_full_join step-change.EXPLAIN the degraded digest; compare to a previous plan baseline.
Lock contention adding wait timeInnodb_row_lock_waits and Innodb_row_lock_time_avg rising; Threads_running piles up while Questions drops.performance_schema.data_lock_waits (MySQL 8.0+) or SHOW ENGINE INNODB STATUS for lock waits.
Buffer pool / I/O saturationAll query types slow simultaneously; buffer pool hit ratio dropping; Innodb_buffer_pool_reads spiking.Buffer pool hit ratio and Innodb_buffer_pool_wait_free.
Temp table spills and missing indexesCreated_tmp_disk_tables ratio climbing; Sort_merge_passes increasing; specific joins show Select_full_join.SHOW GLOBAL STATUS LIKE 'Created_tmp%'; and SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

Quick checks

Run these read-only checks before making any changes.

-- Verify slow log configuration and threshold
SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_output';

-- Baseline the slow query rate against total volume
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Questions';
-- Compute ratio: delta(Slow_queries) / delta(Questions) over 60 seconds

-- Top query patterns by total execution time
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_latency_ms,
       ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_latency_ms,
       SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Check for lock waits and table scans
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';
SHOW GLOBAL STATUS LIKE 'Handler_read_rnd_next';
SHOW GLOBAL STATUS LIKE 'Select_full_join';

-- Check for temp table spills
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

-- Measure active concurrency load
SHOW GLOBAL STATUS LIKE 'Threads_running';

How to diagnose it

  1. Confirm the instrumentation gap. Check long_query_time. If it is 10 seconds, lower it to 1 second or less for OLTP. Verify slow_query_log is ON and log_output includes FILE. If log_output is NONE, queries are not logged even when the slow log is enabled.

  2. Compute the ratio. Take two samples of Slow_queries and Questions sixty seconds apart. A Slow_queries / Questions ratio that steps up sharply signals a real regression rather than normal traffic growth.

  3. Find the top offenders from performance_schema. Query events_statements_summary_by_digest ordered by SUM_TIMER_WAIT DESC. Look for a digest whose AVG_TIMER_WAIT increased while COUNT_STAR remained flat: that indicates the query itself slowed down, not a traffic spike. A large NULL digest row means the table is full and visibility is lost. Increase performance_schema_digests_size if needed.

  4. Distinguish execution slowness from wait slowness. If Innodb_row_lock_waits and Innodb_row_lock_time_avg are rising, queries are slow because of lock waits, not plan changes. Check data_lock_waits to find the blocker. If Innodb_buffer_pool_reads is spiking and the buffer pool hit ratio is dropping, the bottleneck is disk I/O.

  5. Check for plan regressions. Run EXPLAIN on the degraded digest. Look for full table scans, filesort, or joins without indexes. If the plan changed after a deployment or upgrade, compare it to the previous known-good plan. MySQL 8.0 optimizer changes from 5.7 can degrade existing plans.

  6. Check for temp table and sort spills. A high ratio of Created_tmp_disk_tables to Created_tmp_tables, or rising Sort_merge_passes, indicates that intermediate results are spilling to disk. This often correlates with GROUP BY or ORDER BY on large unindexed result sets.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Slow_queries / Questions ratioNormalizes slow queries for traffic volumeSudden step-change or sustained increase above baseline
events_statements_summary_by_digest avg latencyIdentifies which query pattern regressedPer-digest latency increases while execution count stays flat
Innodb_row_lock_waits rateDistinguishes lock waits from execution slownessSustained increase with rising Threads_running and falling Questions
Handler_read_rnd_next rateIndicates full table scansStep-change correlated with a slow query spike
Innodb_buffer_pool_reads rateReveals I/O-induced slownessExponential rise with a falling buffer pool hit ratio
Created_tmp_disk_tables / Created_tmp_tables ratioShows temp table spillsSustained ratio above 25%

Fixes

Fix slow log visibility

Set long_query_time to 1 second or lower for OLTP. The default of 10 seconds misses almost every operationally relevant slowdown. Enable slow_query_log and set log_output to include FILE. On MySQL 8.0.14 and later, enable log_slow_extra to capture Bytes_sent, Bytes_received, and handler counters. To catch queries that skip indexes regardless of time, enable log_queries_not_using_indexes, but pair it with log_throttle_queries_not_using_indexes to prevent log flooding.

On replicas, remember that slow query logging only applies under STATEMENT or MIXED binlog format when using log_slow_replica_statements (or the deprecated log_slow_slave_statements before 8.0.26). If binlog_format=ROW, slow queries on the replica are silently not logged.

Fix query plan regressions

If EXPLAIN shows a full scan where an index seek was expected, the fix depends on root cause. Stale statistics can sometimes be corrected with ANALYZE TABLE, though this itself can cause regressions by updating statistics to a worse distribution. If an index was dropped, restore it. If the optimizer chose a bad join order, a temporary hint such as FORCE INDEX buys time while you fix statistics or schema. After upgrades, compare plans against the previous version because optimizer changes can degrade existing queries.

Fix lock contention

If data_lock_waits shows a blocking transaction, identify whether it is a long-running user transaction or an idle connection with an open transaction. If safe, kill the blocker with KILL <thread_id>. Warning: killing a transaction forces rollback, which can stall the instance and cause application errors.

For repeated hot-row contention, add a missing index to reduce gap lock ranges, or change application logic to acquire locks in a consistent order. If deadlocks are frequent, enable innodb_print_all_deadlocks to log them for pattern analysis.

Fix buffer pool and I/O pressure

If a specific large scan is evicting hot pages and driving up Innodb_buffer_pool_reads, kill the query only if it is not critical. Warning: killing a running query may cause application errors. The buffer pool will re-warm.

If the working set has grown past the pool size, increase innodb_buffer_pool_size (dynamic in MySQL 5.7.5+). If Innodb_buffer_pool_wait_free is nonzero, queries are blocking on page flushing, which means the pool is undersized or page cleaners cannot keep up.

Fix temp table and sort spills

If Sort_merge_passes is rising, the best fix is usually an index that provides the needed order, rather than increasing sort_buffer_size. If Created_tmp_disk_tables is high due to BLOB or TEXT columns in GROUP BY, consider whether the query can be rewritten, because MEMORY-engine temp tables cannot hold variable-length types and will spill to disk.

Prevention

  • Set long_query_time to 1 second or less in production OLTP environments, and monitor the Slow_queries / Questions ratio rather than the absolute counter.
  • Baseline per-digest latency from performance_schema.events_statements_summary_by_digest so you can detect regressions within minutes of a deploy.
  • Enable log_queries_not_using_indexes with a throttle to catch missing-index queries before they become slow under load.
  • Review execution plans after every MySQL upgrade or major schema change, because plan regression is a documented failure mode between 5.7 and 8.0.
  • Do not rely solely on the slow query log for replicas running ROW binlog format. Use performance_schema digest monitoring instead.

How Netdata helps

  • Charts mysql.slow_queries alongside mysql.questions so you can spot ratio changes without manual sampling.
  • Correlates mysql.threads_running, mysql.innodb_row_lock_waits, and buffer pool hit ratio in one view to distinguish lock waits from I/O slowness.
  • Alerts on Slow_queries rate spikes and Threads_running anomalies before connection pools saturate.
  • Collects per-digest statistics from performance_schema where available so you can identify the regressed query pattern without parsing log files.