[MSSQL] VLF 개수에 따른 성능 차이

트랜잭션 로그와 VLF

---

SQL Server 는 각 트랜잭션 및 데이터베이스 수정 내용을 기록하는 트랜잭션 로그가 있습니다.

트랜잭션 로그 파일은 VLF (Virtual Log File) 로 구성됩니다.

지금은 VLF 개수에 따른 성능 차이를 포스팅하는게 목적이기 때문에 개념에 대해서는 설명하지 않습니다.

아래 URL 에서 개념에 대해 파악할 수 있습니다.

https://docs.microsoft.com/ko-kr/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017

https://docs.microsoft.com/ko-kr/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-2017#physical_arch

VLF 에 따른 성능 테스트

---

두 개의 같은 로그 파일 크기에 VLF 수만 다르게 해서 테스트를 진행해 보겠습니다.

DATABASE 생성

LOG1M_AUTO1M 데이터베이스를 생성한 후, 트랜잭션 로그 파일을 1M 씩 증가시켜 300MB 까지 만듭니다.

CREATE DATABASE LOG1M_AUTO1M
ON PRIMARY ( NAME = 'LOG1M_AUTO1M', FILENAME = 'E:\SQL Server\LOG1M_AUTO1M.mdf', SIZE = 50MB, FILEGROWTH = 10MB )
LOG ON ( NAME = 'LOG1M_AUTO1M_Log', FILENAME = 'E:\SQL Server\LOG1M_AUTO1M_Log.ldf', SIZE = 1MB, FILEGROWTH = 1MB )
GO

BACKUP DATABASE LOG1M_AUTO1M TO DISK = 'E:\SQL Server\LOG1M_AUTO1M.bak'
GO

DECLARE @i INT
DECLARE @query VARCHAR(8000)
SET @i = 2
WHILE @i < 301 BEGIN
  SET @query = 'ALTER DATABASE LOG1M_AUTO1M MODIFY FILE
                  ( NAME = LOG1M_AUTO1M_Log, SIZE = ' + CONVERT(VARCHAR(10), @i) + ')'
  EXEC (@query)
  SET @i = @i + 1
END
GO

LOG300M_AUTO1M 데이터베이스를 생성합니다. 최초 생성시 트랜잭션 로그 파일은 300MB 크기입니다.

CREATE DATABASE LOG300M_AUTO1M
ON PRIMARY ( NAME = 'LOG300M_AUTO1M', FILENAME = 'E:\SQL Server\LOG300M_AUTO1M.mdf', SIZE = 50MB, FILEGROWTH = 10MB )
LOG ON ( NAME = 'LOG300M_AUTO1M_Log', FILENAME = 'E:\SQL Server\LOG300M_AUTO1M_Log.ldf', SIZE = 100MB, FILEGROWTH = 1MB )
GO

BACKUP DATABASE LOG300M_AUTO1M TO DISK = 'E:\SQL Server\LOG300M_AUTO1M.bak'
GO

LOG1M_AUTO1M 데이터베이스는 327 개의 VLF 를

LOG300M_AUTO1M 데이터베이스는 8 개의 VLF 를 가지게 됩니다.

테이블/인덱스 생성

생성한 데이터베이스에 테이블/인덱스를 생성한 후 DML 테스트를 진행합니다.

CREATE TABLE T1 (C1 INT)
CREATE INDEX IDX_T1_C1 ON T1 (C1)

INSERT

50,000 건을 INSERT 후 걸리는 ms 를 측정한 결과입니다.

DECLARE @I INT
DECLARE @ST DATETIME
DECLARE @ET DATETIME
SET @I = 1
SET @ST = GETDATE()
WHILE @I < 50001
BEGIN
  INSERT INTO T1 SELECT @I
  SET @I = @I + 1
END
SET @ET = GETDATE()
SELECT DATEDIFF(ms, @ST, @ET) AS INSERT_TEST
GO

INSERT 테스트	LOG1M_AUTO1M	LOG300M_AUTO1M
1차(ms)	14410	13850
2차(ms)	14454	14140
3차(ms)	14650	14193

UPDATE

50,000 건을 UPDATE 후 걸리는 ms 를 측정한 결과입니다.

DECLARE @I INT
DECLARE @ST DATETIME
DECLARE @ET DATETIME
SET @I = 1
SET @ST = GETDATE()
WHILE @I < 50001
BEGIN
  UPDATE T1 SET C1 = C1 - 1 WHERE C1 = @I
  SET @I = @I + 1
END
SET @ET = GETDATE()
SELECT DATEDIFF(ms, @ST, @ET) AS UPDATE_TEST
GO

UPDATE 테스트	LOG1M_AUTO1M	LOG300M_AUTO1M
1차(ms)	19327	18867
2차(ms)	19267	18740
3차(ms)	19763	19173

DELETE

50,000 건을 DELETE 후 걸리는 ms 를 측정한 결과입니다.

DECLARE @I INT
DECLARE @ST DATETIME
DECLARE @ET DATETIME
SET @I = 1
SET @ST = GETDATE()
WHILE @I < 50001
BEGIN
  DELETE T1 WHERE C1 = @I - 1
  SET @I = @I + 1
END
SET @ET = GETDATE()
SELECT DATEDIFF(ms, @ST, @ET) AS DELETE_TEST
GO

DELETE 테스트	LOG1M_AUTO1M	LOG300M_AUTO1M
1차(ms)	16840	15993
2차(ms)	16643	16333
3차(ms)	16934	16607

BACKUP LOG

위의 DML 작업을 한 후 BACKUP LOG 에 걸리는 시간을 측정한 결과입니다.

BACKUP LOG LOG1M_AUTO1M TO DISK = 'E:\SQL Server\LOG1M_AUTO1M_Log.bak'
BACKUP LOG LOG300M_AUTO1M TO DISK = 'E:\SQL Server\LOG300M_AUTO1M_Log.bak'

Log Backup	LOG1M_AUTO1M	LOG300M_AUTO1M
1차	13038개의 페이지를 3.282초 동안 처리했습니다(31.034MB/초)	13020개의 페이지를 2.698초 동안 처리했습니다(37.701MB/초)
2차	13027개의 페이지를 3.181초 동안 처리했습니다(31.994MB/초)	13021개의 페이지를 2.790초 동안 처리했습니다(36.458MB/초)
3차	13026개의 페이지를 3.162초 동안 처리했습니다(32.138MB/초)	13188개의 페이지를 2.669초 동안 처리했습니다(38.600MB/초)

VLF 에 따른 성능 테스트 결과

---

VLF 가 많을 수록 DML 및 로그파일의 백업에 걸리는 소요 시간이 소폭이지만 증가하는 것을 알 수 있습니다.

왜그럴까요?

데이터베이스 엔진에서 각각의 VLF 를 관리해야 하기 때문입니다.

관리해야 하는 양이 많아질수록, 비용이 증가하는건 당연합니다.

트랜잭션 로그파일 크기를 너무 작게 줄이면 로그파일이 가득 차 FILEGROWTH 에 의해 크기가 증가하며 동시에 VLF 가 증가하게 되는 문제를 낳을 수 있습니다.

따라서, 트랜잭션 로그 파일 크기를 줄이더라도 크기를 산정하여 진행해야 합니다.