MySQL 복제 지연 수정: 일반적인 원인 및 해결 방법

MySQL 복제는 현대 데이터베이스 환경에서 고가용성, 재해 복구, 읽기 워크로드 확장을 달성하는 데 중요한 구성 요소입니다. 이는 기본(소스) 서버에서 발생한 데이터 변경 사항이 하나 이상의 복제본(보조) 서버로 정확하고 효율적으로 전파되도록 보장합니다. 그러나 관리자가 직면하는 일반적인 문제는 복제 지연으로, 복제본이 트랜잭션 적용에서 소스보다 뒤처지는 현상입니다.

복제 지연은 복제본의 데이터가 오래된 상태로 유지되거나, 애플리케이션 일관성에 영향을 미치거나, 장애 발생 시 장애 조치 메커니즘의 효과를 저해하는 등 심각한 결과를 초래할 수 있습니다. 이 지연을 진단하고 해결하는 것은 MySQL 인프라의 상태와 신뢰성을 유지하는 데 매우 중요합니다. 이 글에서는 MySQL 복제의 메커니즘을 자세히 살펴보고, 가장 일반적인 지연 원인을 탐색하며, 서버 전반에 걸쳐 데이터 일관성을 보장하고 복제 성능을 개선하는 데 도움이 되는 실용적이고 실행 가능한 해결 방법을 제공합니다.

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

출력에서 검토해야 할 주요 지표:

Slave_IO_Running: Yes여야 합니다.
Slave_SQL_Running: Yes여야 합니다.
Seconds_Behind_Master: 지연을 나타내는 가장 직접적인 지표입니다. 현재 처리 중인 이벤트의 소스 바이너리 로그 타임스탬프와 복제본의 릴레이 로그 타임스탬프 간의 시간 차이를 초 단위로 보여줍니다. 0보다 큰 값은 지연을 나타냅니다.
Last_IO_Error: 네트워크 또는 I/O 관련 오류.
Last_SQL_Error: 이벤트를 적용하는 동안 발생하는 오류.

Seconds_Behind_Master에 대한 중요 참고 사항: 이 지표는 트랜잭션 기반이 아닌 시간 기반입니다. 소스가 60초가 걸리는 대규모 트랜잭션을 처리하면 Seconds_Behind_Master는 해당 트랜잭션이 커밋되어 바이너리 로그에 기록될 때만 증가합니다. 복제본이 10초 후에 이를 적용하면 지연이 50초로 보일 수 있습니다. 이는 대기 중인 트랜잭션이나 이벤트 수를 반영하는 것이 아니라 이벤트 타임스탬프 간의 시간 차이만을 나타냅니다.

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

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

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

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

원인: 소스와 복제본 간의 느리거나 불안정한 네트워크 연결, 또는 바이너리 로그 이벤트를 빠르게 전송하기 위한 네트워크 대역폭 부족.
진단: Seconds_Behind_Master가 높고 Slave_IO_Running은 Yes이지만 Relay_Log_Space가 크게 증가하지 않거나, 네트워크 문제와 관련된 Last_IO_Error 항목이 자주 발생하는 경우. ping, mtr 또는 traceroute와 같은 네트워크 진단 도구를 사용하여 지연 및 패킷 손실을 확인하십시오.
해결 방법:
- 네트워크 인프라 개선: 서버 간에 안정적이고 높은 대역폭의 연결을 보장하십시오.
- 서버 공동 배치: 이상적으로는 소스와 복제본이 지연 시간을 최소화하기 위해 동일한 데이터 센터 또는 클라우드 영역에 있어야 합니다.
- 압축: 이전 MySQL 버전의 경우 slave_compressed_protocol=1을 사용하면 대역폭 사용량을 줄일 수 있지만 CPU 오버헤드가 추가됩니다. 최신 연결은 일반적으로 이를 투명하게 처리합니다.

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

원인: 복제본의 디스크 하위 시스템이 릴레이 로그를 쓰거나 데이터 파일에 변경 사항을 충분히 빠르게 적용할 수 없습니다. 이는 특히 sync_binlog 또는 innodb_flush_log_at_trx_commit이 1 (최대 내구성)로 설정되어 빈번한 디스크 플러시를 유발하는 경우에 해당됩니다.
진단: 복제본의 top 또는 vmstat 출력에서 높은 iowait, 높은 디스크 사용량 (iostat -x 1), 그리고 Seconds_Behind_Master가 꾸준히 증가하는 경우. 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: 기본값은 1 (각 커밋 후 동기화)입니다. 0 (OS가 동기화 처리) 또는 더 높은 값(예: 100 또는 1000)으로 설정하면 플러시 횟수를 줄이지만 소스 충돌 시 바이너리 로그 손실 위험이 있습니다. 이 설정은 소스에 있지만 이벤트 볼륨으로 인해 복제본이 따라가지 못하는 데 영향을 미칩니다.
```ini

복제본의 예시 /etc/my.cnf 설정 (극도의 주의 필요)

[mysqld]
innodb_flush_log_at_trx_commit = 2 # 또는 허용 오차에 따라 0
```

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

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

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

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

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

원인: 이전 MySQL 버전에서는 SQL 스레드가 소스에서 발생한 동시 트랜잭션 수에 관계없이 모든 트랜잭션을 순차적으로 적용했습니다. 소스가 많은 동시 쓰기를 처리하는 경우 복제본의 단일 SQL 스레드는 쉽게 병목 현상이 될 수 있습니다.
진단: Seconds_Behind_Master가 높고 Slave_SQL_Running_State에 종종 활성 쿼리가 표시되는 반면, 복제본의 CPU는 모든 코어에서 완전히 포화되지 않을 수 있습니다.
해결 방법:
- 다중 스레드 복제 (MTS): MySQL 5.6은 slave_parallel_type=DATABASE (데이터베이스 스키마 기반 병렬 처리)와 함께 slave_parallel_workers를 도입했습니다. MySQL 5.7 이상에서는 slave_parallel_type=LOGICAL_CLOCK (또는 TRANSACTION_COMMIT_ORDER)로 이를 크게 개선하여 동일한 데이터베이스 내에서도 충돌하지 않는 트랜잭션의 병렬 적용을 허용합니다. 이는 CPU 바운드 SQL 스레드 병목 현상에 가장 효과적인 해결책입니다.
```ini

복제본의 MTS를 위한 예시 /etc/my.cnf 설정

[mysqld]
slave_parallel_workers = 4 # 또는 더 높게, 일반적으로 CPU 코어의 2배
slave_parallel_type = LOGICAL_CLOCK # MySQL 5.7+에 권장
log_slave_updates = 1 # 복제본 체이닝 또는 백업에 권장
```
- 복제 다시 시작: MTS 설정을 변경한 후 복제본 SQL 스레드를 다시 시작해야 합니다.
sql STOP REPLICA; START REPLICA;

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

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

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

원인: STATEMENT 기반 복제는 비결정적(예: NOW(), UUID() 사용)인 문이 복제본에서 다른 결과를 생성하여 복잡한 컨텍스트 평가가 필요하거나 복제를 중단시킬 수 있기 때문에 문제가 될 수 있습니다. ROW 기반 복제는 실제 행 변경을 기록하므로 일반적으로 복잡한 트랜잭션에 대해 더 안전하고 효율적이지만, 바이너리 로그가 더 커질 수 있습니다.
진단: 비결정적 문 또는 Missing_Master_Log_Pos 오류와 관련된 빈번한 Last_SQL_Error 메시지. SHOW VARIABLES LIKE 'binlog_format'.
해결 방법:
- ROW 또는 MIXED 사용: 일반적으로 안정성과 결정성으로 인해 대부분의 최신 애플리케이션에는 binlog_format=ROW가 권장됩니다. MIXED는 안전할 때 STATEMENT를 사용하고 그렇지 않으면 ROW를 사용하는 절충안입니다.
```ini

소스의 예시 /etc/my.cnf 설정

[mysqld]
binlog_format = ROW
```
- 참고: binlog_format을 변경하려면 MySQL을 다시 시작하고, STATEMENT에서 ROW로 전환하는 경우 일관성을 보장하기 위해 복제를 완전히 다시 초기화해야 할 수 있습니다.

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

예방은 항상 치료보다 낫습니다. 이러한 관행을 MySQL 운영에 통합하십시오.

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

결론

MySQL 복제 지연은 일반적이지만 관리 가능한 문제입니다. 성공적인 문제 해결의 열쇠는 문제를 체계적으로 진단하고, 근본 원인을 이해하며, 적절한 해결 방법을 적용하는 데 있습니다. SHOW REPLICA STATUS를 활용하고, 서버 리소스를 모니터링하며, 다중 스레드 복제 및 쿼리 최적화와 같은 모범 사례를 채택함으로써 복제 지연을 크게 줄이거나 제거하여 MySQL 데이터베이스 생태계의 상태, 일관성 및 성능을 보장할 수 있습니다. 정기적인 감시와 사전 예방적 유지 보수는 원활하고 효율적인 복제 설정을 유지하는 데 가장 좋은 동맹입니다.