963초짜리 쿼리 하나가 HLL 205만까지 끌어올렸습니다

date

slug

author

status

tags(최대 3개)

summary

type

thumbnail

새벽에 울린 알림

메인 Writer DB에는 HLL(History List Length)이 100만을 넘으면 울리는 P2 알람이 있습니다. AWS CloudWatch도 RollbackSegmentHistoryListLength의 권장 임계값으로 100만을 제시하며, 이 값이면 문제를 조사할 시간을 확보할 수 있다고 설명합니다. 다만 허용 가능한 성능 수준과 워크로드 특성에 따라 임계값을 조정할 수 있다는 조건을 함께 둡니다.

운영 환경에서는 수천 만에서 억 단위 테이블을 조회하는 작업과 동시 쓰기가 함께 일어나 HLL이 평소에도 십만 단위를 오갑니다. 보편적인 안전선이나 장애가 시작되는 상한은 아닙니다.

아임웹이 설정한 알람 기준 수치는 100만입니다. 이 기준은 평소보다 HLL이 크게 높아진 상황을 파악하면서, 원인을 조사할 시간을 확보합니다.

알람은 있었지만 정작 숫자를 파악하는데 어려움이 있었습니다. HLL이 왜 오르는지, 어디까지가 정상이고 어디부터 원인을 찾아야 하는지 판단할 기준이 없었습니다.

그래서 Aurora MySQL의 각각 버전 2와 버전 3에 같은 시나리오를 적용했습니다. 두 엔진의 성능을 비교하려는 게 아니라, 조회 시점의 스냅샷이 오래 유지될 때 HLL이 쌓이고 회복하는 방향이 두 세대에서 재현되는지 확인하려는 실험이었습니다.

아임웹 메인 Writer DB의 CloudWatch HLL 60초 max 추이입니다(반환 간격 120초, y축 로그 스케일).
평소 범위는 01:00~02:54 실측값이며, DBM 동일 구간 최고값은 2,105,693 — 아임웹 메인 Writer DB의 CloudWatch HLL 60초 max 추이입니다(반환 간격 120초, y축 로그 스케일). 평소 범위는 01:00~02:54 실측값이며, DBM 동일 구간 최고값은 2,105,693

실험을 마치고 얼마 지나지 않아, 운영에서 바로 알람이 울렸습니다. HLL 값은 2,054,013으로 임계값의 두 배를 넘겼습니다. 그리고 11분 뒤 알람은 스스로 해제됐습니다. 별다른 조치가 없었는데도 HLL은 왜 다시 내려왔을까요?

HLL(History List Length)의 정의

History list는 커밋된 트랜잭션들의 undo log를 모아둔 목록이고, 그 길이가 HLL입니다. CloudWatch에서는 RollbackSegmentHistoryListLength라는 이름으로 나옵니다.

HLL을 이해하려면 undo가 왜 남는지부터 봐야 합니다. InnoDB는 MVCC(Multi-Version Concurrency Control, 다중 버전 동시성 제어)를 위해 row를 바꿀 때 변경 전 버전을 undo에 남깁니다. read view(읽기 스냅샷)는 자기가 만들어진 시점의 데이터를 보려고 이 과거 버전을 사용하고, purge는 이제 어떤 read view에도 필요 없어진 undo를 지웁니다. 결국 HLL은 아직 지우지 못하고 남아 있는 undo log의 개수입니다.

여기서 HLL과 변경된 row 수는 구분해야 합니다. undo log 하나에는 트랜잭션 하나가 남긴 여러 건의 수정이 함께 들어갑니다. 그래서 HLL과 실제 변경된 row 수는 비례하지 않습니다. 같은 row를 여러 트랜잭션에서 반복 수정하고 커밋하는 워크로드라면 row는 적어도 HLL은 크게 오를 수 있습니다.

undo가 쌓이고 purge가 지우는 과정과 read view가 purge 경계를 멈추는 지점

HLL은 커밋된 쓰기가 undo를 만드는 속도가 purge보다 빠를 때 증가합니다. 오래 유지된 read view가 있으면 purge가 과거 버전을 정리하지 못하고, 그 사이 동시 쓰기가 만든 undo가 계속 쌓입니다. read view 자체가 HLL을 만드는 게 아니라, 이미 생성되는 undo의 정리를 막는 관계입니다.

이 사건에서 HLL은 평소 3,600~6,700 사이를 오르내리다 2,054,013이 됐습니다. 그런데도 CPU는 17%를 넘지 않았고, 서비스는 정상이었으며, 11분 뒤 알람은 스스로 해제됐습니다.

HLL은 지연이나 CPU 사용률 그 자체가 아니라, 정리되지 못한 undo가 얼마나 밀려 있는지를 보여주는 backlog 신호입니다. 숫자가 크다는 사실만으로 장애를 확정할 수도, 서비스가 멀쩡하다는 이유로 무시할 수도 없습니다. 다만 그 backlog가 풀리는 순간에는 실제로 지연이 생길 수 있습니다.

undo 누적의 문제점

커뮤니티 MySQL 문서는 purge가 밀리기 시작하면 정리되지 않은 과거 row version 때문에 테이블이 계속 커지고, 결국 디스크 의존도가 높아지면서 느려질 수 있다고 설명합니다.

보조 인덱스는 더 구체적입니다. 보조 인덱스 컬럼이 수정되면 예전 레코드에 삭제 표시가 남는데, 그 표시가 남아 있는 동안에는 covering index를 쓸 수 없습니다. 인덱스만 읽고 끝낼 수 있었던 조회가 클러스터 인덱스를 다시 찾아가게 됩니다. 쌓인 undo는 buffer pool도 함께 압박하고, 과거 버전을 따라가는 조회는 추가 I/O를 만듭니다. 이런 이유로 MySQL 문서는 읽기만 하는 트랜잭션도 정기적으로 커밋하라고 권합니다.

Aurora로 오면 조합이 하나 더 붙습니다. AWS는 Writer에 쓰기가 몰리고 Reader에서 장기 실행 쿼리가 도는 경우를 지목하며, 이때 상당한 purge 지연이 생긴다고 설명합니다. 장기 트랜잭션이 끝난 뒤에는 purge 활동이 급증해 replica lag가 늘고, 심하면 reader가 재시작될 수 있다고도 밝힙니다. HLL 자체가 장애는 아니지만 쌓일 때도 풀릴 때도 값을 치릅니다.

과거에 우리가 겪은 것이 이 상황이었습니다. Aurora 3로 옮긴 뒤 Reader 전용 Read Committed 설정을 켜기 전까지, HLL이 하루 열 번 넘게 100만 이상으로 오르내렸습니다. 그리고 그 backlog가 풀리는 시점마다 아래 지표가 함께 튀었습니다.

지표	평소	급증 시
CPU	20%	30%
커넥션	500	1,000
슬로우 쿼리	5	200
ReadLatency	1.08ms	1.31ms
WriteLatency	1.29ms	1.56ms
CommitLatency	35ms	441ms

이 설정을 켠 뒤 반복 급증은 사라졌습니다. CommitLatency가 열두 배 넘게 뛰는 구간은 사용자가 체감할 수 있는 지연입니다.

다만 이 글 앞부분의 사건과는 성격이 다릅니다. 그때는 Reader 조회가 purge를 반복해서 막았고, 이번에는 Writer 단일 장기 조회와 겹친 한 번의 큰 스파이크였습니다. 우리 운영 관측에서는 반복되는 backlog가 지연과 함께 나타났지만, 일회성 스파이크의 영향은 HLL 하나만으로 판단할 수 없습니다.

Aurora의 공유 스토리지

커뮤니티 MySQL 복제에서는 프라이머리와 리플리카가 각자의 스토리지와 undo를 가집니다. 리플리카에서 30분짜리 리포트 쿼리를 돌리면 그 영향은 리플리카 쪽 undo에 남습니다.

Aurora는 Writer와 Reader가 하나의 스토리지 볼륨을 공유합니다. Reader에서 열린 read view가 살아 있는 동안에는 그 스냅샷이 보는 과거 버전을 지울 수 없고, 그 결과가 Writer의 HLL로 나타납니다. 공유 스토리지 전체가 잠기거나 Writer의 쓰기가 멈추는 것은 아닙니다. Writer의 쓰기와 커밋은 계속되지만, Reader의 read view 때문에 purge 경계가 전진하지 못해 그동안 생성된 undo를 정리하지 못하는 구조입니다.

AWS가 Aurora Replica 전용으로 aurora_read_replica_read_committed(이하 ARRRC) 파라미터를 따로 제공하는 이유도 여기에 있습니다. 켜면 Reader의 장기 조회가 purge를 붙잡는 정도를 줄일 수 있습니다.

왜 줄어드는지는 스냅샷 수명으로 설명됩니다. 일반적인 Read Committed는 statement마다 새 스냅샷을 만들지만, 그 statement가 실행되는 동안에는 스냅샷이 고정됩니다. 반면 Aurora Reader의 Read Committed는 일반 MySQL Read Committed보다 일관성이 느슨합니다. 하나의 statement가 실행되는 동안에도 다른 트랜잭션이 커밋한 변경이 결과에 보일 수 있습니다. 과거 버전을 오래 붙잡지 않으니 purge 지연은 줄지만, 같은 쿼리가 도는 동안 데이터가 바뀔 수 있습니다. purge는 덜 밀리는 대신 조회 결과는 더 흔들릴 수 있습니다.

무거운 조회는 Reader로 옮기면 된다는 말은 Aurora 환경에서는 절반만 맞습니다. Writer만 살펴보면 과거 버전 정리를 늦추는 Reader 세션을 놓칠 수 있습니다.

아임웹은 Aurora 2의 EOL 대응으로 Aurora MySQL 3.04.1로 올렸고, 이후 3.10.4 LTS로 다시 업그레이드했습니다. 이 글과 직접 관련해서는 관측 지표가 늘었습니다. 아래 표는 두 운영 버전에서 확인할 수 있는 지표만 비교합니다.

ㅤ	3.04.1	3.10.4
HLL (`RollbackSegmentHistoryListLength`)	제공	제공
`TransactionAgeMaximum`	없음	제공 (3.08+)
`PurgeBoundary` / `PurgeFinishedPoint`	없음	제공 (3.08+)

운영 버전 비교와 별개로, Aurora MySQL 2.12.x와 3.12.x에서 조회 시점의 스냅샷이 오래 유지되는 상황을 재현했습니다. 두 환경 모두 스냅샷이 유지되는 동안 purge가 제한됐고, 그 사이 동시 쓰기가 만든 undo가 HLL에 쌓였습니다. 이 실험은 두 엔진의 절대 성능을 비교하기 위한 것이 아닙니다.

Repeatable Read와 Read Committed는 HLL에 어떤 차이를 만드나

알람이 울렸을 때 HLL 숫자를 제대로 해석하려면 세 가지를 먼저 알아야 했습니다. 평소 HLL은 어느 수준까지 오르는지, 읽기 스냅샷은 언제 만들어지는지, 그리고 그 스냅샷이 오래 유지될 때 HLL은 어떻게 쌓이고 다시 내려오는지입니다.

isolation level은 스냅샷을 재사용하는 경계를 정합니다. Repeatable Read는 트랜잭션의 첫 consistent read에서 만든 스냅샷을 끝까지 공유하고, Read Committed는 statement마다 새 스냅샷을 만듭니다. 다만 스냅샷 하나가 얼마나 오래 유지되는지는 해당 트랜잭션이나 statement의 실행 시간에 달려 있습니다. 아래 조건으로 직접 확인했습니다.

항목	Aurora MySQL 2	Aurora MySQL 3
엔진	5.7 호환 (2.12.x)	8.0 호환 (3.12.x)
구성	Writer 1 + Reader 1 (각 db.t4g.medium)	동일
데이터	sysbench 20테이블 × 100만 행	동일
부하 발생기	AWS VPC 내 EC2 1대	동일
Writer 쓰기 부하	sysbench OLTP read/write 64 threads	동일
read view holder	조건에 따라 0 또는 1세션. holder 조건에서는 트랜잭션을 열고 SELECT 후 유지	동일
HLL 수집	5초 간격 sampler	동일
반복 횟수	조건별 1회	조건별 3회

부하 발생기는 EC2 1대로 고정했습니다. 이 실험의 목적은 부하 발생기의 확장성 비교가 아니라, 각 시나리오 안에서 부하 발생기와 thread 조건을 고정한 뒤 read view holder의 유무와 격리 조건에 따른 HLL 움직임을 확인하는 것이었습니다.

Aurora MySQL 3에서 조건별로 세 번 반복해 방향과 흩어짐을 확인한 뒤, 같은 메커니즘이 5.7 호환 세대에서도 재현되는지 보려고 Aurora MySQL 2에서 조건별로 한 번 실행했습니다. 반복 횟수가 다르므로 두 버전의 절대값이나 성능은 비교하지 않습니다. 아래 수치는 모두 Aurora MySQL 3에서 조건별로 세 번씩 반복한 결과입니다.

기준선은 평소 HLL

장기 조회나 첫 읽기 뒤 오래 열린 트랜잭션이 없는 평소 부하에서도 HLL은 0에 고정되지 않았습니다. 세 번 모두 5천 언저리를 상단으로 오르내리는 톱니 모양이었고, 시간이 지나도 그 상단이 커지지 않았습니다. 이 범위를 뒤에서 자기 기준선으로 씁니다.

이 실험에서는 BEGIN만으로 관측 가능한 read view가 생기지 않았습니다. BEGIN만 실행하고 3분을 기다리는 동안 HLL은 평소 수준에 머물렀고, INNODB_TRX를 조회해도 그 트랜잭션은 보이지 않았습니다. 같은 세션에서 InnoDB 테이블을 한 번 SELECT하자 그때부터 트랜잭션이 보였고 HLL도 다섯 배로 뛰었습니다. 세 번 모두 동일했습니다.

각 3분씩, 세 번 반복	트랜잭션이 보이는가	HLL 최고
`BEGIN`만 실행	보이지 않음	약 5천 (평소 수준)
`BEGIN` 뒤 `SELECT` 한 번	세 번 모두 보임	2만 4천 ~ 2만 9천

이 조건에서는 트랜잭션을 연 시각보다 첫 InnoDB consistent read가 일어난 시각이 관측 기준이었습니다.

스냅샷의 수명과 회복 시간

Writer에서 첫 SELECT를 실행한 뒤 트랜잭션을 5분 동안 열어두었고, 그동안 HLL은 일정한 속도로 쌓였습니다. 1초에 약 163씩, 5분이면 5만입니다. 평소 상단이 5천이었으니 열 배입니다.

운영 DB가 아닌 Aurora MySQL 3 실험 클러스터(Writer 1 + Reader 1)의 Writer 값입니다.

sysbench OLTP 동시 부하를 유지하며 SHOW ENGINE INNODB STATUS로 HLL을 5초마다 수집했고, x축 0분은 read view를 연 시점입니다.

커밋하자 다시 내려왔습니다. 다만 걸린 시간은 세 번 모두 달랐습니다.

회차	평소 수준으로 돌아오기까지
1회차	48초
2회차	33초
3회차	54초

회복은 매번 됐지만, 커밋하면 몇 초 안에 돌아온다고 약속할 수는 없습니다.

Reader 조건이 Writer HLL에 미치는 영향

Reader에서 실제로 오래 걸리는 조회를 돌려봤습니다. 60만 건 범위를 훑는 같은 조인을 두 가지 설정 조합에서 실행했습니다. 비교한 것은 Repeatable Read + ARRRC OFF와 Read Committed + ARRRC ON입니다. 두 설정을 함께 바꾼 조합 비교이므로, 아래 표만으로 isolation과 ARRRC 각각의 독립 효과를 분리하지는 않습니다.

항목	조건 A	조건 B
Reader isolation	Repeatable Read	Read Committed
ARRRC	OFF	ON (클러스터 파라미터 그룹)
세션 설정	`SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;`	`SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;`
확인 쿼리	`SELECT @@session.transaction_isolation, @@session.aurora_read_replica_read_committed;`	동일
조회 쿼리	아래 60만 건 범위 JOIN	동일
동시 쓰기	Writer에서 sysbench OLTP 유지	동일
반복	3회	3회


SELECT COUNT(*)
FROM sbtest1 a
STRAIGHT_JOIN sbtest1 b ON a.k = b.k
WHERE a.id < 600000
  AND b.id < 600000;

Reader 조건	조회 경과시간 (3회)	Writer HLL 최고 (3회)
RR + ARRRC OFF	75.08 / 106.85 / 94.57초	10,244 / 14,393 / 12,011
RC + ARRRC ON	25.12 / 38.39 / 24.85초	4,954 / 4,997 / 4,989

Reader를 Repeatable Read로 두고 ARRRC를 끈 조건에서는 조회가 도는 동안 Writer HLL이 평소 수준 위로 올라갔습니다. Read Committed로 두고 ARRRC를 켠 조건에서는 세 번 모두 평소 수준인 5천 안에 머물렀습니다. 세 번 다 같은 방향이었습니다.

설정 적용 여부 검증

이건 실험으로 처음 안 게 아닙니다. Aurora 3로 옮긴 뒤 DB isolation을 Read Committed로 바꿔 운영하고 있었는데, 그 설정은 Writer에만 걸리고 Reader에는 걸리지 않았습니다. global variables를 조회하면 Writer도 Reader도 READ-COMMITTED로 보이지만, Reader에 접속하는 순간 세션은 Repeatable Read가 됩니다. 우리는 이 사실을 1년이 지나서야 알았습니다.

그래서 설정이 실제로 걸렸는지는 파라미터 이름이 아니라 세 가지로 확인했습니다. 세션에서 isolation 값을 직접 읽어보고, 그 조건에서 Writer HLL이 어떻게 움직이는지 보고, 같은 값을 반복 조회해 결과가 흔들리는지 확인했습니다. 파라미터만 켜고 세션은 Repeatable Read로 둔 경우와, 세션만 Read Committed로 요청하고 파라미터는 끈 경우를 각각 한 번씩 시험했는데 둘 다 완화된 움직임이 나오지 않았습니다. 세션에서 Read Committed를 요청해도 실제로 읽어보면 REPEATABLE-READ였습니다.

AWS 문서도 ARRRC를 켜지 않은 기본 상태의 읽기 전용 Aurora 복제본은 REPEATABLE READ를 사용하고, SET TRANSACTION ISOLATION LEVEL 요청을 무시한다고 설명합니다. Reader에서 Read Committed를 쓰려면 ARRRC를 켠 뒤 세션 격리 수준을 함께 설정해야 합니다. 우리가 본 것은 이상 현상이 아니라 문서에 적힌 정상 동작이었습니다.

Read Committed의 이점과 대가

Reader를 Read Committed로 두고 ARRRC를 켜면 문제가 끝나는 것처럼 보입니다. 하지만 그렇지 않았습니다.

첫째, 읽기 결과의 일관성과 반복 가능성이 달라집니다. 우리가 실행한 20번의 조회는 하나의 트랜잭션 안에서 실행한 20개의 statement였습니다. Read Committed는 statement마다 새 read view를 만들기 때문에 각 조회가 시작될 때 보이는 커밋 상태가 달라질 수 있습니다. 이번 실험에서는 Writer에서 커밋된 변경이 다음 조회에 반영됐습니다. 세 번의 실험에서 같은 row의 값은 20번 모두 달라졌고, 범위 조건의 건수도 20번 중 14~15번 달라졌습니다. 이는 Aurora만의 특성이 아니라 일반 Read Committed에서도 허용되는 동작입니다.

Aurora Reader의 ARRRC는 여기서 한 단계 더 나아갑니다. AWS 문서는 하나의 statement가 실행되는 동안에도 다른 트랜잭션의 커밋이 보일 수 있다고 설명합니다. 가변 길이 컬럼의 UPDATE로 행이 재배치되면, 같은 단일 조회 안에서도 예전 버전과 새 버전을 모두 건너뛰거나 같은 행을 두 번 볼 수 있습니다. 다만 이번 실험은 statement와 statement 사이의 변화만 확인했으므로, 단일 statement 안의 중복·누락은 직접 관측하지 않았습니다.

5만 건짜리 JOIN 결과 건수는 여덟 번 확인하는 동안 달라지지 않았습니다. 다만 AWS는 조인에서 한쪽 테이블의 행은 읽고 다른 쪽의 대응하는 행은 읽지 않을 수 있다고 문서에 적어 두었습니다. 못 봤다는 것과 일어나지 않는다는 것은 다릅니다. 이 둘을 같은 현상으로 묶지 않기 위해 아래처럼 나눴습니다.

현상	구분
같은 row를 20번 읽어 값이 달라짐	이번 실험에서 관측. 일반 Read Committed에서도 허용
범위 조건 건수가 달라짐	이번 실험에서 관측. 일반 Read Committed에서도 허용
단일 쿼리 안에서 행이 중복되거나 누락	이번 실험에서는 미관측. AWS 문서상 ARRRC에서 가능

Aurora Reader의 ARRRC에서는 statement 실행 중에도 커밋된 변경이 보임.

이번 실험에서 직접 확인한 범위는 위의 두 Read Committed 현상까지입니다. 단일 statement 안의 중복·누락은 AWS 문서가 경고하는 가능성으로만 남깁니다.

그래서 이 조합을 쓸지 말지는 성능 설정이 아니라 데이터 계약의 문제입니다.

한 트랜잭션 안에서 같은 row를 두 번 읽을 때 값이 달라도 되는가?

반복 집계 중 row count가 변해도 되는가?

이 리포트가 절대 정밀도보다 최신성과 완료 시간을 우선하는가?

값이 흔들렸을 때 애플리케이션이 감지하거나 허용할 수 있는가?

하나라도 아니오라면, HLL이 내려간다는 이유만으로 이 조합을 적용해서는 안 됩니다.

둘째, 모든 쿼리가 같은 대가를 치르는 것은 아닙니다. AWS가 정한 사용 조건도 이 차이에서 나옵니다.

ㅤ	대량 집계·리포트	짧고 정밀한 조회
몇 건 차이의 의미	결과 해석이 바뀌지 않음	한 건 차이가 곧 오류
조회 시간	길다 → purge를 오래 붙잡음	짧다 → 애초에 부담이 작음
판단	얻는 것이 크고 잃는 것이 작다	얻는 것이 작고 잃는 것이 크다

기간 전체를 훑는 대규모 집계라도, 일부 row의 변동이 결과 해석을 바꾸지 않도록 설계된 리포트가 앞쪽에 해당합니다. 이런 조회는 대체로 길어서 purge를 오래 붙잡기 때문에 Read Committed와 ARRRC로 얻는 이익이 큽니다.


-- 앞쪽: 몇 건 오차가 결론을 바꾸지 않는다
SELECT DATE(created_at), COUNT(*), SUM(amount)
FROM 주문 WHERE created_at >= '2026-06-01' GROUP BY 1;
-- 뒤쪽: 읽은 값으로 계산·판정한다
SELECT stock FROM 재고 WHERE item_id = ?;   -- 이 값으로 출고 가능 여부를 정한다

뒤쪽은 반대입니다. 읽은 값 하나로 정산 금액을 계산하거나 출고 여부를 판정한다면, 흔들린 한 건이 그대로 오류가 됩니다. 게다가 조회가 짧아서 애초에 purge를 오래 붙잡지도 않습니다. 얻을 것이 없는데 위험만 지는 셈입니다. 그래서 AWS도 이 설정을 켜기 전에 기존 SQL이 더 느슨한 일관성에서도 예상대로 동작하는지 먼저 확인하라고 안내합니다.

우리 사건은 설정만으로 풀 수 있는 문제도 아니었습니다. Writer는 이미 Read Committed였습니다. 그런데도 HLL은 205만까지 올랐습니다. Read Committed는 statement마다 새 스냅샷을 만들지만, 그 statement 하나가 963초라면 스냅샷도 963초를 삽니다. isolation level은 스냅샷이 몇 번 만들어지는지를 정할 뿐, 하나가 얼마나 오래 사는지는 정하지 못합니다.

운영에서 확인할 점

다시 알람이 울린 새벽으로 돌아갑니다. 앞의 그래프에서 ①~⑤ 마커는 아래 표의 주요 시각과 대응합니다.

시각(KST)	일어난 일	당시 HLL
① 03:00:05	문제 쿼리 시작	150,566
② 03:10:47	P2 알람 발생	—
03:14:00	최고값	2,054,013
③ 03:16:08	문제 쿼리 종료 (963.0초)	168,437
④ 03:21:56	알람 자동 해제	9,812
⑤ 03:40:00	평소 범위 복귀	4,532

당일 오전에 배치 영향으로 추정해 분석을 시작했고, slow query 집계로 원인을 특정했습니다.

원인이 된 패턴은 주문 테이블에서 회원 식별자를 SELECT DISTINCT로 뽑는 조회였습니다. 02시 50분부터 03시 25분 사이 slow query 261건 중 250건이 같은 패턴이었고, 그중 최장 963.0초 동안 약 176만 row를 읽은 실행이 HLL 최고 구간과 겹쳤습니다.

기존 조회는 결과를 행 단위로 읽었습니다. 이 방식 자체가 문제는 아니었습니다. 다만 약 176만 row를 하나의 SELECT DISTINCT로 읽는 동안 조회가 963초 지속됐고, 같은 read view도 그만큼 오래 유지됐습니다. 문제는 결과를 읽는 방식이 아니라 전체 범위를 하나의 긴 statement로 실행한 구조였습니다.

쿼리가 끝난 시점과 HLL이 급락한 시점은 거의 일치했습니다. 다만 이 일치 하나로 원인을 확정한 것은 아닙니다. 같은 구간 slow query의 분포와, 실험에서 재현한 "read view가 유지되는 동안 동시 쓰기가 만든 undo를 purge하지 못한다"는 결과를 함께 놓고 이 장기 조회를 purge 지연의 주원인으로 판단했습니다.

이후 비슷한 알람이 오면 아래 순서로 확인하기로 했습니다.

평소 HLL 범위를 먼저 확인합니다. 장기 조회나 오래 열린 트랜잭션이 없을 때 HLL이 어느 범위에서 오르내리는지 봅니다. 우리 운영 DB는 3,600~6,700이었고 실험 환경은 5천 언저리였습니다. 이 범위를 알아야 알람이 평소와 얼마나 다른지 판단할 수 있습니다.

HLL이 오른 시간대의 장기 조회와 열린 트랜잭션을 찾습니다. information_schema.innodb_trx와 processlist를 같은 시간대로 맞춰 봅니다. 트랜잭션 시작 시각뿐 아니라 실제 쿼리 시작 시각도 함께 확인합니다. Repeatable Read에서는 첫 InnoDB consistent read부터 읽기 스냅샷이 유지될 수 있습니다.

Writer와 Reader를 함께 봅니다. 공유 스토리지 때문에 Reader의 장기 조회도 Writer purge에 영향을 줍니다.

HLL이 높다는 이유만으로 세션부터 종료하지는 않습니다. HLL은 backlog의 크기이지 그 자체로 장애가 아닙니다. 소유자, 재시도 가능성, 서비스 영향과 purge 회복 상태를 함께 본 뒤 중단 여부를 결정합니다.

설정보다 조회를 짧게 끝낼 방법부터 봅니다. 이번 사건처럼 Writer에서 statement 하나가 오래 실행되면 일반 Read Committed만으로는 그 조회의 스냅샷 수명을 줄일 수 없습니다. chunking, pagination, 스캔 범위 축소처럼 조회를 짧게 나누는 방법과, timeout처럼 지나치게 긴 조회를 중단하는 방법을 먼저 검토합니다.

Reader의 Read Committed 전환은 반복 조회 결과가 달라져도 되는 쿼리인지 확인한 뒤 결정합니다. 쿼리마다 얻는 것과 잃는 것이 다르기 때문입니다.

아임웹은 5번을 택했습니다. Writer가 이미 Read Committed였기 때문에, 긴 statement 하나를 여러 개의 짧은 statement로 나눠 스냅샷이 오래 유지되지 않게 했습니다. 기존 단일 SELECT DISTINCT를 기본 키 기준 1만 건 단위 조회로 바꾸고, 각 청크 결과를 합치면서 중복을 제거했습니다. 바꾼 내용은 이렇습니다.

조회 시작 시점에 가장 큰 주문 기본 키를 상한으로 고정한 뒤, 직전 묶음에서 마지막으로 읽은 기본 키보다 큰 주문부터 그 상한까지를 기본 키 순서대로 1만 건씩 읽습니다. 기본 키 인덱스를 따라 다음 구간으로 이동하므로 매번 앞부분을 다시 훑지 않습니다.

중복 제거는 각 청크의 결과를 합치는 과정에서 처리합니다.

신규 조회는 리드 레플리카로 보냅니다.

청크 수집 중 상한 조회나 청크 조회가 실패하면 부분 결과를 버리고, 해당 사이트만 기존 SELECT DISTINCT로 다시 처리하며 경고 로그를 남깁니다.

전체 적용 전에는 일부 사이트에만 새 경로를 적용해 정확성과 HLL 변화를 확인했습니다. 이하 이 제한 적용 단계를 카나리 검증이라고 부릅니다. 검증이 끝난 뒤 기능키 분기를 제거했고, 현재 코드는 모든 사이트에서 청크 경로를 먼저 사용합니다.

이 조회는 직전 쓰기의 즉시 반영이 필요하지 않은 배치 읽기이므로, 읽기 작업을 Reader에 우선 배치하는 운영 원칙을 따랐습니다. 다만 Reader로 옮긴 것만으로 HLL 문제가 해결된 것은 아닙니다. Aurora에서는 Reader의 read view도 Writer의 purge에 영향을 줄 수 있으므로, 핵심은 각 statement를 짧은 청크로 끝내 read view의 수명을 줄인 것입니다.

처음부터 1만 건이 정답이라고 계산한 것은 아닙니다. 조회 한 번이 충분히 짧으면서 왕복 횟수가 너무 많지 않은 값으로 잡고, 실제 계산이 실행되는 일부 대상에 먼저 적용해 청크 실행 시간과 HLL 변화를 확인했습니다.

카나리 단계에서는 나머지 사이트가 여전히 기존 경로를 쓰고 있어 HLL이 곧바로 평소 수준까지 떨어지지는 않았습니다. 단계별 최고값은 아래 그래프에 정리했습니다.

각 값은 해당 날짜 03:00~03:45 KST의 CloudWatch HLL 60초 max이며, 다른 시간대는 포함하지 않았습니다.

대상 사이트 수와 실행 조건이 달라 절대값을 같은 실험군처럼 직접 비교할 수는 없습니다. 다만 전체 적용에서는 2,486개 사이트를 처리하는 동안 Writer HLL 최고값이 11,813에 머물렀습니다. 빠뜨린 이벤트, 실행 중 기존 경로로 전환된 사례, 장시간 SELECT DISTINCT 재발은 없었으며 replica lag는 최대 26ms였습니다.

배치 전체 소요는 약 15분에서 약 45분으로 늘었습니다. 이번에 줄이려던 것은 배치의 총 시간이 아니라 하나의 read view가 살아 있는 시간이었습니다. 쿼리 자체를 더 빠르게 만들거나 여러 쿼리로 나누는 방법 모두 read view 수명을 줄일 수 있습니다. 우리는 963초짜리 조회 하나를 짧은 조회 수천 개로 나누는 방법을 택했습니다.

예외 경로가 실행되면 같은 HLL 위험이 다시 생길 수 있으므로 경고 로그를 함께 봅니다. Aurora 3.08 이상에서는 TransactionAgeMaximum과 PurgeBoundary를 HLL과 함께 보면 원인 read view를 더 빨리 좁힐 수 있습니다.

실험의 범위와 한계

평소 부하의 톱니, 첫 읽기 이후의 read view 생성, 오래 유지된 read view와 동시 쓰기가 겹칠 때의 HLL 누적, Reader의 Read Committed 조합에서 나타난 변화는 Aurora MySQL 2와 3에서 같은 방향으로 재현됐습니다.

다만 Aurora MySQL 2는 조건별 한 번만 실행했고 시작 HLL과 남은 쓰기 부하를 완전히 통제하지 못했으므로, 두 엔진의 절대 성능이나 우열은 결론 내리지 않습니다. CPU가 HLL 회복 구간과 함께 내려온 모습도 AWS가 설명하는 패턴과 부합했지만, 같은 시간대에 배치 부하가 남아 있었습니다. 따라서 CPU 변화와 purge 회복을 1:1 인과로 해석하지 않았습니다.

결론

결과를 놓고 보면 Read Committed가 HLL 문제의 정답은 아니었습니다.

Repeatable Read는 같은 트랜잭션에서 일관된 스냅샷을 유지하지만, 오래 열린 read view가 purge를 제한할 수 있습니다. Aurora Reader의 Read Committed와 ARRRC 조합은 이 부담을 줄이는 대신, 같은 조회가 실행되는 동안에도 결과가 달라질 수 있는 더 느슨한 일관성을 받아들여야 합니다.

같은 값을 반복해서 읽었을 때 결과가 같아야 하거나 정확한 판정이 중요하다면, Repeatable Read를 유지하면서 트랜잭션과 조회를 짧게 만드는 편이 맞습니다. 반대로 일부 값이 달라도 결론이 바뀌지 않는 대규모 집계나 리포트라면, 실제 쿼리로 검증한 뒤 Read Committed와 ARRRC를 고려할 수 있습니다. 선택은 HLL 숫자가 아니라 그 조회에서 반드시 지켜야 할 결과의 일관성에 달려 있습니다.

이번 사건은 그 차이를 보여준 사례였습니다. Writer는 이미 Read Committed였지만 하나의 statement가 963초 동안 실행되면서 read view도 오래 유지됐습니다. 우리는 격리 수준을 다시 바꾸는 대신 조회를 짧게 나눠 해결했습니다.