VLF 개수가 UPDATE작업에 미치는 영향 테스트
로그파일의 VLF 개수가 성능에 어느정도 영향을 미치는지 간단히 확인해 보았습니다.
테스트는 2개의 DB를 생성하고, VLF 개수를 다르게 한 다음 UPDATE 작업에 걸리는 시간을 측정해 보았습니다.
테스트를 위해 두 개의 DB를 생성한 다음 백업을 해서 로그가 잘 쌓이도록 한다.
로그파일 크기는 1MB로 한다.
|
-- DB 생성 CREATE DATABASE logTest1 ON PRIMARY ( NAME = logTest1_Data , FILENAME = 'D:\logTest1_Data_PRIMARY.mdf' ) LOG ON ( NAME = logTest1_Log , FILENAME = 'D:\logTest1_LOG_01.ldf' , SIZE = 1MB , FILEGROWTH = 1MB ) -- 백업 BACKUP DATABASE logTest1 TO DISK = 'd:\temp\logTest1.BAK' |
현재 로그파일 상태를 확인해 보면 4개의 VLF가 생성되어 있는걸 볼 수 있다.
|
DBCC loginfo FileId : 로그파일의 id FileSize : VLF의 크기(byte) StartOffset : 전체 트랜잭션 로그 파일에서의 위치 FSeqNo : VLF Sequence Number. Status : VLF의 상태(0:비활성, 2:활성VLF) Parity : 알듯말듯 도대체 뭔지 모르겠다. 나중에 확인해 보자!!! CreateLSN : 알듯말듯 도대체 뭔지 모르겠다. 나중에 확인해 보자!!! ## 로그파일을 증가시킬 때 VLF의 개수는 아래와 같은 공식에 의해 결정된다고 한다.
# 테스트로 테이블 생성시 로그파일 크기를 10GB로 설정했더니 640MB짜리 VLF가 생성되었다. |
이제 로그파일의 크기를 1MB씩 증가시켜 VLF개수를 마구마구 증가시켜 보도록 한다.
먼저 50개만 만들어 보도록 하자.
|
DECLARE @i INT DECLARE @qry VARCHAR(8000) SET @i = 2 WHILE @i < 14 BEGIN SET @qry = 'ALTER DATABASE logTest1 MODIFY FILE ( NAME = logTest1_Log , Size = ' +CONVERT(VARCHAR(10), @i) + ')' EXEC (@qry) SET @i = @i + 1 END WHILE문에 의해 12번이 실행되어서 48개의 VLF가 추가되었다. 총 52개의 VLF가 생성되었고, 로그파일의 전체용량은 13MB가 되었다. |
그리고 비교 테스트를 위해 logTest2 DB를 생성한 다음 이 DB의 경우 초기 로그파일의 크기를 13MB로 지정하여
VLF 개수를 4개가 되도록 한다.
|
-- DB 생성 CREATE DATABASE logTest2 ON PRIMARY ( NAME = logTest2_Data , FILENAME = 'D:\logTest2_Data_PRIMARY.mdf' ) LOG ON ( NAME = logTest2_Log , FILENAME = 'D:\logTest2_LOG_01.ldf' , SIZE = 13MB , FILEGROWTH = 1MB ) -- 백업 BACKUP DATABASE logTest2 TO DISK = 'd:\temp\logTest2.BAK' |
이제 logTest1, logTest2 두 개의 DB에서 테이블을 하나 생성한 다음 insert, update, delete 작업에 대해 성능차이가 있는지 보도록 하겠다.
|
-- INSERT Test -- logTest1 DB에서 시간 확인 USE logTest1 GO -- 테이블 생성 CREATE TABLE t (a INT) -- 데이터 INSERT DECLARE @i INT DECLARE @s DATETIME, @e DATETIME SET @i = 1 SET @s = GETDATE() WHILE @i < 20001 BEGIN INSERT INTO t SELECT @i SET @i = @i + 1 END SET @e = GETDATE() -- 걸린 시간 측정 SELECT DATEDIFF(ms, @s, @e) Result1> 4063 Result2> 4170 Result3> 4186 -- logTest2 DB에서 시간 확인 USE logTest2 GO -- 테이블 생성 CREATE TABLE t (a INT) -- 데이터 INSERT DECLARE @i INT DECLARE @s DATETIME, @e DATETIME SET @i = 1 SET @s = GETDATE() WHILE @i < 20001 BEGIN INSERT INTO t SELECT @i SET @i = @i + 1 END SET @e = GETDATE() -- 걸린 시간 측정 SELECT DATEDIFF(ms, @s, @e) Result1> 3876 Result2> 3876 Result3> 3890 -- UPDATE TEST -- logTest1 DB Test USE logTest1 GO -- Index 생성 CREATE INDEX idx_t ON t (a) -- Log BACKUP(Update와 Backup을 3회 반복했을 때 각 걸린 시간) BACKUP LOG logTest1 TO DISK = 'D:\logTest1.TRN' GO 파일15에서데이터베이스'logTest1', 파일'logTest1_Log'에대해1254개의페이지를처리했습니다. BACKUP LOG이(가) 1254개의페이지를0.939초동안처리했습니다(10.935MB/초). 파일16에서데이터베이스'logTest1', 파일'logTest1_Log'에대해1254개의페이지를처리했습니다. BACKUP LOG이(가) 1254개의페이지를1.217초동안처리했습니다(8.440MB/초). 파일17에서데이터베이스'logTest1', 파일'logTest1_Log'에대해1254개의페이지를처리했습니다. BACKUP LOG이(가) 1254개의페이지를0.795초동안처리했습니다(12.919MB/초). -- 데이터UPDATE DECLARE @i INT DECLARE @s DATETIME, @e DATETIME SET @i = 0 SET @s = GETDATE() WHILE @i < 10001 BEGIN UPDATE t SET a=a-1 WHERE a=@i SET @i = @i + 1 END SET @e = GETDATE() -- 걸린시간측정 SELECT DATEDIFF(ms, @s, @e) Result1> 3906 Result2> 3720 Result3> 3923 -- logTest2 DB Test USE logTest2 GO -- Index 생성 CREATE INDEX idx_t ON t (a) -- Log BACKUP(Update와 Backup을 3회 반복했을 때 각 걸린 시간) BACKUP LOG logTest2 TO DISK = 'D:\logTest2.TRN' GO 파일11에서데이터베이스'logTest2', 파일'logTest2_Log'에대해1255개의페이지를처리했습니다. BACKUP LOG이(가) 1255개의페이지를0.653초동안처리했습니다(15.734MB/초). 파일12에서데이터베이스'logTest2', 파일'logTest2_Log'에대해1255개의페이지를처리했습니다. BACKUP LOG이(가) 1255개의페이지를0.778초동안처리했습니다(13.205MB/초). 파일13에서데이터베이스'logTest2', 파일'logTest2_Log'에대해1254개의페이지를처리했습니다. BACKUP LOG이(가) 1254개의페이지를0.689초동안처리했습니다(14.909MB/초). -- 데이터UPDATE DECLARE @i INT DECLARE @s DATETIME, @e DATETIME SET @i = 0 SET @s = GETDATE() WHILE @i < 10001 BEGIN UPDATE t SET a=a-1 WHERE a=@i SET @i = @i + 1 END SET @e = GETDATE() -- 걸린시간측정 SELECT DATEDIFF(ms, @s, @e) Result1> 3610 Result2> 3673 Result3> 3563 -- DELETE TEST -- logTest1 DB Test USE logTest1 GO TRUNCATE TABLE t -- Log BACKUP BACKUP LOG logTest1 TO DISK = 'D:\logTest1.TRN' GO -- 데이터INSERT DECLARE @i INT SET @i = 1 WHILE @i < 20001 BEGIN INSERT INTO t SELECT @i SET @i = @i + 1 END -- Log BACKUP BACKUP LOG logTest1 TO DISK = 'D:\logTest1.TRN' GO -- 데이터DELETE DECLARE @i INT DECLARE @s DATETIME, @e DATETIME SET @i = 1 SET @s = GETDATE() WHILE @i < 20001 BEGIN DELETE t WHERE a=@i SET @i = @i + 1 END SET @e = GETDATE() -- 걸린시간측정 SELECT DATEDIFF(ms, @s, @e) Result1> 5750 Result2> 4983 Result3> 4936 -- logTest2 DB Test USE logTest2 GO TRUNCATE TABLE t -- Log BACKUP BACKUP LOG logTest2 TO DISK = 'D:\logTest2.TRN' GO -- 데이터INSERT DECLARE @i INT SET @i = 1 WHILE @i < 20001 BEGIN INSERT INTO t SELECT @i SET @i = @i + 1 END -- Log BACKUP BACKUP LOG logTest2 TO DISK = 'D:\logTest2.TRN' GO -- 데이터DELETE DECLARE @i INT DECLARE @s DATETIME, @e DATETIME SET @i = 1 SET @s = GETDATE() WHILE @i < 20001 BEGIN DELETE t WHERE a=@i SET @i = @i + 1 END SET @e = GETDATE() -- 걸린시간측정 SELECT DATEDIFF(ms, @s, @e) Result1> 4576 Result2> 4750 Result3> 4690 |
INSERT, UPDATE, DELETE 테스트 결과
|
|
logTest1 |
logTest2 |
|
VLF 개수 |
52 개 |
4 개 |
|
INT형 INSERT 2만건시 시간 |
Result1> 4063 ms Result2> 4170 ms Result3> 4186 ms |
Result1> 3876 ms Result2> 3876 ms Result3> 3890 ms |
|
INT형 UPDATE 1만건시 시간 |
Result1> 3906 ms Result2> 3720 ms Result3> 3923 ms |
Result1> 3610 ms Result2> 3673 ms Result3> 3563 ms |
|
UPDATE후 Log백업 시간 |
0.939초동안처리(10.935MB/초) 1.217초동안처리(8.440MB/초) 0.795초동안처리(12.919MB/초) |
0.653초동안처리(15.734MB/초) 0.778초동안처리(13.205MB/초) 0.689초동안처리(14.909MB/초) |
|
INT형 DELETE 2만건시 시간 |
Result1> 5750 ms Result2> 4983 ms Result3> 4936 ms |
Result1> 4576 ms Result2> 4750 ms Result3> 4690 ms |
VLF 개수를 증가시켜서 위와 같은 방법으로 다시 테스트 해 보았더니 아래와 같은 결과가 나타났다.
|
|
logTest1 |
logTest2 |
|
VLF 개수 |
500 개 (로그파일을 1MB씩 증가시켜 생성) |
16 개 (로그파일을 30MB씩 증가시켜 생성) |
|
로그파일 크기 |
126 MB |
120 MB |
|
INT형 INSERT 20만건시 시간 |
Result1> 40516 ms Result2> 40106 ms Result3> 40826 ms |
Result1> 36813 ms Result2> 36330 ms Result3> 36686 ms |
|
INT형 UPDATE 10만건시 시간 |
Result1> 37703 ms Result2> 36263 ms Result3> 36313 ms |
Result1> 35953 ms Result2> 35593 ms Result3> 34953 ms |
|
UPDATE후 Log백업 시간 |
6.512초동안처리(15.736MB/초) 6.707초동안처리(15.275MB/초) 7.226초동안처리(14.178MB/초) |
5.386초동안처리(19.021MB/초) 5.177초동안처리(19.795MB/초) 5.409초동안처리(18.941MB/초) |
|
INT형 DELETE 20만건시 시간 |
Result1> 49156 ms Result2> 48860 ms Result3> 48975 ms |
Result1> 45550 ms Result2> 45750 ms Result3> 45475 ms |
결과적으로 UPDATE에 의해 로그를 쓰는 시간은 약 10%정도 차이가 있었고,
로그백업 작업의 경우처럼 로그파일을 읽을때는 25% 정도 차이가 있었습니다.
로그파일을 읽고 쓸때 로그파일 내에서 똑 같은 수의 VLF들에 대해 랜덤 IO가 발생할텐데
왜 이정도 차이가 발생하는지는 아직 잘 모르겠습니다.
