MySQL Performance Schema: Monitoring Server Bottlenecks

MySQL Performance Schema

Performance Schema is a built-in MySQL diagnostic tool that operates at the engine level. Unlike SHOW STATUS, it lets you track specific queries, threads, locks, and resource consumption in real time.

Enabling Performance Schema

# Check status
mysql -e "SELECT * FROM performance_schema.setup_instruments LIMIT 5;"

# In my.cnf (enabled by default in MySQL 5.6+)
[mysqld]
performance_schema=ON

Top Slow Queries

-- Top 10 queries by total execution time
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec,
  ROUND(AVG_TIMER_WAIT/1e12, 6) AS avg_sec,
  SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Lock Analysis

-- Current InnoDB locks
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Memory Monitoring

-- Memory usage by component
SELECT
  EVENT_NAME,
  CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_mb,
  HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024 AS peak_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

Table I/O Statistics

-- Tables with the highest read/write load
SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  COUNT_FETCH,
  SUM_TIMER_WAIT / 1e12 AS total_wait_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Sys Schema — a Convenient Wrapper

-- The sys schema provides human-readable views
-- Top queries by latency
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- Tables without indexes
SELECT * FROM sys.schema_tables_with_full_table_scans;

-- Unused indexes
SELECT * FROM sys.schema_unused_indexes;

Common Errors When Working with Performance Schema

ERROR 1146 (42S02): Table 'performance_schema.events_statements_history_long' doesn't exist

Empty set (0.00 sec) -- when querying events_statements_summary_by_digest

ERROR 1227 (42000): Access denied; you need the PROCESS privilege

events_statements_history: Truncated due to table size limit

Performance Schema vs. SHOW STATUS and the Slow Query Log

When Performance Schema Doesn't Fit

• The server runs on a minimal plan with a tight memory limit — the in-memory Performance Schema tables themselves take up noticeable space when every instrument is enabled at once
• You need a representative load profile over a long period (weeks) — in-memory data is evicted by a ring buffer, so long-term history needs an external export to monitoring
• You need to profile one specific query in isolation — plain EXPLAIN ANALYZE is simpler and clearer here than aggregated digest statistics
• The database isn't MySQL/MariaDB but, say, PostgreSQL — it has its own pg_stat_statements system, built differently

Verifying the Data Is Actually Being Collected

-- Confirm the instruments and consumers are enabled
SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE 'statement/%' AND ENABLED='NO' LIMIT 5;
SELECT * FROM performance_schema.setup_consumers WHERE ENABLED='NO';

-- The count of accumulated events grows under real load
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;

1. Run a test query against the database and confirm it shows up in events_statements_summary_by_digest — an empty table means the instruments are disabled
2. Check that setup_instruments and setup_consumers don't have the rows you need set to ENABLED='NO'
3. Compare CPU load before and after enabling the full set of instruments — a drop of more than a few percent means you should enable only the needed instruments, not everything at once
4. Make sure the collection plan survives a server restart — if instruments were enabled manually via UPDATE rather than in my.cnf, they'll revert to the defaults after a restart

A more detailed look at slow queries through the classic mechanism is in the article Slow Query Log, and reading a specific query's execution plan is covered in reading an EXPLAIN query plan. The indexes that often turn out to be the cause of problems found via Performance Schema are covered in MySQL indexes: types and optimization.

Frequently asked questions

Why does the query statistics table come back empty even though Performance Schema is enabled in the configuration?

An empty result almost always means Performance Schema is enabled overall, but the specific instruments for statement statistics are switched off by default. You need to separately enable the needed instruments and consumers by updating the setup tables, otherwise events simply are not recorded, even though the diagnostic mechanism itself is formally active on the server.

How much does enabling Performance Schema affect the server's own performance?

The overhead depends on what exactly is enabled: with only the needed instruments running, the effect on performance is small and barely noticeable against ordinary load. Turning on absolutely every instrument at once makes the overhead noticeable, so it makes more sense to enable things selectively for a specific diagnostic task instead of leaving everything active all the time.

What privileges does an account need to read Performance Schema data?

The account needs the privilege to read the performance system views, or the server immediately refuses the query with an error about a missing privilege. That privilege is granted with a separate command at the database user level and limited to the account used for monitoring, rather than handed out broadly, so access to internal server statistics stays under control.

Is it worth enabling every instrument and consumer at once, or only the needed ones?

No, it makes more sense to enable only the instruments and consumers actually needed for the current diagnostic task. Turning on the whole set at once noticeably raises overhead and the amount of in-memory data, while selective enabling gives the picture you need with almost no performance cost and does not bloat the history tables beyond reason.