다량의 데이터 삽입 시간 줄이기

이번 네트워킹 과제는 지난 과제와 비슷하게 이어서 진행된 과제이다.

과제레포

지난 과제는 20여개의 엑셀에 있는 데이터를 파싱해서 삽입하는것이 포인터라면, 이번 과제는 파일의 크기가 10,000배 증가한 20만개의 데이터를 삽입하는 과제이다.

우선, 기존 코드로 엑셀에 데이터를 넣어보자.

 

DB에 20만개의 삽입 쿼리

@Transactional
public void createData() throws InvalidFormatException, IOException {
    OPCPackage excel = OPCPackage.open(new File(EXCEL_PATH));
    Workbook sheets = new XSSFWorkbook(excel);
    Sheet firstSheet = sheets.getSheetAt(0);
    
    long start = System.currentTimeMillis(); // 시작 시간
    for (Row row : firstSheet) {
        if (row.getRowNum() == 0) {
            continue;
        }
        String zipCode = getZipCode(row);
        String basicAddress = getBasicAddress(row);
        String roadNameAddress = basicAddress + " " + getRoadNameAddress(row);
        String landLotNameAddress = basicAddress + " " + getLandLotNameAddress(row);
        log.info("[기본 주소]: {}", basicAddress);

        Property createdProperty = Property.of(zipCode, roadNameAddress, landLotNameAddress);
        propertyRepository.save(createdProperty);
    }
    System.out.println("==========소요 시간 =========" + (System.currentTimeMillis() - start) + "ms");
    sheets.close();
}

 

10만 밀리초 (약 100초)가 소요된다.

삽입 쿼리가 한번에 하나씩 들어가기 때문에 소요시간이 길다.

요청도 2분이 걸린다;;

saveAll()을 사용해보자.

    @Transactional
    public void createData() throws InvalidFormatException, IOException {
        OPCPackage excel = OPCPackage.open(new File(EXCEL_PATH));
        Workbook sheets = new XSSFWorkbook(excel);
        Sheet firstSheet = sheets.getSheetAt(0);
        long start = System.currentTimeMillis();
        List<Property> properties = new ArrayList<>(); // 리스트에 데이터를 담아두고
        for (Row row : firstSheet) {
            if (row.getRowNum() == 0) {
                continue;
            }
            String zipCode = getZipCode(row);
            String basicAddress = getBasicAddress(row);
            String roadNameAddress = basicAddress + " " + getRoadNameAddress(row);
            String landLotNameAddress = basicAddress + " " + getLandLotNameAddress(row);
            log.info("[기본 주소]: {}", basicAddress);

            Property createdProperty = Property.of(zipCode, roadNameAddress, landLotNameAddress);
            properties.add(createdProperty);
        }
        propertyRepository.saveAll(properties); // saveAll()을 통해 한번에 삽입한다.
        System.out.println("========== 소요 시간 =========" + (System.currentTimeMillis() - start) + "ms");
        sheets.close();
    }

거의 10만 ms로 약 100초, 크게 달라지지 않는다.

데이터가 많아질수록, save()와 saveAll() 사이에도 성능차이가 존재하는데 그 차이는 Transaction에 있다.

save()를 사용하면 이 메서드 자체가 하나의 트랜잭션이기에, 20만개 데이터를 삽입하는 트랜잭션안에 작은 save 트랜잭션을 생성하고 종료하는 과정이 필요하다.

 

반대로 saveAll()의 경우 하나의 자바 인스턴스 안에서 내부 함수를 호출하는 로직이기에 트랜잭션을 새로 생성하고 종료하지 않기에 성능 차이를 내는 것이다.

saveAll() 구현체

@Transactional 
public <S extends T> List<S> saveAll(Iterable<S> entities) { 
		Assert.notNull(entities, "Entities must not be null"); 
		List<S> result = new ArrayList(); 
		Iterator var4 = entities.iterator(); 
		while(var4.hasNext()) { 
            S entity = (Object)var4.next(); 
			result.add(this.save(entity)); 
		} 
        return result;
}

이에 관한 참고글은 아래 링크를 통해 참고하자.

https://sjparkk-dev1og.tistory.com/232

또한, 이 방법 또한 Transaction이 새로 생성되지 않았을 뿐 1개씩 데이터를 삽입하는 쿼리가 일어나고 있다.

따라서, save도, saveAll()도 20만 건의 데이터를 삽입하는데 1분이상의 시간이 걸리기 때문에 한번에 많은 데이터를 삽입하는데 불리하다.

Batch Insert

우선 쿼리가 많이 나가는 것을 줄여보도록 노력하자. 여러개의 가벼운 쿼리보다 하나의 무거운 쿼리가 효율적이니까, 20만개의 데이터를 하나의 쿼리로 넣게 되면 성능을 개선할 수 있지 않을까?

마치, SQL에서 아래와 같이 컬럼을 삽입하는 쿼리를 실행하면 되지 않을까? 라는 생각으로 이러한 Multi Row insert 문을 만들도록 노력해보자.

insert into property (c1, c2, ,,,) values
		(?, ?, ,,,)
		(?, ?, ,,,)
		(?, ?, ,,,)		

하지만, 이 방법은 현재 사용하고 있는 기본 키 생성 전략이 IDENTITY 이고 JPA를 사용하는 경우엔 따라 실행이 불가능했다.

 

IDENTITY 전략을 사용하면, 데이터베이스에 객체를 삽입하는 시점에서 DB의 auto_increment 전략에 맞춰 PK를 생성한다.

IDENTITY 전략과 JPA를 사용하게 되면 트랜잭션이 종료되는 시점이 아닌, 하나의 save가 일어나는 시점 (영속성 컨텍스트에 값을 저장하는 시점)에서 객체의 PK에 null을 담아서 실제 DB에 insert 쿼리가 날아간다.

이후 데이터베이스에서 값을 저장하면서 PK를 배정하는 것이다.

 

하지만, 이렇게 여러개의 데이터를 한번에 삽입하는 Batch insert를 실행해도 saveAll() 구현체 내부의 this.save() 를 실행하는 시점에 insert 쿼리가 날아간다.

 

즉, 실제 Multi-Row-Insert가 일어나지 않는 것이다.

 

결론 : JPA + IDENTITY 방식으론 Batch Insert를 구현할 수 없었다.

 

그래서 기본 키 생성 전략을 바꿔보자는 생각을 했다.

Sequence 전략과, Table 전략이 있었는데, 과제 요구사항 DB인 MySQL은 Sequence 전략을 지원하지 않기에 Table 전략으로 테스트를 진행했다.

 

Table 전략 사용

아래와 같이 Table에 DB 시퀀스를 만든다. (이에 대한 자세한 내용은 해당글을 참고하자.)

@TableGenerator(
        name = "PROPERTY_SEQ_GENERATOR",
        table = "SEQUENCE_TABLE",
        pkColumnName = "sequence_name" ,
        valueColumnName = "PROPERTY_SEQ",
        initialValue = 1,
        allocationSize = 200000
)

 

또한 실제 MySQL 데이터베이스에서 실행되는 쿼리를 조회하기 위해 yml 파일에 아래와 같은 설정을 추가한다.

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: ${DB_URI}?profileSQL=true&&logger=Slf4JLogger

• profileSQL=true : 실제 SQL에서 발생하는 쿼리를 조회하겠다.
• logger=Slf4JLogger : logger로는 Slf4j를 사용한다.

과연 성능이 개선되었을까?

 

우선, 이렇게 작성을 해도 @Transactional 이 달린 서비스 메서드를 실행하고 종료하는데 걸리는 시간은 104초 가량 걸린다.

또한, 여전히 트랜잭션이 insert문이 하나씩 실행된다.

Hibernate의 sql

 

MySQL에서 실행되는 SQL

 

하나씩 실행 중이다.

이렇게 실행되는 이유는 사실 JPA로는 Batch Insert를 구현하는 구현체가 존재하지 않기 때문인데, Table Generate 전략과 saveAll() 메서드를 사용하는 현재 방식으론 일부 설정을 바꿔 아래와 같이 Batch Insert를 구현할 수 있다.

이를 위해 우선 쿼리가 묶여서 실행될 수 있게 설정을 추가적으로 해주자.

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: ${DB_URI}?rewriteBatchedStatements=true&&profileSQL=true&&logger=Slf4JLogger
    
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 200000 // 200000개의 데이터를 묶어서 쿼리를 실행한다.
        order_inserts: true // 같은 테이블에 적용하는 insert문을 묶는다.
        order_updates: true // 같은 테이블에 적용된 update문을 묶어서 실행한다.

• rewriteBatchedStatements=true : 이 설정을 통해 Mutil-Row-Insert를 가능하게 해준다.

 

실행 결과

20만개의 쿼리가 묶여서 한번에 나간다.

이렇게 Batch Insert를 구현할 수 있고 구현 후 트랜잭션 메서드의 실행 시간은 아래와 같이 약 41초로 감소했다. (로그 실행 시간 감안)

 

또한, saveAll() 메서드의 실행 전 후 시간은 9초로 감소했다.

 

 

JdbcTemplate 사용

JPA에서는 명시적으로 Batch Insert를 할 수 있는 방법이 없지만, JdbcTemplate은 batchUpdate() 를 통해 Batch Insert를 구현할 수 있다.

아래와 같이 JdbcTemplate을 통해 데이터를 삽입할 수 있는 Repository를 만든다.

@Repository
@RequiredArgsConstructor
public class PropertyBulkRepository {

    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public void saveAll(List<Property> properties) {
        String sql =
                "INSERT INTO property (property_zipcode, property_road_name_address, property_land_lot_name_address)" +
                        "VALUES (?, ?, ?)";

        jdbcTemplate.batchUpdate(sql,
                properties,
                properties.size(),
                (PreparedStatement ps, Property property) -> {
                    ps.setString(1, property.getZipCode());
                    ps.setString(2, property.getRoadNameAddress());
                    ps.setString(3, property.getLandLotNameAddress());
                });
    }
}

 

단, 이 경우 기본 키 생성 전략을 IDENTITY 으로 다시 변경해줘야한다.

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "property_id")
    private Long id;

 

이렇게 되면 쿼리는 20만개로 하나의 쿼리만 발생한다.

 

이후 20만건 데이터 삽입을 진행하면 saveAll() 메서드 실행에 15초

 

전체 트랜잭션 실행에 34초의 시간이 걸린다.

 

결론

 

 

참고자료

https://cheese10yun.github.io/jpa-batch-insert/