DB 일일 점검 자동화 POC (1) 데이터 수집

핵심 요약

• 한 대기업 제조사에서 DBA가 매일 아침 30분~1시간씩 손으로 하던 DB 점검을, AI가 자동 분석해 매일 오전 8시 HTML 메일로 보내주는 시스템을 2주 POC로 만들고 있습니다.
• 대상은 AWS RDS 위의 Oracle·MySQL·PostgreSQL 3종. 데이터는 모니터링 솔루션(DPM)이 1분 단위로 집계한 시계열 지표입니다.
• 전체는 main.py가 collector → detector → reporter → mailer 4개 모듈을 순서대로 실행하는 파이프라인으로 구성되며, 이번 글은 그 첫 단계인 데이터 수집(collector.py)을 다룹니다.
• collector의 핵심 설계 목표는 "나중에 데이터 출처가 엑셀에서 실제 API로 바뀌어도, 이 파일 하나만 고치면 되게" 격리하는 것입니다.
• 실제 코드와 실제 데이터 샘플을 상세히 확인하며 작업한 내용을 정리했습니다. 

1. 어떤 프로젝트인가

데이터베이스 관리자(DBA)는 매일 아침 출근하면 비슷한 일을 반복합니다. CPU 사용률이 밤사이 튀지 않았는지, 세션이 과하게 몰리지 않았는지, 슬로우 쿼리가 늘지 않았는지, 테이블스페이스나 디스크가 차오르지 않았는지를 모니터링 화면을 띄워 놓고 눈으로 확인합니다. DB가 한두 대면 금방이지만, 운영 DB가 여러 종류·여러 대로 늘어나면 이 "아침 점검"에만 30분에서 1시간이 들어갑니다.

이 작업의 특징은 대부분의 날은 아무 이상이 없다는 점입니다. 그런데도 사람이 매일 같은 화면을 같은 순서로 확인해야 합니다. 이상이 있는 날을 놓치지 않기 위해, 이상이 없는 364일도 똑같이 봐야 하는 구조입니다. 자동화하기 딱 좋은 일입니다.

매일 오전 7시 50분에 자동으로 돌아서, 어제 하루치 DB 지표를 분석하고, 오전 8시에 "오늘의 DB 상태 리포트"를 HTML 이메일로 보낸다. 이상이 있으면 어떤 지표가 왜 문제인지 짚어 주고, 이상이 없는 날도 "정상입니다" 리포트를 보낸다.

조건도 몇 가지 있습니다.

• 온프레미스 납품: 고객사 서버에 파이썬 zip으로 올려서 cron으로 돌립니다. 도커 없이, 용량은 가볍게(1MB 이하).
• 정확도 85~90% 이상: 단순 임계값 초과뿐 아니라, 임계값은 안 넘었지만 평소와 다른 패턴 이상도 잡아야 합니다.
• DB별로 다른 기준: Oracle·MySQL·PostgreSQL은 봐야 하는 지표도, 위험으로 보는 기준값도 다릅니다.

대상 DB는 AWS RDS(ap-northeast-2)에 있는 Oracle 1대, MySQL(Aurora) 1대, PostgreSQL(Aurora) 1대, 총 3종입니다.

2. 전체 구성 — 4단계 파이프라인

시스템은 main.py가 collector·detector·reporter·mailer 4개 모듈을 한 방향으로 실행하는 구조입니다. 데이터가 왼쪽에서 들어와 오른쪽으로 흐르면서 점점 "사람이 읽을 수 있는 리포트"로 변합니다.

main.py  →  collector  →  detector  →  reporter  →  mailer
            (데이터수집)   (이상탐지)    (HTML+그래프)  (메일발송)
              ↓ DbMetrics    ↓ 이상결과     ↓ HTML+PNG

모듈	책임	입력 → 출력	자바로 치면
config.yaml	DB별 임계값·지표·설정	—	application.yml
collector.py	데이터 수집(출처 격리)	(config, 날짜) → DbMetrics	Repository / DAO
detector.py	룰베이스 + ML 이상 탐지	DbMetrics → 이상 결과	Service
reporter.py	HTML 리포트 + 그래프	이상 결과 → HTML + PNG	View / Template
mailer.py	SMTP 메일 발송	HTML + PNG → 메일	메일 컴포넌트

각 모듈을 한 줄로 정리하면 이렇습니다.

• main.py — 전체 파이프라인의 진입점입니다. 정해진 순서대로 나머지 모듈을 호출해 하루 한 번 실행합니다. 스프링으로 치면 배치 잡의 실행 진입점에 해당합니다.
• config.yaml — DB별 임계값·감시 지표·발송 시각 같은 설정을 모아 둔 파일입니다. 코드를 건드리지 않고 기준값만 바꿀 수 있도록 분리했습니다.
• collector.py — 지표 데이터를 모아 표준 형태(DbMetrics)로 돌려줍니다. 데이터 출처(엑셀/실제 API)를 감추는 것이 핵심이며, 이번 글의 주제입니다.
• detector.py — 수집된 데이터를 받아 임계값 초과·전날 대비 급증·패턴 이상을 판정합니다. 룰베이스와 머신러닝 두 방식을 함께 씁니다.
• reporter.py — 판정 결과를 사람이 읽을 수 있는 HTML 리포트와 추이 그래프(PNG)로 만듭니다.
• mailer.py — 완성된 리포트를 SMTP로 메일 발송합니다. 매일 오전 8시에 도착하도록 cron이 7시 50분에 실행합니다.

이렇게 나눈 이유는 단순합니다. 각 모듈이 자기 일만 하게 만들면, 한 부분을 고칠 때 다른 부분이 흔들리지 않습니다. 특히 이번 프로젝트에는 collector만 따로 떼어 둬야 하는 강한 이유가 하나 있는데, 이건 4장에서 자세히 다룹니다.

스프링을 해보신 분이라면 익숙한 그림입니다. Controller(main) → Service(detector) → Repository(collector)로 책임이 나뉘고, 각 계층은 바로 옆 계층하고만 약속된 형태로 대화합니다. 그 "약속된 형태"가 여기서는 DbMetrics라는 객체입니다.

3. 데이터는 어떻게 생겼나

코드를 보기 전에 재료를 먼저 봐야 합니다. collector가 읽어 들이는 원본 데이터가 어떻게 생겼는지 모르면, collector 코드가 왜 그렇게 생겼는지 이해할 수 없습니다.

데이터 출처는 모니터링 솔루션(DPM)입니다. 이 솔루션은 시계열 DB에 5초마다 쌓인 원시 데이터를 1분 단위로 집계해서 제공합니다. 그런데 POC 단계에서는 보안상 실제 API에 붙을 수 없어서, 고객사가 한 달치 1분 단위 지표를 엑셀로 추출해 전달해 줬습니다.

엑셀 파일의 규칙은 이렇습니다.

• 파일 1개 = 지표 1개. 파일명은 db_{db}_{metric}.xlsx 형식입니다. 예: db_oracle_cpu_usage.xlsx, db_oracle_freestoragespace.xlsx.
• DB별 파일 수: Oracle 33개, MySQL 27개, PostgreSQL 32개.

실제 db_oracle_cpu_usage.xlsx를 열어 본 모습입니다(앞 5행, 식별자는 가렸습니다).

     agg_collect_time      target_id        data_id              last_value  max_value  count_data
0  2026-06-03T22:00:00   176d…c8371   db_oracle_cpu_usage     8.235000      8.235          12
1  2026-06-03T22:01:00   176d…c8371   db_oracle_cpu_usage     8.066667      8.066          12
2  2026-06-03T22:02:00   176d…c8371   db_oracle_cpu_usage     9.993333      9.993          12
3  2026-06-03T22:03:00   176d…c8371   db_oracle_cpu_usage    15.608333     15.608          12
4  2026-06-03T22:04:00   176d…c8371   db_oracle_cpu_usage     9.401667      9.401          12

컬럼을 하나씩 보겠습니다.

컬럼	뜻
agg_collect_time	집계 시각. ISO 문자열(2026-06-03T22:00:00)이고 1분 간격입니다.
target_id	인스턴스 식별자(UUID). 어느 DB의 데이터인지 가리킵니다.
data_id	지표 식별자. 파일명과 같은 값입니다.
last_value	그 1분 구간의 대표값(평균). 우리가 실제로 쓰는 값입니다.
sum / max / min / first_value	1분 동안의 합·최대·최소·첫 값.
count_data	그 1분에 들어온 원시 데이터 개수. 12 = 5초 × 12 = 60초.

여기서 중요한 함정 세 가지가 있고, 이게 collector 코드의 모양을 결정합니다.

1. 모든 값이 문자열로 들어옵니다. 숫자처럼 보여도 "8.235" 같은 텍스트입니다. 그대로 비교하면 "9" > "15"가 참이 되는 사고가 납니다. 반드시 float으로 변환해야 합니다.
2. 시간순 정렬이 보장되지 않습니다. 행이 뒤죽박죽일 수 있어서, 날짜로 자르기 전에 시간으로 정렬해야 합니다.
3. 하루는 1,440행입니다. 24시간 × 60분. "어제 하루치"를 뽑으면 정상적으로 1,440행이 나와야 한다는 검증 기준이 여기서 나옵니다.

그리고 한 가지 더. 이 엑셀은 POC용 임시 데이터일 뿐이고, 현장에서는 실제 API로 갈아끼울 예정입니다. 이 사실이 다음 장의 설계로 이어집니다.

4. collector.py가 하는 일

한 줄 정의

collector는 "점검 대상일(보통 어제)과 비교용 전날(그제)의 1분 지표 시계열을, DB별로 모아 표준 형태(DbMetrics)로 돌려주는" 모듈입니다.

설계의 핵심 — "이 파일 하나만 고치면 되게"

이 프로젝트에서 가장 신경 쓴 설계 결정은 collector에 있습니다.

POC는 엑셀로 개발하지만, 마지막 주에 현장에 가서 실제 DPM API로 데이터 출처를 교체해야 합니다. 이때 detector·reporter·mailer까지 줄줄이 고쳐야 한다면, 교체는 위험한 대수술이 됩니다. 그래서 처음부터 규칙을 정했습니다.

데이터 출처가 바뀌어도 collector.py 한 파일만 수정한다. 나머지 모듈은 손대지 않는다.

이걸 가능하게 하는 게 인터페이스 + 구현체 + 팩토리 구조입니다. 자바/스프링 하셨으면 바로 익숙한 패턴입니다.

파이썬 (collector.py)	자바/스프링 대응
MetricSource (추상 클래스, ABC)	interface MetricSource
ExcelMetricSource (엑셀 구현)	class ExcelMetricSource implements MetricSource
DpmApiMetricSource (API 구현)	class DpmApiMetricSource implements MetricSource
get_metric_source() 팩토리	설정 보고 빈 골라 주입하는 스프링 컨테이너

config.yaml의 datasource.type 값이 excel이면 엑셀 구현체가, dpm_api면 API 구현체가 선택됩니다. 하위 모듈은 자기가 받은 데이터가 엑셀에서 왔는지 API에서 왔는지 전혀 모릅니다. 그저 DbMetrics라는 표준 상자만 받습니다.

# config.yaml — 이 한 줄만 바꾸면 출처가 교체된다
datasource:
  type: "excel"          # excel | dpm_api
  excel:
    data_dir: "data"
  dpm_api:               # 현장 연동 시 채움
    base_url: ""
    token: ""

표준 출력 — DbMetrics

모든 모듈이 공유하는 "약속된 형태"입니다. 스프링의 DTO/VO라고 보면 됩니다.

@dataclass
class DbMetrics:
    """한 DB(인스턴스)의 점검 데이터 묶음."""
    db_name: str                 # 'mysql' | 'oracle' | 'postgresql'
    instance: str                # 인스턴스명
    target_date: date            # 점검 대상일
    today: pd.DataFrame          # 대상일 1분 시계열
    prev_day: pd.DataFrame       # 전일(급증/패턴 비교용) 1분 시계열
    unmapped: List[str]          # 데이터 파일이 없어 제외된 지표

today와 prev_day는 DataFrame입니다. 파이썬 데이터 분석의 표 자료구조인데, 엑셀 시트를 코드로 다룬다고 생각하면 됩니다. 행 인덱스는 1분 간격 시각, 열은 지표 이름, 칸은 그 시각의 값입니다.

                     (OS) Cpu Usage   Active Session   Free Storage Space
2026-06-14 00:00:00       8.23             12.0            5.84e+11
2026-06-14 00:01:00       8.07             11.0            5.84e+11
2026-06-14 00:02:00       9.99             14.0            5.84e+11
...                        ...              ...              ...
(1,440행)

prev_day를 따로 들고 다니는 이유는, 이상 탐지에서 "전날 대비 2배 급증", "평소 대비 20%p 상승" 같은 비교를 하기 때문입니다. 그래서 collector가 어제와 그제를 함께 챙겨 줍니다.

5. 핵심 코드 발췌

(1) 엑셀 1개 → 시계열 한 줄로

가장 밑바닥에서 엑셀 파일 하나를 읽어 (시각, 값) 시계열로 바꾸는 함수입니다. 3장에서 본 함정 세 가지(문자열·정렬·중복)가 여기서 모두 처리됩니다.

@staticmethod
def _read_series(path: str):
    """엑셀 1파일 → (timestamp index, float value) Series. 시계열 아니면 None."""
    df = pd.read_excel(path, sheet_name=0, engine="calamine")
    if "agg_collect_time" not in df.columns or "last_value" not in df.columns:
        return None  # 시계열 메트릭 파일이 아님 → skip
    idx = pd.to_datetime(df["agg_collect_time"], errors="coerce")  # 문자열 → 시각
    val = pd.to_numeric(df["last_value"], errors="coerce")          # 문자열 → float
    s = pd.Series(val.values, index=idx).dropna()
    return s[~s.index.duplicated(keep="last")].sort_index()         # 중복 제거 + 정렬

한 줄씩 보겠습니다.

• pd.read_excel(..., engine="calamine") — 엑셀을 읽습니다. calamine이라는 엔진을 콕 집어 쓰는데, 여기엔 사연이 있습니다(아래 6장).
• 컬럼 체크 — agg_collect_time이나 last_value가 없으면 시계열 파일이 아니라고 보고 None을 돌려 건너뜁니다. 일부 파일은 시계열이 아니라 쿼리 목록이라 형태가 다릅니다.
• pd.to_datetime(..., errors="coerce") — 문자열 시각을 진짜 시각 타입으로 바꿉니다. errors="coerce"는 "변환 실패한 값은 에러 내지 말고 빈 값(NaT)으로 둬라"는 뜻입니다.
• pd.to_numeric(...) — 문자열 숫자를 float으로. 함정 ①을 여기서 해결합니다.
• duplicated(keep="last") + sort_index() — 같은 시각이 중복되면 마지막 것만 남기고, 시간순으로 정렬합니다. 함정 ②를 해결합니다.

(2) 한 달치 전체를 한 DataFrame으로 병합

지표별 시계열을 모아 하나의 표로 합칩니다. 한 번 읽어 캐시해 두고, 날짜별 조회는 잘라 쓰기만 합니다.

def _load_frame(self) -> pd.DataFrame:
    if self._frame is not None:
        return self._frame                 # 이미 읽었으면 캐시 반환
    series = {}
    for source, path in self._resolve_files().items():
        s = self._read_series(path)
        if s is None:
            self._unmapped.append(source)  # 시계열 아님 → 제외 목록에
            continue
        series[source] = s
    self._frame = pd.DataFrame(series).sort_index()
    return self._frame

(3) 날짜 하루치 잘라 내기

여기가 의외로 깔끔합니다. 시각으로 정렬된 표라서, 날짜 문자열로 부분 인덱싱만 하면 하루가 잘립니다.

def fetch(self, target_date: date) -> pd.DataFrame:
    frame = self._load_frame()
    key = target_date.isoformat()          # 'YYYY-MM-DD'
    try:
        day = frame.loc[key]               # 그 날짜 1분 시계열만 슬라이스
    except KeyError:
        return frame.iloc[0:0]             # 데이터 없는 날 → 빈 프레임
    return day

(4) DB 전체를 모아 표준 형태로

마지막으로 모든 DB를 돌며 어제·그제를 챙겨 DbMetrics로 포장합니다. 이게 collector의 진입점입니다.

def collect(config: dict, target_date: date) -> Dict[str, DbMetrics]:
    prev_date = target_date - timedelta(days=1)
    result = {}
    for db_name in config["databases"]:
        source = get_metric_source(db_name, config)   # 팩토리가 출처 선택
        today = source.fetch(target_date)
        prev_day = source.fetch(prev_date)
        result[db_name] = DbMetrics(
            db_name=db_name, instance=source.instance,
            target_date=target_date, today=today, prev_day=prev_day,
            unmapped=source.unmapped_metrics(),
        )
    return result

지표 이름 ↔ 파일명 매칭 문제

작지만 성가신 문제가 하나 있었습니다. config.yaml에 쓴 지표 이름(예: (OS) Cpu Usage)과 실제 파일명(db_oracle_cpu_usage.xlsx)이 딱 맞아떨어지지 않습니다. 공백·괄호·대소문자가 제각각입니다.

그래서 3단계로 매칭합니다.

1. 자동 정규화 매칭: 영숫자만 남기고 소문자로 바꿔((OS) Cpu Usage → oscpuusage) 부분 일치로 찾습니다.
2. 예외 보정 표(EXPLICIT_FILE_MAP): 자동으로 안 되는 것만 직접 지정합니다. 예를 들어 PostgreSQL의 Free Memory는 실제 파일이 freeablememory라 자동 매칭이 안 됩니다.
3. 미제공 제외 목록(EXCLUDE_METRICS): config엔 있지만 데이터가 안 온 지표는 아예 건너뜁니다. 이걸 안 하면 비슷한 이름의 엉뚱한 파일에 잘못 붙는 사고가 납니다(예: Host Read IOPS가 readiops에 오매칭).

EXPLICIT_FILE_MAP = {
    "postgresql": {
        "Free Memory": "db_postgresql_freeablememory.xlsx",  # freememory != freeablememory
    },
}
EXCLUDE_METRICS = {
    "oracle": {"Host Read IOPS", "Host Write IOPS", ...},
    "mysql": {"Volume Bytes Used Max", "Host Disk Used"},
    "postgresql": {"Postgresql Slow Query Monitor"},
}

"자동으로 최대한 처리하고, 안 되는 것만 손으로 예외 처리한다"는 방식입니다. 데이터가 추가되거나 이름이 바뀌어도 표 몇 줄만 고치면 됩니다.

6. 한 줄짜리 트러블슈팅 — 왜 calamine인가

코드에서 engine="calamine"을 콕 집어 쓴 이유가 있습니다. 처음엔 파이썬에서 가장 흔한 엑셀 엔진인 openpyxl을 썼는데, 이 엑셀 파일들이 내부 메타데이터(dimension 정보)가 비어 있어서, openpyxl이 행·열 범위를 제대로 못 읽었습니다. 게다가 한 달치 33개 파일을 읽는 데 시간도 오래 걸렸습니다.

calamine(러스트 기반 엑셀 리더) 엔진으로 바꾸자 메타데이터와 무관하게 값을 정확히 읽었고, 읽기 시간도 약 661초에서 73초로 크게 줄었습니다. 이 트러블슈팅 자체는 내용이 많아 다음 글에서 따로 정리하겠습니다.

7. 지금까지 한 것 / 남은 것

완료

• 프로젝트 설계·일정 정리, config.yaml(DB별 임계값·지표 2단 구조) 작성.
• 실데이터 한 달치 수령·정리(Oracle 33 / MySQL 27 / PostgreSQL 32 파일).
• collector.py 완성: 인터페이스·구현체·팩토리 구조, 3단계 지표 매칭, 문자열·정렬 처리, 날짜 슬라이싱. 3종 DB 모두 하루 1,440행이 정상 출력되는 것까지 검증.

다음

• detector.py — 룰베이스(임계값 초과 / 전날 대비 급증 / 패턴 이상)와 비지도학습 이상 탐지.
• reporter.py / mailer.py — HTML 리포트와 그래프 생성, SMTP 메일 발송.
• 마지막 주 현장에서 엑셀 → 실제 DPM API 교체. 이때 4장의 격리 설계가 효과를 봅니다. collector.py의 API 구현체 하나만 채우면 됩니다.

8. 정리하며

이번 글에서 가장 강조하고 싶은 한 가지는 격리 설계입니다. "지금 당장은 엑셀이지만 곧 API로 바뀐다"는 사실을 알고 있을 때, 그 변화의 충격을 한 파일 안에 가두는 것. 인터페이스로 경계를 긋고, 하위 모듈은 표준 형태(DbMetrics)에만 의존하게 만드는 것. 이건 스프링에서 Repository 인터페이스 뒤에 JPA든 MyBatis든 숨기는 것과 정확히 같은 발상입니다.

다음 글에서는 이 데이터를 받아 "이게 정상인가 이상인가"를 판정하는 detector를 다루겠습니다. 임계값만으로는 못 잡는 패턴 이상을 어떻게 잡을지가 핵심입니다.