-- MySQL에서 슬로우 쿼리 로그 활성화 (재시작 없이 적용)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;              -- 1초 이상인 쿼리 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 인덱스 미사용 쿼리도 포함

-- 설정 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

# /etc/mysql/my.cnf 또는 /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 1
log_queries_not_using_indexes = 1
min_examined_row_limit  = 100   # 소량 행 쿼리는 제외 (노이즈 감소)

# 평균 실행 시간 기준 상위 10개 출력
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 총 시간 기준 (DB 자원을 가장 많이 소비한 쿼리)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 특정 테이블이 포함된 쿼리만 필터
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log | grep -A5 "orders"

# Grafana PromQL 입력란에 붙여넣기
rate(pg_stat_database_xact_commit{datname="appdb"}[1m])
+ rate(pg_stat_database_xact_rollback{datname="appdb"}[1m])

# 활성 커넥션
pg_stat_activity_count{datname="appdb", state="active"}

# 위험 임계선 (max_connections의 70%)
0.7 * pg_settings_max_connections

100 * pg_stat_database_blks_hit{datname="appdb"}
    / (pg_stat_database_blks_hit{datname="appdb"}
       + pg_stat_database_blks_read{datname="appdb"} + 1)

SELECT
  LEFT(query, 100)                           AS "쿼리",
  calls                                       AS "호출 수",
  ROUND(total_exec_time / calls)             AS "평균(ms)",
  ROUND(total_exec_time)                     AS "총 시간(ms)"
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time / calls DESC
LIMIT 5;

-- 현재 잠금 대기 중인 세션과 원인 세션을 함께 표시
SELECT
  blocked.pid                          AS blocked_pid,
  blocked.query                        AS blocked_query,
  blocking.pid                         AS blocking_pid,
  blocking.query                       AS blocking_query,
  blocked.wait_event_type,
  blocked.wait_event,
  EXTRACT(EPOCH FROM NOW() - blocked.query_start)::int AS wait_seconds
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY wait_seconds DESC;

-- 잠금 타입별 현황 요약
SELECT
  locktype,
  relation::regclass AS table_name,
  mode,
  granted,
  COUNT(*) AS count
FROM pg_locks
WHERE relation IS NOT NULL
GROUP BY locktype, relation, mode, granted
ORDER BY count DESC;

-- 현재 실행 중인 트랜잭션 목록
SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
  trx_rows_locked,
  trx_rows_modified,
  LEFT(trx_query, 120) AS current_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

-- 잠금 대기 그래프 (MySQL 8.0 — performance_schema 활용)
SELECT
  r.trx_id                             AS waiting_trx_id,
  r.trx_query                          AS waiting_query,
  b.trx_id                             AS blocking_trx_id,
  b.trx_query                          AS blocking_query,
  b.trx_mysql_thread_id                AS blocking_thread
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;