Find High-Cost MySQL Queries with Performance Schema

MySQL Performance Schema records what the server actually spends time on, so you can rank statements by total time instead of guessing from a slow query log threshold. This guide shows you how to read events_statements_summary_by_digest, the sys schema views built on top of it, and the wait event tables that explain where the time goes.

The slow query log only shows statements that crossed long_query_time. Performance Schema shows every digested statement, how many times it ran, how many rows it examined, and how long it waited — which is what you need when a fast query called a million times is the real problem.

Before you start

• A Linux VPS running MySQL 8.0 or 8.4 (MariaDB 10.5+ works for most of this, but its sys schema coverage is thinner).
• SSH access and a MySQL account with the PROCESS privilege. Some performance_schema tables are readable by any user, but thread and wait tables need PROCESS.
• Comfort reading EXPLAIN output. This guide finds the queries; diagnosing slow database performance explains how to fix them.
• A rough idea of your normal traffic, so you can tell a genuine regression from a busy period.

Confirm Performance Schema is running

Check the variable first:

SHOW VARIABLES LIKE 'performance_schema';

If it returns OFF, add this to your my.cnf (see where my.cnf lives if you are unsure of the path):

[mysqld]
performance_schema = ON

Then restart MySQL. On Ubuntu and Debian:

sudo systemctl restart mysql

Next, check which consumers are enabled. Consumers decide what gets recorded:

SELECT NAME, ENABLED FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_statements%' OR NAME LIKE 'events_waits%';

For historical analysis you want the _history_long consumers on. Enable them for the current session:

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_statements_history_long', 'events_waits_history_long');

That change is lost on restart. To make it permanent, add to my.cnf:

[mysqld]
performance-schema-consumer-events-statements-history-long = ON
performance-schema-consumer-events-waits-history-long = ON

Rank statements by total time

This is the core query. It groups statements by digest — the normalised form where literal values are replaced with ? — and sorts by accumulated wait time:

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS calls,
  ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
  ROUND(MAX_TIMER_WAIT/1000000000, 2) AS max_ms,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent,
  SUM_NO_INDEX_USED AS no_index,
  SUM_CREATED_TMP_DISK_TABLES AS tmp_disk
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Timers are in picoseconds, so dividing by 1,000,000,000,000 gives seconds and dividing by 1,000,000,000 gives milliseconds.

How to read the output:

• High total_sec with low avg_ms — a cheap query called far too often. Look at caching or batching in the application, not the SQL.
• High avg_ms — a genuinely slow statement. Take the DIGEST_TEXT and run EXPLAIN on it.
• rows_examined far above rows_sent — the index is not selective enough, or is missing.
• no_index greater than zero — at least some executions did a full table scan.
• tmp_disk greater than zero — temporary tables spilled to disk. Usually a GROUP BY or ORDER BY on unindexed columns.

The sys schema wraps the same data with friendlier column names:

SELECT * FROM sys.statement_analysis LIMIT 20;

Find scans, sorts and temporary tables

Three sys views isolate the patterns that most often cause high cost:

SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;
SELECT * FROM sys.statements_with_temp_tables LIMIT 20;
SELECT * FROM sys.statements_with_sorting LIMIT 20;

statements_with_full_table_scans gives you a query_cost and the ratio of rows examined to rows returned. Anything scanning thousands of rows to return a handful is a candidate for a new index.

statements_with_temp_tables separates in-memory from on-disk temporary tables (memory_tmp_tables versus disk_tmp_tables). Disk temporary tables are the expensive ones.

To see which tables are actually being read and written most:

SELECT * FROM sys.schema_table_statistics
ORDER BY total_latency DESC LIMIT 20;

Inspect wait events

Statement time is only half the picture. Wait events tell you whether the server is waiting on disk, locks or the network:

SELECT
  EVENT_NAME,
  COUNT_STAR AS waits,
  ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Common event names you will see:

• wait/io/file/innodb/innodb_data_file — InnoDB reading from disk. A large total here means the working set no longer fits in the buffer pool.
• wait/io/table/sql/handler — table I/O at the handler layer.
• wait/lock/table/sql/handler — table-level lock waits, often from MyISAM tables or LOCK TABLES.
• wait/synch/mutex/innodb/* — internal contention, usually a symptom rather than the cause.

The sys schema makes this easier to read:

SELECT * FROM sys.io_global_by_wait_by_latency LIMIT 20;
SELECT * FROM sys.waits_by_host_by_latency LIMIT 20;

If lock waits dominate, check the current blockers directly:

SELECT * FROM sys.innodb_lock_waits;

That view shows the waiting transaction, the blocking transaction and the SQL each is running. For a deeper walkthrough, see troubleshooting MySQL deadlocks and transaction failures.

Reset the counters and re-measure

Aggregates accumulate since the last server start, which mixes old and new behaviour. Truncate them before a measurement window:

CALL sys.ps_truncate_all_tables(FALSE);

Passing FALSE keeps the summary tables for the current session; passing TRUE truncates everything. Either way, you lose the collected statistics.

Then let the workload run for a representative period — an hour, or one full business cycle — and re-run the digest query from the previous section.

Check it works

Generate a deliberately expensive statement and confirm it appears:

SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;
SELECT SLEEP(3);
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE 'SELECT SLEEP%';

You should see the SLEEP statement with an average around 3,000 ms. If it does not appear, the digest table is full or the statement consumer is disabled — see below.

Also confirm nothing is being dropped:

SHOW STATUS LIKE 'Performance_schema_digest_lost';

A non-zero value means the digest table is too small and you are losing statements.

Troubleshooting

• DIGEST_TEXT is NULL or truncated — the digest is longer than performance_schema_max_digest_length. Raise it in my.cnf and restart. Truncated digests still aggregate correctly, but you cannot read the full SQL.
• Performance_schema_digest_lost keeps climbing — the digest table is full. Increase performance_schema_digests_size in my.cnf and restart MySQL.
• Tables are empty — the relevant consumer is disabled. Re-check setup_consumers and confirm performance_schema = ON survived the last restart.
• Access denied on wait or thread tables — your account lacks the PROCESS privilege. Grant it with GRANT PROCESS ON *.* TO 'deploy'@'localhost'; and reconnect.
• Memory use climbed after enabling everything — Performance Schema allocates memory for its buffers. If the VPS is small, review the performance_schema_max_* variables and see tuning MySQL and MariaDB memory for a small VPS.

Next steps

• Find slow queries in MySQL with the slow query log — a complementary view that captures full statements with their literal values.
• Diagnosing slow database performance: slow query optimization with EXPLAIN — turn the digests you found here into index and query changes.
• Troubleshooting MySQL deadlocks and transaction failures — when lock waits are the top event.
• Tune MySQL and MariaDB memory settings for a small VPS — size the buffer pool so disk waits stop dominating.