[MySQL] Group Commit & 2Phase Commit

■ DBMS에서 커밋(Commit)이란??

COMMIT 문은 관계형 데이터베이스 관리 시스템(RDBMS)에서 트랜잭션을 종료하고 다른 사용자에게 변경된 모든 사항을 보이도록 만드는 명령문입니다. 일반적으로 트랜잭션 종료시 해당 업데이트를 확정한다는 의미에서 "commit"이라고 합니다. 반대로 업데이트를 취소 처리를 롤백 (ROLLBACK)이라고 하며, 이러한 제어를 약속 제어라고 부르기도합니다. SQL에서는 ROLLBACK 문이 그 처리를 담당합니다.

 

SQL에서 COMMIT은 RDBMS 내에 있는 데이터베이스 트랜잭션을 종결시키고, 모든 변화를 다른 사용자들이 볼 수 있게 합니다. 일반적인 포맷은 BEGIN WORK 구문으로 시작하여, COMMIT 구문이 나오게 됩니다. 다른 방법으로 ROLLBACK 구문으로 시작될 수 있으며, 이것은 BEGIN WORK로 시작된 모든 작업을 그 이전으로 되돌리게 됩니다.

 

이렇게 중요한 Commit작업은 DBMS입장에서는 굉장이 중요한 작업이기도 하면서 동시에 부하가 큰 작업중에 하나이기도 합니다. 이렇게 부하가 큰 작업을 매 트랜잭션마다 하나 하나 작업을 하게 된다면 부하가 엄청날 것입니다. 그래서 이 Commit을 좀더 효율적으로, 모아서 한번에 작업을 하면 어떨까 해서 나오게 된 방법이 바로 그룹 커밋입니다.

 

그러나 이런 Group Commit(그룹커멋)에도 치명적인 문제가 있습니다. 만약 트랜잭션을 모아 한번에 커밋도중 시스템에 문제가 생긴다면 현제 커밋중인 트랜잭션의 데이터는 어떻게 보장해야 할까요?? 이 문제를 해결하는 것이 바로 2단계 커밋(2 phase commit)입니다. 그래서 보통 그룹커밋과 2단계 커밋을 동시에 다루게 됩니다. 트랜잭션에서 중요한 이 중요한 2가지에 대해서 알아보겠습니다.

 

■ 그룹 커밋(Group Commit)의 이론 및 컨셉

그룹 커밋은 보통 여객선 수송에 대해서 예제로 많이 다룹니다. 여기서도 여객선을 예제로 분석을 해보고 실제로 어떻게 수행이 되는지 알아보도록 하겠습니다. 또한 그룹 커밋은 내부적으로 2단계 커밋(2 Phase Commit) 방법이 필요합니다.

 

항구에 여객선이 있습니다. 이 여객선은 A 지점에서 줄을 서 있는 다음 승객을 태우고 B 지점으로 이동합니다. A와 B 사이의 여행은 왕복 약 10분이 소요됩니다. A지점에서 B지점으로 이동하는 시간동안 A지점에는 승객이 여러명 모이고 있을 것입니다. 여객선이 A지점에 다시 도착하면 줄을 서 있는 바로 다음 승객만 태웁니다. 그리고 다시 B지점으로 승객을 이동시킵니다. 이것은 여객선이 A지점으로 도착했을 때 그 시점에 있는 사람들만 태우는 것입니다.

 

위의 방법을 좀더 효율적으로 바꾸어 보겠습니다. 여객선이 A 지점에서 모든 승객을 태운 다음 B 지점으로 이송합니다. 새로운 승객을 태우기 위해 A지점으로 돌아올 때 그순간만이 아니라 A지점으로 가는 모든 사람들을 모아 B지점으로 옮겨줍니다. 이렇게 하게 되면 10분이나 걸리는 왕복 횟수를 줄일 수 있고 효율적으로 이동할 수 있습니다.

 

위의 방법을 조금 더 효율적으로 바꾸어 보겠습니다. 여객선이 대기 중인 승객을 데리러 A 지점에 다시 도착하면 새로운 승객이 도착할 가능성이 있다는 것을 알고 조금 더 기다리도록 구성할 수 있습니다. A 지점과 B 지점 간의 이동 시간이 우리는 이미 10분이라는 것을 알고 있다면 출발하기 전에 A 지점에서 추가로 30초를 더 기다리지 수 있지 않을까요? 이렇게 하면 왕복 여행을 줄이고 운송할 수 있는 전체 승객 수를 늘릴 수 있습니다.

 

 

■ 그룹 커밋 및 2phase commit(2단계 커밋) 설명

위의 예제에 대한 내용에 대해 그림으로 좀더 자세히 알아보겠습니다.

 

▶ 플러시(FLUSH) 단계

1. 트랜잭션 그룹이 하나, 둘 쌓이기 시작합니다.(그림 1,2번째)

2. 먼저 대기열에서 트랜잭션 그룹을 가져옵니다.(그림 3번째)

3. Redo 로그의 준비 단계에서 데이터를 플러시합니다(그림 3번째에서 Flush Redo Log 단계).

4. binlog 데이터를 파일에 쓰는 것은 물론, 이때는 파일 시스템의 버퍼에만 기록되며 데이터베이스가 충돌할 때 binlog가 손실될수도 있습니다.(그림 4번째의 binlog 쓰기 단계).

5. Flush 단계 대기열의 역할은 Redo 로그의 그룹 커밋을 제공하는 것입니다.(그림 5번째)

이 단계가 완료된 후 데이터베이스가 충돌하는 경우 코디네이터(조정자) binlog가 트랜잭션 그룹의 레코드를 가지고 있다고 보장하지 않기 때문에 MySQL은 재시작 후 트랜잭션 그룹을 롤백할 수 있습니다.

 

▶ 동기화(SYNC) 단계

 

1. 트랜잭션들이 Flush Redo log로 인해 Binlog로 쌓이게 됩니다.(그림 1,2)

2. 여기서, 트랜잭션 그룹의 트랜잭션 수를 늘리고 디스크 비용을 개선하기 위해 MySQL은 두 개의 매개변수를 사용하여 대기열 트랜잭션 그룹을 가져오는 시기를 제어합니다.

- Binlog_group_commit_sync_delay=N : N μs 대기 후 트랜잭션 플러시 시작(그림 3의 Sync BINLOG 단계)

- Binlog_group_commit_sync_no_delay_count=N : 대기열의 트랜잭션 수가 N에 도달하면 binlog_group_commit_sync_delay 설정을 무시하고 직접 플러시 시작(그림 4의 Sync BINLOG 단계)

3. Sync 단계에서 큐의 역할은 binlog의 그룹 제출을 지원하는 것입니다.

4. 이 단계가 완료된 후 데이터베이스가 충돌하면 코디네이터 binlog에 이미 트랜잭션 레코드가 있으므로 MySQL은 플러시된 데이터를 통해 트랜잭션을 계속합니다.

재시작 후 Flush 단계에서 Redo 로그를 제출합니다.

 

 

 

 

 

▶ 커밋(COMMIT) 단계

1. 먼저 대기열에서 트랜잭션 그룹을 가져옵니다.

2. 엔진 계층의 REDO LOG에 있는 Redo Log 트랜잭션 제출(그림 1의 InnoDB COMMIT 단계)

3. 위에서 언급한 것처럼 COMMIT 단계는 브러시할 필요가 없으며, 플러시 단계의 Redo 로그 브러시만 있으면 데이터베이스가 충돌할 때 데이터가 안전합니다.(그림 2)

4. COMMIT 단계의 역할은 SYNC 단계에서 트랜잭션을 수행하고 최종 엔진 커밋을 완료하여 SYNC가 가능한 한 빨리 다음 트랜잭션 세트를 처리하고 그룹의 효율성을 극대화할 수 있도록 하는 것입니다.(그림 3)

* 브러시 - Redo Log에서 Binlog로 동기화 하는 과정

 

 

■ 2 phase commit(2단계 커밋) 의 내부 작동 설명

위에서 그룹커밋과 동시에 2단계 커밋을 이용해서 데이터가 어떻게 Binlog 및 엔진에 커밋되는지에 대해 알아보았습니다.

이제 2단계 커밋에 대해서 내부적으로 어떻게 작동되는지에 대해 좀더 알아보도록 하겠습니다.

 

▶ 2단계 커밋 내부 작동 설명

서버가 트랜잭션을 실행할 때 서버는 커넥션별 트랜잭션 캐시에서 트랜잭션에 의해 수행된 변경 사항을 수집합니다. 명령문(STATEMENT) 기반 복제를 사용하는 경우 명령문은 트랜잭션 캐시에 기록되고 로우(ROW) 기반 복제를 사용하는 경우 변경된 실제 행이 트랜잭션 캐시에 기록됩니다. 트랜잭션이 커밋되면 트랜잭션 캐시가 하나의 단일 블록으로 바이너리 로그에 기록됩니다. 이렇게 하면 각 세션이 서로 독립적으로 실행될 수 있으며 트랜잭션 데이터를 기록할 때 바이너리 로그만 잠그면 됩니다. 트랜잭션은 서로 격리되어 있으므로 커밋시 트랜잭션을 직렬화합니다.

 

그림 1. 2단계 커밋 프로토콜(2PC)

 

스토리지 엔진과 바이너리 로그를 동기화된 상태로 유지하기 위해 서버는 그림 1에서 볼 수 있는 2단계 커밋 프로토콜(또는 2PC)을 사용합니다. 보시다시피 다이어그램에서 write() 및 fsync()에 대해 한번의 호출을 하게 됩니다.

2단계 커밋 프로토콜을 사용하는 전체적인 목적은 서버가 준비 후 충돌하고 이후에 복구되는 경우에도 트랜잭션이 엔진과 바이너리 로그(또는 둘 다)에 있음을 보증하는 것입니다. 즉, 트랜잭션이 엔진에 있지만 바이너리 로그에는 없는 경우 또는 그 반대의 경우도 가능하지 않아야 합니다. 2단계 커밋은 트랜잭션이 엔진에서 준비되면 서버가 충돌하고 복구하는 경우에도 완전히 커밋되거나 완전히 롤백될 수 있도록 요구함으로써 이 문제를 해결합니다. 따라서 복구 시 스토리지 엔진은 준비되었지만 아직 커밋되지 않은 모든 트랜잭션을 서버에 제공하고 서버는 바이너리 로그에서 트랜잭션을 찾을 수 있으면 트랜잭션을 커밋하고 그렇지 않으면 롤백합니다.

 

그러나 이것은 트랜잭션이 엔진에서 트랜잭션을 커밋하기 전에 바이너리 로그에서 지속성을 보장할 수 있을 때만 가능합니다. 디스크가 느리고 메모리가 빠르기 때문에 운영 체제는 디스크에 직접 쓰는 대신 파일의 일부를 메모리에 보관함으로써 성능을 향상시키려고 합니다. 충분한 변경 사항이 기록되거나 메모리가 다른 용도로 필요하게 되면 변경 사항이 디스크에 기록됩니다. 이것은 운영 체제와 컴퓨터를 사용하는 모든 사람에게 좋지만, 서버가 충돌할 경우 트랜잭션이 저장 엔진에서 커밋될 수 있지만 바이너리 로그에는 추적되지 않기 때문에 데이터베이스 서버에 문제를 일으킵니다.

 

따라서 복구가 제대로 작동하려면 파일이 실제로 디스크에 있는지 확인해야 합니다. 그래서 그림 1에서 fsync()를 호출하여 파일의 인메모리 부분을 디스크에 기록해야 합니다.

 

▶ prepare_commit_mutex의 동작과정

그림 2. 커밋되지 않은 트랜잭션 누락

 

서버가 복구되면 바이너리 로그에 액세스할 수 있으므로 커밋할 항목과 롤백할 항목을 결정할 수 있지만 바이너리 로그가 없으면 어떻게 될까요?

일반적인 경우 복구는 준비된 모든 트랜잭션을 롤백하고 다시 시작할 수 있습니다. 결국, 방금 준비되었지만 커밋되지 않은 트랜잭션은 롤백하는 것이 안전합니다.

그들은 단지 트랜잭션을 시작하기 직전의 상태로 데이터베이스를 이동합니다.

연결중인 클라이언트에는 트랜잭션이 커밋되었다는 표시가 없으므로 트랜잭션을 다시 실행해야 함을 알게됩니다.

 

이러한 방식으로 복구가 사용되는 또 다른 경우가 있는데, InnoDB Hot Backup(MySQL Enterprise Backup에서 사용됨)과 같은 온라인 백업 방법을 사용할 때입니다.

이러한 도구는 데이터베이스 파일 및 InnoDB 트랜잭션 로그의 복사본을 직접 가져옵니다. 이는 백업을 수행하는 쉬운 방법이지만 트랜잭션 로그에는 방금 준비된 트랜잭션이 포함됩니다.

복구시 모든 트랜잭션을 롤백하고 일관된 상태의 데이터베이스를 가지게 됩니다.

 

이러한 온라인 백업 방법은 새 슬레이브를 부트스트랩하는데 자주 사용되기 때문에 마지막으로 커밋된 트랜잭션의 바이너리 로그 위치가 InnoDB redo 로그의 헤더에 기록됩니다.

복구 시도시 복구 프로그램은 마지막으로 커밋된 트랜잭션의 바이너리 로그 위치를 알려주고 이 정보를 CHANGE MASTER 명령과 함께 사용하여 올바른 위치에서 복제를 시작할 수 있습니다.

이것이 올바르게 작동하려면 모든 트랜잭션이 바이너리 로그에 기록되는 것과 동일한 순서로 커밋되어야 합니다.

그렇지 않은 경우 일부 트랜잭션이 바이너리 로그에 기록되지만 아직 커밋되지 않은 “구멍들(Holes)”이 있을 수 있으며 이로 인해 슬레이브가 커밋되지 않은 트랜잭션을 놓치게 됩니다.

발생할 수 있는 문제가 있는 경우는 아래 그림 3과 같습니다.

 

그림 3. 병렬 커밋

 

복제가 마지막 커밋된 위치에서 시작되는 그림 2에서 이에 대한 예를 볼 수 있지만 방금 준비된 트랜잭션(Prepared Transaction)이 있으므로 백업이 슬레이브에서 복원될 때 롤백되었습니다.

이를 해결하기 위해 InnoDB는 트랜잭션을 준비할때 가져오고 트랜잭션을 커밋할때 해제되는 prepare_commit_mutex라는 뮤텍스를 추가했습니다.

이것은 문제에 대한 간단한 해결책이지만 몇분 안에 해결될 몇 가지 문제를 일으킵니다. 기본적으로 prepare_commit_mutex는 호출 순서를 그림 4와 같게 하여 문제를 해결합니다.

 

그림 4. 시퀀싱 트랜잭션

 

▶ 트랜잭션 및 바이너리 로그 동기화

디스크 쓰기가 느리기 때문에 모든 트랜잭션을 디스크에 쓰는 것은 성능에 상당히 많은 영향을 미칩니다.

이를 처리하기 위해 바이너리 로그가 디스크에 기록되어야 하는 빈도로 설정할 수 있는 서버 옵션 sync_binlog가 있습니다. 0으로 설정하면 운영 체제가 파일 페이지를 디스크에 기록해야 하는 시기를 결정하고 1로 설정하면 모든 트랜잭션이 바이너리 로그에 기록된 후에 fsync()가 호출됩니다. 일반적으로 sync_binlog를 N으로 설정하면 최대 N-1개의 트랜잭션을 잃을 수 있으므로 실제로는 두 가지 유용한 설정만 있습니다. sync_binlog=1은 트랜잭션 손실을 전혀 허용하지 않음을 의미합니다. 물론 중간에 무언가를 얻기 위해 다른 값으로 설정할 수 있지만 실제로는 트랜잭션 손실을 처리하거나 처리하지 않을 수 있습니다.

 

성능을 향상시키기 위해 일반적인 경우는 각 동기화와 함께 많은 쓰기를 묶는 것입니다. 이것이 운영 체제가 하는 일이며 DBA가 할 수 있어야 하는 일입니다. 그러나 그림 4를 보면 여러 트랜잭션이 동시에 디스크에 기록되도록 해당 시퀀스에서 fsync() 호출을 배치할 방법이 없음을 알 수 있습니다. 이유는 해당 시퀀스의 어느 지점에서든 최대 하나의 준비되고 작성된 트랜잭션이 있기 때문입니다. 그러나 그림 3으로 돌아가면 표시된 대로 fsync()를 배치하고 동시에 여러 트랜잭션을 디스크에 쓰는 것이 가능하다는 것을 알 수 있습니다. 가능하다면 fsync() 호출 전에 바이너리 로그에 기록된 모든 트랜잭션은 한 번에 디스크에 기록됩니다. 그러나 이것은 prepare_commit_mutex를 사용하지 않고 쓰기와 동일한 순서로 커밋을 정렬해야함을 의미합니다.

 

▶ 동작방법

사용된 바이너리 로그 그룹 커밋 구현은 그림 5에서 볼 수 있듯이 커밋 절차를 여러 단계로 분할합니다. 이 단계는 전적으로 바이너리 로그 커밋 절차 내부에 있으며 다른 어떤 것에도 영향을 미치지 않습니다. 이론적으로 커밋 순서 지정을 위한 다른 정책을 사용하여 다른 복제 구현을 수행하는 것이 가능합니다. 커밋 절차가 여러 단계로 분리되어 있기 때문에 여러 스레드가 동시에 트랜잭션을 처리할 수 있어 처리량도 향상됩니다.

 

그림 5. 커밋 절차 단계

 

각 단계에는 세션이 처리를 위해 대기하는 입력 대기열이 있습니다. 스레드가 빈 대기열에 등록되면 스테이지 리더로 간주되고 그렇지 않으면 세션이 팔로워가 됩니다.

스테이지 리더는 큐에 있는 모든 스레드를 스테이지를 통해 가져온 다음 다음 스테이지를 위해 리더와 모든 팔로워를 등록합니다.

팔로워는 옆으로 이동하여 리더가 전체 커밋이 완료되었다는 신호를 기다립니다. 리더가 비어 있지 않은 대기열에 등록할 수 있으므로 리더도 팔로워가 되기로 결정하고 대기를 떠날 수 있지만 팔로워는 결코 리더가 될 수 없습니다. 리더가 스테이지에 입장하면 한번에 전체 큐를 잡아 스테이지에 따라 순서대로 처리합니다. 대기열이 확보된 후 리더가 이전 대기열을 처리하는 동안 다른 세션이 스테이지에 등록할 수 있습니다.

 

플러시 단계에서 등록된 모든 스레드의 캐시는 바이너리 로그에 기록됩니다. 모든 트랜잭션이 내부 I/O 캐시에 기록되기 때문에 스테이지의 마지막 부분은 메모리 캐시를 디스크에 쓰는 것입니다(즉, 메모리의 파일 페이지에도 기록됨).

 

동기화 단계에서 바이너리 로그는 sync_binlog의 설정에 따라 디스크에 동기화됩니다. sync_binlog=1이면 플러시 단계에서 플러시된 모든 세션이 매번 디스크에 동기화됩니다.

 

커밋 단계에서는 해당 단계에 등록된 세션이 등록된 순서대로 엔진에 커밋되며 모든 작업은 스테이지 리더가 수행합니다. 커밋 절차의 각 단계에서 순서가 유지되기 때문에 쓰기와 커밋이 같은 순서로 수행됩니다.

 

커밋 단계가 실행을 완료한 후 커밋 단계에 대한 대기열에 있던 모든 스레드는 완료된 것으로 표시되고 계속할 수 있다는 신호를 받습니다. 그런 다음 각 세션은 커밋 절차에서 반환되어 계속 실행됩니다.

 

리더가 다음 큐에 등록하고 팔로워가 될 준비가 되었기 때문에 가장 느린 단계가 가장 많은 작업을 축적하게 됩니다. 이것은 일반적으로 적어도 일반 하드 디스크의 경우 동기화 단계입니다.

하지만 플러시 단계를 최대한 많은 트랜잭션으로 채우는 것이 중요하므로 플러시 단계는 조금 특별하게 취급됩니다.

 

플러시 단계에서 리더는 플러시 대기열(플러시 단계의 입력 대기열)에서 세션을 하나씩 훑어볼 것입니다. 마지막 세션이 대기열에서 제거되지 않았거나 첫 번째 세션이 binlog_max_flush_queue_time 마이크로초 전에 대기열에서 해제된 한 이 프로세스는 계속됩니다. 프로세스를 중지할 수 있는 두 가지 조건이 있습니다.

 

대기열이 비어 있으면 리더는 즉시 다음 단계로 진행하고 처리된 모든 세션을 동기화 단계 대기열에 등록합니다.

시간 초과에 도달하면 전체 대기열이 캡처되고 세션 트랜잭션 캐시가 플러시됩니다(이전과 같이). 그런 다음 리더는 동기화 단계로 진행합니다.

 

 

■ Group Commit 내구성과 설정 방법 및 모니터링 방법

일반적인 Commit은 디스크 작업이 많이 수행되는 즉 비용이 많이 드는 작업입니다. 이 Group Commit을 이용하여 작업 비용을 줄여서 성능 향상이 되도록 할 수 있어야 합니다.

 

▶ 내구성

ACID 용어에서 "D"는 내구성을 나타냅니다. 그룹 커밋으로 내구성을 보장하려면 파라미터를 innodb_flush_log_at_trx_commit=1과 sync_binlog=1로 설정해야 합니다. 이러한 설정은 서버에 문제(Crash)가 발생하는 경우 문제가 발생하기 전에 커밋된 트랜잭션이 문제 복구 후에도 데이터베이스에 계속 존재하도록 하는데 필요합니다.

 

• 내구성 있는 InnoDB 데이터 및 바이너리 로그

innodb_flush_log_at_trx_commit=1 및 sync_binlog=1을 모두 설정하면 충돌 후 최고의 내구성과 복제 일관성 보장을 제공합니다.

 

• 내구성이 없는 InnoDB 데이터

sync_binlog=1이 설정되어 있지만 innodb_flush_log_at_trx_commit이 1 또는 3으로 설정되지 않은 경우에는 문제발생 후 서버의 바이너리 로그에 있는 트랜잭션이 서버의 InnoDB 리두 로그에서 누락된 상태로 끝날 수 있습니다. 서버가 복제 마스터인 경우 슬레이브가 마스터의 로컬 InnoDB 데이터에 더 이상 존재하지 않는 마스터의 바이너리 로그에서 트랜잭션을 복제했을 수 있기 때문에 서버가 슬레이브와 일치하지 않을 수 있음을 의미합니다.

 

• 비영구 바이너리 로그

innodb_flush_log_at_trx_commit이 1 또는 3으로 설정되고 sync_binlog=0인 경우, 충돌 후 서버의 InnoDB 리두 로그에 있는 트랜잭션이 서버의 바이너리 로그에서 누락된 상태로 끝날 수 있습니다. 서버가 복제 마스터인 경우 서버의 슬레이브가 서버의 바이너리 로그에서 누락된 트랜잭션을 복제할 수 없기 때문에 서버가 슬레이브와 일치하지 않을 수 있음을 의미합니다.

 

• 비 내구성 InnoDB 데이터 및 바이너리 로그

sync_binlog=1이 설정되지 않았을 때 innodb_flush_log_at_trx_commit=1을 설정하면 해당 버전에 추가된 일부 최적화로 인해 서버의 InnoDB의 Redo 로그에서 트랜잭션이 누락될 수도 있습니다.

이 경우 항상 sync_binlog=1로 설정하는 것이 좋습니다. 그렇게 할 수 없다면 innodb_flush_log_at_trx_commit을 1이 아닌 3으로 설정하는 것이 좋습니다.

 

▶ 디스크 플러시 비용 분할 상환(상각)(Amortizing Disk Flush Costs)

모든 트랜잭션은 COMMIT 후에 DB서버는 일반적으로 InnoDB 리두 로그 및 바이너리 로그에 대한 트랜잭션 변경 사항을 디스크로 플러시해야 합니다(fsync() 또는 fdatasync() 또는 이와 유사한 것과 같은, 즉 시스템 콜이라고 하는것을 이용합니다.). 이렇게 하면 트랜잭션에 의해 변경된 데이터가 디스크에 영구적으로 저장됩니다. 디스크 플러싱은 시간이 많이 걸리는 작업이며 커밋할 수 있는 TPS(초당 트랜잭션 수)의 측면에서 처리량에 대한 제한을 쉽게 부과할 수 있습니다.

 

그룹 커밋의 아이디어는 여러 병렬 트랜잭션의 여러 커밋에 대해 디스크로의 각 플러시 비용을 상각하는(줄이는) 것입니다. 예를 들어 병렬로 커밋을 시도하는 10개의 트랜잭션이 있는 경우 각 커밋에 대해 하나의 시스템 호출을 수행하는 대신 단일 시스템 호출로 모든 트랜잭션을 강제로 한 번에 디스크를 플러시할 수 있습니다. 이렇게 하면 플러시 작업의 필요성을 크게 줄일 수 있으며 결과적으로 초당 트랜잭션(TPS) 처리량을 크게 향상시킬 수 있습니다.

 

그러나 그룹 커밋의 긍정적인 효과를 보려면 워크로드에 충분한 병렬 처리가 있어야 합니다. 그룹 커밋이 효과적이려면 최소 3개의 병렬 트랜잭션이 필요하다는 것입니다. 예를 들어, 첫 번째 트랜잭션이 플러시 작업이 완료되기를 기다리는 동안 다른 두 트랜잭션은 변경 사항이 디스크에 플러시될 때까지 대기합니다. 첫 번째 트랜잭션이 완료되면 단일 시스템 호출을 사용하여 대기열에 있는 두 트랜잭션을 플러시할 수 있으며 이 경우 세 가지 시스템 호출 중 하나를 저장할 수 있습니다.

 

충분한 병렬 처리 외에도 플러시 작업이 병목 현상을 일으킬만한 충분한 초당 트랜잭션이 있어야 합니다. 그러한 병목 현상이 없으면(즉, 트랜잭션이 다른 트랜잭션의 플러시가 완료될 때까지 기다릴 필요가 전혀 또는 거의 필요하지 않은 경우) 그룹 커밋은 거의 성능 개선을 제공하지 않습니다. 대량의 병렬 트랜잭션에서 많은 장점이 있는 기능이기 때문입니다.

 

▶ 그룹 커밋 빈도 변경

그룹 커밋 빈도는 디스크 동기화 대기 시간을 설정하는 파라미터와 한번에 적용할 트랜잭션 크기를 설정하는 파라미터 값을 조절하면 빈도수를 변경 할 수 있습니다. MySQL과 MariaDB에서 설정하는 파라미터는 각각 다음과 같습니다.

	파라미터 이름	기본값	설명
MySQL	binlog_group_commit_sync_delay	0	바이너리 로그 파일을 디스크에 동기화하기 전에 바이너리 로그 커밋이 대기하는 마이크로초를 제어합니다. 기본적으로 binlog_group_commit_sync_delay는 지연이 없음을 의미하는 0으로 설정됩니다. binlog_group_commit_sync_delay를 마이크로초 지연으로 설정하면 더 많은 트랜잭션을 한 번에 디스크에 동기화할 수 있으며, 큰 그룹은 그룹당 더 적은 시간 단위가 필요하기 때문에 트랜잭션 그룹을 커밋하는 전체 시간을 줄입니다.
MySQL	binlog_group_commit_sync_no_delay_count	0	binlog_group_commit_sync_delay에 지정된 대로 현재 지연을 중단하기 전에 대기할 최대 트랜잭션 수입니다. binlog_group_commit_sync_delay가 0으로 설정된 경우 이 옵션은 효과가 없습니다.
MariaDB	binlog_commit_wait_usec	100000	빈 로그 그룹 커밋을 위해 대기하는 커밋의 최대 시간(마이크로초)입니다. binlog_commit_wait_count 값이 0이 아닌 경우에만 적용됩니다.
MariaDB	binlog_commit_wait_count	0	0이 아닌 경우 binlog 쓰기는 binlog_commit_wait_usec 마이크로초까지 대기하여 binlog에 대한 그룹 커밋을 대기합니다. 이렇게 하면 빈 로그의 I/O를 줄이고 슬레이브에서 병렬 적용 기회를 늘릴 수 있지만 값이 너무 높으면 커밋 처리량이 감소합니다.

 

 

 

 

■ 추가 정보 - 그룹 커밋 비율 측정(MariaDB 에서만)

그룹 커밋이 플러시 오버헤드를 줄이는 데 얼마나 효과적인지 확인하기 위해 두 가지 상태 변수를 사용할 수 있습니다.

binlog_commits 및 binlog_group_commits 상태 변수입니다. 다음 쿼리를 사용하여 해당 값을 얻을 수 있습니다.

 

SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');

Binlog_commits는 바이너리 로그에 커밋된 총 트랜잭션 수입니다.

 

Binlog_group_commits는 바이너리 로그에 커밋된 총 그룹 수입니다. 이 페이지의 이전 섹션에서 설명했듯이 그룹 커밋은 단일 플러시 시스템 호출을 공유하여 트랜잭션 그룹이 바이너리 로그에 함께 플러시되는 경우입니다. sync_binlog=1이 설정되면 이것은 바이너리 로그에 대한 커밋을 플러시하는 과정에서 실행된 플러시 시스템 호출의 총 수이기도 합니다.

 

따라서 그룹 커밋이 바이너리 로그의 플러시 시스템 호출 수를 줄이는 데 효과적인 정도는 이 두 상태 변수 간의 비율로 결정할 수 있습니다. Binlog_commits는 항상 Binlog_group_commits보다 크거나 같습니다. 이러한 상태 변수 간의 차이가 클수록 플러시 오버헤드를 줄이는 데 그룹 커밋이 더 효과적이었습니다.

 

그룹 커밋 비율을 계산하려면 실제로 두 개의 스냅샷에서 이러한 상태 변수의 값이 필요합니다. 그러면 다음 공식으로 비율을 계산할 수 있습니다.

transactions/group commit = (Binlog_commits(snapshot2) - Binlog_commits(snapshot1))/(Binlog_group_commits(snapshot2) - Binlog_group_commits(snapshot1))

 

예를 들어 다음과 같은 첫 번째 스냅샷이 있는 경우:

SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Binlog_commits       | 120   |
| Binlog_group_commits | 120   |
+----------------------+-------+
2 rows in set (0.00 sec)

그리고 다음 두 번째 스냅샷:

SHOW GLOBAL STATUS WHERE Variable_name IN('Binlog_commits', 'Binlog_group_commits');
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Binlog_commits       | 220   |
| Binlog_group_commits | 145   |
+----------------------+-------+
2 rows in set (0.00 sec)

 

그러면 다음과 같이 됩니다.

transactions/group commit = (220 - 120) / (145 - 120) = 100 / 25 = 4 transactions/group commit

그룹 커밋 비율이 1에 너무 가까우면 그룹 커밋 빈도를 변경하는 것이 도움이 될 수 있습니다.

 

▶ 병렬 복제와 함께 그룹 커밋 사용

MariaDB 10.0 이상에서 그룹 커밋은 순차 병렬 복제의 보수적 모드를 활성화하는 데도 사용됩니다.

 

▶ 그룹 커밋이 InnoDB 성능에 미치는 영향

MariaDB 10.0 이상에서 innodb_flush_log_at_trx_commit=1(기본값)이 모두 설정되고 바이너리 로그가 활성화되면 커밋하는 동안 InnoDB 내부 디스크에 대한 동기화가 하나 줄어듭니다(3개가 아닌 트랜잭션 그룹 간에 2개의 동기화가 공유됨). 자세한 내용은 바이너리 로그 그룹 커밋 및 InnoDB 플러싱 성능을 참조하십시오.

 

10.0부터 MariaDB

그룹 커밋과 관련된 InnoDB 플러싱 성능 개선 사항은 MariaDB 10.0에서 처음 도입되었습니다.

 

▶︎ 상태 변수

Binlog_commits는 바이너리 로그에 커밋된 총 트랜잭션 수입니다.

Binlog_group_commits는 바이너리 로그에 커밋된 총 그룹 수입니다.

 

Binlog_group_commit_trigger_count는 그룹의 바이너리 로그 커밋 수가 시스템 변수 binlog_commit_wait_count에 의해 설정된 제한에 도달했기 때문에 트리거된 그룹 커밋의 총 수입니다.

 

Binlog_group_commit_trigger_lock_wait는 이전 바이너리 로그 커밋에 의해 잠금이 유지되었던 잠금 대기로 인해 바이너리 로그 커밋이 지연되어 트리거된 총 그룹 커밋 수입니다. 이 경우 이후의 바이너리 로그 커밋이 다음 그룹 커밋에 배치됩니다.

 

Binlog_group_commit_trigger_timeout은 첫 번째 바이너리 로그 커밋이 시스템 변수 binlog_commit_wait_usec에 의해 설정된 제한에 도달한 이후 시간으로 인해 트리거된 그룹 커밋의 총 수입니다.

 

▶ 10.0부터 MariaDB

Binlog_group_commit_trigger_count, Binlog_group_commit_trigger_lock_wait 및 Binlog_group_commit_trigger_timeout 상태 변수는 MariaDB 10.0.18 및 MariaDB 10.1.5에서 처음 도입되었습니다.

 

이러한 변수를 쿼리하려면 다음과 같은 SQL문을 사용힙니다.

SHOW GLOBAL STATUS LIKE 'Binlog_%commit%';

 

그룹 커밋의 성능은 binlog에서 확인해볼 수 있습니다.

mysqlbinlog 명령어를 이용하여 바이너리 로그를 텍스트로 추출하고 확인해보면 성능을 확인해볼 수 있습니다.

 

[root]# mysqlbinlog mysql-bin.0000006 | grep last_committed
#150520 14:23:11 server id 88 end_log_pos 259 CRC32 0x4ead9ad6 GTID last_committed=0 sequence_number=1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed=0 sequence_number=2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed=0 sequence_number=3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed=0 sequence_number=4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed=0 sequence_number=5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed=0 sequence_number=6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed=6 sequence_number=7
#150520 14:23:11 server id 88 end_log_pos 8834 CRC32 0x96864e6b GTID last_committed=6 sequence_number=8
#150520 14:23:11 server id 88 end_log_pos 10057 CRC32 0x2de1ae55 GTID last_committed=6 sequence_number=9
#150520 14:23:11 server id 88 end_log_pos 11280 CRC32 0x5eb13091 GTID last_committed=6 sequence_number=10
#150520 14:23:11 server id 88 end_log_pos 12504 CRC32 0x16721011 GTID last_committed=6 sequence_number=11
#150520 14:23:11 server id 88 end_log_pos 13727 CRC32 0xe2210ab6 GTID last_committed=6 sequence_number=12
#150520 14:23:11 server id 88 end_log_pos 14952 CRC32 0xf41181d3 GTID last_committed=12 sequence_number=13
...

 

바이너리 내용을 확인해보면 last_committed과 sequence_number 이 두가지가 반복적으로 출력되는것을 알 수 있습니다.

이 두 값은 소위 "논리 타임스탬프 태그(논리 클록)으로 다중 스레드 복제(MTS) 기능을 제어하는 데 사용할 수 있습니다.

 

- sequence_number: 바이너리 로그 파일에서 순차적으로 증가하는 번호로 제출된 트랜잭션에 대해 번호를 부여후 순차적으로 증가시킵니다. 즉, 각 트랜잭션의 일련 번호에 해당합니다. 최신 커밋 트랜잭션을 표시하는 데 사용됩니다.

이 값은 트랜잭션의 2번째 단계에서 준비단계 제출(Prepare phase submitted)에서 기록됩니다.

 

- last_committed: Last_committed는 트랜잭션이 제출될 때 제출된 마지막 트랜잭션 수를 나타냅니다. 트랜잭션에 동일한 last_committed가 있으면 이러한 트랜잭션이 그룹에 있고 멀티 슬레이브 구성시 병렬로 재생할 수 있음을 의미합니다. 또한 Last_committed는 처음 시작 번호가 sequence_number의 시작번호와 같습니다. 즉 sequence_number와 Last_committed 숫자를 조합하면 트랜잭션의 시작점이 어딘지 알아낼 수 있습니다.

 

 

참고 자료

http://www.tocker.ca/2014/12/30/an-easy-way-to-describe-mysqls-binary-log-group-commit.html

https://programmerall.com/article/24532280760/

http://www.tocker.ca/2014/12/30/an-easy-way-to-describe-mysqls-binary-log-group-commit.html

https://mariadb.com/kb/en/group-commit-for-the-binary-log/

https://developer.aliyun.com/article/617776

http://mysqlmusings.blogspot.com/2012/06/binary-log-group-commit-in-mysql-56.html

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※