정렬이 디스크로 넘어갈 때 — work_mem 과 external merge

정렬이 들어간 쿼리가 평소엔 빠른데 가끔 수십 배로 느려져요. 같은 쿼리, 같은 인덱스, 같은 데이터인데요.

느려지는 날의 패턴을 보면 트래픽이 높은 시간대예요. 그런데 CPU 도 여유가 있고 디스크도 안 바쁜 것 같아요.

EXPLAIN 을 떠 보면 정렬 단계에 external merge 라는 말이 붙어 있어요. 빠를 때는 quicksort 였고요. 같은 정렬인데 방식이 달라진 거예요.

그리고 이 차이는 데이터가 아니라 동시에 몇 개가 돌고 있느냐로 갈려요. 그래서 혼자 진단할 때는 절대 재현이 안 되고, 재현이 안 되니 원인도 못 찾습니다.

왜 그런가

정렬은 메모리 안에서 하는 게 압도적으로 빨라요. 데이터가 작업 메모리 안에 들어가면 메모리에서 정렬하고 끝내요.

넘치면 방식이 바뀌어요. 데이터를 조각으로 나눠 각각 정렬해서 디스크에 쓰고, 그 조각들을 다시 합쳐요. 이걸 외부 병합 정렬이라고 해요. 디스크를 여러 번 읽고 쓰니 느려지죠. 수십 배 차이가 나는 게 이상하지 않아요.

여기서 중요한 게 작업 메모리의 단위예요. 이 값은 서버 전체가 아니라 정렬 작업 하나당 허용되는 크기예요. 쿼리 하나가 정렬을 두 번 하면 두 배를 쓸 수 있고, 동시에 열 개의 쿼리가 정렬하면 열 배가 잡혀요.

그래서 트래픽이 높을 때만 느려지는 현상이 설명돼요. 동시 실행이 늘면 전체 메모리 압력이 커지고, 서버가 스왑을 하거나 캐시가 밀려나면서 각자의 정렬이 더 자주 디스크로 넘어가요.

값을 크게 잡으면 해결될 것 같지만 위험해요. 동시성이 높은 순간에 곱해지면서 서버 메모리를 초과할 수 있거든요. 그러면 정렬이 느려지는 정도가 아니라 서버가 죽습니다.

정렬만의 문제도 아니에요. 해시 조인이나 그룹화도 같은 메모리를 쓰고, 넘치면 같은 방식으로 디스크로 넘어가요.

코드로 보면

정렬 자체를 없애는 게 최선이다:

· 1) 인덱스로 정렬을 없앤다 — 근본 해결
✓ CREATE INDEX idx ON posts (status, created_at DESC);
✓ SELECT ... WHERE status='paid'
✓ ORDER BY created_at DESC LIMIT 50;
✓ → 정렬 단계가 계획에서 사라진다

· 2) 정렬 대상을 줄인다 — 필요한 컬럼만
✕ SELECT * FROM posts ORDER BY created_at;
✓ SELECT id, created_at FROM posts ORDER BY created_at;
· 행 폭이 좁아지면 같은 메모리에 더 들어간다

· 3) 그래도 큰 정렬이 필요하면 그 세션만 올린다
✓ SET LOCAL work_mem = '256MB';   -- 트랜잭션 한정
✕ 전역으로 올리면 동시성만큼 곱해져 서버가 죽는다

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

해결 순서가 있어요. 메모리 설정을 만지는 건 마지막이에요.

첫째는 정렬을 없애는 거예요. 인덱스가 이미 정렬된 순서를 갖고 있으면 정렬 단계가 계획에서 사라져요. ORDER BY 와 인덱스의 컬럼 순서·방향이 맞아야 하고, 앞서 본 복합 인덱스 순서 규칙이 그대로 적용돼요. LIMIT 이 붙어 있으면 이득이 훨씬 커요 — 정렬 없이 앞에서 몇 개만 떼면 되니까요.

둘째는 정렬할 양을 줄이는 거예요. 필요 없는 컬럼을 빼면 행 하나가 차지하는 폭이 줄어 같은 메모리에 더 많이 들어가요. 큰 텍스트 컬럼이 섞여 있으면 특히 효과가 큽니다. 조건을 먼저 걸어 대상 행을 줄이는 것도 같은 방향이고요.

셋째가 메모리 조정인데, 전역이 아니라 필요한 세션이나 트랜잭션에서만 올리세요. 야간 리포트나 배치처럼 동시 실행이 적고 큰 정렬이 필요한 작업에는 합리적이에요. 온라인 트래픽 경로에서 전역으로 올리는 건 위험합니다.

측정도 중요해요. 얼마나 자주 디스크로 넘어가는지 지표로 볼 수 있어요. 임시 파일 생성 횟수와 크기가 그 신호예요. 이게 늘고 있으면 조용히 느려지고 있다는 뜻이에요.

그리고 정렬이 필요한 이유를 다시 묻는 것도 방법이에요. 정렬 결과를 정말 다 쓰나요? 상위 몇 개만 쓴다면 LIMIT 을 명시하는 것만으로 계획이 달라져요. 페이지네이션이라면 커서 방식이 정렬 부담을 크게 줄이고요.

처방

1. 인덱스로 정렬을 해결할 수 있는지 먼저 보세요. 계획에서 정렬 단계가 사라지는 게 최선입니다.
2. 정렬 대상의 폭을 줄이세요. 필요한 컬럼만 가져오고, 큰 텍스트는 정렬 뒤에 조인으로 붙이세요.
3. LIMIT 을 명시하세요. 상위 몇 개만 필요하면 옵티마이저가 전체 정렬을 피할 수 있어요.
4. 작업 메모리는 전역이 아니라 세션·트랜잭션 단위로 올리세요. 배치나 리포트에만요.
5. 임시 파일 생성을 지표로 보세요. 늘어나면 정렬이나 해시가 디스크로 넘어가고 있다는 뜻이에요.
6. 큰 정렬이 필요한 작업의 동시 실행을 제한하세요. 리포트 쿼리 열 개가 동시에 돌면 메모리가 열 배로 잡힙니다.
7. 임시 파일이 쓰이는 디스크의 성능도 확인하세요. 어차피 넘어갈 수밖에 없는 작업이라면 그 디스크가 빠른 게 도움이 됩니다.
8. 큰 정렬이 필요한 작업은 온라인 경로에서 빼세요. 리포트나 내보내기는 복제본이나 별도 인스턴스로 보내면, 넘치더라도 서비스 트래픽을 안 건드립니다.

지금 확인해 보세요

정렬이 디스크로 넘어가는가:

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ORDER BY ...;
✓ Sort Method: quicksort  Memory: 25kB     ← 메모리에서 끝
✕ Sort Method: external merge  Disk: 240MB ← 디스크로 넘어감

· 얼마나 자주 넘어가는지 — 조용히 나빠지는 걸 잡는다
SELECT datname, temp_files, pg_size_pretty(temp_bytes)
  FROM pg_stat_database WHERE datname = current_database();
✕ 이 값이 계속 늘면 정렬·해시가 상시로 넘치고 있다

· 현재 설정
SHOW work_mem;

· 정렬이 아예 사라졌는지 확인 (인덱스로 해결한 경우)
✓ 계획에 Sort 노드가 없어야 성공이다

핵심 한 줄정렬은 없애는 게 최선이다. 메모리를 올리는 건 곱셈을 감당할 때만.