조회 성능 최적화(2) - 슬로우 쿼리 튜닝하기

Steady - 개발 스터디 및 프로젝트 인원 모집 사이트
[ 배포 링크 ] [ 프로젝트 깃허브 ]
자체적으로 제공하는 폼과 유저 평가를 통해 외부 서비스에 의존하지 않고 검증된 스터디 인원을 모집할 수 있는 서비스입니다.

 

개요

steady 테이블은 `steady_stacks, steady_positions, steady_likes` 테이블과 일대다 관계를 맺고있습니다. 위 테이블들을 활용하여 필터링 및 검색 기능에 대한 구현을 앞두고 사용자 입장에서 페이지 방식과 무한 스크롤 방식 중 어떤 방식으로 응답을 받는 것이 좋을지에 대한 회의를 진행하게 되었습니다.

 

회의를 진행하며 나온 의견 중 “무한 스크롤 방식은 내가 봤던 글이 어디쯤에 있었는지 파악하기 어렵다” 라는 의견이 가장 많은 공감을 얻어 페이지 방식으로 구현하기로 결정되었습니다. 하지만, 복잡한 쿼리와 offset 기반의 페이징 방식이 맞물려 데이터 조회에 예상보다 많은 시간이 소요되는 문제를 발견하였고 이를 해결하기 위한 과정을 담고 있습니다.

 

테스트 환경

QueryDsl 메서드

 

MySQL 쿼리

더미 데이터

테이블	데이터 수
steady	3,000,000
steady_stacks	3,000,000
steady_position	3,000,000
steady_like	6,000,000

 

 

 

성능 테스트

위 테스트 환경을 기반으로 테스트하였을 때 offset 의 크기에 따른 성능은 다음과 같았습니다.

offset	실행 시간
10,000	0.265s
20,000	0.516s
40,000	1.266s
70,000	9.604s
71,000	실패 (30s 경과)

offset 의 크기가 커지면 커질 수록 성능이 기하급수적으로 나빠지는 것을 확인할 수 있었습니다. 쿼리에 어떠한 문제가 있는지 확인하기 위해 쿼리의 실행 계획을 살펴보았습니다.

 

최초 실행 계획

 

MySQL의 경우 외래키에도 `Index`를 생성해주기 때문에 외래키를 통해 Join 되는 테이블들을 조회할 때는 인덱스 전략이 사용되고 있는 것을 확인할 수 있습니다. 반면에 메인 SELECT 쿼리에 해당하는 steady 테이블의 경우 인덱스 전략이 사용되지 않았으며, `Using temporary, Using filesort` 전략이 사용된 것을 볼 수 있습니다.

위 전략들에 대해서 알아본 바는 다음과 같습니다.

 

Using temporary

• 레코드를 정렬하거나 그룹핑 하기 위해 임시 테이블을 이용하는 방식으로 `DISTINCT`와 `ORDER BY` 가 동시에 존재하거나 `DISTINCT`가 인덱스로 처리되지 못하는 등의 경우에 사용됨.
• 임시 테이블을 이용한 정렬은 모든 테이블의 결과를 임시 테이블에 넣고 정렬을 수행하므로 가장 느리다.

Using filesort

• 정렬을 위해 디스크나 임시 파일(Sort Buffer)을 사용하는 방식으로 `ORDER BY` 에 들어가는 컬럼이 인덱스로 처리되지 못하는 경우 사용됨.
• 드라이빙 테이블을 정렬하고 나머지 테이블들과 조합하는 방식으로 Sort Buffer만으로 해결할 수 없는 경우 임시 테이블을 사용함.

요약하면 조회 대상들을 정렬할 때 인덱스만으로 처리하지 못하는 경우 사용되는 전략이기 때문에 성능이 좋지 않다는 것이었습니다.

 

위 정보를 토대로 현재 steady 테이블이 인덱스 전략을 사용하지 못하고 있는 이유는 DISTINCT 와 ORDER BY가 동시에 존재하며 ORDER BY 조건에 사용된 컬럼에 인덱스가 생성되어 있지 않기 때문임을 유추할 수 있었습니다.

 

다만, 일대다 관계를 페이징 처리하기 위해선 중복된 결과를 제거하기 위한 DISTINCT는 필수적이었기 때문에 우선은 ORDER BY 조건에 사용된 컬럼에 인덱스 생성만을 진행하여 실행 계획을 살펴보았습니다.

 

DISTINCT가 아닌 GROUP BY를 사용한다면?
`GROUP BY` 와 ORDER BY를 함께 사용하는 경우 명시된 컬럼의 순서와 내용이 모두 같아야만 인덱스를 사용합니다. 이를 만족하지 않으면 두 조건 모두 인덱스를 사용하지 않기 때문에 DISTINCT를 사용하였습니다.

 

 

ORDER BY 인덱스 생성후 실행 계획

실행 계획을 확인하니 인덱스가 사용되어 Using filesort 전략이 사라진 것을 확인할 수 있었습니다. 이를 적용한 후의 성능은 다음과 같습니다.

 

 

offset 40,000까지는 큰 차이가 없었지만, offset 70,000에서는 `75%` 가량 개선된 것을 확인했습니다. 단순히 인덱스 생성만으로도 유의미한 성능 개선이 이뤄진 것입니다. 하지만, 인덱스 생성 전과 마찬가지로 offset 71,000에서는 조회가 실패하였습니다. 원인을 찾기 위해 offset 71,000의 실행계획을 확인해보았습니다.

 

 

OFFSET 71,000 실행 계획

분명 인덱스를 생성했음에도 불구하고 인덱스 전략을 사용하지 않고 `ALL (테이블 풀 스캔)` 전략이 사용되었으며, 인덱스로 처리되지 못했기 때문에 Using filesort 전략도 다시 사용된 것을 확인할 수 있었습니다.

위 사실을 통해 추측한 내용은 다음과 같습니다.

1. 현재 Non-Clustered Index (steady 테이블의 bio 컬럼) 으로 조회를 진행하고 있는데, Non-Clustered Index 조회 이후 `SELECT` 절에서 조회를 원하는 컬럼들을 가져오기 위해 다시 Clustered Index를 조회하는 과정이 존재한다. 이 과정에서 옵티마이저는 offset이 커지면 커질 수록 인덱스를 탐색하는 비용이 테이블 풀 스캔하는 비용보다 크다고 판단하여 실행 전략을 테이블 풀 스캔으로 변경한다.
2. 1번 추측을 기반으로 Clustered Index를 조회하는 동작을 생략하면 테이블 풀 스캔 방식으로 변경되지 않고 기존의 인덱스 스캔 방식을 그대로 사용할 수 있게 될 것이다.

출처 : https://taegyunwoo.github.io/tech/Tech_DBPagination

 

MySQL의 `offset`은 단순히 지정한 값부터 데이터를 읽는 식으로 동작하지 않고 데이터의 시작부터 지정한 값까지 모든 데이터를 읽은 후 `limit`의 크기만큼 반환하는식으로 동작하기 때문에 위 그림과 같이 Clustered Index를 조회 과정을 생략할 수 있다면 유의미한 개선을 이뤄낼 수 있을 것이라 생각하였습니다.

 

이를 위해 커버링 인덱스를 적용하면 쉽게 해결할 수 있을 것입니다. 하지만, 현재 `select`절에서 steady 테이블의 모든 컬럼을 요구하고 있어 이에 해당하는 모든 컬럼에 인덱스를 생성하기에는 너무 많은 비용이 든다고 판단하였습니다. 따라서 인덱스를 적게 생성하면서도 커버링 인덱스를 사용할 수 있는 방법을 찾던 도중 `Deffered Join` 기법이라는 것을 발견하게되었습니다.

 

Deferred Join 과 서브 쿼리

위 기법은 일종의 Lazy Loading 기법과 유사합니다. 최초부터 모든 컬럼들을 조회하고 페이징을 진행하는 것이 아니라 페이징을 수행해야 하는 offset 구문을 `서브 쿼리`로 만들고 해당 서브 쿼리에 `커버링 인덱스`를 적용하여 인덱스만으로도 offset 결과를 반환할 수 있게 하고, 반환된 offset 결과와 원래 테이블을 join하여 필요한 컬럼들을 뒤늦게 채워주는 방식입니다.

 

이렇게 한다면 offset의 크기만큼 데이터를 읽을 때 `select` 에 포함된 컬럼들을 실제 테이블을 통해 조회하지 않고 서브 쿼리 내의 인덱스를 통해 조회를 하기 때문에 Clustered Index를 조회하는 과정을 생략할 수 있게 됩니다.

 

해당 방식을 적용하여 작성한 쿼리와 테스트 결과는 다음과 같습니다.

offset 70,000 기준 인덱스 적용 전과 비교하였을 때는 `96%`의 성능 개선이 이루어졌으며 인덱스 적용 후와 비교하였을 때는 `84%` 의 성능 개선이 이루어진 것을 확인할 수 있었습니다. 또한, offset 71,000 이상의 값에서도 정상적으로 동작하는 것을 확인할 수 있었습니다.

 

실행 계획 비교

Deferred Join 적용 전

Deferred Join 적용 후

기존에는 steady 테이블 풀 스캔 방식이기 때문에 2,903,047 개의 row를 조회하였다면, 변경 후에는 테이블 풀 스캔을 하지 않고 정확히 offset과 limit의 크기 만큼만 row를 조회한 것을 확인할 수 있었습니다.

 

 

트러블 슈팅

최종적으로 Deferred Join을 적용한 쿼리에서 offset을 `252,666` 보다 크게 설정하게 되면 아래와 같이 잘못된 결과를 반환하는 문제가 발생하게 되었습니다.

정렬 방향도 내림차순에서 오름차순으로 변경되었으며, offset도 제대로 적용되지 않아 잘못된 데이터를 가져오고 있는 것을 확인할 수 있습니다.

 

이번에도 offset이 커짐에 따라 실행 계획이 변경된 것은 아닐까하여 정상 동작하는 offset 200,000과 비정상적으로 동작하는 offset 300,000의 경우를 비교해보았습니다.

 

OFFSET 200,000 실행 계획

-> Table scan on <temporary>  (cost=3.29e+6..3.29e+6 rows=10) (actual time=3115..3115 rows=10 loops=1)
    -> Temporary table with deduplication  (cost=3.29e+6..3.29e+6 rows=10) (actual time=3115..3115 rows=10 loops=1)
        -> Nested loop inner join  (cost=3.29e+6 rows=10) (actual time=3114..3115 rows=10 loops=1)
            -> Nested loop inner join  (cost=3.09e+6 rows=10) (actual time=3114..3115 rows=10 loops=1)
                -> Nested loop inner join  (cost=2.89e+6 rows=10) (actual time=3114..3114 rows=10 loops=1)
                    -> Table scan on s2  (cost=2.69e+6..2.69e+6 rows=10) (actual time=3114..3114 rows=10 loops=1)
                        -> Materialize  (cost=2.69e+6..2.69e+6 rows=10) (actual time=3114..3114 rows=10 loops=1)
                            -> Limit/Offset: 10/200000 row(s)  (cost=2.69e+6..2.69e+6 rows=10) (actual time=3114..3114 rows=10 loops=1)
                                -> Table scan on <temporary>  (cost=2.69e+6..2.69e+6 rows=406994) (actual time=2556..2971 rows=200010 loops=1)
                                    -> Temporary table with deduplication  (cost=2.69e+6..2.69e+6 rows=406994) (actual time=2556..2556 rows=200010 loops=1)
                                        -> Limit table size: 200010 unique row(s)
                                            -> Nested loop left join  (cost=2.65e+6 rows=406994) (actual time=0.09..2030 rows=333964 loops=1)
                                                -> Covering index scan on s using steadies_bio_index  (cost=3763 rows=200010) (actual time=0.0676..192 rows=200010 loops=1)
                                                -> Covering index lookup on sl using steady_id (steady_id=s.id)  (cost=0.897 rows=2.03) (actual time=0.00325..0.00495 rows=1.67 loops=200010)
                    -> Covering index lookup on ss using steady_id (steady_id=s2.id)  (cost=1 rows=1) (actual time=0.0138..0.0157 rows=1 loops=10)
                -> Covering index lookup on sp using steady_id (steady_id=s2.id)  (cost=1 rows=1) (actual time=0.00818..0.0109 rows=1 loops=10)
            -> Single-row index lookup on s using PRIMARY (id=s2.id)  (cost=1 rows=1) (actual time=0.0143..0.015 rows=1 loops=10)

 

OFFSET 300,000 실행 계획

-> Table scan on <temporary>  (cost=3.64e+6..3.64e+6 rows=10) (actual time=4792..4792 rows=10 loops=1)
    -> Temporary table with deduplication  (cost=3.64e+6..3.64e+6 rows=10) (actual time=4792..4792 rows=10 loops=1)
        -> Nested loop inner join  (cost=3.64e+6 rows=10) (actual time=4791..4792 rows=10 loops=1)
            -> Nested loop inner join  (cost=3.34e+6 rows=10) (actual time=4791..4791 rows=10 loops=1)
                -> Nested loop inner join  (cost=3.04e+6 rows=10) (actual time=4791..4791 rows=10 loops=1)
                    -> Table scan on s2  (cost=2.74e+6..2.74e+6 rows=10) (actual time=4791..4791 rows=10 loops=1)
                        -> Materialize  (cost=2.74e+6..2.74e+6 rows=10) (actual time=4791..4791 rows=10 loops=1)
                            -> Limit/Offset: 10/300000 row(s)  (cost=2.74e+6..2.74e+6 rows=10) (actual time=4791..4791 rows=10 loops=1)
                                -> Table scan on <temporary>  (cost=2.73e+6..2.74e+6 rows=610481) (actual time=3976..4591 rows=300010 loops=1)
                                    -> Temporary table with deduplication  (cost=2.73e+6..2.73e+6 rows=610481) (actual time=3976..3976 rows=300010 loops=1)
                                        -> Limit table size: 300010 unique row(s)
                                            -> Nested loop left join  (cost=2.67e+6 rows=610481) (actual time=0.101..3165 rows=501056 loops=1)
                                                -> Covering index scan on s using steadies_bio_index  (cost=6678 rows=300010) (actual time=0.0748..296 rows=300010 loops=1)
                                                -> Covering index lookup on sl using steady_id (steady_id=s.id)  (cost=0.897 rows=2.03) (actual time=0.00351..0.00525 rows=1.67 loops=300010)
                    -> Covering index lookup on ss using steady_id (steady_id=s2.id)  (cost=1 rows=1) (actual time=0.00924..0.0105 rows=1 loops=10)
                -> Covering index lookup on sp using steady_id (steady_id=s2.id)  (cost=1 rows=1) (actual time=0.00518..0.00695 rows=1 loops=10)
            -> Single-row index lookup on s using PRIMARY (id=s2.id)  (cost=1 rows=1) (actual time=0.00638..0.00702 rows=1 loops=10)

실행 계획 상에서는 offset의 크기에 따른 수치 이외에는 별도로 실행 전략이 변경된 부분을 확인할 수는 없었습니다. 변경 지점을 확인할 수 없었으니 기존 실행계획에서 문제가 되는 곳이 어디인지 찾는 과정이 필요했는데, 가장 먼저 눈에 띈 것이 `임시 테이블` 전략이었습니다.

 

임시 테이블 전략은 위에서 확인했듯이 조회 결과들을 임시 테이블 (메모리) 에 올린 후 한 번에 정렬을 진행하는 전략입니다. 그렇다면 임시 테이블 메모리가 부족한 경우 정상적인 정렬 작업이 불가하여 잘못된 결과를 반환하게 되는 것은 아닐까? 라는 추측을 하게 되었고, 이를 바탕으로 임시 테이블의 크기를 조절하는 방식을 시도해보게 되었습니다.

 

우선, 아래의 명령어로 임시 테이블의 기본 크기를 확인하였습니다.

// 임시 테이블 메모리 확인
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

위 명령어로 확인한 결과 기본 크기는 `16777216byte` 로 대략 16MB 정도의 크기임을 확인할 수 있었습니다.

 

해당 크기를 어느정도로 설정하면 좋을지 판단하기 위해 MySQL 여유 자원도 확인하였습니다.

// 여유 메모리 확인
SELECT SUM(data_length+index_length)/1024/1024 used_MB, SUM(data_free)/1024/1024 free_MB FROM information_schema.tables;

위 명령어로 확인한 결과 297MB 의 여유 자원이 있음을 확인하였고, 임시 테이블의 크기를 `50MB`로 설정하고 다시 쿼리를 실행해보았습니다.

// 임시 테이블 크기 변경
set global tmp_table_size = 50000000;
set global max_heap_table_size = 50000000;

 

결과적으로 offset 252,666 에서도 정상적으로 동작하였으며, 그 이상의 offset 값에서도 정상적으로 동작하는 것을 확인할 수 있었습니다.

후기

DB는 어렵지만 재밌다.

MySQL은 관계형 데이터베이스이기 때문에 데이터간의 관계를 어떻게 설정하느냐에 따라 후에 생성될 쿼리의 복잡도나 성능이 큰 영향을 받게 되는데, 이러한 것들을 모두 고려하면서 데이터를 설계할 능력이 아직은 갖춰지지 않아 어려운 것 같습니다.

 

또한, MySQL 옵티마이저가 어떤 근거로 인덱스 전략을 사용하는지, 인덱스는 어떻게 사용하면 좋은지에 대한 깊은 고민을 해본 것은 이번 과정에서 처음 경험해보는 것이었기 때문에 더욱 어렵게 느껴졌던 것 같습니다.

 

그럼에도 실제로 튜닝을 통해 개선되어 나가는 쿼리를 보면서 큰 성취감을 느낄 수 있었고, 더 공부하여 데이터 베이스의 제 성능을 이끌어내고 싶다는 목표가 생기게 되었습니다.

 

Count 쿼리의 한계

일련의 최적화 과정을 거쳤음에도 쿼리 성능에 가장 큰 영향을 주는 COUNT 쿼리에 대해서는 답을 찾지 못했습니다. 아무런 JOIN도 사용하지 않고 `select count(*) from steadies` 만 실행하더라도 3,000,000 개의 데이터를 계산하는데 `7.781s` 가 소요되었습니다. 실제 쿼리에는 다수의 JOIN과 WHERE 조건도 포함되기 때문에 성능을 위해서는 무언가 다른 방법을 찾아야 했습니다.

 

COUNT 쿼리 자체를 최적화하기 보다는 실행 빈도를 줄이는 게 맞다고 생각하여 COUNT 쿼리 결과를 캐싱하거나 따로 저장하는 식으로 관리하여 호출 빈도를 줄이는 방식도 떠올려 보았는데, 이러한 방식도 대용량 데이터를 처리하다보면 언제든 타임아웃이 발생할 수 있는 구조입니다.

 

참고로 Google 역시 최근에 무한 스크롤 방식으로 변경되었는데, 기존 Offset 방식을 사용할 때도 40페이지까지만 표시하였고 검색 결과에 해당하는 데이터의 수도 대략적으로 표시했던 것으로 보아 Offset 방식임에도 COUNT 쿼리를 사용하지 않았던 것으로 보입니다.

 

이러한 점들로 미루어 보았을 때, COUNT 쿼리를 최대한 사용하지 않는 쪽으로 페이지네이션을 구현하는 것이 가장 좋은 방법이 될 것 같습니다.

 

 

명사: 오프셋, 갈라짐, 출발 동사: 차감 계산을 하다, 오프셋 인쇄로 하다, 파생하다