벌크연산을 통한 update 성능 개선 시도 그리고 일반 연산과의 비교

💡 Issue

• 기존에는 각각의 데이터 중 30일이 지난 데이터를 dirty checking 하여 status를 update했음

  	<update id="updateOutOfDateSchedule">
  		UPDATE "Sbom_History"
  		SET status = 'exp'
  		WHERE DATE(created_at) < CURRENT_DATE - INTERVAL '30 days';
  	</update>

• 실제로 단건의 dirty checking이었으므로 DB에 반영하는 매 건마다 커넥션을 가져오고, Commit을 하는 과정에서 비용이 발생하여 성능적으로 문제가 발생할 것이 우려됐다.

• 앞으로 많은 양의 sbom 발급본이 저장될 경우를 대비하여 개선이 필요하다.

 

💡 MyBatis에서 Bulk 연산

• 기존에 사용하는 JPA가 아닌, MyBatis에서 Bulk 연산을 시도한다.foreach 태그를 사용하여 리스트나 배열의 각 아이템에 대한 업데이트 쿼리를 동적으로 생성
  • foreach 태그
    • 전달받은 collection 인자 값을 바탕으로 반복적인 SQL 구문을 작성
      • collection : 전달받은 인자를 속성 값으로 삽입 (ex. Map, Array, List, Set 등)
      • item : collection 속성에서 전달받은 collection의 인자값을 대체할 ‘이름’을 삽입
      • open & close : 구문이 시작/종료 될때 삽입할 문자열
      • separator : 반복된느 구문 사이에 삽입할 문자열
      • index : index값을 부를 변수명을 속성값으로 삽입

• @Transactional 을 활용해서 bulk update 작업을 하나의 트랜잭션으로 관리함으로써, 모든 업데이트가 성공적으로 수행되거나, 하나라도 실패할 경우 전체가 롤백되어 데이터의 일관성을 유지하도록 변경한다.

 

• 최종 변경 코드

  @Transactional
  @Scheduled(cron = SCHEDULE)
  public void sbomOutOfDateScheduling() {
  	List<sbomHistoryUpdateResponse> sbomHistoryUpdateList = sbomMapper.getSbomHistoryUpdateList();
  	if(!sbomHistoryUpdateList.isEmpty()) {
  		sbomMapper.updateOutOfDateSchedule(sbomHistoryUpdateList);
  	}
  }	
	
	<select id="getSbomHistoryUpdateList" resultType = "com.sga.sbomProject.domain.sbom.dto.sbomHistoryUpdateResponse">
		<![CDATA[ 
			SELECT id
		    FROM "Sbom_History"
		    WHERE created_at::date < CURRENT_DATE - INTERVAL '30 days'
		]]>
	</select>
	
	
	<update id="updateOutOfDateSchedule" parameterType="list">
		<![CDATA[ 
		    UPDATE "Sbom_History"
		    SET status = 'exp'
		    WHERE id IN
		    <foreach collection="sbomHistoryUpdateList" item="item" open="(" separator="," close=")">
		        #{item}
		    </foreach>
		]]>
	</update>

• SELECT로, 오늘 기준 30일 이전인 데이터를 먼저 추출

• 해당 아이템이 존재할 경우, UPDATE를 진행하는데, 이때 foreach 구문으로 한번에 UPDATE를 진행

 

💡 Quality Test

• FK 위반을 금하기 위해, 먼저 Scan_Target_Info에 데이터 10만개 저장

DO $$
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO "Scan_Target_Info" (target_type, target_uploaded_at)
        VALUES('Solution', '2024-08-05 11:21:02.206909');
    END LOOP;

END $$;

• 이후, Sbom_History에 약 10만개의 데이터 주입 (30일 이전으로 주입한다.)

DO $$
BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO "Sbom_History" (scan_target_id, request_user_name, description, type, status, name, path, created_at, finished_at, created_by )
        VALUES (i+2, 'test', 'test', 'SPDX','ava','test_name', 'test_path', NOW() - (INTERVAL '1 day' * (30 + i)),NOW() - (INTERVAL '1 day' * (30 + i)), 1 );
    END LOOP;
END $$;

 

 

 

🚨 테스트 도중 발견한 문제 🚨

PreparedStatement can have at most 65,535 parameters. Please consider using arrays, or splitting the query in several ones, or using COPY. Given query has 100,000 parameters

• 내가 사용하는 postgreSQL은 한 쿼리에서 최대 65,535개의 파라미터를 지원한다...
  • 이 제한은 PostgreSQL의 내부 데이터 구조와 관련있으므로 당장 변경은 어려울 것 같았다.
  • 10000개씩 10번 테스트 하는 걸로 서비스 코드를 변경했다

    	if(!sbomHistoryUpdateList.isEmpty()) {
    		int batchSize = 10000; // 한 번에 처리할 ID의 개수
    	    for (int i = 0; i < sbomHistoryUpdateList.size(); i += batchSize) {
    	        int end = Math.min(sbomHistoryUpdateList.size(), i + batchSize);
    	        List<Long> batchList = sbomHistoryUpdateList.subList(i, end);
    	        System.out.println("update 쿼리 시작 : " +  System.currentTimeMillis());
    	        sbomMapper.updateOutOfDateSchedule(batchList);
    	        System.out.println("update 쿼리 끝 : " +  System.currentTimeMillis());
    	    }
    	}

 

📗 Result

• select 시간 : 575ms (0.5초)

• update 시간 : 10000개씩 :
  • 0번째: 289 ms
  • 10000번째: 619 ms
  • 20000번째: 235 ms
  • 30000번째: 202 ms
  • 40000번째: 207 ms
  • 50000번째: 172 ms
  • 60000번째: 189 ms
  • 70000번째: 172 ms
  • 80000번째: 216 ms
  • 90000번째: 168 ms
  ⇒ 총 : 2,449 ms (2.5초)
• ⇒ 평균 : 244.9 ms

• 결과적으로 10만개의 데이터 3초 update