인덱스 기반 페이지네이션 성능 문제 테스트와 해결

커서 기반 페이지네이션으로 전환

PicUp 프로젝트는 앨범 기반의 사진 클라우드 스토리지 서비스이다. 프로젝트에서 기존 Offset 기반의 페이지네이션에서 Cursor 기반의 페이지 네이션으로 방식을 변경하는 과정을 정리하려고 한다.

 

이 글에선 세가지 키워드를 다룬다.

- Offset based 의 성능 문제

- 100 만개 더미 데이터 생성 / LOAD DATA INFILE 방식 업로드

- 쿼리 테스트 결과

 

Offset based vs Cursor based

Offset 기반 페이지네이션에는 유명한 2가지 문제가 있다. 데이터 중복 / 누락 조회 문제, 퍼포먼스 문제. 

 

데이터 중복 / 누락 문제는 일상에서 만나기 쉬워 예상하기 쉬울 것 같다. 3 페이지를 보던 와중에 다른 글이 많이 추가되면 4 페이지로 이동했을 때 이전 3 페이지에서 보던 글이 조회된다. 이건 그렇게 크리티컬하지 않다고 생각했다. 유저들도 그냥 다른 글이 추가되었겠거니 할테니 말이다. 

 

 

 

 

더 큰 문제는 퍼포먼스 문제에 있다. offset 기반 페이지네이션에선 오프셋이 클 경우 조회 시간이 급격히 늘어난다. 아래는 offset 기반과 cursor 기반의 페이지네이션에서 데이터가 커짐에 따른 조회 시간을 나타낸 그래프이다. 7백만개의 데이터에서 13초나 걸리는 offset 기반의 페이지네이션과 달리 커서 기반의 페이지네이션은 다른 변화가 없는 겂을 볼 수 있다.

 

"MySQL and PostgreSQL offer the offset clause for discarding the specified number of rows from the beginning of a top-N query. The limit clause is applied afterwards."

 

그 이유는 Mysql 의 offset 구현이 그저 페이지까지의 전부를 읽고 offset 이전 데이터를 버리는 형식이기 때문이다. (정확한 reference 는 위 Mysql 뿐이지만 oracle이나 다른 대다수의 rdbms 도 마찬가지인 듯 하다.)

 

 

 

 

그래서 offset 이 커지면 커질 수록 조회해야 하는 데이터는 점점 많아지고 결국 사용자는 page 수가 늘어날 수록 느린 응답을 받게 되는 것이다.

 

테스트 환경 준비

1. MySQL 8.0과 제거된 쿼리 캐시 

 

MySQL 8.0 에서 5개 컬럼, 100만개 데이터로 그 둘의 성능 차이를 확인한다.

 

이전까지 성능 테스트에선 MySQL 5.6 을 사용했었는데 쿼리 캐시로 테스트가 명확하게 안되는 문제를 걱정했었다. MySQL 5.7버전부터 쿼리 캐시는 deprecated 되고, 8.0 부터는 아예 제거되어 테스트 안에서 쿼리 캐시에 대한 고민은 덜 할 수 있었다.

 

몇 번 듣고, 읽어도 왜 쿼리 캐시를 제거하는게 더 이득인지 잘 이해가 가지 않는데 혹시 참고하실 분은 MySQL 8.0: Retiring Support for the Query Cache 을 읽어봐도 좋을 것 같다. 

 

2. file load 방식으로 더미데이터 준비

 

100만개의 데이터를 CSV 파일로 만들어 'LOAD DATA INFILE'으로 업로드 했다. 처음엔 1억개 데이터를 bulk insert 했었는데 30분 이상 소요되어 다른 방법을 찾다가 file load 가 가장 빠름을 확인하고 그 방법으로 업로드했다.

 

insert 실행 위치도 영향이 있었던 것 같다. 외부에 파일을 두고 MySql 서버에 연결하고 수행하는거보다 MySQL 서버 내부에 파일을 옮기고 수행하는게 훨씬 빨랐고, 보안 설정에도 문제를 안 일으켜서 편했다. 

 

csv 파일의 더미데이터는 코드를 짜서 만들었다. 5개 컬럼 100만개 데이터는 20MB, 파일 생성에는 1초가 걸렸고 1억개 데이터는 2GB, 파일 생성에는 100초가 걸렸다. 1억개 데이터는 DB 메모리 문제로 자꾸 에러가 나서 이번에는 100만개로 테스트하고 다음에 도전하려고 한다.

 

2-1. secure file 경로 조회

 

MySQL에는 file 을 다루기 안전하다고 처리하는 경로가 설정되어 있다.

 

SELECT @@GLOBAL.secure_file_priv;

 

위 쿼리로 경로를 확인하고 해당 경로에 csv 파일을 위치시킨다. 그게 불가능한 경우라면 secure-file-priv를 키워드로 해당 설정을 변경하여 파일 위치에 보안 설정을 피한다.

 

2-2 LOAD DATA INFILE

 

아래 명령어로 CSV 파일을 로드 할 수 있다. 

 

LOAD DATA INFILE "${LOCAL_FILE_PATH}" INTO TABLE ${TABLE_NAME} FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

ex) LOAD DATA INFILE "/var/lib/mysql-files/init-data.csv" INTO TABLE product FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

 

쿼리가 간단해서 꼭 comma separated 가 아닌 다른 문자 기반인 파일이나 라인 종료 시그널이 다르더라도 쉽게 커스텀해서 사용할 수 있을 것 같다. 특히 IGNORE 1 ROWS 는 컬럼명을 나타내는 첫 줄을 무시하는 옵션으로 추가했는데 본인 CSV 파일의 상황에 따라서 빼거나 값을 변경한다. 

 

쿼리 테스트 

생성한 더미데이터로 쿼리 테스트를 수행한다. MySQL 8.0, 5개 컬럼을 갖고 있는 테이블에 100만개의 로우를 생성해두었다. 

모든 수행 시간은 쿼리를 10회 반복하여 처리 속도를 평균 내었다.

 

쿼리 형태 비교 

 

서버 애플리케이션 쪽에서 쿼리하는 쿼리 형태를 각각 확인한다.

 

Offset based 

select
    product0_.id as id1_1_,
    product0_.name as name2_1_,
    product0_.price as price3_1_,
    product0_.quantity as quantity4_1_ 
from
    product product0_ 
order by
    product0_.id asc,
    product0_.id asc limit ?

 

Cursor based 

select
    product0_.id as id1_1_,
    product0_.name as name2_1_,
    product0_.price as price3_1_,
    product0_.quantity as quantity4_1_ 
from
    product product0_ 
where
    product0_.id>50 
order by
    product0_.id asc,
    product0_.id asc limit ?

 

 

성능 테스트 1 - Full scan, 인덱스가 ID만 걸려있는 상황

페이지가 작은 경우 

 

위 Offset 기반, 아래 Cursor 기반의 쿼리이다. 실행 계획에서 후자의 경우 필터로 검색 데이터 수가 줄 것을 예상한다.  

 

explain select * from product order by name asc limit 10 offset 0;          // filtered : 100%  
explain select * from product where name > 'a' order by name asc limit 10;  // filtered : 33%

select * from product order by name asc limit 10 offset 0;                   // about : 0.398 sec  
select * from product where name > 'a' order by name asc limit 10;           // about : 0.407 sec

 

실제 수행에선 그렇게 큰 차이를 보이지 않는다. Offset이 낮은 경우에는 Offset 기반의 페이지네이션이 큰 문제가 되지 않음을 확인할 수 있다. 

 

 

페이지가 큰 경우 

 

높은 페이지를 임의로 offset = 900000, cursor = y보다 이름이 큰 경우에서 다음 커서를 조회한다. 이때 더미데이터 안에서 이름이 중복되는 경우는 없다.

 

select * from product order by name asc limit 10 offset 900000;              // about : 2.504 sec 
select * from product where name > 'y' order by name asc limit 10;           // about : 0.154 sec

 

실제 수행 결과에서 큰 차이를 보인다. offset은 뒤쪽으로 갈 수록 수행 시간이 크게 늘어난다. 반면 커서 기반은 페이지가 늘어나도 수행 속도에 문제가 생기지 않으며 오히려 필터링 되는 부분이 많아 수행 시간이 줄어들기까지 한다. 

 

 

성능 테스트 2 - 인덱스가 걸려있는 상황에서도 의미가 있을까?

이번엔 인덱스가 걸려있는 상황에서의 성능 차이를 확인하고 싶었다. 커버링 인덱스를 만들고 위와 같은 테스트를 수행한다. 

 

ALTER TABLE `mymarket`.`product` ADD INDEX `index2` (`name` ASC, `id` ASC, `quantity` ASC, `price` ASC);  // 2.80 sec

 

 

페이지가 작은 경우 

 

offset 방식의 경우에는 full index scan 이, cursor 의 경우에는 index range scan 으로 수행된다.

 

explain select * from product order by name asc limit 10 offset 0;          // filtered : 100% / full index scan   
explain select * from product where name > 'a' order by name asc limit 10;  // filtered : 100% / index range scan

select * from product order by name asc limit 10 offset 0;                   // about : 0.009 sec  
select * from product where name > 'a' order by name asc limit 10;           // about : 0.008 sec

 

실제 수행에선 마찬가지로 차이가 없었다.

 

 

페이지가 큰 경우

 

explain select * from product order by name asc limit 10 offset 900000;     // filtered : 100% / full index scan
explain select * from product where name > 'y' order by name asc limit 10;  // filtered : 100% / index range scan

select * from product order by name asc limit 10 offset 900000;              // about : 0.120 sec 
select * from product where name > 'y' order by name asc limit 10;           // about : 0.008 sec

 

페이지가 큰 경우에는 covering index 상황에서도 cursor based pagination 이 의미가 컸다. 

 

물론 서비스 요구 사항에 따라 선택할 수 있는 방식이 명확하게 갈리겠지만, 그게 아니라 구현 방식을 둘 사이에서 선택 할 수 있는 상황이라면 이후로도 cursor based pagination 적용으로 offset 방식의 퍼포먼스 문제를 피할 것 같다.