[개선] 백만 단위 데이터 처리를 위한 커버링 인덱스 도입 (속도 184배 향상)

커뮤니티 서비스는 likelion univeristy 서비스의 핵심 기능 중 하나이다.

 

유저들에게 가치를 전달할 수 있는 핵심 도메인이다.

 

즉, 서비스의 성공의 선두에 커뮤니티 서비스가 제공되어야 한다.

 

그럼에도, 현재는 쿼리 하나하나를 무겁게 실행하고 있어 데이터가 조금만 많아져도 서버가 많은 부담을 느끼고 있다.

 

이를 확인하고자 먼저 테스트 데이터를 강제로 늘려보았다. 운영 DB를 mysql로 사용하고 있기 때문에 이와 최대한 근사한 환경을 구축하고자 로컬 test db로 mysql로 구성하였다. 테스트용 더미데이터 입력을 위해 프로시저를 제작하여 등록하여 호출을 통해 데이터 복제를 수행하였다.

CREATE DEFINER=`likelion`@`localhost` PROCEDURE `InsertMillionRows`()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE curr_date DATETIME DEFAULT CURRENT_TIMESTAMP;
  DECLARE main_category_list VARCHAR(255) DEFAULT '...';
  DECLARE sub_category_list VARCHAR(255) DEFAULT '...';

  WHILE i <= 1000000 DO
    INSERT INTO post (created_date, modified_date, body, main_category, sub_category, thumbnail, title, user_id) 
    VALUES (
      curr_date, 
      curr_date, 
      CONCAT('body', i), 
      ELT(FLOOR(1 + RAND() * 5), '...'), 
      ELT(FLOOR(1 + RAND() * 10), '...'), 
      CONCAT('thumbnail', i), 
      CONCAT('title', i), 
      1
    );
    SET i = i + 1;
  END WHILE;
END

 

위와 같이 routine을 등록해주면 간단하게 아래와 같이 호출함으로써 쿼리를 수행할 수 있다.

CALL InsertMillionRows();

 

workbench는 mysql에서 공식적으로 제공하는 툴이다보니 확실히 datagrip과 같은 범용 툴에 비해 mysql 스키마에 최적화되어 쿼리를 처리하는 것으로 느껴졌다. 하지만 timeout의 default value가 30초라서 테스트 데이터 생성을 위한 bulk row insert procedure 코드를 수행하는 과정에서 시간이 오래 걸리다보니 계속 중단이 일어났다.

 

따라서 mysql workbench의 timeout을 해제하여 수행하고자 했다.

 

 

Edit -> Preferences -> SQL Editor 를 누르면 위와 같은 화면이 뜬다. 여기서 가운데와 아래 시간을 600으로 설정해 줬다.

 

 

일단 timeout 문제는 해결했다.

 

그럼 이제 식별자를 기준으로 데이터 개수를 조회해보자.

현재 더미 게시글 개수는 2,108,834개이다. 약 2백만 개 정도 되는 데이터를 넣어두었다. 이제 이걸 조회하는 API가 지금 어떻게 구동되는지 한번 postman으로 응답 속도를 체크해 봤다.

캐시가 없는 상태에서 1분 47초나 걸렸다. 이는 로컬 DB 기준이며, 실제 DB에서는 사용하는 DB 서버 스펙에 따라 변동될 순 있겠지만, 근본적으로 2분 가까이 시간이 걸리고 있는 거라서 처리 속도 문제가 매우매우 상당히 심각한 상황이라는 건 명확하다고 판단했다.

 

(이후 이걸 재수행했을 때에는 추정상 OS 캐시 때문에 조금 빨라진 것을 확인할 수 있었다. DB 조회시 디스크 I/O 연산을 수행하는 건데, OS 캐시, MySQL 쿼리 캐시, DBMS 버퍼 풀, SSD 캐시 등이 성능 측정에 영향을 줄 수 있다. 나는 윈도우 환경에서 수행했고, SSD를 사용하고 있으므로 아무리 SQL_NO_CACHE 키워드로 쿼리 캐시를 비우고, flush tables 명령어로 버퍼 풀을 지워줘도 강력한 캐싱을 막을 수 없었다... 그래서 재실행하니까 약 20초 정도로 성능이 이미 최적화되었다.)

 

문제가 무엇인가?

현재 해당 API가 수행하는 연산을 재현하기 위해 수행한 쿼리는 다음과 같다. 백만건을 조회할 때, 특정 기준으로 정렬을 하고 페이지네이션을 수행해야 한다.

# DBMS 버퍼 풀 비우기
FLUSH TABLES;

# API query
select SQL_NO_CACHE * 
from post 
join users on users.id=post.user_id 
where post.id in (select id from post where post.main_category='HQ_BOARD' AND post.sub_category = 'NOTICE')
order by post.created_date desc
LIMIT 5 OFFSET 1000000;

 

근데 이 모든 과정에서 데이터를 처리하고 조회하기 위해 항상 데이터 블록에 접근해야 한다는 게 문제다. 이걸 풀 테이블 스캔이라고 하는데, 사용하지 않을 데이터를 포함하는 전체 데이터에 대하여 Disk I/O를 수행하는 거라서 매우 비효율적이고 느린 연산이다. (가지치기가 되지 않은 DFS 탐색이라고 이해하면 된다.)

 

그럼 이걸 어떻게 개선할까?

 

1. 필요한 인덱스 생성

필요한 데이터가 어떤 것들일지 알고 데이터 블록에 접근할 수 있다면 성능이 개선될 것이다. 현재는 cluster index인 pk와 non-cluster index로 fk만 생성되어 있다.

 

따라서 아까 작성한 쿼리의 실행 계획을 보면 서브쿼리 내에서 cluster index를 조회할 때에만 살짝 인덱스가 사용되고, 전부 where이나 다른 걸 사용할 때에는 무식하게 모든 정보를 데이터 블록에 접근하여 조회하고 있는 것으로 이해할 수 있다.

 

따라서 이를 개선하기 위해 우선 created_date 와 category 기준으로 인덱스를 먹여주었다. 카디널리티가 큰 값을 기준으로 순서를 지정하여 인덱스 성능이 좋을 수 있도록 순서를 고려하였다.

CREATE INDEX IDX_CATEGORIES_LATEST ON post 
(created_date, sub_category, main_category);

 

문제 없이 인덱스가 잘 생성되었다.

2. 쿼리 수정

문제는, 현재 쿼리로는 게시글 목록이나 상세 조회 API의 경우 post 테이블 뿐만 아니라 users 테이블의 데이터를 대부분 조회해야 하므로 모든 칼럼을 커버할 순 없었다.

 

따라서 querydsl 기준으로 category가 일치하는 게시글들만 인덱스로 빠르게 조회하여 모든 데이터에 대하여 데이터블록에 접근하고서야 카테고리를 확인하고 돌아가는 일이 없도록 쿼리를 수정하였다.

 

그렇게 수행되는 쿼리 구조는 다음과 같다.

select *
from post
join user on post.user_id = user.id
where post.id in (select id 
                  from post 
                  where post.main_category='HQ_BOARD' 
                  AND post.sub_category = 'NOTICE' 
                  order by post.created_date desc
                  LIMIT 5 OFFSET 1);

 

index를 더욱 적극적으로 활용하기 위해 서브쿼리에 커버링 인덱스가 적용되도록 쿼리를 수정한 뒤, 이를 explain 키워드로 확인해보니 의도한 인덱스가 잘 쓰이고 있는 걸 확인할 수 있었다.

 

이를 이용하여 카테고리별 최신순 게시글 조회에 OS 캐시 없는 상태에서 조회한 결과가 583 ms까지 줄어들었다.

 

이는 숫자로 비교하면 107,000 ms -> 583 ms로 약 184배 정도 개선된 성능을 보여준다.

 

레슨런

Real MySQL을 읽으면서 MySQL에서는 InnoDB라는 스토리지 엔진을 사용한다는 점, 학교에서 데이터베이스를 배우면서 B트리와 B+트리 자료구조를 배우고 이해했던 점 등이 어우러져 디버깅 방향성을 설정할 수 있었다. 세상에는 이미 많은 지식이 흩뿌려져 있지만, 원리를 알지 못하면 문제가 발생했을 때 원인을 예상하기 쉽지 않다. 원인조차 모르면 디버깅 방향성을 잡는 것부터 어렵기 마련이다. 결과적으로, 우리가 컴퓨터 공학 지식과 사용 기술에 대한 이해도를 갖춰야 하는 이유를 몸소 체감하게 되었다.

 

무엇이든지 해결하고 난 뒤 문제를 다시 바라보면 참 솔루션이 간단해 보이고, 이런 것들조차 내가 모르고 코딩을 하고 있었구나 하는 것들이 많았던 것 같다. 화려한 아키텍처에 매혹되지 말고 기본을 더 다져야 할 때인 것 같다. 나는 아직 많이 부족하다.