다음을 통해 공유

문제 해결: 비동기-커밋 가용성 그룹 복제본의 잠재적인 데이터 손실

  • 아티클

적용 대상: SQL Server

가용성 그룹에서 비동기 커밋 보조 복제본으로의 강제 수동 장애 조치(failover)를 수행한 후 데이터 손실이 RPO(복구 지점 목표)를 초과할 수 있습니다. 또는 Always On 가용성 그룹에 대한 성능 모니터링의 방법을 사용하여 비동기 커밋 보조 복제본의 잠재적 데이터 손실을 계산할 때 RPO 초과 문제를 발견합니다.

동기 커밋 보조 복제본은 데이터 손실 없음을 보증하지만 비동기 커밋 보조 복제본의 잠재적 데이터 손실은 보조 복제본에 확정되어 아직 대기 중인 로그의 양에 따라 달라집니다.

다음 섹션에서는 서버 인스턴스에 가용성 그룹과 무관한 시스템 성능 문제가 없다고 가정하고 비동기 커밋 보조 복제본의 높은 잠재적 데이터 손실의 일반적인 원인을 설명합니다.

  1. 네트워크 대기 시간이 길거나 네트워크 처리량이 낮으면 기본 복제본에 로그가 쌓입니다.

  2. 디스크 I/O 병목 상태로 보조 복제본에 확정된 로그 속도 저하

네트워크 대기 시간이 길거나 네트워크 처리량이 낮으면 기본 복제본에 로그가 쌓입니다.

자체의 RPO를 초과하는 데이터베이스의 가장 일반적인 원인은 보조 복제본으로 충분히 빠르게 전송될 수 없기 때문입니다.

설명

기본 복제본은 보조 복제본으로 전송된 승인되지 않은 최대 허용 메시지 수를 초과하는 경우 로그 전송에 대한 흐름 제어를 활성화합니다. 이러한 메시지의 일부가 승인될 때까지 로그 블록이 보조 복제본으로 전송될 수 없습니다. 데이터 손실은 보조 복제본에서 강화된 경우에만 방지할 수 있으므로, 전송되지 않은 로그 메시지가 쌓이면 잠재적인 데이터 손실이 증가합니다.

진단 및 해결

보조 복제본으로 많은 수의 메시지가 다시 전송되면 네트워크 대기 시간 및 네트워크 노이즈가 늘어날 수 있습니다. 또한 DMV 값 log_send_rate를 성능 개체의 초당 플러시된 로그 바이트 수(Log Bytes Flushed/sec)와 비교할 수 있습니다. 로그가 전송되는 것보다 더 빠르게 디스크로 플러시되면 잠재적 데이터 손실이 무한히 증가할 수 있습니다.

또한 두 성능 개체 SQL Server:Availability Replica > Flow Control Time (ms/sec) 및 SQL Server:Availability Replica > Flow Control/sec를 검사하는 것이 유용합니다. 이러한 두 값을 곱하면 흐름 제어가 해제될 때까지 대기하는 데 소비한 최종 시간이 나타납니다. 흐름 제어 대기 시간이 길수록 전송 속도가 느려질 수 있습니다.

다음 메트릭은 네트워크 대기 시간과 처리량을 진단하는 데 유용합니다. ping.exe 및 네트워크 모니터와 같은 다른 Windows 도구를 사용하여 대기 시간 및 네트워크 사용률을 평가할 수 있습니다.

  • DMV sys.dm_hadr_database_replica_states, log_send_queue_size

  • DMV sys.dm_hadr_database_replica_states, log_send_rate

  • 성능 카운터 SQL Server:Database > Log Bytes Flushed/sec

  • 성능 카운터 SQL Server:Database Mirroring > Send/Receive Ack Time

  • 성능 카운터 SQL Server:Availability Replica > Bytes Sent to Replica/sec

  • 성능 카운터 SQL Server:Availability Replica > Bytes Sent to Transport/sec

  • 성능 카운터 SQL Server:Availability Replica > Flow Control Time (ms/sec)

  • 성능 카운터 SQL Server:Availability Replica > Flow Control/sec

  • 성능 카운터 SQL Server:Availability Replica > Resent Messages/sec

이 문제를 해결하려면 네트워크 대역폭을 업그레이드하거나, 불필요한 네트워크 트래픽을 제거하거나 줄여봅니다.

디스크 I/O 병목 상태로 보조 복제본에 확정된 로그 속도 저하

데이터베이스 파일 배포에 따라, 보고 워크로드와의 I/O 경합으로 인해 로그 강화 속도가 느려질 수 있습니다.

설명

로그 파일에서 로그 블록이 강화되는 즉시 데이터 손실이 방지됩니다. 따라서 데이터 파일로부터 로그 파일을 격리하는 것이 중요합니다. 로그 파일과 데이터 파일이 모두 동일한 하드 디스크에 매핑될 경우, 데이터 파일에 대한 읽기를 많이 사용하는 워크로드를 보고하는 데 로그 강화 작업에 필요한 것과 동일한 I/O 리소스가 사용됩니다. 로그 강화 속도가 느리면 기본 복제본에 대한 승인 속도가 느려질 수 있으며, 이로 인해 흐름 제어가 과도하게 활성화되고 흐름 제어 대기 시간이 길어질 수 있습니다.

진단 및 해결

네트워크에서 대기 시간이 길거나 처리량이 낮은 것으로 확인된 경우, 보조 복제본에서 I/O 경합을 조사해야 합니다. SQL Server: 디스크 I/O 최소화의 쿼리는 경합을 식별하는 데 유용합니다. 이 문서의 예가 사용자의 편의를 위해 아래에 정리되어 있습니다.

다음 스크립트를 사용하면 SQL Server 인스턴스에서 실행되는 모든 가용성 데이터베이스의 각 데이터 및 로그 파일에 대한 읽기 및 쓰기 수를 확인할 수 있습니다. 이는 평균 I/O 정지 시간(밀리초) 기준으로 정렬됩니다. 이 수치는 서버 인스턴스가 마지막으로 시작된 시점부터 누적됩니다. 따라서 시간이 경과한 후의 두 측정값 간 차이를 고려해야 합니다.

SELECT DB_NAME(database_id) AS   
   [Database Name] ,   
   file_id ,   
   io_stall_read_ms ,   
   num_of_reads ,   
   CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,   
   io_stall_write_ms ,   
   num_of_writes ,  
   CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] ,   
   io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,   
   num_of_reads + num_of_writes AS [total_io] ,   
   CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads  
+ num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]  
FROM sys.dm_io_virtual_file_stats(NULL, NULL)  
WHERE DB_NAME(database_id) IN (SELECT DISTINCT database_name FROM sys.dm_hadr_database_replica_cluster_states)  
ORDER BY avg_io_stall_ms DESC;

다음 쿼리는 시스템에서 보류 중인 I/O 요청의 특정 시점(누적되지 않음) 스냅샷 제공합니다.

SELECT DB_NAME(mf.database_id) AS [Database] ,   
   mf.physical_name ,  
   r.io_pending ,   
   r.io_pending_ms_ticks ,   
   r.io_type ,   
   fs.num_of_reads ,   
   fs.num_of_writes  
FROM sys.dm_io_pending_io_requests AS r   
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle   
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id  
AND fs.file_id = mf.file_id  
ORDER BY r.io_pending , r.io_pending_ms_ticks DESC;

읽기 I/O 및 쓰기 I/O를 서로 비교하여 I/O 경합을 식별할 수 있습니다.

다음은 I/O 병목 상태를 진단하는 데 유용한 몇 가지 다른 성능 카운터입니다.

  • 물리적 디스크: 모든 카운터

  • 물리적 디스크: 평균 디스크 초/전송

  • SQL Server: 데이터베이스 > 로그 플러시 대기 시간

  • SQL Server: 데이터베이스 > 로그 플러시 대기 횟수/초

  • SQL Server: 데이터베이스 > 로그 풀 디스크 읽기 횟수/초

I/O 병목 상태를 식별하고 로그 파일과 데이터 파일을 동일한 하드 디스크에 배치한 경우, 먼저 데이터 파일과 로그 파일을 별도의 디스크에 배치해야 합니다. 이 모범 사례는 보고 워크로드가 기본 복제본에서 로그 버퍼로의 로그 전송 경로와 보조 복제본의 트랜잭션을 강화하는 기능을 방해하지 않도록 방지합니다.

다음 단계

SQL Server의 성능 문제 해결(SQL Server 2012에 적용)