공부저장소

📘 SyncMon

1. 배경

모니터링 시스템은 특성상 조회보다 적재가 훨씬 자주 발생합니다.

특히 SyncMon처럼 프로세스 상태, 체크포인트, 큐 적재 시간, 지연 시간 같은 운영성 데이터를 지속적으로 수집하는 구조에서는 시간이 지날수록 테이블이 빠르게 비대해집니다.

초기에는 단순 조회 쿼리로도 충분히 대응할 수 있습니다.

문제는 데이터가 수억 건 단위로 쌓이기 시작한 이후입니다. 이 시점부터는 “조건이 있으니 알아서 빨리 찾겠지” 수준의 기대가 통하지 않습니다. 조회 패턴이 명확하지 않거나, 시간 조건과 최신 1건 조회가 반복되는 구조에서는 작은 쿼리 하나가 전체 서비스 응답 시간을 끌어내릴 수 있습니다.

SyncMon에서도 비슷한 문제가 있었습니다.

특정 PROCESS_ID에 대해 최근 하루, 일주일 전 같은 특정 시간 범위 안에서 가장 마지막 데이터 1건을 조회하는 기능이 반복적으로 사용되고 있었는데, 테이블 건수가 5억 건을 넘어가면서 단순한 조건 조회 방식만으로는 안정적인 응답 시간을 보장하기 어려워졌습니다.

2. 문제였던 쿼리 패턴

실제 조회 형태는 대략 아래와 같았습니다.

SELECT*FROM (
SELECT A.PROCESS_ID
         , A.SEQ
         , LAG_AT_CHKPT
         , TIME_SINCE_CHKPT
         , TO_CHAR(QUEUE_TIME,'yyyy-mm-dd hh24:mi:ss')AS QUEUE_TIME
         , TO_CHAR(QUEUE_TIME,'yyyy-mm-dd hh24:mi:ss')ASTIME
         , ROW_NUMBER() OVER (ORDERBY JOB_TIMEDESC)AS RNUM
FROM TB_DISP_PROCESS A
WHERE A.PROCESS_ID= :processId
AND A.JOB_TIMEBETWEEN :from_timeAND :to_time
) SUBQUERY
WHERE RNUM=1

겉으로 보면 조건도 단순하고, 결국 1건만 가져오니 가벼워 보일 수 있습니다.

하지만 데이터가 커지면 이 쿼리는 생각보다 비싸집니다.

이유는 분명합니다.

첫째, 조건 대상이 되는 테이블 자체가 너무 큽니다.

5억 건 이상 적재된 테이블에서 적절한 접근 경로가 없으면, 단순한 범위 조회도 결국 많은 블록을 읽게 됩니다.

둘째, 이 쿼리는 단순 조회가 아니라 정렬 기준이 포함된 최신 1건 조회입니다.

즉, PROCESS_ID와 JOB_TIME 조건으로 대상을 좁힌 뒤에도, 그 안에서 다시 JOB_TIME DESC 기준으로 가장 마지막 데이터를 찾아야 합니다.

셋째, 모니터링 시스템에서는 이런 조회가 일회성이 아닙니다.

프로세스별 최근 상태, 특정 시점 이전 상태, 전일 동일 시각 상태 같은 패턴이 반복적으로 호출됩니다. 즉, 한 번 느린 게 아니라 자주 느린 구조가 됩니다.

결국 이 문제는 단순 쿼리 튜닝보다는, 데이터 접근 구조 자체를 바꾸지 않으면 계속 되풀이되는 문제였습니다.

3. 왜 인덱스만으로는 부족하고, 파티션만으로도 부족했는가

이런 상황에서 흔히 나오는 접근은 둘 중 하나입니다.

  • 인덱스를 추가한다.
  • 날짜 기준으로 파티션을 나눈다.

둘 다 맞는 방향이지만, 둘 중 하나만으로는 부족한 경우가 많습니다.

3.1 인덱스의 역할

이 쿼리에서 가장 중요한 조건은 사실상 두 개입니다.

  • PROCESS_ID = ?
  • JOB_TIME BETWEEN ? AND ?

그리고 결과는 그 범위 안에서 가장 최신 1건입니다.

즉, 이 조회 패턴에 맞는 인덱스는 단일 인덱스 두 개가 아니라 복합 인덱스여야 합니다.

예를 들어 아래와 같은 인덱스입니다.

(PROCESS_ID, JOB_TIMEDESC)

이 인덱스의 의미는 분명합니다.

  • 먼저 PROCESS_ID로 대상을 좁히고
  • 그 안에서 JOB_TIME 범위를 타고
  • 최신 순서대로 바로 접근한다

즉, 정렬과 필터링을 따로 하는 것이 아니라, 조회 패턴 자체를 인덱스 구조에 반영하는 것입니다.

다만 인덱스만으로 모든 문제가 해결되지는 않습니다.

테이블 전체가 너무 크고, 시간 범위가 넓고, 적재량이 계속 증가하면 인덱스 역시 커지고 유지 비용도 커집니다. 특히 오래된 데이터까지 같은 구조 안에 계속 쌓이면, 필요한 데이터는 최근 일부인데도 전체 구조가 비대해지는 문제가 생깁니다.

3.2 파티션의 역할

반대로 파티션은 인덱스처럼 “어떤 row를 빠르게 찾는 도구”라기보다, 애초에 보지 않아도 되는 데이터 구간을 버리는 도구에 가깝습니다.

JOB_TIME 기준으로 날짜별 파티션을 구성하면, 예를 들어 최근 1일 데이터 조회 시 전체 5억 건을 보는 것이 아니라 해당 날짜 파티션만 접근하게 만들 수 있습니다. 이것이 partition pruning의 핵심입니다.

하지만 파티션만 있다고 끝나지는 않습니다.

하루 파티션 안에도 데이터가 많다면, 해당 파티션 내부에서 또 원하는 row를 빨리 찾아야 합니다. 즉, 파티션이 “검색 범위를 줄이는 역할”이라면, 인덱스는 “줄여진 범위 안에서 바로 찾는 역할”을 해야 합니다.

그래서 이 문제는 결국 인덱스와 파티션을 같이 가져가야 맞는 문제였습니다.

4. SyncMon에서 적용한 방향

SyncMon에서는 이 조회 패턴이 반복된다는 점을 먼저 기준으로 삼았습니다.

즉, “특정 프로세스의 특정 시간 범위 내 최신 상태 1건 조회”가 핵심 패턴이라면, 그 패턴에 맞춰 저장 구조를 정리해야 했습니다.

적용 방향은 두 가지였습니다.

4.1 PROCESS_ID + JOB_TIME 복합 인덱스 적용

우선 접근 경로를 명확하게 만들기 위해 PROCESS_ID, JOB_TIME 기준 복합 인덱스를 구성했습니다.

CREATE INDEX IDX_TB_DISP_PROCESS_01
ON TB_DISP_PROCESS (PROCESS_ID, JOB_TIMEDESC);

핵심은 인덱스를 많이 만드는 것이 아니라, 조회 조건과 정렬 방향을 반영한 인덱스를 만드는 것입니다.

이렇게 하면 옵티마이저 입장에서는

  • 특정 프로세스만 타고 들어간 뒤
  • 원하는 시간 범위만 스캔하고
  • 최신 데이터부터 확인할 수 있으므로

불필요한 정렬 비용과 넓은 범위 탐색을 줄이기 쉬워집니다.

4.2 JOB_TIME 기준 날짜 파티션 적용

두 번째는 JOB_TIME 기준 파티션입니다.

모니터링 데이터는 대부분 시간축 기반으로 쌓이고, 조회 역시 최근 하루, 특정 과거 시점, 최근 N일 같은 패턴이 많습니다. 따라서 시간 컬럼을 파티션 키로 잡는 것이 가장 자연스러웠습니다.

예를 들어 일 단위 파티션을 구성하면,

  • 최근 1일 조회는 1개 파티션
  • 지난주 동일 시점 조회는 해당 일자 파티션
  • 전월 동일 시점 조회는 해당 월의 특정 일자 파티션

위주로 접근할 수 있어, 전체 테이블을 기준으로 접근하는 것보다 훨씬 안정적인 실행계획을 기대할 수 있습니다.

즉, SyncMon에서는 파티션을 통해 읽지 않아도 되는 오래된 데이터 구간을 먼저 잘라내고,

그 안에서 복합 인덱스를 통해 필요한 최신 1건을 빠르게 찾도록 구조를 바꿨습니다.

5. 실제로 중요한 건 “1건만 조회한다”가 아니라 “1건에 도달하는 경로”다

이런 조회에서 흔히 하는 오해가 있습니다.

“결과가 1건이면 빨라야 하는 것 아닌가?”

문제는 결과 row 수가 아니라, 그 1건에 도달하기까지 얼마나 많은 데이터를 읽어야 하느냐입니다.

5억 건 테이블에서 최신 1건을 조회하더라도,

  • 접근 경로가 없으면 많은 블록을 읽어야 하고
  • 시간 범위를 못 줄이면 오래된 데이터까지 훑게 되고
  • 정렬 최적화가 안 되면 최신 1건을 찾기 위해 후보군을 넓게 처리해야 합니다

즉, 중요한 것은 “1건 반환”이 아니라 “1건까지 얼마나 적게 읽고 도달하느냐”입니다.

이번 개선은 바로 이 지점을 손댄 작업이었습니다.

6. 쿼리 구조도 함께 보는 것이 맞다

인덱스와 파티션을 적용하더라도, 쿼리 구조 자체가 접근 경로를 살리지 못하면 효과가 제한됩니다.

현재 쿼리는 ROW_NUMBER() OVER (ORDER BY JOB_TIME DESC)를 사용하고 있는데, 목적이 “최신 1건”이라면 아래처럼 더 직접적으로 표현하는 편이 유리한 경우가 많습니다.

SELECT A.PROCESS_ID
     , A.SEQ
     , A.LAG_AT_CHKPT
     , A.TIME_SINCE_CHKPT
     , TO_CHAR(A.QUEUE_TIME,'yyyy-mm-dd hh24:mi:ss')AS QUEUE_TIME
     , TO_CHAR(A.QUEUE_TIME,'yyyy-mm-dd hh24:mi:ss')ASTIME
FROM TB_DISP_PROCESS A
WHERE A.PROCESS_ID= :processId
AND A.JOB_TIME>= :from_time
AND A.JOB_TIME< :to_time
ORDERBY A.JOB_TIMEDESC
FETCHFIRST1ROWONLY

이 방식의 장점은 분명합니다.

  • 목적이 최신 1건 조회라는 점이 더 직접적으로 드러나고
  • 정렬 후 상위 1건만 가져오는 패턴이라
  • 인덱스와 stopkey 최적화를 기대하기 쉬워집니다

물론 실제 적용은 DB 버전, 옵티마이저 특성, 실행계획 결과를 보고 판단해야 합니다.

핵심은 “인덱스를 만들었다”로 끝내는 것이 아니라, 쿼리도 그 인덱스를 잘 타도록 써야 한다는 점입니다.

7. 날짜별 파티션이 항상 정답은 아니다

여기서 한 가지는 분명히 짚고 가야 합니다.

JOB_TIME 기준 일 단위 파티션은 이 케이스에서 꽤 자연스러운 선택이지만, 날짜별 파티션이 항상 정답은 아닙니다.

왜냐하면 파티션도 결국 운영 비용이 있기 때문입니다.

  • 파티션 수가 너무 많아지면 관리 부담이 커집니다
  • 로컬 인덱스 관리 전략도 함께 봐야 합니다
  • 보관 주기와 삭제 정책이 없으면 파티션만 쪼개고 효과는 반감될 수 있습니다
  • 조회 패턴이 날짜 기준이 아니라 다른 키 기준이라면 partition pruning 효과가 약할 수 있습니다

즉, 파티션은 “대용량이니까 무조건”이 아니라,

데이터 적재 패턴과 조회 패턴이 시간축 중심일 때 가장 큰 효과를 냅니다.

SyncMon은 모니터링 데이터라는 특성상 시간축 조회가 많고, 오래된 데이터를 운영적으로 분리할 이유도 분명했기 때문에 파티션 전략이 잘 맞았습니다.

8. 운영 관점에서 얻은 이점

이번 작업의 효과는 단순히 특정 쿼리 하나가 빨라진 데 있지 않았습니다.

첫째, 최근 데이터 조회 응답 시간을 보다 안정적으로 관리할 수 있게 됐습니다.

모니터링 시스템에서는 평균 응답 시간보다 운영자가 체감하는 지연 변동폭이 더 중요할 때가 많습니다. 최신 상태 조회가 들쑥날쑥하면 운영 화면 신뢰도 자체가 떨어집니다.

둘째, 오래된 데이터와 최근 데이터를 같은 방식으로 다루지 않게 되었습니다.

이는 성능뿐 아니라 운영 정책 측면에서도 의미가 있습니다. 최근 데이터는 빠르게 조회하고, 오래된 데이터는 보관/아카이빙/삭제 정책에 따라 별도로 관리할 수 있는 기반이 생기기 때문입니다.

셋째, 대용량 적재가 계속되는 환경에서도 조회 성능 저하를 구조적으로 늦출 수 있게 되었습니다.

즉, 이번 개선은 단순 튜닝이 아니라 성장하는 테이블을 감당하기 위한 접근 구조 재설계에 가까웠습니다.

9. 정리

5억 건 이상 쌓이는 모니터링 테이블에서 “특정 프로세스의 특정 시간 범위 내 최신 1건”을 빠르게 조회하려면, 단순히 조건절을 잘 쓰는 것만으로는 부족합니다.

이 문제에서 중요한 것은 두 가지였습니다.

하나는 PROCESS_ID + JOB_TIME 복합 인덱스를 통해

조회 조건과 정렬 방향에 맞는 접근 경로를 만드는 것,

다른 하나는 JOB_TIME 기준 파티션을 통해

애초에 읽지 않아도 되는 데이터 구간을 잘라내는 것이었습니다.

즉, 인덱스는 “어디서 찾을지”를 줄이고,

파티션은 “어디를 아예 안 볼지”를 줄입니다.

SyncMon처럼 데이터가 계속 쌓이는 모니터링 시스템에서는 이 둘을 따로 보지 않고 함께 설계해야 합니다.

결국 중요한 것은 쿼리 한 줄을 고치는 것이 아니라, 데이터가 커져도 최신 상태 조회가 무너지지 않는 구조를 미리 만들어두는 것입니다.

참고