숙박 조회 기능 - 인덱스 적용 및 성능테스트 트러블 슈팅, 테스트 결과

📍개요

팀 프로젝트로 숙박 예약 서비스를 만들면서 전 담당 도메인을 핵심 도메인인 숙박 도메인을 맡게 되었습니다.
숙박엔티티에는 여러 필드 값들을 갖고있는데 그중 대표적인 필드로 카테고리와 지역이 있습니다.
벤더 사이트가 없기 때문에 숙박 데이터들은 공공기관 API 에서 임의의 데이터를 받아, 더미 데이터로 넣어주었습니다.
프로젝트의 기능 구현을 모두 마치고 나서, 기능들을 돌아 보았을 때 숙박 조회시 많은 WHERE 절 조건을 따진다고 생각했고, 이건 성능 문제로 이어질 수도 있겠다는 생각을 해보았습니다.
조회시 성능을 더 높일 수 있는 방법을 찾게 되었고, 데이터베이스 테이블에서 검색 속도를 높이기 위해 사용하는 방법인 인덱스에 대해 학습하게 됐습니다.

테스트 툴은 아파치 제이미터를 사용하였고, 테스트 서버는 테스트 RDS에서 실시하였습니다.

 

🔖 인덱스

인덱스 스캔은 필요한 데이터를 찾기 위해 테이블의 모든 행을 검색하는 대신, 인덱스를 사용하여 빠르게 특정 데이터를 찾아갑니다. 이는 검색 대상이 되는 데이터 양을 상당히 줄여 주어 처리 시간을 단축시킵니다.

Table의 Column을 색인화 하여 따로 파일로 저장하게 됩니다.
해당 Table의 인덱스 조회가 일어날 시 Record를 Table Full scan 하지 않지않고, 색인화 된  Index 파일 검색으로 검색 속도 향상하게 됩니다.
즉, 기본 조회 방법인  Table Full scan을 한다면 기본키로 정렬되어 있는 테이블 데이터에서 WHERE 절의 조건을 하나 거르고 그다음 거르고 또 그다음 걸러서 원하는 데이터를 얻는 방식이라면, Index range scan 은 WHERE 절의 조건이 인덱스화 되어 있으면, 그 인덱스로 색인화 되어 구조화 된 파일에서 빠르게 검색이 가능하다는 점입니다.
저는 RDBMS로 MySQL을 사용했기 때문에 B-Tree 자료구조로 색인화 된 인덱스를 갖게 되고, 다른 인덱스 알고리즘으로는 해시 테이블을 기반으로 한 해시 인덱스 등이 있습니다.

 

장점

• 검색 속도 향상: 데이터 검색 속도를 향상시키기 위해 사용됩니다. 인덱스를 통해 데이터를 빠르게 찾을 수 있어 쿼리 성능이 향상됩니다.
• 정렬된 결과 제공: 인덱스는 데이터를 정렬된 상태로 저장하여 정렬 작업의 성능을 향상시킵니다.

단점

• 공간 비용: 인덱스를 저장하기 위한 추가적인 디스크 공간이 필요합니다.
• 성능 저하: 데이터 삽입, 삭제, 갱신 시 인덱스를 갱신해야 하므로 성능이 저하될 수 있습니다.

이러한 장단점이 있기에 인덱스를 적용하기 좋은 경우가 있고, 사용하면 성능상 좋지 않은 경우가 있을 수 있습니다.

효율적인 인덱스 설계	피해야할 인덱스 설계
WHERE 절에 사용되는 열 (WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음)	DML이 자주 일어나는 Column
SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능하다.	Data 중복도가 높은 Column(열은 익덱스 효과가 없다. 예를 들어 성별 열에 M, F만 있다고 하면 인덱스를 안쓰는 게 낫다.따라서 일반 보조 인덱스보다 unique 보조 인덱스가 빠른 이유가 이것이다.)
JOIN절에 자주 사용되는 열에는 인덱스의 효율이 좋음.	자주 사용되지 않으면 성능 저하를 초래할 수 있음. (INSERT만 주구장창 하는 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아짐. 사용하지 않는 인덱스는 제거하는 것이 바람직함.)
외래키가 사용되는 Column (단, 외래키는 자동으로 외래키 인덱스 만듦. 따라서 제약 조건을 묶지 않았을 시에 사용할 수 있음.)

INDEX 손익분기점
테이블이 가지고 있는 전체 데이터양의 10% ~ 15%이내의 데이터가 출력 될 때만 INDEX를 타는게 효율적이고, 그 이상이 될 때에는 오히려 풀스캔이 더 빠르다고 합니다.

 

 

💻 프로젝트 케이스

제 숙박 엔티티 설계와 인덱스 설계를 접목 시켜 봤을 때 합당하는 판단이 내려졌습니다.
1. 숙박 데이터는 더미 데이터 저장한 값으로(이미 숙박 제공자가 등록을 다 했다는 전제 하에) 추가, 삭제, 변경이 일어나지 않습니다. 예약 데이터만 잦은 수정, 추가, 삭제 연산이 일어납니다.
2. 숙박 데이터 조회시, 매번 WHERE 절에 ENUM 값으로 지역과 카테고리를 통해 검색합니다. 
3. 지역과 카테고리 다중 컬럼 인덱스(복합 인덱스)를 만들기로 결정을 하였고, Cardinality 를 따져 지역을 더 앞서서 배치했습니다. 

 

카디널리티

카디널리티(Cardinality)가 높다는 것은 특정 컬럼의 값들이 고유한 값의 개수가 많다는 것을 의미합니다.
즉, 해당 컬럼에 저장된 값들이 중복이 적고 다양한 값을 가진다는 뜻입니다. 카디널리티는 데이터베이스 성능 최적화에서 중요한 개념으로, 인덱스의 효율성과 밀접한 관련이 있습니다.

 

다중 컬럼 인덱스

다중 컬럼 인덱스는 두개 이상의 필드를 조합해서 생성한 INDEX 입니다. (MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능)
1번째 조건과 이를 만족하는 2번째 조건을 함께 INDEX해서 사용합니다. 
다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야 합니다.
때문에 가급적 UPDATE가 안되는 값을 선정 해야합니다.

---

 

🚀 트러블 슈팅 1

🚨 상황

인덱스를 설정해주었지만, 작동하지 않았습니다.
EXPAIN 문을 써서 쿼리를 실행했을 때 어떻게 작동하는지 확인하였습니다.

 

외래키는 외래키 인덱스를 사용하여 잘 조회하지만 다중 컬럼 인덱스를 설정 해준 값은 사용되지 않았습니다.

EXPLAIN 명령어의 결과에서 type이 ALL로 나타나는 것은 MySQL이 해당 쿼리를 실행할 때 풀 테이블 스캔(Full Table Scan)을 수행한다는 것을 의미합니다.

 

⚠️ 원인

 

질의문에 COALESCE 함수를 사용해서였습니다.

 

초기 숙박 조회 메소드는 아래와 같습니다. 숙박 인원을 필수로 갖고, 지역과 카테고리는 전체(ALL) 조회가 가능하므로, 지역 또는 카테고리를 전체로 조회 할땐 각각 다른 메소드를 호출하여 조회하게끔 했습니다.

다만 이같은 경우는 한 기능(API) 에 너무 많은 레포지토리 메소드가 생기기 때문에 인덱스 전용 전 리팩토링을 진행 하였습니다.

    @Query("SELECT a FROM Accommodation a "
        + "WHERE a.category = :category "
        + "AND a.region = :region "
        + "AND a.maximumCapacity >= :headCount")
    Page<Accommodation> findAccommodationsByCategoryAndRegionAndHeadCount(
        @Param("category") Category category,
        @Param("region") Region region,
        @Param("headCount") Integer headCount,
        Pageable pageable
    );
 
    @Query("SELECT a FROM Accommodation a "
        + "WHERE a.category = :category "
        + "AND a.maximumCapacity >= :headCount")
    Page<Accommodation> findAccommodationsByCategoryAndHeadCount(
        @Param("category") Category category,
        @Param("headCount") Integer headCount,
        Pageable pageable
    );
 
    @Query("SELECT a FROM Accommodation a "
        + "WHERE a.region = :region "
        + "AND a.maximumCapacity >= :headCount")
    Page<Accommodation> findAccommodationsByRegionAndHeadCount(
        @Param("region") Region region,
        @Param("headCount") Integer headCount,
        Pageable pageable
    );
 
    @Query("SELECT a FROM Accommodation a "
        + "WHERE a.maximumCapacity >= :headCount ")
    Page<Accommodation> findAccommodationsByHeadCount(
        @Param("headCount") Integer headCount,
        Pageable pageable
    ); 

 

리팩토링을 진행하여 OR 연산이 되게 하려했지만 COALESCE 함수가 그 역할을 하는 함수인줄 잘못 착각하고, 틀리게 리팩토링을 하여서 인덱스 레인지 스캔이 일어나지 않았던 것입니다.

COALESCE 함수는 SQL에서 여러 인수 중 첫 번째로 NULL이 아닌 값을 반환하는 함수입니다. 만약 A, B라는 컬럼을 인자로 COALSESC 함수로 주게 되면 A 컬럼 값이 NULL 값이 아닌 경우 A 값을 리턴하고 A가 NULL이고 B가 NULL이 아닌 경우 B 값을 리턴합니다. 모든 인수가 NULL이면 NULL을 반환합니다. 

 

+ 추가로 인덱스가 적용 되지 않는 쿼리

✔️인덱스 입힌 컬럼을 가공
WHERE SUBSTR(컬럼명, 1,4) = ‘2019’ 
해결 → WHERE 컬럼명 LIKE ‘2019%’

✔️ 인덱스가 있는 열 이름에는 함수나 연산을 가함
WHERE count*10=100
해결 →  WHERE count=100/10

✔️ 인덱스 컬럼의 묵시적 형변환(같은 타입으로 비교해야함)
WHERE 컬럼명 = ‘20190730’ 
해결 → WHERE 컬럼명 = TO_DATE(‘20190730’, ‘YYYYMMDD’)

✔️ 인덱스 컬럼 부정형 비교.
WHERE 컬럼명 != ‘10’ 
해결 → WHERE 컬럼명 IN(‘20’, ‘30’)

✔️ LIKE %가 앞에 위치.
WHERE 컬럼명 LIKE ‘%2019’
해결 → OR 조건 사용 WHERE 컬럼명 IN(‘102019’,‘202019’,‘302019’)

 

🧑‍💻 액션

OR 연산자를 써서 제대로 된 리팩토링을 진행해주었습니다.

 

🪄 결과

 

인덱스 레인지 스캔이 제대로 작동하는 걸 EXPAIN 문을 통해 확인할 수 있었습니다.

제대로된 리팩토링을 진행할 수 있었습니다. 그리고  잘못 사용한 COALESCE 함수를 제대로 학습할 수 있었고, 추가적으로 인덱스가 적용되지 않는 쿼리 예시도 공부할 수 있었습니다.

 

 

🚀 트러블 슈팅 2

🚨상황

인덱스 설정 전 숙박 조회 성능 테스트를 진행 시 에러가 발생했습니다.

 

⚠️ 원인

Thread Group 설정을 너무 오버해서 설정이 원인이었습니다. 

 

응답 메시지에서도 너무 많은 커넥션이 있다고 나타나는 걸 봤습니다.

커넥션 수를 보기 위해 AWS Cloud Watch로 모니터링을 하였습니다.

해당 부하테스트 시간에 최대 60이 넘는 커넥션수가 있었던 걸 확인할 수 있었습니다.

 

🧑‍💻 액션

너무 높은 스트레스를 주었기에 Thread Group 설정을 낮췄습니다.

	기존	변경
Number of Threads (users)	1000	100
Ramp-Up Period (in seconds)	100	300
Loop Count	1	1

 

🪄 결과

 

 

모든 테스트들이 정상적으로 끝마친걸 확인할 수 있었습니다.

제 서비스가 얼마만큼의 대용량 트래픽을 겪게 되지 제대로 예측해봐야 한다는 점을 배웠고, 아직 테스트 시나리오를 짜는 부분에선 미흡하다고 느꼈습니다. 제 서비스가 어느 정도의 유저를 맞이하게 될지, 그리고 그정도의 트래픽이면 테스트 시나리오를 어떻게 짜서, Thread Group 설정을 할지는 조금 더 학습이 필요하다고 느꼈습니다.

 

🔶 인덱스 적용 전 성능 테스트 VS 인덱스 적용 후 성능 테스트 결과

인덱스 전용 전과 후 Thread Group 설정은 다음과 같이 동일하게 진행되었습니다. 

Number of Threads (users) : 100

Ramp-Up Period (in seconds) : 300

Loop Count : 1

인덱스 적용 전 총합보고서

 

 

인덱스 적용 후 총합 보고서

 

 

결과

 

 

엣지 케이스까지 포함하기에 99% 응답시간 기준은 지표로서 잘 사용되지 않고, 95% 응답시간 기준을 지표로서 가장 많이 사용합니다.

95% 응답시간 기준에서 적용 전 성능 테스트는 평균 481ms 가 걸렸고, 적용 후 성능테스트에서는 평균 242ms 가 걸렸습니다. 이는 98.76%의 개선율을 보인 것이며, 2배 가까이 빠른 응답속도를 보여주었습니다.

JMeter 자체는 서버의 CPU 사용량, 메모리 사용량, 디스크 I/O 등과 같은 시스템 리소스 사용량을 직접 모니터링하는 기능을 제공하지 않기 때문에 부하 테스트를 실행하기 전과 실행하는 동안에 AWS CloudWatch 모니터링 도구를 사용하여 RDS 인스턴스의 메트릭을 지속적으로 모니터링해보았습니다. RDS 데이터베이스 커넥션 수와 CPU 사용률, 가용 메모리 등은 큰 차이는 존재하지 않았습니다.

 

마무리

😍 좋았던 것(Liked)

실제로 성능 테스트를 진행해보았을 때, 95% 응답시간 기준에서 98.76%의 개선율을 보이며, 2배 가까이 빠른 응답속도를 경험했을 때, '이래서 성능테스트를 진행하는 구나' 하고 느꼈습니다. 성능이 향상되었을 때 매우 뿌듯함과 보람을 느꼈고, 만약 실제 운영 서비스였다면, 사용자에게 더 빠른 서비스를 제공할 수 있다는 것으로, 이 일에 있어서 만족감을 크게 느꼈을 것 같습니다.

📚 배운 것(Learned)

인덱스 적용과 성능테스트를 진행하면서 많은 것을 학습할 수 있었습니다. 인덱스가 무엇인지, 인덱스 설정 유의사항, 장단점을 알게 됐고, 조회 기능에 있어서 인덱스 레인지 스캔이 얼마나 성능을 향상시킬 수 있는지 느꼈습니다. 또한 잘못된 리팩토링으로 SQL 함수를 하나 다시 공부하게 됐고, 이 과정에서 더욱 인덱스를 깊게 공부할 수 있었습니다.

💦 부족했던 것(Lacked)

성능테스트를 진행하면서는, 아직 테스트 시나리오나, 테스트 계획을 짜는 데에 미숙하다고 느꼈습니다. 제가 만든 서비스가 어느 정도 트래픽을 맞이할 껀지 유추하는 능력과 유추한 데이터에 따라 어떻게 시나리오를 짤것인지는 보다 많은 학습이 필요할 것 같습니다.

🙏 바라는 것(Longed for)

테스트 시나리오를 경험하기 위해선 역시 실무에서 여러가지 경우의 수를 겪으며 테스트를 해야 배울 수 있다고 생각이 듭니다. 좋은 사수 분을 만나서 어떤 케이스엔 어떻게 시나리오를 짜는 게 좋은지 배워 보고 싶고, 나아가 대규모 트래픽을 맞이하게 되면 지금 만든 서비스의 아키텍처를 어떻게 바꿔야하는 지도 배워보고 싶습니다.