[개발일지] Java application에서 RDBMS에 bulk insert

목차

• step1. 최적의 bulk insert  sql script 찾기
• step2. Java 구현 : UNION ALL batch insert  
• Spring Data JPA saveAll과의 성능 비교 
• 정리

 

 다들 엑셀을 참 좋아한다. 몇만 개의 row를 가지고 엑셀로 이리저리 조작하는 사업부서 분들을 보면 경이롭다. 동시에 컴퓨터가 매우 힘들어하는 게 옆에서도 느껴진다.

 

 아무튼 이번에 엑셀 업로드 기능으로 대용량으로 테이블에 insert 할 기능이 생겼다 (최대 수만 건 규모). 이런 대용량 insert는 실제 요구사항이 있는 편이다. 사업 부서에서 이벤트 소스를 모아 엑셀 파일로 개발팀에 전달한다던지, 일정 기간 동안 소스를 모집해서 디비 테이블에 넣는다던지, 백오피스 시스템에서 엑셀 업로드 기능을 활용해서 한 번에 넣는다던지 등이다. 

 

 Java application에서 JDBC 커넥션을 통해 RDBMS에 insert를 하는데, 이때 건건이 JDBC Connection을 맺어 insert하는 것보다, 일정 사이즈를 잡고 connection당 n개의 insert를 수행하는 것이 더 성능이 좋다. 이를 두고 batch insert, bulk insert 등으로 표현하는데 여기서는 bulk insert라 하겠다.

 

 bulk insert를 할 때 주요하게 볼만한 자원 지표를 아래와 같이 정했다.

 

n건의 데이터를 insert 한다고 했을 때,

• 사용할 JDBC Conneciton 개수
• JDBC Conneciton 하나에서 insert 할 row 수 (= bulk size)
• connection 마다 commit 여부 (JPA 영속성 반영)

  bulk insert를 활용하여 JDBC conneciton 수를 아끼고, DB 측의 부하도 줄여 DB, WAS 양쪽에서 전체적인 성능을 개선할 수 있다. 

 

테스트 (구현) 환경

• Oracle 19c
• Java 17
• Spring Boot 3.1.1
• Junit 5
• tool (sql developer, IntelliJ)

 

---

step1. 최적의 bulk insert  sql script

 먼저 Java 없이 DB 측에서 실행할 sql문을 만들어야한다. 최종적으로 어떤 SQL을 bulk insert로 만들어 보내는 것이 효율적인가를 찾아보기 위해서다. 결론부터 말하면 1만건 기준 일반적인 INSERT문 보다 98% 이상 짧아지는 INSERT문을 생성했다.

 

실행해 볼 insert script 유형은 다음과 같이 3가지이다.

-- 1. ROW 수만큼 INSERT STATEMENT
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
COMMIT;
....

-- 2. INSERT ALL
INSERT ALL 
    INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...)
    INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...)
    INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...)
	...
SELECT * FROM DUAL;
COMMIT;

-- 3. UNION ALL 
INSERT INTO TB1 (COL1, COL2, COL3, ...)
SELECT VAL1, VAL2, VAL3, ... FROM DUAL UNION ALL
SELECT VAL1, VAL2, VAL3, ... FROM DUAL UNION ALL
SELECT VAL1, VAL2, VAL3, ... FROM DUAL UNION ALL
....
SELECT VAL1, VAL2, VAL3, ... FROM DUAL;
COMMIT;

 

 

 1번부터 테스트해 봤다. 어차피 이 방법은 사용하지 않을 것이므로, 데이터가 많으면 진짜 오래 걸리는구나만 보고 넘기자.

 

INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
INSERT INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...);
....

 

다음은 INSERT ALL .. 문이다.

 

INSERT ALL 
    INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...)
    INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...)
    INTO TB1 (COL1, COL2, COL3, ...) VALUES(VAL1, VAL2, VAL3, ...)
	...
SELECT * FROM DUAL;

 

INSERT ALL... 실행시간

 

 1번 방법보다 압도적으로 빠르다. 5,000개에서 1,000개 대비 소요시간이 5배 이상으로 증가된 것으로 봐서, 5천 건을 1천 건씩 나누어서 commit 하며 실행했고, 실행시간이 약 80% 줄게 되었다.

 

세 번째 UNION ALL 절을 사용해서 테스트해 봤다.

 

-- 3. UNION ALL 
INSERT INTO TB1 (COL1, COL2, COL3, ...)
SELECT VAL1, VAL2, VAL3, ... FROM DUAL UNION ALL
SELECT VAL1, VAL2, VAL3, ... FROM DUAL UNION ALL
SELECT VAL1, VAL2, VAL3, ... FROM DUAL UNION ALL
....
SELECT VAL1, VAL2, VAL3, ... FROM DUAL;

 

UNION ALL... 실행시간

 

 UNION ALL 절을 사용한 bulk insert가 다른 스크립트보다 소요시간이 짧은 것을 확인했다. UNION ALL 절을 사용해 적절한 사이즈를 잡아 중간마다 commit을 해준다면 DB의 실행 시간이 줄어들어 DB로부터의 응답 시간을 획기적으로 줄일 수 있을 것이다. 1만 건 기준으로 1번 방법보다 98% 이상 빨라졌다. (6m 31s -> 4s)

 

 

 

 추가로 1천 건씩 나누어서 Web application 측에서 병렬 (parallel)로 실행한다면 위 시간보다 더 짧아질 수 있을 것이다. 이제 최적의 bulk insert문을 찾아냈으니 해당 insert문을 작성할 수 있게 Java를 만들어보자

---

step2. Java 구현 : UNION ALL bulk insert  

 JPA에는 Bulk size를 잡아서 insert를 할 수 있지만 (spring.jpa.hibernate.jdbc.batch_size), 이것이 내가 의도하는 oracle의 최적의 bulk isnert문을 작성해내지 못한다. Dialect를 Oracle로 지정해 주어도, 실제 실행되는 sql문은 1번과 같은 유형이었다. 단지 해당 insert문들을 1개의 JDBC Connectrion으로 수행할 뿐이었다. (아래 JPA Session Metrics 참고) 이 포스팅을 읽는 분 중에 Oracle RDBMS의 UNION ALL 절을 사용한 INSERT를 작성하는 JPA 설정 방법을 아는 사람은 댓글 바람

 

[09-21 10:13:10:235621] INFO  org.hibernate.engine.internal.StatisticalLoggingSessionEventListener - Session Metrics {
    33599 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    110014993 nanoseconds spent preparing 1006 JDBC statements;
    4126740925 nanoseconds spent executing 1006 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    66834591 nanoseconds spent executing 101 flushes (flushing a total of 303 entities and 202 collections);
    79699026 nanoseconds spent executing 505 partial-flushes (flushing a total of 307 entities and 307 collections)
}

 

 JPA 설정으로는 내가 의도한 batch insert를 작성하기는 불가능했고, 따라서 queryDSL, Spring JDBC Template 등의 다른 DB 접근 기술을 써야 했다. 아니면 Native Query를 직접 작성해야 한다.

 

 나는 프로젝트에 이미 myBatis를 통해 NativeQuery를 작성하고 있었기 때문에 아래처럼 xml 파일에 sql문을 작성했다. 

 

<insert id="saveBatch" parameterType="java.util.List">
    INSERT INTO TB1 (COL1, COL2, COL3, ...)

    <foreach collection="list" item="item" close="FROM DUAL " separator=" FROM DUAL UNION ALL ">
        SELECT #{item.val1}, #{item.val2}, #{item.val3}, ...
    </foreach>

</insert>

---

Spring Data JPA saveAll 과의 성능 비교

 Spring Data JPA는 비슷한 기능의 saveAll을 제공하지만, 앞선 native query에 비해서는 성능이 좋지 않다. saveAll은 내부적으로 save를 이용하여 하나의 Transaction 안에서 (=@Transactional proxy) 안에서 여러 건의 insert 문을 실행할 뿐이다. 

 

saveAll (위), UNION ALL insert (아래) (5천건 기준)

 

 실제 비즈니스 로직을 사용해서 하나는 saveAll을 사용하고, 하나는 Oracle UNION ALL insert 문을 사용했을 때 위처럼 80% 이상의 성능 차이가 나는 것으로 확인된다. 앞 스크립트 테스트와 같은 5천 건을 기준으로 시간차이가 나는 이유는 앞 뒤에 비즈니스 로직이 있기 때문이니 감안하길 바란다. 

 

 실제로 아래 영상을 보면 이동욱 개발자님 (현 인프런 CTO)의 경우 JPA로 Bulk Insert는 자제하고, (17:50 참고) jdbc 옵션으로 rewriteBatchedStatements 를 주어도, auto_increment (MySQL)이라면 Bulk Insert가 되지 않는 다는 것을 언급한다.

 

https://youtu.be/zMAX7g6rO_Y?t=1068 

[우아콘2020] 수십억건에서 QUERYDSL 사용하기 - 이동욱

 

---

정리

 결과적으로 bulk size를 1천 건으로 잡아, 하나의 JDBC Conneciton에서 1천 건을 insert 하고, commit 하도록 했다. 대용량 batch는 native query를 사용해서 최대한 DB 벤더사에 의존하는 것이 성능이 우수하다. MySQL의 경우 jdbc url에 rewriteBatchedStatements=true를 주어서 MySQL에서 제공하는 batch insert 문을 작성할 수 있는 것으로 안다. MySQL이라면 적극적으로 사용할 수 있겠다. (다만 auto_increment 의 경우 불가능)

 

 DB에 의존성을 높이는 로직이 좋지만은 않으나, saveALL()을 사용해서 JPA 수준에서 batch를 구현했다고 하기에는 앞에서 본 것처럼 실행시간이 80% 차이가 나기 때문에 이럴 때야말로 Native Query를 사용해서 성능 좋은 DB의 sql을 적극 활용할 줄 아는 것이 맞다고 본다.  객체 지향이라는 핑계로 성능을 무시하지 않았는지, 반드시 확인해봐야하며 이는 JPA를 사용할때 더욱 주의해보아야하는 문제들이다. 

 

 번외로 나의 경우 JPA Repository를 3가지로 구분해서 사용한다. 

 

repository 계층 구조

*RepositoryCustom에 Spring Data JPA  API가 아닌 DB에 의존하는 로직들을 넣어둔다. 따라서 Custom 구현체는 Mybatis, queryDSL 등의 API에 의존한다. 이번 batch insert도 Custom구현체에 MyBatis를 사용하여 구현하였다. 

 

TODO

 추가로 구현해야 하는 것들은 Batch 전체의 트랜잭션을 rollback 하는 부분이다. Batch 중간마다 commit을 해주기 때문에  예외가 발생했을 경우 적절하게 복구 (대응)하는 로직이 필요하다. 과감하게 전체 batch 트랜잭션을 취소할 수도 있고, 문제가 있는 부분만 복구하거나, 제외하여 batch를 계속 진행할 수도 있겠다. 이 부분은 배치 업무에 맞게 추가로 구현해 주면 된다. 

 

 나는 논리적으로 transaction ID를 채번해주어 예외 발생 시 전체 rollback 해주는 방향으로 구현했다. 채번방식은 아래 포스팅을 참고 바란다. 

 

https://kghworks.tistory.com/148

[개발일지] 채번(採番) 개발하기