쿼리는 그대로인데 계획이 바뀌었다

한 줄 요약

쿼리도 인덱스도 그대로인데 어느 날 갑자기 느려졌다면 바뀐 것은 옵티마이저가 가진 숫자다. 그리고 표가 줄지 않는다면 지울 것이 없어서가 아니라 지워도 되는지 아직 모르기 때문이다.

왜 이게 필요했나

집계 API 하나가 어제까지 40밀리초였는데 오늘 6초다. 배포는 없었다. 인덱스도 그대로다. 데이터가 늘긴 했지만 세 배쯤이지 백 배가 아니다.

이럴 때 가장 흔한 대응이 인덱스를 하나 더 만드는 것이고, 대개 그건 답이 아니다. 실행 계획을 열어 보면 이유가 한 줄에 적혀 있다.

어떻게 동작하나

EXPLAIN (ANALYZE, BUFFERS) 를 붙이면 계획 노드마다 두 벌의 숫자가 나온다. 앞 괄호의 rows= 는 예상, 뒤 괄호의 rows= 는 실제다. 실습 환경에서 그대로 찍은 것이다.

 Aggregate  (cost=1988.00..1988.01 rows=1 width=8) (actual time=8.282..8.282 rows=1 loops=1)   Buffers: shared hit=988   ->  Seq Scan on events  (cost=0.00..1988.00 rows=1 width=0) (actual time=0.925..5.801 rows=60000 loops=1)         Filter: (tenant_id = 41)         Rows Removed by Filter: 20000

예상 1행, 실제 60000행. 이 한 줄이 그날의 사고를 전부 설명한다. 옵티마이저는 이 조건이 한 행만 내놓는다고 믿었기 때문에, 그 한 행을 상대 표의 모든 행과 맞춰 보는 중첩 루프를 골랐다. 실제로는 6만 행이 나왔으니 그 루프가 6만 번 돌았다.

왜 1이라고 봤나. 새 테넌트를 대량으로 적재하고 ANALYZE 를 하지 않았기 때문이다. pg_stats 에는 적재 전의 분포가 남아 있고, 거기에 tenant_id = 41 은 존재하지 않는다. 없는 값을 물으면 옵티마이저는 "거의 없다" 고 답한다.

같은 환경에서 잰 값이다.

통계가 낡은 상태   Nested Loop   실행 3059 msanalyze events;통계를 고친 뒤     Hash Join     실행   66 ms

바꾼 것은 쿼리도 인덱스도 아니고 옵티마이저가 가진 숫자뿐이다. 앞의 숫자는 여러 번 재는 동안 1.5초에서 6초 사이로 흔들렸다. 절대값이 아니라 비율을 보는 것이 맞다.

읽을 때 함정이 하나 있다. 계획에 loops= 가 1이 아니면 표시된 rows 와 시간은 1회 평균이다. 병렬 계획에서는 워커마다 한 번씩 도므로 rows=100000 loops=2 는 실제로 20만 행이라는 뜻이다. 헷갈리는 자리라서, 숫자를 정확히 읽어야 할 때는 set max_parallel_workers_per_gather = 0 으로 병렬을 끄고 한 번 더 뽑는 편이 낫다.

인덱스를 더하면 낫나

같은 사고에 events(tenant_id) 인덱스를 만들어 두고 다시 재 봤다. 아래는 20만 행짜리로 잰 값이다.

인덱스 없음 · 통계 낡음    9774 ms   Nested Loop / Seq Scan     예상      1행인덱스 있음 · 통계 낡음     205 ms   Nested Loop / Index Scan   예상      1행인덱스 없음 · 통계 정상      60 ms   Hash Join  / Seq Scan      예상 234564행

인덱스는 확실히 도움이 된다. 9.7초가 0.2초가 됐다. 그런데 계획은 여전히 틀렸다. 예상은 그대로 1행이고 옵티마이저는 여전히 중첩 루프를 고르고 있다. 통계를 고친 쪽이 인덱스 없이도 세 배 넘게 빠르다.

이게 인덱스를 먼저 손대면 안 되는 이유다. 증상은 줄어들지만 원인은 그대로 남고, 인덱스는 쓰기마다 비용을 물린다. 다음에 같은 표에 또 대량 적재가 들어오면 같은 일이 반복된다.

덧붙여, 인덱스가 항상 손해라는 말도 사실이 아니다. 인덱스를 놔둔 채 통계를 고쳤더니 옵티마이저는 인덱스를 쓰지 않기로 하고 46밀리초를 냈는데, enable_seqscan = off 로 억지로 쓰게 했더니 33밀리초로 오히려 빨랐다. 표가 전부 shared_buffers 안에 있었고 찾는 행이 물리적으로 뭉쳐 있었기 때문이다. "인덱스가 있으면 빠르다" 도 "큰 결과에는 인덱스가 손해다" 도 그 자리에서 재 보기 전에는 알 수 없다. 계획의 예상과 실제를 먼저 맞춰 놓고, 그다음에 재는 것이 순서다.

표는 왜 갱신했는데 커지나

PostgreSQL 은 행을 고치지 않는다. 새 버전을 쓰고 옛 버전에 "이 트랜잭션 이후로는 안 보임" 표시를 남긴다. 그래서 UPDATE 는 사실상 삽입이고, DELETE 도 자리를 비우지 않는다. 남은 옛 버전이 죽은 튜플 이다.

한 컬럼만 바꾸는 update 를 6만 행에 돌린 결과다.

갱신 전   9,945,088 바이트갱신 후  17,358,848 바이트     n_dead_tup = 60000

한 글자도 늘리지 않았는데 표가 1.7배가 됐다. 이걸 되돌리는 일이 VACUUM 이고, 평소에는 autovacuum 이 알아서 한다.

지평선 — vacuum 이 못 치우는 이유

그런데 VACUUM 을 직접 돌려도 줄지 않는 경우가 있다. vacuum 이 스스로 이유를 말해 준다.

tuples: 0 removed, 131456 remain, 60000 are dead but not yet removableremovable cutoff: 970, which was 10 XIDs old when operation ended

"지울 것이 없다" 가 아니라 "아직 지울 수 없다" 이다. 아직 열려 있는 트랜잭션이 그 옛 버전을 보게 될지도 모르기 때문이다. 그 경계가 removable cutoff 이고, 그 값은 살아 있는 가장 오래된 트랜잭션이 정한다.

누가 잡고 있는지 찾을 때 흔히 backend_xmin 을 훑는데, 그것만 보면 못 찾는다.

 pid  | application_name |        state        | backend_xid | backend_xmin------+------------------+---------------------+-------------+--------------  941 | nightly-batch    | idle in transaction |         932 |  947 | api-order        | active              |         933 |          932  949 | api-cart         | active              |         934 |          932 1050 | psql             | active              |             |          932

cutoff 를 잡고 있는 것은 941 인데 941 의 backend_xmin 은 비어 있다. 자기 트랜잭션 id 인 backend_xid 932 로 지평선을 잡고 있기 때문이다. 반대로 932 를 backend_xmin 에 달고 있는 947, 949, 1050 은 941 이 아직 살아 있다는 사실을 자기 스냅샷에 반영했을 뿐이다. 이들을 끊어도 지평선은 움직이지 않는다.

찾는 방법은 하나다 — 가장 오래된 backend_xid.

select pid, application_name, backend_xid, now() - xact_start as age  from pg_stat_activity where backend_xid is not null order by age(backend_xid) desc limit 1;

그리고 941 은 앞의 잠금 사슬에서 이미 본 그 세션이다. 증상은 둘인데 원인은 하나다. 표가 안 줄어드는 원인이 그 표 안에 없을 수 있다는 것 — 이 코스가 가르치려는 것이 그것이다.

현장에서 만나는 모습

첫째, 대량 적재 스크립트의 마지막 줄은 ANALYZE 다. autoanalyze 는 결국 돌지만 언제 돌지는 아무도 모르고, 그 몇 분이 장애 시간이다. 적재한 사람이 그 자리에서 한 줄 넣는 것이 가장 싸다.

둘째, 원인이 표 밖에 있을 수 있다는 것을 기억한다. "이 표만 유난히 커진다" 는 신고를 받으면 그 표의 인덱스나 적재 패턴부터 뒤지게 되는데, 이 사고에서는 표에 아무 잘못이 없다. n_dead_tup 이 크고 VACUUM 이 not yet removable 이라고 답하면 그때부터는 표가 아니라 트랜잭션 목록을 봐야 한다.

셋째, n_dead_tup 은 통계이지 실측이 아니다. 통계 수집기가 갱신하는 값이라 실제와 어긋날 수 있다. 확실히 알고 싶으면 VACUUM (VERBOSE) 의 출력을 읽는 편이 낫다. 그 출력에는 cutoff 까지 함께 나와서 "왜 못 치웠는지" 를 한 번에 알려 준다.

다음 실습에서 할 것

잠금 사슬, 무너진 실행 계획, 부풀어 오른 표 — 세 증상이 동시에 나 있는 데이터베이스를 받아서 각각의 증거를 숫자로 뽑고, 마지막에 진단서 한 장으로 묶는다. 채점기는 적어 낸 숫자를 살아 있는 데이터베이스에서 다시 뽑아 맞춰 보므로 어떻게 찾았는지는 자유이고 진단이 맞아야 통과한다.