PRO DBDATA PRO DBDATA

설정을 0으로 바꿨는데 커밋은 왜 2배만 빨라졌을까: innodb_flush_log_at_trx_commit와 binlog fsync

읽는 시간 약 16분
binlog 켜짐과 꺼짐 상태에서 innodb_flush_log_at_trx_commit 설정 1과 0의 INSERT 1만 건 소요 시간을 막대로 비교한 이미지

설정을 0으로 내렸는데 기대만큼 빨라지지 않았습니다

DMS 태스크로 데이터를 이관할 때 적재 속도를 올리려고 타깃 서버의 innodb_flush_log_at_trx_commit를 2로 바꾼 적이 있습니다. 작업이 끝난 뒤에는 다시 1로 되돌렸습니다. INSERT 처리량이 나오지 않던 다른 상황에서는 로그 디스크 관련 이벤트가 자주 발생하는 것을 보고, 커밋 부하로 인한 I/O 대기를 원인으로 의심하기도 했습니다.

그래서 이 설정은 커밋 병목을 푸는 손잡이로 인식하고 있었습니다. 그런데 MySQL 8.4.11 기본 구성에서 다시 재현해 보니 숫자가 생각과 달랐습니다. 1건마다 커밋하는 INSERT 1만 건이 설정 1에서 39.1초, 설정 0에서 17.4초가 걸렸습니다. 빨라진 건 맞지만 2.2배에 그쳤습니다. 나머지 시간은 8.4에서 기본으로 켜져 있는 바이너리 로그 쪽에 있었습니다.

포인트 원장 1만 건을 한 건씩 커밋한 조건

환경은 Windows, MySQL Community Server 8.4.11, MySQL Workbench입니다. 바이너리 로그는 켜져 있고 sync_binlog = 1, innodb_flush_method = unbuffered로, 8.4의 기본값 그대로입니다.

MySQL 8.4.11에서 autocommit, trx_commit, sync_binlog, log_bin, flush_method 값을 조회한 결과
sync_binlog 1과 log_bin 켜짐이 기본값이고, trx_commit 2는 이전 작업에서 SET GLOBAL로 바꾼 값이 남아 있던 상태입니다.

캡처의 trx_commit = 2는 측정 직전 단계에서 바꿔 둔 값이 그대로 남은 것입니다. SET GLOBAL로 바꾼 값은 서버를 재기동할 때까지 유지되고, 실제로 재기동 후에는 1로 돌아온 것을 확인했습니다. 측정 프로시저가 조건마다 값을 다시 지정했으므로 측정 결과에는 영향이 없습니다. 다만 이관 작업 뒤 원복을 빠뜨리면 서버가 이 상태로 계속 운영된다는 점을 그대로 보여 주는 화면이라 지우지 않았습니다.

테이블은 포인트 적립 원장 point_ledger입니다. 커밋 응답을 받은 기록이 사라질 수 있는지가 의미 있는 데이터라서 골랐습니다. 건수는 1만 건으로 정했는데, 커밋마다 생기는 fsync 비용이 초 단위 차이로 드러나면서도 조건 8개를 3회씩 돌릴 수 있는 크기이기 때문입니다. 값은 난수 없이 순번으로 만들었습니다. member_id는 5,000명에 고르게 퍼지고 금액은 10~500 범위를 반복합니다.

CREATE TABLE point_ledger (
  ledger_id  BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  member_id  INT         NOT NULL,
  amount     INT         NOT NULL,
  reason     VARCHAR(10) NOT NULL,
  cond_name  VARCHAR(20) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;

측정은 프로시저가 직접 합니다. INSERT 루프 전후로 performance_schema.global_status의 Innodb_os_log_fsyncs와 Innodb_os_log_written을 읽어 차이를 기록하고, 시간은 SYSDATE(6)로 잽니다. 커밋 단위가 1이면 autocommit으로 건마다 커밋하고, 100이면 100건마다 COMMIT합니다.

SET t0 = SYSDATE(6);

IF p_batch > 1 THEN START TRANSACTION; END IF;
WHILE i <= p_rows DO
  INSERT INTO point_ledger (member_id, amount, reason, cond_name)
  VALUES (1 + MOD(i * 7919, 5000), 10 * (1 + MOD(i, 50)),
          ELT(1 + MOD(i, 4), 'order', 'review', 'event', 'refund'), p_cond);
  IF p_batch > 1 AND MOD(i, p_batch) = 0 THEN
    COMMIT;
    START TRANSACTION;
  END IF;
  SET i = i + 1;
END WHILE;
IF p_batch > 1 THEN COMMIT; END IF;

INSERT INTO bench_result (cond_name, run_no, flush_setting, binlog_on, batch_size,
                          elapsed_ms, log_fsyncs, log_written_bytes)
VALUES (p_cond, v_run, @@GLOBAL.innodb_flush_log_at_trx_commit, @@SESSION.sql_log_bin,
        p_batch, TIMESTAMPDIFF(MICROSECOND, t0, SYSDATE(6)) / 1000, f1 - f0, w1 - w0);

조건마다 CALL을 손으로 수십 번 실행하려니 순서가 꼬이기 쉬워서, 설정 1·2·0을 각각 3회 돌리고 이어서 설정 1에 100건 커밋을 3회 돌리는 run_all 프로시저로 묶었습니다. binlog를 끈 조건은 같은 세션에서 sql_log_bin만 바꿔 다시 호출했습니다.

SET SESSION sql_log_bin = 1;
CALL run_all();

SET SESSION sql_log_bin = 0;
CALL run_all();

SET SESSION sql_log_bin = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

처음 프로시저를 만들 때는 DELIMITER $$ 줄이 서버로 그대로 넘어가 1064 오류가 났습니다. 다른 쿼리 툴에서는 문제없던 방식인데, Workbench에서는 블록 전체를 Ctrl+Shift+Enter로 실행해야 구분자 변경이 적용됐습니다.

여덟 가지 조건의 중앙값

SELECT cond_name, binlog_on, flush_setting, batch_size,
       elapsed_ms AS median_ms, log_fsyncs, log_written_bytes
FROM (SELECT r.*, ROW_NUMBER() OVER (PARTITION BY cond_name ORDER BY elapsed_ms) rn
      FROM bench_result r) t
WHERE rn = 2
ORDER BY binlog_on DESC, batch_size, flush_setting;
binlog 켜짐과 꺼짐, 설정 0 1 2, 커밋 단위별 중앙값 시간과 redo fsync 횟수 8행 결과
같은 1만 건이 조건에 따라 0.5초에서 39초까지 벌어졌고, redo fsync 횟수는 설정 1에서만 1만 회를 넘었습니다.
조건binlog 켜짐binlog 꺼짐
설정 1, 1건 커밋39,052 ms (fsync 13,542)20,724 ms (fsync 11,897)
설정 2, 1건 커밋19,204 ms (fsync 104)795 ms (fsync 4)
설정 0, 1건 커밋17,430 ms (fsync 37)528 ms (fsync 3)
설정 1, 100건 커밋690 ms (fsync 157)538 ms (fsync 188)

binlog를 끄자 차이가 26~39배로 벌어졌다

binlog를 끈 세션에서는 설정을 내린 만큼 빨라지는 그림이 나왔습니다. 설정 1이 20.7초였고 2는 0.79초, 0은 0.53초였습니다. 설정 1 대비 26배와 39배입니다. 반면 binlog가 켜진 상태에서는 같은 변경으로 2.0배와 2.2배밖에 줄지 않았습니다. binlog가 켜진 기본 상태에서 이 변수만 바꿀 생각이라면 26~39배가 아니라 2배 남짓을 기대치로 잡아야 합니다.

두 번의 fsync 비용이 거의 그대로 더해졌다

표에서 가장 눈에 띄는 쌍은 binlog 켜짐·설정 2(19.2초)와 binlog 꺼짐·설정 1(20.7초)입니다. 앞쪽은 커밋마다 binlog만 디스크에 강제 기록하고, 뒤쪽은 redo만 강제 기록합니다. 커밋 1건당 1.9 ms와 2.1 ms로 비용이 비슷했고, 두 값을 더하면 39.9초로 binlog 켜짐·설정 1의 39.1초와 거의 맞습니다.

즉 기본 구성의 커밋 비용은 redo fsync 절반, binlog fsync 절반으로 나뉘어 있었습니다. innodb_flush_log_at_trx_commit는 그중 redo 쪽 절반만 걷어냅니다.

fsync 횟수와 redo 쓰기량에서 본 것

설정 1의 log_fsyncs는 13,542회와 11,897회로 커밋 수 1만 회보다 많았습니다. 이 카운터는 서버 전체 값이라 백그라운드 플러시가 섞였을 수 있는데, 이번 측정으로는 초과분을 분해하지 못했습니다. 설정 0과 2는 3~104회에 그쳤습니다.

같은 1만 건인데도 redo 쓰기량은 1.4 MB(100건 커밋)에서 11.6 MB(binlog 꺼짐·설정 2)까지 차이가 났습니다. 커밋 때마다 덜 찬 로그 블록을 반복해서 다시 쓰기 때문으로 추정하지만, 블록 단위로 확인한 값은 아닙니다.

짧게 끝나는 조건은 회차 편차도 컸습니다. binlog 꺼짐·설정 2의 1회차는 1,384 ms였는데 2·3회차는 691 ms와 795 ms였습니다. 1초 미만 조건을 한 번만 재고 판단했다면 결론이 흔들렸을 수 있어서 중앙값을 썼습니다.

설정보다 커밋 단위가 먼저 효과를 냈다

설정을 1로 그대로 두고 100건마다 커밋했더니 binlog가 켜진 상태에서도 690 ms가 나왔습니다. 1건 커밋·설정 1(39.1초)보다 57배 빠르고, 내구성을 내려놓은 설정 0(17.4초)보다도 25배 빠릅니다. fsync 횟수는 157회로, 1만 건이 100번의 커밋으로 줄어든 만큼 함께 줄었습니다.

대량 적재 때 보통 1만 건 단위로 커밋해 성능을 개선해 왔는데, 이번 숫자가 그 습관의 근거가 됐습니다. 이관 속도가 문제일 때 내구성 설정보다 커밋 단위를 먼저 조정할 수 있는지부터 보는 편이 맞습니다. 커밋 단위를 키우면 트랜잭션이 길어지고 언두와 락 보유 시간도 늘어나므로 크기는 대상 테이블의 동시 사용량을 보고 정합니다. 커밋 단위를 이관 도구나 애플리케이션이 정해서 바꿀 수 없을 때가 설정 변경을 검토할 차례입니다.

INSERT 쪽 비용을 다른 각도에서 본 내용은 보조 인덱스가 대량 INSERT 비용에 주는 영향과 UUID PK와 INSERT 순서가 파일 크기에 주는 영향에 정리했습니다.

설정 0에서 mysqld를 강제 종료했는데 500건이 그대로 남았다

성능만 보면 0과 2를 고르고 싶어지지만, 이 설정은 장애 시 잃을 수 있는 데이터와 바꾸는 값입니다. 가장 불리한 설정 0에서 유실이 실제로 생기는지 확인했습니다. 초당 플러시 타이밍에 결과가 좌우되지 않도록 innodb_flush_log_at_timeout을 최댓값 2700초로 올렸습니다. 그런 다음 500건을 한 트랜잭션으로 커밋하고, 약 10초 뒤 프로세스를 강제 종료했습니다.

SET GLOBAL innodb_flush_log_at_timeout = 2700;
SET GLOBAL innodb_flush_log_at_trx_commit = 0;

INSERT INTO loss_probe (seq, setting_tag)
SELECT seq, 'F0'
FROM (WITH RECURSIVE n(seq) AS (SELECT 1 UNION ALL SELECT seq + 1 FROM n WHERE seq < 500)
      SELECT seq FROM n) x;

SELECT COUNT(*) FROM loss_probe;   -- 500

-- 약 10초 뒤 관리자 cmd
-- taskkill /F /IM mysqld.exe
-- net start MySQL84
mysqld 강제 종료 후 재기동하고 loss_probe 건수를 조회해 survived 500이 나온 화면
설정 0과 플러시 주기 2700초 상태에서 프로세스를 강제 종료했지만 커밋한 500건이 모두 남았습니다.

재기동 후 건수는 500건 그대로였습니다. 측정으로 확인된 사실은 여기까지입니다. 원인은 확인하지 않았지만 다음과 같이 추정합니다. 설정 0은 커밋 시점에 디스크 동기화를 기다리지 않을 뿐이고, 로그 내용 자체는 10초 사이에 이미 OS 파일 캐시까지 넘어가 있었을 것입니다. 프로세스만 죽은 경우에는 OS 캐시가 살아 있으므로 데이터가 남습니다. 재기동 로그의 복구 메시지는 이번에 따로 확보하지 않았습니다.

이 결과를 설정 0도 안전하다는 근거로 읽으면 안 됩니다. 확인한 것은 트랜잭션 하나, 약 10초 간격, binlog가 켜진 상태에서의 프로세스 종료라는 조건뿐입니다. 커밋할 때 binlog fsync가 함께 일어났다는 점도 결과에 영향을 줬을 수 있습니다. 0과 2가 실제로 데이터를 잃는 상황은 OS 크래시나 전원 차단처럼 OS 캐시까지 사라지는 경우이고, 이 PC에서는 재현하지 않았습니다. 커밋 직후 1초 안에 프로세스가 죽는 타이밍도 확인 범위 밖입니다.

0과 2 중에서 고른다면

이번 숫자만 보면 2와 0의 속도 차이는 작았습니다. binlog 켜짐에서 19.2초 대 17.4초, 꺼짐에서 0.79초 대 0.53초입니다. 반면 공식 문서 기준으로 2는 mysqld 프로세스 장애에서 커밋분을 지키고, 0은 그 보장까지 내려놓습니다. 이번 강제 종료에서 0이 데이터를 잃지 않은 것은 한 번의 조건에서 나온 결과일 뿐 이 차이를 뒤집는 근거는 아닙니다. 속도를 위해 내구성을 낮춰야 한다면 2를 고르고, 0을 쓸 이유는 찾기 어려웠습니다. DMS 이관 때 2를 썼던 선택도 이 기준에서 벗어나지 않습니다.

2를 쓰더라도 먼저 확인할 것이 있습니다.

  • 대상 서버에 binlog가 켜져 있고 sync_binlog = 1이라면 효과가 이번처럼 절반에 그칠 수 있습니다. sync_binlog까지 낮추면 binlog와 InnoDB 사이의 일관성, 복제 재구성 가능성까지 함께 내려놓게 되므로 같은 무게로 판단하지 않습니다.
  • 변경은 SET PERSIST가 아니라 SET GLOBAL로 하고, 작업이 끝나면 값을 다시 조회해 1로 돌아왔는지 확인합니다. 캡처 1처럼 잊힌 값은 재기동 전까지 조용히 남아 있습니다.
  • 복제 소스이거나 원장처럼 커밋 응답 후 유실이 허용되지 않는 데이터라면 작업 중이라도 1을 유지하고 커밋 단위로 속도를 냅니다.

운영 서버에서는 숫자가 이번과 다르게 나올 가능성이 큽니다. 이번 측정은 세션 하나가 커밋을 연속으로 보내는 구조라서 그룹 커밋이 fsync를 나눠 쓰는 효과가 거의 없었습니다. 동시 세션이 많은 서버에서는 커밋 하나당 fsync 비용이 줄어 설정 간 차이도 작아질 수 있습니다. 또 프로시저 내부 루프라 네트워크 왕복 시간이 빠져 있고, Windows PC 한 대와 이 디스크의 fsync 속도에 묶인 결과입니다.

DMS 이관 때 2로 바꿨던 선택을 다시 보면

MySQL 8.4 기본 구성에서 1건 커밋의 비용은 redo fsync와 binlog fsync가 거의 같은 크기로 나눠 갖고 있었습니다. innodb_flush_log_at_trx_commit를 0이나 2로 내리면 그중 redo 쪽만 사라지기 때문에 2배 남짓 빨라질 뿐입니다. 반면 설정을 1로 둔 채 커밋 단위만 100건으로 키우자 설정 0보다 25배 빨라졌습니다. DMS 이관 때 2로 바꿨던 선택도 이 숫자로 다시 보면, 타깃에 binlog가 켜져 있었다면 기대할 수 있던 효과는 2배 남짓이었습니다.

PRODB
16년 차 현직 DBA. AWS RDS for MySQL과 Aurora MySQL을 운영하며 겪은 문제를 MySQL 8.4에서 직접 재현해 기록합니다.

함께 보면 좋은 글

댓글 0

첫 댓글을 남겨보세요.

광고 차단 알림

광고 클릭 제한을 초과하여 광고가 차단되었습니다.

단시간에 반복적인 광고 클릭은 시스템에 의해 감지되며, IP가 수집되어 사이트 관리자가 확인 가능합니다.