하이러닝 성능 최적화 및 운영 안정화, 2024.01 ~ 2025.10 트러블슈팅
피크 시간대 수업 목록 쿼리를 여러 차례 고쳐 5초에서 팀 기준 1.3초 이내로 단축
상시 APM과 실행 계획으로 수업 목록 쿼리의 원인을 하나씩 찾아, 쓰지 않는 조인 제거, 인덱스를 막던 형변환 제거, 건수 집계 분리, 인덱스 재설계를 여러 차례 나눠 반영
이슈 상황
- 2023년 9월 시범 운영 이후 1년 만에 사용 학교가 162개교에서 2,470개교로 늘었습니다.
- 오전 수업 시작 시간대(서비스 피크 TPS 3,000+)에 ‘나의 수업 목록’ 조회 지연과 Primary DB 과부하가 반복돼, 수업 직전에 목록을 불러오지 못하는 일이 생겼습니다.
원인 분석
- 상시 켜 둔 APM에서 피크 시간대에 호출량이 많고 응답이 4초 이상인 API를 골랐습니다. 요청별 추적 화면에서 SQL과 호출 횟수를 보고 원인 쿼리를 좁힌 뒤, EXPLAIN ANALYZE로 실행 계획을 확인했습니다.
- 수업 목록 쿼리는 인덱스가 있었는데도 풀 스캔을 했습니다. 조회 조건이 복합 인덱스의 선행 컬럼과 맞지 않았기 때문입니다.
- 쿼리 하나는 CTE 하나와 서브쿼리 17개(화면 분기 포함)로 이루어져 있었습니다. 교사가 나의 수업 목록을 한 번 열면 테이블 11개를 JOIN 12개로 연결했습니다.
- 인덱스 말고도 쿼리 구조에 원인이 겹쳐 있었습니다.
- 과목마다 차시(수업 회차) 정보를 LEFT JOIN해서 과목 한 행이 차시 수만큼 늘어났습니다. 그런데 바깥 쿼리는 차시 컬럼을 쓰지 않고, 늘어난 행을
DISTINCT로 다시 합쳤습니다. - 학년 이름 조인은 번호 컬럼을
::TEXT로 바꿔 비교해서, 그 컬럼의 인덱스를 쓸 수 없었습니다. - 마스킹 함수와 학년 이름 서브쿼리가 결과 행마다 실행됐습니다.
- 과목마다 차시(수업 회차) 정보를 LEFT JOIN해서 과목 한 행이 차시 수만큼 늘어났습니다. 그런데 바깥 쿼리는 차시 컬럼을 쓰지 않고, 늘어난 행을
- 그래서 인덱스만 다시 만들어서는 해결되지 않았습니다.
최적화 전 쿼리 구조 보기
이름은 일반 이름으로 바꾸고, 문제가 된 부분만 남겼습니다.
WITH subject_cte AS (
-- ② 늘어난 행을 다시 합침
SELECT DISTINCT ...
FROM subject s
-- ① 과목 1행이 차시 수만큼 늘어남, 결과에는 쓰지 않음
LEFT JOIN (차시 정보) c ON c.subject_seq = s.seq
/* 학기, 담당 교사, 알림 집계 등 JOIN 생략 */
)
SELECT ...,
-- ③ 결과 행마다 DB 함수 호출
mask_function(a.teacher_name),
-- ④ 행마다 서브쿼리, 컬럼 형변환으로 인덱스를 쓰지 못함
(SELECT ... FROM school_year y WHERE y.seq::TEXT = ...)
FROM subject_cte a개선
한 번에 고치지 않고 원인을 하나씩 나눠 여러 차례 반영했습니다. 저는 원인을 분석해 고칠 지점을 정하고, 변경을 검토해 반영했습니다. 바꾼 내용은 원인 종류에 따라 네 가지입니다.
| 원인 | 바꾼 것 | 왜 느렸는지 |
|---|---|---|
| 인덱스를 쓸 수 없는 조건 | 학년 번호 비교에서 컬럼의 형변환(::TEXT)을 없애고, 비교할 값 쪽을 정수 배열로 바꿈 | 컬럼에 형변환이나 함수가 걸리면 그 컬럼의 인덱스로 값을 찾을 수 없음 |
| 불필요한 조인 | 수업 목록 API가 함께 실행하는 탭 건수 쿼리(오늘, 신규, 알림)의 담당 교사 확인에서 회원 테이블 조인을 빼고, 담당 교사 테이블의 회원 번호로 바로 거름 | 회원 테이블을 거쳐 회원 ID로 거르느라, 담당 교사 테이블을 회원 번호로 바로 찾지 못함 |
| 인덱스를 쓸 수 없는 조건 | 바뀐 조건에 맞춰 복합 인덱스를 다시 설계하고 DB에 직접 반영 | 조회 조건이 복합 인덱스의 선행 컬럼과 맞지 않아 풀 스캔 |
| 결과에 쓰지 않는 계산 | 목록·건수 쿼리의 LEFT JOIN 서브쿼리(차시 정보, 영상 시작일 집계), 교사 목록 서브쿼리, 건수 쿼리의 SUM 제거 | 화면에 쓰지 않는 조인과 집계를 조회할 때마다 계산 |
| 반복되던 계산 | 과목마다 붙이던 학생 수 같은 건수 집계를 수업 복사 창을 열 때만 실행 | 목록을 열 때마다 과목별 건수 집계를 함께 실행 |
| 반복되던 계산 | 분류체계 값과 수업명 조건을 WITH 절에서 한 번 만들고 IN (SELECT …)로 비교 | 같은 조건 값을 비교할 때마다 다시 만듦 |
| 반복되던 계산 | 오늘 수업 목록에서 SQL이 행마다 부르던 마스킹 함수를 Java 처리로 옮김 | 조회한 행 수만큼 DB 함수 호출 |
조건을 바꾼 두 곳의 변경 전후 SQL 보기
테이블과 컬럼 이름은 일반 이름으로 바꿨습니다.
-- 변경 전
-- 1) 탭 건수 쿼리의 담당 교사 확인: 회원 테이블을 조인해 회원 ID로 거름
SELECT 1 FROM subject_teacher st
JOIN member m ON st.member_seq = m.member_seq
WHERE st.subject_seq = s.subject_seq AND m.member_id = :memberId AND st.del_yn = 'N'
-- 2) 학년 이름: 학년 번호 컬럼을 문자로 바꿔 비교
FROM UNNEST(STRING_TO_ARRAY(s.grade_seqs, '|')) AS g
JOIN school_year y ON y.grade_seq::TEXT = g
-- 변경 후
-- 1) 담당 교사 테이블에 있는 회원 번호로 바로 거름
SELECT 1 FROM subject_teacher st
WHERE st.subject_seq = s.subject_seq AND st.member_seq = :memberSeq AND st.del_yn = 'N'
-- 2) 쪼갠 값을 정수 배열로 바꾸고 컬럼은 그대로 비교
FROM UNNEST(STRING_TO_ARRAY(s.grade_seqs, '|')::int[]) AS g
JOIN school_year y ON y.grade_seq = g- 변경마다 실행 계획으로 1.3초 기준(원래 있던 팀 개발 기준)을 확인했고, 새 기능을 붙일 때도 같은 기준을 먼저 확인했습니다. 성능이 걸린 변경이 있으면 운영과 최대한 같게 맞춘 테스트 서버에서 해당 API를 JMeter로 부하 테스트한 뒤 반영했습니다.
- 같은 방식으로 논술 평가 조회의
과목 IN (서브쿼리)조건을 존재 여부 확인(CASE WHEN EXISTS)으로 바꿨습니다.
결과
- 수업 목록 쿼리 실행 시간을 5초에서 팀 기준 1.3초 이내로 줄였습니다(74% 이상 단축).
- 오전 수업 시작 직전에 수업 목록을 불러오지 못해 수업이 늦어지던 문제를 완화했습니다.