[Spring] csv 파일의 데이터 파싱 및 저장 성능 개선기 (JPA save() vs JdbcTemplate batchUpdate() vs MariaDB LOAD DATA INFILE)

운영 중인 사내 시스템에서 IEEE OUI 파일(https://standards-oui.ieee.org/oui/oui.txt)을 다운로드하여 MAC 주소와 제조사 정보를 추출하고, 이를 DB에 저장해야 하는 요구사항이 있었습니다.

 

기존 처리 흐름 (AS-IS)

• oui.txt file -> oui.csv 파일 형식으로 파싱합니다.
• csv 파일을 BufferedReader로 읽어서 JPA save() 반복 호출로 저장합니다.

 

즉, 애플리케이션 런타임 시점에 csv 파일을 oui, vendor 정보로 파싱하여 일일이 save() 메서드를 호출하는 방식이었습니다. 

하지만, 해당 방식은 매우 성능이 낮으며 이에 대한 첫 번째 대안으로는 saveAll() 메서드를 활용할 수 있습니다. 

 

먼저, JPA에서 제공하는 save(), saveAll() 메서드의 차이를 알아보고, 핵심 내용인 JdbcTemplate의 batchUpdate() 메서드도 save()와의 성능 비교를 통해서 상세히 설명드리도록 하겠습니다. 

 

save() vs saveAll()

 

• save(): 내부적으로 EntityManager를 통해 신규 엔티티면, persist() (비영속 -> 영속) 메서드를 호출하고, 영속성 컨텍스트에서 관리된 엔티티면 merge() (준영속 -> 영속) 메서드를 호출합니다.
• saveAll(): 하나의 트랜잭션에서 save() 메서드를 반복해서 호출합니다.

 

같은 클래스에서 save() 메서드를 내부적으로 호출하는 방식이므로 프록시 로직을 타지 않습니다. 그래서 saveAll()이 성능적으로 당연히 뛰어나다고 말할 수 있습니다.

 

하지만, JPA의 save()와 saveAll() 방식은 모두 쿼리가 하나씩 나가는 단건 삽입 방식입니다. (saveAll()도 마찬가지로 메서드 내부에서 save() 메서드를 호출하기에 쿼리 발생 방식은 save()와 동일하게 단건 삽입 방식입니다.)

 

반면에, 대량의 데이터를 저장할 때 Insert 쿼리가 한번 발생하는 방식인 Bulk Insert 방식은 위 두 방식 (JPA save(), saveAll())보다 훨씬 성능이 뛰어나다고 볼 수 있습니다. 따라서, 성능 개선을 위해 JdbcTemplate.batchUpdate() 메서드를 적용하여 다량의 Insert 쿼리를 한번에 처리하도록 변경하였습니다. 

 

Bulk Insert 쿼리

INSERT INTO common_mac_to_vendor (mac, vendor) VALUES (?, ?), parameters ['10:C2:BA:00:00:00','UTT Co. Ltd.'],['00:08:13:00:00:00','Diskbank Inc.'],['00:06:74:00:00:00','Spectrum Control Inc.'],['00:02:C6:00:00:00','Data Track Technology PLC'],['88:15:C5:00:00:00','Huawei Device Co. Ltd.'],['58:BA:D3:00:00:00','NANJING CASELA TECHNOLOGIES

 

 

Hibernate에서 기본키 생성 전략이 GenerationType.IDENTITY (AUTO_INCREMENT)인 경우, batch insert가 비활성화된다고 나와있습니다. 영속성 컨텍스트 내부에서 엔티티를 식별할 때는 엔티티 타입과 id 값으로 식별하지만, IDENTITY에서는 insert 쿼리를 실행해야만 id 값을 확인할 수 있기에 Batch Insert를 비활성화 한다고 나와있습니다. 

Whenever an entity is persisted, Hibernate must attach it to the currently running Persistence Context which acts as a Map of entities. The Map key is formed of the entity type (its Java Class) and the entity identifier.

For IDENTITY columns, the only way to know the identifier value is to execute the SQL INSERT. Hence, the INSERT is executed when the persist method is called and cannot be disabled until flush time.

For this reason, Hibernate disables JDBC batch inserts for entities using the IDENTITY generator strategy.

 

기본키 생성 전략이 Identity가 아니라면, batch_size 조정을 통해 Hibernate의 Batch Insert를 사용할 수 있습니다.

spring.jpa.properties.hibernate.jdbc.batch_size=100

 

 

첫 번째 개선: JdbcTemplate.batchUpdate() (TO-BE) (AS-IS와 비교해 16배 개선)

• 여러 개의 DB 업데이트 작업 (insert, update) 명령을 한 번에 묶어서 처리하기 위한 메서드
• 많은 양의 데이터를 DB에 삽입하는 작업인 Bulk Insert 방식으로 동작합니다.

 

관련 설정

• spring.datasource.url: rewriteBatchedStatements = true (MySQL)
• MariaDB Driver는 rewriteBatchStatements 속성을 확인하고, useBatchMultiSend 여부를 판단하여 Batch Insert 작업을 수행하는데 useBatchMultiSend 속성의 default 값이 true이기에 별도 설정이 필요 없습니다.

Stops checking if every INSERT statement contains the "ON DUPLICATE KEY UPDATE" clause. As a side effect, obtaining the statement's generated keys information will return a list where normally it would not. Also be aware that, in this case, the list of generated keys returned may not be accurate. The effect of this property is canceled if set simultaneously with "rewriteBatchedStatements=true".

 

batchUpdate() 메서드 

public void bulkInsert(List<CommonMacToVendor> commonMacToVendors) {
    String sql = "INSERT INTO common_mac_to_vendor (mac, vendor) VALUES (?, ?)";

    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            CommonMacToVendor v = commonMacToVendors.get(i);
            ps.setString(1, v.getMac());
            ps.setString(2, v.getVendor());
        }

        public int getBatchSize() {
            return commonMacToVendors.size();
        }
    });
}

 

 

37000 rows 기준: save() vs batchUpdate() 데이터 삽입 수행 시간 비교

 

1. save() 호출 시, 데이터 삽입 Total 소요 시간: 24s

05-21 10:41:10.192  INFO 14992 [task-scheduler-7] c.p.c.a.c.s.CommonMacToVendorService    :135 - total save time - 24589 ms (약 24s 소요)

 

2. batchUpdate() 호출 시, 데이터 삽입 Total 소요 시간 - 1.5s (AS-IS에 비해 약 16배 개선)

05-21 10:50:16.125  INFO 35236 [task-scheduler-9] c.p.c.a.c.s.CommonMacToVendorService    :137 - total save time - 1576 ms (약 1.5s 소요)

 

JPA의 기본 save 메서드를 다음 insert 쿼리가 계속 발생하지만, JDBC Batch Insert는 다음과 같이 Insert 쿼리 한번 발생합니다. 그래서 save()와 비교했을 때, 약 16배 개선된 것을 확인할 수 있습니다. 

 

두 번째 개선: LOAD DATA INFILE (TO-BE) (AS-IS와 비교해 약 42배 개선)

• csv file을 DB에 import 하기 위한 쿼리
• csv file을 읽어와 한번에 insert 쿼리를 발생하기에 성능이 매우 뛰어납니다.

 

명령어

• LOAD DATA INFILE 'file_name': 입력할 파일의 경로
• INTO TABLE 'table_name': 입력받을 테이블의 이름
• FIELDS: 라인 내의 필드들을 구분하는 방법
• TERMINATED BY ',': 각 필드가 끝나는 구분문자
• LINES -- 각 라인을 구분하는 방법
• TERMINATED BY '\n' -- 각 라인이 끝나는 구분문자

 

쿼리문 1: MariaDB(MySQL) 서버에서 적용 시

LOAD DATA INFILE '[csv path]'
INTO TABLE '[table name]'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 0 ROWS
(col1, col2 ..);

 

쿼리문 2: Local에서 적용 시

-- MySQL, MariaDB CLI 접속
mysql --local-infile=1 -u[username] -p[password] schema;

LOAD DATA LOCAL INFILE '[csv path]'
INTO TABLE '[table name]'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 0 ROWS
(col1, col2 ..);

 

 

해당 쿼리 실행 시 37000 rows 일괄 insert 하는 경우, 1s 안에 삽입이 되는 것을 확인할 수 있습니다.

MariaDB [cproject]> LOAD DATA INFILE '/cproject/sql/csv/MACToVendor.csv'
    -> INTO TABLE common_mac_to_vendor
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 0 ROWS
    -> (mac, vendor);
Query OK, 37832 rows affected (0.978 sec)
Records: 37832  Deleted: 0  Skipped: 0  Warnings: 0

 

 

 

<참고 자료>

https://www.oneschema.co/blog/import-csv-mysql

How to Import CSV into MySQL

https://piaojian.tistory.com/61

[MySQL] Error 2068 _데이터 로드 오류

https://velog.io/@hyunho058/JdbcTemplate-batchUpdate%EB%A5%BC-%ED%99%9C%EC%9A%A9%ED%95%9C-Bulk-Insert#jpa-saveall%EA%B3%BC-jdbctemplate-batchupdate-%EC%84%B1%EB%8A%A5-%EB%B9%84%EA%B5%90

JdbcTemplate batchUpdate()를 활용한 Bulk Insert

https://zoetechlog.tistory.com/90

[MySQL, MariaDB] csv 파일을 DB 테이블로 Import