이번 글에서는 복제가 내부적으로 어떻게 동작하는지에 대해 네트워크 프로토콜 수준부터 스레드 아키텍처, GTID 복구 메커니즘, 장애 시나리오별 동작, 그리고 실제 운영에서 놓치기 쉬운 함정들까지 깊게 다룬다.
Replica 복제는 어떤 방식으로 어떻게 전송되는가
1. MySQL 복제는 TCP 위의 이벤트 스트리밍
많은 사람들이 복제를 "주기적으로 변경 내역을 가져오는 것"으로 막연하게 이해한다.
실제로는 TCP 위에서 동작하는 MySQL 고유의 Binary Protocol로 이벤트를 스트리밍하는 구조다.
OSI 계층 관점:
Layer 7 (Application) → MySQL Binary Protocol (COM_BINLOG_DUMP_GTID)
Layer 6 (Presentation) → 선택적 압축 (zstd / zlib)
Layer 5 (Session) → MySQL Connection (long-lived session)
Layer 4 (Transport) → TCP (3306 포트, 일반 클라이언트 포트와 동일)
Layer 3 (Network) → IP
Layer 1-2 → Ethernet / 물리 매체Replica의 IO Thread는 일반 MySQL 클라이언트처럼 3306 포트로 TCP 연결을 맺는다.
별도 복제 전용 포트가 없다. 이 TCP 커넥션을 끊지 않고 유지하면서 Primary로부터
Binlog 이벤트를 지속적으로 스트리밍 받는다. HTTP의 Server-Sent Events와 개념적으로 유사하지만,
그것보다 훨씬 낮은 레벨의 바이너리 프로토콜이다.
2. 복제 핸드셰이크 과정
TCP 연결이 수립된 후 다음 순서로 복제가 시작된다.
다이어그램 순서를 따라가 보면:
① COM_REGISTER_REPLICA: Replica가 자신의 server_id, 호스트명, 포트를 Primary에 등록한다.
Primary는 이 정보로 SHOW REPLICAS 목록을 관리한다.
여기서 주의할 게 있다. server_id가 충돌하면 나중에 연결한 Replica가 기존 연결을 끊어버린다.
Docker 환경에서 설정 파일에 server-id를 명시하지 않으면 두 컨테이너가 모두 기본값 1이 되고,
서로가 서로를 끊는 무한 루프에 빠진다.
② COM_BINLOG_DUMP_GTID: "나는 이 GTID들을 이미 갖고 있으니, 그 다음 것부터 보내줘"라는 요청이다.
이를 수신한 Primary는 해당 커넥션 전담 Binlog Dump Thread를 생성한다.
이 스레드가 Binlog 파일을 직접 읽어 Replica I/O Thread로 스트리밍한다.
Replica가 3개면 Primary에 Dump Thread도 3개 생긴다. 각각 하나의 커넥션 슬롯을 점유한다.
-- Primary에서 확인:
SHOW PROCESSLIST;
-- Id User Command State
-- 12 replicator Binlog Dump GTID Master has sent all binlog to replica; ...
-- 13 replicator Binlog Dump GTID Master has sent all binlog to replica; ...Replica가 늘어날수록 Dump Thread 수도 늘어난다. max_connections 계획 시 이 점을 함께 고려해야 한다.
3. Binlog 이벤트 패킷 구조
Primary가 Replica에게 전송하는 각 이벤트는 MySQL 프로토콜 패킷으로 감싸진다.
헤더의 timestamp는 Primary에서 트랜잭션이 커밋된 시각이다.
이 값이 seconds_behind_master 계산에 핵심적으로 사용된다.
4. 네트워크 압축 옵션
MySQL 8.0.18부터 binlog_transaction_compression = ON을 설정하면
Binlog 이벤트의 Body를 zstd 알고리즘으로 압축하여 전송한다.
압축 효과 (INSERT 위주 워크로드 실측 예):
- 비압축: 100MB/s Binlog 생성 → 100MB/s 네트워크 사용
- zstd 압축: 100MB/s Binlog → 약 20~30MB/s 네트워크 사용 (70% 절감)
적합한 환경:
- Primary-Replica 간 네트워크 대역폭이 제한된 환경 (클라우드 WAN 등)
- 대량 텍스트/JSON 데이터를 처리하는 워크로드 (압축률 높음)
- Phase 3 Debezium을 통한 CDC 시에도 압축된 상태 전달 → Kafka로 보내기 전 해제
주의:
- CPU 사용량 증가 (압축/해제)
- 바이너리 로그 뷰어(mysqlbinlog)에서 별도 처리 필요
- Debezium connector 버전 호환성 확인 필요
압축이든 비압축이든, 이 이벤트들을 실제로 받아서 처리하는 것은 Replica의 I/O Thread다.
MySQL I/O Thread는 어떻게 동작하는가
1. I/O Thread는 기본적으로 단일 스레드다
하나의 Replica-Primary 연결 = 하나의 I/O Thread.
I/O Thread가 멀티스레드가 아닌 이유:
- Primary의 Binlog는 단일 스트림이다. 여러 트랜잭션이 하나의 순서로 기록된다.
- 여러 I/O Thread가 하나의 스트림을 읽으면 순서가 뒤섞인다.
- 따라서 수신 자체는 단일 스레드로 순서를 보장한다.
단, Multi-Source Replication(여러 Primary에서 동시에 복제)에서는
각 소스마다 독립된 I/O Thread가 생성된다.
일반 복제 (1 Primary → 1 Replica):
I/O Thread 1개 (Primary 연결 유지)
Multi-Source 복제 (2 Primary → 1 Replica):
I/O Thread 1 (Primary-A 전용)
I/O Thread 2 (Primary-B 전용)2. I/O Thread의 내부 동작 세부 흐름
핵심은 이벤트를 받는 즉시 Relay Log에 파일로 쓴다는 것이다.
메모리에 쌓아뒀다가 나중에 쓰는 구조가 아니다. 그래서 I/O Thread가 죽어도 SQL Thread는 이미 기록된 Relay Log를 계속 처리할 수 있고, 재연결 후에도 기록된 지점부터 이어받을 수 있다.
데이터가 Primary에서 I/O Thread까지 오는 경로도 직접 연결이 아니다.
3. 네트워크 버퍼와 흐름 제어
I/O Thread가 Primary 소켓에서 직접 데이터를 꺼내는 게 아니다.
OS 커널이 TCP 패킷을 수신 버퍼(net.core.rmem_default 기본 212KB) 에 먼저 쌓고, I/O Thread가 read() 시스템 콜로 꺼내간다. I/O Thread는 이 버퍼에서 이벤트를 꺼내 Relay Log에 쓰는 과정을 계속 반복한다.
Primary 전송 속도 >> I/O Thread 처리 속도인 경우:
→ OS TCP 수신 버퍼 포화
→ TCP 흐름 제어(Flow Control)가 자동으로 Primary의 전송 속도를 낮춤
→ Primary의 Send_Q에 미전송 이벤트 누적
→ Primary의 net_write_timeout(기본 60초) 초과 시 연결 끊김 위험
확인:
SHOW GLOBAL STATUS LIKE 'Replica_running'; -- MySQL 8.0.26+ (이전: Slave_running)
netstat -s | grep "receive buffer errors" -- OS 레벨 버퍼 오버플로우4. Heartbeat
아무 변경이 없는 조용한 시간대에도 I/O Thread는 Primary와 연결이 살아있는지 확인해야 한다.
이를 위해 Primary는 주기적으로 HEARTBEAT_LOG_EVENT 를 전송한다.
Heartbeat 주기 = replica_net_timeout / 2
replica_net_timeout = 60초 설정 시:
→ Primary는 30초마다 Heartbeat 이벤트 전송
→ I/O Thread가 60초 내에 아무것도 못 받으면 → 연결 사망 판단 → 재연결
Heartbeat는 Relay Log에 기록되지 않는다.
(실제 데이터 변경이 없으므로 SQL Thread가 처리할 내용 없음)
특수 케이스:
Primary에 변경이 지속적으로 발생하면 Heartbeat는 자동으로 생략된다.
"최근 N초 내에 실제 이벤트를 보냈으면 Heartbeat 불필요"
I/O Thread가 이렇게 연결을 유지하며 받아온 이벤트들은 메모리에 머물지 않고 하나씩 파일에 기록되는데 이 파일을 Relay Log 라고 한다.
Relay Log: I/O Thread와 SQL Thread를 분리하는 이유와 구조
I/O Thread가 Primary로부터 받은 이벤트를 SQL Thread에 바로 넘기지 않고, 왜 Relay Log라는 중간 파일을 거치는 걸까?
만약 Relay Log 없이 직접 연결한다면:
문제 1 - 속도 차이:
I/O Thread 수신 속도: 네트워크 속도에 의존 (빠를 수 있음)
SQL Thread 실행 속도: 쿼리 복잡도에 의존 (느릴 수 있음)
→ 속도 차이를 완충할 버퍼 없음 → SQL Thread가 못 따라가면 I/O Thread도 멈춰야 함
→ Primary 전체 복제가 SQL 실행 하나에 블로킹됨
문제 2 - 내구성:
Relay Log 없이 수신만 하다가 Replica가 크래시되면?
→ 수신은 됐지만 실행 못 한 이벤트가 메모리에만 있었다면 모두 소실
→ I/O Thread가 Primary에서 다시 받아와야 함 (Primary Binlog가 아직 있다면)
문제 3 - 재시작 후 위치 복원:
결과적으로 "어디까지 받았고 어디까지 실행했는가"를 분리해서 추적 불가Relay Log는 내구성 있는 중간 버퍼로, I/O Thread(수신)와 SQL Thread(실행)를 완전히 분리한다.
1. Relay Log 파일 구조
Relay Log는 Binlog와 동일한 바이너리 포맷을 사용한다. 다른 점은 파일명뿐이다.
Relay Log 파일:
mysql-relay-bin.000001 ← 현재 IO Thread가 쓰는 파일
mysql-relay-bin.000002
mysql-relay-bin.index ← 모든 Relay Log 파일 목록
파일 교체(rotation) 조건:
1. max_relay_log_size 초과 (0이면 max_binlog_size 기준, 기본 1GB)
2. Primary에서 ROTATE_EVENT 수신 (Primary의 Binlog가 교체됨)
3. FLUSH RELAY LOGS 명령 실행
4. Replica 재시작
※ relay_log_space_limit와 혼동 주의:
relay_log_space_limit = 모든 Relay Log 총합 제한
→ 초과 시 rotation이 아니라 IO Thread를 일시 중단 (SQL Thread가 오래된 파일 삭제할 때까지 대기)
→ 쓰기 부하가 높고 SQL Thread가 뒤처진 환경에서 Primary 디스크 보호에 사용
Relay Log 자동 정리:
SQL Thread가 특정 Relay Log 파일의 마지막 이벤트를 실행하면
해당 파일은 자동으로 삭제된다 (relay_log_purge = ON 기본값)
→ 정상 운영 중에는 Relay Log가 최소한으로 유지됨
→ SQL Thread가 뒤처질수록 Relay Log가 디스크를 채움2. Relay Log 위치 추적: relay_log_info
Replica는 두 가지 위치를 항상 추적한다.
mysql.slave_relay_log_info 테이블 (MySQL 8.0 기본):
Column Value 예시
─────────────────────────────────────────────────
Master_log_name mysql-bin.000010 ← I/O Thread가 현재 읽는 Primary Binlog 파일
Master_log_pos 4194832 ← 그 파일 내 I/O Thread의 현재 위치
Relay_log_name mysql-relay-bin.000003 ← SQL Thread가 읽는 Relay Log 파일
Relay_log_pos 1024 ← SQL Thread가 Relay Log에서 현재 실행한 위치이 두 위치의 차이가 "복제 지연의 바이트 단위 크기"다.
-- SHOW REPLICA STATUS로 확인
SHOW REPLICA STATUS\G
-- 주요 출력:
Master_Log_File: mysql-bin.000010 ← I/O Thread가 읽는 위치
Read_Master_Log_Pos: 4194832
Relay_Master_Log_File: mysql-bin.000009 ← SQL Thread가 실행한 위치
Exec_Master_Log_Pos: 3145728
-- 만약 두 파일이 다르다면:
-- I/O Thread는 .000010을 읽는데 SQL Thread는 아직 .000009 처리 중
-- → Primary에서 .000009 이후 변경이 Replica에 아직 미적용3. relay_log_recovery: 재시작 시 안전장치
relay_log_recovery = ON (권장 설정)
Replica 비정상 종료 → 재시작 시 동작:
1. Relay Log 파일 중 마지막 파일을 검사
2. 완전히 기록되지 않은 (partial) 이벤트 탐지
3. SQL Thread의 exec_master_log_pos를 기준점으로 사용
4. 해당 위치 이후의 모든 Relay Log를 삭제 (손상된 것 포함)
5. IO Thread를 통해 Primary에서 해당 위치 이후 이벤트를 다시 수신
relay_log_recovery = OFF 시 위험:
비정상 종료로 마지막 이벤트가 절반만 기록된 Relay Log 파일이 있을 수 있음
SQL Thread가 손상된 이벤트를 읽으려 하면 복제 중단 및 에러이렇게 Relay Log에 차곡차곡 쌓인 이벤트들을 SQL Thread가 꺼내 실행한다.
단, 단일 SQL Thread 하나로는 현대의 고TPS 환경에서 구조적으로 따라잡기 어렵다는 한계가 있다. 다음 절에서 이 문제와 해결책을 살펴본다.
SQL Thread와 MTS: 병렬 복제의 진짜 동작 원리
1. 단일 SQL Thread의 한계
전통적인 복제에서 SQL Thread는 단 하나였다.
Primary: 1초에 트랜잭션 1000개 커밋
T1, T2, T3, ... T1000 (병렬로 처리됨)
전통 Replica SQL Thread (단일):
T1 실행 → T2 실행 → T3 실행 → ... (직렬 처리)
→ 초당 500개밖에 처리 못 하면 지연이 쌓임
→ 수 분, 수 시간의 복제 지연 발생
→ Primary TPS가 높을수록 구조적으로 따라잡기 불가능2. MTS (Multi-Threaded Slave): 병렬 복제 구조
MySQL 5.7부터 replica_parallel_workers > 1로 설정하면 MTS가 활성화된다.
MTS가 활성화되면 기존 단일 SQL Thread는 역할이 분리된다.
SQL Thread는 Coordinator Thread로 전환되어 Relay Log 읽기와 Worker 배분만 담당하고,
실제 트랜잭션 실행은 Worker Thread들이 병렬로 처리한다.
3. DATABASE vs LOGICAL_CLOCK: 병렬화 기준의 차이
Coordinator가 "트랜잭션 의존 관계 분석"에 사용하는 기준은 replica_parallel_type 설정으로 결정된다. 어떤 트랜잭션을 병렬로 실행해도 안전한가를 판단하는 방식이 두 가지다.
DATABASE 방식 (구버전, 비권장)
"서로 다른 데이터베이스의 트랜잭션은 병렬 실행 가능"
DB_A 트랜잭션 ←──── Worker 1
DB_B 트랜잭션 ←──── Worker 2 (동시 실행 가능)
문제: 대부분의 서비스는 단일 DB 사용
→ 모든 트랜잭션이 같은 DB
→ 병렬화 효과 없음 (단일 직렬 실행과 동일)LOGICAL_CLOCK 방식 (MySQL 5.7.22+, 권장)
핵심 아이디어: Primary에서 동시에 커밋된 트랜잭션들은 서로 간에 의존성이 없다.
Primary에서 트랜잭션들이 커밋되는 상황:
시각 커밋된 트랜잭션
t=0ms T1 (last_committed=0, sequence_number=1)
t=0ms T2 (last_committed=0, sequence_number=2) ← T1과 동시 커밋
t=0ms T3 (last_committed=0, sequence_number=3) ← T1, T2와 동시 커밋
t=1ms T4 (last_committed=3, sequence_number=4) ← T3 커밋 후에 시작
t=1ms T5 (last_committed=3, sequence_number=5) ← T3 커밋 후에 시작last_committed는 이 트랜잭션이 시작될 때 가장 마지막으로 커밋된 트랜잭션의 sequence_number다.
같은 last_committed를 가진 트랜잭션들은 서로 겹치는 잠금이 없었음을 의미하며,
따라서 Replica에서도 병렬로 안전하게 실행할 수 있다.
Binlog의 GTID_LOG_EVENT 내용:
# T1
last_committed=0, sequence_number=1
# T2 (T1과 같은 last_committed → 병렬 실행 가능)
last_committed=0, sequence_number=2
# T4 (last_committed=3 → T3 완료 후에만 실행 가능)
last_committed=3, sequence_number=4
Coordinator의 판단:
T1, T2, T3 → last_committed 동일 → Worker들에게 동시 배분
T4, T5 → last_committed=3 → T3의 sequence_number(3) 완료 후에 배분이 값들은 Binlog에 직접 기록되어 있으므로, Coordinator는 쿼리를 실행해보지 않고도
Relay Log만 읽어서 병렬화 계획을 수립할 수 있다.
그런데 여기서 핵심 의문이 생긴다. 얼마나 많은 트랜잭션이 동일한 last_committed를 갖는지. 즉, 병렬화 효과가 실제로 얼마나 큰가는 Primary에서 얼마나 많은 트랜잭션이 동시에 커밋되는가에 달려 있다. 이 메커니즘이 바로 Group Commit이다.
4. Group Commit: 병렬 복제의 실질적 원천
LOGICAL_CLOCK 방식의 병렬 복제가 얼마나 효과적인가는 Primary에서 얼마나 많은 트랜잭션이 동시 커밋되는가에 달려 있다. Group Commit이 적으면 last_committed가 모두 달라져 Replica에서 병렬화가 불가능하다.
Group Commit 메커니즘:
MySQL Binary Log Group Commit 파이프라인 (3단계):
Stage 1: Flush Stage — 각 스레드의 Binary Log 캐시를 binary log 파일에 write (OS 버퍼)
Stage 2: Sync Stage — binary log 파일을 디스크에 fsync (sync_binlog=1이면 매번)
Stage 3: Commit Stage — InnoDB 스토리지 엔진 커밋 (Binlog는 이미 Stage 1/2 완료)
※ Redo Log 기록은 Stage 1 이전 InnoDB prepare 단계에서 별도 처리됨
각 Stage는 "Leader" 스레드가 Stage에 진입한 후,
잠시 기다려 다른 스레드들을 "모아서" 한 번에 처리한다.
Group Commit 크기를 늘리는 설정:
# Primary 측 설정
# Sync Stage에서 N마이크로초 추가 대기 (더 많은 트랜잭션을 모음)
binlog_group_commit_sync_delay = 100 # 단위: 마이크로초 (0.1ms)
# N개의 트랜잭션이 모이면 delay 없이 즉시 진행 (delay와 함께 사용)
binlog_group_commit_sync_no_delay_count = 10최적화 효과 비교:
기본 설정 (delay=0):
TPS=1000 환경에서 트랜잭션 간격 1ms
Group Commit 그룹 크기: 평균 1~2개
→ Binlog의 last_committed 대부분 다름 → Replica 병렬화 거의 없음
delay=100 마이크로초 설정:
0.1ms 대기하는 동안 평균 수십 개의 트랜잭션 모임
→ 그룹 크기 평균 20~50개
→ Replica에서 20~50개가 동시 실행 → 복제 지연 극적 감소
트레이드오프:
Primary 쓰기 레이턴시가 delay만큼 증가
→ OLTP 서비스: 100~1000μs 정도는 사용자 체감 없음
→ 실시간 금융 거래: delay 최소화 필요Group Commit 효과 확인:
-- Binlog에서 last_committed 분포 확인
mysqlbinlog mysql-bin.000001 | grep last_committed | head -20
-- 연속된 이벤트들의 last_committed가 같으면 Group Commit 그룹
-- last_committed=5, sequence_number=10
-- last_committed=5, sequence_number=11 ← 동시 커밋 → 병렬화 가능
-- last_committed=5, sequence_number=12
-- last_committed=12, sequence_number=13 ← 직렬 → 병렬화 불가5. replica_preserve_commit_order = ON의 필요성
병렬로 실행하면 Worker들이 커밋하는 순서가 Original 순서와 달라질 수 있다.
예시:
Primary 커밋 순서: T1 → T2 → T3
Worker에 배분: Worker1=T1, Worker2=T2, Worker3=T3
Worker 완료 순서: T3 먼저 완료, T2 두 번째, T1 마지막
replica_preserve_commit_order = OFF 시:
Replica의 커밋 순서: T3, T2, T1 (역순!)
→ 만약 T3 커밋 후 서버 크래시 → T1, T2는 커밋 안 됨
→ GTID 기준으로는 T3만 완료 → T1, T2는 다시 실행
→ 하지만 T3에서 T1, T2의 작업을 전제로 한 변경이 있었다면?
→ 데이터 불일치 위험
replica_preserve_commit_order = ON 시:
Worker들이 실행은 병렬로 하되, 커밋은 Original 순서로 대기
T1 커밋 완료 → T2 커밋 허용 → T3 커밋 허용
→ 실행 병렬화 + 커밋 순서 보장 (안전)LOGICAL_CLOCK + replica_preserve_commit_order = ON 조합이 표준 설정이다.
이 설정은 단순히 순서 보장에만 그치지 않는다. MTS 환경에서 비정상 종료 시 gap 문제에도 직결된다.
병렬 실행 중 크래시 시나리오:
시각 Worker1 Worker2 Worker3
t=0 T1 실행 T2 실행 T3 실행
t=100ms T1 커밋✅ (실행중) T3 커밋✅
t=150ms [크래시] [크래시] [크래시]
커밋된 트랜잭션: T1(seq=1), T3(seq=3)
미커밋 트랜잭션: T2(seq=2)
gtid_executed: uuid:1,3 ← seq 2가 빠진 "gap" 발생!
replica_preserve_commit_order = ON이면:
T3는 T2보다 먼저 커밋할 수 없으므로 크래시 시점에 T3도 실제로는 커밋 안 됨
→ gap이 발생하지 않음6. Worker Thread가 실행하는 것의 정체
Worker Thread는 단순히 "SQL 문자열을 실행"하는 것이 아니다.
ROW 형식 Binlog에는 SQL 문자열이 없다. Row Event를 해석해서 직접 행을 변경한다.
ROW 형식 이벤트를 Worker가 처리하는 과정:
1. TABLE_MAP_EVENT 수신
→ 테이블 ID와 실제 테이블명의 매핑 정보 획득
→ 컬럼 타입, 메타데이터 확인
2. WRITE_ROWS_EVENT (INSERT에 해당) 수신
→ After-image: 삽입할 행의 실제 데이터 (바이너리)
→ Worker가 InnoDB에 직접 행 삽입
3. UPDATE_ROWS_EVENT (UPDATE에 해당) 수신
→ Before-image: 변경 전 행 데이터
→ After-image: 변경 후 행 데이터
→ Worker가 Before-image로 행을 찾고 After-image로 교체
4. DELETE_ROWS_EVENT (DELETE에 해당) 수신
→ Before-image: 삭제할 행 데이터
→ Worker가 해당 행 삭제
5. XID_EVENT (COMMIT) 수신
→ Worker가 트랜잭션 커밋이 과정에서 Worker는 Primary에서 실행된 것과 동일한 결과를 만들어내야 한다.
Before-image로 행을 찾지 못하면 (Row not found) 복제가 중단된다.
올바르게 실행하는 것과 별개로, 어디까지 실행했는지를 정확하게 기억하는 것도 중요하다. 지금까지는 "파일명 + 위치(Position)"를 기준으로 추적한다고 설명했는데, 이 방식에는 구조적인 약점이 있다. 특히 장애 시 Primary를 교체할 때 이 약점이 그대로 드러난다.
GTID: Position 기반 복제의 한계와 해결 방법
1. Position 기반 복제의 문제
MySQL 5.6 이전에는 파일명+위치(Position) 기반 복제만 존재했다. 이 방식의 한계는 Failover 상황에서 명확하게 드러난다.
Position 기반 복제에서 Failover 시나리오:
구성: Primary(A) → Replica1(B), Replica2(C)
장애: A 다운, B를 새 Primary로 승격
문제:
B는 A의 mysql-bin.000005, position=1024까지 받았다고 가정
C는 A의 mysql-bin.000005, position=2048까지 받았다고 가정
(C가 B보다 더 많이 받음)
C를 새 Primary(B)의 Replica로 등록하려면:
"B의 어떤 파일, 어떤 위치부터 복제를 시작해야 하는가?"
→ B의 Binlog 파일명과 위치는 A와 완전히 다름
→ 직접 계산해야 함: C가 A에서 얼마나 받았고, B에는 어디까지 있는지 수동 매칭
→ DBA가 실수하면 트랜잭션 누락 또는 중복 실행 → 데이터 불일치!2. GTID가 이 문제를 해결하는 방식
Position 기반의 문제는 "파일명+위치"가 서버마다 달라서 수동으로 매칭해야 한다는 것이었다. GTID는 추적 단위를 바꾼다.
Position 기반: "A 서버의 mysql-bin.000005, 위치 1024까지 받았다"
→ 서버가 바뀌면 이 정보가 의미 없어짐
GTID 기반: "uuid-A:1-950번 트랜잭션을 실행했다"
→ 트랜잭션에 전역 고유 ID가 붙어있으므로
어느 서버로 연결해도 "내가 없는 것을 줘"라고 요청 가능각 서버는 자신이 실행한 GTID 목록(`gtid_executed`)을 항상 보관한다.
Replica가 새 Primary에 연결할 때 `SOURCE_AUTO_POSITION=1` 하나면 된다.
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='new_primary',
SOURCE_AUTO_POSITION=1; -- "내 gtid_executed에 없는 것을 알아서 보내줘"DBA가 파일명과 위치를 직접 계산할 필요가 없다.
3. GTID의 구조
GTID 형식:
server_uuid:transaction_id
예시:
a1b2c3d4-e5f6-7890-abcd-ef1234567890:1
a1b2c3d4-e5f6-7890-abcd-ef1234567890:2
a1b2c3d4-e5f6-7890-abcd-ef1234567890:3
GTID Set (범위 표현):
a1b2c3d4-...:1-100 ← 1번부터 100번 트랜잭션 모두 포함
a1b2c3d4-...:1-50:60-100 ← 51~59번 누락 (이 경우는 복제 오류 가능성)server_uuid는 MySQL 최초 시작 시 자동 생성되어 auto.cnf 파일에 영구 저장된다.
서버 재시작해도 바뀌지 않는다. (컨테이너를 재생성하면 바뀔 수 있으므로 주의)
4. GTID 영구 저장: gtid_executed 테이블
GTID 복제의 핵심은 "내가 어떤 트랜잭션들을 실행했는가"를 영구적으로 기억하는 것이다.
mysql.gtid_executed 테이블 (InnoDB):
source_uuid interval_start interval_end
────────────────────────────────────────────────────────
a1b2c3d4-e5f6-7890-abcd-ef1234567890 1 500
a1b2c3d4-e5f6-7890-abcd-ef1234567890 501 1000
→ 이 Replica는 해당 Primary의 트랜잭션 1~1000을 모두 실행했음
이 테이블은 InnoDB 트랜잭션과 함께 원자적으로 기록된다:
비즈니스 SQL 실행 + gtid_executed 업데이트 = 하나의 트랜잭션
→ Replica 크래시 후 재시작 시 정확히 어디까지 실행했는지 InnoDB 복구로 정확히 알 수 있음
확인:
SELECT * FROM mysql.gtid_executed;
-- 또는
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';5. GTID 기반 Failover
구성: Primary(A, uuid-A) → Replica1(B), Replica2(C)
장애: A 다운, B를 새 Primary로 승격
B의 gtid_executed: uuid-A:1-950 (950개 실행 완료)
C의 gtid_executed: uuid-A:1-1000 (1000개 실행 완료)
C를 새 Primary(B)의 Replica로 등록:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='B',
SOURCE_AUTO_POSITION=1; ← GTID 방식, 위치 수동 지정 불필요
C: "나는 uuid-A:1-1000을 가지고 있어"
B: "나는 uuid-A:1-950을 가지고 있어"
B: "C는 이미 1-1000을 갖고 있으니 내가 가진 것은 이미 있는 것. 아무것도 전송 불필요"
→ 자동 동기화 완료
반대로 C → B 방향으로 950~1000을 B에게 줄 수 없는가?
→ 줄 수 없다. 이것이 "Errant Transaction" 문제다
GTID 덕분에 Failover 시 자동 동기화가 가능해졌다. 그렇다면 Replica가 한동안 내려갔다 재시작될 때는 어떻게 처리되는가.
Replica 재시작 시 누락 데이터는 어떻게 처리되는가
1. 정상적인 재시작 (계획된 다운타임)
상황: Replica를 30분간 내렸다가 재시작
Replica의 mysql.gtid_executed: uuid-A:1-500 (재시작 전 마지막 실행)
재시작 후 IO Thread 동작:
1. Primary에 연결
2. COM_BINLOG_DUMP_GTID 전송:
"나는 uuid-A:1-500을 가지고 있다. 그 이후 것을 줘"
3. Primary 응답:
"501번부터 현재(예: 2000번)까지 Binlog 스트리밍"
4. I/O Thread가 501~2000 이벤트를 순서대로 Relay Log에 기록
5. SQL Thread가 순서대로 실행 → 따라잡기 완료
소요 시간: 30분치 누적 이벤트 양에 따라 다름
- 이벤트가 적으면 수 초 ~ 수 분
- 대량 쓰기가 있었다면 수십 분2. Primary의 Binlog가 이미 삭제된 경우 (장기 다운타임)
상황: Replica가 10일 다운, binlog_expire_logs_seconds = 604800 (7일)
Replica: uuid-A:1-500
Primary의 현재 gtid_executed: uuid-A:1-10000
Primary의 gtid_purged: uuid-A:1-4000 (4000번까지 Binlog 삭제됨)
I/O Thread: "501번 이후를 줘"
Primary: "501번은 이미 purge됨. 줄 수 없음. Error 1236: Got fatal error from master"
해결 방법:
STOP REPLICA;
방법 1 - Primary에서 Full Dump:
mysqldump --source-data=2 --single-transaction ... -- MySQL 8.0.26+ 권장
-- (구버전: --master-data=2, MySQL 8.0.26부터 deprecated)
→ Primary 현재 상태를 덤프
→ Replica에 Import
→ CHANGE REPLICATION SOURCE TO ... MASTER_AUTO_POSITION=1;
→ START REPLICA;
방법 2 - Percona XtraBackup (무중단):
Hot backup → Replica에 복원 → Binlog 위치 자동 설정
→ 대용량 DB에서 mysqldump보다 훨씬 빠름
방법 3 - Clone Plugin (MySQL 8.0.17+):
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
-- Replica에서:
CLONE INSTANCE FROM 'root'@'primary':3306 IDENTIFIED BY 'password';
→ Primary의 현재 상태를 온라인으로 Replica에 복사
→ 복사 완료 후 자동으로 복제 재시작3. 비정상 종료 후 재시작 (Relay Log 손상 가능)
상황: Replica 서버가 OOM Killer 또는 전원 차단으로 비정상 종료
relay_log_recovery = ON 설정 시:
1. MySQL 시작 시 relay_log_recovery 루틴 실행
2. mysql.slave_relay_log_info에서 SQL Thread의 마지막 실행 위치 확인
exec_master_log_pos = Primary mysql-bin.000007, position 12345 (InnoDB에 저장)
3. 현재 Relay Log 파일들을 모두 삭제
(일부 손상됐을 수 있으므로 안전하게 전부 제거)
4. IO Thread가 Primary에서 12345 이후 이벤트를 다시 수신
5. SQL Thread가 다시 실행 시작
relay_log_recovery = OFF이고 손상된 경우:
에러: "Could not find first log file name in binary log index file"
수동 복구 필요:
STOP REPLICA;
RESET REPLICA;
CHANGE REPLICATION SOURCE TO ... (위치 수동 지정);
START REPLICA;장애 시나리오별 동작과 복구
1. 시나리오: 네트워크 순간 단절 (30초 이내)
t=0s : Primary-Replica 간 네트워크 장애
t=0s : IO Thread: TCP keepalive 실패 감지
t=30s: replica_net_timeout(60)/2 = Heartbeat 주기 초과
t=60s: replica_net_timeout 초과 → IO Thread 연결 종료 판단
t=61s: IO Thread 재연결 시도 (replica_connect_retry = 60초마다)
t=61s: 네트워크 복구 → 재연결 성공
t=62s: COM_BINLOG_DUMP_GTID로 누락 이벤트 요청
t=63s: 누락 이벤트 수신 및 SQL Thread 실행 → 정상 복구
애플리케이션 영향:
Replica는 읽기 요청을 계속 처리 가능 (기존 데이터 유지)
다만 Primary에서 60초간 발생한 변경이 지연되어 반영됨
seconds_behind_master: 약 60~120초 일시 증가 후 빠르게 0으로 수렴2. 시나리오: Primary 장애, Replica를 새 Primary로 승격
장애 발생 및 감지:
1. Primary 다운
2. 모니터링 알림 (mysqld_exporter: mysql_up{job="mysql-primary"} = 0)
3. Orchestrator / MHA / ProxySQL 등 HA 도구가 Failover 시작
Failover 절차 (GTID 환경):
Step 1: IO Thread만 중단, SQL Thread는 Relay Log 소진 대기
STOP REPLICA IO_THREAD; -- 새 이벤트 수신 중단 (SQL Thread는 계속 실행)
Step 2: 남은 Relay Log 모두 실행 (중요!)
-- SQL Thread가 수신된 모든 이벤트를 실행 완료했는지 확인
SHOW REPLICA STATUS\G
-- Exec_Master_Log_Pos = Read_Master_Log_Pos 인지 확인
-- 아니라면 SQL Thread가 따라잡을 때까지 대기
Step 3: Replica를 Primary로 전환
STOP REPLICA; -- IO Thread + SQL Thread 모두 중단 (Step 1에서 IO만 멈췄으므로)
RESET REPLICA ALL; -- 복제 메타데이터 완전 삭제
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;
Step 4: 애플리케이션 연결 전환
Spring: primary datasource URL을 새 Primary(구 Replica) 주소로 변경
또는: ProxySQL/HAProxy가 자동으로 트래픽 전환
Step 5: 구 Primary 복구 후 새 Replica로 편입 (선택사항)
-- 구 Primary의 데이터 상태 확인 (gtid_executed 비교)
-- 필요시 Clone Plugin으로 재동기화
-- CHANGE REPLICATION SOURCE TO (새 Primary 주소);
-- START REPLICA;
핵심 위험: Split-Brain (두 서버가 모두 Primary로 동작)
→ 구 Primary가 복구됐을 때 애플리케이션이 여전히 구 Primary에 쓰기를 보내면
→ 두 서버에 서로 다른 트랜잭션 발생 → 나중에 합치기 매우 어렵거나 불가
→ 해결: 구 Primary가 복구돼도 네트워크 레벨에서 격리 (STONITH, Fencing)3. 시나리오: Replica에서 복제 오류 발생
흔한 복제 오류 유형:
① Duplicate entry (Error 1062)
원인: Replica에 이미 존재하는 PK를 Primary가 INSERT하려 함
근본 원인: Replica에 누군가 직접 쓰기를 해서 데이터 불일치 발생
② Row not found (Error 1032)
원인: Replica에 없는 행을 UPDATE/DELETE하려 함
근본 원인: 이전에 일부 이벤트가 누락되었거나 Replica 직접 쓰기
③ GTID 비호환 SQL 실행 오류 (Error 1800번대)
원인: GTID 환경에서 허용되지 않는 SQL 패턴 실행
예: CREATE TABLE ... SELECT (Error 1786, ER_GTID_UNSAFE_CREATE_SELECT)
트랜잭션 내 CREATE TEMPORARY TABLE (Error 1787)
※ Error 1759 (ER_BINLOG_STMT_MODE_AND_ROW_ENGINE)는 별개:
binlog_format=ROW 환경에서 statement-only 스토리지 엔진(MEMORY 등)을 건드릴 때 발생
오류 확인:
SHOW REPLICA STATUS\G
Last_Error: ...
Last_SQL_Error_Timestamp: ...
임시 우회 (권장하지 않음, 원인 파악 후 사용):
⚠️ GTID 모드(gtid_mode=ON)에서는 replica_skip_counter가 동작하지 않음!
MySQL 공식 문서: "SET GLOBAL sql_slave_skip_counter is not supported when GTID_MODE is ON"
GTID 모드에서 에러 이벤트를 건너뛰는 방법:
STOP REPLICA;
-- 에러 난 트랜잭션의 GTID를 확인 (SHOW REPLICA STATUS의 Executed_Gtid_Set 참고)
-- 해당 GTID를 "이미 실행된 것처럼" 빈 트랜잭션으로 소비
SET GTID_NEXT = 'uuid-A:오류_시퀀스번호';
BEGIN; ROLLBACK; -- 빈 트랜잭션으로 해당 GTID 소진
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;
→ 데이터 불일치를 감수하고 계속 진행 (원인 분석이 우선)
올바른 해결:
1. 원인 파악: 누가, 언제, 어떤 데이터를 Replica에 직접 썼는가?
2. Primary 기준으로 Replica 데이터를 수정
3. 복제 재시작
또는
4. Primary에서 Clone/Dump로 Replica 전체 재동기화4. Errant Transaction: 가장 위험한 상황
Errant Transaction: Replica에만 존재하고 Primary에는 없는 트랜잭션
발생 원인:
- super_read_only = OFF 상태에서 Replica에 직접 쓰기
- 복구 과정에서 실수로 Replica에 DML 실행
상황 예시:
Primary gtid_executed: uuid-A:1-1000
Replica gtid_executed: uuid-A:1-1000, uuid-B:1 ← B는 Replica 자신의 uuid
"uuid-B:1"이 Errant Transaction
위험성:
이 Replica를 Primary로 승격 시:
다른 Replica들: "나는 uuid-A:1-1000만 있어. uuid-B:1은 없어"
새 Primary: "uuid-B:1을 너네한테 복제해줄게"
→ 원래 있어서는 안 될 데이터가 모든 Replica에 전파됨
탐지:
-- Primary에서:
SELECT @@global.gtid_executed; -- a:1-1000
-- Replica에서:
SELECT @@global.gtid_executed; -- a:1-1000, b:1 ← b:1이 Errant!
제거 (신중하게):
-- Replica에서:
STOP REPLICA;
-- Errant GTID를 "이미 실행된 것처럼" 속임
SET GTID_NEXT = 'b:1';
BEGIN; ROLLBACK; -- 빈 트랜잭션으로 해당 GTID를 소모
SET GTID_NEXT = 'AUTOMATIC';
-- 이 Replica의 gtid_executed에서 b:1이 사라짐
-- (실제로는 b:1 트랜잭션을 undone하는 것이 아님, 단지 GTID를 소진시킴)
START REPLICA;
이런 장애들이 발생하기 전에 먼저 감지할 수 있다면 가장 좋다. 그 첫 번째 지표가 seconds_behind_master인데, 이 숫자가 생각보다 믿을 만하지 않다.
모니터링: seconds_behind_master
1. seconds_behind_master가 실제로 측정하는 것
seconds_behind_master의 계산식은 단순하다.
seconds_behind_master = UNIX_TIMESTAMP() - timestamp_of_current_event
// timestamp_of_current_event:
// SQL Thread가 지금 실행 중인 이벤트의 Primary 커밋 시각
// (Binlog의 이벤트 헤더의 timestamp 필드)여기서 결정적인 전제가 있다. 이 계산은 SQL Thread가 이벤트를 실행하는 동안에만 이루어진다.
SQL Thread가 Relay Log의 이벤트를 모두 처리하고 idle 상태가 되면, MySQL은 0을 반환한다.
따라서 이 지표가 실제로 측정하는 것은:
"SQL Thread가 I/O Thread를 얼마나 따라가지 못하고 있는가"
Primary와 Replica 사이의 실제 데이터 차이가 아니다.
2. 0이 믿을 수 없는 세 가지 이유
문제 1: 0이어도 Primary와 Replica가 동일하다는 보장이 없다
다음 두 상황 모두 seconds_behind_master = 0을 반환한다:
상황 A: Primary에 1시간째 쓰기 없음 → Replica 완전 정상
상황 B: Replica가 1시간치 지연 후 방금 따라잡음 → 직전까지 문제였음
이유: SQL Thread가 Relay Log를 모두 처리하면 무조건 0
"Primary가 조용한 것"인지 "Replica가 방금 따라잡은 것"인지 구분 불가문제 2: Primary → I/O Thread 구간의 지연이 보이지 않는다
복제 지연의 실제 경로:
Primary 커밋 → [네트워크] → IO Thread 수신 → Relay Log → SQL Thread 실행
seconds_behind_master가 측정하는 구간:
↑──────────────────────────────↑
IO Thread 이후 구간만 측정
예: 네트워크 이상으로 IO Thread가 3분치 이벤트를 못 받은 상황
→ SQL Thread: Relay Log에 있는 것 모두 처리 완료, idle
→ seconds_behind_master = 0
→ 실제 end-to-end 지연 = 3분문제 3: 시계 동기화에 민감하다
계산식이 Primary의 이벤트 timestamp와 Replica의 현재 시각을 비교하므로
두 서버의 시계가 어긋나면 계산값도 어긋난다.
Primary 시계가 Replica보다 1분 빠른 경우:
최근 이벤트 timestamp - Replica 현재 시각 = 음수 → MySQL이 0으로 처리
→ 실제 지연이 있어도 0으로 보임
NTP/Chrony로 두 서버의 시계를 동기화해야 한다.3. 정확한 복제 지연 측정
seconds_behind_master의 한계를 보완하는 방법들:
방법 1: Heartbeat 테이블 (pt-heartbeat)
-- Percona Toolkit의 pt-heartbeat:
-- Primary에 heartbeat 테이블을 만들고, 1초마다 현재 시각을 UPDATE
-- Replica에서 그 값을 읽어 현재 시각과 비교
-- Primary:
CREATE TABLE heartbeat (
server_id int NOT NULL PRIMARY KEY,
ts varchar(26) NOT NULL
);
-- pt-heartbeat (Primary 측):
-- 1초마다: UPDATE heartbeat SET ts=NOW(6) WHERE server_id=1;
-- Replica 측 지연 계산:
SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1e6 AS lag_seconds
FROM heartbeat WHERE server_id=1;
-- 이 방식의 장점:
-- 실제 end-to-end 지연 (Primary 쓰기 → Replica 읽기 가능 시점)을 측정
-- seconds_behind_master가 놓치는 IO Thread 구간까지 포함
-- 단: Primary와 Replica의 시계가 동기화되어 있어야 측정값이 정확함
-- 단: Primary에 쓰기 부하가 있어야 측정 의미 있음 (부하 없으면 stale)방법 2: GTID 기반 차이 측정
-- ① IO Thread 수신 지연 확인 (Replica에서 실행)
-- "IO Thread가 수신했지만 SQL Thread가 아직 실행하지 않은 GTID"
SELECT GTID_SUBTRACT(
(SELECT RECEIVED_TRANSACTION_SET
FROM performance_schema.replication_connection_status),
@@global.gtid_executed -- 이 서버(Replica)가 실행한 GTID
) AS sql_thread_backlog;
-- 이 값이 비어있으면 SQL Thread가 IO Thread를 완전히 따라잡은 것
-- 비어있지 않으면 SQL Thread가 아직 처리하지 못한 트랜잭션 목록
-- ② Primary 기준 미수신 GTID 확인 (두 서버에 별도 접속 후 비교)
-- Step 1: Primary에서 실행
SELECT @@global.gtid_executed; -- 예: uuid-A:1-1500
-- Step 2: Replica에서 실행 (Step 1 결과를 대입)
SELECT GTID_SUBTRACT(
'uuid-A:1-1500', -- Primary에서 가져온 값을 직접 대입
(SELECT RECEIVED_TRANSACTION_SET
FROM performance_schema.replication_connection_status)
) AS not_yet_received;
-- 비어있으면 IO Thread가 Primary의 모든 GTID를 수신한 것4. 모니터링 핵심 지표 목록
복제 상태 핵심 지표:
지표 정상값 임계값 의미
───────────────────────────────────────────
Seconds_Behind_Master 0~2초 > 10초 SQL Thread 지연
Replica_IO_Running (≥8.0.26) Yes No IO Thread 다운
Replica_SQL_Running (≥8.0.26) Yes No SQL Thread 다운
Relay_Log_Space (bytes) 작을수록 > 1GB Relay Log 누적
Read_Master_Log_Pos - 0 > 1MB IO Thread 미처리
Exec_Master_Log_Pos (bytes diff)
mysqld_exporter 지표:
mysql_slave_status_seconds_behind_master
mysql_slave_status_slave_io_running
mysql_slave_status_slave_sql_running
mysql_slave_status_relay_log_space
Grafana 알림 예시:
seconds_behind_master > 10 for 2m → Warning
seconds_behind_master > 30 for 1m → Critical
slave_io_running == 0 → 즉시 알림
slave_sql_running == 0 → 즉시 알림5. 복제 지연이 갑자기 치솟는 주요 원인
원인 1: Large Transaction (가장 흔함)
Primary에서 대용량 UPDATE/DELETE 실행
예: UPDATE posts SET status='archived' WHERE created_at < '2025-01-01'
→ 100만 행 업데이트 → 1개 트랜잭션, 대용량 ROW 이벤트
→ SQL Thread가 이 하나의 트랜잭션을 처리하는 동안 다른 것 처리 불가
→ seconds_behind_master = 해당 트랜잭션 크기 / Replica 처리 속도
해결: 대량 DML을 작은 배치로 분할
DELETE FROM posts WHERE created_at < '2025-01-01' LIMIT 1000;
(반복)
원인 2: Non-parallelizable Transactions
last_committed가 모두 다른 순차 트랜잭션 → 병렬화 불가
replica_parallel_workers가 있어도 직렬 실행
원인 3: Secondary Index 업데이트 폭풍
Primary: Primary Key 기준으로 빠르게 처리
Replica: Secondary Index (FULLTEXT 포함) 업데이트가 느림
→ Change Buffer가 꽉 차면 I/O 폭주
원인 4: Lock Contention on Replica
Replica에서 SELECT 쿼리가 InnoDB 잠금을 보유 중
SQL Thread의 DML이 잠금 대기 → seconds_behind_master 증가
→ 읽기 트랜잭션도 최대한 짧게 유지 필요지연의 원인을 파악했다고 해서 끝이 아니다. 지표상 아무 문제가 없어 보여도 운영 중에 복제를 조용히 망가뜨리는 함정들이 있다.
운영에서 놓치기 쉬운 함정들
1. auto.cnf와 server_uuid의 함정
상황: Docker 컨테이너를 삭제하고 재생성
컨테이너 삭제 시 /var/lib/mysql이 함께 삭제되면:
→ auto.cnf 파일도 삭제
→ MySQL 재시작 시 새로운 server_uuid 자동 생성
→ 기존 GTID 이력 소실
결과:
Primary: uuid-A:1-1000 (기존 uuid)
Replica (재생성): uuid-A-NEW:0 (새 uuid)
→ "새 서버"로 인식, 처음부터 복제 시작
만약 Relay Log나 gtid_executed가 남아있으면 혼란 발생
Docker에서 안전한 방법:
/var/lib/mysql을 Named Volume으로 마운트
→ 컨테이너 삭제/재생성해도 데이터 및 auto.cnf 유지
# docker-compose.yml
volumes:
mysql: {} # Named volume
services:
mysql:
volumes:
- mysql:/var/lib/mysql # 항상 동일한 volume 사용2. binlog_expire_logs_seconds와 Replica 지연의 상호작용
위험한 상황:
binlog_expire_logs_seconds = 86400 (1일)
Replica가 복제 연결은 유지하지만 SQL Thread가 심각하게 뒤처진 경우
예: Replica의 seconds_behind_master = 25시간
Primary의 binlog_expire_logs_seconds = 1일
→ "연결이 되어있는 Replica가 읽는 Binlog는 삭제 안 한다" (MySQL 보호 로직)
→ Primary의 Binlog가 쌓이고 쌓여 디스크 가득 참
→ Primary 디스크 꽉 차면 새 트랜잭션 커밋 불가 → 서비스 장애!
모니터링 포인트:
Primary의 디스크 사용량 (특히 /var/lib/mysql)
mysql_global_status_bytes_sent (Binlog 전송량 추이)
Relay_Log_Space on Replica
대응:
Replica 지연이 과도하면 복제를 일시 중단하고 Full Re-sync 고려
STOP REPLICA;
→ Primary Binlog가 더 이상 보호받지 않아 정상 삭제됨
→ Full dump 후 재동기화3. Replica에서 SELECT가 느린 이유: SQL Thread Lock Contention
일반적인 기대:
Replica는 읽기 전용이므로 잠금 경합이 없을 것이다?
실제:
SQL Thread가 UPDATE/DELETE를 실행하면 InnoDB 행 잠금을 보유함
읽기 쿼리(SELECT)가 같은 행을 읽으려 하면 잠금 대기 발생
→ seconds_behind_master가 낮은데도 특정 SELECT가 느린 경우 의심
※ Semi-Consistent Read (InnoDB UPDATE 최적화)와는 다른 개념:
Semi-Consistent Read = UPDATE 실행 시 잠금 경합 행의 최신 커밋 버전을
먼저 읽어 WHERE 조건 불일치 시 즉시 잠금 해제하는 기능
→ 이 섹션의 주제는 SQL Thread가 걸어놓은 잠금을 SELECT가 기다리는 현상
해결책:
1. transaction_isolation = READ-COMMITTED (MySQL 기본 REPEATABLE-READ)
→ Non-locking consistent read 범위가 넓어짐
2. replica_transaction_isolation = READ-UNCOMMITTED
→ SQL Thread의 트랜잭션 격리 수준을 낮춰 읽기 쿼리와 잠금 충돌 최소화
→ 단, SQL Thread 자체의 일관성에 영향 없음 (ROW 기반이므로)
3. 읽기 쿼리에 SELECT ... FOR SHARE 대신 일반 SELECT 사용
→ InnoDB MVCC에 의해 기존 버전 읽기 가능4. 복제와 DDL: Online DDL의 함정
Primary에서 Online DDL 실행:
ALTER TABLE posts ADD COLUMN view_count BIGINT DEFAULT 0;
(MySQL 8.0 Online DDL: 실행 중에도 DML 가능)
Replica에서의 동작:
DDL은 ROW 이벤트가 아닌 QUERY_EVENT로 전달됨
SQL Thread가 Replica에서 동일한 ALTER TABLE 실행
문제:
Primary: Online DDL으로 수 초만에 완료 (Concurrent DML 허용)
Replica: 동일 DDL이 테이블 잠금을 요구하는 경우
→ Replica의 다른 쿼리가 잠금 대기
→ SQL Thread가 DDL을 실행하는 동안 seconds_behind_master 급증
또 다른 문제: Replica에 SELECT 쿼리가 많은 경우
DDL이 Replica에서 실행될 때 Table Lock → 모든 SELECT 대기
→ "복제 지연은 없는데 왜 검색이 갑자기 느려지지?" 현상
해결:
pt-online-schema-change / gh-ost 같은 도구로 Replica 친화적 DDL 실행
또는 replica_skip_apply_schema_changes 활용 (특수 케이스)복제 성능 최적화: 병목을 계층별로 제거하는 방법
복제 지연은 단일 원인이 아니다. Primary 쓰기 처리 → Binlog 기록 → 네트워크 전송 → Relay Log 기록 → SQL Thread 실행까지 각 단계가 독립적인 병목이 될 수 있다. 계층별로 원인을 구분하고 최적화해야 한다.
1. Primary 측 최적화
Binlog 기록 최적화
| 설정 | 최적화 방향 | 기본값 | 권장값 | 효과 |
| sync_binlog | 1 유지 (안전) 또는 0 (성능) |
1 | Primary: 1 | 안전성 vs IOPS 트레이드오프 |
| binlog_cache_size | 트랜잭션이 크면 증가 | 32KB | 4MB | 디스크 임시 파일 없이 캐시 처리 |
| binlog_cache_size | 대형 트랜잭션 처리 | 18EB | 2GB | 초대형 배치 트랜잭션 한도 |
| binlog_transaction_compression | WAN 환경에서 ON | OFF | WAN: ON | 네트워크 전송량 70% 절감 |
| binlog_group_commit_sync_delay | 100~1000μs | 0 | 100~500 | Group Commit 그룹 크기 증가 |
| binlog_group_commit_sync_no_delay_count | 10~100 | 0 | 10 | 지연 없이 즉시 진행할 임계 수 |
binlog_cache_size 튜닝 방법:
SELECT
variable_value AS binlog_cache_use,
(SELECT variable_value FROM performance_schema.global_status
WHERE variable_name = 'Binlog_cache_disk_use') AS disk_use
FROM performance_schema.global_status
WHERE variable_name = 'Binlog_cache_use';
-- disk_use / cache_use 비율이 높으면 binlog_cache_size 증가 필요
-- 디스크 사용은 트랜잭션이 캐시보다 커서 임시 파일에 넘쳐나는 상황2. Replica 측 최적화
MTS Worker 수 튜닝
replica_parallel_workers 설정 기준:
너무 적으면: CPU 코어를 충분히 활용 못 해 처리량 제한
너무 많으면: Worker 간 락 경합, Coordinator 오버헤드, 메모리 낭비
권장 계산:
read-intensive 워크로드: CPU 코어 수의 50~75%
write-intensive 복제: CPU 코어 수 이하로 설정
검증 방법 (성능 스키마 활용):
SELECT
NAME,
PROCESSLIST_STATE,
PROCESSLIST_INFO
FROM performance_schema.threads
WHERE NAME LIKE '%worker%';
-- Worker들이 대부분 idle이면 workers 수 감소 또는 병렬화 불가 트랜잭션이 많음
-- Worker들이 대부분 active이면 workers 수 증가 고려Replica 전용 InnoDB 최적화
# Replica는 쓰기가 복제만이므로 내구성을 약간 희생해 성능 향상
# fsync 빈도 완화 (OS 크래시 시 최대 1초 손실, 재동기화로 복구)
innodb_flush_log_at_trx_commit = 2
# Relay Log fsync 완화 (relay_log_recovery로 복구 가능)
sync_relay_log = 10000
# Replica Binlog도 fsync 완화
sync_binlog = 0
# Change Buffer 최대화 (Secondary Index 업데이트 버퍼링)
innodb_change_buffer_max_size = 50 # 기본 25%, 최대 50%
# INSERT/UPDATE/DELETE가 Secondary Index를 많이 건드리는 복제에서 효과적Change Buffer 최적화 상세:
Change Buffer가 하는 일:
Secondary Index 페이지가 버퍼 풀에 없을 때(cache miss),
즉시 디스크에서 읽지 않고 변경 사항을 "나중에 처리" 버퍼에 기록
적용 예: posts 테이블의 category_id에 인덱스가 있을 때
INSERT INTO posts (id, title, category_id) VALUES (...)
→ Primary Index(PK) 업데이트: 즉시
→ Secondary Index(category_id): 버퍼 풀에 없으면 Change Buffer에 적립
→ 나중에 해당 페이지를 읽을 때 병합(merge)
Replica에서 복제 INSERT/UPDATE가 많으면 Change Buffer가 클수록 IOPS 절감
→ innodb_change_buffer_max_size = 50으로 늘려 더 많이 적립
확인:
SHOW ENGINE INNODB STATUS\G
→ "IBUF: size 1, free list len 0, seg size 2, 1234 merges"
→ merge 횟수가 많으면 Change Buffer 효과 있음3. 네트워크 계층 최적화
OS TCP 버퍼 튜닝
# 복제 연결의 OS 레벨 소켓 버퍼를 늘려 네트워크 처리량 향상
# /etc/sysctl.conf (호스트 OS, 컨테이너 환경에서는 호스트에 적용)
# 수신 버퍼 (Replica → Primary 방향 수신)
net.core.rmem_max = 134217728 # 128MB
net.core.rmem_default = 16777216 # 16MB
net.ipv4.tcp_rmem = 4096 16777216 134217728
# 송신 버퍼 (Primary → Replica 방향 송신)
net.core.wmem_max = 134217728
net.core.wmem_default = 16777216
net.ipv4.tcp_wmem = 4096 16777216 134217728
# TCP 백로그 큐 증가
net.core.netdev_max_backlog = 30000
net.ipv4.tcp_max_syn_backlog = 8192
# TIME_WAIT 재사용 (연결 끊김 후 빠른 재연결)
net.ipv4.tcp_tw_reuse = 1효과:
기본 버퍼(212KB)로는 고속 네트워크에서 TCP 윈도우 제한이 병목이 됨
1Gbps 링크 × 10ms RTT → 이론적 최대 처리량 = 1Gbps × 10ms / 8 = 1.25MB
기본 버퍼 212KB << 1.25MB → 버퍼 포화로 속도 제한
128MB 버퍼 → 처리량 100배 이상 개선 가능
AWS 내 두 인스턴스 복제 (< 1ms RTT): 버퍼 크기보다 PPS(패킷/초) 한계가 먼저 옴네트워크 대역폭 모니터링
# 복제 연결의 실시간 대역폭 확인
ss -tin | grep 3306
# PRIMARY에서 Replica로의 전송량
SHOW GLOBAL STATUS LIKE 'Bytes_sent';
# Replica에서 수신량
SHOW GLOBAL STATUS LIKE 'Bytes_received';4. 애플리케이션 수준 최적화
트랜잭션 크기와 복제 지연의 관계
가장 효과적인 복제 최적화는 애플리케이션 트랜잭션 크기를 줄이는 것이다.
대용량 트랜잭션의 복제 영향:
상황: 게시글 100만 개를 한 번에 삭제
DELETE FROM posts WHERE status = 'deleted';
Binlog에 기록되는 내용:
BEGIN
DELETE_ROWS_EVENT (1000만 바이트, 100만 행의 Before-image)
XID_EVENT (COMMIT)
→ 이 하나의 트랜잭션이 Relay Log에 도착할 때까지: IO Thread 블로킹
→ SQL Thread가 이 트랜잭션 처리: 다른 모든 트랜잭션 대기
→ seconds_behind_master 급등, 모든 읽기 쿼리 동시 지연
올바른 방법: 배치 분할
-- 1000건씩 나눠서 실행
WHILE affected_rows > 0:
DELETE FROM posts WHERE status = 'deleted' LIMIT 1000;
SLEEP 0.1; -- Replica가 따라잡을 여유
→ 각 트랜잭션: 10KB 이하
→ Replica 복제 지연: 거의 없음
→ 동시 읽기 쿼리: 영향 없음읽기 일관성 요구사항에 따른 라우팅
모든 읽기를 Replica로 보내면 안 되는 케이스:
케이스 1: 쓰기 직후 읽기
-- "좋아요를 눌렀더니 좋아요 수가 줄었다" 현상
POST /posts/1/like → Primary (쓰기)
GET /posts/1 → Replica (읽기, 아직 반영 안 됨)
해결: 해당 세션의 쓰기 후 N초 동안 읽기도 Primary로 routing
또는: 좋아요 수 같은 빠르게 변하는 값은 Redis 캐시 활용
케이스 2: 결제/재고 처리
재고 10개 남음 (Primary)
Replica에는 아직 12개로 표시 (복제 지연 2초)
두 사용자가 동시에 Replica 읽기 → 둘 다 "재고 있음" 확인 → 결제 → 재고 초과 주문
해결: 재고/결제는 반드시 Primary 읽기 (readOnly=false)
Spring 구현:
@Transactional(readOnly = true) // → Replica (일반 조회)
@Transactional(readOnly = false) // → Primary (결제, 재고 등 정확성 요구)5. 복제 성능 진단 쿼리 모음
-- 1. 현재 복제 병렬화 효율 확인
SELECT
THREAD_ID,
NAME,
PROCESSLIST_STATE AS state,
PROCESSLIST_INFO AS current_sql
FROM performance_schema.threads
WHERE NAME LIKE 'thread/sql/replica%'
ORDER BY NAME;
-- Worker들이 'Waiting for an event from Coordinator' 상태면
-- 병렬화 가능한 트랜잭션이 부족함 (Group Commit 늘려야)
-- 2. 복제 관련 대기 이벤트 상위
SELECT
EVENT_NAME,
COUNT_STAR AS count,
SUM_TIMER_WAIT / 1e12 AS total_wait_sec,
AVG_TIMER_WAIT / 1e12 AS avg_wait_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%replica%'
OR EVENT_NAME LIKE '%binlog%'
OR EVENT_NAME LIKE '%relay%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 3. Relay Log 처리 처리량 추이
SELECT
variable_name,
variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
'Replica_retried_transactions', -- 재시도 횟수 (락 경합 지표) [MySQL 8.0.26+, 구버전: Slave_retried_transactions]
'Replica_running' -- [MySQL 8.0.26+, 구버전: Slave_running]
);
-- 4. Group Commit 효율 확인 (binlog 분석)
-- mysqlbinlog 사용: last_committed 분포를 보면 병렬화 가능 그룹 크기 확인 가능
-- mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001 | grep last_committed
-- 5. 복제 지연 원인이 SQL Thread인지 IO Thread인지 판별
SHOW REPLICA STATUS\G
-- Seconds_Behind_Master > 0 이고
-- Master_Log_File == Relay_Master_Log_File 이면 → IO Thread는 따라잡음, SQL Thread가 병목
-- Master_Log_File != Relay_Master_Log_File 이면 → IO Thread도 뒤처짐 (네트워크/디스크)정리
MySQL Primary-Replica 복제는 단순한 "데이터 복사"가 아니다.
| 계층 | 핵심 |
| 네트워크 | TCP 3306, MySQL Binary Protocol, COM_BINLOG_DUMP_GTID, 지속 스트리밍 |
| IO Thread | 단일 스레드, COM_REGISTER_REPLICA, Heartbeat, Relay Log 순차 기록 |
| Relay Log | Binlog와 동일 포맷, IO Thread/SQL Thread 분리 버퍼, relay_log_recovery |
| SQL Thread (MTS) | Coordinator + Workers, LOGICAL_CLOCK으로 last_committed 기준 병렬화, preserve_commit_order |
| GTID | 전역 고유 트랜잭션 ID, mysql.gtid_executed 영구 저장, Failover 자동화 |
| 장애 복구 | 재시작 시 gtid_executed 기준 자동 재개, Binlog purge 초과 시 Full Re-sync |
| 모니터링 | seconds_behind_master의 한계 인지, IO Thread 지연은 별도 측정, pt-heartbeat |
| 운영 함정 | auto.cnf 손실, Binlog 보호로 인한 Primary 디스크 포화, Errant Transaction, DDL 잠금 |
| 성능 최적화 | Group Commit 크기 조정, Change Buffer, MTS Worker 튜닝, 트랜잭션 크기 분할 |
복제는 설정보다 운영 중 발생하는 예외 상황을 얼마나 잘 이해하는가가 더 중요하다.
'데이터베이스 > MySql' 카테고리의 다른 글
| [MySQL] 트랜잭션 격리가 구현되는 원리 — InnoDB MVCC (0) | 2026.03.12 |
|---|---|
| [MySQL] Container MySQL Primary-Replica 셋팅 (0) | 2026.02.25 |