Database Replication Lag 완벽 가이드: 프로덕션 30초 임계값과 2025 최신 모니터링 전략

[Primary Database]
 ↓ (Replication)
 ↓ (Write Ahead Log)
 ↓
[Replica Database]

용도:
- Read 부하 분산 (Primary: Write, Replica: Read)
- High Availability (Primary 장애 시 Replica로 Failover)
- Backup (Replica에서 백업 수행)
- Analytics (Replica에서 무거운 쿼리 실행)

Replication Lag = 현재 시간 - Replica가 마지막으로 적용한 트랜잭션의 타임스탬프

예시:
- Primary: 트랜잭션 T1 커밋 (14:00:00)
- Replica: T1 적용 완료 (14:00:05)
- Replication Lag: 5초

 정상:
- Lag: 0~2초 (거의 실시간)
- Primary와 Replica가 거의 동기화
- Failover 안전

️ 경고:
- Lag: 5~30초
- Stale Read 발생 가능
- Failover 시 일부 데이터 손실 가능

 위험:
- Lag: 30초 이상
- Failover 불가 (데이터 손실 위험)
- 사용자 경험 저하
- Replica 재구축 필요 가능

-- Primary에서 실행 (10초 소요)
UPDATE products
SET inventory = inventory - 1
WHERE category = 'electronics'; -- 100만 개 상품 업데이트 (인덱스 없음)

-- 문제:
-- 1. Primary에서 10초 소요
-- 2. Replication Log에 10초짜리 트랜잭션 기록
-- 3. Replica도 10초 동안 이 트랜잭션 처리
-- 4. 그동안 쌓이는 다른 트랜잭션들
-- → Lag 급증!

T=0초: Primary에서 대량 UPDATE 시작
T=5초: Lag = 5초 (Replica가 처리 시작)
T=10초: Lag = 10초 (Replica 처리 중)
T=15초: Lag = 15초 (아직 처리 중, 새 트랜잭션 누적)
T=20초: Lag = 20초
T=25초: Lag = 25초 (다른 트랜잭션 대기)
T=30초: Lag = 30초 ( Red Flag!)

→ Failover 불가 상태!

-- 배치 크기 제한 + 인덱스 사용
-- 한 번에 1,000개씩만 업데이트
UPDATE products
SET inventory = inventory - 1
WHERE category = 'electronics'
 AND id IN (
 SELECT id FROM products
 WHERE category = 'electronics'
 LIMIT 1000
 );

-- 인덱스 생성
CREATE INDEX idx_products_category ON products(category);

-- 결과:
-- - 각 배치 0.1초 소요
-- - 1,000번 반복해도 총 100초
-- - 하지만 각 트랜잭션은 0.1초 → Lag 누적 안 됨!
-- - Lag 유지: 1~2초

Primary (Seoul Region)
 ↓ (100 Mbps 네트워크)
 ↓ (50ms latency)
 ↓
Replica (Tokyo Region)

대량 트랜잭션:
- 초당 10,000 INSERT
- 각 레코드 1KB
- 총 10MB/s 전송 필요

하지만:
- 네트워크: 100 Mbps = 12.5 MB/s
- 실제 처리량: ~8 MB/s (오버헤드)
- 2 MB/s 부족!

결과:
- Replication Stream 병목
- Lag 계속 증가
- 복구 불가

-- PostgreSQL: Replication 지연 확인
SELECT
 client_addr,
 state,
 sent_lsn,
 write_lsn,
 flush_lsn,
 replay_lsn,
 pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 출력:
-- client_addr | state | lag_bytes
-- ------------|-----------|----------
-- 10.0.2.100 | streaming | 524288000 ← 500MB 뒤처짐!

-- lag_bytes가 계속 증가 = 네트워크 병목

1. 네트워크 업그레이드:
 - 100 Mbps → 1 Gbps
 - 또는 Direct Connect / VPN 전용선

2. Replica 위치 변경:
 - 같은 리전으로 이동
 - Latency: 50ms → 1ms

3. 압축 활성화:
 - PostgreSQL: wal_compression = on
 - MySQL: binlog_row_image = minimal

4. Replication Slot 크기 증가:
 - max_wal_size = 10GB (기본 1GB)

Primary (SSD: 10,000 IOPS)
 ↓
Replica (HDD: 200 IOPS) ← 병목!

Primary:
- 초당 5,000 트랜잭션 처리 (Write IOPS 충분)

Replica:
- 초당 200 트랜잭션만 처리 (Disk IOPS 부족)
- 4,800 트랜잭션 누적
- Lag 급증!

1초 후:
- Lag: 4,800 트랜잭션
- 계속 증가 중...

1. 디스크 업그레이드:
 - HDD → SSD
 - 200 IOPS → 10,000 IOPS

2. RAID 구성:
 - RAID 10 (성능 + 안정성)
 - Write 성능 2배 향상

3. Write-Ahead Log 최적화:
 - PostgreSQL: synchronous_commit = off (Replica에서)
 - MySQL: innodb_flush_log_at_trx_commit = 2

4. 병렬 Replication 활성화:
 - MySQL: slave_parallel_workers = 8
 - PostgreSQL: max_parallel_workers = 8

-- Primary에서 실행
SELECT
 application_name,
 client_addr,
 state,
 sync_state,
 pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
 (EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())))::INT AS lag_seconds
FROM pg_stat_replication;

-- 출력:
-- application_name | client_addr | state | sync_state | lag_bytes | lag_seconds
-- -----------------|-------------|-----------|------------|-----------|------------
-- replica1 | 10.0.2.100 | streaming | async | 524288 | 15

-- 해석:
-- - lag_bytes: 512KB 뒤처짐 (Replication Log)
-- - lag_seconds: 15초 지연 (시간 기준)
-- - sync_state: async (비동기 복제)

-- Replica에서 마지막 트랜잭션 적용 시간 확인
SELECT
 now() AS current_time,
 pg_last_xact_replay_timestamp() AS last_replay_time,
 now() - pg_last_xact_replay_timestamp() AS replication_lag
FROM pg_stat_replication;

-- 출력:
-- current_time | last_replay_time | replication_lag
-- ------------------------|-------------------------|----------------
-- 2025-11-04 15:30:45+00 | 2025-11-04 15:30:15+00 | 00:00:30

-- 30초 Lag!

-- Replica에서 실행
SHOW SLAVE STATUS\G

-- 주요 필드:
-- Slave_IO_Running: Yes ← I/O Thread 정상
-- Slave_SQL_Running: Yes ← SQL Thread 정상
-- Seconds_Behind_Master: 35 ← 35초 Lag!
-- Master_Log_File: mysql-bin.000123
-- Read_Master_Log_Pos: 154789
-- Relay_Log_Space: 524288000 ← 500MB Relay Log 누적

-- 문제 진단:
-- - Seconds_Behind_Master > 30: 위험!
-- - Relay_Log_Space 계속 증가: Replica 처리 느림

Seconds_Behind_Master = 현재 시간 - Relay Log의 마지막 트랜잭션 타임스탬프

예시:
- 현재 시간: 15:30:45
- Relay Log 마지막 트랜잭션: 15:30:10 (Primary에서 발생)
- Seconds_Behind_Master = 45 - 10 = 35초

-- Seconds_Behind_Master = NULL인 경우:
-- 1. Replication이 중단됨 (IO/SQL Thread 중 하나 멈춤)
-- 2. 네트워크 단절
-- 3. Replica 장애

-- 확인:
SHOW SLAVE STATUS\G

-- Slave_IO_Running: No ← 문제!
-- Slave_SQL_Running: Yes
-- Last_IO_Error: error connecting to master ← 네트워크 문제

Primary (8 Threads)
 ↓ (병렬 Write)
 ↓
Replication Log
 ↓
Replica (1 Thread) ← 병목!
 ↓ (순차 Apply)
 ↓
Slow!

문제:
- Primary: 초당 10,000 트랜잭션 (8 threads)
- Replica: 초당 2,000 트랜잭션 (1 thread)
- Gap: 8,000 트랜잭션/초
- Lag 계속 증가!

-- Replica에서 설정
STOP SLAVE;

SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK'; -- 논리 클럭 기반
SET GLOBAL slave_parallel_workers = 8; -- 8개 Worker Thread
SET GLOBAL slave_preserve_commit_order = 1; -- 커밋 순서 보존

START SLAVE;

-- 확인:
SHOW PROCESSLIST;

-- 출력:
-- Id | User | Command | State
-- ----|--------------|--------------|------------------
-- 10 | system user | Connect | Waiting for master
-- 11 | system user | Connect | Slave has read all relay log ← SQL Thread
-- 12 | system user | Connect | Waiting for an event from Coordinator ← Worker 1
-- 13 | system user | Connect | Waiting for an event from Coordinator ← Worker 2
--...
-- 19 | system user | Connect | Waiting for an event from Coordinator ← Worker 8

-- 8개 Worker Thread 동작 중!

Before (Single-Thread):
- Replica 처리량: 2,000 TPS

After (8 Threads):
- Replica 처리량: 12,000 TPS (6배 개선!)
- Lag: 35초 → 3초 (12배 개선!)

-- postgresql.txt
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
max_worker_processes = 16

-- Logical Replication Parallel Apply (PG 16+)
ALTER SUBSCRIPTION my_subscription
SET (streaming = 'parallel', parallel_workers = 8);

-- 확인:
SELECT
 subname,
 parallel_workers,
 stream
FROM pg_subscription;

-- 출력:
-- subname | parallel_workers | stream
-- ---------------|------------------|----------
-- my_subscription| 8 | parallel

Azure Portal → SQL Database → Metrics

Metric: Replication lag (seconds)
- 수집 주기: 1분
- 범위: Primary-Secondary 간 지연
- 단위: 초

알람 설정:
- Threshold: 30초
- Aggregation: Average (1분)
- Action: Email + PagerDuty

network_lag = Primary에서 전송한 시간 - Replica가 받은 시간

예시:
- Primary 전송: 15:30:00
- Replica 수신: 15:30:03
- network_lag: 3초

→ Lag의 원인이 네트워크인지 Replica 처리 속도인지 구분!

PMM UI → Dashboards → PostgreSQL Replication

Metrics:
- Replication Lag (seconds)
- Replication Lag (bytes)
- Replay LSN vs Sent LSN
- Replication Slot Size
- Sync State (async/sync)

Alerts:
- Lag > 30s: Critical
- Lag > 5s: Warning
- Replication Stopped: Critical

Async Replication (기본):
Primary → Write → Commit (즉시 반환) ← 빠름
 ↓ (나중에 전송)
Replica → Apply

장점: 빠름
단점: Primary 장애 시 Replica가 뒤처질 수 있음 (데이터 손실)

Sync Replication:
Primary → Write → Wait (Replica 확인 대기) ← 느림
 ↓ (실시간 전송)
Replica → Apply → ACK
 ↓
Primary → Commit (Replica 확인 후)

장점: 데이터 손실 없음
단점: 느림 (네트워크 Latency 영향)

-- postgresql.txt (Primary)
synchronous_commit = on -- 동기 커밋 활성화
synchronous_standby_names = 'replica1' -- 동기 Replica 지정

-- 재시작
pg_ctl restart

-- 확인:
SELECT
 application_name,
 sync_state,
 state
FROM pg_stat_replication;

-- 출력:
-- application_name | sync_state | state
-- -----------------|------------|----------
-- replica1 | sync | streaming ← 동기 복제!

-- 이제 Primary Commit은 replica1의 ACK를 기다림
-- → Lag = 0 (항상 동기화)
-- → Failover 안전 (데이터 손실 없음)

Async:
- Write Latency: 1ms
- Throughput: 10,000 TPS
- Lag: 1~5초
- Failover: 일부 데이터 손실 가능

Sync:
- Write Latency: 10ms (네트워크 RTT 추가)
- Throughput: 5,000 TPS (50% 감소)
- Lag: 0초 (항상 동기화)
- Failover: 데이터 손실 없음

import psycopg2

def check_failover_readiness():
 """Replica가 Failover 준비되었는지 확인"""

 # Primary 연결
 primary_conn = psycopg2.connect(
 host="primary.example.com",
 database="mydb",
 user="postgres"
 )

 cursor = primary_conn.cursor()

 # Replication Lag 확인
 cursor.execute("""
 SELECT
 application_name,
 pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
 EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::INT AS lag_seconds
 FROM pg_stat_replication
 WHERE application_name = 'replica1'
 """
 )

 result = cursor.fetchone()

 if not result:
 print("️ Replica not connected!")
 return False

 app_name, lag_bytes, lag_seconds = result

 print(f"Replica: {app_name}")
 print(f"Lag: {lag_bytes} bytes, {lag_seconds} seconds")

 # Failover 안전 임계값
 MAX_LAG_SECONDS = 30
 MAX_LAG_BYTES = 16 * 1024 * 1024 # 16MB

 if lag_seconds > MAX_LAG_SECONDS:
 print(f" Lag {lag_seconds}s > {MAX_LAG_SECONDS}s - Failover UNSAFE!")
 return False

 if lag_bytes > MAX_LAG_BYTES:
 print(f" Lag {lag_bytes} bytes > {MAX_LAG_BYTES} bytes - Failover UNSAFE!")
 return False

 print(" Failover SAFE - Lag within threshold")
 return True

# 사용
if check_failover_readiness():
 print("Proceeding with failover...")
 # perform_failover()
else:
 print("Failover aborted - Lag too high")

-- postgresql.txt (Primary)
wal_level = replica -- Replication 활성화
max_wal_senders = 10 -- 최대 10개 Replica 지원
wal_keep_size = 1024MB -- 1GB WAL 보관 (네트워크 단절 대비)
max_replication_slots = 10 -- Replication Slot
wal_compression = on -- WAL 압축 (네트워크 절약)

-- postgresql.txt (Replica)
hot_standby = on -- Read 쿼리 허용
max_standby_streaming_delay = 30s -- 최대 30초 Lag 허용
wal_receiver_status_interval = 10s -- 10초마다 상태 보고

-- my.cnf (Primary)
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
sync_binlog = 1 -- 안전성 (성능 저하)
binlog_row_image = MINIMAL -- 네트워크 절약

-- my.cnf (Replica)
server-id = 2
relay-log = relay-bin
read_only = 1 -- Read-Only 모드
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8 -- 8 Thread 병렬 처리
slave_preserve_commit_order = 1

{
 "dashboard": {
 "title": "Database Replication Monitoring",
 "panels": [
 {
 "title": "Replication Lag (seconds)",
 "targets": [
 {
 "expr": "pg_replication_lag_seconds",
 "legendFormat": "{{instance}}"
 }
 ],
 "thresholds": [
 { "value": 5, "color": "yellow" },
 { "value": 30, "color": "red" }
 ]
 },
 {
 "title": "Replication Lag (bytes)",
 "targets": [
 {
 "expr": "pg_replication_lag_bytes",
 "legendFormat": "{{instance}}"
 }
 ]
 }
 ]
 }
}

정상: Lag 1~2초
갑자기: Lag 120초

-- 1. Primary에서 느린 쿼리 확인
SELECT
 pid,
 now() - query_start AS duration,
 state,
 query
FROM pg_stat_activity
WHERE state = 'active'
 AND now() - query_start > INTERVAL '10 seconds'
ORDER BY duration DESC;

-- 출력:
-- pid | duration | state | query
-- ------|----------|--------|---------------------------
-- 12345 | 00:02:15 | active | UPDATE products SET... ← 2분째 실행 중!

-- 2. Replication Slot 크기 확인
SELECT
 slot_name,
 pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

-- 출력:
-- slot_name | lag_size
-- ----------|---------
-- replica1 | 2048 MB ← WAL 2GB 누적!

-- 느린 쿼리 강제 종료
SELECT pg_terminate_backend(12345);

-- 30초 후 Lag 확인
-- Lag: 120초 → 5초 (복구됨!)

pg_stat_replication 뷰에 Replica 없음