이슈 전체 조회 성능 개선하기

준비 사항

• 프로젝트 1만개 생성
• 멤버 1만개 생성
• 프로젝트와 멤버 1대1 매칭
  • 예를들어 1번 프로젝트는 1번 멤버, 2번 프로젝트는 2번 멤버, n번 프로젝트는 n번 멤버가 속합니다.
  • 성능 비교를 위한 것은 프로젝트와 멤버가 아닌 이슈이기 때문에 최대한 간단하게 매칭 시켰습니다.
• EPIC 이슈는 100개 생성
• STORY 이슈는 에픽당 200개씩 총 2만개의 스토리 이슈 생성
• TASK 이슈는 스토리당 200개씩 총 4만개의 테스크 이슈 생성
• 모든 이슈는 1번 프로젝트에 속하도록 합니다.

 

문제 사항

1. 로직 리팩토링

 

더미데이터를 넣고 이슈 전체 조회 API를 요청 했지만 쿼리가 돌다가 응답이 오지 않고 멈췄습니다.(성능측정을 해야지하고 몇 초나 걸릴까를 생각했는데 그냥 아예 팅겨버린..)

이유가 당연했습니다. 로직이 다음과 같이 구현되어있습니다.

/api/projects/{key}/issues 해당 API를 요청하면

 

generateCompleteEpicRootStructure(project, responses);
generateCompleteStoryRootStructure(project, responses);
generateCompleteTaskRootStructure(project, responses);

 

3번의 로직이 돕니다.

• 첫번째 로직은 프로젝트에 속한 에픽이 루트로 시작되어 조회.
• 두번째 로직은 부모 이슈가 없는 스토리가 루트로 시작되어 조회
• 세번째 로직은 부모 이슈가 없는 테스크가 루트로 시작되어 조회

이 세개의 로직은 비슷하며, 첫번째 로직만 보겠습니다.

 

 

findEpicsByProject(project) 는 프로젝트에 속한 에픽들을 모두 가져온다라는 로직입니다.

 

쿼리는 다음과 같이 JPQL로 작성되어있습니다.

@Query("SELECT e FROM Epic e WHERE e.project = :project")

 

두번째 쿼리를 보면 가져온 에픽들을 토대로 에픽아이디들만 묶어서 해당 에픽들을 부모이슈로 가지고 있는 스토리들을 모두 가져옵니다.

 

이때 in 절 한방쿼리로 성능을 개선했습니다.

@Query("SELECT s FROM Story s WHERE s.epic.id IN :epicIds")

 

세번째 쿼리를 보면 @Query("SELECT t FROM Task t WHERE t.story.id IN :storyIds")

 

가져온 스토리들을 토대로 테스크를 모두 가져오도록 합니다. 총 2만개의 스토리아이디가 in절에 들어가고 해당 스토리 아이디를 가지고 있는 테스크는 2만*200이므로 400만개의 테스크를 가져오게 됩니다.

 

당연히 400만개의 데이터를 준비하는 동안 타임아웃이 나고 네트워크는 끊기게 됩니다.

 

 

그런데 디비는 타임아웃과 같은 어떠한 예외도 터지지 않았습니다.

mysql에서 타임아웃을 확인하는 명령어는 다음과 같습니다.

show variables like 'interactive%';

 

28800초로 총 8시간입니다. 이렇게 긴 시간으로 기본값이 설정된 이유는 잘 모르겠지만 커넥션 타임아웃 관련해서도 조심해야 할 사항이 많은 것 같습니다.

 

https://netmarble.engineering/jdbc-timeout-for-game-server/

https://d2.naver.com/helloworld/1321

 

정확히 테스크에서 가져오는 곳에서 문제가 있는 지 확인해보기 위해 테스트코드를 작성해보겠습니다.

@SpringBootTest
@Profile("local")
class TaskRepositoryTest {

    @Autowired
    private TaskRepository taskRepository;

    @Test
    void 로컬에서_저장한_이슈들_정상적으로_가져오는지_조회() {
        // given
        List<Long> storyIds = new ArrayList<>();
        for (long i = 101L; i < 20100L; i++) {
            storyIds.add(i);
        }
        // when
        List<Task> tasks = taskRepository.findTasksByStoryIds(storyIds);
        // then
        assertThat(tasks.size()).isEqualTo(4_000_000L);
    }
}

 

이 상태로 실행해보면 당연하게도 OOM이 발생하고 실패합니다. 즉, 400만개의 데이터를 넣는 것은 불가능합니다.

 

따라서 아래처럼 한번에 가져오는 방식이었던 기존 UI에서 접이식 방식으로 바꾸고 API를 분리하는 방식으로 가도록 했습니다.

 

기존 API

- 프로젝트에 할당된 모든 이슈들을 관계가 맺어진채 가져오는 API

 

변화된 API

• 프로젝트에 할당된 에픽만 가져오는 API
• 에픽 아이디를 부모이슈로 요청해서 스토리들만 가져오는 API
• 스토리 아이디를 부모이슈로 요청해서 테스크들만 가져오는 API

예상 UI

 

 

세가지 API를 분리함으로써 좀 더 확장성이 커진 이점이 있었습니다. 예를들면 에픽을 조회할때 상단 이미지처럼 에픽에 속하는 스토리들의 상태에 대한 통계가 필요했는데 그 역시 쉽게 추가할 수 있었습니다.

 

가장 핵심인(사용자가 처음 진입할때 먼저보이게되는 API) 에픽만 가져오는 API를 보겠습니다.

1. 전체 에픽 로드: 프로젝트의 시작 단계에서 전체 에픽을 불러오는 과정입니다.
2. DTO 변환 과정: AssigneeDto의 경우 이전에는 성능 문제로 인해 특정 데이터를 제공하지 못했지만, 최적화를 통해 이제는 필요한 모든 정보를 효율적으로 제공할 수 있습니다.
3. 스토리 통계 로직: 각 에픽에 속하는 스토리들에 대한 통계를 취합하는 과정입니다. 이 단계에서는 QueryDsl과 Projection을 사용하여 데이터베이스에서 직접 필요한 데이터를 추출하고, 이를 DTO에 매핑합니다.

 

2. EXPLAIN ANALYZE로 쿼리가 잘못된 것을 파악후 쿼리 변경

이제 조회방식이 달라졌기 때문에 기존 에픽에 100만 개의 데이터를 더추가하고 스토리에는 200만개의 데이터를 추가한다음 에픽 10번에 해당 200만개의 데이터를 상위이슈로 정한채 성능을 진단했습니다.

 

에픽에 있는 스토리들의 통계를 가져오는 쿼리 입니다.

 

select
            s1_0.epic_id,
            count(s1_0.issue_id),
            sum(case 
                when (i1_0.status="DO") 
                    then 1 
                else 0 
            end),
            sum(case 
                when (i1_0.status="PROGRESS") 
                    then 1 
                else 0 
            end),
            sum(case 
                when (i1_0.status="DONE") 
                    then 1 
                else 0 
            end) 
        from
            story s1_0 
        left join
            issue i1_0 
                on s1_0.issue_id=i1_0.issue_id 
        left join
            epic e1_0 
                on e1_0.issue_id=s1_0.epic_id 
        where
            i1_0.project_id=1 
        group by
            s1_0.epic_id 
        order by
            s1_0.epic_id;

 

아래 EXPLAIN ANALYZE 로 확인하면

 

 

1. story의 epic_id 인덱스를 통해 커버링 인덱싱
2. epic 테이블의 PRIMARY 키(issue_id)를 통해 (issue_id = story의 epic_id)인 레코드를 찾는다.
3. 그리고나서 1번의 결과와 2번의 결과를 조인한다.

여기서 3번 수행이 매우 이상합니다. P1에 속하는 에픽들을 조회하는 건데 에픽은 모두 포함해야 하지만 해당 부분을 보니 story테이블이 드라이빙 테이블이 되면 결과가 이상해질거라고 예상이 갔습니다.

 

따라서 P1에 속하는 EPIC을 여러개 생성하고 한 EPIC에만 STORY를 매핑시키고 다른 EPIC에는 어떠한 스토리도 넣지 않고 테스트를 해봤습니다. 역시 기대와 달리 이상하게 나왔습니다. 쿼리 부터 잘못된 것입니다.

 

 

 

3. SubQuery로 변경 후 성능 개선

재작성된 쿼리

SELECT
    e.issue_id AS epic_id,
    COUNT(s.issue_id) AS stories_count,
    SUM(CASE WHEN i.status = 'DO' THEN 1 ELSE 0 END) AS status_do,
    SUM(CASE WHEN i.status = 'PROGRESS' THEN 1 ELSE 0 END) AS status_progress,
    SUM(CASE WHEN i.status = 'DONE' THEN 1 ELSE 0 END) AS status_done
FROM
    epic e
    LEFT JOIN story s ON e.issue_id = s.epic_id
    LEFT JOIN issue i ON s.issue_id = i.issue_id
GROUP BY
    e.issue_id;

 

 

 

재작성된 쿼리를 보면 에픽과 스토리와 이슈를 LEFT JOIN 을 하여 통계를 내고 있습니다. 여기서 스토리와 이슈는 특정 칼럼만 필요한데 과연 모든 칼럼과 모든 데이터들을 조회할 필요가 있을까해서 서브 쿼리로 다시 변경했습니다.

 

서브 쿼리로 변경

SELECT
    e.issue_id AS epic_id,
    COUNT(DISTINCT s.issue_id) AS stories_count,
    SUM(CASE WHEN i.status = 'DO' THEN 1 ELSE 0 END) AS status_do,
    SUM(CASE WHEN i.status = 'PROGRESS' THEN 1 ELSE 0 END) AS status_progress,
    SUM(CASE WHEN i.status = 'DONE' THEN 1 ELSE 0 END) AS status_done
FROM
    epic e
    LEFT JOIN (SELECT issue_id, epic_id FROM story WHERE epic_id IS NOT NULL) s ON e.issue_id = s.epic_id
    LEFT JOIN (SELECT issue_id, status from issue WHERE project_id = 1) i ON i.issue_id = s.issue_id

GROUP BY
    e.issue_id;

 

 

LEFT JOIN 절에 서브쿼리를 이용하여 필요한 칼럼들만 추출하였더니 group by절을 통해 불러오는 데이터 양이 감소하여 기존 3.59s에서 1.25s 로 성능이 개선되었습니다.

 

4. native query와 projection으로 querydsl에서 지원하지 않는 from SubQuery 해결

 

Querydsl에서는 from 절에 SubQuery 를 지원하지 않습니다. → 네이티브 쿼리로 해결하던지 JOIN으로 합니다.

hibernate 6.1 부터는 from절에 서브쿼리 지원 (하지만 Querydsl은 여전히 지원안함 ->

추후에 알게된 사실: blaz-persistence 가 querydsl 익스텐션으로 from 절 서브쿼리 지원

https://javadoc.io/doc/com.blazebit/blaze-persistence-integration-querydsl-expressions/latest/com/blazebit/persistence/querydsl/package-summary.html)

 

 

 

현재 프로젝트는 hibernate 6.4.4 이므로 사용가능 합니다. 하지만 criteria은 자주 사용되지 않습니다. HQL로 객체지향적으로 쿼리를 작성할 수 있긴하지만 네이티브로 이번엔 작성해봤습니다.

 

따라서 아래 글을 참고하여 native query와 projection으로 위 성능을 챙길 수가 있게 되었습니다.

https://medium.com/swlh/spring-data-jpa-projection-support-for-native-queries-a13cd88ec166

Spring Data JPA Projection support for native queries