Diagnose a Database That Is Up

목표

세 가지 증상이 동시에 나 있는 데이터베이스를 받아, 각각의 원인을 숫자로 가려내고 마지막에 진단서 한 장으로 묶습니다. 고치는 것이 아니라 진단이 이 실습의 과제입니다.

왜 중요한가

데이터베이스는 대개 죽지 않습니다. 떠 있는 채로 이상해지고, 그때 증상이 보이는 자리와 원인이 있는 자리는 거의 언제나 다릅니다. 막힌 세션이 가리키는 pid 는 자기도 막힌 피해자이고, 느려진 쿼리의 원인은 그 쿼리에 없고, 안 줄어드는 표의 원인은 그 표 안에 없습니다.

그래서 이 실습은 명령을 외우게 하지 않습니다. 증거를 뽑아 적어 내는 일만 시키고, 채점기는 그 숫자를 지금 살아 있는 데이터베이스에서 다시 뽑아 맞춰 봅니다. 어떻게 찾았는지는 자유이고, 진단이 맞아야 통과합니다.

환경

이 파드는 postgres 계정으로 돕니다. psql 만 치면 labdb 에 바로 붙습니다.

export PATH=/usr/lib/postgresql/16/bin:$PATH
export PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdb
psql

산출물은 전부 /root/inc/ 아래에 둡니다. mkdir -p /root/inc 를 먼저 하세요.

세션 여러 개가 필요합니다. 터미널이 하나뿐이므로 백그라운드로 띄웁니다. 아래 형태가 트랜잭션을 연 채 노는 세션을 만듭니다 — stdin 을 열어 두면 psql 이 다음 명령을 기다리며 idle in transaction 으로 남습니다.

( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } \
    | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

세 리다이렉션을 모두 붙이세요. 하나라도 빠지면 셸이 그 세션을 기다립니다.

단계

1. 사고 재현 + 활동 스냅샷 → /root/inc/01-activity.txt
2. 잠금 사슬의 끝 → /root/inc/02-root.txt
3. 대기가 먹는 커넥션과 lock_timeout → /root/inc/03-waiting.txt
4. 무너진 실행 계획 → /root/inc/04-plan.txt
5. 통계를 고친 뒤 → /root/inc/05-stats.txt
6. 죽은 튜플과 vacuum 의 대답 → /root/inc/06-bloat.txt
7. 지평선을 쥔 세션 → /root/inc/07-horizon.txt
8. 진단서 → /root/inc/08-report.md

참고

• 사고를 낸 세션들은 끝까지 살려 두세요. 채점이 살아 있는 데이터베이스와 대조하기 때문에, 중간에 끊으면 앞 단계가 다시 채점되지 않습니다. 실제 조치(누구를 끊을지)는 8단계의 진단서에 글로 적습니다.
• events 표는 통계 자동 갱신만 꺼 두었습니다. 실무에서 이 상태는 대량 적재 직후 autoanalyze 가 돌기 전 몇 분 동안 저절로 생기는데, 실습 중에 그 몇 분이 지나가 버리면 4단계를 볼 수 없기 때문입니다. autovacuum 자체는 켜져 있습니다.
• 4단계의 집계 쿼리는 몇 초 걸립니다. 느린 것이 정상이고, 그 시간이 증거입니다.

사고를 재현하고 한 장으로 찍는다

사고 재현 + 활동 스냅샷 → /root/inc/01-activity.txt

먼저 사고를 만듭니다. events 표에 tenant 41 을 6만 건 적재하되 ANALYZE 는 하지 않고, orders 를 고친 뒤 커밋하지 않는 세션 하나와 같은 행을 건드리는 세션 둘을 띄우세요.

트랜잭션을 연 채 노는 세션은 stdin 을 열어 두면 만들어집니다: ( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

그다음 pg_stat_activity 를 통째로 /root/inc/01-activity.txt 에 저장합니다. state·wait_event_type·pg_blocking_pids 를 꼭 포함하세요.

사슬의 끝을 찾는다

잠금 사슬의 끝 → /root/inc/02-root.txt

막힌 세션이 가리키는 pid 는 그 자신도 막힌 피해자일 수 있습니다. 남을 막고 있으면서 자기는 아무에게도 막혀 있지 않은 pid 를 찾으세요.

unnest(pg_blocking_pids(pid)) 로 펼친 뒤 cardinality(pg_blocking_pids(b)) = 0 인 것만 남기면 됩니다.

/root/inc/02-root.txt 에 root_pid= · root_app= · root_state= 세 줄을 적으세요. 채점기는 이 세 값을 지금 살아 있는 데이터베이스에서 다시 뽑아 맞춰 봅니다.

대기가 무엇을 먹고 있나

대기가 먹는 커넥션과 lock_timeout → /root/inc/03-waiting.txt

막혀 있는 세션의 수를 세고(cardinality(pg_blocking_pids(pid)) > 0), show max_connections 와 함께 적으세요. 그 세션들은 느린 것이 아니라 커넥션을 하나씩 물고 멈춰 있습니다.

그다음 set lock_timeout = '1s'; 를 걸고 같은 행을 건드려 보세요. 무한히 기다리는 대신 1초 만에 오류로 돌아옵니다. 그 오류 줄을 그대로 파일에 남기세요.

파일: /root/inc/03-waiting.txt

안 바뀐 쿼리가 느려졌다

무너진 실행 계획 → /root/inc/04-plan.txt

events 를 customers 와 조인해 tenant 41 을 집계하는 쿼리를 EXPLAIN (ANALYZE, BUFFERS) 로 돌리세요. 몇 초가 걸립니다 — 그게 이 단계의 요점입니다.

예상과 실제 행 수는 병렬을 끄고 따로 재는 편이 정확합니다. loops 가 1이 아니면 표시된 값이 1회 평균이기 때문입니다: set max_parallel_workers_per_gather = 0; 뒤에 explain (analyze) select count(*) from events where tenant_id = 41

/root/inc/04-plan.txt 에 est_rows= · actual_rows= · exec_ms= 를 적고 계획 전문을 함께 남기세요. 이 단계에서는 아직 ANALYZE 를 돌리지 마세요.

고칠 것은 쿼리가 아니었다

통계를 고친 뒤 → /root/inc/05-stats.txt

인덱스를 만들지 말고 analyze events 만 하세요. 그리고 4단계와 똑같이 다시 재세요.

/root/inc/05-stats.txt 에 est_rows_after= · exec_ms_after= · join_after= 를 적고 계획 전문을 남기세요. 채점기는 스스로 계획을 다시 뽑아 옵티마이저의 예상이 실제와 맞는지 확인하므로, 통계를 고치지 않으면 무엇을 적어도 통과하지 못합니다.

갱신했는데 표가 커졌다

죽은 튜플과 vacuum 의 대답 → /root/inc/06-bloat.txt

update events set kind = 'view' where tenant_id = 41 처럼 한 컬럼만 바꾸고, 갱신 전후의 pg_total_relation_size('events') 를 바이트로 재세요. 그다음 vacuum (verbose) events 를 돌리면 vacuum 이 스스로 이유를 말해 줍니다.

/root/inc/06-bloat.txt 에 dead_tuples= · removable_cutoff= · size_before= · size_after= 를 적고 vacuum 출력을 함께 남기세요. cutoff 는 지어내면 안 됩니다 — 채점기가 지금 열려 있는 트랜잭션과 맞춰 봅니다.

청소를 막고 있는 것

지평선을 쥔 세션 → /root/inc/07-horizon.txt

6단계의 removable cutoff 를 붙잡고 있는 세션을 찾으세요. backend_xmin 만 훑으면 못 찾습니다 — 트랜잭션을 열어 놓고 노는 쓰기 세션은 xmin 이 비어 있고, 정작 지평선은 그 세션의 backend_xid 가 잡고 있습니다.

where backend_xid is not null order by age(backend_xid) desc limit 1 이 답을 줍니다.

/root/inc/07-horizon.txt 에 holder_pid= · holder_xid= · holder_app= 을 적으세요. holder_xid 는 6단계의 cutoff 와 같은 값이어야 하고, holder_pid 는 2단계에서 찾은 pid 와 비교해 보세요.

진단서 한 장

진단서 → /root/inc/08-report.md

앞 일곱 단계에서 뽑은 숫자를 묶어 /root/inc/08-report.md 에 진단서를 씁니다. 증상 · 원인 · 조치 절을 두고, 사슬의 끝 pid, vacuum 의 cutoff 값, 실제 행 수를 숫자 그대로 넣으세요.

핵심은 세 증상 중 둘이 같은 원인이고 하나는 별개라는 것을 갈라 놓는 일입니다. 재발 방지에는 lock_timeout 을 반드시 언급하세요. 채점기는 진단서에 적힌 숫자를 지금 살아 있는 데이터베이스와 맞춰 봅니다.