MySQL Performance Schema: Monitoring Server Bottlenecks

MySQL Performance Schema

Performance Schema is MySQL's built-in diagnostics engine. It tracks specific queries, threads, locks, and resource consumption in real time.

Top Slow Queries

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1e12, 3) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Sys Schema

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

-- Unused indexes
SELECT * FROM sys.schema_unused_indexes;