MySQL DB 접근 제어(ACL) 동기화 배치에서 원본 전체를 메모리에 올리던 Reader를 커서 스트리밍으로 바꾸고, 대상 행을 먼저 지운 뒤 재적재하던 흐름을 UPSERT 후 미수집 행 정리로 변경했다. 첫 청크의 OutOfMemoryError가 해당 DBMS의 ACL 전체 소실로 이어진 장애를 계기로 원인 분석부터 설계, 구현, 검증까지 담당한 작업이다.
개선 흐름
- 01 · 장애 분석전체 적재 Reader·삭제 선행 구조 확인
- 02 · 동기화 변경UPSERT·커서 스트리밍·미수집 행 정리
- 03 · 결과 확인복사 테이블 검증·운영 건수와 자원 사용량 확인
아래 탭에서 장애와 기존 적재 구조, UPSERT·Reader 개선, 검증 결과를 1→3단계로 볼 수 있다. 두 애니메이션의 A~E 등 가상 행은 각각 독립된 설명용 사례이며, 실제 ACL을 이어서 추적하는 데이터가 아니다.
Stage 1. 전체 적재와 삭제 선행 구조 분석
장애 관측: 첫 청크에서 OOM
일일 ACL 동기화 배치는 모니터링 DB의 접속 이력을 서버 매핑에 따라 펼쳐 ACL을 동기화한다. 장애 당시 처리 규모는 전일과 비슷했지만, 적재 Step의 첫 청크에서 java.lang.OutOfMemoryError: Java heap space가 발생했다.
| 관측 항목 | 당시 값 |
|---|---|
| 모니터링 DB 원본 | 약 600만 건 |
| 서버별로 펼친 ACL | 약 1,000만 건 |
| 실패 시점 | 첫 청크, READ 10,000 / WRITE 0 |
| JVM 힙 | 상주량 약 3GB, 최고 3.5~4GB |
원인: 청크 밖에서 전체 목록 보유
기존 Reader는 당일 원본 전체를 List로 받고 서버별로 펼친 결과도 모두 만든 뒤 IteratorItemReader에 넘겼다.
원본 약 600만 건 List
→ 서버별 ACL 약 1,000만 건으로 확장
→ IteratorItemReader가 Step 종료까지 보유
→ 10,000건 청크로 전달
청크는 한 번에 처리·커밋하는 건수를 제한했지만, Reader가 미리 만든 전체 목록의 메모리 사용량은 제한하지 못했다. JVM에 -Xmx를 지정하지 않아 실행 서버 메모리의 약 4분의 1인 약 4GB가 기본 힙 상한으로 적용되고 있었다. 평소에도 이 상한에 가까웠던 전체 적재 방식이 OOM의 직접적인 원인이었다.
장애 영향과 임시 조치
앞 Step에서 해당 DBMS의 기존 ACL을 모두 삭제한 뒤 적재 Step이 실패해, 해당 DBMS의 ACL은 0건이 되었다. ACL 조회·관리와 장기 미접속 화면, ACL 알림 메일 배치, Excel 다운로드에서 데이터가 누락됐다.
별도 서버에서 배치를 다시 실행해 데이터를 복구했다. 장애 이후 구조 개선 전까지는 ACL 동기화 Job을 실행할 때만 -Xmx6G를 지정해 힙 상한을 높였다. 이는 실행을 위한 임시 조치였고, 전체 적재와 삭제 선행 구조는 남아 있었다.
백업 테이블의 실제 역할
기존 동기화는 해당 DBMS의 ACL을 백업 테이블에 복사하고, 대상 행을 전체 삭제한 다음 원본을 다시 넣었다. 마지막으로 백업 테이블과 조인해 last_used_at을 보정했다.
기존: 대상 ACL 백업 → 대상 ACL 전체 삭제 → 원본 재적재 → 최종 사용 일시 보정
백업 테이블을 실패 복구용으로 해석하기 쉬웠지만, 코드를 추적했을 때 백업 데이터를 통째로 복원하는 로직은 없었다. 화면에서 ACL을 연장하면 다른 서비스가 대상 테이블의 last_used_at을 현재 시각으로 갱신한다. 백업은 다음 날 재적재 후 이 연장 값을 되살리는 스냅샷 역할이었다.
| 구조적 문제 | 확인한 영향 |
|---|---|
| 매일 전체 복사·삭제·재삽입 | 약 1,000만 건의 반복 I/O와 DB 복제 부하 |
| 삭제가 적재보다 앞섬 | 적재 실패 시 해당 DBMS의 ACL이 비는 경로 |
| 백업에 없는 신규 ACL 보정 | 서브쿼리 결과 NULL로 일부 컬럼이 덮임 |
| 매일 새로 INSERT | created_at이 매일 초기화 |
아래 애니메이션은 정상 경로의 백업·삭제·원본 전체 적재·보정과, 실패 경로의 원본 List→서버별 확장→첫 청크 OOM을 따로 보여 준다. 그림의 A~E와 블록 수는 설명용이며 실제 건수가 아니다.
Stage 2. UPSERT와 커서 스트리밍으로 동기화 변경
대상 ACL을 비우지 않은 상태에서 원본을 한 행씩 읽어 UPSERT하고, 적재가 끝난 뒤 이번 실행에 없는 행만 정리하도록 순서를 바꿨다.
Job 기준 시각 T 결정 → 원본 커서로 읽고 서버별 확장 → 청크 UPSERT
→ 커서 종료 → 0건 가드 → 미수집 ACL만 삭제
UPSERT로 연장 값과 최초 등록일 유지
대상 테이블에 이번 실행의 수집 여부를 표시하는 sync_at 컬럼을 추가했다. 수집된 ACL은 고유 키 기준으로 UPSERT하면서 sync_at = T로 표시한다. 기존 행의 created_at은 갱신하지 않는다.
| ACL 상태 | 적재 후 처리 |
|---|---|
| 기존 행이 다시 수집됨 | sync_at을 갱신하고 최초 등록일 created_at은 유지 |
| 화면에서 연장한 기존 행이 다시 수집됨 | 기존 last_used_at이 더 최신이면 연장 값과 그에 맞는 상태를 유지 |
| 새로 수집됨 | 새 행을 INSERT |
| 이번 실행에서 수집되지 않음 | 적재 완료 후 삭제 대상 |
기존 last_used_at이 수집 값보다 최신이면 연장 값과 그에 맞는 상태를 유지한다. 백업 테이블의 연장 값 이월 역할을 대상 행 자체에서 처리하므로, 전체 복사와 삭제 후 재생성이 필요 없어졌다.
다음은 가상 테이블명과 단순화한 키를 사용한 공개용 개념 SQL이다. status가 기존 last_used_at을 기준으로 연장 여부를 판단하도록 last_used_at 대입을 마지막에 둔다. MySQL 단일 테이블 UPDATE의 대입은 일반적으로 왼쪽부터 평가된다는 동작에 따른 순서다. 실제 키와 나머지 컬럼은 생략했다.
INSERT INTO acl_record (acl_key, status, created_at, last_used_at, sync_at)
VALUES (:aclKey, :status, NOW(), :lastUsedAt, :runAt)
ON DUPLICATE KEY UPDATE
sync_at = :runAt,
status = CASE
WHEN acl_record.last_used_at > :lastUsedAt THEN acl_record.status
ELSE :status
END,
last_used_at = CASE
WHEN acl_record.last_used_at > :lastUsedAt THEN acl_record.last_used_at
ELSE :lastUsedAt
END;
기준 시각과 미수집 행 정리
Job 시작 시 애플리케이션에서 기준 시각 T를 한 번 정해 적재·삭제 Step이 공유한다. 각 쿼리에서 DB의 NOW()를 호출하면 시점과 애플리케이션·DB 시계 차이가 섞일 수 있기 때문이다. sync_at은 TIMESTAMP(0)이므로 T도 초 단위로 맞췄다. 수집 여부는 정확한 일치(=) 대신 기준 이후(>= T)와 이전(< T)으로 판별한다.
0건 가드. 삭제 Step 앞에서 해당 DBMS의 ACL이 이번 실행에 한 건이라도 수집됐는지 확인한다. 수집 행이 전혀 없으면 삭제하지 않고 실패 처리한다. 존재 여부만 필요해 COUNT 대신 EXISTS를 사용했다.
정상 실행의 삭제 비율에도 편차가 있어 비율 임계값은 두지 않았다. 0건 가드는 빈 수집 결과가 삭제로 이어지는 경로를 막지만, 일부만 수집된 상황까지 검출하지는 않는다.
가드를 통과하면 해당 DBMS에서 sync_at이 없거나 T 이전인 행만 1만 건씩 반복 삭제한다.
다음 SQL은 공개용으로 단순화한 삭제 대상과 0건 가드의 개념 예시다. acl_record는 가상 테이블명이며 실제 키와 조건은 생략했다.
SELECT EXISTS (
SELECT 1 FROM acl_record
WHERE dbms_type = :dbmsType AND sync_at >= :runAt
);
DELETE FROM acl_record
WHERE dbms_type = :dbmsType
AND (sync_at IS NULL OR sync_at < :runAt)
LIMIT 10000;
인덱스 변경과 적용 범위
삭제 조건과 가드 조회를 고려해 기존 (dbms_type) 인덱스를 (dbms_type, sync_at)으로 재구성했다. 기존 dbms_type 조건은 새 인덱스의 선두 컬럼을 사용할 수 있다. 변경 전에 관련 프로젝트 세 곳에서 기존 인덱스 이름을 직접 참조하거나 인덱스 힌트를 쓰지 않는 것도 확인했다.
실제 인덱스 접근 방식은 실행계획과 데이터 분포에 좌우된다. NULL과 이전 시각 조건이 언제나 하나의 범위 스캔으로 처리된다고 전제하지 않았다.
신규 Writer와 삭제 Tasklet은 DBMS 공용으로 작성하되 먼저 한 DBMS Job에만 적용했다. 다른 DBMS Job은 기존 흐름을 유지해 단계적으로 전환할 수 있게 했다.
커서 Reader에서 서버별로 펼치기
원본 약 600만 건을 List로 받지 않고 MyBatisCursorItemReader로 한 행씩 읽는다. 서버 매핑에 따라 ACL을 펼치는 작업도 Reader 내부에서 현재 원본 행 단위로 수행한다. 펼친 ACL은 버퍼에서 하나씩 청크에 반환한다.
원본 커서에서 1행 읽기
→ 해당 행의 서버 매핑만큼 ACL로 펼치기
→ 버퍼에서 ACL을 하나씩 청크에 전달
→ 매핑 없는 원본 행은 건너뛰기
약 1,000만 건의 결과 전체를 Step이 보유하지 않는다. 다만 서버 매핑 정보, 현재 원본 행에서 펼친 ACL과 처리 중인 청크는 메모리에 남는다.
ItemStream 생명주기와 커서 비용
Reader는 ItemStreamReader로 구현하고 내부 커서 Reader의 open, update, close를 위임했다. Step 시작 시 커서를 열어 여러 청크에 걸쳐 읽고, 종료 시 닫는다. @StepScope Bean의 반환 타입도 ItemStream 이상으로 유지해 Step이 이 생명주기를 인식하게 했다.
MySQL Connector/J의 행 단위 스트리밍 조건은 forward-only·read-only 결과 집합과 fetchSize = Integer.MIN_VALUE다. 구현에서 fetchSize를 설정했고, 임시 테스트로 MyBatis가 이 값을 실제 전달하는지 확인했다.
스트리밍 연결은 Step 동안 점유하고 결과를 끝까지 읽거나 닫기 전에는 같은 연결에서 다른 쿼리를 실행할 수 없다. 서버의 net_write_timeout 영향도 받는다. 원본 DB 전용 커넥션 풀이어서 이 비용을 감수했다.
페이징 대신 커서를 선택한 이유
원본 테이블은 다른 팀 소유라 인덱스를 추가할 수 없었다. 뒤 페이지로 갈수록 앞부분을 다시 건너뛰는 OFFSET 페이징은 약 600만 건에서 비용이 커지고, 키 기반 페이징에는 적절한 유일 키 인덱스가 필요했다. 페이지 사이의 원본 변경이 누락·중복으로 이어질 수 있다는 점도 고려했다.
원본이 InnoDB이고 배치 시간대에 쓰기가 없음을 확인한 뒤, 하나의 조회를 이어 읽는 커서 방식을 선택했다. 페이지 경계의 변동 위험을 줄이되, 실제 읽기 결과의 일관성은 격리 수준과 트랜잭션 조건에도 영향을 받는다.
실패 후 처음부터 재실행
삭제는 적재 완료 이후에만 수행하도록 설계했다. 적재 중 일부 청크만 커밋된 채 실패하면 대상에는 기존 ACL과 부분 갱신 행이 남을 수 있다. 그래서 재실행 시에는 새 기준 시각 T를 정하고, 커서 위치를 이어서 읽지 않고 원본 처음부터 다시 읽는다. 중간부터 읽으면 앞선 실행에서만 처리한 행이 이전 sync_at으로 남아 삭제 대상으로 분류될 수 있기 때문이다.
아래 애니메이션은 Job의 기준 시각 결정 → 커서 open → 원본 한 행 읽기와 서버별 확장 → 청크 UPSERT → 커서 close → 0건 가드 → 미수집 행 삭제를 차례로 보여 준다. 예시 행과 블록은 흐름 설명용이며, 실제 처리 건수나 10,000건 청크를 그대로 나타내지 않는다.
Stage 3. 검증과 운영 결과
복사 테이블과 운영 건수 검산
단위 테스트에서 원본 한 행의 서버별 펼치기, 매핑 없는 행 건너뛰기, open·update·close 위임과 0건 가드를 확인했다. 임시 테스트로 MyBatis의 fetchSize 전달도 확인했다.
운영 테이블 대신 복사 테이블에서 Job을 실행하고, 의도적으로 다른 상태를 심어 결과를 대조했다.
| 복사 테이블의 확인 조건 | 결과 |
|---|---|
| 대상에서 지워 두고 원본에는 있는 ACL | 신규 INSERT 확인 |
| 기존 ACL | created_at 유지 확인 |
| 연장된 ACL | 더 최신인 연장 값 유지 확인 |
| 대상에만 있는 ACL | 미수집 행 삭제 확인 |
| 다른 DBMS의 ACL | 변경 없음 확인 |
운영 첫 실행에서는 실행 전 행 수 − 삭제 건수 + 신규 건수 = 실행 후 행 수를 검산했다.
운영 관측 결과
구조 개선 배포일 직전 10일과 배포 후 5일의 운영 실행을 비교했다. 두 구간 모두 ACL 동기화 Job에 동일한 -Xmx6G 설정을 적용했다.
수행 시간은 각 실행 시간의 평균, 힙 수치는 각 실행에서 관측한 JVM 힙 최대 사용량의 평균이다. 변경 요소별 기여도는 분리해 측정하지 않았다.
| 항목 | 이전 | 이후 |
|---|---|---|
| 평균 배치 수행 시간 | 약 40분 | 약 30분 |
| JVM 힙 최대 사용량의 평균 | 약 5.5GB | 약 2.7GB |
| 하루 삭제 건수 | 약 1,000만 건, 전체 재적재 | 수천 건, 수집되지 않은 ACL만 삭제 |
동일한 -Xmx6G 설정에서 구조 변경 전후의 힙 사용량과 수행 시간이 함께 낮아졌다. 다만 평균 집계에 성공 실행만 포함됐는지와 두 기간의 처리량이 유사했는지는 확인되지 않아, 수치 비교에는 이 한계가 있다.
삭제 건수는 평균 힙·시간 수치와 별개로, 전체 재적재에서 실제로 수집되지 않은 ACL만 정리하는 방식으로 바뀐 운영 규모를 나타낸다.
이번 변경은 원본과 펼친 결과를 한꺼번에 보유하던 구조를 제거하고, 삭제 전에 새 ACL을 반영하도록 순서를 바꿨다. 연장된 사용 일시와 최초 등록일을 유지하면서 실제로 사라진 ACL만 정리한다. 적용과 운영 검산은 먼저 전환한 한 DBMS에서 확인한 결과다.
BEFORE · 백업 → 삭제 → 재적재
삭제 뒤 적재 실패가 만든 빈 구간
대상에는 전날 ACL이, 백업에는 더 이전 스냅샷이 남아 있습니다. B·서버1은 화면에서 사용 일시가 연장된 행입니다.
A~E와 표시된 블록 수는 흐름을 설명하는 예시입니다. 실제 처리 건수, 청크 크기 또는 메모리 사용량을 나타내지 않습니다.
AFTER · 스트리밍 → UPSERT → 정리
기존 ACL을 유지하며 한 청크씩 갱신
Job 시작 시 기준 시각 T를 한 번 정하고 서버 매핑을 준비합니다. 기존 ACL은 지우지 않아 계속 조회할 수 있습니다.
A~E와 표시된 블록 수는 흐름을 설명하는 예시입니다. 실제 처리 건수, 청크 크기 또는 메모리 사용량을 나타내지 않습니다.