MySQL - Binary Log Group Commit - 바이너리 로그 그룹 커밋 - Two-Phase Commit

Last Updated on 6월 26, 2024 by Jade(정현호)

안녕하세요  
이번 포스팅에서는 MySQL의 Binary Log Group Commit과 트랜잭션의 Commit과 관련된 내용을 확인해 보도록 하겠습니다.     

Two-Phase Commit(2단계 커밋)

MySQL 5.6 버전 부터 바이너리 로그가 활성화 및 sync_binlog=1 설정시에도 바이너리 로그 그룹 커밋을 사용할 수 있게 되었으며, 이는 바이너리 로그 활성화에 따른 바이너리 로그 기록과 관련하여 MySQL 서버의 쓰기 성능을 향상시키는데 중점을 두고 있습니다.

바이너리 로그 그룹 커밋은 바이너리 로그에 대한 여러 쓰기를 하나씩 작성하는 대신 그룹화하여 성능을 향상시킵니다.

바이너리 로그 그룹 커밋에 대한 내용에 앞서서 Redo 로그와 바이너리 로그가 기록되는 방식에 대해서 먼저 내용을 확인해 보도록 하겠습니다.

MySQL 5.0에서 분산 트랜잭션 및 2PC(Two Phase Commit)에 대한 지원이 도입되었습니다. 그러면서 잠시 MySQL 5.0 버전에서는 바이너리 로그 사용시 그룹 커밋이 사용이 불가능한 상황도 있었던 적이 있습니다.

MySQL서버는 클라이언트 또는 애플리케이션으로부터 COMMIT에 대한 요청을 받으면 트랜잭션에 대한 코디네이터 역할을 하게 되고, "Prepare" 와 Commit" 이라는 두 단계를 거쳐서 커밋을 하게 되고 이를 Two-Phase Commit(2 Phase Commit) 또는 XA, 분산 트랜잭션이라고 합니다.

커밋을 할 때 스토리지 엔진에 적용된 내용과 바이너리 로그에 기록된 내용간의 일관성(일치)을 유지하고, 서버의 비정상 종료(Crash)가 발생되었을 때를 인스턴스 복구(롤백관련)을 위해 사용됩니다.

[그림1]

위의 그림1은 Two-Phase Commit 프로토콜을 설명하는 그림입니다.

1. 첫 번째 단계는 MYSQL_BIN_LOG::prepare를 호출하여 각 스토리지 엔진의 리소스를 트랜잭션 커밋하고 트랜잭션이 성공하는지 확인을 하는 것입니다.
   InnoDB는 트랜잭션이 redo 로그에 완전히 기록되도록 하고 필요에 따라(필요한 경우) redo 로그 파일을 디스크에 동기화합니다.
   이 시점에서 InnoDB는 트랜잭션을 "준비(Prepared)" 되었지만 아직 커밋되지 않은 것으로 간주합니다. 지금 MySQL이 중단(Crash)되면 InnoDB는 복구 시 진행 중인 트랜잭션을 롤백합니다.
2. 다음 단계로 MySQL은 COMMIT이 요청된 트랜잭션에 대해서 바이너리 로그에 기록합니다.
3. 다음으로 스토리지 엔진은 변경 사항을 커밋 완료를 합니다.

이 시점까지 정상적으로 완료된다면 트랜잭션은 효과적으로 커밋 되고 바이너리 로그와 InnoDB 리두 로그 모두에 존재하는 상태가 되게 됩니다.

Redo 로그는 마스터 서버의 데이터에 영향을 미치고(연관이 있고), 바이너리 로그는 Slave 서버의 데이터에 영향을 미치기 때문에 Redo 로그와 바이너리 로그는 일관성이 있어야 마스터 서버와 슬레이브 서버 간의 데이터가 일관성을 유지할 수 있습니다.

그래서 Redo 로그와 바이너리 로그의 쓰기가 각각 독립적이기 때문에, 일반적인 분산 트랜잭션(XA) 시나리오로 처리되며, 그래서 일관성을 유지하기 위해서 Two-Phase Commit 를 사용하게 되는 것입니다.

즉, 트랜잭션이 스토리지 엔진에는 있지만 바이너리 로그에는 없거나 그 반대의 경우도 가능하지 않아야 합니다. Two-Phase Commit은 트랜잭션이 스토리지 엔진에서 Prepared 되었다면 서버가 비정상 종료(Crash)후 인스턴스 복구 단계에서 완전히 커밋 되거나 완전히 롤백 될 수 있도록 하여 문제를 해결합니다.

따라서 복구 시 스토리지 엔진은 Prepared 되었지만 아직 커밋 되지 않은 모든 트랜잭션에 대한 정보를 제공하고, 바이너리 로그에서 트랜잭션을 찾을 수 있는 경우 MySQL 서버는 트랜잭션을 커밋하고 그렇지 않으면 롤백을 하게 됩니다.

Two-Phase Commit 프로토콜을 기반으로 커밋의 원자성을 보장하기 위해 잠금을 추가하여 여러 트랜잭션에 대해서 두 로그의 커밋 순서가 일관되도록 하고 합니다.

이전 버전의 MySQL에서는 prepare_commit_mutex 잠금을 사용하여 트랜잭션 커밋 순서를 보장함으로써 하나의 트랜잭션이 잠금을 획득할 때 Prepared에 들어갈 수 있으며 커밋이 끝날 때까지 잠금을 해제할 수 없으며 다음 트랜잭션이 준비 작업을 계속할 수 있습니다.

순차적 일관성 문제는 이러한 잠금을 통해 완벽하게 해결되지만, 잠금이라는 것은 안정성을 높이지만 Trade off 관계로 동시성이 크면 잠금에 대한 경합에 의한 성능 저하로 이어집니다.

성능에 영향을 미치는 것에는 잠금의 경합 외에도 더 큰 영향을 미치는 지점이 있는데, 각 트랜잭션 커밋은 두 개의 fsync(디스크 쓰기)를 수행하는데, 하나는 다시 Redo 로그 용이고 다른 하나는 바이너리 용입니다.

그래서 이와 같은 Two-Phase Commit 프로세스에서 발생되는 성능 병목 현상에 대한 개선 사항으로 바이너리 로그 그룹 커밋이 도입 및 사용되고 있습니다.

관련된 파라미터는 innodb_support_xa 이며, MySQL 5.7.10 버전 부터 파라미터가 Deprecated 되었으며, innodb_support_xa=OFF(0) 으로 설정을 더 이상 허용하지 않습니다.
              

바이너리 로그 그룹 커밋

바이너리 로그 그룹 커밋은 이와 같이 쓰기 성능 저하의 문제를 해결하고자 여러 트랜잭션에 대해서 그룹으로 묶어서 커밋을 하는 기능으로 바이너리 로그 쓰기 시 여러 트랜잭션을 하나의 커밋을 단위로 처리할 수 있어서 성능이 향상됩니다.

다른 ACID 호환 데이터베이스 엔진과 마찬가지로 InnoDB는 트랜잭션이 커밋 되기 전에 트랜잭션의 리두 로그를 플러시 해야 합니다.
역사적으로 InnoDB는 각 커밋에 대해 하나의 플러시를 피하기 위해 그룹 커밋 기능을 사용하여 이러한 여러 플러시 요청을 함께 그룹화했습니다.

그룹 커밋을 통해 InnoDB는 거의 동시에 커밋 되는 여러 사용자 트랜잭션에 대한 커밋 작업을 실행하기 위해 로그 파일에 단일 쓰기를 실행하여 처리량을 크게 향상시킬 수 있습니다.

InnoDB의 그룹 커밋은 MySQL 4.x까지 작동하였으나, MySQL 5.0 버전에서 분산 트랜잭션 및 2PC(Two Phase Commit)에 대한 지원이 도입되면서 바이너리 로그 활성화 시에 그룹 커밋을 사용할 수 없게 되었습니다.

InnoDB Plugin 1.0.4 버전부터 또는 MySQL 5.1 버전부터 2PC(Two Phase Commit)이 도입되었을 때 중단되었던 그룹 커밋 기능이 MySQL의 Two Phase Commit 프로토콜과 함께 다시 사용이 가능해가능 해졌습니다.
다만 바이너리 로그에 대한 그룹 커밋은 sync_binlog=0으로 설정된 경우에만 지원되었습니다.

MySQL 5.6 버전부터는 바이너리 로그 사용 및 sync_binlog=1 일 경우에도 사용할 수 있도록 기능 추가 및 개선이 되면서, 지금의 그룹 커밋 사용 형태가 되었고 그 결과로 바이너리 로그의 fsync 횟수가 줄어들면서 sync_binlog=1 환경에서의 트랜잭션 COMMIT에 대한 쓰기 처리가 빨라지게 되었습니다.

그래서 사실상 MySQL 5.6 버전부터는 바이너리 로그 그룹 커밋을 활성화하는데 필요한 구성 설정은 별도로 필요가 없으며, 기본적으로 작동하고 있습니다.

[그림2]

바이너리 로그 그룹 커밋에서 트랜잭션의 커밋 처리 진행 중에 Prepare 이후 바이너리 로그 쓰기를 할 때 위의 이미지와 같은 세 단계를 거쳐서 수행이 되게 됩니다.

각 단계(stage)에는 처리를 위해 대기하는 대기열(queue)가 있습니다. 스레드가 빈 대기열에 첫 번째로 등록되면 해당 단계(stage)의 리더로 간주되고 그렇지 않으면 팔로워가 됩니다.

스테이지(stage) 리더는 대기열(queue)의 모든 스레드를 스테이지(stage)로 가져와 처리한 다음, 다음 단계(stage)의 대기열(queue)에 리더와 모든 팔로워를 등록합니다.

리더가 비어 있지 않은 다음 스테이지의 대기열에 등록하는 경우도 있으므로, 리더는 팔로워가 되어 대기할 수도 있지만, 팔로워는 결코 리더가 될 수 없습니다.

리더가 스테이지에 진입하면, 한 번에 전체 대기열을 가져와서 스테이지에 따라 순서대로 처리합니다. 대기열(queue)을 가져온 후에는 리더가 이전 대기열을 처리하는 동안 다른 세션들이 해당 스테이지에 등록할 수 있습니다.

위의 세 단계에 대해서 트랜잭션들은 다음과 같은 처리를 수행하게 됩니다.

1. Flush Stage
   플러시 단계에서 대기열(queue, 큐)에 등록되어 있는 모든 스레드를 바이너리 로그 캐시 기록합니다.
   

2. Sync Stage
   싱크 단계에서는 sync_binlog 설정에 따라 바이너리 로그가 디스크에 동기화됩니다. sync_binlog=1인 경우 플러시 단계(Stage)에서 플러시된 모든(전체)) 트랜잭션에 대해서 매번 디스크에 동기화 합니다.
   바이너리 로그 그룹 커밋 도입 이전에는 트랜잭션별로 커밋 요청시 매번 동기화를 수행하였다면, 바이너리 로그 그룹 커밋 기능 사용으로 트랜잭션 그룹에 대해서 동기화를 수행하게 되는 것입니다.

3. Commit Stage
   커밋 단계에서는 스테이지에 등록된 세션들이 등록한 순서대로 엔진에서 커밋 되며, 여기에서 모든 작업은 스테이지 리더가 수행합니다. 커밋 순서를 보존할 수도 있고 순서와 상관없이 병렬처리를 할 수도 있습니다.

MySQL 5.7 버전에서 바이너리 로그 그룹 커밋에 대한 추가 개선 사항으로 binlog_group_commit_sync_delay 과 binlog_group_commit_sync_no_delay_count 파라미터의 도입이 되었으며, 인위적인 쓰기 지연과 관련된 내용으로 한 번에 더 많은 트랜잭션을 바이너리 로그에 쓰기 위한 설정입니다.

해당 파라미터는 위의 세 단계 중에서 Sync Stage와 연관되어 있으며, Sync Stage에서 인위적인 지연에 따른 얼마나 많은 트랜잭션을 모아서 처리할 지에 대한 설정입니다.

binlog_group_commit_sync_delay 파라미터는 기본값 0 으로 지연이 없음을 의미하며, 즉 바로 디스크 동기화를 의미합니다.

binlog_group_commit_sync_delay 값(ms)만큼 기다리는 시간을 설정할 수 있으며 이 값을 너무 크게 설정하면 그 만큼 COMMIT에 대한 응답속도가 느려질수있으므로 주의가 필요하며, 적절한 값의 설정을 통해 응답속도를 어느정도 희생하는 대신에 트랜잭션의 처리량과 더 적은 fsync 호출에 목적이 있습니다.

데이터베이스를 사용하는 애플리케이션의 특성이나 사용 패턴과 사용하고 있는 시스템의 Disk 성능 또는 IOPS 성능에 따라서 binlog_group_commit_sync_delay의 값을 크게 하여도 처리량이 향상되지 않을 수도 있습니다. 이럴 경우 COMMIT의 응답속도를 희생하면서 해당 파라미터를 값을 무리하게 크게 증가할 필요는 없을 것으로 생각됩니다.

Commit Stage에서 binlog_order_commits 파라미터의 설정에 따라서 커밋 순서를 보존할 수도 있고 순서와 상관없이 병렬처리를 할 수도 있습니다.

기본값인 1로 사용할 경우 순서를 보장하여 바이너리 로그에 기록된 순서대로 스토리지 엔진이 커밋 되고, 0으로 설정시에는 바이너리 로그에 기록된 순서와 상관없이 병렬로 처리합니다.

추가적으로 연관되어 Master서버와 MultiThread 복제 기능을 사용하는 Replica(Slave)서버간의 트랜잭션 기록 순서가 동일하게 유지되게 하려면 Replica 서버에서 slave_preserve_commit_order=1 로 설정을 해야 합니다.
이 파라미터를 활성화하면 Master서버에서 트랜잭션이 커밋된 순서가 Replica(Slave)에서 유지됩니다.
                 

Reference

Reference URL
• Binary Log Group Commit in MySQL 5.6
• mysql.com/innodb-plugin-1.0-en.a4.pdf
• mysql.com/innodb_support_xa
• mysql.com/binlog_group_commit_sync_delay
• percona.com/what-is-innodb_support_xa
• percona.com/group-commit-and-real-fsync
• itpub.net/crash-safe-resolved


연관되어 이어지는 글

Multi-Threaded Replication - 병렬 복제 - 멀티 스레드 리플리케이션

MySQL의 Multi-Threaded Replication(병렬 복제) 에 대해서 내용으로 MySQL 5.7과 MySQL 8.0 기준으로 작성되어 있습니다.

연관된 다른 글

MySQL Semi-Sync 복제 환경에서의 Failover시 주의할 점 - HA 솔루션과 같이 사용할 경우

포스팅 글에서는 MySQL의 Semi-Sync 복제 환경에서 Source 데이터베이스의 장애 발생에 따른 장애조치(Failover) 상황과 관련된 내용을 다루고 있습니다.

MySQL 8.0.30 Dynamic InnoDB Redo Log

안녕하세요 이번 포스팅에서는 MySQL 8.0.30 에서 추가된 온라인 중에 Redo Log 크기 변경에 관련된 “Dynamic InnoDB Redo Log” 와 추가로 변경된 점에 대해서 살펴보려고 합니다.

MySQL - 트랜잭션 과 잠금 (1) - Real MySQL 8.0 - MySQL Transaction

안녕하세요 이번 포스팅은 MySQL 의 트랜잭션 과 잠금(Lock) 에 대해서 확인 해보려고 합니다. 전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용 으로 아래 포스팅에서 이어지는 글 입니다. 

MySQL - Double Write Buffer(이중 쓰기 버퍼)

안녕하세요 이번에는 MySQL 의 InnoDB 스토리지 엔진의 Double Write Buffer(이중 쓰기 버퍼) 기능에 대해서 확인 해보도록 하겠습니다. Real MySQL 8.0 책을 정리 및 MySQL Document 를 참조 하였습니다.

MySQL 과 Timestamp 그리고 Y2K38 이슈 - 2038년 이슈

안녕하세요 MySQL 의 Timestamp 날짜 데이터 타입에 대한 내용과 Unix Timestamp 그리고 Y2K38 이슈(2038년 이슈)에 관한 글을 핀다 회사 기술 블로그에 작성하였습니다.

MySQL - 8.0.33 AUTO_INCREMENT Bug105092 Fix 내용

안녕하세요 이번 포스팅에서는 MySQL 버전 8.0.33 Fixed 에 추가된 AUTO_INCREMENT과 관련된 Bug105092 에 대해서 확인해보려고 합니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io