Ngrinder 부하테스트 툴을 통해 인덱스 활용 전과후에 성능 확인 with Scouter

테스트용 더미데이터 10만개를 입력했다

1. @Test 문으로 진행시 3500개 정도 추가하는데 7분정도 소모가 되었다

@RepeatedTest(100000)
public void testInsertInquiry(RepetitionInfo repetitionInfo) {

    // 테스트 데이터
    int currentRepetition = repetitionInfo.getCurrentRepetition();

    String title = "더미테스트 제목" + currentRepetition;
    String content = "더미테스트 내용부분";
    String userId = "rltjs987"; // 사용자 ID
    long catNo = 3L; // 카테고리 번호

    int totalRepetitions = repetitionInfo.getTotalRepetitions();

    inquiryService.insertInquiry(title,content,userId,catNo);
    System.out.println("###########   현재 반복: " + currentRepetition + " / 총 반복: " + totalRepetitions +" ############");
}

2. Query 로 입력

insert into INDIVIDUAL_INQUIRIES
    (INQUIRY_CATEGORY_NO,INQUIRY_USER_NO,INDIVIDUAL_INQUIRY_TITLE,INDIVIDUAL_INQUIRY_CONTENT)
values
    (2 , 2 , '더미데이터 제목' ,'더미데이터 내용'),
    (2 , 2 , '더미데이터 제목' ,'더미데이터 내용');

100개 , 500개 , 1000개 , 1만개 순서로 삽입
2만개 이상부터 한번에 삽입하려니 인텔리제이가 멈춰버리는 일이 발생.. 열심히 손으로 하시면됩니다

---

 

실제 서비스들은 계속해서 데이터가 쌓입니다.
계속되는 데이터의 축적으로 인해 조회가 느려질수 있다고 하는데 이것을 개선하기위해 쿼리 튜닝이 필요합니다

그중 하나인 인덱스에 대해서 다뤄보겠습니다.

---

INDIVIDUAL_INQUIRIES 테이블에 인덱스를 설정하기

우선 앞에 말했다 싶이 PK , 와 UNIQUE 에는 인덱스가 설정되어있습니다

 

그렇다면 어떤 기준으로 인덱스를 설정해야 할까 ?

1. 쿼리의 빈도 ,  자주 사용되는 쿼리에 대해 인덱스를 설정을 합니다

2. 컬럼의 카디널리티 수치가 높은 컬럼을 인덱스로 설정하기
 쿼리결과로 가져오는 데이터 중복이 적을수록 좋다고합니다.

3. WHERE , JOIN , ORDER 컬럼에 적용

 

<select id="selectInquiryListPaging" resultType="com.example.bookhub.user.dto.InquiryListDTO">
    select
        i.INDIVIDUAL_INQUIRY_NO             as no,
        i.INQUIRY_CATEGORY_NO               as "faqCategory.no",
        f.FAQ_CATEGORY_NAME                 as "faqCategory.name",
        i.INQUIRY_USER_NO                   as "user.no",
        u.USER_ID                           as "user.id",
        i.INDIVIDUAL_INQUIRY_TITLE          as title,
        i.INDIVIDUAL_INQUIRY_CONTENT        as content,
        i.INDIVIDUAL_INQUIRY_ANSWER_YN      as answerYn,
        i.INDIVIDUAL_INQUIRY_DELETE_YN      as deleteYn,
        i.INDIVIDUAL_INQUIRY_CREATE_DATE    as createdDate ,
        i.INDIVIDUAL_INQUIRY_UPDATE_DATE    as updatedDate,
        u.USER_NAME                         as "user.name"
    from INDIVIDUAL_INQUIRIES i , USER u , FAQ_CATEGORIES f
    where i.INQUIRY_USER_NO = u.USER_NO
      and i.INQUIRY_CATEGORY_NO = f.FAQ_CATEGORY_NO
      and u.USER_ID = #{id}
    order by  i.INDIVIDUAL_INQUIRY_CREATE_DATE DESC
        LIMIT #{offset} ,10
</select>

 

📌 컬럼에 카디널리티 수치를 기준으로 설정 ( 특정 컬럼이나 관계에서 , 고유한 값을 가지는 정도 , 중복도가 낮은 컬럼)
Ex ) 사람이라는 테이블에 이름 ,이메일 , 주민번호는 다른 컬럼에 비해 카디널리티 수치가 높다 
왜 ? 컬럼의 값이 다양하고 중복이 적기 때문이다.

 

select
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_NO) / count(*) * 100 , 2),'%') AS inquiryNO_carinality,
    concat(ROUND(COUNT(DISTINCT INQUIRY_CATEGORY_NO) / count(*) * 100 , 2),'%') AS cateNO_carinality,
    concat(ROUND(COUNT(DISTINCT INQUIRY_USER_NO) / count(*) * 100 , 2),'%') AS  userNO_carinality,
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_CONTENT) / count(*) * 100 , 2),'%') AS inquiryContent_carinality,
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_CREATE_DATE) / count(*) * 100 , 2),'%') AS created_carinality,
    concat(ROUND(COUNT(DISTINCT INDIVIDUAL_INQUIRY_UPDATE_DATE) / count(*) * 100 , 2),'%') AS upated_carinality
from INDIVIDUAL_INQUIRIES;

카디널리티 수치 (cardinality)

상대적인 개념으로 전체 행에 대한 특정 컬럼의 중복 수치를 나타내는 지표
중복도가 '낮으면' 카디널리티가 '높다'
중복도가 '높으면' 카디널리티가 '낮다'

주민등록번호는 중복되는 값이 없으므로 카디널리티가 높다고 할 수 있다
이름은 동명이인 때문에 중복되는 값이 있으므로 '주민등록 번호' 에 비해 카디널리티가 낮다고 할 수 있다

DISTINCT 값이 많다는 것은 '중복도가 낮다 카디널리티가 높다' 라는 의미다

 

📌 추가 설명 

예를들어 성별이라는 카디널리티는 2다 , 왜냐하면 대부분의 경우 성별은 남자 , 여자 2가지 값중 하나를 갖는다
그러나 이름을 담은 컬럼은 카디널리티 수치가 높다 , 왜냐하면 많은 사람들이 서로 다른 이름을 가지고 있기 때문

 

---

 

 

💡 실행 계획을 통한 인덱스 확인

explain
select * from INDIVIDUAL_INQUIRIES
where INDIVIDUAL_INQUIRY_CREATE_DATE BETWEEN '2024-03-01 01:25:28' AND '2024-03-01 22:25:28'
;

• id : 각 쿼리 에 부여된 고유 식별자
• select_type : 쿼리의 유형 "SIMPLE" → 단순한 SELECT 쿼리
• table : 해당 테이블
• type : 조회 타입이 ALL - 해당 조건컬럼이 idx 로 등록되어 있지 않아 ALL로 표시 된다
• partitions : 대용량 테이블을 논리적 , 물리적 단위로 나누는 DB 기술이라고한다 ? - 파티션의 키 값이나 식별자
• passoble_keys : 쿼리에서 사용할 수 있는 인덱스 목록
• key : 실제 선택된 인덱스
• key_len : 인덱스에서 실제 사용되는 바이트수 , 인덱스의 크기
• ref : 쿼리의 각 테이블에 대해 사용된 인덱스의 조건
• rows : 쿼리를 실행하기 위해 읽어야하는 예상 행 수
• filtered : 테이블의 전체 행중에서 몇 퍼센트 행이 결과에 포함되는지

 

🔒 인덱스를 추가해도 인덱스가 작동되지 않을 때

• DB 엔진이 쿼리에 대해 실행계획을 결정할 때
  • 상황에 따라 풀 테이블 스캔이 더 나은 선택이라고 판단해 사용가능한 인덱스 후보에 인덱스가 존재하더라도
    실제로 인덱스를 사용하지 않을 수 있다

- 풀 테이블 스캔(Full Table Scan)
MySQL에서 데이터를 읽을 때 전체 테이블을 순차적으로 스캔하여 검색.
인덱스를 사용하지 않고 모든 행을 확인하여 필요한 데이터를 찾는 방식.

- 테이블에 인덱스가 없는 경우
쿼리에서 사용된 조건이나 순서에 따라 인덱스가 생성되지 않은 경우에는 MySQL이 전체 테이블을 스캔하여 데이터를 찾는다

- 인덱스를 사용할 수 없는 경우
인덱스를 사용하기보다 테이블을 전체로 스캔하는 것이 더 효율적이라고 판단할 때 발생
일부 조건이나 함수 등이 인덱스를 사용할 수 없는 경우에 주로 발생한다

풀 테이블 스캔은 작은 테이블에는 큰 영향을 주지 않지만, 대용량 테이블에서는 성능 문제를 발생시킬수 있다
풀 테이블 스캔은 모든 행을 검사해야 하므로 처리 속도가 느려진다

가능하면 인덱스를 적절히 활용하여 풀 테이블 스캔을 피하는 것이 좋다고 판단한다

 

 

---

 

테이블에 인덱스를만들고 , 해당 테이블의 인덱스를 확인

create index idx_createdDate
    on INDIVIDUAL_INQUIRIES (INDIVIDUAL_INQUIRY_CREATE_DATE);

show index from INDIVIDUAL_INQUIRIES;

해당 테이블에 4개의 인덱스가 설정 되어있는것을 확인할수 있으며 각각 카디널리티 수치를 보면
PK , 카테고리 번호 , 사용자 번호 , 만든 날짜 순서인 것을 확인할 수 있다

explain
select * from INDIVIDUAL_INQUIRIES
where INDIVIDUAL_INQUIRY_CREATE_DATE BETWEEN '2024-03-01 00:00:00' AND '2024-03-01 23:59:59'
and INQUIRY_USER_NO=2
;

USER_NO 보다 , CREATED_DATE가 카디널리티 수치가 더 높기에 생성일자 기준으로 인덱스를 실행되는것을 확인 할 수 있다.

상황에 따라서 다른 인덱스를 타야할 때가 있다면 HINT 기능을 사용해 인덱스를 설정해서 SELECT 한다

explain
select * from INDIVIDUAL_INQUIRIES use index(FK_INQUIRY_USER_NO)
where INDIVIDUAL_INQUIRY_CREATE_DATE BETWEEN '2024-03-01 00:00:00' AND '2024-03-01 23:59:59'
and INQUIRY_USER_NO=2
;

---

1. xml

<select id="findInquiryByDate" resultType="com.example.bookhub.board.vo.Inquiry">
    select
        i.INDIVIDUAL_INQUIRY_NO             as no,
        i.INDIVIDUAL_INQUIRY_TITLE          as title,
        i.INDIVIDUAL_INQUIRY_CONTENT        as content,
        i.INDIVIDUAL_INQUIRY_ANSWER_YN      as answerYn,
        i.INDIVIDUAL_INQUIRY_DELETE_YN      as deleteYn,
        i.INDIVIDUAL_INQUIRY_CREATE_DATE    as createdDate ,
        i.INDIVIDUAL_INQUIRY_UPDATE_DATE    as updatedDate,
        u.USER_ID                           as "user.id"
    from INDIVIDUAL_INQUIRIES i , USER u
    where INDIVIDUAL_INQUIRY_CREATE_DATE BETWEEN #{startDate} AND #{endDate}
      and u.USER_ID = #{id}
    order by i.INDIVIDUAL_INQUIRY_CREATE_DATE desc
</select>

2. Mapper Interface

@Mapper
public interface MyPageMapper {
List<Inquiry> findInquiryByDate(@Param("startDate") LocalDateTime startDate,
                                @Param("endDate") LocalDateTime endDate,
                                @Param("id") String id);
    }

3.Service

@Slf4j
@Service
@RequiredArgsConstructor
public class MyPageService {

    private final MyPageMapper myPageMapper;
    private final UserMapper userMapper;
    private final UserService userService;

    public List<Inquiry> findInquiryByDate(LocalDateTime startDate, LocalDateTime endDate , String id ) {
        return myPageMapper.findInquiryByDate(startDate,endDate , id);
    }
}

4. Controller

@RestController
@RequestMapping("/api/inquiry")
@RequiredArgsConstructor
public class IndexController {

    private final UserService userService;
    private final MyPageService myPageService;

    @GetMapping("/dates")
    public Object findInquiryByDate(@RequestParam("startDate") String startDate ,
                                    @RequestParam("endDate") String endDate,
                                    @RequestParam("id") String id) {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime startDate1 =  LocalDateTime.parse(startDate, formatter);
        LocalDateTime endDate1 = LocalDateTime.parse(endDate, formatter);


        User user = userService.selectUserById(id);

        List<Inquiry> inquiryList = myPageService.findInquiryByDate(startDate1,endDate1 , user.getId());
        return inquiryList;
    }
}

5. nGrinder Script

@RunWith(GrinderRunner)
class TestRunner {

    public static GTest test
    public static HTTPRequest request
    public static Map<String, String> headers = [:]
    public static Map<String, Object> params = [:]
    public static List<Cookie> cookies = []

    @BeforeProcess
    public static void beforeProcess() {
        HTTPRequestControl.setConnectionTimeout(300000)
        test = new GTest(1, "127.0.0.1")
        request = new HTTPRequest()
        grinder.logger.info("before process.")
    }

    @BeforeThread
    public void beforeThread() {
        test.record(this, "test")
        grinder.statistics.delayReports = true
        grinder.logger.info("before thread.")
    }

    @Before
    public void before() {
        request.setHeaders(headers)
        CookieManager.addCookies(cookies)
        grinder.logger.info("before. init headers and cookies")
    }

    @Test
    public void test() {

    // API 호출 URL 파라미터 설정
        String url = "http://127.0.0.1:8080/api/inquiry/dates";
        String startDate = "2024-03-01 00:00:00";
        String endDate = "2024-03-01 23:59:59";
        String id = "rltjs987";

        // 파라미터를 URL 인코딩
        String encodedStartDate = URLEncoder.encode(startDate, "UTF-8");
        String encodedEndDate = URLEncoder.encode(endDate, "UTF-8");

        // 인코딩된 파라미터를 URL에 추가
         String fullUrl = url + "?startDate=" + encodedStartDate + "&endDate=" + encodedEndDate + "&id=" + id;

        // id없이 URL
        // String fullUrl = url + "?startDate=" + encodedStartDate + "&endDate=" + encodedEndDate;

        // GET 요청 실행
        HTTPResponse response = request.GET(fullUrl);


        //HTTPResponse response =  request.GET(url,startDate,endDate);

        // HTTPResponse response = request.GET("http://127.0.0.1:8080/api/inquiry/dates?startDate=2024-03-01%2000:00:00&endDate=2024-03-01%2023:59:59", params)

        if (response.statusCode == 301 || response.statusCode == 302) {
            grinder.logger.warn("Warning. The response may not be correct. The response code was {}.", response.statusCode)
        } else {
            assertThat(response.statusCode, is(200))
        }
    }
}

---

Ngrinder 부하테스트 툴을 통해 인덱스 활용 전과후에 성능 확인

💡 평균 TPS : 8.8 → 62.5 로 개선, 더 많은 트랜잭션을 처리할 수 있는것을 확인

 

1 : 1 문의사항 생성일자가 2024-03-01 00:00:00 ~ 2024-03-01 23:59:59 인 데이터 조회

인덱스 적용하기 전

• Vuser(가상 사용자) : 10
• Duration : 1분

인덱스(idx_notice_createDate) 적용 후

• Vuser(가상 사용자) : 10
• Duration : 1분

수치 변화

• 평균 TPS : { 8.8 } → { 62.5 } (약 610% 개선)    ((62.5 - 8.8) / 8.8) * 100
• Peek TPS : { 10.0 } → { 87.0 }
• Mean Test Time : { 1541.43 } ms → { 161.19}ms
• Exected Tests : { 478 } → { 3520}

---

TPS: 초당 트랜잭션의 수(HTTP request가 성공할 때마다 트랜잭션 수 1씩 증가) , 초당 처리 수

시스템이 처리하는 트랜잭셕의 양을 측정하는 지표이다
TPS 가 개선되었다는 것은 시스템의 성능이 향상되어 더 많은 트랜잭션을 처리할 수 있게 되었음을 나타내므로
더 많은 요청을 처리할 수 있는 확장성을 갖축 시스템을 구축하는데 도움이된다

TPS 가 100 이라면 초당 처리할수 있는 작업이 100이라고 생각하면된다
TPS 수치가 높을 수록 짧은 시간내에 많은 작업을 처리할 수 있다

 

가상 사용자(vuser)는 동시에 접속하는 가상 사용자의 숫자입니다.

vuser = agent x process x thread

VUsers (가상 사용자) 를 10명으로 설정한 이유는 
시스템의 자원이 한정이 되어있기에 , 너무 많은 가상 사용자 예를들면 1000명 , 1만명 , 10만명을 하기에는 너무 무리이기에 시스템의 안전성을 생각했습니다

초기 테스트부터 대량의 사용자로 시작하기보다는 소수의 가상 사용자로 시작했습니다

1000명의 가상 사용자로 테스트 했을시 테스트 도중 에러가 발생했습니다

[ERROR] DESKTOP-KCEG8OA agent is about to die due to lack of free memory.
Shutdown PerfTest 194 by force for safety
Please decrease the vuser count.

- 데스크탑이 사용가능한 메모리가 부족해 곧 사망할 예정입니다
- 안전을 위해 테스트 종료
- 사용자의 수를 줄여주세요 라는 메시지를 볼수 있다

 

2000명의 가상 사용자 테스트를 해보겠습니다

Error의 갯수가 3600개인것을 볼수 있으며 1000명때 와 마찬가지로 메모리 부족으로 종료되었습니다.

 Scouter 모니터링 도구를 사용해CPU 를 확인해보겠습니다

• 1000명 가상 테스트에서  CPU High 71.5% 
• 2000명 가상 테스트에서는 CPU High 100% 이 때문인지 Error와 함께 종료된것 같습니다