Amazon RDS for MySQL 및 Aurora MySQL에서 높은 binlog 복제본 지연 시간 문제를 해결하려면 어떻게 해야 합니까?

간략한 설명

Amazon RDS for MySQL은 비동기식 복제를 사용하며, 복제본이 기본 DB 인스턴스보다 지연되는 경우가 있습니다. 복제 지연 시간을 모니터링하려면 바이너리 로그 파일 위치 기반 복제가 포함된 Amazon RDS for MySQL 읽기 전용 복제본을 사용하십시오.

Amazon RDS의 ReplicaLag 지표를 확인하려면 Amazon CloudWatch를 사용하십시오. ReplicaLag 지표는 SHOW SLAVE STATUS 명령의 Seconds_Behind_Master 필드 값을 보고합니다.

Aurora의 경우 AuroraBinlogReplicaLag 지표는 바이너리 로그를 사용하는 Aurora DB 클러스터 간의 복제본 지연 시간을 측정합니다.

참고: MySQL 버전 8.0.22 이상에서는 SHOW REPLICA STATUS 명령이 SHOW SLAVE STATUS 명령을 대체했습니다. 자세한 내용은 MySQL 웹 사이트의 SHOW SLAVE | REPLICA STATUS 문을 참조하십시오.

Seconds_Behind_Master 필드는 복제본 DB 인스턴스가 소스 인스턴스보다 뒤처지는 현재 지연 시간을 초 단위로 표시합니다. 또한 복제본 DB 인스턴스에서 처리하는 이벤트의 기본 DB 인스턴스에 로깅된 원래 값도 표시됩니다.

MySQL 복제는 바이너리 로그 덤프, 복제 I/O 수신기 및 복제 SQL 어플라이어 스레드를 사용합니다. 스레드 작동 방식에 대한 자세한 내용은 MySQL 웹 사이트의 복제 스레드를 참조하십시오. 복제가 지연되는 경우 IO_THREAD 복제본 또는 SQL_THREAD 복제본으로 인해 지연이 발생하는지 확인합니다. 그러면 지연의 근본 원인을 확인할 수 있습니다.

해결 방법

지연되는 복제 스레드 식별

다음 단계를 완료하십시오.

1. 소스 또는 기본 DB 인스턴스가 바이너리 로그를 기록하는 위치를 확인하려면 기본 DB 인스턴스에서 SHOW MASTER STATUS 명령을 실행합니다.

   mysql> SHOW MASTER STATUS;

   출력 예시:

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   참고: 위의 출력 예시에서 소스 또는 기본 DB 인스턴스는 바이너리 로그를 mysql-bin.066552 파일에 기록합니다.
2. 출력 상태를 확인하려면 복제본 DB 인스턴스에서 SHOW SLAVE STATUS 또는 SHOW REPLICA STATUS 명령을 실행합니다.

   mysql> SHOW SLAVE STATUS\G;

출력 예시 1:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

위의 출력 예시에서 Master_Log_File: mysql-bin.066548은 IO_THREAD 복제본이 mysql-bin.066548 바이너리 로그 파일에서 읽음을 보여줍니다. 기본 DB 인스턴스는 바이너리 로그를 mysql-bin.066552 파일에 기록합니다. IO_THREAD 복제본은 4개의 바이너리 로그만큼 뒤쳐집니다. 하지만 Relay_Master_Log_File이 mysql-bin.066548이므로 SQL_THREAD 복제본은 IO_THREAD와 동일한 파일에서 읽습니다. SQL_THREAD 복제본은 속도를 유지하지만 IO_THREAD 복제본은 지연됩니다.

출력 예시 2:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

위의 출력 예시는 기본 인스턴스의 로그 파일이 mysql-bin.066552임을 보여줍니다. IO_THREAD는 기본 DB 인스턴스와의 속도가 유지됩니다. 복제본 출력에서 SQL 스레드는 Relay_Master_Log_File: mysql-bin.066530을 수행합니다. 결과적으로 SQL_THREAD는 22개의 바이너리 로그보다 지연됩니다.

IO_THREAD는 기본 또는 소스 인스턴스에서만 바이너리 로그를 읽기 때문에 IO_THREAD는 일반적으로 큰 복제 지연을 유발하지 않습니다. 그러나 네트워크 연결 및 네트워크 지연 시간은 서버 간 읽기 속도에 영향을 줄 수 있습니다. 대역폭 사용량이 많으면 IO_THREAD 복제본이 더 느리게 작동할 수 있습니다.

SQL_THREAD 복제본으로 인해 복제 지연이 발생하는 경우 다음 문제 해결 단계를 사용하여 문제를 해결하십시오.

기본 인스턴스의 장기 실행 쓰기 쿼리

복제본 DB 인스턴스에서 실행하는 데 동일한 시간이 걸리는 기본 DB 인스턴스의 쓰기 쿼리를 장기간 실행하면 seconds_behind_master가 증가할 수 있습니다. 예를 들어 기본 인스턴스에 대한 변경을 실행하는 데 1시간이 걸리는 경우 지연은 1시간입니다. 복제본에서도 변경을 완료하는 데 1시간이 걸리면 총 지연은 2시간입니다.

지연을 최소화하려면 기본 인스턴스의 느린 쿼리 로그를 모니터링하고 최적화가 필요한 쿼리를 식별하십시오. 또한 장기 실행 명령문을 더 작은 명령문 또는 트랜잭션으로 줄일 수 있습니다.

binlog 관련 성능 문제를 해결하려면 Performance Insights에서 발견된 대기 이벤트를 분석하여 자세한 내용을 확인하십시오. 격리 수준을 조정할 수도 있습니다.

DB 인스턴스 클래스 크기 또는 스토리지 부족

복제본 DB 인스턴스 클래스 또는 스토리지 구성이 기본 인스턴스보다 낮은 경우 리소스 부족으로 인해 복제본이 제한될 수 있습니다. 복제본은 기본 인스턴스의 변경 사항 수를 유지할 수 없습니다.

이 문제를 해결하려면 복제본의 DB 인스턴스 유형이 기본 DB 인스턴스와 같거나 더 높아야 합니다. 복제가 효과적으로 작동하려면 각 읽기 전용 복제본에 소스 DB 인스턴스와 동일한 수의 컴퓨팅 및 스토리지 리소스가 필요합니다.

기본 DB 인스턴스에서 병렬 쿼리 실행

기본적으로 MySQL 복제는 단일 스레드입니다. 기본 인스턴스에서 쿼리를 병렬로 실행하면 쿼리가 복제본에서 순차적으로 커밋됩니다. 소스 인스턴스에 대량의 쓰기가 병렬로 발생하는 경우 읽기 전용 복제본에 대한 쓰기는 단일 SQL_THREAD를 사용하여 직렬화합니다. 그러면 소스 DB 인스턴스와 읽기 전용 복제본 간에 지연이 발생할 수 있습니다.

MySQL 버전 8.0.27 이상에서는 replica_parallel_workers의 기본값이 4입니다. 이 값은 복제본이 기본적으로 다중 스레드임을 의미합니다. 마찬가지로 Aurora MySQL 버전 3.04 이상에서는 복제가 기본적으로 다중 스레드되며, replica_parallel_workers 값은 4로 설정됩니다. 사용자 지정 파라미터 그룹에서 이 파라미터를 수정할 수 있습니다.

다중 스레드 복제에 대한 자세한 내용은 MySQL 웹 사이트에서 바이너리 로깅 옵션 및 변수를 참조하십시오.

다중 스레드 복제로 인해 복제에 공백이 발생할 수 있습니다. 예를 들어 건너뛰는 트랜잭션을 식별하기 어렵습니다. 다중 스레드 복제를 사용하는 경우 복제 오류를 건너뛰는 것은 권장하지 않습니다. 기본 DB 인스턴스와 복제본 DB 인스턴스 간의 데이터 일관성에 차이가 생길 수 있습니다.

복제본 DB 인스턴스의 디스크에 동기화된 바이너리 로그

복제본에서 자동 백업을 활성화하면 바이너리 로그를 복제본의 디스크에 동기화하는 데 오버헤드가 발생합니다. sync_binlog 파라미터의 기본값은 1로 설정됩니다. 값을 0으로 변경하면 MySQL 서버가 바이너리 로그를 디스크에 동기화할 수 없습니다. 대신 운영 체제(OS)에서 바이너리 로그를 디스크에 플러시하는 경우가 있습니다.

커밋할 때마다 바이너리 로그를 디스크에 동기화하는 데 필요한 성능 오버헤드를 줄이려면 바이너리 로그 동기화를 해제합니다. 하지만 정전이 발생하거나 OS 충돌이 발생하면 일부 커밋이 바이너리 로그와 동기화되지 않을 수 있습니다. 비동기화는 PITR(시점 복원) 기능에 영향을 미칠 수 있습니다. 자세한 내용은 MySQL 웹사이트에서 sync_binlog를 참조하십시오.

binlog_format이 ROW로 설정됨

다음 시나리오에서 복제가 발생하면 SQL 스레드는 전체 테이블 스캔을 수행합니다.

• 기본 DB 인스턴스의 binlog_format이 ROW로 설정되어 있습니다.
• 소스 테이블에 프라이머리 키가 없습니다.

이 스캔은 slave_rows_search_algorithms 파라미터의 기본값이 TABLE_SCAN,INDEX_SCAN이기 때문에 발생합니다.

이 문제를 일시적으로 해결하려면 검색 알고리즘을 INDEX_SCAN,HASH_SCAN으로 변경하여 전체 테이블 스캔의 오버헤드를 줄이십시오. 보다 영구적으로 해결하려면 각 테이블에 명시적인 기본 키를 추가하는 것이 좋습니다.

slave-rows-search-algorithms 파라미터에 대한 자세한 내용은 MySQL 웹 사이트에서 slave_rows_search_algorithms를 참조하십시오.

복제본 만들기 지연(RDS MySQL에 적용 가능)

Amazon RDS는 MySQL 기본 인스턴스의 읽기 전용 복제본을 만들기 위해 DB 스냅샷을 생성합니다. 그런 다음, Amazon RDS는 스냅샷을 복원하여 새 DB 인스턴스를 생성하고 두 인스턴스 사이에 복제를 설정합니다.

Amazon RDS가 복제를 설정한 후 Amazon RDS에서 기본 DB 인스턴스의 백업을 생성할 때 지연이 발생합니다. 이 지연을 최소화하려면 복제본 생성을 요청하기 전에 수동 백업을 생성하십시오. 그러면 DB 스냅샷이 증분 백업이 됩니다.

스냅샷에서 읽기 전용 복제본을 복원할 때 복제본은 소스로부터 모든 데이터가 전송될 때까지 기다리지 않습니다. 복제본 DB 인스턴스는 DB 작업을 수행하는 데 사용할 수 있습니다. 기존 Amazon Elastic Block Store(Amazon EBS) 스냅샷은 백그라운드에서 새 볼륨을 생성합니다.

참고: Amazon RDS for MySQL 복제본(Amazon EBS 기반 볼륨)의 경우 지연 로딩이 복제 성능에 영향을 미칠 수 있으므로 처음에는 복제 지연이 증가할 수 있습니다.

새 읽기 전용 복제본의 테이블에 대해 지연 로드가 미치는 영향을 줄이려면 전체 테이블 스캔을 사용하는 작업을 수행하십시오. 예를 들어, 특정 테이블 또는 데이터베이스의 읽기 전용 복제본에서 mysqldump를 실행하여 Amazon Simple Storage Service(Amazon S3)에서 백업한 모든 테이블 데이터의 우선순위를 지정합니다.

또한 RDS MySQL의 온디맨드 InnoDB 캐시 워밍 기능을 사용할 수도 있습니다. InnoDB 캐시 워밍 기능은 디스크의 버퍼 풀 상태를 InnoDB 데이터 디렉터리의 ib_buffer_pool이라는 파일에 저장합니다. Amazon RDS는 읽기 전용 복제본을 만들기 전에 기본 DB 인스턴스 버퍼 풀의 현재 상태를 덤프하므로 로드가 적을수록 성능이 향상됩니다. 그런 다음, 읽기 전용 복제본을 만든 후 버퍼 풀을 다시 로드할 수 있습니다.

복제본 지연 시간의 영향

복제본 인스턴스 또는 클러스터의 복제본 지연 시간이 크면 소스에 바이너리 로그가 누적될 수 있습니다. 발생 가능한 문제를 방지하려면 binlog 파일 크기 및 디스크 공간 사용량을 모니터링하고 관리하십시오. RDS의 경우 소스 DB 인스턴스에서 BinLogDiskUsage 지표를 모니터링할 수 있습니다.

최적의 binlog 보존 기간을 구성하여 RDS MySQL 인스턴스 및 Aurora에서 특정 시점 복구 기능과 스토리지 사용 간의 균형을 맞춥니다. 또한 binlog를 복제 및 복구에 활용하려면 binlog 파일 명명 규칙과 교체 동작을 이해해야 합니다. 사용 가능한 로그 목록을 가져오려면 SHOW BINARY LOGS 명령을 실행합니다.

관련 정보

아마존 RDS에서 MySQL 복제본으로 작업

MySQL 읽기 전용 복제본으로 작업

Aurora MySQL의 바이너리 로그 복제 최적화