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
