[댕댕어디가] 땅따먹기 주인 조회 API 속도 개선

 1. 땅따먹기란

댕댕어디가에는 땅따먹기 서비스가 존재합니다.

'땅따먹기'란, 각 지역에 있는 반려동물 동반 가능 시설을 가장 많이 방문한 유저가 땅 주인이되는 기능을 말하며, 땅 주인이 되면 보상으로 스토리 업로드 혜택이 주어집니다.

 

 2. 부하테스트

그러나 모든 지역의 땅 주인을 조회하는 API가 응답 속도가 느린 문제가 있었습니다.

잦은 요청이 있는 API임에도 불구하고 다른 API보다 속도가 느려 부하테스트를 진행하였습니다.

 

DAU가 2만명인 것을 가정하여, 10%인 2000명이 동시에 요청했을 때의 시나리오로 테스트해 봤습니다..

결과는 실패율 100%..!

 

아래와 같은 오류가 발생했습니다.

 

DB 연결시 파일 디스크립터를 할당받는데, 할당 받을 수 있는 파일 디스크립터가 초과했다는 뜻입니다.

허용 가능한 파일 디스크립터 수를 늘릴 수 있겠지만, 기존 API 테스트에서는 발생하지 않았던 문제이기 때문에 단순히 늘린다고 해결될 문제가 아니라고 생각합니다. 원인을 분석해 해결해 보겠습니다.

 

 

 2. 병목 지점 분석

intelliJ profiler로 분석해 본결과 비교적 fetchRegion 메서드가 CPU를 많이 차지했음을 알 수 있었습니다.

 

 

병목 지점은 DB에서 '모든 지역의 땅 주인 정보를 조회하는 쿼리'에서 발생했습니다.

직접 Mysql에 쿼리를 실행해 본 결과, 쿼리를 실행하는데 1.5s 정도 걸렸습니다...

 

쿼리는 다음과 같으며 실행계획을 분석해 보겠습니다.

SELECT rol1_0.id, 
       rol1_0.city, 
       rol1_0.city_detail, 
       rol1_0.count, 
       rol1_0.user_id, 
       u1_0.nickname, 
       p1_0.pet_id, 
       p1_0.name, 
       p1_0.image
FROM region_owner_log rol1_0
JOIN (
    SELECT rol2_0.city, 
           rol2_0.city_detail, 
           MAX(rol2_0.created_at) AS createdAt
    FROM region_owner_log rol2_0
    GROUP BY rol2_0.city, rol2_0.city_detail
) ro1_0 
    ON ro1_0.city = rol1_0.city 
    AND ro1_0.city_detail = rol1_0.city_detail 
    AND ro1_0.createdAt = rol1_0.created_at
JOIN users u1_0 
    ON u1_0.user_id = rol1_0.user_id
LEFT JOIN pet p1_0 
    ON rol1_0.user_id = p1_0.user_id;

 

실행 계획 분석 결과, 아래와 같은 문제가 있었습니다.

• 3번의 Full Scan
• 임시 테이블 생성
• 해시 조인

3번의 Full Scan

우선 풀 스캔이 진행된 테이블은 rol1 테이블과 rol2 테이블, rol2 테이블로부터 만들어진 임시테이블입니다.

rol1 테이블은 조인 조건에 해당하는 city, city_detail, created_at에 대한 인덱스가 없어 풀스캔하게 되었습니다.

rol2 테이블은 임시 테이블을 생성하기 위해 풀 스캔이 진행되었고, 임시 테이블 또한 조인에 대한 인덱스가 없어 풀 스캔하게 되었습니다.

 

임시 테이블 생성

임시 테이블을 생성하는 이유는 rol2 테이블은 서브쿼리로 조회하기 때문에, 서브 쿼리에 대한 결과가 임시테이블로 저장됩니다.

임시 테이블은 메모리나 디스크에 저장되기 때문에 읽는 과정에서 I/O 비용이 발생해 속도 지연의 문제가 있습니다.

 

해시 조인

해시 조인은 인덱스가 없거나 스캔해야 하는 테이블의 크기가 커서 Nested Loops 조인을 사용하지 못하는 경우에 주로 사용됩니다.

아마 이번 경우는 인덱스가 없어 옵티마이저가 해시 조인을 선택한 것 같습니다. 해시 조인은 작은 테이블을 읽어 해시 값으로 변환해 저장해두기 때문에, 메모리나 디스크를 사용합니다. 이 또한 속도 지연의 문제가 우려됩니다.

 

# EXPLAIN
-> Filter: ((ro1_0.city_detail = rol1_0.city_detail) and (ro1_0.city = rol1_0.city))  (cost=11.9e+6 rows=0)
    -> Inner hash join (ro1_0.createdAt = rol1_0.created_at), ((ro1_0.city_detail)=(rol1_0.city_detail)), ((ro1_0.city)=(rol1_0.city))  (cost=11.9e+6 rows=0)
        -> Table scan on ro1_0  (cost=2.5..2.5 rows=0)
            -> Materialize  (cost=0..0 rows=0)
                -> Table scan on 
                    -> Aggregate using temporary table
                        -> Table scan on rol2_0  (cost=22406 rows=215413)
        -> Hash
            -> Nested loop left join  (cost=496315 rows=215413)
                -> Nested loop inner join  (cost=259361 rows=215413)
                    -> Table scan on rol1_0  (cost=22406 rows=215413)
                    -> Single-row index lookup on u1_0 using PRIMARY (user_id=rol1_0.user_id)  (cost=1 rows=1)
                -> Index lookup on p1_0 using FKhg3enfwsufxjb6enqetxx2ku0 (user_id=rol1_0.user_id)  (cost=1 rows=1)

 

 3. 병목 지점 개선

인덱싱 적용

이전까지는 조인을 위한 테이블을 읽는 과정에서 조인 조건에 대한 인덱스가 없어 2번의 풀 스캔이 실행되었습니다. 또한, 인덱스 부재로 해시 조인이 사용되어, 속도 저하가 우려되었습니다. 이를 개선하기 위해 적절한 복합 인덱스를 추가했습니다.

CREATE INDEX idx_city_detail_created_at ON region_owner_log(city, city_detail, created_at);

 

이후, 실행 계획을 다시 분석해 보겠습니다.

아무래도, 서브 쿼리로 인해 임시 테이블 생성과 임시 테이블의 풀 스캔은 개선되지 않았지만 그 외로 2번의 풀 스캔 대신 인덱스 적용, 해시 조인 대신 Nested loop inner join이 사용되어 조회 속도 개선에 도움되었습니다.

# EXPLAIN
-> Nested loop left join  (cost=161 rows=60)
    -> Nested loop inner join  (cost=95.5 rows=60)
        -> Nested loop inner join  (cost=30.2 rows=60)
            -> Filter: ((ro1_0.city is not null) and (ro1_0.city_detail is not null) and (ro1_0.createdAt is not null))  (cost=111..9.25 rows=60)
                -> Table scan on ro1_0  (cost=113..116 rows=60)
                    -> Materialize  (cost=113..113 rows=60)
                        -> Covering index skip scan for grouping on rol2_0 using idx_city_detail_created_at  (cost=99 rows=60)
            -> Index lookup on rol1_0 using idx_city_detail_created_at (city=ro1_0.city, city_detail=ro1_0.city_detail, created_at=ro1_0.createdAt)  (cost=0.252 rows=1)
        -> Single-row index lookup on u1_0 using PRIMARY (user_id=rol1_0.user_id)  (cost=0.989 rows=1)
    -> Index lookup on p1_0 using FKhg3enfwsufxjb6enqetxx2ku0 (user_id=rol1_0.user_id)  (cost=0.996 rows=1)

직접 쿼리를 실행 해본 결과, 1.5 s에서 0.0073s로 99.51% 향상되었습니다.

 

이전 테스트는 응답 속도가 58.s 이고 실패율이 100% 였던 반면 개선 후 테스트 결과는 응답속도가 0.47s, 실패율 0% 로 개선되었습니다.

 

 

캐싱 적용

땅 주인을 조회하는 기능은, 업데이트보다 조회하는 기능이 훨씬 압도적으로 많음으로, 캐싱이 필수입니다.

기존에 사용하고 있던 Redis를 활용하여 캐싱을 적용했습니다.

@Cacheable(value="regionOwnersCache", key="'regionOwner'")
	public RegionVisit<RegionOwnerCityDetail> fetchRegionOwners() {
    ...
    }

 

테스트를 통해 캐싱으로 많은 DB 부하가 감소하여 응답 속도가 빨라진 것을 확인할 수 있습니다.

 

이렇게 파일디스크립터 한도 초과 문제로 시작해서, 쿼리 병목 분석, 인덱싱 및 캐싱 적용으로 응답속도를 58s에서 0.007s까지 개선해 보았습니다. 다음에 더 좋은 아이디어가 생각난다면, 현재 쿼리를 더 튜닝해서 개선해 보면 좋을 것 같습니다!