Postgres 쿼리 1,000배 더 빠르게 만들기

(mattermost.com)

22P by xguru 6달전 | favorite | 댓글 3개

Mattermost는 Elasticsearch를 사용하여 데이터베이스 부하를 줄이고 훨씬 더 빠른 검색 결과를 제공함
- Elasticsearch가 제대로 동작하려면 검색 대상이 되는 모든 데이터를 색인해야 함
- 이미 색인된 데이터에 대해서는 이후 새 글과 파일의 색인 작업이 꽤 빠름
- 그러나, 아주 큰 데이터베이스(1억 개 포스트)를 처음부터 완전히 색인하는 것은 매우 느림(18시간동안 절반도 못하고 점점 느려짐)
데이터베이스 호출 별 소요 시간 그래프를 통해, PostStore.GetPostsBatchForIndexing 메서드의 SQL 쿼리가 문제인 것으로 파악됨
- 해당 쿼리는 기본적으로 creation timestamp 기준으로 포스트를 정렬하고, 주어진 timestamp보다 새로운 N개의 포스트를 반환함
- 이 쿼리를 반복 실행하여 모든 포스트가 색인될 때까지 indexing job이 수행됨
EXPLAIN (ANALYZE, BUFFERS)를 사용해 쿼리 실행 계획을 분석함:
- Posts 테이블에 인덱스 스캔 수행 시 Filter 조건 적용을 위해 4천만 블록을 처리하고 있음 (309GB)
- Channels 테이블과의 JOIN은 문제가 아님
- WHERE 조건의 OR 절 중 Posts.CreateAt > ?1 부분만 적용하면 훨씬 빨라짐 (30ms)
- 거기서 Posts.CreateAt = ?1 AND Posts.Id > ?2 조건을 적용하면 극도록 빨라짐 (0.047ms)
원인 파악:
- 원본 쿼리는 Posts의 모든 행을 훑으면서 Filter로 걸러내는 반면, 수정된 쿼리는 인덱스만 확인하고 필요한 행만 추출함
- 시간이 지남에 따라 쿼리가 점점 느려진 이유는 점점 더 많은 행을 걸러내야 했기 때문임
해결책:
- PostgreSQL의 row constructor comparison 기능을 활용하여 (Posts.CreateAt, Posts.Id) > (?1, ?2) 조건으로 변경
- 이렇게 변경한 쿼리는 34밀리초로 실행 시간이 대폭 줄어듦
- 그런데 MySQL에서는 변경된 쿼리가 오히려 더 느리게 동작함. MySQL에서는 원본 쿼리가 더 빨랐기에 DB 별로 다른 쿼리 사용하도록 코드 분기 처리함
배운 점들:
- EXPLAIN 시 BUFFERS 옵션을 항상 사용할 것
- Filter 대신 Index Cond를 활용하도록 할 것
- PostgreSQL과 MySQL은 거의 항상 다르게 동작함을 전제할 것
결론
- 최적화 작업을 통해 쿼리 실행 시간을 1000배 이상 줄이는 성과를 얻음
- 이 최적화는 Mattermost v9.7.0 및 v9.5 ESR 버전에 반영됨
- 최적화 작업을 통해 많은 것을 배울 수 있었음