쿼리 실행 계획으로 살펴보는 성능 최적화

들어가며

당연하게도 백엔드 시스템에서 성능을 좌우하는 주요 요소 중 하나는 I/O다.

특히 데이터베이스는 이러한 I/O 비용이 크게 발생할 수 있는 부분이라 생각한다. 따라서 백엔드 시스템을 개발하며 지표를 분석하고 성능 개선을 시도하다 보면, 데이터베이스가 주요 병목 지점으로 드러나는 경우가 경험상 정말 많았다.

특히 Postgres이나 MySQL과 같은 행 지향 데이터베이스는 특정 조건을 만족하는 소수의 행을 조회하거나 갱신하는 트랜잭션 중심의 작업에 적합하다.

하지만 쿼리가 비효율적이라면, 실제로 필요한 데이터는 극히 일부임에도 수많은 행을 읽거나 불필요한 연산을 수행하게 된다. 이는 과도한 I/O나 메모리 사용등(후반부에 얘기할 예정)으로 이어져 데이터베이스가 가진 구조적 이점을 충분히 활용하지 못하게 만든다.

즉, 필요한 데이터에 얼마나 효율적으로 접근하냐가 가장 큰 과제이다.

실행 계획 분석

Scan Type과 인덱스 활용

쿼리의 성능을 분석할 때는 상황에 따라 확인해야 할 지점이 다르지만, 보통 먼저 Scan Type을 확인하여 데이터에 어떤 방식으로 접근하고 있는지 살펴본다.

또한 해당 인덱스가 실제 쿼리에 제대로 활용되고 있는지도 확인해야한다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'COMPLETED';

Index Scan using idx_orders_user_id on orders
  Index Cond: (user_id = 42)
  Filter: (status = 'COMPLETED')
  Rows Removed by Filter: 9990
  actual rows: 10

user_id = 42에 해당하는 10,000개 행을 인덱스로 찾았지만, status 는 인덱스로 찾지 못했다.

데이터 선택도

물론 인덱스가 항상 효율적인 것은 아니다. 쿼리 조건의 선택도에 따라 효율적인 접근 방식이 달라진다.

WHERE id = 1처럼 전체 데이터 중 극히 일부만 조회하는 경우에는 Index Scan이 효과적일 수 있다. 반면 SELECT * FROM orders처럼 대부분의 데이터를 읽어야 하는 경우에는 인덱스를 통해 개별 행에 접근하는 것보다 테이블을 순차적으로 읽는 Sequential Scan이 더 효율적일 수 있다.

따라서 단순히 인덱스 사용 여부만으로 쿼리 성능을 판단해서는 안 된다.

통계 정보 추정

또한 특히 대용량 데이터를 저장한 데이터베이스에서 자주 발생할 수 있는 문제 중 하나는 옵티마이저가 추정한 행 수와 실제 처리 행 수가 크게 다른 경우이다.

통계 정보가 실제 데이터 분포를 정확하게 반영하지 못하면 옵티마이저가 잘못된 실행 계획을 선택할 수 있다.

postgres의 경우 통계 정보를 생성할 때 모든 데이터를 조사하는 것이 아니라, 샘플링을 통해 일부 데이터를 추출하고 이를 바탕으로 전체 데이터의 분포를 추정하므로 표본이 실제 데이터 분포를 충분히 반영하지 못할 수 있다.

이를 위해서 샘플링 크기를 늘리거나, 여러 컬럼의 관계를 함께 수집하는 방법이 있긴하다.

Estimated Rows : 4,624,111
Actual Rows    : 419

이 예시처럼 예상 행 수가 실제보다 약 11,000배 크게 추정되는 경우가 있다.

통계 정보 갱신

이러한 추정 오류로 인해 인덱스 조회가 효율적인 상황에서 인덱스가 없거나 통계 오류로 Sequential Scan을 선택하거나, 적절하지 않은 Join 방식 및 순서를 선택할 수 있다.

따라서 예상 행 수와 실제 행 수의 차이가 크다면 통계 정보가 최신인지 확인하고, 필요하다면 ANALYZE를 통해 통계 정보를 갱신하는 등으로 해결한다.

-- 특정 컬럼의 통계 수집 목표값 증가
ALTER TABLE works_authorships
ALTER COLUMN work_id SET STATISTICS 1000;

-- 통계 정보 재수집
ANALYZE VERBOSE works_authorships;

JOIN 전략

통계 정보의 추정 오차가 특히 큰 영향을 미치는 연산 중 하나가 JOIN이다. postgres의 경우 각 테이블의 예상 행 수와 JOIN 조건의 선택도 등을 바탕으로 Nested Loop Join, Hash Join, Merge Join 중 적절한 전략을 선택한다.

따라서 예상 행 수가 실제와 크게 다르다면 비효율적인 JOIN 전략이 선택될 수 있다. 개인적으로 JOIN 연산이 쿼리 실행 시간에서 가장 큰 비중을 차지하는 경우를 너무나도 많이 경험했다.

주요 JOIN 방식 비교

세 가지 JOIN 방식을 단순화하면 다음과 같다.

Nested Loop Join: 외부 테이블의 각 행에 대해 내부 테이블에서 일치하는 데이터를 반복적으로 탐색한다. 이때 내부 테이블의 JOIN 키에 적절한 인덱스가 있다면 탐색 비용을 크게 줄일 수 있다.
Hash Join: 한쪽 입력의 JOIN 키로 해시 테이블을 만들고, 다른 쪽 입력의 키로 조회한다. 반복 탐색을 줄일 수 있지만 해시 테이블을 만드는 비용과 메모리가 필요하다.
Merge Join: 양쪽 입력을 JOIN 키 순서로 정렬한 뒤 병합한다. 인덱스로 정렬된 입력을 읽을 수 있다면 별도의 Sort 연산을 생략할 수 있다.

아래에서 방식을 바꾸고 다음 단계를 눌러 탐색 과정을 비교해 보자.

Nested Loop는 입력 A의 각 키를 입력 B와 반복 비교한다. Hash는 해시 테이블을 만들고 키로 조회한다. Merge는 양쪽을 정렬한 뒤 작은 키 쪽을 이동한다. 세 방식 모두 같은 키 1, 3, 5를 찾는다.

인덱스와 통계 정보가 JOIN에 미치는 영향

즉, 적절한 인덱스가 없다면 Nested Loop Join에서 내부 테이블을 반복적으로 탐색하는 비용이 커질 수 있으며, 이 경우 Hash Join과 같은 다른 전략이 더 효율적일 수 있다.

또한 통계 정보의 부정확성으로 인해 실제 데이터 규모에 적합하지 않은 JOIN 전략이 선택된다면 쿼리 실행 시간이 크게 증가할 수 있다.

메모리 사용

또한 postgres기준으로 특히 대량의 데이터에서 신경써야할 부분이 바로 work_mem이다.

우리가 Hash Join을 쓰기 위해 해시 테이블을 만든다고 가정해보자.

먼저 JOIN 키에 해시 함수를 적용하고, 계산된 해시값에 따라 데이터를 해시 테이블의 버킷에 저장한고, 이후 다른 쪽 입력을 순회하면서 동일한 해시 함수를 적용한다. 문제는 해시 테이블의 크기가 사용 가능한 메모리를 초과하는 경우다.

해시 테이블 전체를 메모리에 유지하기 어려우면 데이터를 여러 개의 Batch로 나누어 처리한다. 메모리에서 즉시 처리할 수 없는 데이터는 임시 디스크 파일에 기록하여 나중에 필요할 때 가져오는 방식인 것이다.

연산 메모리가 차면 나머지 데이터를 임시 디스크에 나누어 기록하며 추가 I/O가 발생한다.

Hash
  ...
  Batches: 1024
  ...

Buffers: temp written=964595

Batches는 해시 테이블을 처리하기 위해 나눈 배치 수를 나타낸다.

work_mem의 기본값과 적용 범위

여기서 이 메모리에 해당하는 부분을 work_mem이라고 부르는데, 진짜 문제는 postgres의 경우 이 공간이 기본 4MB라는 것이다. 따라서 대용량 데이터에서는 엄청난 횟수의 Disk Spill이 발생하여 심각한 성능 손상을 불러 일으키게 된다.

work_mem이 작은 이유는 work_mem이 쿼리 하나에 적용되는 제한이 아니라, 쿼리 내부의 개별 연산에 적용되기 때문이다.

이건 JOIN에만 해당되는 내용이 아닌 정렬이 필요한 ORDER BY에도 해당된다.

work_mem은 세션 단위로 변경할 수 있지만, SET LOCAL을 사용하면 특정 트랜잭션이 실행되는 동안에만 값을 변경할 수도 있다.

BEGIN;

-- 현재 트랜잭션에만 적용
SET LOCAL work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    user_id,
    COUNT(*)
FROM orders
GROUP BY user_id
ORDER BY COUNT(*) DESC;

COMMIT;