데이터 20만건 처리에 대해서 - part1

[Irisation23]

Outline

Spring AOP를 사용하여 위의 이미지와 유사한 구조로 DB 쿼리 성능에 대한 동작 시간 단위 별 테스트를 진행하고자 한다.

• 주 목적은 아래과 같음

1. 시간이 오래 걸릴 가능성이 있는 SELECT문의 쿼리 성능을 테스트 후 개선
2. ++

Main

먼저 Spring AOP를 사용하기 위해서는 의존성 주입이 필요하다.

<dependencies>
	<!-- AOP 사용을 위한 의존성 -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-aop</artifactId>
		<version>5.3.28</version>
	</dependency>
	<!-- AspectJ 라이브러리 의존성 -->
	<dependency>
		<groupId>org.aspectj</groupId>
		<artifactId>aspectjrt</artifactId>
		<version>1.9.7</version>
	</dependency>
	<!-- AspectJ 라이브러리 의존성, 실행 시 클래스 로딩 역할 -->
	<dependency>
		<groupId>org.aspectj</groupId>
		<artifactId>aspectjweaver</artifactId>
		<version>1.9.7</version>
	</dependency>
</dependencies>

• aop 디렉토리 구조

• TimeTraceAspect

@Slf4j
@Aspect
@Component
public class TimeTraceAspect {
    @Pointcut("@annotation(com.douzon.smartlogistics.global.common.aop.annotation.TimeTrace)")
    private void timer(){};

    @Around("timer()")
    public Object logTimeData(ProceedingJoinPoint joinPoint) throws Throwable {

        long startTime = System.currentTimeMillis();

        Object returnValue = joinPoint.proceed();

        MethodSignature signature = (MethodSignature) joinPoint.getSignature();
        String methodName = signature.getMethod().getName();

        long totalTimeMillis = System.currentTimeMillis() - startTime;

        log.info("실행 메서드: {}, 실행시간 = {}ms", methodName, totalTimeMillis);

        return returnValue;
    }
}

• TimeTrace

@Target({ElementType.METHOD, ElementType.ANNOTATION_TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface TimeTrace {
}

• 테스트할 위치 지정

@Mapper
public interface ReceiveMapper {
    @TimeTrace
    List<ReceiveList> findReceive(
            @Param("receiveCode") String receiveCode,
            @Param("manager") String manager,
            @Param("itemCode") Integer itemCode,
            @Param("itemName") String itemName,
            @Param("accountNo") Integer accountNo,
            @Param("accountName") String accountName,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate
    );
    @TimeTrace
    List<CmpPOrder> waitingReceive(
            @Param("porderCode") String porderCode,
            @Param("itemCode") Integer itemCode,
            @Param("itemName") String itemName,
            @Param("manager") String manager,
            @Param("accountNo") Integer accountNo,
            @Param("accountName") String accountName,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate
    );
}

• 테스트 되는 쿼리

SELECT RECEIVE_ITEM.RECEIVE_ITEM_NO, RECEIVE_ITEM.RECEIVE_CODE, RECEIVE_ITEM.MANAGER,
        RECEIVE_ITEM.ITEM_CODE, ITEM.ITEM_NAME, RECEIVE_ITEM.RECEIVE_COUNT, ITEM.UNIT,
        RECEIVE_ITEM.ACCOUNT_NO, ACCOUNT.ACCOUNT_NAME, RECEIVE_ITEM.CREATE_DATE
FROM RECEIVE_ITEM
LEFT JOIN RECEIVE ON RECEIVE.RECEIVE_CODE = RECEIVE_ITEM.RECEIVE_CODE
LEFT JOIN ACCOUNT ON RECEIVE_ITEM.ACCOUNT_NO = ACCOUNT.ACCOUNT_NO
LEFT JOIN ITEM ON RECEIVE_ITEM.ITEM_CODE = ITEM.ITEM_CODE
WHERE ...

Conclusion

1차

• 호출

http://localhost:8888/api/receive/list

• 결과

2023-08-01 13:53:50.776  INFO 22677 --- [nio-8888-exec-4] c.d.s.g.c.aop.advisor.TimeTraceAspect    : 실행 메서드: findReceive, 실행시간 = 21ms

2차

• 입고 테이블 기준 20만개 가량 추가한 후 테스트 재진행
• 20만개 추가 [DB] - 그룹화 GROUP BY #64
• 30만개 LIMIT 으로 조회 테스트해본 결과
  콘솔에서 쿼리 부분 체크 시간은 5초 가량 걸리고 리스트 다 불러오고 로딩 끝나는 시간 까지는 약 1분가량 걸림
    * 쿼리 부분 콘솔 체크
   
    * 페이지 로딩 완료 시간 체크
   
• 56만개 이상으로 조회할 시 IOException pipe broken 발생됨
• 55만개 이하로 조회할 시 IOException pipe broken 발생하지 않음
  

쿼리 성능 개선방법

• 인덱스 사용하기
• 적절한 JOIN 사용하기
• 서브쿼리 최소화하기
• 쿼리 실행 계획 확인하기

개선 방법 중 인덱스 사용으로 쿼리 성능 테스트를 진행

인덱스 사용한 쿼리 튜닝 시 참고할 점

• 가급적 WHERE 조건에서는 인덱스 컬럼을 모두 사용한다.
• 인덱스 컬럼에 사용하는 연산자는 가급적 동등 연산자를 사용한다. (** LIKE, IS NULL, IS NOT NULL, NOT IN 등이 사용될 경우 인덱스 효율이 떨어짐)
• 인덱스 컬럼은 변형하여 사용하지 않도록 한다.
• OR 보다는 AND를 사용한다.
• 그룹핑 쿼리를 사용할 경우 HAVING 보다는 WHERE 절에서 데이터를 필터링한다. (** HAVING 절은 이미 WHERE 절에서 처리된 로우들을 대상으로 조건을 조회하기 떄문에 좋은 성는 발휘가 힘듦)
• DISTINCT는 가급적 사용하지 않는다. (내부적으로 정렬 작업이 일어나기 때문)
• IN, NOT IN 대신 EXIST 와 NOT EXIST를 사용한다.

인덱스 생성 전 쿼리 시간 (LIMIT 30만 기준)

인덱스 생성 후 쿼리 시간

위 그림과 같이 인덱스를 적용한 컬럼들을 조건 걸어서 약 21만개 가량의 데이터를 조회했으나 인덱스 적용 전과 큰 차이가 없었다. 다른 방법을 조사해야할 것으로 보임.

3차

쿼리 성능 개선에 대한 다양한 방법들 조사 후 적용해본 결과

• 인덱스
  이전에 WHERE 조건에 들어가는 컬럼들을 인덱스 지정하고 테스트했고 이번에 JOIN에 들어가는 컬럼들을 인덱스 지정하여 테스트 했으나 4~5초가량 걸리는 걸 확인했고 이전에 비해 개선되지 않았다.
• 쿼리 실행 계획 분석
  EXPLAIN 으로 확인해보았을 때 테스트하는 쿼리에는 인덱스가 정상적으로 동작하고 있었다.

• 쿼리 캐싱
  반복적으로 실행되는 쿼리를 캐시하여 데이터베이스 서버의 부하를 줄이고 성능을 향상시킬 수 있는 것.
  아래는 쿼리 캐싱 확인

SELECT SQL_CACHE * FROM TABLE

위와 같은 형식으로 실행가능하다. 테스트해보았으나 큰 차이가 없었다.

• 이외에는 서버의 하드웨어 성능과 구성을 최적화하거나 데이터 모델을 다시 구성하는 등으로 쿼리의 실행 속도를 개선할 수 있다.

4차

Sequence, Trigger 사용해서 JOIN 방식 변경

• join 되는 RECEIVE_CODE 가 문자열(RV)+시간(년월일~밀리초) 로 구성되어 있고 이 형식은 쿼리를 조회하는데 성능을 저하시키는 요인이 될 수 있다.
• 기본키가 AUTO_INCREMENT 와 같은 형식은 쿼리를 조회하는데 성능 개선이 되는 방법이 될 수 있다.
• 현재 RECEIVE_CODE 기본키 JOIN을 개선하기 위해서 PK를 RECEIVE_CODE 로 유지하면서 AUTO_INCREMENT가 되는 컬럼을 추가하여 쿼리를 수정하여 JOIN을 변경시킨다. (테이블에 바로 AUTO_INCREMENT 컬럼을 추가할 수는 없다 AUTO_INCREMENT는 기본키로 설정되어야 하기 때문)
• 방법으로는 1부터 시작하여 1씩 증가하는 Sequence를 생성하고 테이블에 데이터가 추가될 때 해당 컬럼에 추가될 수 있도록 Trigger를 작성해준다.
• 최종 목표 -> RECEIVE 테이블에 RECEIVE_ID 컬럼 추가, RECEIVE_ITEM에 RECEIVE_ID 컬럼 추가 / RECEIVE_ID는 Sequence와 Trigger를 사용해서 1씩 증가하는 auto_increment가 된다. / 쿼리의 JOIN 은 RECEIVE_ID 가 된다.

Sequence 생성 + 기존 테이블에 컬럼 추가 및 업데이트

• Sequence 생성문

CREATE SEQUENCE RECEIVE_SEQ START WITH 1 INCREMENT BY 1;

• Column 추가

ALTER TABLE RECEIVE ADD COLUMN RECEIVE_ID INT(10);

ALTER TABLE RECEIVE_ITEM ADD COLUMN RECEIVE_ID INT(10);

• 기존 RECEIVE 테이블 데이터들에 RECEIVE_ID 업데이트

UPDATE RECEIVE SET RECEIVE_ID=@RECEIVE_ID:=@RECEIVE_ID+1 ORDER BY RECEIVE_CODE; 

• 기존 RECEIVE_ITEM 테이블 데이터들에 RECEIVE_CODE에 맞춰 RECEIVE_ID 업데이트

UPDATE RECEIVE_ITEM
JOIN RECEIVE ON RECEIVE_ITEM.RECEIVE_CODE = RECEIVE.RECEIVE_CODE
SET RECEIVE_ITEM.RECEIVE_ID = RECEIVE.RECEIVE_ID;

Trigger 생성 + 기존 백 코드 일부 수정

• Trigger 생성문

DELIMITER //

CREATE TRIGGER GENERATE_RECEIVE_ID
BEFORE INSERT ON RECEIVE
FOR EACH ROW
BEGIN
    SET NEW.RECEIVE_ID = (SELECT NEXT VALUE FOR RECEIVE_SEQ);
END //

DELIMITER ;

• 백 코드 일부 수정

receiveMapper.insertReceive(receiveInsertDto);

        /* receiveId 불러옴 */
        Integer receiveId = receiveMapper.getReceiveIdByReceiveCode(receiveInsertDto.getReceiveCode());

        /* 불러온 receiveId를가지고 receiveItem이 저장됨 */
        for(ReceiveItemInsertDto receiveItem : receiveInsertDto.getReceiveItems()) {
            receiveItem.setReceiveCode(receiveInsertDto.getReceiveCode());
            receiveItem.setReceiveId(receiveId);
            receiveItemMapper.insertReceiveItem(receiveItem);
        }

결과 및 문제점

• 데이터 insert 과정까지 이상 없이 진행되었음.

• 테스트 쿼리의 JOIN을 아래와 같이 변경하였음.

SELECT RECEIVE_ITEM.RECEIVE_ITEM_NO, RECEIVE_ITEM.RECEIVE_CODE, RECEIVE_ITEM.MANAGER,
        RECEIVE_ITEM.ITEM_CODE, ITEM.ITEM_NAME, RECEIVE_ITEM.RECEIVE_COUNT, ITEM.UNIT,
        RECEIVE_ITEM.ACCOUNT_NO, ACCOUNT.ACCOUNT_NAME, RECEIVE_ITEM.CREATE_DATE
FROM RECEIVE_ITEM
LEFT JOIN RECEIVE ON RECEIVE.RECEIVE_ID = RECEIVE_ITEM.RECEIVE_ID
LEFT JOIN ACCOUNT ON RECEIVE_ITEM.ACCOUNT_NO = ACCOUNT.ACCOUNT_NO
LEFT JOIN ITEM ON RECEIVE_ITEM.ITEM_CODE = ITEM.ITEM_CODE
WHERE ...

• 문제점
  아래의 첫번째 이미지와 같이 DB에서 직접 쿼리 동작 시 시간이 20분 이상 지속되며 쿼리 결과를 불러오지 못하고 'http://localhost:8888/api/receive/list?itemName=신발&acountName=고양이 카페&manager=김' 와 같이 주소 창에서 테스트할 때는 두번째 이미지와 같이 경고가 뜨고 나서 수행이 반복되는 동작이 일어났다.
  
  

5차

쿼리 개선의 방향성 재정립

• 쿼리를 모든 데이터를 전체 조회하는 경우는 실질적으로 잘 일어나지 않으며 전체 조회를 하더라도 시간이 오래 소요되는 문제는 당연하고 개선함에 한계가 있다고 생각한다.
• 쿼리 개선의 방향을 1~3개의 조건문에 입력하고 그 상태에서 쿼리 시간을 체크하는 방향으로 개선한다.

index 사용하여 재확인

index 없는 경우

• 조건 3개 (3.3~3.5초 가량 소요)

http://localhost:8888/api/receive/list?itemName=신발&acountName=고양이&manager=김

• 조건 2개 (3.8초 가량 소요)

http://localhost:8888/api/receive/list?itemName=신발&acountName=고양이

• 조건 1개 (3.8초 가량 소요)

http://localhost:8888/api/receive/list?itemName=신발

index 있는 경우

• 조건 3개 (3.2초 가량 소요)

http://localhost:8888/api/receive/list?itemName=신발&acountName=고양이&manager=김

• 조건 2개 (3.2초 가량 소요)

http://localhost:8888/api/receive/list?itemName=신발&acountName=고양이

• 조건 1개 (3.1~3.5초 가량 소요)

http://localhost:8888/api/receive/list?itemName=신발

테스트 결과

• 큰 시간적인 차이는 보이지 않지만 각각 약 0.3초에서 0.7초 까지 정도의 개선이 있음을 확인하였음
• 이는 약 21만개의 데이터를 불러오는 것에 있어서 개선이며 보다 많은 양의 데이터인 200만개일 경우 3초~7초 정도의 개선이 있을 수 있는 등 데이터가 많을 수록 더 큰 개선이 있을거라 생각됨.
  
  
  

IOException : Broken pipe 원인 및 해결방법

원인

• 클라이언트가 응답 데이터를 적절한 시간 동안 처리하지 못하면 발생
• 네트워크나 서버 CPU 이슈로 느리게 처리되는 경우 발생

해결방법

• 클라이언트 요청 후 응답이 마칠 때까지 기다리는 것
• Exception 무시하도록 하기
• 중복 요청 확인 후 막는 것
• Timeout 값 늘리기
• 가용 스레드 늘리기

• 현재 증상은 다량의 데이터를 요청 후 응답과정에서 발생하므로 세번째 외의 방법으로 해결 계획
• Exception을 무시하도록 하는 것은 경우에 따라 할 수도 있으나 권장하는 방식은 아님
• 동작 시간을 측정하여 특정 시간을 넘어갈 경우 처리하는 방식으로 구상

결과

• IOException : Broken pipe 발생에 대한 해당 Exception의 직접적인 처리는 불가능하다. 이미 클라이언트와 연결이 끊겼을 때 나타나는 것이기 때문에 끊어지기 전에 처리가 되어야한다.
• 해당 Exception이 발생되는 시간을 체크한 후 AOP를 사용해서 처리하는 방식을 적용하였다.
  
  
  

방법 1 : 클라이언트에 '요청량 초과'를 담아서 보내줌

• TimeOutAspect

@Slf4j
@Aspect
@Component
public class TimeOutAspect {

    // 타임아웃 시간 설정 (8초를 넘어갈 경우 IOException 발생이 일어남으로 8초로 설정함)
    private static final long TIMEOUT = 8000;

    @Pointcut("@annotation(com.douzon.smartlogistics.global.common.aop.annotation.TimeOut)")
    private void timeOut(){};

    @Around("timeOut()")
    public Object handleTimeout(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();

        Object result = joinPoint.proceed();

        long endTime = System.currentTimeMillis();

        long elapsedTime = endTime - startTime;

        if (elapsedTime > TIMEOUT) {

            return ResponseEntity.status(HttpStatus.REQUEST_TIMEOUT)
                                .contentType(MediaType.APPLICATION_JSON)
                                .body(CommonResponse.error(new ErrorResponse<>("요청량이 초과되었습니다. 찾는 데이터를 입력하여 요청량을 최소화하세요.")));
        }

        return result;
    }
}

• TimeOut

@Target({ElementType.METHOD, ElementType.ANNOTATION_TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface TimeOut {
}

• Controller 적용

    @TimeOut
    @GetMapping("/list")
    public ResponseEntity<CommonResponse<List<ReceiveList>>> findReceive(
            @RequestParam(required = false, defaultValue = "") String receiveCode,
            @RequestParam(required = false, defaultValue = "") String manager,
            @RequestParam(required = false, defaultValue = "0") Integer itemCode,
            @RequestParam(required = false, defaultValue = "") String itemName,
            @RequestParam(required = false, defaultValue = "0") Integer accountNo,
            @RequestParam(required = false, defaultValue = "") String accountName,
            @RequestParam(required = false, defaultValue = "") String startDate,
            @RequestParam(required = false, defaultValue = "") String endDate
    ){
        List<ReceiveList> receiveList = receiveService.findReceive(
                receiveCode, manager, itemCode, itemName, accountNo, accountName, startDate, endDate);
        return ResponseEntity.status(HttpStatus.OK)
                .contentType(MediaType.APPLICATION_JSON)
                .body(CommonResponse.successWith(receiveList));
    }

• 메서드 시간을 측정해본 결과 8초를 초과했을 때 IOException : Broken pipe 발생하고 아래와 같이 페이지 로딩이 끊김

따라서 메서드 시간에 8초 시간 제한을 걸고 넘을 경우 아래와 같이 전송하도록 구현함.

방법 2 : 초과 발생할 경우 상위 일부만 보내줌

• 응답 데이터를 아무것도 없이 메시지만 띄우도록 하는 것은 클라이언트에서 조회에 따른 일반적인 응답이 아닐 수 있음
  따라서 상위의 일부 데이터만 띄워서 보내주는 것으로 수정

• TimeOutAspect

@Slf4j
@Aspect
@Component
public class TimeOutAspect {

    // 타임아웃 시간 설정 (8초를 넘어갈 경우 IOException 발생이 일어남으로 8초로 설정함)
    private static final long TIMEOUT = 8000;

    @Pointcut("@annotation(com.douzon.smartlogistics.global.common.aop.annotation.TimeOut)")
    private void timeOut(){};

    @Around("timeOut()")
    public Object handleTimeout(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();

        Object result = joinPoint.proceed();

        long endTime = System.currentTimeMillis();

        long elapsedTime = endTime - startTime;

        if (elapsedTime > TIMEOUT) {
            /* 상위 일부 데이터를 담을 리스트 생성 후 저장하여 리턴 */
            List<?> acceptData = null;
            if (responseData instanceof List<?>) {
                List<?> dataList = (List<?>) responseData;
                acceptData = dataList.subList(0, 500000);
            }
            return ResponseEntity.status(HttpStatus.REQUEST_TIMEOUT)
                                .contentType(MediaType.APPLICATION_JSON)
                                .body(CommonResponse.successWith(acceptData));
        }

        return result;
    }
}

• Timeout의 값보다 큰 시간이 측정되어서 아래와 같이 50만개의 상위 데이터가 출력됨(56만개 이상일 경우 Exception 발생했으므로 50만개로 제한함)
  
  

방법 3 : 페이징 처리

• 위의 '방법 1, 2'와 같이 AOP를 사용하여 처리 시간에 대한 대응 방법을 개선하여 '방법2'의 50만개만 끊어서 사용자에게 보여주는 것을 페이징 처리가 가능할지에 대해 조사한 결과 서비스단으로 다시 넘기는 등의 방식으로 구성하면 될 수도 있어보이긴 했으나 이미 결과 데이터를 가지고 있는 상태에서 다시 컨트롤러, 서비스 단 등으로 이동하여 동작하는 것은 권장되지 않는 방식이다.
  따라서 클라이언트에 요청 받고 리스트에 대한 페이징 처리를 하는 방식으로 진행한다.
  해당 내용은 [DB] - DBMS의 실행 계획 - Fin #75 로 이어짐.

추가 확인 및 개선할 부분

1. AOP에 대한 추가 학습 필요
2. AOP 적용 시 결과가
     {
         "success": true,
         "data": null,
         "error": null
     }
     이렇게 반환되는 상황인데 aop 적용과 리스트 결과 반환이 서로 잘되도록 수정 필요
     -> 메서드 타입 Object로 변경. 'joinPoint.proceed()' 호출한 후 원래의 메서드를 실행하고 반환값을 'returnValue' 변수에 저장하도록 함.
   이 후 해당 값을 리턴하도록 수정하여 해결.
3. 데이터가 그렇게 많지 않아 쿼리 시간 체크가 애매해서 데이터 추가 및 쿼리 수정 등을 통한 추가 테스트가 필요해보임
    -> shell script 사용해서 입고 테이블 기준 20만개 가량 추가하고 테스트 진행 중
4. 쿼리 동작 시간 개선 방법 조사
5. 확인 기준 55만개까지는 괜찮았으나 56만개 부터는 'java.io.IOException: Broken pipe' 이 발생하여 처리 방법에 대한 조사도 필요해보임
6. 인덱스 사용했으나 크게 변화가 없어서 다른 방법 조사
7. Timout 발생 시 처리를 페이징으로 응답할 수 있을지 조사

References

[Reference]	[Link]
Outline Image	https://webcoding-start.tistory.com/3
Settings	https://atoz-develop.tistory.com/entry/AspectJ-Weaver%EB%A5%BC-%EC%82%AC%EC%9A%A9%ED%95%9C-%EC%95%A0%EB%85%B8%ED%85%8C%EC%9D%B4%EC%85%98-%EA%B8%B0%EB%B0%98%EC%9D%98-%EC%8A%A4%ED%94%84%EB%A7%81-AOP-%EA%B5%AC%ED%98%84-%EB%B0%A9%EB%B2%95
간단한 AOP 적용예제	https://velog.io/@dhk22?tag=aop
AOP를 적용하여 메서드 호출 속도 측정	https://velog.io/@woply/Spring-AOP-%EA%B4%80%EC%A0%90%EC%9C%BC%EB%A1%9C-%EC%8B%A4%ED%96%89-%EC%86%8D%EB%8F%84-%EC%B8%A1%EC%A0%95%ED%95%98%EA%B8%B0
Spring AOP 정리, 개념, 프록시 기반 AOP, @aop	https://engkimbs.tistory.com/746
Spring Boot AOP 적용해보기	https://devjaewoo.tistory.com/87
특정상황에 스프링 AOP 적용	https://hstory0208.tistory.com/entry/Spring-%ED%8A%B9%EC%A0%95%EC%83%81%ED%99%A9%EC%97%90-%EC%8A%A4%ED%94%84%EB%A7%81-AOP-%EC%A0%81%EC%9A%A9%ED%95%98%EA%B8%B0
SQL 쿼리 튜닝하는 여러가지 방법	https://chung-develop.tistory.com/145
SQL 튜닝 (일반적인 튜닝방법)	https://cornswrold.tistory.com/87
SQL 튜닝 방법 정리	https://velog.io/@gillog/SQL-%ED%8A%9C%EB%8B%9D
쿼리 캐시	https://malgogi-developer.tistory.com/59
HTTP Request 전송 시, Broken pipe 이슈	https://devvkkid.tistory.com/223