Amazon Aurora — Performance Schema / pg_stat_* ビューによる取得とセクション定義

-- Performance Schema の有効状態を確認
SHOW VARIABLES LIKE 'performance_schema';

-- 主要インスツルメントの有効化（セッション中のみ有効）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%'
   OR NAME LIKE 'wait/%';

-- コンシューマの有効化
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN (
  'events_statements_history_long',
  'events_waits_history_long',
  'events_waits_current'
);

-- 累積実行時間が長い上位20 SQL
SELECT
  DIGEST_TEXT                                           AS sql_text,
  COUNT_STAR                                            AS executions,
  ROUND(SUM_TIMER_WAIT / 1e12, 3)                       AS total_sec,
  ROUND(SUM_TIMER_WAIT / COUNT_STAR / 1e12, 6)          AS avg_sec,
  SUM_ROWS_EXAMINED                                     AS rows_examined,
  SUM_NO_INDEX_USED                                     AS no_index_cnt
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 待機時間が長い上位イベント（AWR の Top 5 Timed Events 相当）
SELECT
  EVENT_NAME                            AS wait_event,
  COUNT_STAR                            AS wait_count,
  ROUND(SUM_TIMER_WAIT / 1e12, 3)       AS total_wait_sec,
  ROUND(AVG_TIMER_WAIT / 1e9, 3)        AS avg_wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE SUM_TIMER_WAIT > 0
  AND EVENT_NAME NOT LIKE 'idle%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 現在ロック待ちが発生しているトランザクションを確認
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,
  TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_secs
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
ORDER BY wait_secs DESC;

# カスタムパラメータグループに pg_stat_statements を追加
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-aurora-pg-params \
  --parameters \
    "ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot"

# DB 再起動後に拡張を有効化
# (DB に接続して実行)
# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 拡張の確認
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- 統計リセット（計測開始時）
SELECT pg_stat_reset();
SELECT pg_stat_statements_reset();

-- 累積実行時間が長い上位20 SQL
SELECT
  LEFT(query, 100)                                  AS sql_text,
  calls,
  ROUND(total_exec_time::numeric, 3)                AS total_ms,
  ROUND(mean_exec_time::numeric, 3)                 AS avg_ms,
  shared_blks_hit,
  shared_blks_read,
  ROUND(100.0 * shared_blks_hit
        / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 現在アクティブなセッションの待機イベント（Top 5 Timed Events 相当）
SELECT
  wait_event_type,
  wait_event,
  COUNT(*) AS session_count,
  STRING_AGG(LEFT(query, 60), ' | ') AS queries
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY session_count DESC;

-- ロック待ちセッションの一覧
SELECT
  blocked.pid                         AS blocked_pid,
  blocked.query                       AS blocked_query,
  blocking.pid                        AS blocking_pid,
  blocking.query                      AS blocking_query,
  AGE(NOW(), blocked.query_start)     AS wait_duration
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
ORDER BY wait_duration DESC;

-- DB 全体のキャッシュヒット率・デッドロック・ロールバック数
SELECT
  datname                                         AS database_name,
  numbackends                                     AS connections,
  xact_commit                                     AS commits,
  xact_rollback                                   AS rollbacks,
  blks_hit,
  blks_read,
  ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
  deadlocks,
  conflicts
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1', 'postgres')
ORDER BY xact_commit DESC;