Bulk Update, Temporary Table 성능 테스트 - Spring

Spring - Mabatis에서 temporary 테이블을 이용한 대량의 데이터를 업데이트하는 것이 본 포스팅의 목표입니다.

 

 

안녕하세요.

대량의 데이터를 업데이트할 때 효과적인 방법을 소개하고자 합니다.

MySQL의 Temporary Table를 사용하는 것인데요.

MyBatis에서 Bulk Update를 구현해야 할 때 유용합니다.

 

테스트를 통해 어느정도의 시간이 걸리는지를 확인할텐데요.

비교를 위해 Temporary Table를 사용한 Bulk Update와여러 줄의 Update 문을 실행했을 때의 속도를 비교합니다.

참고로, 이번 포스팅에 이어 다음 포스팅은 Upsert(ON DUPLICATE KEY UPDATE) 를 테스트 해볼 예정입니다.

Update + Insert 시 어떻게 하면 가장 빠를지 궁금해서 시작했습니다 ㅎㅎ

 

해당 내용과 관련해서 UPSERT와 관련된 성능 테스트를 같이 진행했는데요.

"Bulk UPSERT 성능 테스트 - Spring" 글을 참고하실 수 있습니다.

 

 

Temporary Table?

 

공식문서를 참고해서 임시 테이블을 정의하면 아래와 같습니다.

 

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

 

해석해보면 다음과 같습니다.

 

TEMPORARY 테이블은 현재 세션 내에서만 표시되며, 세션이 닫히면 자동으로 삭제됩니다.

서로 다른 세션에서 같은 임시 테이블 이름을 정의할 수 있고, 일반 테이블(non-TEMPORARY 테이블)과 동일한 이름을 정의할 수 있습니다 (충돌이 나지 않습니다). 이 때, 임시 테이블이 삭제될 때까지 이름이 동일한 기존 테이블은 숨겨집니다.

 

 

그렇다면 이 임시테이블은 언제, 왜 사용할 수 있을까요?

 

임시 테이블은 일반 테이블과 동일하게 DML(SELECT, INSERT, UPDATE, DELETE)을 사용할 수 있습니다.

또 한 세션에서만 생성되었다가 삭제되기 때문에,

일반테이블처럼 영구적으로 저장되는 테이블이 아니기 때문에 일시적으로 데이터를 처리할 때 사용할 때 유용합니다.

 

 

실제 제가 경험한 다음의 예시를 보면 훨씬 더 납득이 되실거에요.

 

대량의 데이터를 업데이트 해야하는 상황이 생깁니다.

INSERT 문과 동일하게 BULK INSERT를 사용하면 좋겠지만, UPDATE 구문은 해당 기능이 없습니다.

그래서 우회적으로 임시 테이블에 데이터를 모두 담아두고 JOIN을 걸어서 변경된 사항을 UPDATE 하는 방법을 사용하곤 합니다.

 

Pseudocode

해당 예시에 대해 간단한 Pseudocode를 작성해보았습니다.

예시의 Pseudocode이자, 해당 포스팅에서 다룰 내용의 요약본이라고도 할 수 있겠네요.

 

/* ① 원본 테이블의 구조를 가진 임시 테이블 생성 */
CREATE TEMPORARY TABLE tmp_table LIKE origin_table

/* ② 임시 테이블에 모든 데이터 저장 */
INSERT tmp_table VALUES (data1), (data2), (data3) ...

/* ③ 원본 테이블 BULK UPDATE */
UPDATE origin_table ori INNER JOIN tmp_table tmp ON ori.col1 = tmp.col1 SET ori.col2 = tmp.col2

/* ④ 임시 테이블 삭제 - maybe, not mandatory */
DROP TEMPORARY TABLE tmp_table;

 

 

② BULK INSERT 

만약 BULK INSERT에 대한 추가적인 설명이 필요하다면 'Bulk Insert, 성능 테스트'을 참고하시길 추천드립니다.

 

③ UPDATE

핵심 코드라고 할 수 있겠네요.

col1 이라는 기준 값을 기준으로 INNER JOIN을 걸어 데이터를 UPDATE 하고 있습니다. 

기준 값은 대부분 pk, 혹은 unique값이 될 수 있으며, 상황에 따라 그 어떤 reference values 이 될 수 있습니다.

 

 

④ DROP

번을 보면 임시테이블을 DROP하고 있는데, 상황에 따라 필수가 아닐 수도 있습니다. 

임시 테이블이 생성된 Session이나 Connection이 끊어지면 자동으로 삭제되기 때문입니다.

 

만약 Conntection pooling이나 Persistent connection 기반의 application 을 개발하고 있다면,

TEMPORARY TABLE Application이 종료되었을 때 자동으로 삭제된다는 것을 보장해주지 않습니다. 

 

Application이 사용하는 Database Connection이 아직 Connection pool을 재사용하기 위해 유지될 경우가 있기 때문입니다.

이러한 오류가 발생할 여지를 주기 보다 명시적으로 삭제해주면 걱정도 덜하겠죠.

 

 

Performace

TEMPORARY TABLE은 일반 테이블에 비해 SELECT문의 성능이 잘 나오지 않습니다.

그에 반해 UPDATE문의 성능은 큰 차이를 보입니다.

 

아래 성능은 참고 블로그에서 가져온 내용으로, 정리하면 아래와 같습니다.

 

아래는 Java Thread를 여러 개 발생시켜 Session(Threads)를 5개 ~ 200개까지 단계적으로 늘려 TPS를 측정한 결과입니다.

TPS, transaction per second: 초당 트랜젝션 수, 초당 처리할 수 있는 양을 의미하여 해당 수치가 높을 수록 높은 성능을 보여줍니다.

 

 

위의 그래프는 Temporary Table과 Non-Temporary Table의 Select / Update 문의 성능 차이를 테스트한 결과를 아주 잘 보여줍니다. Threads의 수가 많아질 수록 두 결과의 TPS 차이가 커지고 있는 것을 확인할 수 있습니다.

 

임시 테이블을 사용할 때 최대한 SELECT문을 피하는게 좋겠죠.

 

 

 

이제, Spring Mybatis에서 BULK UPDATE를 수행해보도록 하겠습니다.

 

 

 

Set Up

테스트를 본격적으로 실행하기 전, 미리 세팅해야할 조건들을 살펴보겠습니다.

 

Test Table

먼저 사용할 쿼리를 살펴보도록 할게요.

테이블은 아주 간단하게 정의했습니다.

 

 

 

 

Jdbc MultiQueries - Allow

기본적으로 Mybatis 에서 여러 쿼리를 수행할 수 없습니다.

만약, 여러 쿼리를 실행하고 싶다면 아래의 설정을 추가해주면 됩니다.

혹은 Multi queries를 설정하지 않고 단일 문으로 각각 호출할 수도 있습니다.

 

 

allowMultiQueries

Jdbc 설정 중 allowMultiQueries 을 true로 설정해주면 됩니다.

 

allowMultiQueries=true
# jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&allowMultiQueries=true

 

 

Create Data Set

업데이트가 될 데이터를 테이블에 저장하겠습니다.

 

@BeforeEach
void setUp() {

    // ① 테이블 초기화
    userDAO.truncate();
    List<UserVO> insertList = new ArrayList<>(TEST_SIZE);

    // ② 데이터 Type을 "Init"으로 설정해서 Update와 구별 (e.g. UserVO("UserVO078", "Init"))
    for (int i = 1; i <= TEST_SIZE; i++) {
        insertList.add(new UserVO(String.format("UserVO%03d", i), "Init"));
    }

    // ③ 데이터 저장
    int cnt = userDAO.insertInit(insertList);
}

 

내용은 주석으로 달아두었습니다.

TEST_SIZE 만큼의 데이터를 먼저 테이블에 저장하는 코드입니다.

어려운 내용은 아니니, 자세한 설명은 생략하겠습니다.

 

 

 

Update from Temporary Table

 

그럼 지금부터 Mybatis에서 임시 테이블을 사용해보겠습니다.

Test Code를 이용해서 작성을 했는데요. 각 Given, When, Then에 대한 설명을 하자면 아래와 같습니다.

 

Given : 테이블에 이미 저장된 데이터 절반을 업데이트하는 리스트 생성

10000개의 테스트 데이터를 저장 후 5000개의 데이터를 업데이트할 예정

When : 임시 테이블을 이용하여 데이터 업데이트

Then : 업데이트된 데이터가 반영된 데이터의 수와 일치

 

이제 임시 테이블을 사용한 데이터 업데이트의 테스트 코드를 확인해보겠습니다.

 

@Test
public void given_HalfOfTestSet__when_UpdateFromTempTable__then_EqualsUpdatedCount() {

        // Given : create updateList with half the size of test set.
        int UPDATE_SIZE = TEST_SIZE / 2;
        List<UserVO> updateList = new ArrayList<>(UPDATE_SIZE);

        for (int i = 1; i <= TEST_SIZE; i += 2) {
            updateList.add(new UserVO(String.format("UserVO%03d", i), "Update"));
        }
        
        // When : update bulk data using temporary table.
        double beforeTime = System.currentTimeMillis();
        
        userDAO.bulkUpdateFromTempTable(updateList);

        double afterTime = System.currentTimeMillis();


        // Then : the number of updated data should be equal to half of the test set.
        int updateUserCnt = userDAO.selectUpdateUserCnt();
        Assertions.assertEquals(updateUserCnt, UPDATE_SIZE);

        log.info("### run time : {}s", (afterTime-beforeTime) / 1000);
}

 

 

Given

updateList는 Init 데이터와의 구별을 위해 type으로 "Update"를 설정합니다.

데이터의 크기는 기존 데이터의 반으로 설정했습니다.

 

 

When

임시 테이블을 이용한 대량 업데이트입니다. 자세히 살펴보겠습니다.

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="demo.bulkUpdate">
    <insert id="bulkUpsertFromTempTable" parameterType="com.gngsn.demo.bulkUpsert.UserVO">
        CREATE TEMPORARY TABLE user_temp LIKE user;

        INSERT INTO user_temp (name, type) VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.type})
        </foreach>;

        UPDATE user u
        INNER JOIN user_temp t ON u.name = t.name
        SET u.type = t.type;

        DROP TEMPORARY TABLE user_temp;
    </insert>
</mapper>

 

조금 헷갈린다면 위의 Pseudocode를 다시 확인하시는 것을 추천드립니다.

위와 같이 적을 수도 있고, 저와 같은 경우는 재사용과 가독성을 위해 아래와 같이 적었습니다.

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="demo.bulkUpdate">
    <update id="bulkUpdateFromTempTable" parameterType="com.gngsn.demo.bulkUpsert.UserVO">
        <include refid="createTempTable"/>;

        <include refid="insertTempTable"/>;
        <include refid="updateMainTable"/>;

        <include refid="dropTempTable"/>;
    </update>
    
    <sql id="createTempTable">
        CREATE TEMPORARY TABLE user_temp LIKE user
    </sql>

    <sql id="insertTempTable">
        INSERT INTO user_temp (name, type) VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.type})
        </foreach>
    </sql>

    <sql id="updateMainTable">
        UPDATE user u
        INNER JOIN user_temp t ON u.name = t.name
        SET u.type = t.type
    </sql>

    <sql id="dropTempTable">
        DROP TEMPORARY TABLE user_temp
    </sql>
</mapper>

 

만약, 멀티라인을 허용하고 싶지 않다면 각 쿼리를 서비스 레벨에서 따로 호출할 수도 있습니다.

 

 

 

Then

 

결과는 아래와 같이 정리할 수 있습니다.

 

TEST_SIZE : 10000

UPDATE_SIZE : 5000

RUN_TIME : 0.28s

 

참고로 run time은 위의 테스트 코드에서 확인할 수 있는 (afterTime-beforeTime) / 1000 의 값입니다.

 

 

 

 

 

Calling Each at the Service 

 

Service 딴에서 각각 불러올 때와의 차이는 얼마나 날지 궁금해서 추가해본 테스트 코드입니다.

만약, MultiQueries를 허용하고 싶지 않을 때 Service에서 아래와 같이 호출할 수 있는데요.

이 때 성능 차이가 얼마나 날지 같이 측정해보겠습니다.

 

@Test
public void given_HalfOfTestSet__when_UpdateWithTempTableCallEach__then_EqualsUpdatedCount() {
        // Given : create updateList with half the size of test set.
        int UPDATE_SIZE = TEST_SIZE / 2;
        List<UserVO> updateList = new ArrayList<>(UPDATE_SIZE);

        for (int i = 1; i <= TEST_SIZE; i += 2) {
            updateList.add(new UserVO(String.format("UserVO%03d", i), "Update"));
        }


        // When : update bulk data using temporary table.
        double beforeTime = System.currentTimeMillis();
        
        userDAO.createTempTable();
        userDAO.insertTempTable(updateList);
        userDAO.updateMainTable();
        userDAO.dropTempTable();

        double afterTime = System.currentTimeMillis();


        // Then : the number of updated data should be equal to half of the test set.
        int updateUserCnt = userDAO.selectUpdateUserCnt();
        Assertions.assertEquals(updateUserCnt, UPDATE_SIZE);

        log.info("### run time : {}s", (afterTime-beforeTime) / 1000);
}

 

 

간단하게 결과만 정리하겠습니다.

TMI) 사실 Connection Pool 설정하기 시간 없어서 안하려다가 ... 

너무 궁금해서 호다닥 Hikari pool 해서 테스트 해보았습니다 ~.~ 그래서 간단히 적습니다..ㅎㅎ

 

TEST_SIZE : 10000

UPDATE_SIZE : 5000

RUN_TIME : 0.634s

 

 

0.28s 와 비교했을 때, 어느정도 차이가 보이는 걸 확인할 수 있습니다.

다양한 테스트 크기 비교는 본 포스팅 가장 하단의 Result 에서 확인할 수 있습니다.

 

 

 

Update Multiple Lines

 

이번엔 비교를 위해 여러 개의 Update 구문을 사용해보겠습니다.

마찬가지로 Test Code를 이용해서 작성을 했는데요.

각 Given, When, Then에 대한 설명을 하자면 아래와 같습니다.

 

Given : 테이블에 이미 저장된 데이터 절반을 업데이트하는 리스트 생성

10000개의 테스트 데이터를 저장 후 5000개의 데이터를 업데이트할 예정

When : 여러 줄의 UPDATE 쿼리이용하여 데이터 업데이트

Then : 업데이트된 데이터가 반영된 데이터의 수와 일치

 

코드는 아래와 같습니다.

 

@Test
public void given_updateHalfOfTestSet__when_updateMultiLine__then_equalsUpdatedCount() {

        // Given : create updateList with half the size of test set.
        int UPDATE_SIZE = TEST_SIZE / 2;
        List<UserVO> updateList = new ArrayList<>(UPDATE_SIZE);

        for (int i = 1; i <= TEST_SIZE; i += 2) {
            updateList.add(new UserVO(String.format("UserVO%03d", i), "Update"));
        }


        // When : update bulk data using temporary table.
        double beforeTime = System.currentTimeMillis();
        
        userDAO.bulkUpdateMultiLine(updateList);

        double afterTime = System.currentTimeMillis();


        // Then : the number of updated data should be equal to half of the test set.
        int updateUserCnt = userDAO.selectUpdateUserCnt();
        Assertions.assertEquals(updateUserCnt, UPDATE_SIZE);

        log.info("### run time : {}s", (afterTime-beforeTime) / 1000);
}

 

 

Given 

임시 테이블과의 동일한 독립 변인으로, 동일한 Update 테스트 셋을 생성합니다. 

 

 

When

이번엔 여러 줄의 Update 쿼리 문을 생성해 DB에 질의를 합니다.

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="demo.bulkUpdate">
    <update id="bulkUpdateMultiLine" parameterType="com.gngsn.demo.bulkUpsert.UserVO">
        <foreach collection="list" item="item" separator=";">
            UPDATE user
            SET type = #{item.type}
            WHERE name = #{item.name}
        </foreach>
    </update>
</mapper>

 

foreach를 사용해서 간단한 문장을 연결했습니다.

 

 

 

Then

 

결과는 아래와 같이 정리할 수 있습니다.

 

TEST_SIZE : 10000

UPDATE_SIZE : 5000

RUN_TIME : 3.34s 

 

참고로 run time은 위의 테스트 코드에서 확인할 수 있는 (afterTime-beforeTime) / 1000 의 값입니다.

 

 

 

 

 

 

Result

 

결과를 정리하면 아래와 같습니다.

 

 

UPDATE SIZE	Temporary Table	Temporary Table calling each	Multiple Lines	Upsert
500	0.051s	0.056s	0.39s	0.056s
2500	0.116s	0.278s	1.105s	0.24s
5000	0.28s	0.634s	3.34s	0.427s
10000	0.716s	1.317s	6.847s	0.772s

 

 

 

큰 기대없이 실행한 테스트였는데, 생각보다 큰 차이를 보여줍니다.

Upsert는 다음 포스팅에서 다룰 예정인데, 이번 포스팅에서는 참고차 넣었습니다.

Upsert는 INSERT INTO user VALUES (...), (...), ... ON DUPLICATE KEY UPDATE col2 = VALUES(col2)   형식입니다.

 

아쉬운 점은 CPU 나 Memory도 비교를 하고 싶은데 ,,, 어떤 툴을 사용할 수 있을까요 ?

추천해주시면 시간이 생길 때 추가해보겠습니다.

 

 

 

 

이상으로 Bulk Update를 하는 두 가지 방법에 대해서 다뤘습니다.

오타나 잘못된 내용은 댓글 부탁드립니다.

감사합니다 ☺️