MySQL 복제 지연 해결: 일반적인 원인과 해결 방법

MySQL 복제 지연은 복제본이 소스보다 뒤쳐져 있음을 의미하며, 해당 복제본에서 읽은 데이터가 오래되었거나 장애 조치 시 최신 상태가 아닐 수 있습니다. 일반적인 원인은 간단히 언급할 수 있지만 잘못 해석하기 쉽습니다: 느린 릴레이 로그 가져오기, 느린 트랜잭션 적용, 과부하된 디스크, 긴 소스 트랜잭션, 스키마 드리프트, 또는 쓰기 워크로드와 일치하지 않는 복제 설정 등이 있습니다.

이 가이드는 일반적으로 먼저 확인해야 할 사항을 안내합니다: 복제 상태, I/O, 장기 트랜잭션, 스키마 드리프트 및 병렬 적용 설정.

MySQL 복제 간략히 이해하기

문제 해결에 들어가기 전에 MySQL 복제의 기본 흐름을 이해하는 것이 도움이 됩니다:

소스의 바이너리 로그 (Binlog): 소스 서버에서 데이터를 수정하는 모든 명령문(DML) 및 스키마 변경(DDL)은 바이너리 로그에 기록됩니다. 이 로그는 모든 변경 사항의 시간순 기록 역할을 합니다.
복제본의 I/O 스레드: 복제본의 전용 I/O 스레드는 소스 서버에 연결하여 바이너리 로그 이벤트를 요청합니다. 그런 다음 이러한 이벤트를 복제본의 로컬 파일인 릴레이 로그에 복사합니다.
복제본의 SQL 스레드: 복제본의 또 다른 전용 스레드인 SQL 스레드는 릴레이 로그에서 이벤트를 읽고 복제본 데이터베이스에서 실행하여 변경 사항을 적용함으로써 소스와 동기화 상태를 유지합니다.

복제 지연은 I/O 스레드가 소스에서 이벤트를 가져오는 속도를 따라가지 못하거나, 더 일반적으로는 SQL 스레드가 릴레이 로그에서 이벤트를 적용하는 속도를 따라가지 못할 때 발생합니다.

복제 지연 진단

복제 상태 및 지연을 확인하는 주요 도구는 복제본 서버에서 SHOW REPLICA STATUS (또는 이전 MySQL 버전에서는 SHOW SLAVE STATUS) 명령입니다.

SHOW REPLICA STATUS\G

출력에서 확인해야 할 주요 지표:

Replica_IO_Running 또는 이전 Slave_IO_Running: MySQL 버전에 따라 Yes여야 합니다.
Replica_SQL_Running 또는 Slave_SQL_Running: Yes여야 합니다.
Seconds_Behind_Source 또는 이전 Seconds_Behind_Master: 이벤트 타임스탬프를 기준으로 지연 시간(초)을 추정합니다. 0보다 큰 값은 지연을 나타내지만 적용되지 않은 트랜잭션 수를 나타내지는 않습니다.
Last_IO_Error: 네트워크 또는 I/O 관련 오류.
Last_SQL_Error: 이벤트 적용 중 발생한 오류.

지연 시간에 관한 중요 참고 사항: 이 지표는 시간 기반이지 트랜잭션 기반이 아닙니다. 소스가 오래된 이벤트 타임스탬프를 가진 대규모 트랜잭션을 커밋하면 복제본이 해당 트랜잭션을 적용하는 동안 큰 지연 값을 보고할 수 있습니다. 대기 중인 트랜잭션 수를 알려주지 않으므로 릴레이 로그 크기, 적용 스레드 상태 및 서버 지표와 함께 사용해야 합니다.

고급 모니터링을 위해 Percona Monitoring and Management (PMM), Prometheus with Grafana 또는 시간 경과에 따른 복제 지표를 추적하는 기타 데이터베이스별 모니터링 솔루션 사용을 고려하십시오.

복제 지연의 일반적인 원인 및 해결 방법

근본 원인을 식별하는 것이 중요합니다. 다음은 복제 지연의 가장 빈번한 원인과 해당 해결 방법입니다:

1. 네트워크 지연 또는 대역폭 문제

원인: 소스와 복제본 간의 느리거나 불안정한 네트워크 연결, 또는 바이너리 로그 이벤트를 빠르게 전송하기에 충분하지 않은 네트워크 대역폭.
진단: 복제본 I/O 스레드가 실행 중이지만 Relay_Log_Space가 크게 증가하지 않거나 네트워크 문제와 관련된 빈번한 Last_IO_Error 항목이 있는 경우 높은 지연 시간. ping, mtr 또는 traceroute와 같은 네트워크 진단 도구를 사용하여 지연 시간 및 패킷 손실을 확인합니다.
해결 방법:
- 네트워크 인프라 개선: 서버 간 안정적이고 높은 대역폭의 연결을 보장합니다.
- 서버 공동 배치: 이상적으로는 소스와 복제본이 동일한 데이터 센터 또는 클라우드 리전에 있어 지연 시간을 최소화합니다.
- 압축: 대역폭이 제한된 링크의 경우 MySQL 버전의 복제 연결 압축 옵션을 확인하십시오. 압축은 네트워크 트래픽을 줄일 수 있지만 CPU 오버헤드가 추가되며 복제본을 소스 근처에 배치하는 것을 대체할 수 없습니다.

2. 복제본의 I/O 병목 현상

원인: 복제본의 디스크 하위 시스템이 릴레이 로그를 쓰거나 데이터 파일에 변경 사항을 적용할 만큼 빠르지 않습니다. 이는 특히 sync_binlog 또는 innodb_flush_log_at_trx_commit이 1(최대 내구성)로 설정된 경우에 해당하며, 빈번한 디스크 플러시를 유발합니다.
진단: 복제본에서 top 또는 vmstat 출력의 높은 iowait, 높은 디스크 사용률(iostat -x 1), 그리고 꾸준히 증가하는 지연 시간. Innodb_data_writes 및 Innodb_data_fsyncs와 같은 MySQL 상태 변수도 통찰력을 제공할 수 있습니다.
해결 방법:
- 더 빠른 스토리지: 복제본에 SSD 또는 NVMe 드라이브로 업그레이드합니다. 성능을 위해 적절한 RAID 구성을 사용합니다(예: RAID 10).
- 내구성 설정 조정(주의해서!) :
  - innodb_flush_log_at_trx_commit: 기본값은 1(가장 내구성 있음). 2(OS 캐시로 플러시) 또는 0(초당 한 번 플러시)로 설정하면 I/O를 크게 줄일 수 있지만 복제본 충돌 시 데이터 손실 위험이 있습니다. 복제본이 기본 진실 공급원이 아니고 복제본 자체의 일부 데이터 손실을 감당할 수 있는 경우에만 0 또는 2를 고려하십시오.
  - 복제본이 바이너리 로그도 쓰는 경우 sync_binlog는 복제본에 플러시 오버헤드를 추가할 수 있습니다. 이를 완화하면 처리량이 향상될 수 있지만 서버 충돌 시 최근 바이너리 로그 이벤트를 잃을 가능성이 높아집니다.
```
# 복제본의 /etc/my.cnf 설정 예시 (매우 주의해서 사용)
[mysqld]
innodb_flush_log_at_trx_commit = 2 # 또는 0, 허용 범위에 따라
```

3. 복제본의 리소스 경합 (CPU, 메모리)

원인: 복제본 서버의 CPU 또는 메모리가 들어오는 트랜잭션을 처리하고 적용하기에 충분하지 않습니다. 특히 읽기 쿼리도 제공하는 경우 더욱 그렇습니다.
진단: top 또는 htop에서 높은 CPU 사용률(특히 mysqld 프로세스), 또는 높은 메모리 사용량. 지연 시간이 높고 복제본 SQL 스레드 상태에 장기 실행 명령문이 표시될 수 있습니다.
해결 방법:
- 리소스 증가: 복제본 서버에 더 많은 CPU 코어와 RAM을 프로비저닝합니다.
- 전용 복제본: 가능하면 복제본을 복제 전용으로 사용하고 무거운 읽기 쿼리를 제공하지 않도록 합니다. 읽기가 필요한 경우 적절한 인덱스로 잘 최적화되었는지 확인합니다.
- 쿼리 최적화: SQL 스레드와 리소스를 경합할 수 있는 복제본에서 실행되는 느린 쿼리를 검토하고 최적화합니다.

4. 소스의 느린 쿼리 또는 장기 트랜잭션

원인: 소스에서 단일하고 매우 크거나 오래 실행되는 트랜잭션(예: ALTER TABLE, LIMIT 없는 대규모 UPDATE/DELETE, 대규모 LOAD DATA INFILE)은 전체 기간 동안 복제본의 SQL 스레드를 차단하여 심각한 지연을 유발할 수 있습니다. 복제본은 소스에서 커밋된 방식과 동일하게 트랜잭션을 적용해야 하므로 시간이 오래 걸릴 수 있습니다.
진단: 지연 시간이 소스의 특정 작업과 상관 관계가 있는 갑작스러운 큰 스파이크를 보여줍니다. 이러한 이벤트 중에 소스의 느린 쿼리 로그 또는 SHOW PROCESSLIST를 확인합니다.
해결 방법:
- 소스 쿼리 최적화: 소스에서 장기 실행 쿼리를 식별하고 최적화합니다. 적절한 인덱스를 추가합니다.
- 일괄 작업: LIMIT 절을 사용하여 대규모 DELETE 또는 UPDATE 문을 더 작고 관리 가능한 배치로 나눕니다.
- 온라인 스키마 변경: DDL 작업의 경우 Percona Toolkit의 pt-online-schema-change와 같은 도구를 사용하여 비차단 스키마 수정을 수행하여 복제 중단을 최소화합니다.

5. 단일 스레드 복제 (MySQL 5.7 이전 또는 특정 구성)

원인: 이전 MySQL 버전에서는 SQL 스레드가 소스에서 발생한 병렬 트랜잭션 수에 관계없이 모든 트랜잭션을 순차적으로 적용했습니다. 소스가 많은 동시 쓰기를 처리하는 경우 복제본의 단일 SQL 스레드는 쉽게 병목 현상이 될 수 있습니다.
진단: 높은 지연 시간 및 복제본 SQL 스레드 상태가 활성 쿼리를 자주 표시하는 반면 복제본의 CPU는 모든 코어에서 완전히 포화되지 않을 수 있습니다.
해결 방법:
- 다중 스레드 복제: 하나의 복제본 SQL 스레드가 소스의 동시 쓰기를 따라잡을 수 없을 때 병렬 적용이 도움이 될 수 있습니다. MySQL 5.6은 데이터베이스 기반 병렬 처리를 도입했으며 이후 버전에서는 논리적 클록 기반 병렬 적용을 추가했습니다. 최신 MySQL 버전은 replica_parallel_workers 용어를 사용하는 반면 이전 구성은 여전히 slave_parallel_workers를 사용할 수 있습니다.
```
# MTS를 위한 복제본의 /etc/my.cnf 설정 예시
[mysqld]
replica_parallel_workers = 4 # 적당히 시작한 후 측정
replica_parallel_type = LOGICAL_CLOCK
replica_preserve_commit_order = ON # 읽기에 커밋 순서가 중요할 때 유용
```
- 복제 다시 시작: MTS 설정을 변경한 후 복제본 SQL 스레드를 다시 시작해야 합니다:
```
STOP REPLICA;
START REPLICA;
```

6. 최적화되지 않은 스키마 또는 복제본의 누락된 인덱스

원인: 복제본의 스키마가 소스와 다르거나 필수 인덱스가 없는 경우 SQL 스레드가 적용하는 쿼리가 소스보다 훨씬 느리게 실행될 수 있습니다. 이는 스키마 드리프트 또는 의도적인 차이(예: 복제본의 다른 보고 인덱스)로 인해 발생할 수 있습니다.
진단: CPU/I/O 병목 현상과 유사하지만 복제본 SQL 스레드 상태의 특정 쿼리 또는 복제본의 느린 쿼리 로그가 문제를 나타낼 수 있습니다. 소스와 복제본에서 동일한 쿼리에 대한 EXPLAIN 계획을 비교합니다.
해결 방법:
- 스키마 일관성: 복제본이 모든 필수 인덱스를 포함하여 소스와 동일하고 최적화된 스키마를 가지고 있는지 확인합니다.
- 인덱스 생성: 복제본에서 쿼리 성능(복제본에서 읽는 애플리케이션과 SQL 스레드 자체 모두)에 중요한 누락된 인덱스를 추가합니다.

7. 바이너리 로그 형식 (ROW vs. STATEMENT)

원인: STATEMENT 기반 복제는 비결정적 명령문(예: NOW(), UUID() 사용)이 복제본에서 다른 결과를 생성할 수 있어 복잡한 컨텍스트 평가가 필요하거나 복제를 중단시킬 수 있기 때문에 문제가 될 수 있습니다. ROW 기반 복제는 실제 행 변경 사항을 기록하므로 일반적으로 복잡한 트랜잭션에 더 안전하고 효율적이지만 더 큰 바이너리 로그를 생성할 수 있습니다.
진단: 비결정적 명령문 또는 누락된 로그 위치 또는 중복 키 오류와 관련된 빈번한 Last_SQL_Error 메시지. SHOW VARIABLES LIKE 'binlog_format'.
해결 방법:
- ROW 또는 MIXED 사용: 일반적으로 대부분의 최신 애플리케이션에서는 안정성과 결정론을 위해 binlog_format=ROW가 권장됩니다. MIXED는 안전할 때 STATEMENT를 사용하고 그렇지 않으면 ROW를 사용하는 절충안입니다.
```
# 소스의 /etc/my.cnf 설정 예시
[mysqld]
binlog_format = ROW
```
- 참고: binlog_format은 많은 MySQL 설정에서 런타임에 변경할 수 있지만 프로덕션 토폴로지에서 복제 형식을 변경하는 것은 신중하게 계획해야 합니다. 새 형식에 의존하기 전에 모든 복제본과 애플리케이션 패턴이 호환되는지 확인하십시오.

복제 지연 방지를 위한 모범 사례

다음 습관을 사용하여 반복적인 지연 사고를 줄이십시오:

사전 예방적 모니터링: 복제 지연 시간, 서버 리소스(CPU, I/O, 네트워크) 및 바이너리 로그 크기에 대한 강력한 모니터링을 구현합니다. 정상적인 동작에서 벗어나는 경우 알림을 설정합니다.
정기적인 최적화: 소스와 복제본 모두에서 느린 쿼리를 정기적으로 검토하고 최적화합니다. 인덱스가 최신이고 효과적인지 확인합니다.
하드웨어 크기 조정: 복제 부하와 처리할 수 있는 모든 읽기 워크로드를 예상하여 복제본 서버에 충분한 하드웨어 리소스(CPU, RAM, 빠른 스토리지)를 프로비저닝합니다.
일괄 작업: 개발자와 관리자에게 대규모 데이터 수정에 대한 모범 사례를 교육하고 일괄 처리 또는 온라인 스키마 변경 도구 사용을 장려합니다.
GTID 활용: 직접적인 지연 방지는 아니지만 GTID(Global Transaction Identifiers)는 특히 장애 조치 중 또는 복제 중단에서 복구할 때 복제 관리를 단순화하여 간접적으로 장기 지연을 유발할 수 있는 가동 중지 시간을 줄일 수 있습니다.
최신 상태 유지: MySQL 버전을 합리적으로 최신 상태로 유지하십시오. 최신 버전에는 성능 개선 및 향상된 복제 기능(예: 고급 MTS)이 포함되는 경우가 많습니다.

최종 요점

MySQL 복제 지연을 대기열 문제로 취급하십시오. 복제본이 이벤트를 가져오는 데 느린지, 릴레이 로그를 쓰는 데 느린지, 아니면 트랜잭션을 적용하는 데 느린지 찾으십시오. 그런 다음 일치하는 원인(네트워크 배치, 스토리지, 긴 소스 트랜잭션, 누락된 인덱스 또는 병렬 적용 설정)을 수정하십시오. 지연 및 복제 오류에 대한 알림을 유지하여 다음 속도 저하가 오래된 읽기 또는 장애 조치 계획이 오래된 복제본에 의존하기 전에 포착하십시오.