DB 모니터링 이상 탐지와 진단 리포트 로직 구성

핵심 요약

사내 DB 모니터링 시스템을 만들면서, 매일 아침 사람이 눈으로 훑던 DB 점검을 자동화했습니다. 핵심은 두 가지 이상 탐지를 한 화면에 녹여 넣는 일이었습니다.

• 룰베이스 3종 — 절대 임계 초과(그날 최고값이 경고/위험 선을 넘는가), 전일 대비 급증(일평균이 2배 이상 뛰었는가), 패턴 변화(% 지표가 전일 대비 20%p 이상 올랐는가)를 분 단위로 검사합니다. DB마다 임계값이 달라 설정 파일로 분리했습니다.
• 비지도 학습(PCA 복원오차) — 한 달치 정상 패턴을 주성분으로 압축했다가 복원해, 평소와 다른 조합이 들어오면 복원이 잘 안 되는 성질(복원오차 증가)로 "임계는 안 넘었지만 평소와 다른 날"을 잡습니다. 단일 지표가 아니라 여러 지표가 동시에 평소 조합에서 벗어나는 조합 이상을 잡는 게 목적입니다.
• 진단 리포트 — 감지된 이상을 카테고리로 분류하고 동시 발생 패턴을 상관 규칙에 매칭해 ① 감지된 이상 ② 원인 추정 ③ 권장 조치 ④ 종합으로 카드를 만듭니다. 룰베이스 결과는 기본색, ML(비지도) 결과는 보라색으로 같은 카드 안에 통합합니다.

이 글은 그 탐지 로직과 리포트 구성이 실제로 어떻게 짜였는지를 코드와 함께 자세히 정리한 기록입니다. 중간에 부딪힌 문제(대표 지표 선정, 카테고리 동점 비결정 버그, 그래프 가독성 등)와 그걸 어떻게 풀었는지도 함께 적었습니다.

---

배경 — 단일 임계값의 한계

처음 떠올리는 자동화는 단순합니다. 지표마다 "이 값을 넘으면 경고"라는 선을 긋고, 넘으면 알린다. CPU 사용률 80% 넘으면 경고, 90% 넘으면 위험. 이런 절대 임계는 직관적이고 설명하기 쉽습니다.

그런데 실제 데이터를 들여다보면 임계만으로는 못 잡는 상황이 많습니다. 두 가지 한계가 있었습니다.

첫째, 임계는 안 넘지만 평소와 분명히 다른 날. 예를 들어 CPU 사용률 평소 15%대인 DB가 어느 날 45%까지 올라도 80% 선에는 한참 못 미칩니다. 임계 기준으로는 "정상"입니다. 하지만 평소의 3배입니다. 무언가 일어난 겁니다. 그래서 절대 임계만이 아니라 전일 대비 급증도 봐야 합니다.

둘째, 단일 지표는 멀쩡한데 여러 지표의 조합이 이상한 날. 이게 더 까다롭습니다. CPU도 임계 아래, 세션 수도 임계 아래, 디스크 읽기도 임계 아래인데, 이 셋이 같은 시각에 동시에 평소보다 들떠 있다면? 각각은 정상 범위라 어떤 단일 임계 규칙도 발동하지 않습니다. 하지만 "CPU·세션·디스크가 한꺼번에 평소와 다른 조합"은 분명 비정상입니다.

일상에 비유하면 이렇습니다. 체온 37도, 맥박 90, 혈압 130. 하나씩 보면 다 정상 범위입니다. 그런데 평소 이 사람은 체온 36.3도, 맥박 65, 혈압 110이었습니다. 세 수치가 동시에 평소보다 올라 있다면, 각 항목이 "정상 범위"여도 몸 상태가 평소와 다르다는 신호입니다. 항목별 기준선(룰베이스)만 보면 놓치고, "이 사람의 평소 조합"을 학습해 둬야 잡힙니다.

그래서 탐지를 두 갈래로 짰습니다. 명시적인 규칙으로 잡는 룰베이스, 그리고 "평소 조합"을 학습해 벗어남을 잡는 비지도 학습. 둘은 상호보완입니다. 룰베이스는 설명이 명확하지만 미리 정의한 것만 잡고, 비지도는 미리 규칙을 못 적은 조합 이상을 잡지만 "정확히 무엇이 왜 이상인지"는 따로 설명해 줘야 합니다. 그 설명을 만드는 게 뒤에 나오는 영향 지표 산출과 진단 카드입니다.

---

전체 그림 — 파이프라인 구조

그림1은 매일 새벽 cron이 도는 파이프라인입니다. 네 모듈이 한 줄로 연결됩니다. collector가 데이터를 표준 형태로 만들고, detector가 이상 항목 리스트를 뽑고, reporter가 그걸 HTML과 그래프로 바꾸고, mailer가 메일로 보냅니다. 각 모듈은 앞 모듈의 출력에만 의존하도록 경계를 분명히 했습니다.

모듈 책임을 정리하면 이렇습니다.

• collector — 원천 데이터에서 "오늘 1분 시계열"과 "전일 1분 시계열"을 잘라내 DbMetrics라는 표준 객체로 만듭니다. POC 단계에서는 엑셀 목데이터를 읽고, 현장에서는 API로 바꿉니다. 데이터 출처가 바뀌어도 이 파일 한 곳만 고치면 되도록 격리했습니다. 뒷 모듈은 collector가 무엇을 읽었는지 모릅니다. DbMetrics만 봅니다.
• detector — DbMetrics를 받아 DB별로 이상 항목(Anomaly) 리스트를 만듭니다. 룰베이스 3종과 ML(PCA) 한 종이 여기 있습니다.
• reporter — detector의 결과(DbDetection)와 collector 데이터를 받아 HTML 리포트와 추이 그래프(PNG)를 만듭니다. 진단 카드도 여기서 조립합니다.
• mailer — HTML을 본문에, 그래프 PNG를 CID로 임베드해 메일을 보냅니다.

핵심 설계 원칙은 "reporter는 Anomaly 리스트에만 의존한다" 입니다. detector가 룰베이스로 잡았든 PCA로 잡았든, 결과는 똑같이 Anomaly 객체 한 종류로 흘러갑니다. reporter는 그 Anomaly의 kind 필드(threshold/surge/pattern/ml)만 보고 표시를 다르게 합니다. 덕분에 탐지 방식을 늘려도 reporter 구조는 안 흔들립니다.

이 Anomaly가 모듈 사이의 계약입니다. 실제 정의는 이렇습니다.

@dataclass
class Anomaly:
    """이상 항목 1건."""
    db_name: str
    instance: str
    metric: str                       # 지표 source명 (config 와 동일 문자열)
    kind: str                         # 'threshold' | 'surge' | 'pattern'
    grade: str                        # 'critical' | 'warning'
    value: float                      # 대표값(threshold=peak, surge/pattern=오늘 일평균)
    threshold: Optional[float] = None # 비교 임계값(threshold 일 때)
    baseline: Optional[float] = None  # 전일 비교값(surge/pattern 일 때)
    at_time: Optional[pd.Timestamp] = None   # 발생 시각(threshold peak 시각)
    detail: dict = field(default_factory=dict)  # 부가 정보(지속분/비율/증가폭 등)

kind마다 의미 있는 필드가 조금씩 다릅니다. 임계 초과면 threshold와 at_time(피크 시각)이 차고, 급증이면 baseline(전일값)과 detail["ratio"](배수)가 찹니다. 공통이 아닌 부가 정보는 전부 detail 딕셔너리에 담아, 종류가 늘어도 클래스 구조를 바꾸지 않게 했습니다.

자바로 비유하면 Anomaly는 모듈 간에 오가는 DTO이고, kind는 일종의 타입 구분자입니다. 상속으로 4개 서브클래스를 만들 수도 있었지만, POC 규모에서는 kind 문자열 + detail 딕셔너리가 더 가볍고 직렬화도 편했습니다.

---

이상 탐지 1 — 룰베이스 3종

그림2는 한 지표가 룰베이스 3종을 어떻게 통과하는지 보여줍니다. 절대 임계는 thresholds에 정의된 지표만, 급증과 패턴은 모니터링 대상 전체 지표를 훑습니다. 패턴은 % 단위 지표에만 적용합니다. 각 검사가 독립적이라, 한 지표가 동시에 여러 종류로 잡힐 수도 있습니다.

진입점은 detect_db입니다. 한 DB에 대해 룰베이스 3종을 차례로 돌리고 ML을 더합니다.

def detect_db(db_metrics: DbMetrics, config: dict) -> DbDetection:
    """한 DB의 이상 탐지(룰베이스 + ML)."""
    db_name = db_metrics.db_name
    db_config = config["databases"][db_name]
    anomaly_cfg = config.get("global", {}).get("anomaly", {})
    surge_ratio = float(anomaly_cfg.get("surge_ratio", 2.0))
    rise_pp = float(anomaly_cfg.get("pattern_rise_pp", 20))

    today, prev_day = db_metrics.today, db_metrics.prev_day
    has_data = today is not None and not today.empty
    anomalies: List[Anomaly] = []

    if has_data:
        # 1) 절대 임계 (thresholds 정의 지표만)
        for key, spec in db_config.get("thresholds", {}).items():
            a = _check_threshold(db_name, db_metrics.instance, key, spec, today)
            if a:
                anomalies.append(a)

        # 2)·3) 급증/패턴 (threshold + monitored 전체 — 임계 미초과 패턴도 잡기 위함)
        for source in _wanted_metrics(db_config):
            s = _check_surge(db_name, db_metrics.instance, source,
                             today, prev_day, surge_ratio)
            if s:
                anomalies.append(s)
            p = _check_pattern(db_name, db_metrics.instance, source,
                               today, prev_day, rise_pp)
            if p:
                anomalies.append(p)

        # 4) ML
        anomalies.extend(detect_ml(db_metrics, config))
    ...

여기서 눈여겨볼 점이 둘입니다.

첫째, 임계값과 급증/패턴 비율을 코드가 아니라 설정에서 읽습니다. surge_ratio는 기본 2.0, pattern_rise_pp는 기본 20입니다. 절대 임계는 db_config["thresholds"]에 DB별로 따로 들어 있습니다. 왜 굳이 분리했냐면, DB마다 정상 범위가 완전히 다르기 때문입니다. 같은 "활성 세션 수"라도 한 DB는 평소 1, 다른 DB는 평소 800대입니다. 임계를 코드에 박으면 DB가 늘 때마다 코드를 고쳐야 합니다. 설정으로 빼두면 DBA 피드백을 받아 값만 바꾸면 됩니다. 운영하면서 임계는 계속 조정될 거라, 처음부터 데이터(설정)와 로직(코드)을 갈라놨습니다.

둘째, 급증·패턴은 임계 지표만이 아니라 모니터링 대상 전체를 훑습니다. _wanted_metrics가 임계 정의 지표와 추가 모니터링 지표를 합쳐 중복 제거한 목록을 줍니다.

def _wanted_metrics(db_config: dict) -> List[str]:
    """이 DB에서 surge/pattern 으로 볼 전체 지표(threshold source + monitored, 중복 제거)."""
    threshold_sources = [s["source"] for s in db_config.get("thresholds", {}).values()]
    monitored = list(db_config.get("monitored_metrics", []))
    seen: Dict[str, None] = {}
    for name in threshold_sources + monitored:
        seen.setdefault(name, None)
    return list(seen.keys())

임계가 없는 지표라도 "전일 대비 2배"는 봐야 하기 때문입니다. 임계를 정해 둔 핵심 지표 몇 개만 급증 검사하면, 임계를 안 정한 지표가 폭증해도 못 잡습니다. 그래서 급증/패턴은 가능한 한 넓게 봅니다.

임계 초과 판정

_check_threshold는 분 단위로 임계를 검사하되, 결과는 "그날 최고 등급 + 피크값 + 발생 시각 + 지속 분"으로 요약합니다.

def _check_threshold(db_name, instance, key, spec, today) -> Optional[Anomaly]:
    """절대 임계 초과 판정(분 단위 검사 → 최고 등급 요약)."""
    if key in DISABLED_THRESHOLDS.get(db_name, set()):
        return None
    source = spec["source"]
    s = _series(today, source)
    if s is None:
        return None

    warning = spec.get("warning")
    critical = spec.get("critical")
    direction = spec.get("direction", "above")

    if direction == "above":
        over_crit = s[s >= critical] if critical is not None else s.iloc[0:0]
        over_warn = s[s >= warning] if warning is not None else s.iloc[0:0]
        peak, at_time = s.max(), s.idxmax()
    else:  # below — 값이 낮을수록 위험
        over_crit = s[s <= critical] if critical is not None else s.iloc[0:0]
        over_warn = s[s <= warning] if warning is not None else s.iloc[0:0]
        peak, at_time = s.min(), s.idxmin()

    if len(over_crit) > 0:
        grade, thr, dur = "critical", critical, int(len(over_crit))
    elif len(over_warn) > 0:
        grade, thr, dur = "warning", warning, int(len(over_warn))
    else:
        return None

    return Anomaly(
        db_name=db_name, instance=instance, metric=source,
        kind="threshold", grade=grade, value=float(peak), threshold=float(thr),
        at_time=at_time, detail={"duration_min": dur, "direction": direction},
    )

줄별로 보면 이렇습니다.

• direction이 above면 값이 높을수록 위험(CPU, 세션 등), below면 낮을수록 위험(여유 공간 등)입니다. 방향에 따라 비교 부호와 피크 계산(max/min)을 뒤집습니다.
• s[s >= critical]은 위험 선을 넘은 분들만 골라낸 시리즈입니다. 그 길이가 "몇 분간 초과했는가"(duration_min)가 됩니다. 5초 틱을 1분으로 집계한 데이터라 한 행이 1분입니다.
• 위험에 한 번이라도 닿으면 위험 등급, 아니면 경고에 닿았는지 봅니다. 둘 다 아니면 None(이상 아님).
• 대표값(value)은 그날 피크값으로, 발생 시각(at_time)은 피크가 찍힌 분으로 잡습니다. 하루치를 한 줄로 요약하면서도 "언제 가장 심했는지"를 남깁니다.

여기서 현실 데이터를 다루며 넣은 가드가 두 개 있습니다.

DISABLED_THRESHOLDS: Dict[str, set] = {
    "oracle": {"storage_ratio"},   # Free Storage Space=여유byte, 임계=사용률% → 계산 불가
}

Oracle의 여유 저장 공간 지표는 절대 임계 평가를 껐습니다. 받은 데이터는 "여유 바이트(약 690GB)"인데, 임계표는 "사용률 80/85%"로 적혀 있습니다. 단위가 다르고(바이트 vs %), 총용량을 주지 않아 사용률(%)을 계산할 수도 없습니다. 단위·방향이 안 맞는 임계를 억지로 적용하면 매일 엉뚱한 경고가 뜹니다. 그래서 이 지표는 절대 임계 대신 "여유가 급격히 줄어드는 것"만 보는 쪽으로 돌렸습니다(아래 급감 감시).

전일 대비 급증

_check_surge는 전일 일평균 대비 오늘 일평균이 2배 이상이면 급증으로 봅니다.

def _check_surge(db_name, instance, source, today, prev_day, surge_ratio):
    """전일 일평균 대비 급증(또는 DROP_WATCH 지표의 급감) 판정."""
    t = _series(today, source)
    p = _series(prev_day, source)
    if t is None or p is None:
        return None
    today_mean, prev_mean = float(t.mean()), float(p.mean())
    if prev_mean <= 0:
        return None
    # 노이즈 가드: 둘 다 너무 작으면(쥐꼬리) 배수가 커도 의미 없음 → 급증 제외
    if today_mean < SURGE_FLOOR and prev_mean < SURGE_FLOOR:
        return None
    ratio = today_mean / prev_mean

    is_drop_watch = source in DROP_WATCH.get(db_name, set())
    if ratio >= surge_ratio:
        return Anomaly(
            db_name=db_name, instance=instance, metric=source,
            kind="surge", grade="warning", value=today_mean, baseline=prev_mean,
            detail={"ratio": ratio, "drop": False},
        )
    if is_drop_watch and ratio <= (1.0 / surge_ratio):
        return Anomaly(
            db_name=db_name, instance=instance, metric=source,
            kind="surge", grade="warning", value=today_mean, baseline=prev_mean,
            detail={"ratio": ratio, "drop": True},
        )
    return None

현실 데이터 보정이 두 군데 들어갑니다.

0 나눗셈 가드 — prev_mean <= 0이면 배수를 계산할 수 없으니 그냥 넘어갑니다. 전일 평균이 0인 지표(예: 평소 거의 0인 deadlocks)는 나눗셈이 무한대가 되거나 의미가 없습니다.

바닥값(floor) 가드 — SURGE_FLOOR = 1.0. 오늘·전일 둘 다 1 미만이면 배수가 아무리 커도 급증으로 안 칩니다. 왜냐하면 실데이터에서 이런 일이 흔합니다. 어떤 지표가 전일 평균 0.01, 오늘 평균 1.14면 배수로는 114배입니다. 숫자만 보면 "폭증"이지만, 절대량이 워낙 작아 실무적으로 의미가 없습니다. 예를 들어 위 데이터에서 MySQL의 Aborted Connects는 평균 0.000246, Com Delete는 평균 0.000313 수준입니다. 이런 쥐꼬리 지표는 하루 한두 건만 더 생겨도 배수가 폭발합니다. 바닥값 가드가 이 노이즈를 걸러 줍니다. POC 단계라 단일 고정값으로 두고, 운영 피드백을 받으며 조정할 계획입니다.

급감 감시(DROP_WATCH) — 보통은 값이 뛰는 게 위험이지만, 여유 공간 같은 지표는 줄어드는 게 위험입니다.

DROP_WATCH: Dict[str, set] = {
    "oracle": {"Free Storage Space"},
}

이 지표는 오늘 평균이 전일의 절반 이하(ratio <= 1/2)로 떨어지면 경고를 냅니다. 위에서 절대 임계를 끈 Oracle 여유 공간을, 여기서 "급감"으로 대신 감시하는 겁니다. 약 690~729GB를 오가는 여유 공간이 하루 만에 절반으로 줄면 분명 비정상입니다.

패턴 변화

_check_pattern은 % 단위 지표에 한해 전일 대비 절대 상승폭(%포인트)을 봅니다.

def _check_pattern(db_name, instance, source, today, prev_day, rise_pp):
    """% 단위 지표의 전일 대비 +rise_pp(%p) 이상 상승 판정."""
    t = _series(today, source)
    p = _series(prev_day, source)
    if t is None or p is None:
        return None
    if not _is_percent(source, t):
        return None
    today_mean, prev_mean = float(t.mean()), float(p.mean())
    rise = today_mean - prev_mean
    if rise >= rise_pp:
        return Anomaly(
            db_name=db_name, instance=instance, metric=source,
            kind="pattern", grade="warning", value=today_mean, baseline=prev_mean,
            detail={"rise_pp": rise},
        )
    return None

급증이 "배수"라면 패턴은 "%포인트 차이"입니다. 둘을 나눈 이유가 있습니다. % 단위 지표는 배수가 부적절합니다. CPU 사용률이 5%에서 40%로 올라도 배수로는 8배라 급증으로 잡힐 수 있지만, 같은 8배라도 0.5%에서 4%면 실무적으로 별일 아닙니다. 반대로 30%에서 55%는 배수로 1.8배라 급증 기준(2배)에 안 걸리지만, +25%포인트는 분명 패턴 변화입니다. 그래서 % 지표는 배수가 아니라 %포인트 차이로 봅니다.

% 지표인지는 이름 힌트와 값 범위로 판정합니다.

_PERCENT_NAME_HINTS = ("usage", "percent", "ratio", "%")

def _is_percent(source: str, series: pd.Series) -> bool:
    """% 단위 지표인가 — 이름 힌트 + 값 범위(0~100)로 판정."""
    name = source.lower()
    if not any(h in name for h in _PERCENT_NAME_HINTS):
        return False
    return series.max() <= 100.5 and series.min() >= -0.5

이름에 usage/percent/ratio/% 가 들어가면서, 값이 실제로 0~100 범위 안일 때만 % 지표로 인정합니다. 이름만 보면 "Cache Hit Ratio" 같은 게 잡히는데, 값이 0~100인지 한 번 더 확인해야 안전합니다. 이름에 ratio가 들어가도 값이 100을 한참 넘는 비율 지표가 있을 수 있어서입니다.

마지막으로 한 DB의 최종 등급은 가장 나쁜 항목 하나로 결정합니다. 리포트 맨 위 신호등(위험/주의/정상 카운트)에 쓰입니다.

@property
def worst_grade(self) -> str:
    """이 DB의 최종 등급(critical > warning > normal) — 리포트 신호등용."""
    if any(a.grade == "critical" for a in self.anomalies):
        return "critical"
    if any(a.grade == "warning" for a in self.anomalies):
        return "warning"
    return "normal"

---

이상 탐지 2 — PCA 복원오차로 조합 이상 잡기

룰베이스는 "미리 적은 규칙"만 잡습니다. CPU·세션·디스크가 동시에 평소보다 들뜬 조합 이상은, 각각이 임계를 안 넘으면 어떤 룰도 발동하지 않습니다. 이걸 잡으려고 비지도 학습을 붙였습니다. 방법은 PCA 복원오차입니다.

PCA 복원오차의 원리

그림3은 핵심 직관입니다. 한 달치 정상 데이터를 PCA로 주성분 몇 개로 압축했다가 다시 복원합니다. 평소와 같은 조합이면 잘 복원되어 오차가 작고, 평소에 없던 조합이면 복원이 어긋나 오차가 큽니다. 그 복원오차를 이상 점수로 씁니다.

비유하면 이렇습니다. 평소에 늘 같은 멜로디를 듣던 사람에게 그 곡을 다섯 음만 들려주고 나머지를 흥얼거려 보라고 합니다. 익숙한 곡이면 빈 부분을 거의 정확히 채웁니다(복원 잘 됨, 오차 작음). 한 번도 안 들어본 곡을 다섯 음만 주면 나머지를 엉뚱하게 채웁니다(복원 실패, 오차 큼). PCA의 주성분은 "평소 데이터가 주로 움직이는 방향 몇 개"입니다. 평소 조합은 그 방향들로 잘 표현되어 복원되고, 평소에 없던 조합은 그 방향으로 표현이 안 되니 복원이 어긋납니다.

좀 더 정확히는, 여러 지표(수십 개 컬럼)를 한 점으로 보면 한 달치 데이터가 고차원 공간에 흩어진 점 구름입니다. PCA는 그 점 구름이 가장 넓게 퍼진 축 몇 개(주성분)를 찾습니다. 각 1분 데이터를 그 축들에만 투영(압축)했다가 원래 차원으로 되돌립니다(복원). 점 구름 안에 있던 평소 패턴은 거의 제자리로 돌아오고, 구름 밖으로 튄 이상 패턴은 멀리 어긋난 채 돌아옵니다. 그 어긋난 거리가 복원오차입니다.

학습과 추론을 분리한 이유

그림4처럼 학습과 추론을 분리했습니다. train.py(또는 학습 스크립트)가 한 달치 데이터로 모델을 학습해 models/{db}.pkl로 저장하고, 매일 도는 detector.detect_ml은 그 저장된 모델을 읽어 오늘 데이터만 채점합니다. 매일 새로 학습하지 않습니다.

이유가 있습니다. 첫째, 매일 학습은 비쌉니다. 둘째, "정상 기준"이 매일 흔들리면 안 됩니다. 어제 이상했던 날의 데이터로 오늘 기준을 다시 잡으면, 이상이 정상으로 흡수되어 버립니다. 그래서 한 달치 정상 패턴으로 기준을 고정해 두고, 그 기준에 비춰 오늘을 봅니다. 셋째, 학습과 추론이 똑같은 전처리를 써야 복원오차가 의미를 가집니다. 그래서 전처리·모델 로직을 ml.py 한 곳에 모으고, 학습 때 만든 변환 규칙(피처 목록, 결측 채울 중앙값, 로그 대상 컬럼, 표준화기)을 모델 묶음에 함께 저장합니다.

그 묶음이 ModelBundle입니다.

@dataclass
class ModelBundle:
    """학습 결과 한 묶음(저장/로드 단위). 추론에 필요한 모든 규칙을 담는다."""
    columns: List[str]                       # 학습에 쓴 피처(이 순서 그대로 추론)
    medians: Dict[str, float]                # 결측 채울 중앙값(피처별)
    log_columns: List[str]                   # log1p 적용한 피처
    scaler: StandardScaler                   # 표준화기(평균0·표준편차1)
    pca: PCA                                 # 주성분 모델
    error_threshold: float                   # 분당 복원오차 임계(점 단위 분위수)
    contamination: float                     # 임계 산출에 쓴 비율
    variance_kept: float                     # 목표 설명분산
    daily_minutes_threshold: float = 0.0     # 정상일의 '임계 초과 분 수' 95퍼센타일(하루 판정 기준)
    n_components: int = 0                     # 실제 선택된 주성분 수
    n_train_rows: int = 0                     # 학습에 쓴 행(분) 수
    trained_at: str = ""                     # 학습 시각
    dropped_constant: List[str] = field(default_factory=list)  # 분산0으로 제외한 피처

자바로 비유하면 ModelBundle은 직렬화해서 파일로 떨군 학습 결과 객체입니다. joblib.dump/load가 ObjectOutputStream/ObjectInputStream 역할입니다. 모델 가중치만이 아니라 전처리 규칙까지 한 객체에 담아 저장하는 게 핵심입니다. 추론 때 같은 규칙을 그대로 꺼내 써야, 학습 때 본 좌표계와 추론 때 좌표계가 일치합니다.

전처리 단계

ml.py의 전처리는 다섯 단계입니다. 학습용 fit_preprocess를 보겠습니다.

def fit_preprocess(frame, wanted, skew_threshold):
    """학습용 전처리 ①~⑤ 를 수행하고, 추론 때 재현할 규칙을 bundle 에 적재."""
    raw = _numeric_frame(frame, wanted)

    # 중앙값 기록(③ 전 원본 기준) 후 결측 채우기
    medians = {c: float(raw[c].median()) for c in raw.columns}
    filled = _fill_missing(raw, medians)

    # ③ 상수 컬럼 제거(분산 0 / 값 1종) — PCA 에 무의미
    dropped = [c for c in filled.columns if filled[c].nunique(dropna=True) <= 1]
    kept = [c for c in filled.columns if c not in dropped]
    filled = filled[kept]

    # ④ 로그 변환 — 한쪽으로 심하게 쏠린(왜도 큰) 음수없는 지표
    log_cols: List[str] = []
    for col in kept:
        s = filled[col]
        if s.min() >= 0 and abs(float(s.skew())) > skew_threshold:
            log_cols.append(col)
    transformed = filled.copy()
    if log_cols:
        transformed[log_cols] = np.log1p(transformed[log_cols])

    # ⑤ 표준화
    scaler = StandardScaler()
    matrix = scaler.fit_transform(transformed.values)
    ...

각 단계가 왜 필요한지 정리하면 이렇습니다.

• ① 피처 선택 — 모니터링할 지표 컬럼만 골라 숫자로 변환합니다.
• ② 결측 채우기 — 짧은 갭은 선형 보간, 그래도 남은 결측은 학습 중앙값으로 채웁니다. 한 달치 1분 데이터에는 수집 누락이 군데군데 있어, 빈칸을 그대로 두면 PCA가 못 돕니다.
• ③ 상수 컬럼 제거 — 한 달 내내 값이 한 종류(분산 0)인 지표는 PCA에 정보가 없어 뺍니다. 예를 들어 위 데이터에서 Oracle EBS IO Balance Max는 한 달 내내 99로 거의 고정입니다. 이런 컬럼은 주성분 계산에 기여하지 못하고 표준화에서 0 나눗셈만 일으킵니다.
• ④ 로그 변환 — 한쪽으로 심하게 쏠린(왜도 큰) 양수 지표에 log1p를 씌웁니다. 디스크 읽기나 Redo 같은 지표는 평소 작다가 가끔 수백 배로 튑니다. 위 데이터에서 MySQL Innodb Rows Read는 평균 3,966인데 최대가 4,478,067, Oracle Redo Entries는 평균 2,772인데 최대 1,407,527입니다. 이렇게 분포가 극단적으로 길면 거대한 몇 값이 표준화를 지배해 버립니다. 로그를 씌워 분포를 눌러야 평소 패턴이 제대로 학습됩니다. 음수가 있는 지표에는 안 씁니다(로그가 정의 안 됨).
• ⑤ 표준화 — 평균 0, 표준편차 1로 맞춥니다. 단위와 크기가 제각각인 지표들을(바이트 vs %, 카운트 vs 초) 같은 저울에 올려야 PCA가 한 지표에 휘둘리지 않습니다. 표준화 안 하면 수십억 바이트 단위 지표가 0~100 % 지표를 압도합니다.

여기서 중요한 건 학습에서 만든 규칙(중앙값, 로그 대상, 표준화 파라미터)을 추론에서 그대로 재현한다는 점입니다. 추론용 transform을 보면, 새 규칙을 만들지 않고 묶음에 저장된 규칙을 꺼내 씁니다.

def transform(bundle: ModelBundle, frame: pd.DataFrame):
    """추론용 전처리 — 학습과 '같은 규칙'으로 변환(컬럼/순서/중앙값/로그/스케일 동일)."""
    if frame is None or frame.empty:
        return np.empty((0, len(bundle.columns))), pd.Index([])
    # 학습 컬럼 기준으로 재정렬(없는 컬럼은 NaN → 중앙값으로 채움)
    raw = frame.reindex(columns=bundle.columns).apply(pd.to_numeric, errors="coerce")
    filled = _fill_missing(raw, bundle.medians)
    if bundle.log_columns:
        cols = [c for c in bundle.log_columns if c in filled.columns]
        filled[cols] = np.log1p(filled[cols].clip(lower=0))
    matrix = bundle.scaler.transform(filled.values)
    return matrix, frame.index

fit_transform(학습)과 transform(추론)을 자바의 비유로 보면, 학습 때 scaler.fit_transform은 평균·표준편차를 계산하면서 변환까지 하지만, 추론 때 scaler.transform은 학습에서 계산해 둔 평균·표준편차로 변환만 합니다. 추론 데이터로 다시 평균을 재면 좌표계가 어긋나 복원오차가 무의미해집니다. 컬럼 순서도 reindex로 학습 때 순서에 맞춥니다. 새 데이터에 없는 컬럼은 중앙값으로 채웁니다.

모델 학습과 임계 산출

fit_model이 주성분을 학습하고 복원오차 임계를 정합니다.

def fit_model(frame, wanted, variance_kept=0.95, contamination=0.05, skew_threshold=1.5):
    """한 달치 frame 으로 PCA 모델 학습 + 복원오차 임계 산출 → 완성된 bundle 반환."""
    matrix, bundle = fit_preprocess(frame, wanted, skew_threshold)
    if matrix.shape[0] < 10 or matrix.shape[1] < 2:
        raise ValueError(f"학습 데이터 부족: rows={matrix.shape[0]}, features={matrix.shape[1]}")

    pca = PCA(n_components=variance_kept, svd_solver="full")
    pca.fit(matrix)
    bundle.pca = pca
    bundle.n_components = int(pca.n_components_)
    ...
    per_row, _ = reconstruction_error(bundle, matrix)
    bundle.error_threshold = float(np.quantile(per_row, 1.0 - contamination))

    # 하루 판정 기준: 정상일에도 점 임계는 (contamination)만큼 넘으므로(예 0.05×1440≈72분),
    # '정상일의 일별 초과 분 수' 분포의 95퍼센타일을 하루 임계로 잡는다(데이터 기반).
    over = pd.Series(per_row > bundle.error_threshold, index=frame.index)
    daily_counts = over.groupby(over.index.normalize()).sum()
    bundle.daily_minutes_threshold = float(daily_counts.quantile(0.95)) if len(daily_counts) else 0.0
    return bundle

PCA(n_components=0.95)는 "누적 설명분산 95%를 채우는 만큼" 주성분을 자동 선택합니다. 주성분 수를 손으로 정하지 않고, 데이터의 95%를 설명할 만큼만 남깁니다. 나머지 5%는 잡음으로 보고 버립니다. 이 "버린 5%"가 복원오차로 나타납니다. 평소 패턴은 95% 주성분으로 충분히 표현되지만, 이상 패턴은 버려진 방향에 정보가 실려 있어 복원이 크게 어긋납니다.

복원오차는 압축했다가 복원한 뒤 원본과의 차이를 제곱해 평균낸 값입니다.

def reconstruction_error(bundle, matrix):
    """행(분)별 복원오차와 피처별 제곱오차를 반환."""
    if matrix.shape[0] == 0:
        return np.empty(0), np.empty((0, len(bundle.columns)))
    reduced = bundle.pca.transform(matrix)            # 압축(주성분 좌표)
    restored = bundle.pca.inverse_transform(reduced)  # 복원
    per_feature_sq = (matrix - restored) ** 2
    per_row = per_feature_sq.mean(axis=1)
    return per_row, per_feature_sq

여기서 두 가지를 같이 반환하는 게 뒤에 중요해집니다. per_row는 1분마다의 복원오차(이상 점수), per_feature_sq는 그 분에서 어느 지표가 얼마나 복원을 어긋나게 했는지(피처별 제곱오차)입니다. 후자가 "무엇이 평소와 달랐나"를 알려주는 재료입니다.

점 임계와 하루 임계 두 단계

임계를 두 단계로 둔 게 이 설계의 핵심 결정입니다.

점 임계(error_threshold) — 학습 복원오차의 95퍼센타일입니다(contamination=0.05). 즉 "정상 데이터의 상위 5%만큼 어긋난 분"을 이상 분으로 칩니다.

문제는, 이 점 임계만 쓰면 정상일에도 매일 약 5%(하루 1440분 중 약 72분)가 이상으로 잡힌다는 점입니다. 95퍼센타일이니 정의상 그렇습니다. 매일 72분씩 "이상"이라고 알리면 알림이 무의미해집니다.

그래서 하루 임계(daily_minutes_threshold) 를 한 겹 더 뒀습니다. 학습 데이터를 날짜별로 묶어 "그날 점 임계를 넘은 분 수"를 세고, 그 분포의 95퍼센타일을 하루 발동 기준으로 잡습니다. 정상일이라도 보통 며칠은 점 임계를 60~80분 넘기는데, 그 정상 변동 폭을 데이터로 측정해 두는 겁니다. 오늘의 초과 분이 그 평소 범위를 유의하게 넘을 때만 "이상한 날"로 발동합니다. 임계를 손으로 정한 게 아니라 정상 데이터 분포에서 뽑았다는 점이 중요합니다. 매일 오발동하지 않게 하는 안전장치입니다.

추론과 하루 판정

매일 도는 detect_ml이 이 둘을 종합합니다.

def detect_ml(db_metrics: DbMetrics, config: dict) -> List[Anomaly]:
    """PCA 복원오차 기반 패턴 이상 탐지."""
    ml_cfg = config.get("global", {}).get("ml", {})
    if not ml_cfg.get("enabled", False):
        return []

    db_name = db_metrics.db_name
    model_path = os.path.join(ml_cfg.get("model_dir", "models"), f"{db_name}.pkl")
    today = db_metrics.today
    if not os.path.exists(model_path) or today is None or today.empty:
        if not os.path.exists(model_path):
            print(f"[ml] 모델 없음(skip): {model_path} — train.py 로 학습 필요")
        return []

    bundle = joblib.load(model_path)
    matrix, index = ml.transform(bundle, today)
    if matrix.shape[0] == 0:
        return []

    per_row, per_feature_sq = ml.reconstruction_error(bundle, matrix)
    over = per_row > bundle.error_threshold
    anomaly_minutes = int(over.sum())

    min_floor = int(ml_cfg.get("min_anomaly_minutes", 10))
    need_minutes = max(min_floor, bundle.daily_minutes_threshold)
    if anomaly_minutes < need_minutes:
        return []
    ...

흐름은 이렇습니다. 모델이 없거나 데이터가 없으면 조용히 빈 리스트를 돌려줍니다(매일 도는 cron이 모델 한 개 없다고 죽으면 안 됩니다). 모델이 있으면 오늘 데이터를 같은 전처리로 변환하고, 분당 복원오차를 구해 점 임계를 넘은 분 수를 셉니다. 그 분 수가 하루 임계(설정 최소값과 학습 임계 중 큰 값)보다 작으면 발동하지 않습니다. 즉 "평소보다 유의하게 많은 분이 어긋난 날" 만 이상으로 봅니다.

왜 Isolation Forest나 오토인코더가 아니라 PCA인가

처음 비지도 탐지로 검토한 건 Isolation Forest와 오토인코더였습니다. 결국 PCA로 정한 데는 이유가 있습니다.

• 설명 가능성 — PCA 복원오차는 "어느 지표가 복원을 얼마나 어긋나게 했는지"를 피처별 제곱오차로 바로 분해할 수 있습니다(per_feature_sq). 이게 뒤에서 "무엇이 이상한가"를 만드는 재료가 됩니다. Isolation Forest는 이상 점수는 주지만 "어느 지표 때문에"를 깔끔하게 분해하기 어렵습니다. DBA에게 "이상입니다"만 던지면 쓸모가 없고, "이 지표들이 평소와 달랐습니다"까지 말해야 합니다.
• 가벼움과 결정성 — PCA는 선형이라 학습이 빠르고, 산출물이 작아 zip 납품(도커 없는 온프레미스)에 맞습니다. 오토인코더는 더 복잡한 비선형 패턴을 잡을 수 있지만 학습 비용·의존성·튜닝 부담이 큽니다. POC 단계에서는 과합니다.
• 단계적 고도화 — PCA로 골격(전처리·복원오차·임계·영향 지표 산출)을 먼저 세워 두면, 나중에 같은 자리에 오토인코더를 끼워 넣기 쉽습니다. 복원오차라는 개념과 인터페이스가 동일하기 때문입니다. 그래서 POC는 PCA로 하고, 고도화 단계에서 오토인코더로 교체하는 방향으로 잡았습니다.

---

무엇이 이상한가 만들기 — 영향 지표 산출

복원오차가 크다는 건 "평소와 다르다"는 신호일 뿐, "무엇이 어떻게 달랐는지"는 아닙니다. DBA에게는 그 "무엇"이 필요합니다. 그래서 detect_ml 뒤쪽에서 영향 지표를 뽑습니다.

그림6은 영향 지표를 만드는 흐름입니다. 하루 중 복원오차가 가장 큰 1분(최악의 분)을 고르고, 그 분에서 피처별 제곱오차가 큰 지표 Top3를 뽑습니다. 그다음 각 지표가 평소(그날 평균) 대비 피크 시각에 어떻게 달랐는지(올랐는지/내렸는지, 몇 배인지)를 계산합니다.

    # 최악의 분 + 그 분에서 복원오차가 큰 지표 Top3('무엇이 평소와 달랐나')
    worst_pos = int(per_row.argmax())
    worst_error = float(per_row[worst_pos])
    peak_at = index[worst_pos] if len(index) > worst_pos else None
    top_idx = per_feature_sq[worst_pos].argsort()[::-1][:3]
    top_metrics = [bundle.columns[i] for i in top_idx]

    # 영향 지표가 '평소(그날 평균) 대비 피크 시각에 어떻게 달랐나' 까지 계산
    top_detail = []
    for col in top_metrics:
        entry = {"name": col}
        if col in today.columns and peak_at is not None:
            s = pd.to_numeric(today[col], errors="coerce").dropna()
            if not s.empty and peak_at in s.index:
                peak_v = float(s.loc[peak_at])
                avg_v = float(s.mean())
                entry.update({
                    "peak": peak_v, "avg": avg_v,
                    "arrow": "↑" if peak_v >= avg_v else "↓",
                    "ratio": (peak_v / avg_v) if avg_v not in (0, 0.0) else None,
                })
        top_detail.append(entry)

줄별로 보면, per_row.argmax()로 하루 중 복원오차가 가장 큰 분의 위치를 찾고, peak_at으로 그 시각을 잡습니다. 그 분의 per_feature_sq[worst_pos](지표별 제곱오차)를 내림차순 정렬해 상위 3개를 영향 지표로 고릅니다. 그 분에서 복원을 가장 크게 어긋나게 한 지표들이 곧 "무엇이 평소와 달랐나"입니다.

그다음 영향 지표 각각에 대해, 그날 평균(avg_v)과 피크 시각 값(peak_v)을 비교해 방향(arrow, 올랐는지 내렸는지)과 배수(ratio)를 채웁니다. 이름만으로는 "Innodb Rows Read가 영향 지표"라고 해 봐야 무슨 일이 났는지 모릅니다. "평소 4,495였는데 피크 938,813으로 약 208배 튀었다"까지 줘야 의미가 통합니다. 여기서도 avg_v가 0이면 배수를 None으로 둬 0 나눗셈을 피합니다.

실제 데이터로 보면 이런 케이스가 나옵니다. MySQL 어느 날, 복원오차 최악의 분에서 영향 지표 Top으로 Innodb Rows Read가 평소 4,495 → 피크 938,813(약 208배), Innodb Buffer Pool Read Requests가 평소 7,822 → 피크 3,154,251(약 403배)로 함께 튀었습니다. 둘 다 디스크 읽기·조회 계열입니다. 각각의 절대값은 임계 규칙으로 못 잡았지만, 둘이 같은 시각에 함께 평소 조합을 크게 벗어난 겁니다. 영향 지표 카테고리가 "디스크 읽기·조회"로 모이니, 진단은 "인덱스를 못 타는 대량 조회"로 추정합니다.

마지막으로 등급을 매기고 Anomaly로 포장합니다.

    # 등급: 초과 분이 정상일 기준의 2배↑ 거나 오차가 점임계의 3배↑ 면 critical, 그 외 warning
    grade = "critical" if (anomaly_minutes >= 2 * need_minutes or
                           worst_error >= 3 * bundle.error_threshold) else "warning"

    return [Anomaly(
        db_name=db_name, instance=db_metrics.instance,
        metric="패턴 이상(PCA)", kind="ml", grade=grade,
        value=worst_error, threshold=float(bundle.error_threshold),
        at_time=peak_at,
        detail={"anomaly_minutes": anomaly_minutes, "top_metrics": top_metrics,
                "top_metrics_detail": top_detail},
    )]

ML 이상도 결국 같은 Anomaly 객체로 나옵니다. kind="ml"이고, 지표명은 "패턴 이상(PCA)"이라는 고정 문자열이며, 영향 지표 목록(top_metrics)과 상세(top_metrics_detail)가 detail에 들어갑니다. 등급은 초과 분이 하루 기준의 2배 이상이거나 최악 오차가 점 임계의 3배 이상이면 위험, 아니면 주의입니다. 어긋남이 넓게(분 수) 혹은 깊게(오차 크기) 일어나면 위험으로 올립니다.

조금 전 영향 지표 상세에서 한 가지를 더 처리합니다. 변화가 미미한 영향 지표를 "평소 수준"으로 표시하는 건데, 이건 표시 단계의 일이라 리포트 쪽에서 다룹니다. 다음 절에서 봅니다.

---

리포트에 녹이기 — 룰베이스와 ML을 한 화면에

탐지가 두 갈래여도 DBA가 보는 건 한 장의 리포트입니다. 룰베이스로 잡은 것과 ML로 잡은 것을 따로 두면 혼란스럽습니다. 그래서 같은 진단 카드 안에 통합하되, 출처를 색으로 구분했습니다.

그림7이 진단 카드 한 장의 구조입니다. 위에서부터 ① 감지된 이상(무엇이 잡혔나), ② 원인 추정(왜 그럴까), ③ 권장 조치(무엇을 확인할까), ④ 종합 한 줄. 룰베이스 항목은 기본색(빨강/주황), ML 항목은 보라색으로 같은 칸에 섞입니다. 보라색만 따라가면 "기계가 추가로 잡은 것"이 한눈에 보입니다.

① 감지된 이상 줄 만들기

_anomaly_detail_line이 Anomaly 한 건을 사람이 읽는 한 줄로 바꿉니다. kind마다 표현이 다릅니다.

def _anomaly_detail_line(a: Anomaly) -> str:
    """① 감지된 이상 — 항목 1건 상세(단위·정확 시각 포함)."""
    if a.kind == "threshold":
        dur = a.detail.get("duration_min")
        at = f" · 최고시각 {a.at_time:%Y-%m-%d %H:%M:%S}" if a.at_time is not None else ""
        return f"{a.metric}: 최고 <b>{_fmt_value(a.metric, a.value)}</b> (임계 {a.threshold:g} · {dur}분 초과{at})"
    if a.kind == "surge":
        return (f"{a.metric}: 오늘 평균 <b>{_fmt_value(a.metric, a.value)}</b> "
                f"(전일 {_fmt_value(a.metric, a.baseline)} · {a.detail.get('ratio',0):.2f}배↑)")
    if a.kind == "pattern":
        return (f"{a.metric}: 전일 대비 <b>+{a.detail.get('rise_pp',0):.0f}%p</b> "
                f"({_fmt_value(a.metric, a.baseline)}→{_fmt_value(a.metric, a.value)})")
    ...

룰베이스 셋은 단순합니다. 임계는 "최고 X (임계 Y · N분 초과 · 최고시각)", 급증은 "오늘 평균 X (전일 Y · N배↑)", 패턴은 "전일 대비 +N%p"입니다. _fmt_value로 단위에 맞게 표기합니다(% / count / bytes 등).

ML 줄이 가장 공이 들어갔습니다.

    if a.kind == "ml":
        minutes = a.detail.get("anomaly_minutes")
        at = a.at_time.strftime("%Y-%m-%d %H:%M:%S") if a.at_time is not None else "-"
        detail = a.detail.get("top_metrics_detail") or []

        def _changed(d):
            if "peak" not in d:
                return False
            r = d.get("ratio")
            return not (_fmt_value(d["name"], d["avg"]) == _fmt_value(d["name"], d["peak"])
                        or (r is not None and 0.9 <= r <= 1.1))

        def _val(d):  # '평소 X → 피크 Y ↑N배' 또는 '평소 수준'
            if not _changed(d):
                return "평소 수준"
            r = d.get("ratio")
            rt = f", {r:.1f}배" if r and r >= 1.5 else ""
            return f"평소 {_fmt_value(d['name'], d['avg'])} → 피크 {_fmt_value(d['name'], d['peak'])} {d['arrow']}{rt}"

        # 변화 폭(증가는 배수, 감소는 역수)이 가장 큰 지표를 대표로 맨 앞에
        def _mag(d):
            r = d.get("ratio")
            return max(r, 1.0 / r) if r else 0.0
        changed = [d for d in detail if _changed(d)]
        lead = max(changed, key=_mag) if changed else (detail[0] if detail else None)
        if lead is None:
            return f"<b>패턴 이상</b> <span style='color:#7b3fb5;font-weight:bold;'>(ML · 패턴 이상)</span>: 평소와 다른 패턴 {minutes}분 (피크 {at})"
        head = (f"<b>{lead['name']}</b> <span style='color:#7b3fb5;font-weight:bold;'>(ML · 패턴 이상)</span>: "
                f"{_val(lead)}")
        rest = [f"{d['name']}({_val(d)})" for d in detail if d is not lead]
        rest_str = (" · 함께 변동: " + ", ".join(rest)) if rest else ""
        return f"{head}<br><span style='color:#888;'>(평소와 다른 패턴 {minutes}분 · 피크 {at}{rest_str})</span>"
    return a.metric

여기 들어간 판단이 셋입니다.

대표 지표를 맨 앞에 — 영향 지표 Top3 중 변화 폭이 가장 큰 지표를 대표로 골라 줄 맨 앞에 둡니다(_mag로 정렬). 변화 폭은 증가면 배수, 감소면 역수로 봐서 "가장 크게 변한 것"을 잡습니다. 룰베이스 항목이 "지표명: 값" 형식으로 한눈에 읽히는 것과 같은 가독성을 ML 항목에도 주려는 겁니다. 예시로는 Innodb Buffer Pool Read Requests가 403배로 가장 크게 변했다면 그게 대표로 앞에 서고, 나머지는 "함께 변동"으로 뒤에 붙습니다.

변화가 미미한 영향 지표는 "평소 수준" — _changed가 핵심입니다. 영향 지표 Top3라고 다 크게 변한 건 아닙니다. 복원오차에 약간 기여했지만 실제 값은 평소와 거의 같은 지표도 끼어 있을 수 있습니다. 특히 평소 0이던 지표가 피크에도 0이면, 단위 포맷상 둘 다 "0"으로 찍혀 "평소 0 → 피크 0"이라는 무의미한 줄이 나옵니다. 그래서 표시 포맷이 같거나(_fmt_value 결과 동일) 배수가 0.9~1.1 사이면 변화 없음으로 보고 "평소 수준"이라고만 적습니다. 거짓 변화를 안 보여주는 겁니다.

(ML · 패턴 이상) 라벨과 보라색 — ML 항목에는 보라색 (ML · 패턴 이상) 라벨을 붙입니다(#7b3fb5). 룰베이스 항목과 한 목록에 있어도 출처가 보입니다.

② 원인 추정 / ③ 권장 조치 — 상관 규칙 매칭

룰베이스 진단은 "지표를 카테고리로 분류 → 동시 발생 패턴을 상관 규칙에 매칭"으로 만듭니다.

그림8은 진단을 만드는 흐름입니다. 이상 지표들을 카테고리(cpu/session/write/lock 등)로 분류해 집합으로 모은 뒤, 우선순위 순서로 상관 규칙을 검사합니다. 첫 매칭에서 멈추고 그 규칙의 원인·조치·종합을 씁니다. 아무 규칙에도 안 걸리면 폴백 템플릿을 씁니다.

지표를 카테고리로 분류하는 규칙입니다.

_CATEGORY_RULES = [
    ("cpu", ("cpu",)),
    ("lock", ("lock", "잠금")),
    ("longq", ("long active", "long query", "trx time", "elapse")),
    ("write", ("redo", "undo", "log file sync", "com insert", "com update",
               "com delete", "xact commit", "tup updated", "tup inserted", "tup deleted")),
    ("session", ("active session", "active backend", "threads running", "session", "backend", "numbackends")),
    ("conn", ("connection",)),
    ("mem", ("memory",)),
    ("storage", ("storage", "filesystem", "disk", "volume")),
    ("io", ("iops", "tps", "throughput", "queries", "physical reads", "blks read", "rows read")),
]

def _category(metric: str) -> Optional[str]:
    """지표명을 진단용 카테고리로 분류(첫 매칭)."""
    low = metric.lower()
    for cat, keywords in _CATEGORY_RULES:
        if any(k in low for k in keywords):
            return cat
    return None

지표명에 들어간 단어로 카테고리를 정합니다. "rows read"나 "physical reads"가 들어가면 io, "redo"·"undo"·"com insert"면 write, "cpu"면 cpu입니다. 위에서부터 첫 매칭이라 순서가 우선순위입니다.

그다음 _diagnose가 카테고리 조합을 상관 규칙에 매칭합니다.

def _diagnose(det: DbDetection) -> dict:
    """이상 지표 카테고리 동시 발생 → 상관 규칙으로 원인추정·권장조치·종합 생성.

    ML(PCA) 이상은 지표명이 '패턴 이상(PCA)'이라 분류 불가 → 영향 지표(top_metrics)로 분류한다.
    """
    cats: set = set()
    for a in det.anomalies:
        names = a.detail.get("top_metrics", []) if a.kind == "ml" else [a.metric]
        cats.update(c for c in (_category(n) for n in names) if c)
    tool = _DB_TOOLS.get(det.db_name, "")

    def has(*names):
        return any(n in cats for n in names)

    # 우선순위 상관 규칙
    if has("cpu") and has("session", "write"):
        return {
            "title": "대량 DML 배치/비정상 트랜잭션에 의한 부하 (추정)",
            "causes": [
                "CPU 급등과 세션·쓰기(Redo·Undo·DML) 지표가 같은 시간대에 동반 발생 → 단일 작업이 원인일 가능성.",
                ...
            ],
            "actions": [
                f"피크 시각 전후 활성 세션·Top SQL 확인 ({tool})",
                ...
            ],
            "summary": "일시적 배치성 부하로 보이며, 동일 패턴 반복 시 야간 배치 재설계를 권장합니다.",
        }
    if has("lock") and has("longq"):
        return { "title": "장기 트랜잭션에 의한 잠금 경합 (추정)", ... }
    if has("lock"):
        return { "title": "잠금 대기 발생 (추정)", ... }
    if has("conn"):
        return { "title": "커넥션 사용률 상승 (추정)", ... }
    if has("storage"):
        return { "title": "여유 공간 급감 (추정)", ... }
    if has("cpu"):
        return { "title": "CPU 사용률 상승 (추정)", ... }
    if has("write"):
        return { "title": "쓰기 부하 급증 (추정)", ... }
    # 폴백
    return {
        "title": "평소와 다른 추이 감지",
        "causes": ["일부 지표가 평소(전일) 대비 벗어난 추이를 보입니다."],
        "actions": [f"해당 시각 수행 작업·세션 확인 ({tool})" if tool else "해당 시각 수행 작업 확인"],
        "summary": "지속·반복 여부를 모니터링하고 추세 변화 시 상세 점검을 권장합니다.",
    }

여기서 두 가지가 핵심입니다.

ML도 같은 분류 체계로 들어옵니다. ML 이상은 지표명이 "패턴 이상(PCA)"이라 그대로는 카테고리 분류가 안 됩니다. 그래서 ML일 때는 영향 지표(top_metrics)를 분류 대상으로 씁니다. 위 코드의 names = a.detail.get("top_metrics", []) if a.kind == "ml" else [a.metric]이 그 처리입니다. 덕분에 룰베이스로 잡은 CPU와 ML로 잡은 디스크 읽기가 같은 카테고리 집합에 모여, 함께 상관 규칙에 매칭됩니다.

우선순위 규칙은 구체적인 것부터. CPU+세션+쓰기가 동시에 잡히면 "대량 DML 배치/비정상 트랜잭션"으로, lock+longq면 "장기 트랜잭션 잠금 경합"으로 봅니다. 이런 복합 패턴을 먼저 검사하고, 단일 카테고리(lock만, cpu만)는 뒤에 둡니다. 여러 지표가 함께 튀는 건 단일 원인일 가능성이 높아, 묶어서 더 구체적인 진단을 줄 수 있기 때문입니다. 아무것도 안 걸리면 폴백으로 "평소와 다른 추이 감지"를 씁니다. POC는 LLM 없이 이 템플릿·룰만 씁니다. 정밀한 쿼리 단위 원인 분석은 이벤트 데이터와 LLM이 붙는 고도화 단계의 몫입니다.

진단 박스 조립과 ML 통합

_diagnosis_box가 카드 한 장을 조립합니다. 핵심은 ML 항목을 별도 박스가 아니라 ②원인·③조치의 제자리에 보라색으로 끼워 넣는 부분입니다.

def _diagnosis_box(det: DbDetection, accent: str, bg: str) -> str:
    """이상/원인추정/권장조치/함께 확인할 지표/종합 상세 박스."""
    diag = _diagnose(det)
    found = "".join(f"<li>{_anomaly_detail_line(a)}</li>" for a in det.anomalies)
    # ML(비지도) 이상이 있으면 그 원인·권장을 ②원인추정·③권장조치 제자리에 보라색으로 합침
    cause_items = list(diag["causes"])
    action_items = list(diag["actions"])
    ml_anoms = [a for a in det.anomalies if a.kind == "ml"]
    if ml_anoms:
        cats = [c for c in (_category(m) for m in ml_anoms[0].detail.get("top_metrics", [])) if c]
        hints = {
            "io": ("디스크 읽기·조회", "인덱스를 못 타는 대량 조회·통계성 쿼리·비정상 배치"),
            "cpu": ("CPU·연산", "복잡한 연산·풀 스캔·비효율 쿼리"),
            "write": ("쓰기·트랜잭션", "대량 INSERT/UPDATE·배치 적재"),
            "session": ("세션·연결", "연결 급증·세션 미반환"),
            "lock": ("잠금·경합", "장기 트랜잭션·블로킹 세션"),
            "longq": ("장기 실행 쿼리", "미완결 트랜잭션·느린 쿼리"),
            "conn": ("커넥션", "커넥션 풀 고갈·연결 급증"),
            "mem": ("메모리", "캐시 부족·대량 정렬"),
            "storage": ("저장 공간", "급격한 데이터 증가·로그 적재"),
        }
        from collections import Counter
        # 동점일 때 비결정적이지 않도록 Counter(영향지표 순서=복원오차 순서 우선)로 대표 카테고리 결정
        top_cat = Counter(cats).most_common(1)[0][0] if cats else None
        PURPLE = "#7b3fb5"
        if top_cat in hints:
            sit, cause = hints[top_cat]
            cause_items.append(f'<span style="color:{PURPLE};"><b>(ML)</b> {sit} 관련 지표가 평소 패턴에서 함께 벗어남 — 보통 {cause}</span>')
        else:
            cause_items.append(f'<span style="color:{PURPLE};"><b>(ML)</b> 여러 지표가 같은 시각 평소 조합에서 함께 벗어남</span>')
        action_items.append(f'<span style="color:{PURPLE};"><b>(ML)</b> 그 시각 실행된 쿼리·배치 확인 (임계값 미초과여도 평소와 다른 동작)</span>')
    ...

ML 영향 지표들의 카테고리를 세어 대표 카테고리를 정하고(Counter.most_common), 그 카테고리에 맞는 상황·흔한 원인 힌트를 보라색 원인 항목으로 추가합니다. 예를 들어 영향 지표가 디스크 읽기 계열이면 "디스크 읽기·조회 관련 지표가 평소 패턴에서 함께 벗어남 — 보통 인덱스를 못 타는 대량 조회·통계성 쿼리·비정상 배치"라고 붙습니다. 권장 조치에도 "그 시각 실행된 쿼리·배치 확인(임계값 미초과여도 평소와 다른 동작)"을 보라색으로 더합니다.

왜 별도 박스가 아니라 같은 자리에 통합했나. ML 진단을 따로 박스로 빼면, DBA가 "룰베이스 박스"와 "ML 박스"를 두 번 읽어야 합니다. 같은 사건을 두 모듈이 본 것뿐인데 화면이 둘로 갈리면 인지 부담이 커집니다. 그래서 원인·조치 목록 한 군데에 룰베이스 항목(기본색)과 ML 항목(보라색)을 함께 둡니다. 한 번에 읽되, 색으로 출처를 구분합니다.

보라색을 고른 이유. 위험은 빨강, 주의는 주황으로 이미 등급에 색을 썼습니다. ML은 등급이 아니라 "출처"의 구분이라, 등급 색과 겹치면 안 됩니다. 그래서 등급 팔레트와 충분히 다른 보라색(#7b3fb5)을 출처 표시 전용으로 정했습니다. 리포트 어디서든 보라색은 "기계가 추가로 본 것"이라는 일관된 의미를 갖습니다.

진단 박스는 마지막으로 "함께 확인할 지표"(연관 지표 추천)까지 붙여 ①~④ 구조로 렌더링합니다.

    return f"""
      <div style="...border-left:3px solid {accent};...">
        <div style="...color:{accent};...">진단 — {diag['title']}</div>
        <div>① 감지된 이상</div>
        <ul>{found}</ul>
        <div>② 원인 추정 <span>(지표 상관 분석)</span></div>
        <ul>{causes}</ul>
        <div>③ 권장 조치</div>
        <ol>{actions}</ol>
        {related_html}
        <div>④ 종합 · {diag['summary']}</div>
      </div>"""

연관 지표는 카테고리별로 "이 유형 이상이면 함께 보면 좋은 지표"를 미리 정의해 둔 _RELATED_METRICS에서 가져옵니다. 예를 들어 io 카테고리면 "Read/Write IOPS · Throughput · Physical Reads"를 추천합니다. 이 목록은 1차안이라 DBA 검증으로 보정할 대상입니다.

---

그래프

리포트에는 추이 그래프가 들어갑니다. 위험 등급 지표만 그리고, ML 이상이 있으면 영향 지표를 따로 한 장 더 그립니다.

_make_graph가 하루치 시계열 한 장을 그립니다. kind에 따라 기준선이 다른 게 핵심입니다.

def _make_graph(series, anomaly, out_path, metric_name=None) -> bool:
    """위험 지표 하루 추이 그래프(PNG, 라이트 모드). 성공 시 True."""
    ...
    ax.plot(series.index, series.values, color="#3d7ebf", linewidth=1.6)
    if anomaly.kind == "threshold" and anomaly.threshold is not None:
        ax.axhline(anomaly.threshold, color=C_CRIT, linewidth=1, linestyle="--")
        ax.text(series.index[-1], anomaly.threshold, f"Threshold {anomaly.threshold:g} ",
                color=C_CRIT, fontsize=8, va="bottom", ha="right")
    elif anomaly.kind == "ml":
        # ML 영향지표는 임계값이 없으므로, 기준선으로 '평소 평균선'(회색 점선)을 표시
        avg = float(series.mean())
        ax.axhline(avg, color="#888", linewidth=1, linestyle="--")
        ax.text(series.index[-1], avg, f"Avg {avg:g} ",
                color="#888", fontsize=8, va="bottom", ha="right")
    if anomaly.at_time is not None and anomaly.at_time in series.index:
        yv = series.loc[anomaly.at_time]
        ax.scatter([anomaly.at_time], [yv], color=C_CRIT, s=26, zorder=5)
        # 튄 지점 정확 시각 — 피크 점 바로 위에 표기, 흰 배경 박스로 선과 겹쳐도 읽히게
        ax.annotate(anomaly.at_time.strftime("%Y-%m-%d %H:%M:%S"),
                    xy=(anomaly.at_time, yv), xytext=(0, 10), textcoords="offset points",
                    fontsize=7.5, color=C_CRIT, ha="center", va="bottom", zorder=7,
                    bbox=dict(boxstyle="round,pad=0.25", fc="white", ec="none", alpha=0.5))

기준선이 다릅니다. 룰베이스(임계) 그래프는 빨간 점선으로 임계값을 긋습니다. "이 선을 넘었다"가 한눈에 보입니다. 하지만 ML 영향 지표는 임계값이 없습니다(평소와 다르다는 것이지 어떤 절대값을 넘은 게 아닙니다). 그래서 ML 그래프는 임계선 대신 평소 평균선(회색 점선) 을 긋습니다. "이 지표가 평소 평균은 이 정도인데 이만큼 튀었다"를 보여 줍니다. 같은 그래프 함수지만 비교 기준이 탐지 방식에 맞게 바뀝니다.

피크 시각 가독성. 처음엔 피크 점에 시각을 그냥 텍스트로 찍었는데, 데이터 선과 겹쳐 잘 안 읽혔습니다. 그래서 점 바로 위에 흰 배경 박스(bbox, 반투명)를 깔고 그 위에 시각을 얹었습니다. 선과 겹쳐도 시각이 읽힙니다.

y축 큰 수 축약. 디스크 읽기 같은 지표는 값이 수백만까지 갑니다. matplotlib 기본은 1e6 같은 지수 표기를 쓰는데 직관적이지 않습니다. 그래서 K·M으로 바꿨습니다.

    def _yfmt(v, _):
        a = abs(v)
        if a >= 1e6:
            return f"{v/1e6:.1f}M"
        if a >= 1e3:
            return f"{v/1e3:.0f}K"
        return f"{v:.0f}" if (a >= 1 or v == 0) else f"{v:.2f}"
    ax.yaxis.set_major_formatter(FuncFormatter(_yfmt))

3,154,251은 "3.2M", 4,899는 "5K"로 찍힙니다. 그래프는 영문·숫자만 써서(폰트와 무관하게) 한글 깨짐을 피하고, DPI 260으로 저장해 메일에서도 선명하게 보이게 했습니다.

ML 영향 지표 그래프는 _ml_graph가 따로 한 장 더 그립니다. 영향 지표 Top1을 평소 평균선과 함께 그려, "이 지표가 이렇게 튀었다"를 시각적으로 보강합니다. 보라색 라벨 "(ML 결과 · 영향 지표 Top1)"을 달아 출처를 명시합니다.

def _ml_graph(det, db, today, out_dir, images) -> str:
    """ML(비지도) 이상의 영향 지표 Top1 추이 그래프. 없으면 빈 문자열."""
    ml = next((a for a in det.anomalies if a.kind == "ml"), None)
    if ml is None:
        return ""
    metric = (ml.detail.get("top_metrics") or [None])[0]
    if today is None or metric is None or metric not in today.columns:
        return ""
    series = pd.to_numeric(today[metric], errors="coerce").dropna()
    if series.empty:
        return ""
    cid = f"mlgraph_{db}"
    png = os.path.join(out_dir, f"{cid}.png")
    if not _make_graph(series, ml, png, metric):
        return ""
    images[cid] = png
    ...

---

설계하며 부딪힌 것들

기능을 짜는 것보다 "현실 데이터에서 자연스럽게 읽히게" 다듬는 데 더 많은 시간이 들었습니다. 부딪힌 것들을 증상 → 원인 → 해결로 정리합니다.

대표 지표를 복원오차 1순위에서 변화 폭 가장 큰 것으로

증상. ML 이상 줄의 맨 앞(대표 지표)이 어색할 때가 있었습니다. 복원오차 기여 1순위 지표가 정작 값으로는 평소와 거의 안 변한 경우, 줄 맨 앞에 "거의 안 변한 지표"가 서고 정작 크게 튄 지표는 뒤에 묻혔습니다.

원인. 처음엔 대표 지표를 "복원오차 기여 1순위"로 골랐습니다. 그런데 복원오차 기여가 큰 것과 사람 눈에 크게 변해 보이는 것이 항상 일치하지 않습니다. 표준화·로그를 거친 좌표계에서의 기여도라, 원래 값의 체감 변화와 어긋날 수 있습니다.

해결. 대표 지표를 "원래 값 기준 변화 폭이 가장 큰 것"으로 바꿨습니다. _mag로 증가는 배수, 감소는 역수를 잡아 가장 크게 변한 지표를 대표로 세웁니다(위 _anomaly_detail_line 코드). 룰베이스 항목이 "지표명: 큰 변화"로 읽히는 것과 같은 직관을 ML 항목에도 줬습니다. 복원오차 1순위 정보는 영향 지표 목록 자체(Top3 선정)에는 그대로 쓰되, 표시 순서만 변화 폭 기준으로 바꾼 겁니다.

카테고리 동점 시 비결정 버그

증상. ML 진단의 대표 카테고리가 실행할 때마다 달라질 수 있는 자리가 있었습니다. 영향 지표가 io 1개, cpu 1개처럼 카테고리가 동점이면, 어떤 카테고리로 진단되느냐가 들쭉날쭉했습니다.

원인. 처음엔 대표 카테고리를 max(set(cats), ...) 비슷하게 골랐는데, set은 순서가 보장되지 않습니다. 동점일 때 어느 게 먼저 나오는지가 실행 환경에 따라 달라져, 같은 입력에 다른 진단이 나올 수 있었습니다. 매일 도는 리포트에서 이런 비결정성은 신뢰를 깎습니다.

해결. collections.Counter로 바꿨습니다.

from collections import Counter
top_cat = Counter(cats).most_common(1)[0][0] if cats else None

Counter에 넘기는 cats 리스트는 영향 지표 순서(복원오차 순서)대로 만들어집니다. most_common은 동점일 때 먼저 들어온 것을 유지하므로, 결과적으로 "복원오차 기여가 앞선 지표의 카테고리"가 대표가 됩니다. 동점이어도 항상 같은 결과가 나오고, 그 우선순위도 의미가 있습니다(더 크게 어긋난 쪽 우선).

변화 미미한 영향 지표를 평소 수준으로

증상. ML 줄에 "평소 0 → 피크 0" 같은 무의미한 변화가 찍혔습니다.

원인. 영향 지표 Top3는 복원오차 기여 순으로 뽑는데, 그중에는 복원에는 약간 기여했어도 실제 값은 평소와 거의 같은 지표가 섞일 수 있습니다. 평소 0이던 지표가 피크에도 0이면, 단위 포맷상 둘 다 "0"으로 찍혀 "평소 0 → 피크 0 ↑"처럼 변화가 있는 척 보입니다.

해결. _changed 판정을 넣었습니다(위 코드). 표시 포맷 결과가 같거나(_fmt_value로 찍었을 때 평소값과 피크값 문자열이 동일) 배수가 0.9~1.1 범위면 변화 없음으로 보고, 그 지표는 "평소 수준"이라고만 적습니다. 진짜 변한 지표만 "평소 X → 피크 Y"로 보여 주고, 안 변한 건 솔직하게 평소 수준이라고 말합니다. 거짓 변화로 DBA를 헷갈리게 하지 않으려는 처리입니다.

그래프 피크 시각이 선과 겹침

증상. 피크 시각 텍스트가 데이터 선이나 다른 요소와 겹쳐 안 읽혔습니다. 큰 수 y축은 1e6로 찍혀 직관적이지 않았습니다.

원인. 시각을 점 위에 그냥 텍스트로 얹으니 선 색과 글자 색이 겹쳤습니다. y축은 matplotlib 기본 지수 표기였습니다.

해결. 세 가지를 손봤습니다. 첫째, 피크 시각에 반투명 흰 배경 박스(bbox)를 깔아 선과 겹쳐도 읽히게 했습니다. 둘째, y축을 K·M 표기로 바꿔 3,154,251을 "3.2M"으로 보이게 했습니다. 셋째, 저장 DPI를 260으로 올려 메일 본문에서도 선명하게 했습니다. 그래프 텍스트는 영문·숫자만 써서 폰트 환경과 무관하게 깨지지 않게 했습니다.

정상일 오발동 0 확인

증상이라기보다 검증. 비지도 탐지에서 가장 걱정한 건 "정상인 날에도 매일 ML 이상이 뜨는 것"이었습니다. 95퍼센타일 점 임계만 쓰면 정상일에도 약 5%(하루 약 72분)가 점 임계를 넘기 때문입니다.

해결과 확인. 앞서 둔 하루 임계(daily_minutes_threshold, 정상일 일별 초과 분 수의 95퍼센타일)가 이 안전장치입니다. 학습 데이터로 측정한 정상 변동 폭을 넘을 때만 발동하게 했고, 정상일 데이터를 다시 돌려 ML 오발동이 나지 않는지 확인했습니다. "이상 없는 날도 정상 리포트를 보낸다"가 요구사항이라, 정상일에 가짜 이상이 끼면 리포트 신뢰가 무너집니다. 점 임계 한 겹이 아니라 하루 임계까지 두 겹으로 거른 게 여기서 효과를 봤습니다.

---

정리하며

탐지를 두 갈래로 짠 게 이 시스템의 뼈대입니다.

룰베이스는 사람이 명시한 규칙입니다. 임계 초과, 전일 대비 2배 급증, % 지표 20%p 패턴 상승. 설명이 명확하고 DBA가 임계만 조정하면 됩니다. 다만 미리 적은 것만 잡습니다.

비지도(PCA 복원오차) 는 "평소와 다름"을 잡습니다. 한 달치 정상 패턴을 학습해, 단일 임계를 안 넘어도 여러 지표가 동시에 평소 조합에서 벗어나는 조합 이상을 감지합니다. 대신 "무엇이 왜 이상한지"는 영향 지표 산출(복원오차 기여 Top3 + 평소 대비 피크·방향·배수)로 따로 설명해 줘야 합니다.

둘은 상호보완입니다. 룰베이스가 놓치는 조합 이상을 비지도가 줍고, 비지도가 못 주는 명확한 설명을 룰베이스가 줍니다. 그리고 진단 리포트가 이 둘을 한 카드로 합쳐, 룰베이스는 기본색·ML은 보라색으로 출처를 구분하되 ① 감지된 이상 ② 원인 추정 ③ 권장 조치 ④ 종합이라는 같은 틀에 녹입니다.

POC 단계라 진단은 템플릿·상관 규칙으로만 만듭니다. 다음 단계로는 두 갈래를 생각하고 있습니다. 하나는 PCA를 오토인코더로 바꿔 더 복잡한 비선형 패턴까지 잡는 것, 다른 하나는 진단의 원인 추정·권장 조치에 LLM과 이벤트(쿼리·세션) 데이터를 붙여 "이 시각에 이 쿼리가 인덱스를 못 타고 풀 스캔했다" 수준까지 구체화하는 것입니다. 지금 골격(복원오차·영향 지표·진단 카드)을 그 자리에 그대로 끼울 수 있게 인터페이스를 맞춰 둔 게, 두 갈래 모두에서 도움이 될 거라고 봅니다.