MySQL Performance Schema: Top Queries, Blocking Transactions, and Index Usage

SELECT
  ROUND(sum_timer_wait / 1e12, 2) AS total_sec,
  count_star AS calls,
  ROUND(avg_timer_wait / 1e9, 2) AS avg_ms,
  LEFT(digest_text, 100) AS query
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

SELECT
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

SELECT object_schema, object_name,
       count_read, sum_timer_read / 1e12 AS read_sec,
       count_write, sum_timer_write / 1e12 AS write_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql','performance_schema','sys','information_schema')
ORDER BY (sum_timer_read + sum_timer_write) DESC
LIMIT 10;

SELECT object_schema, object_name, index_name,
       count_fetch, count_insert, count_update, count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'myapp'
  AND index_name IS NOT NULL
ORDER BY count_fetch DESC
LIMIT 20;

-- Indexes with zero fetch count are candidates for removal

SELECT event_name,
       sum_number_of_bytes_alloc / 1024 / 1024 AS alloc_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE sum_number_of_bytes_alloc > 1048576
ORDER BY sum_number_of_bytes_alloc DESC
LIMIT 15;

-- Enable statement history for current session
UPDATE performance_schema.setup_consumers
SET enabled = 'YES'
WHERE name = 'events_statements_history_long';

-- Disable high-overhead instruments if needed
UPDATE performance_schema.setup_instruments
SET enabled = 'NO', timed = 'NO'
WHERE name LIKE 'wait/synch/mutex/%';