API 성능 향상기 - 풀스캔 조인의 무서운 위력

최근 사내에서 디비 리소스를 과부하시키던 쿼리가 존재했고 해당 쿼리가 포함된 API의 성능 향상 과정을 사내에서 발표하게되어 그 과정을 개인 블로그에도 따로 기록해 본다.

 

---

1. 이슈 발생

- 최근 사내에 디비 리소스 과부하에 대한 알람 경보가 울렸고 그 당시 쿼리 개선을 한참 공부하고 있던 터라 성능 개선을 해보기 위해 개인 시간에 사부작사부작 참전을 해보기로 마음먹게 되었다.

 

2. 로직의 대표적인 문제 점

해당 로직은 회사의 레거시 시스템에 포함된 어드민 조회 로직이었으며 아주 많은 문제를 담고 있었다. 그 중 대표적인 것들만 기록해본다.

1) N+1 로직 

- 해당 로직에는 N+1 로직이 존재하였으며 그로인 해 쿼리가 여러 번 불리는 문제가 존재했다. 따라서 해당 쿼리들을 in 절로 한꺼번에 부를 수 있도록 소스를 수정해 주었다.

- 레거시 시스템의 경우 Django orm을 사용하고 있었는데 prefetch_related로 손쉽게 해결을 완료하였다.

 

2) 풀스 캔 쿼리... 그리고 JOIN

- 쿼리 검색조건이 isDeleted 즉, 삭제 여부밖에 없는 페이지 네이션 쿼리로 풀스 캔(seq_can)을 타고 있었으며, 다음과 같이 2개의 테이블과 left 조인을 하고 있는 형태였다.

SELECT a,b,c
FROM A
         LEFT OUTER JOIN B ON ( A.b_id = B.id)
         LEFT OUTER JOIN C ON ( A.c_id = C.id) 
WHERE A.is_deleted = false
ORDER BY created_at desc
LIMIT 10;

당시 쿼리의 실행계획은 아래와 같다.

위에서 보면 알 수있 듯이 SEQ_SCAN 즉, 모든 테이블이 풀스 캔을 하며 서로 조인하고 있는 모습을 볼 수 있으며 코스트도 180만에 육박하는 걸 볼 수 있다.

3. 쿼리 개선 방법 

위 2-2) 쿼리를 살펴보면 성능 개선 포인트가 몇 가지 보인다.

1) 검색조건의 범위를 좁힌다

- 현재 검색조건은 is_deleted 밖에 없으므로 풀스 캔이 될 수밖에 없는 상황이다.

- 따라서 어드민 조회의 경우 default값으로 날짜 등의 칼럼으로 검색조건의 범위를 좁히는 게 굉장히 중요하다.

- 하지만 해당 건은 OM, PO님이랑 협의가 되어야 하는 상황이기에 우선 나중으로 미뤄 두었다.

 

2) 조인 쿼리를 분리한다.

- 해당 쿼리들은 접근하는 테이블이 다 풀스 캔을 타고 있으므로 드라이빙 테이블의 전체 로우와 나머지 테이블들을 조인한다고 생각하면 된다.

- 원본 쿼리를 자세히 보면 limit 이 10인 걸 확인할 수 있다

- 이때, 쿼리를 분리하면 나머지 테이블의 경우 각각 PK기반 in 절로 인덱스 기반 빠른 성능의 쿼리로 분리해서 뽑을 수 있다는 것을 알 수 있다.

3) 아이디 기반으로 pagination

- offset, limit이 아니라 PK기반으로 범위를 좁혀가며 무한 스크롤 형식으로 pagination 한다.

 

위의 3가지 방법 중 우선, 정책이나 UI 변경 없이 서버 개발단에서 바로 할 수 있는 2번을 채택하여 성능 개선을 시도해 보았다.

 

4. 개선 시도

위의 3가지 테이블을 다음과 같이 분리하였다.

1) A 테이블 쿼리

SELECT 필드 생략 
FROM A
WHERE is_deleted = false
ORDER BY created_at DESC
LIMIT 10;

실행계획

검색조건이 없다시피 해 여전히 풀스 캔(SEQ_SCAN)을 타지만 코스트의 경우 35만대로 확 줄은걸 볼 수 있다.

 

2) B테이블 쿼리

SELECT 필드 생략
FROM B
WHERE B.id IN (1, 2, NULL, 3, 4)
ORDER BY id DESC;

실행계획

 

역시나 인덱스 스캔을 타며 코스트도 낮은걸 확인할 수 있다.

3) C 테이블 쿼리

SELECT 필드생략
FROM C
WHERE C.id IN (NULL, 1)
ORDER BY id DESC;

실행계획

역시나 인덱스 스캔을 타며 코스트도 낮은걸 확인 할 수 있다... 2

 

소스의 경우 select_related에서 prefetch_related로 바꿔주었다.

Django orm에서 prefetch_related, select_related 차이점
- prefetch_related
   - one to one, many to one, one to many, many to many 다 사용 가능
   - join이 아니라 각각 쿼리를 조회하는 형식
- select_related
  - one to one, many to one 일 때만 사용
  - join으로 쿼리를 뽑아옴 
공식문서: https://docs.djangoproject.com/en/4.0/ref/models/querysets/

5. 추후 더 개선해 보면 좋은 점

1) 검색 조건 좁히기

- 특히나 어드민 목록 조회 페이지 네이션 페이지 같은 경우 성능을 위해 검색조건 고도화가 중요하가 중요하다고 생각이 든다. 

- 따라서, 디폴트 한 달로 날짜 조건을 걸어 주거나 하는 방법으로 검색조건을 좁혀 인덱스 스캔을 타게 만드는 것이 중요하다는 생각이 들었다.

- 이건 실제 cost가 얼마나 줄었을까 궁금하여 찾아보았는데 아래와 같이 21만으로 생각보다 cost가 줄지 않았다. 비트맵 인덱스 스캔을 타는 걸 확인할 수 있었는데 인덱스 정렬과 테이블 정렬 순서가 많이 다른 거 같다. 사내 공통적으로 created_at 칼럼에는 brin인덱스를 걸었는데 그게 상관관계가 있지 않을까 싶었다. 사실 이 부분은 잘 모르겠어서 좀 더 서치가 필요할 거 같다.

EXPLAIN (COSTS, VERBOSE, FORMAT JSON)
SELECT 필드생략
FROM A
WHERE is_deleted = false AND
      A.created_at BETWEEN '2022-07-07T00:27:50.275849'::timestamp AND '2022-08-05T00:27:50.275861'::timestamp)
ORDER BY created_at DESC
LIMIT 10;

실행계획

2) No offset을 사용하는 커서 기반 무한 스크롤 페이지 네이션

- offset, limit이 아닌 아이디 기반으로 범위를 좁혀가면서 페이지 네이션을 하는 방법, 자세한 내용은 아래 블로그 링크에서 확인해보자.

https://jojoldu.tistory.com/528 

1. 페이징 성능 개선하기 - No Offset 사용하기

- 하지만, 어드민의 경우 무한 스크롤로 구현된다면 사용성이 안 좋을 거 같다는 생각이 좀 들었다.

 

6. 참고

1) seq_scan이란

- Seq Scan은 테이블을 Full Scan 하면서 레코드를 읽는 방식이다. 인덱스가 존재하지 않거나, 인덱스가 존재하더라도 읽어야 할 범위가 넓은 경우에 선택한다.

2) Hash_join이란 

3) Bitmap index scan

- 테이블 랜덤 액세스 횟수를 줄이기 위해 고안된 방식으로 액세스 할 블록 번호순으로 정렬한 후 액세스 함.

- 즉, 한번 정렬이 필요하여 index scan보다는 cost가 더 들 거라는 생각이 든다.

- Index Scan 방식과 Bitmap Index Scan 방식을 결정하는 기준은 인덱스 칼럼의 상관관계(Correlation) 값이다.

- Correlation이란 인덱스 칼럼에 대한 테이블 레코드의 정렬 상태이다. (클러스터링 팩터)

- 클러스터링 팩터란 군집성 계수 즉, 데이터가 모여있는 정도를 말한다.

- index를 scan 하는 동안 방문하게 되는 테이블의 Data Block개수로 정한다.

- 인덱스 클러스터링 팩터가 좋다고 하면 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것을 의미한다.

- 즉, Correlation이 좋으면 Index Scan 방식을, 나쁘면 Bitmap Index Scan 방식을 사용한다

7. 결과

- 쿼리 코스트는 180만 에서 35만으로 줄었다.

- 또한 해당 API속도 같은 경우에도 전체 검색을 하는 경우 30배가 빨라 졌다 ㅎㅎ. 사실 검색조건이 없어 풀스 캔을 하고 있는 상황이라 여전히 API가 생각 보단 빠르지 않고, 페이지를 넘길 때마다 그렇게 좋은 속도가 나오진 못하지만, 이건 추후에 검색조건을 넣도록 합의를 본 상황이라 그때 더 좋아질 거라 예상이 든다.

- Kibana dashboard를 사용해서 아래와 같이 대시보드를 생성해봤는데 확실히 배포 이후 너무 크게 튀는 latency가 없었다. 

8. 회고

- 로직을 작성할 때는 역시 실행계획을 꼭 실행시켜보고 인덱스를 타도록 구성하는 게 좋다는 걸 해당 API를 개선하면서 다시 한번 느꼈다.

- 또한, 어드민 목록 조회 페이지 네이션 화면의 경우 반드시 디폴트 검색조건을 추가하고 검색 고도화를 하는 게 서비스 장애로 이어지지 않으려면 중요한 것 같다. 

- 마지막으로 역시 공부한 건 직접 적용해 봤을 때 제일 머릿속에 잘 들어오는 거 같다 ㅎㅎ 

•