Performance Schema

-- Top 10 slowest queries by average latency (sys schema)
SELECT
    query,
    exec_count,
    avg_latency,
    max_latency,
    rows_examined_avg / NULLIF(rows_sent_avg, 0) AS rows_per_sent,
    full_scan,
    tmp_tables,
    tmp_disk_tables
FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;

-- Raw Performance Schema query (more detail)
SELECT
    DIGEST_TEXT,
    COUNT_STAR                            AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1e9, 2)       AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1e9, 2)       AS max_ms,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT,
    SUM_NO_INDEX_USED                     AS full_scan_count
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Find current lock waits (blocking and blocked sessions)
SELECT
    r.trx_id                AS waiting_trx,
    r.trx_mysql_thread_id   AS waiting_thread,
    r.trx_query             AS waiting_query,
    b.trx_id                AS blocking_trx,
    b.trx_mysql_thread_id   AS blocking_thread,
    b.trx_query             AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_trx r
JOIN information_schema.innodb_trx b
  ON r.trx_wait_started IS NOT NULL
  AND b.trx_id IN (
      SELECT blocking_trx_id
      FROM performance_schema.data_lock_waits
  );

-- Simpler sys schema view (MySQL 5.7.9+)
SELECT * FROM sys.innodb_lock_waits;

-- Kill a blocking session
KILL 42;   -- thread_id from blocking_thread above

-- Tables with most lock wait time
SELECT object_schema, object_name, count_star, sum_timer_wait/1e12 AS wait_s
FROM performance_schema.table_lock_waits_summary_by_table
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Tables with most I/O (identify hotspots)
SELECT
    object_schema,
    object_name,
    count_read,
    count_write,
    count_fetch,
    ROUND(sum_timer_read / 1e12, 3)  AS read_s,
    ROUND(sum_timer_write / 1e12, 3) AS write_s
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema')
ORDER BY sum_timer_read + sum_timer_write DESC
LIMIT 10;

-- File-level I/O latency
SELECT
    file_name,
    count_read, count_write,
    ROUND(sum_timer_read / 1e12, 3)  AS read_s,
    ROUND(sum_timer_write / 1e12, 3) AS write_s
FROM performance_schema.file_summary_by_instance
ORDER BY sum_timer_read + sum_timer_write DESC
LIMIT 10;

-- Memory usage per component
SELECT
    event_name,
    current_alloc,
    high_alloc
FROM sys.memory_global_by_current_bytes
LIMIT 20;