[SuperBoard] 댓글 수 조회 쿼리 최적화

요약

• 게시판 기능을 실제로 서비스한다고 가정할 때, 댓글 수 표기 로직을 최적화 수행
• 단순 group by + 집계함수 + limit/offset의 문제점 파악
• 실행계획 분석
• 최종 결정 - from 절 서브쿼리 + index

커밋 및 수정 내역 PR - 링크

Board/feature/41 댓글 수 조회 최적화 by GoGradually · Pull Request #51 · GoGradually/SuperBoard

 

서론

기존에 구현해둔 게시판 기능을 좀 더 최적화해보기 위해, 실제로 서비스하는 상품이라고 가정하고 테스트 환경을 구성해보았다.

부디 기사 내용은 신경쓰지 않아주길 바란다.

네이버 뉴스의 조회수, 글, 댓글 비율을 참고해보았을 때, 게시글 5개의 조회수는 총합 29,283이었고, 댓글 수의 총합 558개였다.

충분한 더미 데이터를 넣어주기 위해, 게시글은 총 10,000개가 있다고 가정했고, 댓글 수는 위 지표를 빌려 게시글 당 100개의 댓글이 존재하도록 하였다.

성능 - 조회 로직 문제 발생

이렇게 더미 데이터를 준비해두고, 집의 데스크탑에 실행시켜둔 웹 서버에 접속해보았는데, 리버스 프록시로 띄워둔 nginx가 504 오류를 반환하는 사태를 맞이했다.

따라서 로컬에서 포스트맨을 설치해서 실행시간을 측정해보았고, 조회 당 8분 26초라는 충격적인 결과를 보게 되었다.

하지만 WAS 상에서는 문제가 없어 보였기에, 그 다음 레이어에 존재하는 DB에 문제가 있을것이라 유추해볼 수 있었다.

이 뿐만 아니라, WAS 상에서 에러 로그도 발견할 수 없었다.

따라서 로컬에서 DB의 슬로우 쿼리 로그를 확인해본 결과, 문제의 쿼리를 확인할 수 있었다.

 

위 쿼리는, 현재 페이지에 존재하는 게시글 제목과 그 댓글 수를 조회하는 로직이다.

과거 이러한 이유때문에 저런 쿼리가 탄생하게 되었다...

실행계획 확인

집에 있던 Real MySQL 8.0 도서를 꺼내, 차근차근 실행계획을 뜯어보기 시작했다.

주요 포인트는 다음과 같았다.

type - ALL

현재 실행 계획의 join type(access type)은 ALL이었고, 이는 테이블 풀 스캔을 의미했다.
정말 어쩔 수 없을때만 사용되어야 하는, 최악의 접근 방식이다.

key - NULL

후보 인덱스 자체가 없어, 매우 비효율적인 탐색을 하고 있다는 것을 짐작해볼 수 있었다.

extra

• Using temporary
  • group by 컬럼이 order by 컬럼과 달라, group by용 임시 테이블이 필요하다.
• Using filesort
  • 별도의 정렬 과정을 수행했다는 의미.
    • filesort라는 이름에 혼동 금지.
    • 두가지 모두 사용 가능하다.
      • 외부 합병 정렬을 사용
      • 퀵 소트 or 힙 소트를 사용
• Using where
  • 이는 아마 on절에 사용되지 않았을까 추정되었다.
  • 현재 type이 ALL이면서 조건절이 사용되고 있으므로, 모든 레코드들을 디스크I/O를 통해 가져와서 조건 검사를 하고 있는 것으로 보인다.
• Using join buffer(블록 네스티드 루프 조인)
  • 현재 조인에 Driven 테이블에 사용할 적절한 인덱스가 없어 쿼리가 비효율적으로 발생하고 있었다.

원인

가설 1 - SQL의 실행 순서에 따른 비효율적 테이블 탐색

나는 개인적으로 개발한 독특한 방식으로 쿼리의 실행 순서를 암기하고 있었다.

• 랮혼애ㅣ → 게토레이 (fowghsdol)
• from→on→where→groupby→having→select→distinct→orderby→offset/limit

이 쿼리의 실행 순서를 보았을 때, join 후 group by를 수행하는데, 정말 필요한 offset/limit 절에 들어갈 데이터를 먼저 구하기 전에, 집계함수를 구하기 위해 모든 레코드를 다 grouping해야 하는 상황이 발생할 수 있다는 것을 파악해볼 수 있었다.

문제의 핵심

• group by가 모든 테이블을 순회하고 있어, 너무 느리다.
• offset/limit의 효과를 하나도 못보고 있다.

가설의 검증 → 서브쿼리로 join할 레코드 제한하기

따라서 아래와 같이 쿼리를 수정했다.

from 절 서브쿼리로 쿼리 실행기가 post 테이블을 풀스캔 하지 않도록 변경하였고, 이는 꽤나 잘 먹혀들어갔다.

• 변경된 실행 계획
  • derived table이 추가되었다.
  • derived table은 인덱스를 사용할 수 없기에 실행계획에서 기피되는 테이블이지만, 현재는 탐색할 레코드 수를 크게 줄여주었기에 의미있게 사용되었다고 할 수 있다.

• 쿼리 로그
  • 505초 ->0.54초로 상당히 빨라졌음을 확인할 수 있었다.

가설 2 - 인덱스가 없기에 발생하는 NL 조인

직접 쿼리 옵티마이저가 된다고 생각하고, 조인 과정을 수행해보자.

1. post 테이블을 driving 테이블, comment 테이블을 driven 테이블로 결정

• (count(post) * 100 = count(comment)

2. post 테이블을 모두 메모리에 올림
3. driven 테이블인 comment에서 각 comment에 매칭되는 post 조인

이 과정에서, 인덱스가 없어 최적화가 불가능했다.

Post와 Comment의 관계는 Composition Aggregation 관계가 아닌 단순 Aggregation 관계라고 판단하였다.

게시글이 삭제되더라도, 해당 글에 작성했던 댓글을 사용자가 확인하고 싶을 수 있다는 상황을 가정했기 때문에 나온 판단이었다.

추후 사용자도 댓글의 소유권을 함께 가질 수 있음을 시사하려고 하였고, 따라서 외래 키 제약조건을 걸지 않았다.

그렇게 예상치 못하게 인덱스 없이 조인하는 문제가 발생하게 되었다.

이는 comment 테이블의 post_id 컬럼에 인덱스를 추가하면 어느정도 완화될 문제라고 판단하였다.

가설의 검증 - comment의 post_id에 인덱스 추가하기

이 문제를 해결하는데 쿼리 자체는 달라지지 않았고, 다음과 같이 인덱스를 추가하였다.

위와 같이 인덱스를 추가했고, 외래 키 제약조건이 존재하는 수준의 성능 개선을 예상해볼 수 있었다.

 

• 변경된 실행 계획
  • type
    • comment의 type: ref
  • extra
    • comment의 Extra: Using index(커버링 인덱스 사용)

key_len에서 8바이트, 즉 post_id의 BIG INT 전부를 사용했다는 것을 확인할 수 있다.
따라서, grouping 및 집계 연산에서 커버링 인덱스로 동작한 것을 확인할 수 있었다.

• 쿼리 로그
  • 505초 -> 0.47초로 이 또한 상당히 빨라졌음을 확인할 수 있었다.

트레이드 오프 - 삽입/수정/삭제 연산의 부하

하지만 인덱스를 추가할 땐, 사용자의 사용 패턴에 따른 트레이드오프를 고려해야 한다.
인덱스는 삽입과 인덱스 대상 컬럼의 삽입/수정/삭제 로직의 부하에 O(logN)만큼의 부하를 일으키기 때문에, 해당 부하가 실제로 어느정도 영향을 미치는지 확인해보아야 한다.

동일 상황 - auto increment 키를 가지도록 하였다.

 

인덱스가 없던, 기존 테이블에 댓글 10만건을 삽입하는데 걸리던 시간

인덱스 추가된, 신규 댓글 10만건 삽입에 걸리는 시간

 

하지만, 맨 처음 테스트 환경에서 제시했듯, 대략 3만의 조회수당 500건의 댓글이 달리고 있었고, 이는 전체 DB의 부하에 큰 영향을 미치지 않을 뿐더러, 인덱스를 추가할 때에 얻는 이득이 충분히 크다고 판단하였다.

가설 3 - filesort가 문제가 되는 것 아닐까?

어째서 filesort가 발생하는가에 대해, 좀 더 자세히 보기 위해, FORMAT=JSON방식으로 실행 계획을 보았다.

현재 필자는 Ubuntu 환경을 사용하고 있었기 때문에, 로컬에서 편히 사용하기 위해 MariaDB를 사용하고 있었다. 근데 MariaDB는 FORMAT=TREE가 없었다.

해당 내용에서 현재 group by가 p.id, p.title 를 기준으로 그룹핑하기 위해 정렬이 발생하고 있음을 확인할 수 있었다.

하지만 진정으로 그룹핑해야 할 대상은 p.id뿐이었는데, 무의미한 p.title 포함때문에 추가 정렬이 진행된다는 것을 확인할 수 있었다.

가설의 검증 - p.title 제거

• 실행 계획
  • 어차피 join 과정에서 정렬이 필요하기에, 실행계획에 변화는 없다.

• 쿼리 로그
  • 505초 -> 482초
  • 단독으로 사용할 경우, 성능 차이는 크지 않았다.

하지만, “불필요한 컬럼을 group by 대상에 넣지 말자”라는 소중한 교훈을 얻을 수 있었다.

해결책의 적용

여태까지 나온 가설들을 바탕으로, 이제 적절한 해결책을 고려해보기 시작했다.

가설 1 + 가설 2 = V4

가설 1과 2는 독립적인 위치에 있어, 별도로 함께 적용 가능했다.

V1+V2의 실행계획

V1+V2의 쿼리 로그

505초 -> 0.001초로, 99.9999%의 쿼리 수행 속도 단축을 이뤄낼 수 있었다.

가설 2 + 가설 3 = V5

가설 2와 3이 독립적인 위치에 있어, 별도로 함께 적용이 가능했다.

V2 + V3의 실행 계획

V5의 경우, 인덱스도 걸고, 불필요한 p.title도 제거하니 풀스캔도 사라지고 Using filesort도 사라졌다.

V2 + V3의 쿼리 로그

505초 -> 0.09초로, 이 또한 99.98%의 성능 개선 효과를 볼 수 있었다.

최종 결정 - V4

V4의 경우, derived 테이블이기 때문에 수행하는 filesort는 한 페이지에 10개밖에 안되는 레코드 수에서 큰 부하가 되지 않는다고 판단했다.

또한 조사하는 row 수가 100만개 vs 1만개로, 여기서 오는 부하도 크다고 판단했다.

성능상으로도 V4가 V5보다 50배 이상 더 나은 성능을 보인다.

따라서 최종적으론 from절 서브쿼리 + index로 최적화를 수행하여 마무리한다.

 

다음엔 부하 테스트를 이용한 인스턴스 레벨 설정값 튜닝으로 찾아뵙도록 하겠다. (작성예정)