2007~2011/SQL Server2011.04.12 13:48
트랜잭션 로그는 내부적으로 여러 개의 가상 로그 파일(VLF)로 나뉘어져 있습니다. 최소 크기는 256KB, 가상 로그 파일의 크기는 고정되어 있지 않으며 로그 파일을 만들거나 확장할 때 데이터베이스 엔진에 의해서 가상 로그 파일 크기가 결정되게 됩니다. 또한 데이터베이스 엔진은 적은 수의 가상 파일을 유지하려고 합니다. 가상 로그 파일의 개수가 많을 경우 백업, 복원, 트리거 등과 같은 로그 관련 작업에 성능에 대한 영향을 주기 때문입니다. 결론은 트랜잭션 패턴을 파악하여 자동으로 로그 확장이 발생하지 않도록 적정한 로그 크기를 할당하는 것이겠지요~ 뻔한 이야기만 했네요... ^^

실제 VLF 개수가 수십 만개로 늘어난 환경에서 RESTORE, Attach 데이터베이스 작업 수행 시 성능에 큰 영향을 준 사례가 있었기에 아래와 같이 정리해봤습니다. 다음 테스트를 통해 VLF 개수 변화를 확인하였습니다.


1. 샘플 데이터베이스를 생성합니다. 트랜잭션 로그는 10MB 로 초기 설정하였습니다.

CREATE DATABASE LAIGO
ON PRIMARY
(NAME = LAIGO_DATA1,
 FILENAME = N'D:\SQLDATA\LAIGO_DATA1.mdf',
          SIZE = 10MB,
          FILEGROWTH = 50MB)
LOG ON
( NAME = LAIGO_log_file1,
 FILENAME = N'D:\SQLDATA\LAIGO_LOG.ldf',
          SIZE = 10MB,
          FILEGROWTH = 1MB)
GO



2. DBCC LOGINFO 명령을 수행하여 VLF 개수와 용량을 확인합니다.

USE LAIGO
GO

DBCC LOGINFO


--1개당 약 2.5MB  VLF 파일이 4개 생성되었습니다.



트랜잭션 로그 파일의 증가 크기에 따른 VLF 개수는 아래와 같이 생성된다고 합니다.

로그파일 증가크기

VLF 개수

VLF당 크기

1MB 까지

2~4

248KB ~ 334KB

64MB 까지

4

256KB ~ 16MB

1GB 까지

8

8MB ~ 128MB

1GB 초과

16

64MB ~

(하만철님 블로그에서 무단 도용함 ㅋㅋㅋ)


샘플 데이터베이스의 로그 파일 초기 크기를 각각 1MB, 10MB, 100MB, 1GB, 10GB 로 달리하여 VLF 개수와 크기를 확인하였습니다. 

 /* FileSize 
1MB
253952
253952
253952
278528

10MB
2555904
2555904
2555904
2809856

100MB
13041664
13041664
13041664
13041664
13041664
13041664
13041664
13557760

1GB
134152192
134152192
134152192
134152192
134152192
134152192
134152192
134668288


10GB
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
671023104
672063488
*/




트랜잭션 로그 파일 초기 크기가 100MB 일 때, VLF 크기는 약 13MB 8개의 파일이 생성되며 로그 파일 증가 크기를 1MB 로 설정한 뒤 자동 증가가 이루어졌다면.... 아래와 같이 작은 단위의 VLF 파일이 많이 생성되기 때문에 성능에 영향을 미칠 수 있다는 것을 염두해야겠습니다. 

/* 
13041664
13041664
13041664
13041664
13041664
13041664
13041664
13557760
253952
253952
253952
286720
253952
253952
253952
......
*/ 



트랜잭션 로그의 가상 로그 파일은 백업을 통해 그 개수가 변경되지 않습니다. 비활성 로그가 있다면 SHRINKFILE 을 통해 잘라낼 수 있으며, 이 때 VLF 개수는 줄어들 수 있습니다. 로그 백업을 수행하는 주기 동안 발생하는 전체 트랜잭션을 모니터링 하여 사전에 충분한 로그 파일을 생성해 놓는 것이 중요하다는 결론을 지어봅니다. 하지만 무조건 큰 크기가 좋다고 볼 수 없습니다. 수십 TB 의 크기를 트랜잭션 로그로 생성한다면 하나의 VLF 크기도 수백 GB 가 될 수 있기 때문입니다.

가장 어려운 설정~ 적당히~~


[참고자료]
Transaction Log Physical Architecture

작성자 : Lai Go / 작성일자 : 2011.04.12 
Posted by Lai Go