JdbcTemplate batchUpdate로 벌크 insert하기

여러번의 단일 insert 질의를 통해 데이터를 저장하던 부분을 벌크 insert로 변경하여 한 번의 insert문으로 성능을 개선했던 이야기를 하고자 합니다.
 
원티드 채용공고를 스크래핑하는 배치 어플리케이션을 구축하고 있습니다.
원티드에 채용공고에는 JD의 상세내용, JD에 포함된 기술스택 목록이 존재합니다.
따라서 스크래핑한 채용공고 데이터를 1:N 관계로 이루어진 wanted_jd 테이블과 skill_history 테이블에 적재하고 있습니다.

• JD 상세 내용을 wanted_jd 테이블에 적재, 기술스택 목록은 skill_history 테이블에 적재

@StepScope
@Component
@RequiredArgsConstructor
public class WantedJdItemWriter implements ItemWriter<WantedJobDetailListResponse> {
    private final WantedJdRepository wantedJdRepository;
    private final SkillHistoryRepository skillHistoryRepository;

    @Override
    public void write(Chunk<? extends WantedJobDetailListResponse> chunk) throws Exception {
        for (WantedJobDetailListResponse wantedJobDetailList : chunk) {
            for (WantedJobDetailResponse wantedJobDetail : wantedJobDetailList.getJobDetailList()) {
            
                final JobCategory jdJobCategory = wantedJobDetail.getJobCategory();
                final List<WantedJobDetailResponse.WantedSkill> wantedJdSkillList = wantedJobDetail.getJobDetailSkillList();
                
		            final WantedJd savedWantedJd = wantedJdRepository.save(wantedJobDetail.toWantedJdEntity());
                createSkillHistory(jdJobCategory, savedWantedJd, wantedJdSkillList);        
            }
        }
    }
    
    private void createSkillHistory(final JobCategory jobCategory, final WantedJd wantedJd,
        final List<WantedJobDetailResponse.WantedSkill> wantedDetailSkillList) {

        for (WantedJobDetailResponse.WantedSkill wantedJdDetailSkill : wantedDetailSkillList) {
            final SkillHistory skillHistory = new SkillHistory(wantedJdDetailSkill.getKeyword(), jobCategory, wantedJd);
            skillHistoryRepository.save(skillHistory);
        }
    }
}

 
스크래핑한 원티드 JD의 목록을 순회하면서 원티드 JD 상세 내역을 WantedJd 엔티티에 저장하고 createSkillHistory 메서드를 통해 JD에 포함된 기술스택 목록을 순회하면서 Spring Data JPA의 save 메서드를 통해 SkillHistory 엔티티를 저장하는 간단한 코드입니다.
 
하지만 위와 같은 방식은 해당 JD에 포함되어있는 기술스택의 목록을 순회하면서 save 메서드로 저장하기 때문에 최악의 경우 JD의 100개의 기술스택의 포함되어있는 경우 insert 문이 100번 생성하게됩니다. (기술스택 목록 만큼 insert 질의 생성)

insert
  into skill_history (job_category_id, wanted_jd_id, keyword)
values
(2, 8194, 'TEST1');

insert
  into skill_history (job_category_id, wanted_jd_id, keyword)
values
(2, 8194, 'TEST3');

insert
  into skill_history (job_category_id, wanted_jd_id, keyword)
values
(2, 8194, 'TEST2');

 
벌크 insert를 사용한다면 아래와 같이 1번의 insert 질의를 통해 데이터를 저장할 수 있습니다.

insert
  into skill_history (job_category_id, wanted_jd_id, keyword)
values
(2, 8194, 'TEST1'),
(2, 8194, 'TEST2'),
(2, 8194, 'TEST3');

 
먼저 JPA를 사용하면서 벌크 insert를 할 수 있는 방안에 대해서 알아봤고
Hibernate가 Spring Data JPA에서 기본키 생성 전략이 IDENTITY라면 BatchInsert를 비활성화한다는 사실을 확인할 수 있었습니다.
이유는 엔티티 기본키 생성 전략이 IDENTITY로 설정되있다면, insert 질의가 발생한 후에야 DB에서 기본키 값을 확인할 수 있기 때문입니다.(JPA 영속성 컨텍스트 내부에서 엔티티를 기본키를 통해 식별하지만 IDENTITY전략의 경우 insert 질의가 DB에 전송된 후 기본키를 확인하기에..)
 
따라서 Spring Data JPA가 아닌 JdbcTemplate를 활용하여 벌크 insert를 구현했습니다.
 

JdbcTemplate BatchUpdate

JdbcTemplate BatchUpdate를 적용하기 앞서
Mysql 에서 벌크 Jdbc를 통해 벌크 insert를 한다면 rewriteBatchedStatements=true 옵션을 추가로 작성해야한다고 합니다.

 
다음과 같이 application.yml의 url에 rewriteBatchedStatements=true 옵션을 추가해줍니다.

 
 
JdbcTemplate을 사용하여 구현합니다.

@Slf4j
@Repository
@RequiredArgsConstructor
public class JdbcSkillHistoryRepository {

    private final JdbcTemplate jdbcTemplate;

    public void saveSkillHistoryList(final Long jobCategoryId, final Long wantedJdId,
        final List<WantedJobDetailResponse.WantedSkill> wantedDetailSkillList) {

        jdbcTemplate.batchUpdate(
            "INSERT INTO skill_history (wanted_jd_id, job_category_id, keyword) VALUES (?, ?, ?)"
            , new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    String keyword = wantedDetailSkillList.get(i).getKeyword();
                    ps.setLong(1, wantedJdId);
                    ps.setLong(2, jobCategoryId);
                    ps.setString(3, keyword);
                }

                @Override
                public int getBatchSize() {
                    return wantedDetailSkillList.size();
                }
            });
            
    }
}

@StepScope
@Component
@RequiredArgsConstructor
public class WantedJdItemWriter implements ItemWriter<WantedJobDetailListResponse> {
    private final WantedJdRepository wantedJdRepository;
    private final SkillHistoryRepository skillHistoryRepository;

    @Override
    public void write(Chunk<? extends WantedJobDetailListResponse> chunk) throws Exception {
        for (WantedJobDetailListResponse wantedJobDetailList : chunk) {
            for (WantedJobDetailResponse wantedJobDetail : wantedJobDetailList.getJobDetailList()) {
            
                final JobCategory jdJobCategory = wantedJobDetail.getJobCategory();
                final List<WantedJobDetailResponse.WantedSkill> wantedJdSkillList = wantedJobDetail.getJobDetailSkillList();
                
		            final WantedJd savedWantedJd = wantedJdRepository.save(wantedJobDetail.toWantedJdEntity());
                createSkillHistory(jdJobCategory, savedWantedJd, wantedJdSkillList);        
            }
        }
    }
    
    private void createSkillHistory(final JobCategory jobCategory, final WantedJd wantedJd,
        final List<WantedJobDetailResponse.WantedSkill> wantedDetailSkillList) {
        JdbcSkillHistoryRepository.saveWantedJdSkillList(wantedJobDetail, wantedJd, wantedDetailSkillList);
    }
}

 

 

+++

 

jdbcTemplate.batchUpdate를 통해 벌크 insert 부분의 SQL 질의만 로그에 출력되지 않는 이슈가 있었습니다.

datasource.url에 다음과 같은 옵션을 추가해야 로그에 출력되더라구요...

 

예시

jdbc:mysql://localhost:3306/db명?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999

• postfileSQL = true : Driver에 전송하는 쿼리를 출력합니다.
• logger=Slf4JLogger : 쿼리가 출력될 때 사용할 로거를 설정합니다.
  • MySQL 드라이버의 경우 기본값은 System.err이므로 설정이 필요합니다.
  • MariaDB 드라이버의 경우에는 Slf4j를 이용하여 로그를 출력하므로 별도의 설정이 필요하지 않습니다.
• maxQuerySizeToLog=999999 : 출력할 쿼리의 최대 길이를 설정합니다.
  • MySQL 드라이버의 경우 기본값이 0으로 되어 있어 설정하지 않으면 쿼리가 출력되지 않습니다.
  • MariaDB 드라이버의 경우 기본값이 1024이며, 0으로 설정할 경우 쿼리의 길이 제한이 없어집니다.

---

성능 비교

현재 시점 지원가능한 원티드의 프론트엔트, 백엔드 직군 채용공고 총 1,666개의 동일한 원티드 JD를 스크래핑하여 벌크 insert 적용 전 전과 적용 후의 원티드 JD 스크래핑 배치 실행 시간 및 SQL 질의 수을 비교했습니다.

(해당 배치 에서는 일정 개수의 데이터를 스크래핑한 후 Thread.sleep 를 통해 일정 시간 대기한 다음 다시 스크래핑을 진행하는 부분이 포함되어있어 스크래핑 시간이 많이 소요됩니다.)
 

벌크 insert 적용 전 실행 시간(JPA의 save() 메서드로 저장) = 약 12분 35초 소요

벌크 insert 적용 전 SQL 질의 수(JPA의 save() 메서드로 저장) = 27714개

 

벌크 insert 적용 후(JdbcTemplate의 batchUpdate() 메서드로 저장) 약 11분 18초 소요 (약 10% 단축)

벌크 insert 적용 후 SQL 질의 수(JdbcTemplate의 batchUpdate() 메서드로 저장) = 12666개 (약 54% 단축)