트랜잭션 단위 조절을 통한 INSERT 쿼리 튜닝

이명우·2024년 3월 24일

쿼리 튜닝, MySQL

목록 보기

15/15

이전 포스팅에서 대량 INSERT 문을 튜닝하는 방법에 대해서 알아보았다. 또 Lock과 트랜잭션, 그리고 트랜잭션 격리 수준에 대해서 포스팅한 적이 있는데 이를 적용한 실습을 진행하지 않았으며, INSERT 튜닝에서도 다루지 않았기 때문에 트랜잭션 단위를 조절하여 INSERT 튜닝을 해보려고 한다.

트랜잭션과 INSERT의 성능 관계

예전 포스팅에서 트랜잭션에 대해 알아본 바 있는데, 트랜잭션은 반드시 커밋 혹은 롤백이 이루어져야한다. 이 중에서 커밋을 완료한 경우에 대해 성능 비교를 하는 것이 의미 있을 것이다. 커밋은 트랜잭션이 성공적으로 완료되었음을 DBMS에 알리는 것으로, 이때 모든 변경사항이 영구적으로 저장되고, 실패할 경우 롤백될 수 있는 시점을 의미한다. 보통 커밋을 할 경우 다음과 같은 작업이 발생한다.

커밋

데이터 변경의 영구 저장: 커밋 시 메모리에 있는 변경사항들이 디스크에 저장되어, 데이터베이스에 영구적으로 반영되며 이 과정에서 트랜잭션 로그에도 해당 변경사항이 기록됨
로깅 : Redo 로그와 Undo로그 등 커밋이 완료될 경우 각종 로그 파일에 로그를 기록
동시성 제어 및 락 해제: 커밋이 성공적으로 완료되면, 락이 해제되어 다른 트랜잭션이 해당 데이터 항목에 접근할 수 있게 됨

커밋을 할 때마다 위 작업이 동반되기 때문에, 당연히 커밋이 많아질 경우 DML 쿼리의 성능 또한 나빠보일 수밖에 없다.

커밋 혹은 롤백은 트랜잭션 한 번마다 각각 한 번싹 발생한다. 따라서 상황에 맞게 트랜잭션의 단위를 조절해가며 커밋의 수를 적절하게 맞추면 쿼리 튜닝이 가능할 것이다.

예시를 통해서 INSERT 쿼리를 트랜잭션의 단위를 조절해서 튜닝해보겠다.

예시

각각 100만개의 레코드를 삽입하고, 커밋 단위(10000100, 10000010)를 다르게 해서 실행 속도를 비교해보게겠다.
프로시저 함수를 실행할 때마다 autocommit 옵션은 껐다가 작업을 완료하면서 키는 방식으로 진행하였다.

커밋 단위 10000개 100번*

DELIMITER $$

CREATE PROCEDURE insertBatch1000x100()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;
    
    -- autocommit을 비활성화하여 시작
    SET autocommit = 0;
    
    -- 총 100번 반복
    WHILE i < 100 DO
        START TRANSACTION;
        -- 각 반복마다 10,000개의 항목을 삽입
        SET j = 0;
        WHILE j < 10000 DO
            -- test_t1 테이블에 데이터 삽입 로직
            INSERT INTO test_t1 (c1, c2, c3) 
            VALUES (CONCAT('Value ', i * 10000 + j), CONCAT('C2 ', i * 10000 + j), CONCAT('C3 ', i * 10000 + j));
            SET j = j + 1;
        END WHILE;
        -- 삽입 후 커밋
        COMMIT;
        SET i = i + 1;
    END WHILE;
    
    -- autocommit을 다시 활성화
    SET autocommit = 1;
END$$

DELIMITER ;

결과

실행 속도 : 7.70초

커밋 단위 100000개 10번*

DELIMITER //

DELIMITER $$

CREATE PROCEDURE insertBatch1000x10()
BEGIN
    -- autocommit을 비활성화하여 시작
    SET autocommit = 0;
    -- 총 10번 반복
    FOR i IN 1..10 DO
        -- 각 반복마다 1,000개의 항목을 삽입
        START TRANSACTION;
        FOR j IN 1..1000 DO
            -- 데이터 삽입 로직
            -- 예: INSERT INTO your_table (column1, column2, ...) VALUES (value1, value2, ...);
        END FOR;
        -- 삽입 후 커밋
        COMMIT;
    END FOR;
    -- autocommit을 다시 활성화
    SET autocommit = 1;
END$$

DELIMITER ;

결과

실행속도 : 7.63초

실행 속도가 약 0.07초 차이가 났다. 생각보다 유의미한 차이가 나지 않았는데, 내가 설계한 테스트가 적절하지 못했을 수도 있기 때문에 이번에는 총 1000만개의 데이터를 100번과 1000번의 트랜잭션으로 나누어 삽입해보겠다.

10000*1000

100000*100

이번에도 실행속도상 유의미한 차이는 발생하지 않았다.

redo 로깅 재활성화 후 1000만건 INSERT 실행 속도 비교

위 결과를 이해하기 어려워서 각종 상태 변수를 체크하다가, redo 로깅 변수가 disable 되어있는 것을 확인할 수 있었다. 그래서 이를 활성화시키고 다시 비교해보았다.

redo 로깅 활성화 후 100001000건*

실행 속도 : 1분 34.44초

redo 로깅 활성화 후 100000100건*

실행 속도 : 1분 30.08초

이번에는 약 4초 정도의 실행 속도 차이가 발생하는 것을 확인할 수 있다. 전체적으로 실행 속도가 증가하기도 하면서, 커밋의 단위가 더 큰 쪽이 빠른 성능을 보였는데, 이는 redo 로깅 활성화에 따라 발생한 것으로 보인다.

마무리

결국 커밋의 횟수를 조절하면 미세하게나마 튜닝이 되는 것을 확인할 수 있었다. 다만 DB 서버의 CPU나 메모리 상황 등을 고려하지 않고 조절한 단위이기 때문에 실제 상황에서는 이를 고려한 커밋 단위 조절이 필요할 것으로 보인다. 또 인덱스가 생성되어있는 테이블에서 실행했을 경우 커밋의 단위에 따라 어떤 변화가 있을지 확인해볼 필요도 있어보인다. 결국 커밋이 될 때마다 어떤 작업이 동반되느냐가 실행 속도 차이를 가르는데, 이번 포스팅 같은 경우에는 Redo 로깅이 추가됨으로 인해 조금 더 실행 속도에 차이가 나는 것을 확인할 수 있었다.

참고

이명우

백엔드 개발자

이전 포스트

트랜잭션 단위 조절을 통한 INSERT 쿼리 튜닝

쿼리 튜닝, MySQL

트랜잭션과 INSERT의 성능 관계

커밋