느린 쿼리와 커넥션 풀

런치챗은 대학생이 교내에서 함께 식사할 상대를 찾는 점심 매칭 서비스입니다. 사용자는 추천 목록과 인기 목록을 보거나, 학과와 학번 같은 조건으로 상대를 검색할 수 있습니다.

서비스를 출시한 뒤 회원은 약 1,000명까지 늘었고 매칭 기록은 약 3만 건이 쌓였습니다. 실제 사용자가 늘기 전에 추천, 인기, 필터 목록을 함께 호출했을 때 서버가 안정적으로 응답하는지 확인하기 위해 부하 테스트를 진행했습니다.

첫 테스트에서는 요청 세 건 중 한 건이 실패했습니다. 느린 요청은 모두 30초 부근에서 끊겼습니다. 처음에는 작은 서버가 원인이라고 생각했지만 CPU와 메모리 사용량은 안정적이었습니다.

로그를 더 살펴보니 요청은 쿼리를 처리하는 시간보다 빈 DB 커넥션을 기다리는 데 오래 머물고 있었습니다. 커넥션 수를 바로 늘리는 대신, 어떤 조회가 연결을 오래 점유하고 다른 요청까지 기다리게 하는지부터 추적했습니다.

문제를 재현한 조건

변경 전후를 비교하려면 조건이 같아야 합니다. 서버 사양과 데이터 양, API 호출 비율을 고정하고 코드를 바꿀 때마다 같은 부하를 다시 걸었습니다.

k6에서는 추천, 인기, 필터 API를 함께 호출했습니다. 코드를 바꿀 때마다 같은 조건으로 다시 돌려, 어느 수정에서 시간이 줄었는지 비교했습니다.

요청은 왜 30초 동안 멈췄을까

p95는 요청 100건 중 95건이 끝나는 시간을 뜻합니다. 이 값이 30초라고 해서 쿼리가 30초 내내 실행된 것은 아닙니다. 빈 커넥션을 기다리다가 타임아웃에 걸렸을 수도 있습니다.

그래서 세 가지를 함께 봤습니다.

• 요청이 커넥션을 얻기까지 걸린 시간
• 열 개의 커넥션이 실제로 모두 사용 중이었는지
• GC와 메모리 변화가 30초를 설명할 만큼 컸는지

이렇게 나눠 보니 실제 쿼리 시간과 커넥션 대기 시간을 구분할 수 있었습니다.

첫 테스트부터 문제가 분명했다

수정을 시작하기 전에 현재 상태부터 측정했습니다. 에러율은 33.3%였고, 절반의 요청이 끝나는 데도 8초가 넘게 걸렸습니다. 특히 느린 요청을 보여주는 p95와 p99는 모두 30초 타임아웃에 걸렸습니다.

같은 지연에도 원인은 달랐다

커넥션은 어디에서 막혔을까

애플리케이션은 요청마다 DB 연결을 새로 만들지 않고, 미리 열어 둔 연결 묶음인 커넥션 풀을 함께 사용합니다. Grafana와 로그를 맞춰 보니 열 개의 연결이 모두 사용 중이었습니다. 다만 이것만으로는 개수가 부족한지, 쿼리가 연결을 너무 오래 잡고 있는지 알 수 없었습니다.

기다림은 쿼리 실행 전부터 시작됐다

로그에는 active=10, idle=0이 찍혀 있었습니다. 열 개의 커넥션이 모두 사용 중이었고, 뒤에 들어온 요청 일곱 개는 빈자리를 기다리고 있었습니다.

HikariPool-1 - Connection is not available, request timed out after 30000ms
(total=10, active=10, idle=0, waiting=7)

새 요청은 쿼리를 시작하지 못한 채 빈 커넥션을 기다렸고, 30초가 지나면 실패했습니다. 이때 응답 시간을 좌우한 것은 쿼리 실행보다 커넥션을 얻기까지의 대기였습니다.

부족한 것은 개수보다 반환 속도였다

주요 조회는 한 번에 27초에서 30초 가까이 걸렸고, 그동안 사용한 커넥션도 반환되지 않았습니다. 커넥션이 열 개여도 각각 수십 초씩 묶여 있다면 요청 몇 건만으로 풀이 가득 찰 수밖에 없었습니다.

GC와 메모리는 원인이 아니었다

자바 애플리케이션이 느려지면 GC를 먼저 의심하기 쉽습니다. 하지만 이번 지표는 30초 지연과 맞지 않았습니다.

Full GC는 발생하지 않았고 Heap 사용량도 130~340MB 사이에서 안정적으로 움직였습니다. 메모리 부족이나 긴 GC 멈춤이 이번 지연을 만든 것은 아니었습니다.

GC로 애플리케이션이 멈춘 시간도 25ms 아래였습니다. 30초 타임아웃을 설명하기에는 지나치게 짧았습니다.

따라서 GC 옵션은 건드리지 않고, 쿼리가 커넥션을 얼마나 오래 점유하는지 다시 살펴봤습니다.

CPU 상승은 뒤따라온 현상이었다

CPU 사용률은 올랐지만 처리량은 늘지 않았습니다. 계산이 많아서라기보다 대기 중인 스레드가 자주 깨어나고 다시 잠들거나, 타임아웃 예외와 재시도를 반복해서 처리하는 비용이 섞여 있었습니다.

• 커넥션을 기다리는 스레드가 자주 전환되는 비용
• 반복되는 ConnectionTimeoutException 생성과 로그 기록
• 실패한 요청을 다시 보내는 재시도

CPU 그래프만 봤다면 서버를 키우는 쪽으로 갔을 수 있습니다. 그러나 원인이 DB 대기였기 때문에 쿼리 실행 시간과 커넥션 점유 시간을 먼저 줄이는 편이 맞았습니다.

인덱스를 고쳐도 타임아웃은 남았다

실행 계획과 코드를 함께 살펴보니 두 가지 문제가 보였습니다.

• 필요한 후보는 일부뿐인데 회원 1,000명을 애플리케이션에서 끝까지 순회하고 있었습니다.
• 복합 인덱스는 status 컬럼부터 시작했지만 실제 쿼리에는 해당 조건이 빠져 있었습니다.

인덱스와 실제 조회 조건이 어긋나 있었다

먼저 Repository 쿼리에 status 조건을 추가해 복합 인덱스의 첫 번째 컬럼부터 사용할 수 있게 했습니다.

// Before: 인덱스 미활용 (status 조건 누락)
Page<Member> findByUniversityIdAndIdNot(Long universityId, Long memberId, Pageable pageable);

// After: 선행 컬럼을 포함해 인덱스 활용
@Query("SELECT m FROM Member m WHERE m.status = :status " + // 선행 컬럼 명시
       "AND m.university.id = :universityId AND m.id != :memberId " +
       "ORDER BY m.createdAt DESC")
Page<Member> findActiveByUniversityIdAndIdNot(
    @Param("status") MemberStatus status,
    @Param("universityId") Long universityId,
    // ... params
);

또한 1,000명을 모두 계산하지 않고 상위 후보 30명을 확보하면 순회를 멈추도록 바꿨습니다.

조회 범위는 줄었지만 응답은 그대로였다

조회 범위를 줄이고 인덱스 조건도 맞췄으니 응답 시간이 짧아질 것으로 예상했습니다. 그러나 다시 부하를 걸어보니 30초 타임아웃은 그대로였습니다.

루프 안의 짧은 조회가 계속 쌓였다

조회 범위는 줄었지만 후보를 처리하는 루프 안에서 DB를 다시 호출하고 있었습니다. 요청 한 건이 후보를 훑는 동안 약 90~100번 DB를 오갔습니다.

각 쿼리는 전보다 빨라졌지만 짧은 조회가 한꺼번에 몰리면서 커넥션 경쟁은 계속됐습니다. 로그에도 스레드가 커넥션을 얻지 못하고 기다리는 상태가 남아 있었습니다.

HikariPool-1 - Connection is not available, request timed out after 20000ms
(total=10, active=10, idle=0, waiting=3)

실행 계획만 고쳐서는 부족했습니다. 다음으로 루프 안에서 DB를 반복해서 찾는 흐름 자체를 바꿔야 했습니다.

왜 @BatchSize는 효과가 없었을까

다음에는 @BatchSize를 적용했습니다. 연관 데이터를 여러 건씩 묶어 읽으면 반복 조회가 줄어들 것이라고 예상했습니다.

@BatchSize(size = 50)
@OneToMany(mappedBy = "member", fetch = FetchType.LAZY)
private List<TimeTable> timeTables;

적용 뒤에도 수치는 같았다

문제는 지연 로딩이 아니었다

@BatchSize는 지연 로딩된 연관 객체를 꺼낼 때 여러 건을 묶어 조회합니다. 하지만 이 코드에서는 연관 객체를 따라가지 않고 서비스가 Repository를 직접 호출하고 있었습니다.

// 서비스 코드
timeTableQueryService.findByMemberId(member.getId()); // 직접 호출

따라서 @BatchSize가 개입할 수 없었습니다. 어노테이션 설정을 더 바꾸는 대신 루프 안의 직접 조회를 없애기로 했습니다.

조회 시점을 루프 밖으로 옮겼다

스레드 상태를 보니 대부분이 계산보다 DB 응답을 기다리고 있었습니다. 스레드를 더 늘리면 기다리는 요청만 많아질 수 있어, 먼저 DB를 오가는 횟수부터 줄이기로 했습니다.

후보를 모은 뒤 한 번에 읽었다

후보마다 필요한 데이터를 다시 조회하지 않고 ID를 먼저 모았습니다. 이후 IN 절로 한 번에 가져온 뒤 Map으로 정리해 루프 안에서 사용했습니다.

// Before: 루프 내 쿼리 실행 (N+1 발생)
for (Member m : candidates) {
    // 요청당 약 150회의 쿼리가 실행되는 구조
    timeTableService.findByMemberId(m.getId());
    matchRepository.existsByMatching(currentMember, m);
}

// After: 루프 밖 일괄 조회 (IN 절 사용)
List<Long> ids = candidates.stream().map(Member::getId).toList();

// 2번의 쿼리로 관련 데이터 일괄 조회
Map<Long, List<TimeTable>> timeTables = timeTableRepository.findByMemberIdIn(ids) ...;
Set<Long> matchedIds = matchRepository.findMatchedMemberIds(currentId, ids);

for (Member m : candidates) {
    // 메모리에서 O(1) 조회 -> 추가 DB 접근 없이 처리
    process(m, timeTables.get(m.getId()), matchedIds.contains(m.getId()));
}

요청 한 건에서 실행되던 쿼리는 150회 이상에서 3회로 줄었습니다. 단순히 숫자만 줄어든 것은 아니었습니다. 루프가 돌 때마다 DB를 다시 찾던 흐름이 사라지면서 커넥션을 훨씬 빨리 반환할 수 있었습니다.

추천 API는 0.1초대로 내려왔다

그런데 전체 테스트는 여전히 느렸다

추천 API 자체는 0.1초 안팎에서 처리를 마치고 커넥션도 빠르게 반환했습니다. 그런데 여러 API를 함께 호출하는 전체 테스트에서는 30초 타임아웃이 계속됐습니다.

원인은 같은 커넥션 풀을 쓰는 필터 API였습니다. 이 API는 요청 한 번에 수천 번 DB를 조회해 커넥션을 오래 점유했습니다. 필터 API 몇 건만 들어와도 열 개의 커넥션이 금세 찼고, 이미 빨라진 추천 API조차 빈 커넥션을 기다려야 했습니다. 한 API만 고쳐서는 같은 풀에서 생기는 대기를 없앨 수 없었습니다.

병목은 다른 API에 남아 있었다

추천 API를 고친 뒤에도 필터 API가 같은 커넥션 풀을 오래 점유하고 있었습니다. 전체 응답 시간을 줄이려면 이 경로까지 함께 손봐야 했습니다.

필터 API의 반복 집계도 합쳤다

필터 API는 회원마다 집계 쿼리를 다시 호출하고 있었습니다. 회원 ID를 먼저 모은 뒤, 여러 ID를 한 번에 조회하는 IN 절과 회원별로 값을 묶는 GROUP BY를 사용해 한 번에 계산했습니다.

// Before: 루프 돌며 Count 쿼리 실행 (1,000번)
for (Member m : members) { countMatches(m); }

// After: 집계 쿼리로 일괄 조회
Map<Long, Long> matchCounts = matchRepository.countMatchesByMemberIds(memberIds);

다른 요청의 대기도 함께 줄었다

반복 집계를 한 번으로 모으자 커넥션이 계속 사용 중인 시간이 눈에 띄게 짧아졌습니다. 필터 API뿐 아니라 같은 풀을 쓰는 다른 요청도 더 빨리 커넥션을 얻을 수 있었습니다.

목록에 필요한 정보도 한 번에 가져왔다

로그를 확인하니 부서와 단과대 이름을 꺼낼 때마다 추가 쿼리가 실행되고 있었습니다. 두 정보는 목록에서 항상 필요했으므로, 연관 데이터를 처음부터 한 쿼리로 가져오는 Fetch Join을 적용했습니다.

@Query("SELECT m FROM Member m " +
       "LEFT JOIN FETCH m.department " +
       "LEFT JOIN FETCH m.college " +
       "WHERE m.university.id = :universityId AND m.id != :memberId")
List<Member> findWithDetailsByUniversityIdAndIdNot(
    @Param("universityId") Long universityId,
    @Param("memberId") Long memberId
);

목록에 필요한 정보는 처음부터 가져왔다

부서와 단과대 정보를 한 번에 가져오자 목록을 만드는 동안 반복되던 추가 조회가 더는 발생하지 않았습니다.

쿼리는 줄었지만 너무 많이 읽고 있었다

쿼리 수는 크게 줄었지만 p99는 여전히 15초였습니다. 이번에는 쿼리 횟수보다 한 번에 가져오는 데이터 양이 문제였습니다.

// 문제의 코드 패턴
List<Member> allMembers = memberRepository.findWithDetailsByUniversityIdAndIdNot(...);
// ↑ 수천 명의 데이터를 전부 가져옴

List<Member> filtered = allMembers.stream()
    .filter(member -> isFilterMatched(member, req))  // 메모리에서 필터링
    .collect(toList());

Fetch Join으로 반복 조회는 사라졌지만 수천 명을 모두 가져온 뒤 자바 코드에서 걸러내는 흐름은 남아 있었습니다. 실제로 쓰지 않을 엔티티까지 메모리에 올리느라 작은 인스턴스에 불필요한 부담을 주고 있었습니다.

필요 없는 데이터를 가져온 뒤 버리기보다, 처음부터 필요한 행만 DB에서 골라내는 편이 나았습니다.

가져온 뒤 버리지 않도록 바꿨다

자바 스트림에서 처리하던 필터 조건을 DB의 WHERE 절로 옮겼습니다. 필요한 행만 애플리케이션으로 넘어오게 했고, 자유롭게 입력하던 문자열 값은 정해진 값만 허용하는 Enum으로 바꿔 오타 가능성도 낮췄습니다.

필터링을 DB에 맡긴 결과

필터 조건을 DB로 옮긴 뒤에는 필요한 행만 넘어왔습니다. 애플리케이션에서 객체를 만들었다가 곧바로 버리는 일도 줄었습니다.

추천과 인기 API는 목표 범위에 들어왔지만 필터 API의 p95는 여전히 9초대였습니다. 학번 조건을 자바의 substring으로 처리하고 있었고, DISTINCT와 여러 LEFT JOIN FETCH가 임시 테이블과 정렬 비용을 만들고 있었습니다.

끝까지 남은 필터 API를 손봤다

이제 남은 비용은 학번 필터와 무거운 조인, 불필요한 전체 개수 조회였습니다. 각각 다음과 같이 바꿨습니다.

1. 학번 조건을 자바의 substring 처리에서 DB의 LIKE 조건으로 옮겼습니다.
2. 중복을 없애려고 사용하던 DISTINCT와 여러 Fetch Join은, 조건에 맞는 데이터가 있는지만 확인하는 EXISTS 서브쿼리로 바꿨습니다.
3. 무한 스크롤 화면에는 전체 개수가 필요하지 않았습니다. 전체 건수까지 계산하는 Page 대신 다음 데이터의 존재 여부만 확인하는 Slice를 사용해 매 요청마다 실행되던 COUNT(*) 쿼리를 없앴습니다.

// 최종 Repository 코드
@Query("SELECT m FROM Member m " +
       "WHERE m.status = 'ACTIVE' " +
       "AND m.university.id = :universityId " +
       "AND m.id != :memberId " +
       // 동적 쿼리 조건들 (DB 레벨 필터링)
       "AND (:collegeId IS NULL OR m.college.id = :collegeId) " +
       "AND (:departmentId IS NULL OR m.department.id = :departmentId) " +
       "AND (:studentNoPrefix IS NULL OR m.studentNo LIKE CONCAT(:studentNoPrefix, '%'))")
Slice<Member> findFilteredMembers(...); // Page -> Slice 변경

마지막으로 자주 사용하는 조건과 정렬 순서에 맞춰 인덱스를 추가했습니다.

CREATE INDEX idx_member_filter_opt ON member(status, university_id, updated_at DESC);

평균만으로는 충분하지 않았다

평균 응답 시간만 내려가면 일부 사용자는 여전히 오래 기다릴 수 있습니다. 절반의 요청을 보여주는 p50과 느린 요청 구간을 보여주는 p95, p99를 함께 비교했습니다. 최종 그래프에서는 세 값의 간격이 줄었고, 오래 걸리던 요청도 함께 빨라졌습니다.

타임아웃이 사라졌다

마지막 테스트에서 에러율은 0%가 됐고 p95도 목표 범위 안으로 내려왔습니다.

마지막으로 커넥션 수를 비교했다

쿼리를 모두 고친 뒤에는 커넥션 수만 바꿔 같은 테스트를 반복했습니다. 커넥션이 많으면 무조건 빨라질 것 같았지만, 작은 서버에서는 대기와 경합 사이의 균형이 더 중요했습니다.

네 가지 값을 비교했다

인스턴스당 커넥션 수만 네 가지로 바꾸고 데이터와 부하 등 나머지 조건은 그대로 유지했습니다.

커넥션 2개

요청 대부분이 쿼리를 실행하기 전에 빈 커넥션을 기다렸습니다.

커넥션이 두 개뿐일 때는 DB가 처리할 여유가 있어도 애플리케이션 앞에서 기다리는 요청이 많았습니다. p95는 1.64초였습니다.

커넥션 100개

커넥션을 늘리자 오히려 느려졌다

커넥션을 100개로 늘리자 대기열은 짧아졌지만 전체 처리량은 오히려 줄었습니다. 작은 서버가 동시에 너무 많은 작업을 처리하면서 실제 쿼리보다 스레드 전환과 경합에 더 많은 시간을 썼기 때문입니다. 커넥션을 늘리는 것만으로는 빨라지지 않았습니다.

커넥션 5개

권장값이 가장 빠르지는 않았다

1 vCPU 기준으로 계산하면 커넥션 3~5개가 자연스럽습니다. 하지만 30명이 동시에 요청하는 이번 테스트에서는 DB 효율보다 커넥션을 기다리는 시간이 응답 속도에 더 큰 영향을 줬습니다.

Pool=5는 Pool=10보다 느렸습니다. 권장값이 틀렸다는 뜻은 아닙니다. 권장식은 DB가 효율적으로 처리할 수 있는 동시 작업 수를 기준으로 하고, 실제 응답 시간에는 애플리케이션에서 커넥션을 기다리는 시간도 포함됩니다.

커넥션이 너무 적으면 요청이 앞단에서 오래 기다리고, 너무 많으면 작은 서버의 경합이 커집니다. 이번 조건에서는 커넥션이 10개일 때 p95가 가장 낮았습니다.

이번 테스트에서는 10개가 가장 빨랐다

커넥션 수에는 모든 환경에 통하는 정답이 없었습니다. 너무 적을 때 생기는 대기와 너무 많을 때 생기는 경합 사이에서 실제 부하를 걸어 균형점을 찾아야 했습니다.

다음에 같은 타임아웃을 만난다면

처음에는 서버 크기를 의심했고, 그다음에는 커넥션 수를 늘리려 했습니다. 하지만 먼저 고쳐야 할 것은 커넥션을 오래 잡는 쿼리와 루프 안의 반복 조회였습니다.

비슷한 타임아웃을 다시 만나면 요청이 실제로 처리된 시간과 빈 커넥션을 기다린 시간을 먼저 나눠 볼 생각입니다. 풀 크기는 쿼리와 반복 호출을 정리한 뒤 실제 부하로 맞추는 편이 안전합니다.

이번 작업에서 가장 크게 바뀐 건 숫자보다 확인 순서였습니다. 인덱스를 추가하는 것만으로는 반복 조회가 사라지지 않았고, @BatchSize도 Repository를 직접 호출하는 코드에는 도움이 되지 않았습니다. 결국 루프 안의 DB 왕복을 줄이고, 같은 풀을 오래 점유하는 API까지 함께 고쳐야 타임아웃이 사라졌습니다.

k6는 최종 수치를 보여주는 데도 썼지만, 각 가설을 같은 조건에서 검증하는 데 더 유용했습니다. 다음에 비슷한 문제가 생기면 서버나 풀 크기를 먼저 바꾸기보다, 요청이 실제로 일하는 시간과 기다리는 시간을 나눠서 볼 생각입니다.