preview image
허원철의 개발 블로그

MySQL - 큰 테이블을 다루는 jdbc 활용법 ②

2019-02-12

데이터베이스에 한 번에 많은 데이터 써야 한다면 어떻게 해야할까?

왜 이런 작업이 필요로 하는가?

아마 주변에서 제일 간단하게 찾을 수 있는 상황은 엑셀 파일 업로드가 있을 수 있다. 많게는 수 만건, 수 십만건까지 데이터베이스에 삽입(insert)를 하는 것인데 열당 삽입하게 된다면 굉장히 오랜 작업이 될 수 있다.

BatchInsert

이를 보통 BatchInsert 또는 BulkInsert 라고 말하는데, 여러 Insert 구문을 하나로 하나의 Insert 구문으로 작업하도록 하는 것을 의미한다. 그와 더불어 속도도 빠르다.


예를 들면, 다음 쿼리들이

1
2
3
4
5
6
7
INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at);
INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at);
INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at);
// ...

아래의 쿼리로 대체될 수 있다.

1
2
3
4
5
INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`) 
VALUES (:content, :status, :created_by, :created_at, :last_modified_at)
, (:content, :status, :created_by, :created_at, :last_modified_at)
, (:content, :status, :created_by, :created_at, :last_modified_at)
, ...;

예제

이전에 MySQL - 큰 테이블을 다루는 jdbc 활용법 ①를 분석하면서 작성했던 간단한 예제이다. 내용은 10만건의 데이터를 100개씩 batchInsert 하는 코드 이다.

spring.properties

MySQL Driver의 경우, rewriteBatchedStatements=true 옵션을 추가해야 한다.

1
2
spring.datasource.url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
# ...

Application.java

ApplicationRunner는 애플리케이션 로딩 시에 최초 작업을 정의하는 인터페이스이다. 중점적으로 봐야한 곳은 jdbcTemplate.batchUpdate(...) 부분이다. 내부적으로 batchList를 batchSize만큼 처리해주고 있다. (참고)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
private static final String INSERT_SQL = "INSERT INTO push_message (`content`, `status`, `created_by`, `created_at`, `last_modified_at`) VALUES (?, ?, ?, ?, ?)";

@Autowired
private JdbcTemplate jdbcTemplate;

@Bean
public ApplicationRunner runner() {
    return args -> {
        int insertCount = 100_000;
        int batchSize = 100;

        List<PushMessage> batchList = IntStream.range(0, insertCount)
                .mapToObj(i -> new PushMessage("content" + i, "wait", "heowc", LocalDateTime.now(), LocalDateTime.now()))
                .collect(Collectors.toList());

        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        jdbcTemplate.batchUpdate(INSERT_SQL, batchList, batchSize, (ps, arg) -> {
            ps.setString(1, arg.getContent());
            ps.setString(2, arg.getStatus());
            ps.setString(3, arg.getCreatedBy());
            ps.setTimestamp(4, Timestamp.valueOf(arg.getCreatedAt()));
            ps.setTimestamp(5, Timestamp.valueOf(arg.getLastModifiedAt()));
        });
        stopWatch.stop();
        System.out.println(stopWatch.prettyPrint());
    };
}

소요시간 측정

rewriteBatchedStatements batch size time(ms)
O 200 2500ms
O 100 5000ms
X x 200000ms


역시나 하드웨어 사양, 환경마다 다르겠지만 rewriteBatchedStatements 여부에 따라 batch size에 따라 속도 차이가 많이 났다.

이것도 역시 패킷을 분석해보면, Length가 16388인 패킷을 볼 수 있는데 net_buffer_length의 기본값(16384)과 관련이 있다. 이를 적절히 늘리는 것도 속도 향상에 도움이 될 수 있다(?)

참고

Tags: mysql jdbc batch-insert
← MySQL - 큰 테이블을 다루는 jdbc 활용법 ① Spring Camp 2019 후기 →