Postgresql 슬로우 쿼리에 대처하기

망7H·2021년 4월 7일

어느날 실서비스 환경의 특정 경로를 들어가면 해당 경로에서 비동기적으로 목록을 조회할 때, 로딩바가 뱅글뱅글 돌기만하고 데이터를 조회해오지 못하는 상황이 발생했습니다.
원인은 금방 찾아졌습니다.
사용자와 서버 사이의 Connection timeout보다 쿼리 수행에 걸리는 시간이 오래 걸리고 있었습니다.
쿼리 수행에 걸리는 시간은 대략 900~1000s 정도 였고, 악성 슬로우 쿼리였습니다.
그 슬로우 쿼리는 테이블 구조상 많은 테이블을 LEFT JOIN하여 사용하여야 하였고, 그 밖에도 SELECT할 COLUMN에도 여러가지 작업이 수반되었고, WHERE 절에 사용되는 검색 조건도 많았습니다.
Postgresql의 실행계획을 분석하여 어느 포인트가 가장 문제인지 찾아보면서 결과적으로 문제는 해결하였으나, 실행계획을 읽고 해석하는 능력이 부족하다 판단하여 이번 포스팅을 준비했습니다.

1. 슬로우 쿼리 확인하기

1) 슬로우 쿼리 발생시, 간단한 로그 추가하기

먼저, 서비스 과정에서 슬로우 쿼리가 발생했는지 로그가 남도록 해야 합니다.
별다른 수정을 하지 않았다면 /var/lib/pgsql/data/postgresql.conf 파일에 아래와 같은 옵션을 변경해주면 됩니다.
단위는 ms 이므로 10s를 넘어가는 경우 슬로우 쿼리 발생 로그를 남기기 위해 10000으로 지정할 수 있습니다.

log_min_duration_statement = 10000

이와 같이 설정해두면 슬로우 쿼리 발생 시,
duration: 139530.323 ms statement: SELECT ~~ 와 같은 로그가 /var/lib/pgsql/data/pg_log 경로에 남아 있는 것을 확인할 수 있습니다.
하지만 이정도 옵션으로는 어떤 쿼리의 어떤 포인트가 핵심 원인인지 유추하기 어렵습니다.
(※ 실제 서비스에서는 설정파일(postgresql.conf)이나 로그가 남는 경로(pg_log)가 다를 수 있습니다.)

2) 슬로우 쿼리 발생시, 실행계획 로그 추가하기

슬로우 쿼리가 발생했을 때, 실행계획을 로그에 추가해주는 것은 당시의 실행계획을 볼 수 있다는 장점은 있으나, EXPLAIN ANALYZE 명령의 특성상 쿼리를 한번 더 실행하는 것이므로 응답 속도가 어느정도 느려질 수 있다는 단점이 있습니다.
이렇게 쿼리 실행계획을 발생 시점에 남기는 것은 /var/lib/pgsql/data/postgresql.conf 파일에 아래와 같은 옵션을 변경해주면 됩니다.

session_preload_libraries = 'auto_explain'

아쉽게도 저희 실서비스에서는 해당 옵션을 사용하고 있지 않았기에 실행계획에 대한 정보는 없었습니다.
하지만, 어떤 쿼리가 슬로우 쿼리를 발생시키는지까지 확인이 된 상태이니 직접 실행계획을 돌려볼 수는 있었습니다.

★ 설정파일을 변경한 경우에는 서비스를 재시작 시키는 것에 유의하세요. ★

2. 실행계획에 관하여

먼저 실행계획을 돌려 보기 위해서는 EXPLAIN 명령을 쿼리의 앞에 사용하면 됩니다.
이때, 추가 옵션으로 여러가지가 제공됩니다.

1) 실행계획 옵션

실행계획 옵션은 Postgresql 공식 문서에 설명이 잘 되어 있습니다.
저는 자주 쓰는 옵션들에 대해 간단히 정리해보겠습니다.

ANALYZE (default: false)
EXPLAIN과 가장 많이 쓰는 옵션입니다. 쿼리를 직접 실행하여 실제 실행 계획을 뽑습니다.
ANALYZE 옵션은 실제 수행이 되는 것이므로, 실제 데이터에 영향을 미칠 수 있는 DML(INSERT, UPDATE, DELETE)을 테스트 할 경우에는 트랜잭션의 시작(BEGIN)을 명시적으로 선언하고 EXPLAIN ANALYZE를 수행하는 것이 좋습니다.
테스트 할 쿼리가 끝났다면 트랜잭션의 롤백(ROLLBACK) 명령을 선언해주시면 됩니다.

옵션의 사용 방식은 쿼리의 맨 앞부분에 EXPLAIN ANALYZE를 붙여 쿼리를 작성하시면 됩니다.

EXPLAIN ANALYZE
SELECT * FROM t_sport WHERE teem_name = 'fc_seoul';

COSTS (default: true)
EXPLAIN을 쓰면 알아서 사용되는 옵션입니다.
단계별 실행 계획에서 예측된 시작 비용과 총 비용을 보여주며,
로우의 예측된 수과 한 로우의 크기(예측값)도 보여줍니다.
FORMAT (default: TEXT)
※ Postgresql 9.x 이후의 버전에서 사용가능합니다.
출력 양식을 지정하여 보기 좋게 만들어주는 옵션입니다.
설정 가능한 양식은 TEXT, XML, JSON, YAML 등이 있습니다.
아래와 같이 양식을 지정해줄 수 있습니다.

EXPLAIN (FORMAT YAML)
SELECT * FROM t_sport WHERE teem_name = 'fc_seoul';

TIMING (default: true)
단계별 실행 계획에서 실제 시작시간과 총 소요된 시간을 보여줍니다.
이 옵션은 실제 소요된 데이터를 보여주기 ANALYZE 옵션과 같이 사용해야만 기능이 동작됩니다.

위 4가지 옵션들 외에도 VERBOSE(default: false), BUFFERS(default: false)와 같은 옵션이 있으니 학습에 참고해주세요.

2) 실행 계획 읽는 순서

실행계획을 읽는 순서는 작성이 잘 된 포스팅이 있어서 가져왔습니다.
아래와 같은 쿼리를 수행시켰다고 가정하겠습니다.

-- SQL
EXPLAIN
SELECT *
FROM board_data
    INNER JOIN users ON board_data.user_id = users.id
    INNER JOIN board ON board_data.board_id = board.id
WHERE board_data.board_data_status = 'AVAILABLE'
ORDER BY board_data.id DESC;

실행계획은 아래와 같이 나왔다고 되어 있습니다.

-- 실행계획
Sort  (cost=27.82..27.82 rows=1 width=4814) // 1)
  Sort Key: board_data.id DESC
  -> Nested Loop  (cost=0.28..27.81 rows=1 width=4814) // 2)
     -> Nested Loop  (cost=0.14..19.42 rows=1 width=3766) // 3)
        -> Seq Scan on board_data  (cost=0.00..10.75 rows=1 width=1145) // 4)
             Filter: ((board_data_status)::text = 'AVAILABLE'::text)
        -> Index Scan using users_pkey on users  (cost=0.14..8.15 rows=1 width=2621) // 5)
             Index Cond: (id = board_data.user_id)
     -> Index Scan using board_pkey on board  (cost=0.14..8.16 rows=1 width=1040) // 6)
          Index Cond: (id = board_data.board_id)

실행순서는 4 → 5 → 3 → 6 → 2 → 1 입니다. 실행 순서는 따라가보면 느낄 수 있습니다.

실행순서에 따라 해석해보자면,
4)에서 board_data_status(게시판 데이터 상태)가 AVAILABLE(이용가능)인 WHERE 조건을 먼저 수행했습니다.
이때, Seq Scan이 수행되었는데 Postgresql 옵티마이저는 상황에 따라 Index Scan과 Seq Scan 중 전체 데이터 중 5~10%를 가져오는 경우에는 Seq Scan이 빠르게 동작해서 Seq Scan을 쓴다고 합니다. Seq Scan은 Full Table Scan이라고도 합니다.
※ Index Scan의 경우에는 인덱스 테이블을 먼저 조회한 후, 데이터 테이블을 읽어야 하기 때문에 느릴 수 있다고 합니다.

5)에서 users 테이블의 INNER JOIN에 사용된 users_id를 인덱스(users_pkey)로 만들어서 스캔하였습니다.
Index Scan은 인덱스 테이블을 스캔한 후, 데이터 테이블에 엑세스하여 데이터를 가져옵니다.
Postgresql 옵티마이저는 가져오게 될 데이터(row)의 양이 많은 경우에는 Index Scan을 사용합니다.

3)에서 실질적으로 users 테이블의 INNER JOIN이 수행되었습니다.

6)에서 board 테이블의 INNER JOIN에 사용된 board_id를 인덱스(board_pkey)로 만들어서 스캔하였습니다.

2)에서 실질적으로 board 테이블의 INNER JOIN이 수행되었습니다.

마지막으로 1)에서는 조회된 데이터들에 대해 board_data 테이블의 id를 기준으로 내림차순 정렬을 수행하였습니다.

위 예시는 아쉽게도 COSTS 옵션만이 적용되어 있습니다.
ANALYIZE 옵션이 사용되지 않아서 TIMING 옵션에 대한 정보도 없다는게 아쉽네요.

3. 옵티마이저의 선택

1) Scan

Index Scan
Seq Scan
Index Only Scan
Index Only Scan은 인덱스 테이블에서 필요한 모든 정보를 얻을 수 있다면, 굳이 데이터 테이블에 엑세스 하지 않고 인덱스 테이블의 정보만을 반환하는 것입니다.

2) Join

Join에 관해 참고할만한 좋은 ★포스팅★입니다.

Nested Join
데이터의 양이 적은 경우 수행될 가능성이 높다.
Sort Merge Join
데이터의 양이 많은 경우 수행될 가능성이 높다.
Hash Join
메모리 안에서 처리가 가능한 경우 수행될 가능성이 높다.

포스팅을 위해 자료를 찾다보니, 좋은 자료를 많이 보게 되었네요.
쿼리를 직접 짜는 것을 않게 해줄 ORM과 같은 기술들이 생겨났지만,
여전히 '쌩' 쿼리를 직접 작성할 줄 알고 최적화 하는 능력, 슬로우 쿼리에 대처하는 것 그리고 옵티마이저의 선택들을 공부해두는 것은 중요하다 생각됩니다.