이 글은 당근마켓을 모티브로 한 프로젝트 Secondhand 구현시 이슈 사항을 정리한 글입니다.

API 기본 기능을 모두 구현하고 본격적으로 쿼리 튜닝과 성능 개선을 하기 위해 쿼리를 분석하고 개선해보도록 하겠습니다.

M1 16GB 메모리 환경에서 테스트되었습니다.

200만건의 데이터를 넣어보자

Dummy data를 넣는 다양한 방식이 있습니다. 하지만 저는 item 테이블을 vertical partitioning 한 상태여서 테이블 3개에 동시에 데이터를 넣어야 했기 때문에 프로시저를 통해 랜덤한 데이터를 넣어주고자 했습니다.

더미 데이터를 넣는 가장 빠른 방법은 csv로 데이터를 만든 후 dump insert하는 것이 가장 빠르겠지만, 랜덤하게 변수를 설정하는 부분이 다른 문서 편집기를 사용하는 것보다 MySQL 프로시저를 이용하는 편이 더 편했기 때문에 아래와 같이 프로시저를 만들고 실행시켰습니다. 200만건이 들어가는데에는 15분정도가 소요되었습니다. (이는 실행 환경마다 약간씩 다를 것 같습니다.)

DELIMITER $$

CREATE PROCEDURE InsertDummyData()
BEGIN
    DECLARE i INT DEFAULT 1; -- 1부터 삽입

    WHILE i <= 1000000 DO

        SET @title = CONCAT('중고거래 물품 #', i);
        SET @price = FLOOR(RAND() * 1000000) + 1;
        SET @category = FLOOR(RAND() * 12) + 1;
        SET @region_id = 1168065000; -- 지역값은 고정하였습니다.
    
        -- item_contents 테이블에 삽입
        INSERT INTO item_contents (contents, detail_image_url)
        VALUES ('랜덤한 내용 #', "https://placeimg.com/200/100/any");
    
        SET @item_contents_id = LAST_INSERT_ID();
    
        -- item_counts 테이블에 삽입
        INSERT INTO item_counts (hits, like_counts, chat_counts)
        VALUES (FLOOR(RAND() * 1000), FLOOR(RAND() * 1000), FLOOR(RAND() * 1000));
    
        SET @item_counts_id = LAST_INSERT_ID();
    
        -- item 테이블에 데이터 삽입
        INSERT INTO item (title, price, status, category, thumbnail_url, created_at, updated_at, seller_id, item_counts_id, region_id, item_contents_id, is_deleted)
        VALUES (@title, @price, 'ON_SALE', @category, "[{'url':'https://placeimg.com/200/100/any'}]", NOW(), NULL, 1, @item_counts_id, @region_id, @item_contents_id, 0);
    
        SET i = i + 1;
    
        -- 10000건마다 COMMIT 수행
        IF i % 10000 = 0 THEN COMMIT;
        END IF;

  END WHILE;
  COMMIT;

END $$
DELIMITER ;

-- 정의한 프로시저를 호출합니다.
call InsertDummyData();

성능 개선을 위해 기존 API 분석

가장 많이 호출되는 메서드인 item 목록 조회 쿼리를 Postman을 통해 실행시켜 보았습니다.

HTTP 요청사항

GET /items?regionId=1168065000&page=0 HTTP/1.1
Host: localhost:8080

실행되는 쿼리

// 지역 유효성 검증을 위해 지역 id로 검색합니다.
Hibernate: select region0_.id as id1_7_0_, region0_.city as city2_7_0_, region0_.county as county3_7_0_, region0_.district as district4_7_0_ from region region0_ where region0_.id=?

// item을 조회합니다.
Hibernate: select item0_.id as id1_3_, item0_.created_at as created_2_3_, item0_.updated_at as updated_3_3_, item0_.category as category4_3_, item0_.item_contents_id as item_co10_3_, item0_.item_counts_id as item_co11_3_, item0_.is_deleted as is_delet5_3_, item0_.price as price6_3_, item0_.region_id as region_12_3_, item0_.seller_id as seller_13_3_, item0_.status as status7_3_, item0_.thumbnail_url as thumbnai8_3_, item0_.title as title9_3_ from item item0_ where item0_.region_id=? and item0_.is_deleted=? order by item0_.id desc limit ?, ?

// item의 조회수를 조회합니다. (batch size를 늘려주어 n+1을 1+1로 개선한 상태)
Hibernate: select itemcounts0_.id as id1_5_0_, itemcounts0_.chat_counts as chat_cou2_5_0_, itemcounts0_.hits as hits3_5_0_, itemcounts0_.is_deleted as is_delet4_5_0_, itemcounts0_.like_counts as like_cou5_5_0_ from item_counts itemcounts0_ where itemcounts0_.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

// 로그인한 멤버 조회합니다.
Hibernate: select member0_.id as id1_6_0_, member0_.member_id as member_i2_6_0_, member0_.oauth as oauth3_6_0_, member0_.profile_img_url as profile_4_6_0_ from member member0_ where member0_.id=?

// item의 조회수를 분할하여 조회합니다.
Hibernate: select itemcounts0_.id as id1_5_0_, itemcounts0_.chat_counts as chat_cou2_5_0_, itemcounts0_.hits as hits3_5_0_, itemcounts0_.is_deleted as is_delet4_5_0_, itemcounts0_.like_counts as like_cou5_5_0_ from item_counts itemcounts0_ where itemcounts0_.id=?

// 지역을 다시 조회합니다. (item 에 대한 1+1 쿼리로 나가는 듯)
Hibernate: select region0_.id as id1_7_0_, region0_.city as city2_7_0_, region0_.county as county3_7_0_, region0_.district as district4_7_0_ from region region0_ where region0_.id=?

실행 시간 확인

200만건의 데이터를 넣고서 가장 놀라웠던 부분이 실행 시간이 엄청나게 늘었다는 점입니다. offset 방식을 사용한 페이지네이션의 한계를 보여줍니다.

실제 작성한 java 코드입니다.

@Override
public Slice<Item> findAllByBasedRegion(Long categoryId, Long sellerId, List<Status> sales, Long regionId, Pageable pageable) {
    int pageSize = pageable.getPageSize()+1;

    List<Item> fetch = jpaQueryFactory.selectFrom(item)
            .where(
                    eqRegion(regionId),
                    eqCategory(categoryId),
                    inSales(sales),
                    eqSeller(sellerId),
                    item.isDeleted.eq(false)
            )
            .offset(pageable.getOffset())
            .limit(pageSize)
            .orderBy(item.id.desc())
            .fetch();
    return new SliceImpl<>(getContents(fetch, pageSize-1), pageable, hasNext(fetch, pageSize-1));
}

첫 페이지의 경우 별 문제가 없었습니다.

http://localhost:8080/items?region=1168065000&page=0

거의 끝 페이지를 요청을 했을 때 실행 시간이 20배로 늘어납니다.

http://localhost:8080/items?region=1168065000&page=42012

💪 성능 개선을 하자

쿼리 정리

중복으로 나가는 쿼리를 삭제하기 위해 서비스 로직을 확인하였습니다. 지역 쿼리가 추가로 나가는 부분을 개선하였습니다.

item count 쿼리가 추가로 발생하는 부분은 hibernate가 쿼리를 캐싱한 결과입니다. [참고한 링크] 쿼리에 대한 캐싱 설정을 수정하면 다른 API 효율에 영향을 미칠 수 있을 것 같아 권장한 설정값대로 두었습니다.

인덱스 추가

-- 기존 인덱스
fk_item_item_contents1_idx
fk_item_item_count_idx
fk_item_member_idx
fk_item_region1_idx

create index fk_item_category_index
    on item (category);

검색 조건 중 category 에 대한 index가 없어서 추가해주었습니다. 그러나 쿼리에서는 무조건 지역 정보가 기본으로 하고 검색 조건이 필터링에 따라 추가되는 형태라 이 인덱스를 절대 타지 않았습니다. 그래서 필터 조건이 되는 region 조건을 추가하여 인덱스를 탈 수 있는 조건이 되도록 했습니다.

아래는 가장 기본적인 검색 쿼리로 했을 때의 실행계획입니다.

+--+-----------+------+----------+----+-------------------+-------------------+-------+-----+----+--------+--------------------------------+
|id|select_type|table |partitions|type|possible_keys      |key                |key_len|ref  |rows|filtered|Extra                           |
+--+-----------+------+----------+----+-------------------+-------------------+-------+-----+----+--------+--------------------------------+
|1 |SIMPLE     |item0_|NULL      |ref |fk_item_region1_idx|fk_item_region1_idx|8      |const|1   |50      |Using where; Backward index scan|
+--+-----------+------+----------+----+-------------------+-------------------+-------+-----+----+--------+--------------------------------+

-- 기본 검색일 때
create index fk_item_region_is_deleted on item (region_id, is_deleted);

+--+-----------+------+----------+----+-------------------------+-------------------------+-------+-----------+----+--------+-------------------+
|id|select_type|table |partitions|type|possible_keys            |key                      |key_len|ref        |rows|filtered|Extra              |
+--+-----------+------+----------+----+-------------------------+-------------------------+-------+-----------+----+--------+-------------------+
|1 |SIMPLE     |item0_|NULL      |ref |fk_item_region_is_deleted|fk_item_region_is_deleted|10     |const,const|1   |100     |Backward index scan|
+--+-----------+------+----------+----+-------------------------+-------------------------+-------+-----------+----+--------+-------------------+

No-offset 방식으로 개선

Offset을 사용한 페이지 방식의 문제점

이전의 쿼리가 offset 방식으로 구현된 것을 Cursor 방식으로 바꾸기로 하였습니다.

오프셋 방식은 페이지 번호만 알면 되므로 간편하고 처리가 매우 쉽습니다. 임의의 페이지에 접근하려고 할 때는 해당 페이지의 offset인 페이지 번호만 알면됩니다.

-- 대표적인 offset 방식 페이지네이션의 쿼리 예시

SELECT *
FROM sales
ORDER BY sale_date DESC
LIMIT 10 OFFSET 10

하지만 이 방식을 뜯어보면 치명적인 단점을 발견할 수가 있습니다.

뒷 페이지로 갈 수록 성능 저하

오프셋 방식은 첫번째 레코드부터 마지막 레코드까지 번호를 매기고 원하는 페이지의 내용에서 필요없는 방식을 삭제하는 방법이기 때문에 첫 페이지는 별 문제가 없지만 점점 뒤의 페이지 일수록 빠른 성능 저하가 일어납니다. 스캔할 인덱스 범위가 더 커지기 때문입니다.

위의 예시 쿼리로 보았을 때 페이지 4의 레코드를 불러오기 위해서는 SALE_DATE 로 정렬된 레코드를 순서대로 읽어 4페이지 분량까지 스캔한 후, 3페이지의 분량을 삭제하는 방식인 것입니다.

데이터 추가시 중복 데이터 노출

또 다른 문제점은 만약 페이지 요청과 다음 페이지 요청 사이에 누군가 글을 삭제하거나 작성하면 페이지 내용이 밀려 중복된 데이터가 노출될 수 있다는 것입니다. 스크롤을 이용한 UI를 사용하는 환경에서는 조회 데이터가 누적되어 보이므로 치명적인 단점입니다. 새로고침 하거나 다른 페이지를 조회했다 다시 오지 않는 이상 데이터가 정정될 수도 없습니다. (같은 데이터가 여러번 보이면 아무리 봐도.. 200 OK가 무색하게 사용자에게는 렉이나 오류처럼 보일것 같지요... 👀)

Cursor 방식

그래서 대안이 offset을 사용하지 않는 방식입니다. 이전 페이지의 마지막 값을 사용하여 하한을 지정하는 방식입니다.

SELECT *
  FROM sales
 WHERE sale_date < ?
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

이 방법에서는 정렬 순서와 인덱싱이 매우 중요합니다. 인덱싱을 잘 활용하여 데이터 스캔하는 범위를 줄일수록 성능 개선에 효과적이기 때문입니다.

CREATE INDEX sl_dtid ON sales (sale_date, sale_id) -- 정렬조건에 맞는 인덱스를 생성해줍니다.

SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC
 FETCH FIRST 10 ROWS ONLY

초반에는 큰 차이가 나지 않지만 뒷 페이지 접근을 시도하면 차이점을 확연히 느낄 수가 있었습니다.

[참고한 링크 : Paging Through Results]

아래는 cursor 방식으로 개선한 Java 코드입니다.

public Slice<Item> findAllByIdAndRegion(Long last, Long categoryId, Long sellerId, List<Status> sales, Long regionId, Pageable pageable) {
    int pageSize = pageable.getPageSize()+1;

    List<Item> fetch = jpaQueryFactory.selectFrom(item)
            .where(
                    eqLast(last),
                    eqRegion(regionId),
                    eqCategory(categoryId),
                    inSales(sales),
                    eqSeller(sellerId),
                    item.isDeleted.eq(false)
            )
            .limit(pageSize)
            .orderBy(item.id.desc())
            .fetch();
    return new SliceImpl<>(getContents(fetch, pageSize-1), pageable, hasNext(fetch, pageSize-1));
}

실행 쿼리

// region 검증을 위한 조회
Hibernate: select region0_.id as id1_7_0_, region0_.city as city2_7_0_, region0_.county as county3_7_0_, region0_.district as district4_7_0_ from region region0_ where region0_.id=?
// item 조회
Hibernate: select item0_.id as id1_3_, item0_.created_at as created_2_3_, item0_.updated_at as updated_3_3_, item0_.category as category4_3_, item0_.item_contents_id as item_co10_3_, item0_.item_counts_id as item_co11_3_, item0_.is_deleted as is_delet5_3_, item0_.price as price6_3_, item0_.region_id as region_12_3_, item0_.seller_id as seller_13_3_, item0_.status as status7_3_, item0_.thumbnail_url as thumbnai8_3_, item0_.title as title9_3_ from item item0_ where item0_.id<? and item0_.region_id=? and item0_.is_deleted=? order by item0_.id desc limit ?
// item count 조회
Hibernate: select itemcounts0_.id as id1_5_0_, itemcounts0_.chat_counts as chat_cou2_5_0_, itemcounts0_.hits as hits3_5_0_, itemcounts0_.is_deleted as is_delet4_5_0_, itemcounts0_.like_counts as like_cou5_5_0_ from item_counts itemcounts0_ where itemcounts0_.id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
// login member 조회
Hibernate: select member0_.id as id1_6_0_, member0_.member_id as member_i2_6_0_, member0_.oauth as oauth3_6_0_, member0_.profile_img_url as profile_4_6_0_ from member member0_ where member0_.id=?
Hibernate: select itemcounts0_.id as id1_5_0_, itemcounts0_.chat_counts as chat_cou2_5_0_, itemcounts0_.hits as hits3_5_0_, itemcounts0_.is_deleted as is_delet4_5_0_, itemcounts0_.like_counts as like_cou5_5_0_ from item_counts itemcounts0_ where itemcounts0_.id=?

개선 결과

첫 페이지에 대한 요청
- 개선 이전과 비슷한 결과가 나왔습니다.

http://localhost:8080/v2/items?regionId=1168065000

거의 끝 페이지 (version 1 테스트시 같은 데이터 내용) 요청
- 실행 시간 138 ms로 약 1000% 성능 개선이 되었음을 알 수 있습니다.

http://localhost:8080/v2/items?regionId=1168065000&last=1050

개선의 한계점

API 요청 파라미터를 바꾸지 않고 Offset을 바꿀 수 있는지?

이번 API 개선을 하면서 불가피하게 version 2의 API를 만들어야 했습니다. API 요청하는 서비스가 계속 지속되고 있는 상태에서 요청 파라미터의 개편이 있었기 때문에 기존 API를 유지한 채 진행한 것입니다. [API 버저닝 참고링크]

이를 유지하며 개선하다보니 요청 파라미터를 유치한채 cursor 방식으로 개편하는 방법에 대해 고민해보았는데요. 카운트 쿼리를 추가로 날리게 되어 결국 index 탐색 범위도 크고, is_delete 와 같이 중간에 보이지 않도록 숨겨지는 게시물이 있어 정확하게 인덱싱을 어느 범위부터 해야할지 찾기 어려웠습니다. 게시물의 중복 표시 문제가 해결되지 않는 것 같아 v2의 API를 구현하였습니다.

한번에 1000페이지를 요청하고 싶을 때는 어떻게 하지?

이번에는 무한 스크롤 형태의 페이징 형식이었지만 번호로 라벨링 되는 페이지 형식의 UI일 때는 offset을 사용하는 것이 가장 편리하고 간편한 방식입니다.

대다수의 대형 서비스에서는 애초에 조회 가능한 데이터 수를 한정해 두는 것 같습니다. [참고한 링크: MySQL LIMIT 최적화(feat. 구글이 검색결과를 최대 1,000건만 제공하는 이유)]

Refs.

https://use-the-index-luke.com/sql/partial-results/fetch-next-page?ref=localhost

OFFSET is bad for skipping previous rows

OFFSET doesn’t deliver stable results and makes the query slow. Key-set pagination does neither.

use-the-index-luke.com

https://jojoldu.tistory.com/530?category=637935&ref=localhost

3-1. 페이징 성능 개선하기 - 검색 버튼 사용시 페이지 건수 고정하기

모든 코드는 Github에 있습니다. 앞서 포스팅에서 실질 페이징 쿼리 성능을 올리는 방법들을 소개 드렸는데요. 1. 페이징 성능 개선하기 - No Offset 사용하기 2. 페이징 성능 개선하기 - 커버링 인덱

jojoldu.tistory.com

저작자표시 비영리 동일조건

✨ iirin's space

API 성능개선 : 페이징 cursor 방식으로 개선하기

200만건의 데이터를 넣어보자

성능 개선을 위해 기존 API 분석

실행 시간 확인

💪 성능 개선을 하자

쿼리 정리

인덱스 추가

No-offset 방식으로 개선

Offset을 사용한 페이지 방식의 문제점

뒷 페이지로 갈 수록 성능 저하

데이터 추가시 중복 데이터 노출

Cursor 방식

실행 쿼리

개선 결과

개선의 한계점

API 요청 파라미터를 바꾸지 않고 Offset을 바꿀 수 있는지?

한번에 1000페이지를 요청하고 싶을 때는 어떻게 하지?

Refs.

티스토리툴바

API 성능개선 : 페이징 cursor 방식으로 개선하기

200만건의 데이터를 넣어보자

성능 개선을 위해 기존 API 분석

실행 시간 확인

💪 성능 개선을 하자

쿼리 정리

인덱스 추가

No-offset 방식으로 개선

Offset을 사용한 페이지 방식의 문제점

뒷 페이지로 갈 수록 성능 저하

데이터 추가시 중복 데이터 노출

Cursor 방식

실행 쿼리

개선 결과

개선의 한계점

API 요청 파라미터를 바꾸지 않고 Offset을 바꿀 수 있는지?

한번에 1000페이지를 요청하고 싶을 때는 어떻게 하지?

Refs.

관련글

티스토리툴바