VLF 환경과 성능

jevida(강성욱) 2013.11.21 08:33 Views : 10159

VLF 환경과 성능

 

SQL Server에서 로그파일은 데이터 파일처럼 익스텐트로 구성되지 않고 가상 로그 파일(Virtual Log File 이하 VLF)로 구성되어 있습니다.

VLF는 로그 파일의 크기에 따라 가변적으로 변하며 SQL Server에 의해 정해 집니다.

VLF의 최소 단위는 256K이며 트랜잭션 로그가 가장 작은 512K일 때 두 개의 VLF가 생성 됩니다.

 

데이터베이스에 물리적인 로그 파일이 둘 이상 있으면 로그의 끝이 물리적 파일의 가상 로그 파일을 돌아 다시 첫 번째의 물리적인 파일의 첫 번째 가상로그 파일로 데이터를 기록 하게 됩니다.

(가상로그 파일의 순서대로 순환 한다고 생각하면 됩니다.)

 

실습을 위하여 SSMS를 실행하여 다음과 같은 쿼리문을 이용하여 데이터베이스를 생성 합니다.

CREATE DATABASE VLF_TEST

ON PRIMARY

(NAME = 'VLF_Test', FILENAME = 'D:\SSIS_TEST\VLF_TEST.MDF', SIZE = 10MB, FILEGROWTH = 10MB)

LOG ON

(NAME = 'VLF_Test_Log', FILENAME = 'D:\SSIS_TEST\VLF_TEST_LOG.LDF', SIZE = 512KB, FILEGROWTH = 512KB)

GO

 

DBCC LOGINFO (VLF_TEST)

GO

 

 

 

로그 파일의 사이즈를 10M 설정. 4개의 VFL가 생성.

 

 

로그 파일 사이즈를 1000M 설정, 8개의 VLF 생성.

 

 

로그 파일의 사이즈를 50000M 설정. 16개의 VLF 생성.

 

이처럼 로그파일의 크기에 따라 SQL Server가 다양하게 VLF 개수를 생성 합니다.

 

 

VLF에 따른 성능을 살펴 보도록 하겠습니다. 로그파일의 크기를 1M로 설정하고 자동 증가 값을 1M로 설정 합니다.

CREATE DATABASE VLF_TEST

ON PRIMARY

(NAME = 'VLF_Test', FILENAME = 'D:\SSIS_TEST\VLF_TEST.MDF', SIZE = 10MB, FILEGROWTH = 10MB)

LOG ON

(NAME = 'VLF_Test_Log', FILENAME = 'D:\SSIS_TEST\VLF_TEST_LOG.LDF', SIZE = 1MB, FILEGROWTH = 1MB)

GO

 

DBCC LOGINFO (VLF_TEST)

GO

 

데이터를 입력 합니다.

USE VLF_TEST

GO

 

BEGIN TRY

    DROP TABLE TBL_VLF

END TRY BEGIN CATCH END CATCH

GO

 

SELECT * INTO TBL_VLF

FROM AdventureWorks.Sales.SalesOrderDetail

GO

 

DBCC LOGINFO(VLF_TEST)

GO

 

로그가 증가하면서 VLF의 개수가 늘어난 것을 확인 할 수 있습니다.

 

 

로그파일 증가에 따른 성능을 프로파일러를 통하여 비교해 보도록 하겠습니다.

[로파일러] 실행하여 [Database] – [Log File Auto Grow]를 선택 합니다.

 

 

우선 로그사이즈 1MB, 자동 증가 1MB 설정의 실행 화면 입니다.

VFL의 증가가 모니터링 되며 쿼리 수행 시간(Duration) 값이 2144가 걸렸습니다.

 

이번에는 로그 파일의 사이즈를 미리 넉넉하게 만들고 실행해 보도록 하겠습니다.

CREATE DATABASE VLF_TEST

ON PRIMARY

(NAME = 'VLF_Test', FILENAME = 'D:\SSIS_TEST\VLF_TEST.MDF', SIZE = 10MB, FILEGROWTH = 10MB)

LOG ON

(NAME = 'VLF_Test_Log', FILENAME = 'D:\SSIS_TEST\VLF_TEST_LOG.LDF', SIZE = 50MB, FILEGROWTH = 1MB)

GO

 

DBCC LOGINFO (VLF_TEST)

GO

 

 

동일한 쿼리를 실행하도록 합니다.

쿼리 수행 시간이 2144 -> 668로 단축 된 것을 확인 할 수 있습니다.

즉 VLF가 증가하면서 속도가 느려지는 것을 확인 할 수 있습니다.

 

VLF의 증가와 성능은 어떤 연관성이 있을 까요?

VLF가 증가 될 때 디스크는 파일을 늘리기 위하여 IO작업이 발생 합니다. 이때 디스크의 성능이 저하되면서 전체적인 쿼리 수행이 오래 걸리는 것을 확인 할 수 있습니다.

 

이번에는 로그 파일 사이즈를 1MB로 지정하고 증가 값을 100MB로 지정합니다.

그리고 위에서 실습한 쿼리와 동일한 쿼리를 수행 합니다.

1MB의 로그가 가득차면 100MB의 사이즈로 로그 사이즈가 할당 됩니다. 이때 한번에 100MB의 사이즈를 할당 하기 위해 디스크에선 과도한 IO가 발생하는 것을 확인 할 수 있습니다.

 

그리고 증가 사이즈를 1M로 설정하여 동일한 테스트를 합니다. 이때 한번에 1MB씩 여러 번(위의 실습으로는 67번) 할당하며 디스크에서 IO가 발생하는 것을 확인 할 수 있습니다.

 

 

시스템 모니터를 통해 확인해 본 결과 디스크의 IO작업이 발생하면서 전체적인 성능 저하가 발생하는 것을 수치로 확인 할 수 있습니다.

 

 

위의 상황은 이해를 돕기 위하여 단순하게 비교한 것이므로 작은 사이즈의 VLF 증가가 디스크에 영향을 적게 준다는 보장은 할 수 없습니다. 실제 운영에서는 스토리지의 파워에 따라 적절한 전략을 세워야 합니다.

 

가장 좋은 것은 우리는 DBA이기 때문에 모니터링을 통하여 적절한 LOG 사이즈를 파악하며 점검시 미리 늘려서 불필요한 IO의 발생을 막는 것이 최선의 방법이라 생각합니다.

 

그렇다면 VLF의 개수가 성능 외에 다른 운영 이슈는 어떤게 있을 까요?

다음 포스팅에 다루어 보도록 하겠습니다. 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp



No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 41815
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 22519
1614 SQL Server 성능 테스트 프로그램 minsouk 2015.01.17 6105
1613 extended events 를 이용한 wait 분석 minsouk 2014.07.25 6295
1612 동일기준에대해 복수컬럼 PIVOT 사용하기 열이 2014.06.27 23442
1611 SELECT / UPDATE / INSERT 쿼리를 쉽게 만들자... [1] Hisory 2014.05.12 10924
1610 ssms 자동 언어 변경이 되면, 이렇게 설정하세요 minsouk 2014.04.27 9615
1609 [QUERY] 그룹 번호 만들기 이스트럭(강동운) 2014.04.11 9055
1608 [QUERY]연승을 구해보자! [2] 이스트럭(강동운) 2014.04.11 9917
1607 VLF 이스트럭(강동운) 2014.04.11 8168
1606 extended events 용 excel view 공개 버전 minsouk 2014.04.07 6447
1605 [TIP] 한 라인에서 여러 테이블 삭제 하기. [3] 엘리엘1 2014.01.03 8663
1604 GhostRecord(인덱스에서 행 삭제시발생) jevida(강성욱) 2013.11.25 13613
1603 프로시저, 함수, 트리거 생성 정보 보기. jevida(강성욱) 2013.11.25 10675
1602 힙테이블에서 행을 삭제하면어떻게 처리 될까? [1] jevida(강성욱) 2013.11.25 9425
1601 SSMS의 디자이너에서 테이블 수정 시 발생하는 영향 [1] jevida(강성욱) 2013.11.25 10598
1600 SQL Server를 활용한 Perfmon 로그 저장 [1] jevida(강성욱) 2013.11.21 14882
» VLF 환경과 성능 jevida(강성욱) 2013.11.21 10159
1598 Block 모니터링 jevida(강성욱) 2013.11.21 8160
1597 추적파일을 테이블로 로드하기. jevida(강성욱) 2013.11.21 8938
1596 LOGON 트리거 jevida(강성욱) 2013.11.18 6609
1595 DeadLock(교착상태) 모니터 하기 [2] jevida(강성욱) 2013.11.18 11478





XE Login