Batch Update 1편

650만건 정도의 데이터를 일괄로 업데이트해야 하는 상황이 생겼습니다. 이 과정에서 고민했던 부분들과 그 고민들을 어떻게 해결하였는지를 간단하게 글로 남겨 공유를 드리려고 합니다.

글을 쓰기 이전에 아래와 같은 의문이 들수도 있을것 같아 문답 형식으로 남겨놓았습니다 :) 

왜 코드를 통해 일괄 업데이트를 진행하였나요?

처음에는 sql을 이용하여 일괄 업데이트 할 계획이었지만, 해당 컬럼을 세팅하기 위해서는 여러 비즈니스 로직들이 들어가게 되면서 sql 만으로는 업데이트 하지 못하는 상황이었습니다.

스크립트 언어를 사용하였나요?

저희 회사에서는 spring boot + kotlin을 주력으로 프로젝트를 구성하고 있습니다. 물론 스크립트 언어를 이용하여서도 가능은 할 수 있었지만 여러 비즈니스 로직들을 다시 스크립트 언어로 작성하는 부분에서 많은 부담이 있었습니다. 그래서 주력 프로젝트에 해당 코드를 추가하고 외부에서 접근할 수 없도록 막아버렸습니다.

---

테스트 환경 및 구성

글을 쓰면서 실제로 실행을 해보고 얼만큼 개선되는지를 보기 위해 테스트 프로젝트를 하나 생성하였습니다. 그레이들과 코틀린 조합으로 생성하였습니다.

build.gradle

plugins {
   id 'org.springframework.boot' version '2.6.7'
   id 'io.spring.dependency-management' version '1.0.11.RELEASE'
   id 'org.jetbrains.kotlin.jvm' version '1.6.20-M1'
   id 'java'
   id "org.jetbrains.kotlin.plugin.allopen" version "1.6.21"
   id "org.jetbrains.kotlin.plugin.spring" version "1.6.21"
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
   mavenCentral()
}

dependencies {
   api group: 'io.github.microutils', name: 'kotlin-logging', version: '1.6.25'

   implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
   implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
   implementation 'org.springframework.boot:spring-boot-starter-jdbc'
   implementation 'org.springframework.boot:spring-boot-starter-web'
   implementation "org.jetbrains.kotlin:kotlin-stdlib-jdk8"
   implementation "org.jetbrains.kotlin:kotlin-reflect:1.6.21"
   runtimeOnly 'mysql:mysql-connector-java'
   testImplementation 'org.springframework.boot:spring-boot-starter-test'
   testImplementation 'junit:junit:4.13.1'
}

tasks.named('test') {
   useJUnitPlatform()
}

compileKotlin {
   kotlinOptions {
      jvmTarget = "1.8"
   }
}
compileTestKotlin {
   kotlinOptions {
      jvmTarget = "1.8"
   }
}

 

테스트 테이블

id, token, created_at이 기존에 있던 컬럼이고 expired_dt는 추가된 컬럼이라고 상황을 인지해주세요.

CREATE TABLE `login_token` (
  `id` bigint(11) NOT NULL COMMENT 'id',
  `token` varchar(50) NOT NULL COMMENT '토큰값',
  `expired_dt` date DEFAULT NULL COMMENT '토큰 유효기간',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

테스트 데이터 생성 코드

100만개 정도만 간단하게 생성해서 테스트 하도록 하겠습니다.

fun `테스트 데이터 생성`() {
    val chunkSize = 1000

    // 100만개
    (1..1_000_000).chunked(chunkSize) {
        logger.info { "현재 : ${it.first()}" }

        val loginTokens = it.map { id -> LoginToken(id = id.toLong(), token = UUID.randomUUID().toString()) }

        jdbcTemplate.batchUpdate(
            "INSERT INTO login_token(id, token) value (?, ?)",
            object : BatchPreparedStatementSetter {
                override fun setValues(ps: PreparedStatement, i: Int) {
                    ps.setLong(1, loginTokens[i].id)
                    ps.setString(2, loginTokens[i].token)
                }

                override fun getBatchSize(): Int {
                    return loginTokens.size
                }
            }
        )
    }
}

---

테스트 시나리오

login_table 에는 id, token, created_at 속성만 존재하였는데 토큰의 만료값을 추가로 설정하기 위해 expired_dt 컬럼을 새로 추가하였다. expired_dt는 생성일로부터 7일간만 유효하도록 업데이트해야한다.

고민 1. 메모리 아껴야겠지?

주력 프로젝트에 추가한 후에 사용할 예정인데 만약 해당 코드가 650만개 전부 어플리케이션에 로드해버리면 대참사가 날수도 있을겁니다. 메모리를 아껴야겠습니다.

데이터 쪼개기

가장 기본이 되는 방법입니다. 650만개를 한번에 모두 로드하지 않고 필요한 만큼만 잘라서 로드하도록 하는것입니다. 코드로 표현해보자면 이런느낌입니다. 

val chunkSize = 1000

(1..1_000_000).chunked(chunkSize) { subList ->
	// process..
}

데이터를 쪼개서 테스트를 해보겠습니다. 

쪼개?

fun `데이터 쪼개기`() {
    val chunkSize = 1000
    (1L..1_000_00L).chunked(chunkSize) { subList ->
        log.info { "현재 : ${subList.first()}" }
        val selected = loginTokenRepository.findAllById(subList)
        selected.onEach { it.expiredDt = it.createdAt!!.plusDays(7).toLocalDate() }
            .also { loginTokenRepository.saveAll(it) }
    }
}

제 로컬 기준 9분의 시간이 소요되었습니다.

필요 데이터만 로드하기

데이터를 쪼개서 실행함으로 메모리를 절약할 수 있었지만 조금 더 아낄 수 있을것 같은 부분이 있습니다. 실제로 업데이트 할 때 필요한 필드들만 가져오면 될 것 같다는 생각입니다.

예시를 조금 들어보겠습니다. login_token 이라는 테이블이 있다고 가정해보겠습니다. login_token 테이블에는 [식별값, 토큰값, 생성시간] 컬럼이 존재합니다. 그런데 이 때 테이블에 [만료일자]라는 필드를 추가해야 한다고합니다. 그리고 이 만료일자는 생성시간으로부터 24시간이라고 합니다. 자 그러면 우리가 이 만료일자를 bulk update 하기 위해서 토큰값을 굳이 가져올 필요는 없습니다. 식별값과 생성시간 필드만 있으면 충분하기 때문입니다. 

조회 한번에 7MB 정도 데이터를 로드했는데 수정 이후에는 평균적으로 5.5MB 정도로 줄은 모습을 보입니다.

고민 2 JPA가 필요할까?? 

JPA 버려버리기(?)

저희 회사에서는 JPA를 사용하고 있습니다. 위에서 조회할 때도 물론 JPA를 이용하였구요. JPA는 여러 이점을 이용하기 위해 영속성 컨텍스트라는 것을 사용하고 있는데요. 생각해보니 현재 일괄 업데이트하는 이 작업에는 JPA의 여러 장점(캐시, 변경감지... 등)들이 필요가 없습니다. 오히려 영속관련한 로직들을 실행할테니 이런 과정또한 비효율적일것 같고요. 영속성 컨텍스트에 영속되지 않게 하는 여러가지 방법들이 있을테지만 그냥 JPA를 버리고 jdbcTemplate을 사용하는게 더 나을수도 있겠어요. 버립니다..

버려?

jdbcTemplate의 update에는 두가지가 존재합니다. update() vs batchUpdate() 두 개의 차이는 여러 개의 쿼리를 하나의 쿼리로 뭉쳐서 보내느냐의 차이입니다. 추가적으로 jdbc_url에 다음의 옵션들을 추가해주세요. 

rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999

• rewriteBatchedStatements : 대량의 데이터를 insert할 경우(batch insert) 성능 향상을 시킬 수 있는 옵션
• profileSQL : Driver에서 전송하는 쿼리를 출력
• logger : Driver에서 쿼리 출력시 사용할 Logger를 설정.
• maxQuerySizeToLog : 출력할 쿼리 길이 설정

그럼 바로 테스트해보겠습니다. 

fun `JPA 버리기 - update()`() {
    val rowMapper = RowMapper { rs, _ ->
        LoginToken(
            id = rs.getLong("id"),
            token = rs.getString("token"),
            expiredDt = LocalDate.parse(rs.getString("expired_dt")),
            createdAt = LocalDateTime.parse(rs.getString("created_at"), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
        )
    }

    val chunkSize = 1000
    (1L..1_000_000L).chunked(chunkSize) { subList ->
        log.info { "현재 : ${subList.first()}" }
        val selected = jdbcTemplate.query(
            "select * from login_token where id between ${subList.first()} and ${subList.last() + 1}",
            rowMapper
        )

        selected.forEach {
            it.expiredDt = it.createdAt!!.plusDays(7).toLocalDate()
            jdbcTemplate.update("UPDATE login_token SET expired_dt = '${it.expiredDt.toString()}' WHERE id = ${it.id}")
        }
    }
}

먼저 일반 update() 메서드를 사용할 경우 4분 약간 안되는 시간이 소요되었습니다. 곧바로 batchUpdate() 도 실험해보도록 하죠

fun `JPA 버리기 - batchUpdate()`() {
    val rowMapper = RowMapper { rs, _ ->
        LoginToken(
        id = rs.getLong("id"),
        token = rs.getString("token"),
        expiredDt = LocalDate.parse(rs.getString("expired_dt")),
        createdAt = LocalDateTime.parse(rs.getString("created_at"), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
        )
    }

    val chunkSize = 1000
    (1L..1_000_000L).chunked(chunkSize) { subList ->
        log.info { "현재 : ${subList.first()}" }
        val selected = jdbcTemplate.query(
            "select * from login_token where id between ${subList.first()} and ${subList.last() + 1}",
            rowMapper
        )

        jdbcTemplate.batchUpdate(
            "UPDATE login_token SET expired_dt = ? WHERE id = ?",
            object : BatchPreparedStatementSetter {
                override fun setValues(ps: PreparedStatement, i: Int) {
                    ps.setString(1, selected[i].createdAt!!.plusDays(7).toLocalDate().toString())
                    ps.setLong(2, selected[i].id)
                }

                override fun getBatchSize(): Int {
                    return chunkSize
                }
            }
        )
    }
}

총 3분 20초가 걸렸습니다. 생각보다 크게 좋아진 느낌은 아니네요.

글이 상당히 길어지는 것 같습니다. 다음편에 남은 고민들을 다시 보도록 하시죠~!