[BackEnd] Index를 통해 DB 조회 성능 1.2만배 개선하기

개요

---

 

어느 날부터  리소스를 조회하는 GET 요청에 대해 적게는 20초 많게는 120초까지 조회가 오래 걸리는 현상이 생겼다. 이를 해결하기 위해 했던 작업들과 개선 결과를 정리한다.

 

1. 간단한 프로젝트 구조 설명

---

사내 배포 플랫폼에 기여하며 인프라 & 개발 업무 경험을 쌓고 있다. 주업무 중 하나는 AWS 리소스 관련 CRUD 개발인데, 프레임워크는 FastAPI를 사용하고 대부분의 요청은 Celery Task를 통해 동작한다.

 

AWS 생성과 같은 요청은 본질적으로 느리고 실패할 수 있는 작업이다. S3 버킷, Redis 클러스터, EC2 인스턴스 등 생성에 수십 초~수 분 소요되며, 특히 Redis의 경우 여러 단계를 순서대로 실행해야 하는 복잡한 워크플로우를 따라야 한다. (ex. Subnet Group → Replication Group → Tagging) 그렇기 때문에 대부분의 요청은 Celery Task를 통해 실행되며, 리소스 상태를 추적하기 위해 이 값을 DB에 저장하고 있다.

 

 

2. 문제 파악을 위한 Datadog APM 설정

---

우선 이 문제 파악을 위해서 API 요청 흐름을 정리하고, 이 과정에서 느린 쿼리가 있는지 분석해보았다. 

어느 날부터  리소스를 조회하는 GET 요청에 대해 적게는 20초 많게는 120초까지 조회가 오래 걸리는 현상이 생겼다. 

 

문제가 될 만한 로직은 다음 부분이었는데,

def solve_xxx (self, resource: Resource):
    """ResourceGroup 내의 Resource 목록을 조회할 때 사용하는 함수
    relation(lazy-loading) 이 설정된 모델 애트리뷰트를 직접 참조해서 데이터 형태를 완성한다.
    """

    # XXX: 현재는 단순 참조 로직으로 데이터를 완성하지만
    #      n+1 문제를 유발하므로 성능에 문제가 있는 경우 튜닝해야 함.
    #      리소스는 2env(alpha, prod) * 4region(kr, jp, ca, gb)
    #      총 8개의 리소스이므로 크게 문제는 없을 듯.

    for event in resource.events:
        event.celery_task

 

SQLArchemy 동작 방식에 따르면 resource.events, event.celery_task와 같이 relationship 속성에 접근하면 DB 쿼리를 수행하게 된다. 위 코드에서 celery_task 조회는 event 개수만큼 실행된다. 

 

따라서, 아래 두 가지 데이터 개수를 확인 후, 느린 쿼리에 대해 판단해야 한다.

1. resource별 event 개수 (resource_events 테이블)
2. event별 celery_task 연결 여부 (celery_tasks 테이블)

처음에 실수?를 한 건, 이 resource 별 event 개수에만 집중해서 `for event in resource.events`에 대한 쿼리만 분석했다. 그 결과는 800개 수준이었고 당연히 느린 쿼리가 아닐 것이라고 판단했다. 그래서 좀 더 세밀한 분석을 위해 데이터독 도움을 받고자 APM을 설정하였다.

 

 

3. Datadog APM 설정 및 Latency 분석

---

APM 설정을 위해 필요한 작업은 ddog 가이드 문서를 참고했다. 새삼 우리 팀의 플랫폼 & 모니터링 인프라가 잘 되어있다는 걸 느낀 점은 Datadog Agent Pod 설정과 환경변수 주입은 아주 쉽게 해결할 수 있었다. 🤤🤤

 

3.1 Datadog 가이드 문서를 따라 Trace 설정

• Python ddtrace 라이브러리 추가
• DockerFile에서 실행 부분(CMD)에 ddtrace 패키지 사용 명령어 `ddtrace-run` 추가
• Datadog 태그 환경 변수 설정

세팅해야 할 환경변수들이 Pod에 잘 설정되어 있는 점

ddtrace-run 추가!

 

3.2 APM 설정 완료

이렇게 데이터독 대시보드에서 관련 지표를 확인할 수 있다. 모니터링 준비가 끝나서 바로 느린 요청에 대해 확인을 시작했다.

 

GET 요청에 대해 22s가 걸리는데, 그 중에서 특정 쿼리들이 느린 점을 trace를 통해 확인할 수 있었다. trace를 통해 느린 쿼리를 확인해보니, 처음에 의심했던 로직에 문제가 있었다. 

 

3.3 Full Scan, 느린 쿼리 확인

 

데이터독을 통해 확인한 느린 쿼리와 관련해서 비슷한 쿼리를 만든 다음, EXPLAIN을 통해 어떻게 쿼리가 동작하는지 확인해보았다. 아래 두 가지 결과를 통해 느린 쿼리의 원인임을 판단했다. 

• type=all 즉, full-scan(테이블의 모든 행을 읽음)한다. → 테이블의 모든 행을 스캔해야 하기에, 데이터가 많아질 수록 성능 부하
• show index를 했을 때 primary_index만 생성되어있다. → PK에 대한 primary_index만 생성되어있었다. 문제가 되는 task_id에 대한 인덱스는 존재하지 않는 상황이다.

결론적으로 이 내용을 조합해보면, 리소스를 한번 조회할 때, task_id에 대해서 6만 행에 대한 full-scan이 발생하고 있었다.

 

4. Index 추가 (SQLAlchemy, MySQL)

---

원인 분석 후, 해결 방법은 해당 컬럼에 대한 index를 생성하는 것이었다. index 생성이 이 문제를 해결해줄 것이라고 판단한 기준은 다음과 같다.

4.1 Index 추가로 해결 가능한 이유는?

• 해당 컬럼에 대해 `SELECT`만 빈번히, `UPDATE` 및 `DELETE` 쿼리는 거의 일어나지 않는 패턴이다.
• 카디널리티가 높다.
  • task_id는 uuid같은 값을 가지고 있다. 카디널리티 확인을 위해 null, "", `GROUP BY`를 통한 중복값을 확인하였다.

4.2 SQLAlchemy로 Index 추가하기

DB에서 바로 Index를 추가할 수 있지만, 그러면 코드 <> 마이그레이션 히스토리 <> DB 간 정합성이 깨진다. 그래서 모델 수정 + Alembic 마이그레이션을 통한 index 생성 작업을 진행했다.

 

이 방법이 가능한 이유는 다음과 같다.

• MySQL(InnoDB)에서는 UNIQUE  제약 조건이 붙은 열에 대해서, secondary index를 자동 생성한다.
• 코드 수정을 통한 SQLAlchemy 모델 변경 → Alembic을 통한 DDL Script 실행 →MySQL이 index 생성

이를 반영하기 위해 아래와 같이 코드를 수정 후, DB 마이그레이션을 진행했다.

 

5. 결과!

 

대부분의 GET 응답 Latency가 5s 미만을 유지하게 되었다! 그리고 특히 해당 쿼리는 21.6s → 1.68ms (약 1.2만배) 정도의 개선 결과가 있었다.