SQL Q&A: 대용량 트랜잭션 로그, 복구 사용 시점 및 기타 정보

아티클
08/24/2016

SQL Q&A대용량 트랜잭션 로그, 복구 사용 시점 및 기타 정보

Paul S. Randal

Q: 백업과 관련해서 이상한 동작이 있는데 설명을 부탁드립니다. 저희는 응용 프로그램 개발자들이 사용하는 데이터를 새롭게 갱신하기 위해 빈번하게 62GB 용량의 프로덕션 데이터베이스를 백업합니다. 기존 복사본은 새 복사본을 복원하기 전에 항상 삭제합니다. 복원된 복사본의 크기는 프로덕션 데이터베이스와 동일하고, 데이터 자체도 같은 것으로 보이는데 복원 프로세스에 걸리는 시간이 백업 프로세스 시간에 비해 너무 깁니다. 왜 그럴까요? 백업보다 복원에 훨씬 더 많은 시간이 소요되는 이유는 무엇입니까?

A: 이상한 일이 아닙니다. 환경에 따라 다르긴 하지만 일반적으로 자연스러운 현상입니다. 백업과 복원에 필요한 시간의 차이는 각 프로세스에서 수행해야 하는 단계에 따라 발생합니다.

데이터베이스 백업은 두 단계로 구성됩니다. 기본적으로 보면 그저 데이터베이스에서 IO를 읽고 백업 장치에서 IO를 쓰는 것입니다.

백업 1단계 데이터 파일에서 모든 할당된 데이터를 읽어 백업 장치에 씁니다.

백업 2단계일부 트랜잭션 로그를 읽어 백업 장치에 씁니다.

필요한 트랜잭션 로그의 정확한 크기는 경우에 따라 상당히 달라지는데, 이 크기는 복원된 데이터베이스를 일관된 시점으로 복구하기 위해 필요한 만큼의 크기입니다. 이에 대한 더 심층적인 설명은 sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx의 블로그 게시물에서 볼 수 있습니다.

반면 데이터베이스 복원은 최대 4단계로 구성될 수 있습니다. 또한 필요한 작업도 단순히 IO를 읽고 쓰는 것보다 더 복잡합니다.

복원 1단계 데이터베이스 파일이 존재하지 않으면 만듭니다.

복원 2단계 백업에서 모든 데이터 및 트랜잭션 로그를 읽어 해당되는 데이터베이스 파일에 씁니다.

복원 3단계 트랜잭션 로그에서 복구의 다시 실행 단계를 실행합니다.

복원 4단계트랜잭션 로그에서 복구의 실행 취소 단계를 실행합니다.

백업의 두 단계에 소요되는 전체 시간은 복원 2단계에 필요한 시간과 얼추 비슷합니다(하드웨어 및 서버가 비슷하고 사용자 활동이 없다고 가정할 경우). 데이터 파일이 크고 0으로 초기화되어야 하는 경우(SQL Server 2000의 동작이며 SQL Server 2005에서는 기본 동작) 복원 1단계에 걸리는 시간이 길어질 수 있습니다.

이를 방지하려면 복원을 시작하기 전에 기존 파일을 삭제하지 마십시오. 또는 즉시 초기화를 사용하여 파일이 매우 빠른 속도로 만들어지도록 합니다. 이에 대한 자세한 내용은 msdn.microsoft.com/library/ms175935.aspx를 참조하십시오.

복원 3단계와 4단계는 복원된 데이터베이스가 트랜잭션 측면에서 일관성을 갖도록 하기 위해 복구를 실행하는 단계입니다. 이 프로세스는 크래시 복구 동안 데이터베이스에서 수행되는 프로세스와 동일합니다. 복구에 소요되는 시간은 처리해야 하는 트랜잭션 로그의 양에 따라 달라집니다. 예를 들어 백업이 이루어진 시점에 상당히 오랫동안 실행되는 트랜잭션이 활성 상태였다면 해당 트랜잭션에 대한 모든 트랜잭션 로그가 백업에 포함되어 롤백되어야 합니다.

Q: 프로덕션 데이터베이스의 여유 복사본을 제공하기 위해 로그 전달과 데이터베이스 미러링 중 하나를 선택하려고 합니다. 특히 매일 밤 실행하는 인덱스 재작성 작업에서 서버 간에 전송해야 하는 트랜잭션 로그의 양이 걱정됩니다. 미러링에서는 트랜잭션 로그가 아닌 실제 재작성 명령이 전송되고 미러에서 재작성이 수행된다고 들었습니다. 정말인가요? 그렇다면 BULK_LOGGED 복구 모델을 사용한 로그 전달보다도 미러링이 더 좋은 방법인 것 같은데요, 그렇지 않나요?

A: 사실과 다른 이야기를 들으셨네요. 데이터베이스 미러링은 주 데이터베이스에서 미러 서버로 실제 트랜잭션 로그 레코드가 전송되고, 전송된 트랜잭션 로그 레코드가 미러 데이터베이스에서 "재생"되는 방식으로 동작합니다. 여기에서는 어떤 종류의 변환이나 필터링, 또는 미러링된 데이터베이스에 대한 T-SQL 명령 가로채기도 수행되지 않습니다.

데이터베이스 미러링은 FULL 복구 모델만 지원하며 이는 인덱스 재작성 작업이 항상 완전히 로그에 기록됨을 의미합니다. 즉, 이는 관련 인덱스의 크기에 따라 상당한 양의 트랜잭션 로그가 생성되고, 그에 따라 주 데이터베이스와 로그 파일이 커지고, 미러로 로그 레코드를 전송하기 위한 네트워크 대역폭 사용량이 높아질 수 있음을 의미합니다.

데이터베이스 미러링은 실시간 로그 전달이라고 보면 됩니다. 사실 SQL Server 2005 개발 작업 초기에는 실시간 로그 전달이라는 이름이 사용되었습니다. 로그 전달에서는 주 데이터베이스 트랜잭션 로그의 백업이 정기적으로 보조 서버로 전달되어 보조 데이터베이스에서 복원됩니다.

로그 전달은 FULL 복구 모델과 BULK_LOGGED 복구 모델을 지원합니다. FULL 복구 모델을 사용하는 로그 전달 데이터베이스의 인덱스 재작성 작업에서는 미러링된 데이터베이스에 대해 생성되는 양과 동일한 양의 트랜잭션 로그가 생성됩니다. 그러나 로그 전달 데이터베이스 시나리오에서 데이터는 지속적인 흐름이 아닌 하나의 로그 백업(또는 일련의 로그 백업)으로 중복 데이터베이스로 전송됩니다.

인덱스 재작성이 수행될 때 로그 전달 데이터베이스에 BULK_LOGGED 복구 모델이 사용되는 경우에는 최소한의 트랜잭션 로그만 생성됩니다. 그러나 다음 트랜잭션 로그 백업에는 최소한으로 로그에 기록된 인덱스 재작성 작업에 의해 변경된 모든 데이터 파일 익스텐트도 포함됩니다. 이는 BULK_LOGGED 복구 모델에서 인덱스 재작성을 포함하는 로그 백업의 크기가 FULL 복구 모델에서 인덱스 재작성을 포함하는 로그 백업의 크기와 거의 비슷하다는 사실을 의미합니다.

따라서 여유분의 데이터베이스로 전송되어야 하는 정보의 양은 미러링된 데이터베이스의 인덱스 재작성에 대해서나 로그 전달 데이터베이스의 인덱스 재작성에 대해서나 동일합니다. 실질적인 차이는 정보가 전송되는 방법, 즉 지속적으로 전송되느냐 일괄 처리로 전송되느냐에 있습니다.

이 두 가지 접근 방법 중 하나를 선택할 때는 다른 많은 요소도 고려해야 합니다(1회분의 SQL Q&A에서 다루기에는 너무 많음). 결정을 내리기 전에 이러한 모든 요소가 현재 요구 사항에 어떻게 부합하는지 파악해야 합니다(예: 허용 가능한 데이터 손실 제한선, 허용 가능한 가동 중지 시간).

Q: SQL Server 2005를 실행 중인데 데이터베이스 중 하나의 트랜잭션 로그가 계속 커집니다. 데이터베이스는 전체 복구 모드로 설정되어 있으며 트랜잭션 로그 백업을 수행하고 있습니다. 이렇게 하면 트랜잭션 로그가 커지는 현상을 막을 수 있다고 생각했습니다. 정확히 어떤 부분이 잘못되었습니까?

A: 전체 복구 모드에서는 트랜잭션 로그 백업을 수행하는 것이 중요하다는 점은 맞습니다. 그러나 다른 요소들도 트랜잭션 로그의 증가를 유발할 수 있습니다. 관건은 트랜잭션 로그가 필요하도록 만드는 요소, 또는 트랜잭션 로그가 활성 상태일 것을 요구하는 요소가 무엇이냐입니다. 트랜잭션 로그 백업의 부족 외에 이 문제의 원인이 되는 다른 일반적 요소로는 복제, 데이터베이스 미러링, 활성 트랜잭션 등이 있습니다.

복제는 트랜잭션 로그 레코드를 비동기적으로 읽은 다음 트랜잭션을 로드하여 별도의 배포 데이터베이스로 복제하는 방식으로 이루어집니다. 아직 복제 로그 판독기 작업에서 읽지 않은 트랜잭션 로그 레코드는 해제될 수 없습니다. 현재 작업 부하가 많은 양의 트랜잭션 로그 레코드를 생성하고 복제 로그 판독기가 실행되는 간격이 길게 설정되어 있는 경우 많은 레코드가 누적되어 트랜잭션 로그의 증가를 유발할 수 있습니다.

비동기 데이터베이스 미러링을 실행 중인 경우 주 데이터베이스에서 미러 데이터베이스로 아직 전송되지 않은 트랜잭션 로그 레코드의 백로그가 존재할 수 있습니다(데이터베이스 미러링 SEND 큐라고 함). 트랜잭션 로그 레코드는 성공적으로 전송될 때까지 해제될 수 없습니다. 트랜잭션 로그 레코드가 생성되는 속도가 빠른 상태에서 네트워크 대역폭이 제한적이거나 다른 하드웨어 문제가 있는 경우 백로그의 크기가 상당히 커지면서 트랜잭션 로그의 증가를 유발할 수 있습니다.

마지막으로, 사용자가 명시적인 트랜잭션을 시작한 후에(예: BEGIN TRAN 문 사용) 수정 작업을 실행하는 경우(예: DDL 문 또는 삽입/업데이트/삭제 작업) 생성되는 트랜잭션 로그 레코드는 사용자가 트랜잭션을 커밋하거나 롤백할 때까지 유지되어야 합니다. 이는 다른 트랜잭션에 의해 생성되는 이후의 모든 트랜잭션 로그 레코드 역시 해제될 수 없음을 의미합니다. 트랜잭션 로그는 선택적으로 해제할 수 없기 때문입니다. 예를 들어 이 사용자가 트랜잭션을 완료하지 않고 퇴근할 경우 트랜잭션 로그 레코드는 계속 생성되는데 해제는 되지 않으므로 트랜잭션 로그는 계속 커지게 됩니다.

다음과 같이 sys.databases 시스템 카탈로그 뷰를 쿼리하고 log_reuse_wait_desc 열을 검사하면 트랜잭션 로그가 해제될 수 없는 이유를 찾을 수 있습니다.

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

원인이 활성 트랜잭션으로 밝혀질 경우 DBCC OPENTRAN 문을 사용하여 해당 트랜잭션에 대한 더 자세한 정보를 얻을 수 있습니다.

DBCC OPENTRAN ('dbname')

Q 손상된 상태에서 복구할 때는 백업에서 복원하는 방법을 사용하고 REPAIR_ALLOW_DATA_LOSS는 최후의 수단으로 사용해야 한다고 들었습니다. "repair for SQL Server 2005"를 사용하면 안 되는 이유와, 이것이 그렇게 "위험하다면" 제품에 포함된 이유는 무엇인지 설명해 주십시오.

A 먼저 말씀드리자면 SQL Server 2005용 repair 옵션을 작성한 사람은 바로 접니다. REPAIR_ALLOW_DATA_LOSS(이하 간단히 '복구'로 지칭함)의 문제는 동작 방식이 명확하지 않다는 데 있습니다. 이 복구 옵션의 이름을 이렇게 정한 이유는 실행할 경우 데이터베이스에서 데이터가 손실될 수 있음을 알리기 위해서입니다. 일반적으로 이 기능은 데이터베이스에서 손상된 구조를 삭제한 후 삭제된 구조를 참조했거나 이 구조에 의해 참조된 다른 모든 요소를 수정하는 방식으로 손상된 데이터베이스 구조를 복구합니다. 복구는 데이터베이스를 구조적 측면에서 일관되게 만들기 위한 최후의 수단으로 고안되었지 사용자 데이터를 살리기 위한 기능은 아닙니다. 복구는 굳이 사용자 데이터를 삭제하지는 않지만 굳이 사용자 데이터를 살리지도 않습니다.

무책임한 복구 방식으로 보일지 모르겠지만 이 옵션이 필요한 상황에서는 가장 빠르고 가장 안정적인 손상 복구 방법을 제공합니다. 재해 복구 상황에서는 속도가 가장 중요하며 정확성이 절대적으로 요구됩니다. 모든 경우에 대해 빠르고 정확하게 동작함을 입증할 수 있는 더 복잡한 복구 알고리즘을 엔지니어링하기란 거의 불가능합니다. 예를 들어 복구 코드에는 두 개의 인덱스에 동일한 페이지 또는 익스텐트가 할당된 경우를 해결하기 위한 몇 가지 복잡한 알고리즘이 있지만 알고리즘은 대부분 복구 + 수정입니다.

어쨌든 복구에는 알아야 할 몇 가지 문제가 있습니다.

복구는 손상된 구조를 삭제할 때 외래 키 제약 조건을 고려하지 않으므로 다른 테이블과 외래 키 관계를 가진 테이블에서 레코드를 삭제할 수 있습니다. 복구를 실행한 후에 DBCC CHECKCONSTRAINTS를 실행하지 않고는 이러한 삭제가 발생했는지 여부를 확인할 방법이 없습니다.
복구는 응용 프로그램 수준에서 정의된 기본 비즈니스 논리 또는 데이터 관계를 고려하지 않으며, 고려할 수도 없습니다. 이러한 비즈니스 논리 또는 데이터 관계는 일부 데이터가 삭제될 경우 깨질 수 있습니다. 마찬가지로 응용 프로그램에 기본 제공되는 일관성 확인을 실행하지 않고는 깨진 항목이 있는지 여부를 확인할 방법이 없습니다.
일부 복구 작업은 복제할 수 없습니다. 피어 투 피어 토폴로지의 게시자 또는 노드에서 복구를 실행하면 토폴로지 내에 불일치가 발생할 수 있습니다. 이러한 불일치는 수동으로 수정해야 합니다.

이러한 이유로 손상으로부터 복구할 때는 복구 옵션을 실행하는 것보다 백업을 사용하는 편이 항상 더 좋은 방법입니다. 그럼에도 제품에 복구 기능이 제공되는 이유는 데이터베이스가 손상되었지만 백업은 없고 데이터베이스를 신속하게 온라인 상태로 되돌려야 하는 경우가 발생할 가능성이 상존하기 때문입니다.

Q: 얼마 전 DBA로 새 직장에 들어가면서 여러 응용 프로그램과 이들의 백 엔드 데이터베이스를 담당하게 되었습니다. 그런데 응용 프로그램 중 하나의 업데이트 성능이 상당히 떨어집니다. 조사 결과 응용 프로그램에서 사용하는 테이블 각각에 엄청나게 많은 수의 인덱스가 있다는 사실을 알게 되었습니다. 알아보니 이전 DBA가 습관적으로 각 테이블 열의 인덱스에 몇 가지 조합을 더해 추가했던 것 같습니다. 이 인덱스가 전부 필요하다고는 생각하지 않습니다. 삭제해도 안전한 인덱스를 구분할 수 있는 방법이 있습니까? 현재 SQL Server 2005를 실행하고 있습니다.

A: 짐작하신 대로 많은 수의 인덱스는 열악한 성능의 주 원인일 가능성이 큽니다. 테이블에서 행이 삽입, 업데이트 또는 삭제될 때마다 모든 비클러스터형 인덱스에서 해당 작업이 수행되어야 합니다. 이는 I/O, CPU 사용률 및 트랜젝션 로그 생성 측면에서 많은 오버헤드를 일으킵니다.

SQL Server 2000에서는 어떤 인덱스가 사용 중인지 확인하는 유일한 방법은 프로파일링을 사용하여 쿼리 계획을 검사하는 것이었습니다. SQL Server 2005에는 인덱스 사용을 추적하는 새로운 DMV(동적 관리 뷰)인 sys.dm_db_index_usage_stats가 있습니다.

이 DMV는 자신이 속한 데이터베이스가 시작된 이후 인덱스가 사용될 때마다 그 시점과 사용된 방법을 추적합니다. 모든 데이터베이스에 대한 통계는 SQL Server가 종료될 때 손실되며 단일 데이터베이스에 대한 통계는 해당 데이터베이스가 닫히거나 분리될 때 손실됩니다. 요점은 인덱스가 출력에 나타나지 않으면 데이터베이스가 시작된 이후 이 인덱스는 사용되지 않았다는 것입니다.

시간 경과에 따른 인덱스 사용을 추적하는 간단한 방법은 정기적으로 DMV 출력 스냅숏을 만든 다음 이 스냅숏들을 서로 비교하는 것입니다. 많은 사람들이 놓치는 부분은 전체 비즈니스 주기에 걸쳐 인덱스 사용을 추적해야 한다는 점입니다. 하루에 해당하는 스냅숏만 만든 경우 사용되지 않은 인덱스가 여러 개 발견될 수 있습니다. 그러나 예를 들어 이러한 인덱스가 월말 보고서 실행 속도를 높이기 위해 사용된다면 이 인덱스는 제거하면 안 됩니다. 인덱스가 전체 비즈니스 주기 동안 사용되지 않았다면 이를 삭제하여 공간과 성능을 확보할 수 있습니다.

필자의 블로그 게시물(sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx)에서 정기적으로 DMV 스냅숏을 만드는 데 사용할 수 있는 간단한 코드를 볼 수 있습니다.

Paul S. Randal은 SQLskills.com의 관리 이사이자 SQL Server MVP이며 SQL Server 2005용 DBCC CHECKDB/repair를 작성했고 SQL Server 2008 개발 과정에서 핵심 저장소 엔진 부분을 담당했습니다. 재해 복구, 고가용성 및 데이터베이스 유지 관리 분야의 전문가인 Paul은 각종 컨퍼런스의 단골 발표자이기도 합니다. 블로그 주소는 SQLskills.com/blogs/paul입니다.

추가 리소스