• 아티클

SQL Q&A행 오버플로, 차등 백업 및 기타

Paul S. Randal

Q 최근에 SQL Server 2005에서 실행되도록 응용 프로그램을 업그레이드했습니다. 덕분에 누리게 된 이점 중 하나는 8,060바이트를 초과하는 행을 사용할 수 있다는 것입니다. 즉, 사용자들이 SQL Server 오류 없이 더 긴 데이터 필드를 만들도록 허용할 수 있게 되었습니다. 이 응용 프로그램을 프로덕션 환경으로 옮기자 스키마 변경 전에는 제대로 실행되던 일부 검색 쿼리에서 성능 문제가 발생했습니다. 여러 색인의 조각화도 확인했더니 모두 정상입니다. SQL Server 2005에서 쿼리 실행 속도가 느린 까닭은 무엇입니까?

A 사용 중인 기능, 행 오버플로는 경우에 따라 8,060바이트를 초과하는 행을 허용하므로 효과적이지만, 대다수의 행이 크기를 초과하는 경우에 적합하지 않으며 질문한 대로 쿼리 성능을 저하시킬 수 있습니다.

그 이유는 어떤 행이 크기를 초과하게 되면 그 행의 가변 길이 열 중 하나가 "행 외부"로 밀려나기 때문입니다. 즉, 그 열이 데이터 또는 색인 페이지의 행에서 벗어나 텍스트 페이지로 이동합니다. 데이터 파일에 있는 열 값의 새 위치를 가리키는 포인터가 기존 열의 값을 대체합니다.

이 메커니즘은 XML 텍스트, 이미지 또는 varchar(max)와 같은 일반 LOB(Large Object) 열을 저장할 때와 정확하게 동일합니다. 테이블 스키마가 여러 개의 가변 길이 열을 포함하는 경우, 여러 행이 크기를 초과할 때 동일한 열이 행 외부로 이동한다고 보장할 수 없습니다.

이 메커니즘에서는 성능 문제가 발생할 수 있습니다. 어떤 테이블의 단일 행에서 가변 길이의 열을 검색하는 쿼리를 실행할 때 열이 행 외부로 밀려난 경우 (해당 값의 행 외부 위치가 포함된 텍스트 페이지를 읽기 위해) 갑자기 추가 I/O가 필요할 수 있습니다. 여러 개의 행이 크기를 초과한 경우, 여러 행으로부터 동일한 가변 길이 열을 검색하는 쿼리는 행 외부로 밀려난 값의 개수에 따라 예기치 않은 성능을 경험할 수 있습니다.

질문자의 경우처럼 가변 길이 열을 포함하는 선택 목록에 대해 범위 검색이나 테이블 검색을 수행하는 쿼리는 행 오버플로 및 그 영향으로 인해 성능이 저하됩니다. 색인이 올바르게 조각화되었는지 여부는 중요하지 않습니다. 가변 길이 열이 행 외부로 밀려난 경우 이전에는 효율적이던 검색이 사실상 방해를 받습니다. 행 외부 값이 포함된 텍스트 페이지를 읽기 위해 임의 I/O가 필요하기 때문입니다.

행 오버플로는 이따금씩 크기를 초과하는 행에서는 여전히 유용한 기능입니다. 그러나 쿼리 성능이 중요한 경우 설계에서 이 기능을 지나치게 많이 사용해서는 안 됩니다.

Q SAN(Storage Area Network) 복제보다 저렴한 비용으로 지역 중복성을 실현하기 위해 두 장애 조치 클러스터 간에 데이터베이스 미러링을 구현했습니다. 데이터 센터는 같은 도시에 있으므로 동시 미러링을 사용할 수 있습니다. 그러나 로컬 클러스터에서 장애 조치가 수행될 때 미러링된 데이터베이스가 원격 클러스터로 장애 조치되는 결과가 발생합니다. 이러한 현상을 방지하려면 어떻게 합니까? 우리는 로컬 클러스터를 사용할 수 없는 경우에만 장애 조치를 수행하고자 합니다.

A 가용성 향상을 위해 미러링 모니터 서버와 함께 미러링이 설정되므로 주 서버가 사용할 수 없게 되면 장애 조치가 자동으로 이루어집니다. 즉 로컬 클러스터 전체가 중단되면 데이터베이스 미러링에서 두 번째 클러스터로 장애 조치하여 응용 프로그램이 계속 실행되게 하는 것입니다.

이 문제는 클러스터 장애 조치가 실행될 때 발생합니다. 장애 조치는 데이터베이스 미러링의 기본 시간 제한 설정보다 오랜 시간이 걸립니다. 미러링 모니터 서버와 미러 서버(즉 두 번째 클러스터의 활성 SQL Server 인스턴스)가 주 서버를 인식할 수 없다는 데 동의하면 미러 서버가 두 번째 클러스터에 대한 미러링 장애 조치를 시작하게 됩니다.

이 현상을 방지하는 가장 손쉬운 방법은 미러링 모니터 서버를 제거하여 로컬 클러스터가 중단되더라도 데이터베이스 미러링 장애조치가 자동으로 시작되지 않게 하는 것입니다. 물론 그러면 인위적으로 장애 조치를 시작해야 하므로 가용성이 줄어듭니다.

두 번째 방법은 데이터베이스 미러링의 기본 시간 제한 설정을 변경하는 것입니다. 이는 주 서버가 초당 1회의 "ping"에 몇 차례 응답하지 못하면 사용 불가능으로 선언되는가를 나타냅니다. 이 설정을 파트너 시간 제한이라고 부르며 기본값은 10입니다. 데이터베이스의 현재 시간 제한 값은 다음 코드를 사용하여 확인할 수 있습니다.

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

시간 제한 값은 다음 코드를 사용하여 변경할 수 있습니다.

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

이 시나리오에서 파트너 시간 제한은 일반적으로 로컬 클러스터에서 클러스터 페일오버가 이루어지는 데 걸리는 시간보다 높게 설정해야 합니다. 클러스터 장애 조치 발생 시 미러링된 데이터베이스에서 복구가 실행되는 데 걸리는 시간은 가변적이기 때문에 이 값을 결정하기가 약간 까다로울 수 있습니다. 그러나 상한값으로 결정하는 것은 가능합니다. 이 방법의 문제점은 시간 제한 값이 분 단위가 되는 경우도 있다는 것입니다. 이는 실제 재해가 발생할 때 적절하지 않을 수 있습니다.

Q 전체 및 로그 백업 전략을 사용하고 있습니다. 그러나 복원 시간을 단축하기 위해 차등 백업을 추가하라는 지시를 받았습니다. 전체 백업은 매주 1회, 로그 백업은 매 시간 수행합니다. 일일 차등 백업을 추가해 보았으나, 이상하게도 주말에 수행한 차등 백업은 주간 전체 백업과 크기가 거의 동일합니다. 로그 백업처럼 증분 백업 같습니다. 잘못된 생각입니까?

A 차등 백업의 특성에 대한 오해에서 비롯된 것입니다. 차등 백업은 로그 백업과 달리 증분 백업이 아닙니다. 차등 백업은 이전의 전체 백업 이후 변경된 모든 데이터 파일 범위를 포함하며, 이는 데이터베이스, 파일 그룹 및 파일 수준 백업 모두 해당되는 사항입니다.

어떤 범위(일련의 데이터 파일 페이지 8개로 구성된 논리 그룹)가 어떤 식으로든 변경되면 이는 차등 맵(diff map)이라고 부르는 특수 비트맵 페이지에 표시됩니다. 각 데이터 파일에서 4GB 청크당 하나의 차등 맵이 있습니다. 차등 백업이 수행되면 백업 하위 시스템은 모든 차등 맵을 검색하고 변경된 범위를 모두 복사합니다. 그러나 차등 맵은 재설정되지 않습니다. 따라서 연속적인 차등 백업 사이에 추가로 변경된 범위가 있을 경우 나중의 백업이 더 커집니다. 전체 백업이 수행될 때만 차등 맵이 재설정됩니다.

단기간(예: 1주일 이내)에 데이터베이스 내용이 크게 바뀌는 응용 프로그램 작업 부하에서는 주간 전체 백업이 다음 전체 백업 직전에 수행된 차등 백업과 크기가 거의 비슷합니다. 질문자가 경험한 현상은 이렇게 설명할 수 있습니다.

차등 백업으로 재해 복구 상황에서 복구 시간을 단축할 수 있다는 생각은 옳습니다. 매주 전체 백업을, 매시간 로그 백업을 수행하는 전략의 경우 최신(up-to-the-minute) 복원에서는 다음 조치가 필요합니다.

  • 비상 로그 백업을 수행합니다(가장 최근의 로그 백업 이후 생성된 모든 로그).
  • 가장 최근의 전체 데이터베이스 백업을 복원합니다.
  • 가장 최근의 전체 데이터베이스 백업 이후 모든 로그 백업을 차례로 복원합니다.
  • 비상 로그 백업을 복원합니다.

따라서 특히 다음 전체 백업 예정일 직전에 재해가 발생한 경우 복원해야 하는 로그 백업의 양이 많아질 수 있습니다. 최악의 경우 24 + 24 + 24 + 24 + 24 + 24 + 23개의 로그 백업을 복원해야 합니다! 이 전략에 일일 차등 백업을 추가하면 복원 순서는 다음과 같이 바뀝니다.

  • 비상 로그 백업을 수행합니다(가장 최근의 로그 백업 이후 생성된 모든 로그).
  • 가장 최근의 전체 데이터베이스 백업을 복원합니다.
  • 가장 최근의 차등 백업을 복원합니다.
  • 가장 최근의 차등 백업 이후 모든 로그 백업을 차례로 복원합니다.
  • 비상 로그 백업을 복원합니다.

그러면 많은 양의 로그 백업을 복원해야 하는 수고를 덜 수도 있습니다. 차등 백업 복원이 차등 백업 대상 기간의 모든 로그 백업을 복원하는 것과 사실상 같기 때문입니다.

일일 차등 백업 시나리오에서 최악의 경우는 주의 마지막 날이더라도 로그 백업 23개에 불과합니다. 증분이 아닌 차등 백업의 유일한 단점은 더 많은 공간을 차지할 수 있다는 것인데, 대부분의 경우 복원 시간 단축의 이점 덕분에 감수할 만한 문제입니다.

Q 2노드 장애 조치 클러스터가 있습니다. 각 노드는 SQL Server 2005 단일 인스턴스에서 실행 중입니다. 각 인스턴스가 사용 가능한 메모리 중 50%만 사용하도록 설정하라는 일반적인 조언을 따르고 있습니다. 그런데 양쪽 인스턴스의 작업 부하에 메모리를 추가해야 성능 수준이 동일하게 유지된다는 문제가 발생합니다. 메모리 제한을 없애거나 더 높일 경우 인스턴스 중 하나가 장애 조치되고 둘 다 하나의 노드에서만 실행되는 문제가 생길 것 같습니다. 어떻게 해야 합니까?

A 2노드 2인스턴스 시나리오에서 이 질문에 답하겠지만, 아래의 내용은 다른 다중 인스턴스 설정(N-1 장애 조치 클러스터, 즉 N개의 노드와 N-1개의 SQL Server 인스턴스가 있음)에도 적용됩니다.

양쪽 인스턴스 모두 작업 부하가 많은 상황에서(서버 메모리의 50%를 초과하여 사용) 장애 조치 발생 후 결국 양쪽 인스턴스 모두 단일 노드에서 실행될 경우 작업 부하에 어떤 영향을 미칠 것인지 고려하지 않는 이들이 많습니다. 특별한 구성이 없는 상태에서 인스턴스 간의 메모리 분배가 불균형해져 한쪽 작업 부하는 제대로 처리되지만 다른 쪽은 속도가 크게 떨어지는 상황이 될 수 있습니다.

SQL Server 2000에서는 각 인스턴스의 최대 메모리를 클러스터 노드 메모리의 50%로 제한하는 것이 좋습니다. 이는 SQL Server 2000의 메모리 관리자가 메모리 부족에 응답하지 않기 때문입니다. 만약 SQL Server에서 노드 메모리의 80%를 차지하는 경우 이를 반환하지 않습니다. 이 경우 장애 조치 상황에서 새로 시작한 인스턴스는 사용 가능 메모리 중 20%만 확보하게 됩니다. 두 인스턴스 모두 노드 메모리의 50%를 상한값으로 제한한다면 장애 조치 인스턴스는 메모리의 50%를 보장 받게 됩니다. 여기서 각 인스턴스의 작업 부하 역시 메모리의 50%로 제한된다는 문제가 있습니다.

SQL Server 2005(및 SQL Server 2008)에서는 메모리 관리자가 메모리 부족에 응답할 수 있으므로 더 이상 50% 최대값은 적합하지 않습니다. 그러나 일종의 제한이 없는 경우 두 인스턴스가 하나의 클러스터 노드에서 실행될 때 서로 부담을 주면서 결국 메모리 분배가 불균형해질 수 있습니다.

답은 각 인스턴스가 최소량의 메모리를 갖게 설정하여 너무 많은 메모리를 해제해야 하는 상황을 방지하는 것입니다. 2노드, 2인스턴스 설정에서는 각 인스턴스에 최소 40%의 메모리를 구성하는 것이 일반적입니다. 따라서 각 인스턴스가 개별 노드에서 실행 중일 때는 원하는 만큼의 메모리를 사용할 수 있습니다. 장애 조치가 발생하면 각 인스턴스는 어느 정도의 메모리가 보장되므로 일정 수준의 작업 부하 성능을 유지하는 한편 인스턴스 간에 공유할 메모리를 약간 남겨 놓을 수 있습니다. 장애 조치 상황에서 두 작업 부하의 성능 모두 (예상대로) 떨어질 수 있으나, 각 인스턴스가 개별 클러스터 노드에서 실행되는 대부분의 경우에서는 전혀 제한을 받지 않습니다.

Paul S. Randal은 SQLskills.com의 관리 이사 겸 SQL Server MVP입니다. 1999년부터 2007년까지 Microsoft의 SQL Server 저장소 엔진 팀에서 근무한 Paul은 DBCC CHECKDB/repair for SQL Server 2005를 저술했으며 SQL Server 2008 개발 과정에서 핵심 저장소 엔진 부분을 담당했습니다. Paul은 재해 복구, 고가용성 및 데이터베이스 유지 관리 분야의 전문가이며 전 세계 각종 컨퍼런스에서도 꾸준히 의견을 발표하고 있습니다. 블로그 주소는 SQLskills.com/blogs/paul입니다.