-- PostgreSQL에서 현재 idle in transaction 세션 확인
SELECT
  pid,
  client_addr,
  application_name,
  state,
  now() - state_change AS idle_duration,
  LEFT(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY idle_duration DESC;

-- 5분 이상 idle in transaction 세션 강제 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > INTERVAL '5 minutes';

// 나쁜 예: 예외 발생 시 트랜잭션 미처리
public void processOrder(Long orderId) {
    Connection conn = dataSource.getConnection();
    conn.setAutoCommit(false);
    // ... 쿼리 실행 ...
    // 예외 발생 시 conn.rollback() 없이 메서드 종료
    // → 연결이 풀에 반환되지만 DB에는 idle in transaction으로 남음
    conn.commit();
}

// 좋은 예: try-finally 또는 try-with-resources로 반드시 처리
public void processOrder(Long orderId) {
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false);
        try {
            // ... 쿼리 실행 ...
            conn.commit();
        } catch (Exception e) {
            conn.rollback();  // 반드시 롤백
            throw e;
        }
    }  // 자동으로 conn.close() → 풀에 반환
}

-- 현재 30초 이상 실행 중인 쿼리 확인
SELECT
  pid,
  now() - query_start AS duration,
  state,
  wait_event_type,
  wait_event,
  LEFT(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > INTERVAL '30 seconds'
ORDER BY duration DESC;

-- HikariCP에서 연결 점유 시간 분포 확인 (Prometheus 쿼리)
-- histogram_quantile(0.99, hikaricp_connection_usage_nanos_bucket) / 1e9
-- → p99 연결 점유 시간 (초)

# HikariCP 연결 누수 감지 설정 (application.yml)
spring:
  datasource:
    hikari:
      leak-detection-threshold: 5000  # 5초 이상 반환 안 된 연결 경고
      # 로그 출력 예:
      # Connection leak detection triggered for com.zaxxer.hikari.pool.ProxyConnection
      # Stack trace of leaked connection:
      #   at com.example.service.OrderService.processOrder(OrderService.java:47)

# PgBouncer에서 풀 상태 실시간 확인
# PgBouncer 관리 DB 접속 (포트 6432, 데이터베이스명 'pgbouncer')
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

SHOW POOLS;
-- database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
-- mydb     | app  | 23        | 7          | 20        | 5       | 0       | 0         | 0        | 2
-- cl_waiting=7: 대기 중인 클라이언트 (풀 부족 신호)
-- maxwait=2: 최대 대기 시간(초)

SHOW STATS;
-- total_query_count, total_query_time, avg_query_time 등

SHOW CLIENTS;
-- 연결된 클라이언트 목록과 상태

# Spring Boot application.yml — 프로덕션 권장 설정
spring:
  datasource:
    url: jdbc:postgresql://pgbouncer-host:6432/mydb?ApplicationName=order-service
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      # 풀 크기: (4코어 × 2) + 1 SSD = 9. 여유분 포함해 10
      maximum-pool-size: 10
      minimum-idle: 3          # 최소 유지 유휴 연결

      # 타임아웃 설정 (ms 단위)
      connection-timeout: 3000  # 3초: 풀에서 연결 못 받으면 즉시 실패 (기본 30초는 너무 김)
      idle-timeout: 600000      # 10분: 유휴 연결 제거 (PgBouncer server_idle_timeout보다 짧게)
      max-lifetime: 1800000     # 30분: 연결 최대 수명 (DB의 wait_timeout - 30초)
      keepalive-time: 60000     # 1분: 유휴 연결 유효성 확인 (방화벽 끊김 방지)

      # 연결 유효성 검사
      connection-test-query: SELECT 1
      validation-timeout: 2000  # 유효성 검사 타임아웃

      # 누수 감지 (개발/스테이징)
      leak-detection-threshold: 10000 # 10초 이상 반환 안 된 연결 경고

      pool-name: OrderService-HikariPool

      # Prometheus 메트릭 노출
      register-mbeans: true

// connection-timeout = 3000 (3초) 설정 시 동작
// 풀에 연결이 없으면 3초 후 즉시 SQLException
// → 빠른 실패(fail-fast)로 상위 서비스에 즉시 에러 전파
// → Circuit Breaker가 빠르게 발동해 연쇄 장애 방지

// 응용: Circuit Breaker와 함께 사용
@CircuitBreaker(name = "database", fallbackMethod = "fallbackGetOrder")
public Order getOrder(Long orderId) {
    return orderRepository.findById(orderId).orElseThrow();
}

public Order fallbackGetOrder(Long orderId, Exception ex) {
    log.error("DB 연결 실패, 캐시에서 조회: {}", orderId);
    return orderCache.get(orderId);
}

-- MySQL: wait_timeout 확인
SHOW VARIABLES LIKE 'wait_timeout';  -- 기본 28800초 (8시간)

-- PostgreSQL: 연결 유지 설정 확인
SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;

# 규칙: max-lifetime < DB wait_timeout - 30초
# MySQL wait_timeout = 28800초(8시간)이면
# max-lifetime = 28770000ms (8시간 - 30초)

# 실무 권장: 30분으로 설정 (대부분의 DB 설정보다 짧음)
max-lifetime: 1800000  # 30분

앱 인스턴스 1 [HikariCP: 10개]
앱 인스턴스 2 [HikariCP: 10개]   →  PgBouncer [실제 DB 연결: 25개]  →  PostgreSQL
앱 인스턴스 3 [HikariCP: 10개]
              ...
앱 인스턴스 N [HikariCP: 10개]

클라이언트 연결: N × 10개  →  DB 실제 연결: 25개 (고정)

# PgBouncer 부하 테스트 비교
# PgBouncer 없이 (PostgreSQL 직접 연결)
pgbench -U postgres -h postgres-host -p 5432 \
  -c 200 -j 8 -T 60 mydb
# 결과: TPS=823, 연결 에러 다수 (max_connections 초과)

# PgBouncer를 통한 연결
pgbench -U postgres -h pgbouncer-host -p 6432 \
  -c 200 -j 8 -T 60 mydb
# 결과: TPS=3847, 에러 0 (PgBouncer가 200개를 25개로 관리)

# docker-compose.yml — PgBouncer exporter 포함
version: '3.8'
services:
  pgbouncer-exporter:
    image: prometheuscommunity/pgbouncer-exporter:latest
    environment:
      PGBOUNCER_EXPORTER_HOST: pgbouncer
      PGBOUNCER_EXPORTER_PORT: 6432
      PGBOUNCER_EXPORTER_USER: pgbouncer
      PGBOUNCER_EXPORTER_PASSWORD: ${PGBOUNCER_ADMIN_PASS}
    ports:
      - "9127:9127"

# Prometheus 알림 규칙 (alert.rules.yml)
groups:
  - name: connection_pool
    rules:
      - alert: HikariPoolPendingHigh
        expr: hikaricp_connections_pending > 3
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "HikariCP 풀 대기 세션 증가"
          description: "{{ $labels.pool }} 풀에서 {{ $value }}개 요청이 연결 대기 중"

      - alert: PgBouncerClientWaiting
        expr: pgbouncer_stats_clients_waiting > 10
        for: 30s
        labels:
          severity: critical
        annotations:
          summary: "PgBouncer 클라이언트 대기 급증"

      - alert: IdleInTransactionSpike
        expr: |
          pg_stat_activity_count{state="idle in transaction"} > 20
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "idle in transaction 세션 급증 — 연결 누수 가능성"

-- 핵심 모니터링 쿼리 (정기 실행 권장)
-- 1. 연결 상태 분포
SELECT state, count(*) AS cnt
FROM pg_stat_activity
WHERE datname = 'mydb'
GROUP BY state
ORDER BY cnt DESC;

-- 2. 애플리케이션별 연결 수
SELECT application_name, count(*) AS connections
FROM pg_stat_activity
WHERE datname = 'mydb'
GROUP BY application_name
ORDER BY connections DESC;

-- 3. 가장 오래 실행 중인 쿼리 TOP 5
SELECT
  pid,
  ROUND(EXTRACT(EPOCH FROM (now() - query_start))::NUMERIC, 1) AS seconds,
  state,
  LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY query_start ASC
LIMIT 5;