트랜잭션 로그는 내부적으로 여러 개의 가상 로그 파일(VLF)로 나뉘어져 있습니다. 최소 크기는 256KB, 가상 로그 파일의 크기는 고정되어 있지 않으며 로그 파일을 만들거나 확장할 때 데이터베이스 엔진에 의해서 가상 로그 파일 크기가 결정되게 됩니다. 또한 데이터베이스 엔진은 적은 수의 가상 파일을 유지하려고 합니다. 가상 로그 파일의 개수가 많을 경우 백업, 복원, 트리거 등과 같은 로그 관련 작업에 성능에 대한 영향을 주기 때문입니다. 결론은 트랜잭션 패턴을 파악하여 자동으로 로그 확장이 발생하지 않도록 적정한 로그 크기를 할당하는 것이겠지요~ 뻔한 이야기만 했네요... ^^
실제 VLF 개수가 수십 만개로 늘어난 환경에서 RESTORE, Attach 데이터베이스 작업 수행 시 성능에 큰 영향을 준 사례가 있었기에 아래와 같이 정리해봤습니다. 다음 테스트를 통해 VLF 개수 변화를 확인하였습니다.
1. 샘플 데이터베이스를 생성합니다. 트랜잭션 로그는 10MB 로 초기 설정하였습니다.
2. DBCC LOGINFO 명령을 수행하여 VLF 개수와 용량을 확인합니다.
--1개당 약 2.5MB VLF 파일이 4개 생성되었습니다.
트랜잭션 로그 파일의 증가 크기에 따른 VLF 개수는 아래와 같이 생성된다고 합니다.
(하만철님 블로그에서 무단 도용함 ㅋㅋㅋ)
샘플 데이터베이스의 로그 파일 초기 크기를 각각 1MB, 10MB, 100MB, 1GB, 10GB 로 달리하여 VLF 개수와 크기를 확인하였습니다.
트랜잭션 로그 파일 초기 크기가 100MB 일 때, VLF 크기는 약 13MB 8개의 파일이 생성되며 로그 파일 증가 크기를 1MB 로 설정한 뒤 자동 증가가 이루어졌다면.... 아래와 같이 작은 단위의 VLF 파일이 많이 생성되기 때문에 성능에 영향을 미칠 수 있다는 것을 염두해야겠습니다.
트랜잭션 로그의 가상 로그 파일은 백업을 통해 그 개수가 변경되지 않습니다. 비활성 로그가 있다면 SHRINKFILE 을 통해 잘라낼 수 있으며, 이 때 VLF 개수는 줄어들 수 있습니다. 로그 백업을 수행하는 주기 동안 발생하는 전체 트랜잭션을 모니터링 하여 사전에 충분한 로그 파일을 생성해 놓는 것이 중요하다는 결론을 지어봅니다. 하지만 무조건 큰 크기가 좋다고 볼 수 없습니다. 수십 TB 의 크기를 트랜잭션 로그로 생성한다면 하나의 VLF 크기도 수백 GB 가 될 수 있기 때문입니다.
가장 어려운 설정~ 적당히~~
[참고자료]
작성자 : Lai Go / 작성일자 : 2011.04.12
실제 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
