[트러블슈팅]Offset을 사용한 조회에서 성능이 떨어지는 이슈

간단 요약

 

[ 이슈가 발생한 부분 ]

529만건의 리뷰 데이터에 대해 날짜 순으로  페이징이 가능한 조회 API를 만들었습니다.

이때 JPA의 @Pageable을 이용해서 페이징 기능을 구현했습니다.

이후 성능 측정을 통해 첫 페이지와 마지막 페이지의 성능 차이가 매우 컸음을 알 수 있었습니다.

 

[ 해결 방법 ]

다음과 같은 부분을 변경하여 해결할 수 있었습니다.

- Offset을 사용하지 않도록 쿼리 최적화

- 클러스터드 인덱스를 사용하도록 조회 조건 변경

 

[ 개선 결과 ]

개선 전후의 성능 차이는 다음과 같습니다. 

첫 페이지 평균 조회 시간 : 5.6배 향상, 1.82초 -> 0.32초

마지막 페이지 평균 조회 시간 : 40배 향상 , 10.8초 -> 0.26초

 

 

---

문제 상황

 

[ API 설명 ]

해당 API는 529만건의 리뷰 데이터에 대해 날짜 순으로 페이징이 가능한 조회 API였습니다.

프론트엔드에서는 이 API를 이용해 무한스크롤을 구현하고자 했습니다.

API의 구현 방법은 Spring JPA의 @Pageable을 이용하여 페이징 처리를 했습니다.

 

 

[ 발생한 문제 ]

첫 페이지에서 뒷 페이지로 갈수록 응답 속도가 느려지는 문제가 발생했습니다.

응답 속도의 정확한 측정을 위해 K6를 사용했습니다.

첫 페이지와 마지막 페이지 조회의 성능 차이가 약 5.9배가 발생한다는 것을 확인할 수 있었습니다.

 

첫 페이지의 평균 조회 속도는 1.82s로 결과는 아래와 같습니다.

data_received..................: 536 kB 29 kB/s
data_sent......................: 1.5 kB 83 B/s
http_req_blocked...............: avg=3.47ms  min=2.59µs  med=4.55µs  max=34.71ms p(90)=3.47ms  p(95)=19.09ms
http_req_connecting............: avg=1.41ms  min=0s      med=0s      max=14.19ms p(90)=1.41ms  p(95)=7.8ms
http_req_duration..............: avg=1.82s   min=1.8s    med=1.82s   max=1.86s   p(90)=1.84s   p(95)=1.85s
{ expected_response:true }...: avg=1.82s   min=1.8s    med=1.82s   max=1.86s   p(90)=1.84s   p(95)=1.85s
http_req_failed................: 0.00%  0 out of 10
http_req_receiving.............: avg=26.58ms min=24.18ms med=26.73ms max=29.95ms p(90)=28.44ms p(95)=29.2ms
http_req_sending...............: avg=32.02µs min=10.52µs med=17.62µs max=88.79µs p(90)=79.19µs p(95)=83.99µs
http_req_tls_handshaking.......: avg=0s      min=0s      med=0s      max=0s      p(90)=0s      p(95)=0s
http_req_waiting...............: avg=1.79s   min=1.77s   med=1.79s   max=1.83s   p(90)=1.82s   p(95)=1.82s
http_reqs......................: 10     0.546841/s
iteration_duration.............: avg=1.82s   min=1.8s    med=1.82s   max=1.87s   p(90)=1.86s   p(95)=1.86s
iterations.....................: 10     0.546841/s
vus............................: 1      min=1       max=1
vus_max........................: 1      min=1       max=1

 

마지막 페이지의 평균 조회 속도는 10.8s로 결과는 아래와 같습니다.

data_received..................: 318 kB 2.9 kB/s
data_sent......................: 1.6 kB 14 B/s
http_req_blocked...............: avg=3.05ms  min=3.58µs  med=4.82µs  max=30.54ms p(90)=3.05ms  p(95)=16.8ms
http_req_connecting............: avg=1.3ms   min=0s      med=0s      max=13ms    p(90)=1.3ms   p(95)=7.15ms
http_req_duration..............: avg=10.8s   min=10.71s  med=10.8s   max=10.87s  p(90)=10.84s  p(95)=10.85s
{ expected_response:true }...: avg=10.8s   min=10.71s  med=10.8s   max=10.87s  p(90)=10.84s  p(95)=10.85s
http_req_failed................: 0.00%  0 out of 10
http_req_receiving.............: avg=13.54ms min=11.55ms med=13.62ms max=14.9ms  p(90)=14.63ms p(95)=14.76ms
http_req_sending...............: avg=36.62µs min=11.53µs med=34.87µs max=88.59µs p(90)=60.65µs p(95)=74.62µs
http_req_tls_handshaking.......: avg=0s      min=0s      med=0s      max=0s      p(90)=0s      p(95)=0s
http_req_waiting...............: avg=10.78s  min=10.69s  med=10.79s  max=10.85s  p(90)=10.83s  p(95)=10.84s
http_reqs......................: 10     0.092552/s
iteration_duration.............: avg=10.8s   min=10.71s  med=10.8s   max=10.87s  p(90)=10.86s  p(95)=10.86s
iterations.....................: 10     0.092552/s
vus............................: 1      min=1       max=1
vus_max........................: 1      min=1       max=1

 

---

문제 발생 원인

 

[ 원인 가정 ]

원인을 찾기 위해 쿼리를 확인했습니다.

Hibernate: 
select * 
from review r1_0 
ORDER BY r.review_date ASC 
limit ?,?

 

위 쿼리를 통해 다음 두가지 가정을 세웠습니다.

1. review_date에 인덱스가 없을 것이다.

2. limit ?, ?에 성능 하락 원인이 있을 것이다.

 

 

[ 1번 가정 분석 ]

Mysql DB에서 인덱스를 조회해보니 review_date는 인덱스로 설정되어있었습니다.

또한 인덱스가 없어도,  첫 페이지와 마지막 페이지의 성능 차이로 이어지지는 않을 것이라 생각했습니다.

따라서 1번 가정은 문제의 원인이 아닐 것이라 생각했습니다.

 

[ 2번 가정 분석 ]

limit ?, ?에서 문제가 발생하는 지 찾아보며, offset을 사용한 조회 쿼리의 문제를 확인할 수 있었습니다.

LIMIT A, B와 같은 쿼리는 총 A+B만큼의 행을 읽게 됩니다.

결국,  A가 증가할수록 ( 페이지가 뒤로 갈수록 ) 읽어야 하는 행의 수가 커지게 되어 성능이 하락하게 됩니다.

따라서 2번 가정이 문제의 원인이라고 생각했습니다.

 

---

해결 방법

 

[ offset을 사용하지 않도록 쿼리 변경 ]

Offset을 사용하지 않고, 조회의 시작 부분을 바로 찾을 수 있도록 WHERE 조건을 추가했습니다.

Hibernate: 
SELECT * 
FROM review r 
WHERE r.review_date > ? 
ORDER BY r.review_date ASC 
LIMIT ?

 

---

추가 개선 사항

 

[ 범위 조회 성능 ]

쿼리는 단일 조회가 아닌 범위 조회를 수행하고 있습니다.

따라서 범위 조회를 더 개선하기 위한 방법을 찾아보았습니다.

 

 

[ 클러스터드 인덱스 사용 ]

범위 조회의 경우 클러스터드 인덱스를 사용하면 추가 성능 향상을 기대할 수 있을 것이라 생각했습니다.

 

넌클러스터드 인덱스의 경우 Random 액세스와 Single Block I/O로 동작됩니다. 이러한 이유로 레코드의 수 만큼 block을 읽어와야하고, 또한 block I/O의 성능이 좋지 못하기 때문에 범위 조회에서 낮은 성능이 우려되었습니다.

 

클러스터드 인덱스의 경우 시퀀셜 액세스와 Multi Block I/O로 동작합니다. 때문에 하나의 block을 여러번 중복하여 읽는 비효율이 없고, 한번의 I/O Call마다 많은 block을 가져올 수 있기에 범위 조회에서 좋은 성능을 기대할 수 있습니다.

 

 

[ 적용이 가능한 지 확인 ]

API는 날짜 기준으로 정렬되어야 했고 따라서 WHERE, ORDER BY에서 review_date를 사용하고 있었습니다.

클러스터드 인덱스인 review_seq로 변경해도 날짜 기준으로 정렬이 되는 지 확인을 해보았습니다.

 

PK인 review_seq는 데이터의 삽입 순서대로 값을 가지고 있었고,

review_date는 리뷰 작성 시간을 가지고 있고, 또한 업데이트가 되지 않음을 확인할 수 있었습니다.

즉, review_date와 review_seq는 동일한 순서를 가지고 있었기 때문에 대체해도 문제가 없다고 판단했습니다.

 

다음과 같이 클러스터드 인덱스를 사용하도록 쿼리를 수정했습니다.

Hibernate: 
SELECT * 
FROM review r 
WHERE r.review_seq > ? 
ORDER BY r.review_seq ASC 
LIMIT ?

---

개선 적용

 

[ 개선 후 성능 측정 ]

첫 페이지의 경우 1.82초 -> 0.321초로 5.6배 성능 향상이 있었고

마지막 페이지의 경우 10.8초 -> 0.268초로 40배의 성능 향상이 있었습니다.

 

 

[ 개선 적용 ]

변경된 API를 적용하기 위해서는 기존에 구현된 프론트엔드의 코드도 변경이 필요했습니다.

최대 40배 정도의 성능 향상이 가능하다는 내용을 토대로 프론트엔드 팀에게 개선 의견을 제시했고, 프론트엔드 팀에서도 긍정적으로 받아주셔서 개선된 API를 적용할 수 있었습니다.