수만명의 회원 데이터를 데이터베이스에 효과적으로 인서트하기 (2) - bulk & batch insert

💡
지난 글에서는 Spring Data JPA를 활용한 저장 방법 중 save()와 saveAll() 메서드를 비교해 보았다.
당연하게도 saveAll()이 더 빨랐지만 동작 방식은 생각했던 것과 달랐다.
일괄적으로 insert 쿼리가 나갈 것이라고 생각했던 것과는 달리 save와 마찬가지로 단일 insert 쿼리가 나가는 것을 확인할 수 있었다.
이 글에서는 그 이유를 찾아보고 최종적으로 bulk insert로 회원 데이터를 저장했을 때 어느 정도 속도 차이가 나는지를 테스트해보려고 한다.

 

🥸 기본키 매핑 전략 중 IDENTITY를 사용하면 Bulk Insert를 할 수 없다!

• JPA에서 IDENTITY 전략을 사용하면 일부 제약 사항이 있다. 
• 이 제약 사항은 IDENTITY 전략이 특정 DBMS에서 제공하는 자동 증가(auto-increment) 기능을 사용하기 때문에 발생한다.

 

🤔 IDENTITY?

• IDENTITY 전략이란 데이터베이스에 레코드를 삽입할 때마다 해당 DBMS가 자동으로 식별자를 생성하여 할당하는 전략이다.
• MySQL의 AUTO_INCREMENT, SQL Server의 IDENTITY 등이 이에 해당한다.
• IDENTITY 전략을 사용하면 JPA는 엔티티가 데이터베이스에 삽입될 때마다 식별자를 얻기 위해 즉시 SQL INSERT 문을 실행해야 한다.
  • 이는 JPA가 트랜잭션 종료 시점까지 SQL 쿼리를 모아둘 수 없게 만든다.
• 이로 인해 결과적으로 JPA가 일괄 삽입(bulk insert)을 수행하지 못하게 된다.

 

 

@Entity
public class Member {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // ...
}

• 예를 들어, 다음과 같이 `IDENTITY` 전략을 사용하는 엔티티가 있을 때
• 다음과 같이 JPA를 사용해 여러 엔티티를 저장하려고 하면

 

List<Member> members = // ...

for (Member member : members) {
    entityManager.persist(member);
}

• JPA는 각 persist 호출마다 즉시 SQL INSERT 문을 실행하여 식별자를 얻어야 하므로 일괄 삽입을 수행하지 못하게 된다.
• 따라서 JPA에서 IDENTITY 전략을 사용하는 경우 성능 향상을 위해 일괄 삽입을 사용하려면 다른 방법을 찾아야 한다.
• 예를 들어, `SEQUENCE` 전략을 사용하거나, JDBC batch 기능을 직접 사용하는 방법이 있다.
  • 즉, 전략을 변경해야 하는데 실제 운영 중인 서비스 등에서 기본키 매핑 전략을 변경하는 것은 불안정한 상황을 초래할 수 있기 때문에 PASS
  • 많은 블로그에서 소개하는 JDBC batch 기능을 사용하기로 결정했다.
  • JDBC batch 기능을 통해 데이터베이스에 대한 쿼리 수를 줄여 네트워크 트래픽을 줄여 성능을 향상될 것이다.

 

✋🏻 여기서 잠깐! Bulk? Batch?

• Bulk insert와 batch insert 모두 대량의 데이터를 처리하는 데 사용되지만 그 방식과 사용 목적에 차이가 있다.
• Bulk insert는 대량의 데이터를 한 번에 삽입하는 데 초점을 맞추고 있다.
• 반면에 batch insert는 여러 개의 SQL 쿼리를 묶어서 처리하는 것에 초점을 맞추고 있다.
• bulk가 아무리 일괄처리를 한다고 해도 100만 개 이상의 데이터를 한 번에 넣는다면 데이터베이스에도 큰 무리가 갈지도 모르는 일이다.
• 따라서 데이터의 양과 데이터베이스의 성능을 고려해 batch insert를 이용하여 데이터를 나눠서 처리하는 방식으로 부하를 줄일 수 있다.

 

⚒️ bulk insert 구현하기

📃 application.yaml

spring:
  jpa:
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect
        show_sql: true
  datasource:
    url: jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true # batch 용
    username: root

• MySQL에서 bulk insert를 사용하기 위해서는 datasource.url 맨 뒤에 "rewriteBatchedStatements=true"를 붙인다.
•  기본적으로 JDBC(Java Database Connectivity)를 사용하여 데이터베이스에 대한 쿼리를 실행하면 각각의 쿼리가 개별적으로 실행된다.
  • 이는 작은 쿼리에서는 문제가 되지 않지만 대량의 데이터를 처리해야 할 때는 성능 저하를 초래할 수 있다.
• "rewriteBatchedStatements=true" 설정을 사용하면 JDBC가 일괄 처리된 쿼리를 자동으로 재작성하여 데이터베이스에 효율적으로 전달한다.
• 이를 통해 여러 개의 쿼리를 하나의 쿼리로 결합하여 데이터베이스 서버의 부하를 줄이고 네트워크 트래픽을 최소화할 수 있다.  

 

📃  batch Insert가 제대로 동작하는지 확인하기

url: jdbc:mysql://localhost:33060/test?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999
// profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999

• 위 옵션 추가로 MySQL에서 실제로 동작하는 쿼리를 확인할 수 있다.
• profileSQL=true
  • MySQL JDBC 드라이버에서 쿼리 프로파일링을 활성화할 수 있다.
  • 실행된 쿼리의 실행 시간 및 기타 성능 관련 정보를 확인할 수 있다.
• logger=Slf4JLogger
  • MySQL JDBC 드라이버에서 로깅 기능을 Slf4JLogger로 사용할 수 있도록 지정힌다.
    • Slf4JLogger는 일반적으로 로깅 프레임워크인 SLF4J와 함께 사용된다.
• maxQuerySizeToLog=999999
  • 로깅에 기록할 수 있는 최대 쿼리 크기를 999,999로 설정한다.
  • 이렇게 하면 해당 크기 이하의 쿼리가 로깅될 수 있다.

 

📃 BatchInsertRepository

@Slf4j
@Repository
@RequiredArgsConstructor
public class BatchInsertRepository {

    private final JdbcTemplate jdbcTemplate;

    public void memberSaveAll(List<Member> memberList, int batchSize) {
        String sql = "INSERT INTO member (name, member_grade, mobile, etc) VALUES (?, ?, ?, ?)";

        int totalSize = memberList.size();
        int fromIdx = 0;

        while (fromIdx < totalSize) {
            int toIdx = Math.min(fromIdx + batchSize, totalSize); // batchSize 만큼 끊고 맨 마지막에 남은게 있으면 totalSize로
            List<Member> batchMemberList = memberList.subList(fromIdx, toIdx);

            BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Member member = batchMemberList.get(i);
                    ps.setString(1, member.getName());
                    ps.setString(2, member.getMemberGrade());
                    ps.setString(3, member.getMobile());
                    ps.setString(4, member.getEtc());
                }

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

            // jdbcTemplate의 batchUpdate 메서드를 사용하여 일괄 삽입을 수행합니다.
            jdbcTemplate.batchUpdate(sql, setter);

            log.info("### fromIdx : " + fromIdx);
            fromIdx = toIdx;
        }
    }
}

 

• 여기서는 두 가지 방법이 있을 수 있는데 매번 batchSize를 끊고 db에 인서트 하는 방법과 모아뒀다가 한 번에 인서트하는 방법이다.
• 나의 경우에는 현재 많아야 몇 만의 데이터가 아주 가끔 추가될 가능성이 있고, 기본적으로는 수백 건 정도가 대부분이라 한 번에 일괄 삽입하도록 만들었다.
  • 굳이 리스트를 쪼개어 setter에 넣은 이유는 추후에 batch insert를 할 가능성을 염두에 둔 것이다.

 

📃 MemberController

@PostMapping("/save/batch")
public ResponseEntity<?> saveJsonBatch(@RequestBody Dto.Request request) {

    List<String> header = request.getExcelData().remove(0);
    List<Member> memberList = new ArrayList<>();
    stopWatch.start();

    for (List<String> excelData : request.getExcelData()) {

        Member member = Member.crateMemberOf(excelData.get(0), excelData.get(1), excelData.get(2), excelData.get(3));

        memberList.add(member);
    }

    System.out.println(memberList.size());


    batchInsertRepository.memberSaveAll(memberList, 1000);
    stopWatch.stop();

    log.info("save json batch success, 성능 측정 걸린시간: {}/sec" , stopWatch.getTotalTimeSeconds());

    return new ResponseEntity<>("success", HttpStatus.CREATED);
}

요청 body에 포함된 데이터를 Member 엔티티 리스트에 넣어두고 batchSize와 함께 위의 repository에 보내어 처리하는 방식이다.

• batchSize는 1000으로 고정했다.

 

📌 결과

2024-01-25T17:03:38.793+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 0
2024-01-25T17:03:38.832+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 1000
2024-01-25T17:03:38.865+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 2000
2024-01-25T17:03:38.903+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 3000
2024-01-25T17:03:38.940+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 4000
2024-01-25T17:03:38.986+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 5000
2024-01-25T17:03:39.070+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 6000
2024-01-25T17:03:39.099+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 7000
2024-01-25T17:03:39.146+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 8000
2024-01-25T17:03:39.176+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 9000
2024-01-25T17:03:39.206+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 10000
2024-01-25T17:03:39.242+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 11000
2024-01-25T17:03:39.288+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 12000
2024-01-25T17:03:39.332+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 13000
2024-01-25T17:03:39.360+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 14000
2024-01-25T17:03:39.387+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 15000
2024-01-25T17:03:39.414+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 16000
2024-01-25T17:03:39.440+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 17000
2024-01-25T17:03:39.475+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 18000
2024-01-25T17:03:39.509+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 19000
2024-01-25T17:03:39.539+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 20000
2024-01-25T17:03:39.562+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 21000
2024-01-25T17:03:39.562+09:00  INFO 30338 --- [nio-8080-exec-1] c.e.b.controller.MemberController        : save json batch success, 성능 측정 걸린시간: 0.85178374/sec

• 21629개의 데이터를 인서트 하는데 무려 0.8초 밖에 걸리지 않았다.
• 지난 글의 단건 인서트(save() 메서드 + for 문 사용)에서 145초가 걸린 것에 비하면 굉장한 성능 향상이라고 할 수 있겠다.
• saveAll()을 비교해도 약 15배 빨라졌다.

 

🎡 batchSize 변경해 보기 

url: jdbc:mysql://localhost:33060/test?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999 # batch 용

• MySQL에 실제 쿼리가 어떻게 나가는지 확인하기 위해 application.yaml에 옵션을 추가했다.

 

2024-01-25T18:05:47.423+09:00  INFO 35246 --- [nio-8080-exec-1] MySQL                                    : [QUERY] INSERT INTO member (name, member_grade, mobile, etc) VALUES ('이름1', '회원', '010-0000-0001', '돌맹이1'),... 이하 생략
2024-01-25T18:05:47.423+09:00  INFO 35246 --- [nio-8080-exec-1] MySQL                                    : [FETCH]  [Created on: Thu Jan 25 18:05:47 KST 2024, duration: 0, connection-id: 114, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
2024-01-25T18:05:47.425+09:00  INFO 35246 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 0
2024-01-25T18:05:47.479+09:00  INFO 35246 --- [nio-8080-exec-1] MySQL                                    : [QUERY] INSERT INTO member (name, member_grade, mobile, etc) VALUES ('이름1001', '회원', '010-0000-1001', '돌맹이1001'),... 이하 생략
2024-01-25T18:05:47.480+09:00  INFO 35246 --- [nio-8080-exec-1] MySQL                                    : [FETCH]  [Created on: Thu Jan 25 18:05:47 KST 2024, duration: 0, connection-id: 114, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
2024-01-25T18:05:47.481+09:00  INFO 35246 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 1000
2024-01-25T18:05:47.531+09:00  INFO 35246 --- [nio-8080-exec-1] MySQL                                    : [QUERY] INSERT INTO member (name, member_grade, mobile, etc) VALUES ('이름2001', '회원', '010-0000-2001', '돌맹이2001'),... 이하 생략
2024-01-25T18:05:47.532+09:00  INFO 35246 --- [nio-8080-exec-1] MySQL                                    : [FETCH]  [Created on: Thu Jan 25 18:05:47 KST 2024, duration: 0, connection-id: 114, statement-id: 0, resultset-id: 0,	at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)]
2024-01-25T18:05:47.532+09:00  INFO 35246 --- [nio-8080-exec-1] c.e.b.repository.BatchInsertRepository   : ### fromIdx : 2000

• 1000건씩 나눠서 처리하니 쿼리도 1000건씩 나가는 듯하다.

 

📃 MemberController

batchInsertRepository.memberSaveAll(memberList, 100000);

• batchSize를 10000으로 변경해 보았다.

 

• 쿼리가 한 개만 나가는 것을 확인할 수 있다.

 

🤔 프로젝트에 적용했을 때

• 실제 프로젝트에 적용해 보니 데이터 가공 및 중복 처리 등 여러 로직과 함께 있어 실제 테스트만큼 빠르지는 않았지만 약 1만 건의 신규 회원 데이터를 인서트 하는데 2초 안으로 끊을 수 있었다.

 

 

 

참고

뤼튼

https://techblog.woowahan.com/2695/

https://backtony.github.io/jpa/2021-08-12-jpa-springdatajpa-2/

https://dkswnkk.tistory.com/682