티스토리 뷰
Bulk Insert란?
아래와 같이 여러 개의 데이터를 하나의 Insert 쿼리로 삽입하는 것은 Bulk Insert라고 한다. 개별 Insert의 경우 쿼리를 던지고 응답을 받은 후에야 다음 쿼리를 전달하기 때문에 지연이 발생한다. 하지만 Bulk Insert는 하나의 트랜잭션으로 묶여 하나의 쿼리문으로 수행하기 때문에 성능이 좋다.
-- Bulk Insert
INSERT INTO last_insert_id_test(code) VALUES ('TEST1'), ('TEST2'), ('TEST3');
-- 개별 Insert
INSERT INTO last_insert_id_test(code) VALUES ('TEST4');
INSERT INTO last_insert_id_test(code) VALUES ('TEST5');
문제 상황
프로젝트에서 다수의 여행 데이터를 scheduler를 통해 특정 시간에 DB에 삽입해야 했다. 따라서, 다수의 데이터를 한번에 DB에 저장하기 위해 JPA의 saveAll()을 사용하였지만, Bulk Insert로 처리되지 않았다. 실제 JPA saveAll() 함수의 내부를 확인해보니 for문을 돌며 save() 함수를 호출하고 있었다.
@Transactional
@Override
public <S extends T> List<S> saveAll(Iterable<S> entities) {
Assert.notNull(entities, "Entities must not be null");
List<S> result = new ArrayList<>();
for (S entity : entities) {
result.add(save(entity));
}
return result;
}
1. 왜 JPA saveAll()은 Batch Insert 방식으로 작동하지 않을까?
결론부터 말하자면, JPA에서 ID 생성 전략으로 GenerateType.IDENDITY 사용 시 batch insert를 할 수 없다. JPA의 특성에는 영속성 컨텍스트와 쓰기 지연이 있다. 영속성 컨텍스트는 엔티티를 영구 저장하는 환경이라는 뜻으로, 애플리케이션과 데이터베이스 사이에서 객체를 보관하는 가상의 데이터베이스 같은 역할을 한다. JPA는 영속성 컨텍스트 내부에 엔티티 타입과 PK값을 저장해 엔티티를 식별한다. 쓰기 지연(transaction write-behind)은 영속성 컨텍스트에 변경사항이 발생했을 때 SQL을 버퍼에 모아뒀다가, flush하는 시점(트랜잭션이 commit 하는 시점)에 DB로 쿼리를 보내 실행하는 것이다. 그럼 왜 GenerateType.IDENDITY에서 Batch Insert를 지원하지 않을까?
공식 문서 를 살펴보니 아래와 같은 내용이 명시되어 있었다.
"@GeneratedValue(strategy = GenerationType.IDENTITY)의 경우 Hibernate는 JDBC 수준에서 batch insert를 비활성화한다"
ID 생성 전략이 IDENTITY인 경우 Insert 쿼리를 실행하기 전에는 PK를 알 수 없다. 따라서, 즉시 Insert 쿼리를 실행해 PK값을 가져와야 한다. 이는 Hibernate가 채택한 트랜잭션 write-behind 플러싱 전략을 방해한다. 또한, Bulk Insert를 실행하는 경우 즉시, DB에 Bulk Insert 쿼리를 날리고, Insert한 모든 데이터를 영속화해 영속성 컨텍스트에서 관리해야 한다. 이는 너무 많은 데이터를 영속화시켜 JVM에 할당된 메모리가 부족해질 수 있다. (out of memory 발생)
이러한 이유로, Hibernate는 ID 생성 전략이 IDENTITY인 경우 JDBC 수준에서 batch insert를 비활성화한 것이다. 따라서 JPA에서 batch insert를 사용하기 위해선 ID 생성 전략을 GenerateType.SEQUENCE나 GenerateType.TABLE을 사용해야 한다.
2. GenerateType.IDENTITY에서는 Batch Insert가 불가능한 것인가?
JdbcTemplate을 통해 Batch Insert로 처리할 수 있다. JDBC의 batchUpdate를 이용해 Batch Insert 형태로 쿼리를 실행시킬 수 있다. 아래는 그 예시이다. ( jdbcUrl에 rewirteBatchedStatements=true 을 추가해야 Bulk Insert 형식으로 쿼리가 실행된다. 아래 트러블 슈팅에서 설명할 예정)
@Repository
@RequiredArgsConstructor
public class OriginTripJdbcRepositoryImpl implements OriginTripJdbcRepository {
private final JdbcTemplate jdbcTemplate;
@Override
public void saveAllTrip(List<OriginTripCreateDto> commands) {
String TRIP_SQL = "INSERT INTO trip(name, date_time, meet_place, total_time, max_cnt, current_cnt) " +
"VALUES (?, ?, ?, ?, ?, ?)";
jdbcTemplate.batchUpdate(TRIP_SQL, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
OriginTripCreateDto command = commands.get(i);
ps.setString(1, command.getName());
ps.setTimestamp(2, Timestamp.valueOf(command.getDateTime()));
ps.setString(3, command.getMeetPlace());
ps.setInt(4, command.getTotalTime());
ps.setInt(5, command.getMaxCnt());
ps.setInt(6, command.getCurrentCnt());
}
@Override
public int getBatchSize() {
return commands.size();
}
}
);
}
3. 1 : N 관계에서는 어떻게 Bulk Insert를 구현하지?
JdbcTemplate을 통해 Bulk Insert 구현에 성공했지만, 또 다른 문제가 있었다. 현재 프로젝트에서는 아래와 같이 trip : tripPlace = 1 : N 관계이다. trip을 Bulk Insert로 삽입했지만, ID 생성 전략이 IDENTITY이기 때문에 각 trip의 PK를 알 수 없었다. 따라서, trip_place의 외래키로 trip_id를 넣어줄 수 없었다.
trip의 PK를 어떻게 알 수 있을까?
MySQL에서는 Auto_Increment 컬럼에 대해 중복되지 않고, 저장된 순서대로 증가하는 숫자값을 부여하기 위해 자동 증가 락(AUTO_INCREMENT LOCK) 이라는 테이블 수준의 잠금을 사용한다. 따라서, PK가 Auto Increment인 테이블에서의 Bulk Insert는 값이 1씩 증가하는 순차적인 PK를 가진다는 것을 보장할 수 있다.
아래와 같이 PK가 Auto Increment인 테이블에서 Bulk Insert를 통해 3개의 데이터를 삽입한다 해보자. 삽입 후, SELECT last_insert_id() 를 통해 첫번째로 삽입된 데이터의 PK를 알 수 있다. 따라서, 첫번째로 삽입된 데이터의 PK를 기준으로 1씩 늘려가며 Bulk Insert된 모든 데이터의 PK를 알 수 있다.
mysql> INSERT INTO last_insert_id_test(code) VALUES ('TEST1'), ('TEST2'), ('TEST3');
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
아래는 trip 데이터를 Bulk Insert 한 뒤, SELECT last_insert_id()를 통해 알아낸 첫번째로 삽입된 trip의 PK를 사용해 trip_place 데이터를 삽입하는 코드이다.
@Override
public void saveAllTripPlace(List<OriginTripPlaceCreateDto> commands) {
String TRIP_PLACE_SQL = "INSERT INTO trip_place(`order`, trip_id, place_id) VALUES (?, ?, ?)";
jdbcTemplate.batchUpdate(TRIP_PLACE_SQL, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
OriginTripPlaceCreateDto command = commands.get(i);
ps.setInt(1, command.getOrder());
ps.setLong(2, command.getTripId());
ps.setLong(3, command.getPlaceId());
}
@Override
public int getBatchSize() {
return commands.size();
}
});
}
@Override
public Long lastInsertId() {
return jdbcTemplate.queryForObject("SELECT last_insert_id()", Long.class);
}@Service
@RequiredArgsConstructor
public class OriginTripCreateService {
private final OriginTripRepository originTripRepository;
private List<OriginTripPlaceCreateDto> createOriginTripPlaces(List<OriginTripCreateDto> originTrips) {
Long lastInsertTripId = originTripRepository.lastInsertId();
// lastInsertId기반으로 PK 연산
IntStream.range(0, totalCnt)
.forEach(idx -> {
Long tripId = lastInsertTripId + (long) idx;
originTrips.get(idx).setId(tripId); // PK 설정
});
return originTrips.stream()
.map(trip -> createOriginTripPlace(trip.getId(), trip.getPlaces()))
.flatMap(Collection::stream)
.collect(Collectors.toList());
}
}SELECT last_insert_id() 반환값
1. last_insert_id()는 데이터베이스 커넥션마다 관리된다. 해당 커넥션에서 아직 삽입된 데이터가 없는 경우 0을 반환한다.
2. Bulk Insert에서는 첫번째로 삽입된 데이터의 PK를 반환하지만, 개별 Insert에서는 마지막으로 삽입된 데이터의 PK를 반환한다. 아래 트러블 슈팅 과정에서 더 자세히 설명할 예정이다.
트러블 슈팅
JdbcTemplate으로 bulkInsert를 한 뒤, SELECT last_insert_id() 반환값을 보니 첫번째로 삽입된 PK가 아닌 마지막으로 삽입된 PK로 반환되었다.
[ 문제 원인 가정 1 ]
처음에는 bulkInsert를 실행한 DB 커넥션과 last_insert_id()를 실행한 DB 커넥션이 달라 last_insert_id()가 마지막으로 삽입된 PK값으로 나왔다고 생각했다. 하지만, Spring은 transactionManager를 통해 트랜잭션 동기화 기능을 제공하고 있다. 즉, 같은 트랜잭션 내에서는 같은 커넥션을 사용한다.
또한, DB 커넥션이 달랐다면 last_insert_id()의 값은 마지막으로 삽입된 PK가 아닌, 0이 나왔어야 한다. insert가 발생하지 않은 커넥션에서는 last_insert_id()가 0으로 반환되기 때문이다. 이러한 이유로 각 쿼리를 실행한 DB 커넥션이 다르다는 것은 잘못된 가정이였다.
[ 문제 원인 가정 2 ]
last_insert_id()의 반환값을 살펴보자. 아래와 같이 Bulk Insert 시에는 하나의 쿼리로 보기 때문에 첫번째로 삽입된 PK를 반환한다. 개별 Insert 하는 경우에는 마지막으로 삽입된 PK를 반환한다.
create table last_insert_id_test (
id bigint auto_increment primary key,
code varchar(255)
);-- Bulk Insert
mysql> INSERT INTO last_insert_id_test(code) VALUES ('TEST1'), ('TEST2'), ('TEST3');
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
-- Insert
mysql> INSERT INTO last_insert_id_test(code) VALUES ('TEST4');
mysql> INSERT INTO last_insert_id_test(code) VALUES ('TEST5');
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 5 |
+------------------+
last_insert_id()의 반환값이 마지막으로 삽입된 PK로 나왔다는 것은, Bulk Insert가 실행되지 않다는 뜻이다. 이를 직접 확인해보고자, Jdbc 쿼리 로그를 살펴보았다. 쿼리 로그는 아래와 같은 설정을 통해 확인할 수 있다.
spring:
datasource:
url: jdbc:mysql://localhost:3306/hibernate_batch?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999
username: ${DATABASE_USERNAME}
password: ${DATABASE_PASSWORD}
driver-class-name: com.mysql.cj.jdbc.DriverprofileSQL=true 와 logger=Slf4JLogger, maxQuerySizeToLog=999999 을 추가로 설정해줬다.
1. profileSQL=true : Driver에서 전송하는 쿼리를 출력하기 위한 옵션
2. logger=Slf4JLogger : Driver에서 쿼리 출력시 사용할 Logger를 설정하는 옵션. (MySQL 드라이버는 default로 System.err로 출력하도록 설정되어 있으므로 지정 필수)
3. maxQuerySizeToLog=999999 : 출력할 쿼리 길이를 설정하는 옵션 (MySQL 드라이버는 기본값이 0으로 지정되어 있어 설정하지 않는 경우 쿼리가 출력되지 않는다)
그리고, 실제로 Jdbc 쿼리 로그를 확인해보니, 예상과 같이 Bulk Insert가 실행되고 있지 않았다. Insert 쿼리가 개별적으로 실행되고 있어 last_insert_id() 반환값이 마지막으로 삽입된 PK가 나왔던 것이다.
문제 해결
batch-jdbcbatch 이 글에는 JDBC에서 제공하는 batch 처리 옵션을 알려주고 있다.
JDBC offers support for batching together SQL statements that can be represented as a single PreparedStatement. Implementation wise this generally means that drivers will send the batched operation to the server in one call, which can save on network calls to the database. Hibernate can leverage JDBC batching. The following settings control this behavior.
이를 직역해보면 아래와 같다.
JDBC는 단일 PreparedStatement로 표현될 수 있는 SQL 문을 일괄 처리할 수 있도록 지원한다. 구현 측면에서 이것은 일반적으로 드라이버가 한 번의 호출로 일괄 처리된 작업을 서버로 전송하여 데이터베이스에 대한 네트워크 호출을 절약할 수 있음을 의미한다. Hibernate는 JDBC 일괄 처리를 활용할 수 있다.
즉, JDBC는 Bulk Insert를 지원하는 것이 아니라, batch size 크기 만큼 개별 Insert 쿼리를 한번에 실행시켜주는 것이다. 따라서 위와 같이 개별 Insert 쿼리가 날아갔던 것이다. 그럼 Bulk Insert 형태로 쿼리를 실행하려면 어떻게 해야할까?
JDBC에서 Bulk Insert를 하기 위해선 database url에 rewirteBatchedStatements=true 옵션을 추가해야 한다. 이 옵션을 추가하면 connector 레벨에서 insert 구문을 재작성(rewrite) 하게 되어 Bulk Insert(multi-line Insert)가 가능해진다.
jdbc:mysql://localhost:3306/hibernate_batch?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999&rewriteBatchedStatements=true
그리고, 다시 JdbcTemplate을 실행시켜 보니 아래와 같이 Bulk Insert 되었다! last_insert_id()도 첫번째로 삽입된 데이터의 PK으로 잘 나왔다.
성능 비교
10000건 데이터 삽입에 대해 saveAll()와 Bulk Insert 성능을 비교해봤습니다. 테스트 환경은 MySQL를 사용했습니다. 테스트 결과 Bulk Insert의 쿼리 수행 시간은 374ms가 나왔고, saveAll() 쿼리 수행 시간은 1968ms가 나왔습니다. 10000건의 데이터에 대해 Bulk Insert가 개별 Insert되는 saveAll()에 비해 약 5배 빠른 것을 확인할 수 있었습니다.
@Test
void bulkInsertTest() {
List<OriginTripCreateDto> commands = new ArrayList<>();
for(int i = 0; i < 10000; i++) {
commands.add(createOriginTrip("바다 보러 갈래?"));
}
long startTime = System.currentTimeMillis();
originTripRepository.saveAllTrip(commands); // Bulk Insert
long endTime = System.currentTimeMillis();
System.out.println("execution time = " + (endTime - startTime) + "ms");
}
@Test
void saveAllTest() {
List<Trip> commands = new ArrayList<>();
for(int i = 0; i < 10000; i++) {
commands.add(createTrip());
}
long startTime = System.currentTimeMillis();
tripRepository.saveAll(commands); // saveAll()
long endTime = System.currentTimeMillis();
System.out.println("execution time = " + (endTime - startTime) + "ms");
}
Bulk Insert 주의할 점
1. MySQL의 경우 max_allowed_packet 값을 고려해야 한다.
MySQL의 경우 max_allowed_packet 이라는 설정값이 있다. 이 설정값은 DB server로 보낼 수 있는 최대 요청 packet size이다. 아래와 같은 SQL을 통해 확인할 수 있다. Bulk Insert를 하는 경우 한번에 많은 양의 데이터를 Insert하면 max_allowed_packet_size를 넘을 수 있기 때문에 적절한 batch_size를 고려해야 한다. 일반적으로 권장되는 배치 크기는 50-100이지만 데이터베이스 서버 구성과 각 배치 패키지의 크기에 따라달라질 수 있다.
mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
2. Bulk Insert는 트랜잭션 커넥션 풀을 고갈시킬 수 있다.
Bulk Insert은 시간이 많이 드는 연산이다. 이는 트랜잭션 커넥션 풀을 고갈시켜 다른 트랜잭션이 실행되지 못할 수 있다. 이를 방지하기 위해 꼭 필요한 경우가 아닌 이상 트랜잭션을 사용하지 않는 것이 좋다. 아래와 같이 하나의 트랜잭션에서 10만건을 만개씩 쪼개서 Bulk Insert한다고 해보자.
@Repository
@RequiredArgsConstructor
class CsvRepository {
public void saveAll(List<CsvDto> commands) {
String SQL = "INSERT INTO user(id, name, gender) VALUES (?, ?, ?)";
jdbcTemplate.batchUpdate(SQL, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
OriginTripPlaceCreateDto command = commands.get(i);
ps.setLong(1, command.getId());
ps.setString(2, command.getName());
ps.setString(3, command.getGender());
}
@Override
public int getBatchSize() {
return commands.size();
}
});
}@Service
@RequiredArgsConstructor
public class CsvService {
private final CsvRepository csvRepository;
@Transactional
public void create() {
// 1만건씩 리스트로 담고 있는 10만건 리스트
List<List<CsvDto>> data = new ArrayList<>();
for(List<CsvDto> dto : data) {
csvRepository.saveAll(dto);
}
}
}
만약 9만건까지는 정상적으로 Insert되고, 10만건째 파일을 Insert하는 과정에서 예외가 발생했다고 한다면, 트랜잭션으로 묶여있기 때문에 앞선 9만건의 데이터까지 모두 ROLLBACK 된다. @Transactional은 autoCommit을 false로 세팅해서 트랜잭션이 끝날 때 ROLLBACK/COMMIT 을 결정하게 된다. @Transactional을 사용하지 않는다면, autoCommit이 true이기 때문에 각각의 쿼리가 개별 트랜잭션으로 실행된다.
이를 해결하기 위해선, @Transactional을 사용하지 않고, 각각의 Bulk Insert를 실행하면 된다. 모두 실행한 뒤, 예외가 발생한 연산에 대해서만 따로 처리해주면 된다. (로그를 통해 예외가 발생한 연산 확인 가능)
@Service
@Slf4j
@RequiredArgsConstructor
public class CsvService {
private final CsvRepository csvRepository;
@Transactional
public void create() {
// 1만건씩 리스트로 담고 있는 10만건 리스트
List<List<CsvDto>> data = new ArrayList<>();
for(List<CsvDto> dto : data) {
try {
csvRepository.saveAll(dto);
} catch(Exception e) {
log.error(e); // 로그 찍기
}
}
}
정리
JDBC Bulk Insert 장점
- 대용량 데이터 Insert 처리 속도가 대폭 개선된다.
- DB 부하도 대폭 개선된다.
JDBC Bulk Insert 단점
- DB의 외래키가 복잡하게 설정되어 있을 경우 application의 로직이 복잡해진다.
- 벌크 연산은 영속성 컨텍스트를 무시하고 DB에 직접 쿼리한다. 따라서, DB에 반영된 변경이 영속성 컨텍스트에는 반영되지 않을 수 있다. JPA와 함께 사용할 경우 영속성 컨텍스트와 DB간의 데이터 정합성 문제가 발생할 수 있으므로 주의 필요 (참고)
References
https://helloworld.kurly.com/blog/bulk-performance-tuning/
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id
https://backtony.github.io/jpa/2021-08-12-jpa-springdatajpa-2/#1-bulk-insert