대용량 데이터로 인한 API 성능 문제 해결: 커버링 인덱스 및 인덱스 튜닝을 통한 최적화

개요

최근 개발 중이던 서비스의 게시글 조회 API에서 대용량 데이터를 추가한 후 성능 테스트를 진행하는 과정에서, 데이터 추가 이전에는 나타나지 않았던 문제점들을 발견하게 되었습니다. 기존에는 원활하게 작동하던 API가 데이터가 급격히 증가하자 응답 속도 지연, 데이터베이스 서버 과부하, 일부 요청에서의 예외 발생 등 다양한 성능 저하 현상을 보였습니다. 

 

이러한 예상치 못한 성능 저하 현상은 단순히 데이터 양이 늘어났기 때문이 아니라, 증가된 데이터 볼륨에 기존 시스템이 어떻게 반응하는지에 대한 면밀한 분석이 필요하다는 점을 시사했습니다. 즉, 기존에는 드러나지 않았던 시스템의 한계나 특정 로직의 비효율성이 대용량 데이터 환경에서 발현된 것으로 판단했습니다. 

 

본 글에서는 다양한 계층의 지표들을 종합적으로 분석해 성능 저하의 근본 원인과 병목 지점을 규명하고, 이를 해결하기 위해 어떤 조치를 취했는지 그 과정을 상세히 소개하고자 합니다.

 

테스트 데이터

이번 성능 테스트에 사용된 데이터 규모는 다음과 같습니다.

Table	Size
user	1,000
post_like	50,000
post	1,000,000
content	10,000,000

 

테스트 시나리오

테스트를 위해 다음과 같은 k6 스크립트를 작성하여 게시글 조회 API에 대한 부하 시나리오를 정의했습니다.

import http from 'k6/http';
import { check, sleep } from 'k6';
import { Rate } from 'k6/metrics';

export let options = {
    stages: [
        { duration: '10s', target: 10 },
        { duration: '10s', target: 50 },
        { duration: '10s', target: 100 },
        { duration: '10s', target: 150 },
        { duration: '10s', target: 200 },
        { duration: '10s', target: 250 },
    ],
};

export let errorRate = new Rate("errors");

export default function() {
    const page = Math.floor(Math.random() * 10) + 1
    const url = `http://localhost:8080/v1/posts?page=${page}&size=10`;

    let res = http.get(url);
    let success = check(res, { 'get-posts': (r) => r.status === 200 });

    if (!success) {
        errorRate.add(1);
    }
    
    sleep(1);
}

위 스크립트는 총 60초 동안 가상 사용자를 10명에서 250명까지 점진적으로 증가시키며 게시글 조회 API를 호출하는 시나리오를 담고 있습니다. 

 

문제 상황

문제는 MySQL에 대량의 데이터를 추가한 후 K6 부하 테스트를 실행하였을 때 발생하였습니다. 

주어진 지표를 바탕으로 성능 현황을 분석한 결과, 심각한 수준의 응답 시간 지연과 더불어 오류율이 급증하고 있음이 확인되었습니다. 

 

구체적으로 살펴보면, HTTP 요청에 대한 응답 시간(http_req_duration)이 비정상적으로 길어지고 있습니다. 평균(mean) 응답 시간이 27.76s에 달하며, 전체 요청의 90%(p90)가 35.59s, 95%(p95)가 35.61s 이내에 응답을 받는 것으로 나타났습니다. 가장 빨랐던 응답 시간 또한 1.68초로, API의 일반적인 성능 기대치에 비추어 볼 때  결코 빠른 편이 아니었습니다. 이러한 수치는 대부분의 사용자 요청이 정상적인 응답을 받기까지 수 초에서 수십 초까지 소요된다는 것을 의미합니다. 

 

더욱이, 이러한 응답 지연 현상과 함께 특정 시점에서 Error per Second 지표가 급증하는 양상이 뚜렷하게 관측되었습니다. 이는 단순히 느려지는 것을 넘어, 요청을 정상적으로 처리하지 못하고 있거나 심각한 문제가 발생했을 가능성을 강력하게 시사합니다. 즉, 일부 요청은 아예 성공적으로 처리되지 못하고 오류를 반환하고 있다는 의미입니다. 

 

근본 원인 찾기

최대 CPU 사용률: 92.9%

문제의 근본적인 원인을 파악하기 위해 데이터베이스의 리소스 사용 현황을 분석한 결과, CPU 사용률이 일시적으로 90% 이상까지 급증한 상황이 확인되었습니다. 이러한 CPU 사용률의 급증은 데이터베이스 쿼리 처리 능력에 심각한 병목 현상을 초래했고, 이는 전반적인 응답 시간 지연으로 이어졌습니다. 

 

이러한 지연은 Spring 애플리케이션에서 사용하는 HikariCP 커넥션 풀에도 영향을 미쳤습니다. 커넥션이 확보된 후에도 쿼리 수행이 지연되면서 커넥션 반환이 늦어졌고, 그 결과 커넥션 풀 내에서 사용 가능한 커넥션이 부족해지는 상황이 발생했습니다. 커넥션 풀이 모두 점유되면서 이후 요청은 커넥션을 할당받지 못한 채 Pending 상태로 대기하게 되었고, 결국 애플리케이션은 데이터베이스에 접근하지 못하고 오류를 반환하는 상황으로 이어졌습니다. 

 

이러한 현상의 원인을 보다 구체적으로 파악하기 위해 게시글 조회 API에 사용되는 쿼리를 EXPLAIN ANALYZE를 통해 실행 계획을 분석한 결과, 해당 쿼리들이 비정상적으로 긴 실행 시간을 기록하고 있었음을 확인할 수 있었습니다. 해당 쿼리들은 데이터가 많아지게 되면서 대량의 데이터를 처리하거나 적절한 인덱스가 적용되지 않은 상태로 실행되고 있었습니다. 이로 인해 데이터베이스의 CPU 자원이 급격히 소모되었고, 앞서 언급한 일련의 문제를 유발한 직접적인 원인으로 판단하게 되었습니다.

 

쿼리 분석

게시글 전체 조회 API는 게시글과 해당 게시글에 포함된 콘텐츠로 구성되어 있으며, 조회 시 각 게시글의 정보와 함께 콘텐츠 중 첫 번째 이미지와 첫 번째 텍스트를 함께 반환해야 한다는 요구사항이 있었습니다. 이를 구현하기 위해, 먼저 게시글들을 조회하는 쿼리를 작성했고, 그 결과로 얻은 게시글 ID 목록을 기반으로 해당하는 콘텐츠들을 조회하는 쿼리를 Querydsl로 작성하여 사용하고 있었습니다. 

 

사용된 쿼리

주어진 요구사항에 맞춰 기능을 구현하고자 아래와 같은 방식으로 코드를 작성하였습니다. 

@Service
@Transactional(readOnly=true)
@RequiredArgsConstructor
public class PostService {

    private final PostRepository postRepository;
    private final ContentRepository contentRepository;
    private final UserRepository userRepository;
    
    public SliceResponse<PostFindAllResponse> findAllPost(OffsetLimit offsetLimit) {
        // 1. 게시글 페이징 조회
        List<PostQueryEntity> posts = postRepository.findAllPost(offsetLimit);
        // 2. 게시글 ID들에 해당하는 콘텐츠 조회
        List<ContentQueryEntity> contents = contentRepository.findAllByPostIds(posts.getIds());
        
        // 3. PostFindAllRespose에서 데이터 결합
        return SliceResponse.of(PostFindAllResponse.toList(posts, contents), offsetLimit);
    }
}

postRepository.findAllPost(offsetLimit)를 통해 페이지 및 사이즈에 해당하는 게시글 정보를 먼저 조회합니다. 이어서 contentRepository.findAllByPostIds(postIds)를 사용하여 조회된 게시글 ID들에 해당하는 모든 콘텐츠를 가져옵니다. 최종적으로 PostFindAllResponse에서 게시글 데이터와 콘텐츠 데이터를 결합하는데, 이때 게시글과 postId가 일치하는 콘텐츠 중 ContentType이 TEXT인 것중 contentId가 가장 작은 것 하나와 ContentType이 IMAGE인 것 중 contentId가 가장 작은 것 하나를 선택하여 결합한 후 데이터를 반환합니다.

 

앞서 설명한 콘텐츠 조회 조건을 만족시키기 위해 Querydsl을 사용하여 쿼리를 작성하였고, 이때 데이터베이스에서 실제로 실행되는 SQL 쿼리는 아래와 같습니다.

 

1. 게시글 페이징 조회

SELECT 
    p.post_id as postId,
    p.post_title as postTitle,
    u.user_name as username,
    u.user_profile_image as userProfileImage,
    p.registered_date_time as postRegisteredDateTime,
    count(pl.id) as likeCount
FROM 
	post p
INNER JOIN 
	users u on u.user_id = p.user_id
LEFT JOIN 
	post_like pl on pl.post_id = p.post_id
GROUP BY
	p.post_id, p.post_title, u.user_name, u.user_profile_image, p.registered_date_time
ORDER BY 
	p.post_id desc
LIMIT 10
OFFSET 0;

 

2. 게시글 ID에 해당하는 콘텐츠 조회

SELECT 
    c.post_id as postId,
    c.content_id as contentId,
    c.content_type as contentType,
    c.content_value as contentValue
FROM 
    content c
WHERE 
    c.post_id in (post_ids);

이제부터 앞서 작성한 쿼리들이 데이터 양이 많아질수록 왜 성능 저하를 일으키는지, 그 원인을 구체적으로 살펴보겠습니다. 

 

게시글 페이징 조회 쿼리 분석

쿼리 실행 시간: 4s 312ms (execution: 4ss 262ms, fetching: 50ms)

해당 쿼리에 대해 EXPLAIN ANALYZE를 수행한 결과, 아래와 같은 실행 계획이 출력되었습니다. 

 

위 실행 계획을 분석해 보면, 여러 성능 저하 요인이 복합적으로 작용하고 있음을 확인할 수 있습니다. 

 

1. 대량의 데이터 조인

우선, 인덱스 스캔을 사용하더라도 user와 post 테이블 간 조인 과정에서 상당한 시간이 소요되고 있습니다. 이는 조인 조건에 따라 많은 양의 데이터를 탐색해야 하며, 효율적인 인덱스 활용이 제한적인 구조일 경우 더욱 심각한 병목으로 이어질 수 있습니다.

 

2. 좋아요 집계 시 발생하는 그룹화 비용

또한, 게시글에 대한 좋아요 수를 집계하기 위한 연산에서 대량의 데이터를 임시 테이블에 로드한 후 그룹화하는 과정이 발생하는데, 이 단계에서 매우 높은 비용이 들고 있습니다. 특히 좋아요 데이터가 많아질수록 그룹화에 필요한 연산량도 기하급수적으로 증가하게 되며, 이는 전체 쿼리 성능에 치명적인 영향을 미칩니다.

 

3. OFFSET/LIMIT의 비효율적인 페이징 처리

마지막으로, 페이징 처리를 위해 사용된 OFFSET과 LIMIT절이 실행 계획의 가장 마지막 단계에서 적용되고 있기 때문에, 실제로 필요한 소수의 결과를 얻기 위해서도 전체 결과 셋을 거의 모두 처리해야 하는 비효율이 발생하고 있습니다. 이러한 접근 방식은 특히 데이터 양이 많은 상황에서 불필요한 데이터 읽기와 정렬을 유발하여, 전체 쿼리의 응답 시간을 크게 지연시키는 원인이 됩니다.

 

게시글 ID에 해당하는 콘텐츠 조회 쿼리 분석

쿼리 실행 시간: 393ms (excution: 22ms, fetching: 371ms)

해당 쿼리에 대해 EXPLAIN ANALYZE를 수행한 결과, 아래와 같은  실행 계획이 출력되었습니다.

 

해당 실행 계획을 보면 크게 지연될 만한 특별한 부분은 눈에 띄지 않습니다. 실행 계획 자체는 인덱스를 잘 활용하고 있어 큰 문제가 없어보이지만, 각 단계별로 처리되는 실제 데이터 양(Actual Rows)이 상당히 많다는 것을 확인할 수 있었습니다. 

 

이처럼, 인덱스 스캔으로 데이터를 빠르게 가져온다고 해도, 20,000개라는 상당한 양을 읽어야 하는 점이 문제입니다. 인덱스를 잘 활용하고 있음에도 불구하고, 결국 처리해야 할 데이터의 절대적인 양이 많다는 것이 핵심입니다. 이렇게 불어난 데이터는 스프링 애플리케이션으로 전달될 때 메모리 사용량 증가로 이어져 애플리케이션 성능에 부정적인 영향 또한 미칠 수 있습니다. 

 

쿼리 개선

게시글 조회 쿼리 개선

SELECT 
    p.post_id as postId,
    p.post_title as postTitle,
    u.user_name as username,
    u.user_profile_image as userProfileImage,
    p.registered_date_time as postRegisteredDateTime,
    count(pl.id) as likeCount
FROM 
	post p
INNER JOIN 
	users u on u.user_id = p.user_id
INNER JOIN (
    SELECT post_id
    FROM post
    ORDER BY post_id desc
    LIMIT 10
    OFFSET 0
) as latest_post on latest_post.post_id = p.post_id
LEFT JOIN 
	post_like pl on pl.post_id = p.post_id
GROUP BY
	p.post_id, p.post_title, u.user_name, u.user_profile_image, p.registered_date_time;

기존 쿼리에서는 최신 게시물을 가져오기 위해 모든 post, user, post_like 데이터를 조인한 후 정렬하고 집계하는 방식이었습니다. 이로 인해 쿼리가  많아질수록 전체 테이블에 대한 연산이 발생하여 성능이 저하된 상황이었습니다. 이를 개선하고자 커버링 인덱스를 사용하는 서브쿼리를 사용해서, post_id를 기준으로 limit에 맞게 먼저 선별하도록 쿼리 구조를 변경했습니다. 

 

특히, 이 서브쿼리는 post_id 컬럼만 선택하고 post_id로 정렬하기 때문에, post_id에 이미 생성되어 있는 클러스터링 인덱스가 커버링 인덱스 역할을 하게 됩니다. 즉, 데이터베이스는 실제 테이블 데이터에 접근할 필요 없이 인덱스만으로 필요한 post_id를 빠르게 찾아낼 수 있습니다. 이렇게 찾아진 최소한의 post_id 데이터만 가지고 나머지 테이블들과 조인을 수행함으로써, 불필요한 대규모 조인 연산을 피할 수 있습니다. 

 

게시글 ID에 해당하는 콘텐츠 조회 쿼리 개선

create index idx_content_post_id_type_id on content(post_id, content_type, content_id);

SELECT 
    c.content_id as contentId,
    c.post_id as postId,
    c.content_type as contentType,
    c.content_value as contentValue 
FROM 
	content c
INNER JOIN (
	SELECT 
    	MIN(c_min.content_id) as min_content_id
    FROM 
    	content c_min
    WHERE
    	c_min.post_id in (post_ids)
    GROUP BY
    	c_min.post_id, c_min.content_type
) as min_ids on content.id = min_ids.min_content_id;

기존 쿼리는 특정 post_ids에 해당하는 모든 콘텐츠를 가져오는 방식이었습니다. 이 방식은 post_id당 여러 콘텐츠가 있을 때, 모든 데이터를 전송받아 애플리케이션 레벨에서 다시 필터링했기에 불필요하게 많은 데이터를 전송하는 비효율이 있었습니다.

 

하지만 실제로 필요한 데이터는 post_id와 content_type으로 그룹핑된 데이터 중 최소 content_id를 가진 레코드들이었기 때문에, 모든 데이터를 가져올 필요가 전혀 없었습니다. 이 쿼리를 개선하기 위해 content 테이블에 (post_id, content_type, content_id) 복합 인덱스를 생성하고, 서브쿼리에서 이 인덱스를 커버링 인덱스로 활용하여 필요한 데이터만을 효율적으로 조회할 수 있도록 수정했습니다. 

 

실행 계획을 보면 content 테이블에 대한 불필요한 전체 스캔없이, 인덱스만으로 post_id 및 content_type을 기반으로 데이터를 필터링 하고 필요한 content_id까지 빠르게 조회하고 MIN(content_id) 집계가 이루어집니다. 이렇게 인덱스만으로 이미 최소화된 데이터셋을 추출하여 조인하기 때문에, 불필요하게 모든 데이터를 애플리케이션 레벨로 전송하는 오버헤드를 없애고 쿼리 속도도 개선할 수 있었습니다.

 

개선  결과

위와 같이 쿼리 개선을 완료한 후, K6 부하 테스트를 다시 수행하여 시스템의 성능 변화를 측정하였습니다. 

 

부하테스트 지표

개선 전

개선 후

개선 전에는 HTTP 요청 처리 시간이 심각한 문제였습니다. 평균 응답 시간이 27.76s, p90 지표에서 34.59s로 전반적으로 높은 응답 시간이 지속되고 있었습니다. 또한 응답 시간문제 외에도 에러가 주기적으로 발생하는 패턴을 보이는 상황이었습니다. 

 

개선 후에는 HTTP 요청 처리 시간이 획기적으로 단축되었습니다. 평균 응답 시간이 5.84ms, p90 지표에서 7.43ms로 대폭 감소했습니다. 또한 과거 주기적으로 발생했던 에러 발생률이 완전히 사라졌습니다.

 

데이터베이스 관련 지표

개선 전에는 동일한 기능을 하는 쿼리가 낮은 QPS에서도 CPU를 과도하게 소모하며 시스템 병목을 유발했지만, 쿼리 개선 후에는 QPS를 획기적으로 높이면서도 CPU 사용량은 오히려 현저히 낮추는 데 성공했습니다.

 

또한, 애플리케이션 레벨에서의 HikariCP Connection Pool 또한 개선을 통해 큰 변화를 보였습니다. 이전에는 222회의 타임아웃과 최대 189개의 대기(Pending) 커넥션이 발생하였으나, 이제는 타임아웃이 전혀 없고 대기 커넥션도 없는 안정적인 커넥션 풀 상태를 유지하도록 개선되었습니다. 

 

최대 부하 성능

개선된 시스템의 최대 성능을 확인하기 위해 6,000 VUs 환경에서 K6 부하 테스트를 수행하였습니다. 

3.5K VUs 에서 RPS가 증가하지 않음

테스트 결과, 약 3.5K VUs까지는 RPS가 지속적으로 증가했지만, 그 이후부터는 더 이상 증가하지 않고 포화 상태에 도달하는 양상이 관측되었습니다. 이에 따라 최대 부하 지점을 3.5K VUs 수준으로 판단하고 해당 지표를 기준으로 측정하였습니다. 

 

평균 응답 시간 개선: 27.76s → 15.16ms (1830% 응답 시간 개선)

평균 QPS: 8.75 → 15.5K

CPU 최대 사용률: 92.9% → 23.1%

주요 성능 지표 비교

해당 개선 작업을 통해, 이전에 발생하던 심각한 성능 저하 문제를 성공적으로 해결하였으며, 3,500 VUs까지 안정적인 RPS 증가와 함께 평균 응답 시간 99.95% 단축이라는 성과를 달성하였습니다.

 

또한, 평균 QPS는 기존 8.75에서 15.5K로 약 1,771배 증가하였으며, 더 많은 작업을 처리하면서도 CPU 최대 사용률이 92.9%에서 23.1%로 크게 감소하였습니다. 여기에 더해 오류율 0%를 유지함으로써, 서비스의 처리량, 안정성, 자원 효율성 모두가 크게 향상되었음을 확인할 수 있었습니다.