« 2024/11 »
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

🔗

대규모 데이터를 처리하는 게시판 시리즈 (1) - 웹애플리케이션 개발 및 더미데이터 생성 (2) - JMeter와 테스트플랜 생성, 최초 성능 테스트 (3) - Index(1) (4) - Index(2) (4) - DB Connection (5) - InnoDB Buffer Pool (6) - Replication, Partitioning

인덱싱

이전 글에서 애플리케이션에서 발생하는 쿼리들을 분석한 결과, 크게 2가지의 문제점이 있었습니다.

Backward Index Full Scan으로 인한 문제

Title 필터링으로 발생한 문제

이 중 2번을 먼저 해결하고자 합니다.

Title Index 설정

Title 필드에 인덱스를 생성하는 것은 다음 쿼리를 통해 수행할 수 있습니다. Article의 title에 title_index라는 이름의 인덱스를 생성하는 쿼리입니다.

ALTER TABLE article ADD INDEX title_index (title)

이후 실행계획이 어떻게 바뀌는지 확인해보았습니다.

Article Search Page

실행계획이 바뀌지 않았습니다.

조건절에 like ‘%…%’와 같이 와일드카드가 패턴의 시작 부분에 온다면 인덱스는 사용되지 않는다는 점을 검색을 통해 알게 됐습니다. 다시 생각해본다면 title을 기준으로 정렬된 데이터들 사이에서 특정 데이터 범위를 찾기 위해서는 와일드카드가 없어야 한다는게 당연해 보입니다. 따라서 이 경우는 title에 인덱스를 설정했음에도 불구하고 모든 범위를 스캔하게 됩니다.

패턴 앞에 있는 와일드카드를 삭제한다면 다음과 같이 인덱스를 사용하게 됩니다. 하지만 이러한 검색 기능은 잘 사용되지 않는 것 같습니다.

Article Search 결과를 페이징하기 위한 count 쿼리

기존에는 모든 레코드를 스캔하면서 조건절에 부합하는 값들만 세는 방식으로 동작했습니다. 하지만 인덱스 생성 뒤에는 title 인덱스를 스캔하여 title 인덱스 값이 조건절에 부합하는지 검사하여 세는 방식으로 변경됐습니다.

이때 역시 range scan은 할 수 없어서 큰 차이는 없겠지만, 기존에는 레코드에 접근하여 스캔했던것과 다르게 이번에는 index만을 접근하여 스캔한다는 점에서 오버헤드가 줄어들 것으로 예상됩니다.

❓

커버링 인덱스 커버링 인덱스란 쿼리의 결과물을 반환하기 위해서 필요한 모든 데이터를 갖고 있는 인덱스를 의미합니다. 기존의 count query는 스캔 과정에서 title에 인덱스가 없으므로 모든 레코드 데이터들을 순회하며 title을 비교하는 과정을 거쳐야 했습니다. 반면 새로운 count query는 레코드 데이터에 접근할 필요 없이 title_index에 접근하는 것 만으로 쿼리의 결과를 반환할 수 있는데, 이 경우 실제 데이터까지 접근할 필요가 없으므로 디스크 I/O를 줄이고 조회 성능을 향상 시킬 수 있습니다. 커버링 인덱스가 적용된다면 실행계획의 Extra 컬럼에 Using index 를 확인할 수 있습니다.

결과

title_index 설정 이후 count 쿼리는 0.2초 가량 성능이 개선된 것을 확인할 수 있었습니다. 이 덕분인지 전체 평균 응답 시간은 약 0.3초 정도 빨라졌습니다.

Full-Text Search

단순히 문자열을 인덱스로 설정했을 때 range를 좁힐 수 없는 문제를 해결하기 위해서 MySQL은 전문검색Full-Text Search을 지원합니다.

Full-Text Index 생성

title_index 가 별다른 효과를 얻지 못했으므로, 이 인덱스는 삭제하기로 합니다.

alter table article drop index title_index;

이후 title에 대해 Full-Text 인덱스를 생성해줍니다. 우려했던것보다 금방 수행되었습니다.

alter table article add FULLTEXT(title);

Full-Text 인덱스를 사용하기 위해서는 Full-Text에서 지원하는 방식대로 쿼리를 작성해야합니다.

select * from article 
where 
	match (title) against (CONCAT('*title12345*') in boolean mode)  
order by id desc 
limit 0,10;

Repository 수정

Full-Text는 MySQL에서 고유한 기능이므로 Native Query 방식으로 레포지토리 코드를 작성해야 합니다.

@Query("select * from article where match (title) against  (CONCAT('*',:title, '*') in boolean mode) order by id desc limit :#{#pageable.pageSize} offset :#{#pageable.offset}",
    nativeQuery = true,
    countQuery = "select count(article.id) from article where match (title) against (CONCAT('*',:title, '*') in boolean mode)")
fun fullTextSearch(
    @Param("title")
    title: String,
    pageable: Pageable
): Page<Article>

이렇게 작성하니 쿼리가 정상적으로 동작은 하지만 내부적으로 exception이 터졌습니다.

java.lang.IllegalArgumentException: Could not locate named parameter [__$synthetic$__2], expecting one of [title]

하이버네이트가 파라미터를 관리하면서 생기는 문제 같은데, 아마 여러 바인딩 방식이 섞여서 오류가 나는 것 같아 보입니다. 당장은 무시해도 좋을 것 같습니다.

결과

Full-Text Search를 적용한 이후 결과는 다음과 같습니다.

기존에 평균 응답시간이 22초였던 것에 비해 full-text search를 적용한 이후에는 평균 응답시간이 3.3초로 약 85퍼센트의 성능 향상이 이루어졌습니다. 이로 인해 병목현상이 제거되고 DB Connection을 못 얻어서 생겼던 에러가 전부 없어졌습니다.

title 검색을 통해 article 정보를 조회하는 쿼리는 평균 시간이 2.9초로부터 0.01초로 개선되었습니다.

title 검색 페이징을 위한 count 쿼리는 평균 시간이 2.3초로부터 0.0008초로 줄었습니다.

Backward Index Scan 문제 해결

select a1_0.id,a1_0.content,a1_0.created_at,a1_0.password,a1_0.title,a1_0.updated_at,a1_0.writer 
from article a1_0 
order by a1_0.id 
desc limit ?,?

Backward Index Scan으로 수행되는 쿼리는 평균적으로 2초가 걸렸고 그 중 소수의 쿼리들은 5초에 가까운 시간이 걸렸다는 것을 저번 글에서 확인했습니다.

이러한 문제는 전체 throughput에 영향을 주면서도 과거 게시물을 조회하는 개별 사용자 입장에서도 좋지 않은 경험을 제공하게 됩니다.

근본적인 원인은 OFFSET

이 쿼리가 느린 이유는 OFFSET에서 발생합니다. OFFSET이 크다면 row들을 OFFSET만큼 지나친 후 LIMIT의 개수만큼의 데이터를 가져오는데, 이 과정에서 인덱스를 훑는 시간은 OFFSET에 비례하게 됩니다.

이를 해결하기 위해 제시되는 방법 중 하나는 커서 기반 페이징입니다.

커서 기반 페이징

커서 기반 페이징에서는 다음과 같은 쿼리를 통해 데이터를 가져옵니다.

SELECT * FROM article 
WHERE id > :offsetId 
LIMIT 10

위 쿼리의 실행계획을 보면 type이 range로 되어있는 것을 확인할 수 있습니다. 위 쿼리는 primary index를 통해 검색 대상의 범위를 한정한 뒤 그곳에서부터 10개의 데이터를 가져옵니다. 따라서 id가 작을 수록 범위가 커지므로 검색해야할 데이터가 늘어날 것 같지만, 이는 limit에 의해서 일부만 반환되므로 걸리는 시간이 크게 다르지 않습니다.

이는 Offset 방식과 다르게 데이터를 모두 훑지 않아도 되므로 성능 개선에 큰 도움을 줄 것입니다. 하지만 지금 게시판의 스펙은 커서 기반 페이징 시스템으로는 요구사항을 다 만족시킬 수 없습니다. 지금의 게시판은 특정 숫자의 페이지에 바로 접근하는 기능을 제공해야하기 때문입니다.

반면 게시판이 ‘더보기’ 등을 사용하는 형태의 게시판이라면 커서 기반 페이징을 사용하는 것이 오프셋 기반 페이징보다 성능에 큰 도움이 될 것입니다.

애플리케이션 레벨에서 수행되는 쿼리를 바꿔보자

OFFSET을 사용한다는 특성 자체로써 쿼리는 느리게 수행될 수 밖에 없지 않나 싶습니다. 하지만 이를 조금 해결하기 위해서 다른 방법을 고안해보았습니다. 지금 과거 게시물 페이지의 경우 Index를 거의 다 스캔해야 한다는 점이 문제이니, 다음과 같이 스캔의 개수를 줄일 수 있을 것 같습니다.

Count와 PageNumber, PageSize를 통해 페이지가 전체 데이터 셋에서 왼쪽에서 가까울지 오른쪽에서 가까울 지 판단할 수 있습니다. 이를 통해 왼쪽에서 가까운 경우 정방향에서 스캔을 하도록하고, 오른쪽에서 가까운 경우 역방향에서 스캔을 한다면 시간을 단축할 수 있을 것으로 보입니다.

구현

fun findArticles(pageable: Pageable): Page<Article> {

        val pageNumber = pageable.pageNumber
        val pageSize = pageable.pageSize

        // 1. 카운트 쿼리
        val countQuery = "select count(a.id) from Article a"

        val count = (em.createQuery(countQuery).singleResult as Number).toInt()
        val totalPage = count / pageSize + if (count % pageSize == 0) 0 else 1

        println("count : $count")
        println("totalPage : $totalPage")


        // 2. 데이터 쿼리
        if (totalPage / 2 > pageNumber) {
            val offset  = pageNumber * pageSize
            val limit = pageSize
            val query = "select * from article a order by a.id desc limit $offset , $limit"
            val articles = em.createNativeQuery(query, Article::class.java).resultList as List<Article>
            return PageImpl(articles, pageable, count.toLong())
        } else {

            //last page
            if (totalPage == pageNumber + 1) {
                println("lastPage")
                val offset = 0
                val limit = if (count % pageSize == 0) { pageSize } else { count % pageSize }
                val query = "select * from article a order by a.id asc limit $offset , $limit"
                val articles = em.createNativeQuery(query, Article::class.java).resultList as List<Article>
                val sortedArticle = articles.sortedByDescending { it.id }
                return PageImpl(sortedArticle, pageable, count.toLong())

            } else {

                val modular = if (count % pageSize == 0) { pageSize } else { count % pageSize }
                val offset = (totalPage - pageNumber - 2) * pageSize + modular
                val limit = pageSize
                val query = "select * from article a order by a.id asc limit $offset , $limit"
                val articles = em.createNativeQuery(query, Article::class.java).resultList as List<Article>
                val sortedArticle = articles.sortedByDescending { it.id }
                return PageImpl(sortedArticle, pageable, count.toLong())
            }
        }
    }

코드는 좀 지저분합니다.. 스캔 방향이 바뀔 때 offset과 limit을 전환해주는 과정이 쉽지 않았습니다.

페이징과 스캔 방향 판단에서 사용될 count를 먼저 가져왔고, 이를 통해 totalPage를 도출해 낸 뒤 totalPage와 pageNumber, pageSize를 기준으로 스캔방향을 결정하여 서로 다른 쿼리를 보낼 수 있도록 했습니다.

이제는 25000번 페이지를 기준으로 그 앞은 desc로, 그 뒤는 asc로 데이터를 가져오면서 전체 데이터의 절반 이상 스캔되는 경우를 제거했습니다.

페이징의 마지막 부분도 정상적으로 작동합니다.

딱 맞아 떨어지는 경우

딱 맞아떨어지지 않는 경우

결과

평균 응답시간이 약 0.5 초 정도 단축되었습니다.

쿼리가 조사하는 열을 절반으로 제한했기 때문에 슬로우 쿼리의 수행시간도 약 절반이 되었습니다.

해당 쿼리의 평균 시간도 0.03초 정도 줄어들었음을 확인할 수 있었습니다.

이 방식은 스캔 방향만 바꾸는 것이기 때문에 효과적으로 수행시간을 단축할 수 없습니다. 이 문제를 제대로 해결하려면 다른 방법을 도입해야할 것입니다.

Uploaded by N2T

Web IDE Backend Architecture (0)	2023.12.24
[Toy Project] 대규모 데이터를 처리하는 게시판(5) - DB Connection (0)	2023.11.07
[Toy Project] 대규모 데이터를 처리하는 게시판(3) - Index(1) (1)	2023.11.01
[Toy Project] 대규모 데이터를 처리하는 게시판(2) - JMeter와 테스트 케이스 생성 (0)	2023.11.01
[Toy Project] 대규모 데이터를 처리하는 게시판(1) - 웹애플리케이션 개발 및 더미데이터 생성 (2)	2023.10.31

세상에 나쁜 코드는 없다