[AWS RDS] pt-query-digest를 활용한 slow query 분석

📌 문제 상황

RDS에서 slow_query_log를 켜둔 상태였는데, 로그가 수만 건 이상 쌓여 관리가 어려워짐
로그를 직접 보면 조건값이 조금씩 달라서 전부 다른 쿼리처럼 보임
실제로는 같은 쿼리 패턴인데 단순 값 차이 때문에 중복 로그처럼 쌓이고 있었음

즉, 비슷한 쿼리를 묶어서 패턴화하지 않으면 개선 포인트를 찾기 어려운 상황

📌 해결 방법

저는 크게 두 가지 단계를 거쳐 문제를 해결했습니다.

로그 파일 다운로드
- AWS Console에서 RDS 인스턴스를 선택하고 '로그 및 이벤트' 탭으로 이동
- slowquery/mysql-slowquery.log 파일을 다운로드
로그 분석
- Percona Toolkit의 pt-query-digest를 이용해 쿼리를 패턴화 & 집계
- 상위 몇 개의 문제 쿼리만 집중적으로 튜닝

📌 pt-query-digest 사용하기

1. 설치

MacOS/Linux 환경에서 아래 명령어를 통해 설치

brew install percona-toolkit
또는
sudo apt-get install percona-toolkit

2. 실행

pt-query-digest slowquery.log > slowquery_report.txt

단 한 줄로 수만 건의 로그가 깔끔한 리포트로 변환됩니다 👍

📌 분석 리포트

아래는 실제 일부 로그만을 pt-query-digest 통해 분석한 결과의 일부입니다.

27 total → 총 27번 실행된 쿼리
1 unique → 서로 다른 쿼리 패턴은 딱 1개뿐 (즉, 같은 쿼리가 반복 실행됨)
0.03 QPS → 초당 평균 0.03건 실행 (거의 1분에 2건 정도)
0.04x concurrency → 평균적으로 DB 연결 리소스를 점유한 수준이 낮음
실행 시간은 평균 1~2초
보내는 행은 200여 개뿐이지만
실제 스캔한 행은 80만 개 이상

👉 필요한 결과는 200행인데, 백만 단위의 row를 뒤져서 찾고 있음 (인덱스 부재 가능성 높음)

📌문제 쿼리

SELECT new CounselorSearchResponseDto(...)
FROM Counselors c
LEFT JOIN Users u ON u.id = c.user_id
LEFT JOIN CounselorGrades cg ON cg.id = c.counselor_grade_id
LEFT JOIN CounselorFavorite f ON c.id = f.counselor_id AND f.user_id = :userId
LEFT JOIN CounselorBadge cb ON cb.counselor_id = c.id
WHERE c.status NOT IN ('inactive','terminated','unapproved')
  AND c.user_id NOT IN (
      SELECT ub.user_id 
      FROM UserBlock ub 
      WHERE ub.reporter_id = :userId 
        AND NOT (ub.is_deleted IS NULL OR ub.is_deleted = 1)
  )
ORDER BY c.sequence DESC;

여러 테이블 LEFT JOIN + 서브쿼리
Rows_examined가 80만인데, 실제 반환은 200행 → 인덱스 미사용
문자열 비교(availableStartTime, availableEndTime)로 인해 인덱스 사용 어려움
정렬(ORDER BY c.sequence) 포함 → 인덱스 없으면 느림

📌 개선 포인트

인덱스 후보

-- Counselors 테이블
CREATE INDEX idx_c_status_user_sequence ON Counselors(status, user_id, sequence);

-- UserBlocks 테이블
CREATE INDEX idx_ub_reporter_user_deleted ON UserBlocks(reporter_id, user_id, is_deleted);

-- Reviews, ChatRooms
CREATE INDEX idx_reviews_counselor ON Reviews(counselor_id);
CREATE INDEX idx_chatrooms_counselor ON ChatRooms(counselor_id);

쿼리 구조 개선

서브쿼리 → JOIN + GROUP BY 변환 가능
문자열 비교 → 정규화된 숫자/시간 컬럼 사용

정리

slow_query_log 자체를 그대로 보는 건 의미가 없다.
쿼리 패턴별로 묶어서 집계해야 진짜 문제를 알 수 있다.
pt-query-digest를 활용하면 수만 건 로그도 단 몇 초 만에 정리 가능하다.
상위 몇 개의 문제 쿼리만 개선해도 전체 성능이 크게 개선된다.
이 결과는 "분석한 느린 쿼리 중 단 하나의 쿼리 패턴이 원인"임을 알 수 있다.
문제는 200여 건의 결과를 위해 매번 80만 row를 스캔하는 구조 → 인덱스 최적화 및 쿼리 리팩토링이 필요하다.

개발 끄적임 노트