Performance Schema による取得とセクション定義

-- Performance Schema が有効か確認
SHOW VARIABLES LIKE 'performance_schema';
-- Value = ON であれば有効

-- 計装（instrument）の有効化状況確認
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/%'
  AND ENABLED = 'NO'
LIMIT 20;

-- 計装を一括有効化（通常は不要）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%events_waits%'
   OR NAME LIKE '%statements%'
   OR NAME LIKE '%stages%';

-- 合計待機時間の多い上位20イベント
SELECT
    EVENT_NAME,
    COUNT_STAR                                AS count_total,
    SUM_TIMER_WAIT / 1e12                     AS wait_sec_total,
    AVG_TIMER_WAIT / 1e9                      AS avg_wait_ms,
    MAX_TIMER_WAIT / 1e9                      AS max_wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 待機クラス（カテゴリ）別集計
SELECT
    SUBSTRING_INDEX(EVENT_NAME, '/', 3) AS wait_class,
    SUM(COUNT_STAR)                     AS total_count,
    SUM(SUM_TIMER_WAIT) / 1e12          AS total_wait_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
GROUP BY wait_class
ORDER BY total_wait_sec DESC;

-- 合計実行時間の多い SQL 上位20
SELECT
    SCHEMA_NAME,
    DIGEST_TEXT,
    COUNT_STAR                         AS exec_count,
    SUM_TIMER_WAIT / 1e12              AS total_exec_sec,
    AVG_TIMER_WAIT / 1e9               AS avg_exec_ms,
    MAX_TIMER_WAIT / 1e9               AS max_exec_ms,
    SUM_ROWS_EXAMINED                  AS rows_examined_total,
    SUM_ROWS_SENT                      AS rows_sent_total,
    SUM_CREATED_TMP_TABLES             AS tmp_tables_total,
    SUM_NO_INDEX_USED                  AS no_index_total,
    FIRST_SEEN,
    LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- CPU時間 = 実行時間 - ロック待ち時間 で近似
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    (SUM_TIMER_WAIT - SUM_LOCK_TIME) / 1e12 AS cpu_sec_approx,
    SUM_TIMER_WAIT / 1e12                    AS total_sec,
    SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY cpu_sec_approx DESC
LIMIT 20;

-- ファイル別 I/O 統計
SELECT
    FILE_NAME,
    COUNT_READ,
    SUM_NUMBER_OF_BYTES_READ  / 1024 / 1024 AS read_mb,
    SUM_TIMER_READ / 1e12                   AS read_sec,
    COUNT_WRITE,
    SUM_NUMBER_OF_BYTES_WRITE / 1024 / 1024 AS write_mb,
    SUM_TIMER_WRITE / 1e12                  AS write_sec
FROM performance_schema.file_summary_by_instance
ORDER BY (SUM_TIMER_READ + SUM_TIMER_WRITE) DESC
LIMIT 20;

-- メモリ使用量の多いコンポーネント上位20
SELECT
    EVENT_NAME,
    CURRENT_COUNT_USED,
    CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_mb,
    HIGH_NUMBER_OF_BYTES_USED   / 1024 / 1024 AS peak_mb,
    SUM_NUMBER_OF_BYTES_ALLOC   / 1024 / 1024 AS total_alloc_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 20;

-- ミューテックス待機上位（AWR Latch Activity 相当）
SELECT
    EVENT_NAME,
    COUNT_STAR          AS wait_count,
    SUM_TIMER_WAIT / 1e12 AS total_wait_sec,
    AVG_TIMER_WAIT / 1e9  AS avg_wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/synch/mutex/%'
  AND COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 現在発生中のロック待機（MySQL 8.0+）
SELECT
    r.trx_id                AS waiting_trx_id,
    r.trx_mysql_thread_id   AS waiting_thread,
    r.trx_query             AS waiting_query,
    b.trx_id                AS blocking_trx_id,
    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        r ON r.trx_id = w.requesting_trx_id
JOIN information_schema.innodb_trx        b ON b.trx_id = w.blocking_trx_id;

-- Performance Schema でのロック待機履歴
SELECT
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1e12 AS total_wait_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock/%'
  AND COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC;

-- コミット・ロールバック統計
SELECT
    EVENT_NAME,
    COUNT_STAR        AS total_trx,
    SUM_TIMER_WAIT / 1e12 AS total_sec,
    AVG_TIMER_WAIT / 1e9  AS avg_ms
FROM performance_schema.events_transactions_summary_global_by_event_name
WHERE COUNT_STAR > 0;

-- 特定テーブルの統計をリセット
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.file_summary_by_instance;

-- 全統計を一括リセット（本番環境では注意）
CALL sys.ps_truncate_all_tables(FALSE);