SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기

 

l  Version : SQL Server

 

SQL Server 데이터베이스에는 모든 트랜잭션과 트랜잭션에 의해 적용된 데이터베이스 수정 내용을 기록하는 트랜잭션 로그가 있다. 트랜잭션 로그는 데이터베이스의 매우 중요한 요소로 시스템 오류가 발생한 경우 데이터베이스를 일관된 상태로 다시 전환하려면 로그가 필요하다.

l  트랜잭션 로그 아키텍처 : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

 

데이터베이스에 대량의 쓰기 작업이 발생하면 트랜잭션 로그에는 변경사항을 기록하기 위해 오버헤드가 발생하면서 로그 쓰기 작업(Write Log) 지연되는 경우가 발생한다. 로그 모니터링을 통하여 Write Log 지연이 발생할 경우 DELAYED_DURABILITY 옵션을 사용하여 해당 문제를 개선할 있다. 옵션을 사용할 경우 트랜잭션 로그의 커밋을 기다리지 않기 때문에 워크로드의 대기 시간이 줄어들어 성능 개선은 있지만 트랜잭션 데이터를 소실할 있기 때문에 주의해서 사용해야 한다. 이번 포스트에서는 DELAYED_DURABILITY 옵션에 대한 특징을 살펴보고, 어떠한 경우에 사용할 있는지 살펴본다.

 

SQL Server 트랜잭션 로그 파일에 데이터를 저장해야 트랜잭션 로그 파일이 저장된 디스크에 직접 데이터를 기록하지 않는다. 대신 모든 데이터는 인메모리 구조인 로그 캐시(로그 버퍼, 또는 로그 블록 이라고 불리기도 한다.) 직렬로 기록된다. 또한 SQL Server OS ACID 원칙을 준수해야 하므로 전체 로그 캐시를 디스크 하위 시스템에 저장하거나 필요한 경우 롤백 되는 트랜잭션 로그 파일로 플러시 한다. 로그 캐시의 크기는 512B ~ 64KB이다.

 

로그 캐시는 특정 조건에서 디스크로 플러시 된다.

l  트랜잭션이 커밋될

l  로그 캐시가 가득 차서 60KB 도달했을

l  Sys.sp_flush_log 실행될

l  CHECKPOINT 프로세스가 완료 되었을

 

SQL Server 로그 캐시가 트랜잭션 로그 파일로 플러시 되기 시작하는 순간 WRITELOG 대기 유형에 등록되고 로그 캐시가 메모리에서 디스크 드라이브의 파일로 데이터 플러시를 완료 때까지 해당 시간이 누적된다. 누적시간이 낮을수록 트랜잭션 로그파일의 쓰기 대기 시간이 낮아 진다.

 

SQL Server에서는 기본적으로 동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록된 후에만 커밋을 성공으로 보고하고 클라이언트에 컨트롤을 반환한다. 따라서 이러한 커밋이 느릴수록 클라이언트에서는 느린 응답을 받을 수밖에 없다. 동기 커밋은 아래와 같은 완전 내구성이 있는 환경의 경우 필수적으로 사용해야한다.

l  시스템에서 데이터 손실을 허용할 없는 경우

l  병목 현상의 원인이 트랜잭션 로그 쓰기 대기 시간이 아닌 경우

 

DELAYED_DURABILITY 옵션을 활성화 경우, 비동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록되기 전에 커밋 성공으로 클라이언트에 컨트롤을 반환하기 때문에 클라이언트에서는 빠른 응답을 얻을 있다. 또한 동시 트랜잭션의 로그 I/O 경합 가능성이 낮아지고, 청크 구성으로 디스크에 플러시하여 경합을 줄이고 처리 속도를 높일 있다. 비동기 커밋의 경우 아래와 같은 환경에서 사용할 있다.

l  약간의 데이터 손실을 허용하는 경우

l  트랜잭션 로그 쓰기 중에 병목 현상이 발생하는 경우

l  작업의 경합률이 높은 경우

 

DELAYED_DURABILITY 옵션은 데이터베이스 수준에서 트랜잭션 내구성 수준을 제어할 있다. 아래 스크립트는 DELAYED_DURABILITY 옵션을 데이터베이스 수준에서 변경한다.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

 

l  DISABLED : 기본값으로 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF}) 상관없이 데이터베이스 커밋된 모든 트랜잭션이 완전 내구성을 가진다. 저장 프로시저를 변경하고 다시 컴파일할 필요가 없다. 지연된 내구성으로 인해 데이터가 위험에 노출되지 않는다.

l  ALLOWED : 트랜잭션의 내구성이 트랜잭션 수준에서 결정된다. 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF}) 의해 결정된다.

l  FORCED : 데이터베이스에 커밋되는 모든 트랜잭션이 지연된 내구성을 가진다. 설정은 지연된 트랜잭션 내구성이 데이터베이스에 유용하고 어플리케이션 코드를 변경하지 않으려는 경우에 유용하다.

 

 

아래 스크립트는 커밋 수준을 설정한다.

DELAYED_DURABILITY = { OFF | ON }

 

l  OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 비동기적이고 지연된 내구성을 갖는 경우를 제외하고 트랜잭션은 완전 내구성을 가진다.

l  ON : DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 동기적으로 완전 내구성있는 경우를 제외하고 트랜잭션은 지연된 내구성을 가진다.

 

아래 스크립트는 저장 프로시저에 커밋 수준을 적용한 예시이다.

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER   
AS BEGIN ATOMIC WITH    
(   
    DELAYED_DURABILITY = ON,   
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,   
    LANGUAGE = N'English'   
)   
/* procedure body goes here */
END

 

 

지연된 트랜잭션 내구성을 강제로 적용할 있도록 COMMIT 구문으로도 확장할 있는데, 데이터베이스 수준에서 DELAYED_DURABILITY DISABLED 또는 FORCED 경우 COMMIT 옵션은 무시된다.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

 

l  OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 CIMMIT 비동기 적으로 지연된 내구성을 갖는 경우를 제외하고 COMMIT 트랜잭션은 완전 내구성을 가진다.

l  ON : DELAYED_DURABILITY = DISABLED 데이터베이스 옵션을 적용하여 COMMIT 동기적이고 완전 내구성 있는 경우를 제외하고 COMMIT 트랜잭션은 지연된 내구성을 가진다.

 

 

지연된 내구성을 사용할 경우 특정 상황에서 데이터를 손실 가능성이 있기 때문에 반드시 해당 비즈니스의 목적과 데이터베이스 성능 등을 고려하여 사용 여부를 결정할 있도록 한다.

 

[참고자료]

l  Control Transaction Durability : https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver16

l  Measure Delayed Durability impact in SQL Server 2016 and later : https://www.mssqltips.com/sqlservertip/6355/measure-delayed-durability-impact-in-sql-server-2016-and-later/

l  Get SQL Server Delayed Durability Advantages Without Configuration Changes : https://www.mssqltips.com/sqlservertip/6324/get-sql-server-delayed-durability-advantages-without-configuration-changes/

l  How to handle the SQL Server WRITELOG wait type : https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

l  Improve SQL Server transaction log performance with Delayed Durability : https://www.sqlshack.com/improve-sql-server-transaction-log-performance-with-delayed-durability/

l  The Transaction Log (SQL Server) : https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16

l  SQL Server Transaction Log Architecture and Management Guide : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

 

 

 

2022-10-30/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, DELAYED_DURABILITY, 트랜잭션 로그 비동기 커밋, WAL, Write Log

SQL Server 에서 AWS S3 직접 백업하기

 

l  Version : SQL Server 2022

 

SQL Server 2022부터는 데이터 플랫폼에 통합 오브젝트 스토리지를 도입하여 Azure Storage외에도 AWS S3 호환 오브젝트 스토리지도 사용할 있다. , SQL Server에서 AWS S3 직접 백업을 있게 되었다.

기존 SQL Server 2019 경우 Microsoft Azure 저장소만 가능 하였다. SQL Server 2022 부터 도입된 S3 REST API SQL Server에서 AWS S3 직접 백업, 복원을 있어, 백업 이동에 따른 프로세스 단축 대용량 백업을 진행할 경우에도 로컬 공간이 부족하여도 백업을 진행할 있게 되었다.

 

지원되는 백업 종류

l  전체 백업

l  차등 백업

l  트랜잭션 로그 백업

l  복사 전용 백업 (COPY ONLY)

 

지원되지 않는 백업 종류

l  스냅샷 백업 (FILE_SNAPSHOT)

l  미러 백업 (MIRROR)

 

AWS S3버킷으로 백업 복원을 수행 하기위해서는 아래 정보가 필요 하다.

l  S3 버킷이름

l  S3 URI

l  S3 URL

l  IAM 액세스 ID

l  IAM 액세스

 

AWS IAM(Idnetity Access Management) 정책은 사용자의 권한 또는 특정 서비스에 대해 수행할 있는 역할을 정의한다. SQL Server 2022에서 S3 버킷으로 직접 데이터베이스 백업을 수행하려면 ListBucket PutObject 권한이 필요하다.

l  ListBucket : ListBucket 액세스는 요청의 인증된 발신자가 소유한 모든 버킷 목록을 반환한다.

l  PutObject : PutObject 액세스를 통해 버킷에 객체를 작성하고 추가할 있다.

 

SQL Server에서 AWS S3 백업을 하기 위해 우선 자격증명을 만들어야 한다. 아래는 자격증명을 만드는 스크립트이다.

CREATE CREDENTIAL   [s3://<endpoint>:<port>/<bucket>]
WITH   
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';

 

l  [s3://<endpoint>:<port>/<bucket>] : 자격 증명 이름에는 S3 버킷 이름이 포함되어야 한다. S3:// 시작하고 접두사 HTTPS:// 제거한 S3 버킷 URL 따른다.

l  IDENTITY : S3 Access Key 값으로, 자격 증명에서 S3 커넥터를 사용하고 있음을 나타낸다.

l  SECRET : 접근키 ID 비밀 키를 콜론으로 구분하여 입력한다. 콜론 기호는 구분자 역할을 한다.  

 

 

 

백업 명령은 기존에 사용하던 백업문과 거의 동일하다. 다만 백업 위치가 DISK 아닌 URL 사용한다.

BACKUP DATABASE [DatabaseName] TO URL = 'CredentialName\Backupfilename.bak'

 

 

백업 완료 S3 확인해보면 백업파일이 생성된 것을 확인할 있다.

 

아래 스크립트는 백업하려는 데이터베이스를 여러 스트라이프된 백업 파일로 오브젝트 스토리지 엔드포인트를 사용하여 백업을 수행한다.

BACKUP DATABASE <db_name>
TO      URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    FORMAT -- overwrite
,       STATS               = 10
,       COMPRESSION;

 

 

 

아래 스크립트는 오브젝트 스토리지 엔드포인트 위치에서 데이터베이스 복원을 수행한다.

RESTORE DATABASE <db_name>
FROM    URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    REPLACE -- overwrite
,       STATS = 10;

 

 

데이터를 저장하려면 S3 호환 오브젝트 스토리지 공급자는 파트(Part) 불리는 여러 블록으로 파일을 분할해야 한다. 파일은 최대 10,000개의 파트로 분할 있으며, 파트의 크기는 5MB ~ 20MB이다. 범위는 매개변수 MAXTRANSFERSIZE 통해 T-SQL BACKUP 명령으로 제어할 있다. MAXTRANSFERSIZE 기본값은 10MB 이므로 파트의 기본 크기는 10MB이다.  , 단일 백업 파일의 크기는 MAXTRANSFERSIZE 기본값으로 설정되어 있을 경우 100,000MB이다. 백업 스트라이프가 100,000MB 초과할 경우 아래와 같은 오류 메시지를 반환한다.

Msg 3202, Level 16, State 1, Line 161
Write on 's3://<endpoint>:<port>/<bucket>/<path>/<db_name>.bak' failed: 87(The parameter is incorrect.)
Msg 3013, Level 16, State 1, Line 161
BACKUP DATABASE is terminating abnormally.

 

단일 백업 파일의 크기가 100,000MB 보다 파일을 백업 해야 경우 최대 64개의 URL 스트라이프 백업을 지원한다. , 가능한 최대 백업 파일 크기는 10,000part * MAXTRANSFERSIZE * URL이다. 압축 백업을 사용할 경우 백업 파일 사이즈가 현저히 많이 줄어들기 때문에 백업 압축 옵션을 적극 활용할 있도록 한다.

아래 예제는 MAXTRANSFERSIZE 사용하여 20MB으로 설정하고, 백업 압축 암호화한다.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>;
GO
 
CREATE CERTIFICATE AdventureWorks2019Cert
    WITH SUBJECT = 'AdventureWorks2019 Backup Certificate';
GO
-- Backup database
BACKUP DATABASE AdventureWorks2019
TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2019_Encrypt.bak'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorks2019Cert)
GO

 

백업 복원 엔진에서 URL 길이는 259바이트로 제한되어 있기 때문에 s3:// 제외하고 사용자의 경로 길이(호스트 이름 + 개체키) 259 - 5 = 254글자까지 입력할 있다. 만약 URL 길이를 초과할 경우 아래와 같은 오류가 반환된다.

SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together'

 

S3 스토리지는 SQL 호스트와 S3 서버간의 시간 차이가 15분을 초과할 마다 연결을 거부하고 “InvalidSignatureException”오류를 SQL Server 보낸다. SQL Server에서는 아래와 같은 오류로 반환된다.

Msg 3201, Level 16, State 1, Line 28
Cannot open backup device '<path>'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 28
BACKUP DATABASE is terminating abnormally.

 

 

[참고자료]

l  SQL Server backup to URL for S3-compatible object storage : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16

l  SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting : https://learn.microsoft.com/ko-kr/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage-best-practices-and-troubleshooting?view=sql-server-ver16

l  https://www.mssqltips.com/sqlservertip/7301/sql-server-2022-backup-restore-aws-s3-storage/

l  https://www.mssqltips.com/sqlservertip/7302/backup-sql-server-2022-database-aws-s3-storage/

 

 

 

2022-10-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, AWS S3 backup, S3 백업, 클라우드 백업, cloud backup

VM 환경의 SQL Server에서 할당된 CPU 모두 사용하지 못하는 현상

 

·       Version : SQL Server

 

Virtual Machine (VM) 성능이 향상됨에 따라 많은 시스템들이 Physical 서버에서 VM 머신으로 마이그레이션을 진행하고 있다. 이번 포스트는 Physical머신에서 VM으로 마이그레이션 SQL Server에서 할당된 CPU 모두 사용하지 못하는 성능 문제에 대해서 다룬다.

 

Host Server

OS

Windows Server 2019 Standard

CPU

2 socket (64 Core)

RAM

128GB

 

VM Server

OS

Windows Server 2019 Standard

SQL Server

SQL Server 2016 Standard

CPU

8 Core

RAM

32GB

 

아래 그림을 보면 가상머신에 할당된 CPU 8 Core 에서 4 Core 사용하는것을 확인할 있다.

 

아래 쿼리를 사용하여 실제 SQL Server 사용중인 CPU 확인할 있다. 역시 할당된 8개의 코어중에 4개만 사용하고 있음이 표시되었다.

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers

 

 

원인을 확인결과 VM 머신의 세팅에 문제가 있다는 것을 확인하였다. 필자가 구성한 VM환경을 보면, 8 소켓에 8Core 설정되어 있는것을 확인할 있다. 뜻은 1소켓 1 코어가 8 할당되었다는 의미이다. SQL 공식 다큐먼트를 보면 Standard 경우 4소켓 또는 24코어까지만 지원된다고 되어 있다. 소켓의 개수가 8개로 세팅되어, 실제 4 소켓만 동작을 하기 때문에 4Core 동작을 하게 된것이다.

VM 환경을 변경하여, 1소켓 8 Core 설정을 변경한 정상적으로 모든 CPU 사용하는 것을 확인하였다.

 

VM 환경을 변경하는것은 시스템 운영에 문제가 발생할 있으므로 반드시 시스템 담당자와 상의해서 진행할 있도록 한다.

 

아래 스크립트는 현재 구성된 VM 환경에서 SQL Server 할당된 CPU 모두 사용가능한지 쉽게 확인한다.

----------------------------------------------------------------------------------------------------------------

-- CPU VISIABLE ONLINE CHECK

----------------------------------------------------------------------------------------------------------------

DECLARE @OnlineCpuCount int

DECLARE @LogicalCpuCount int

 

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

 

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',

   CASE

     WHEN @OnlineCpuCount < @LogicalCpuCount

     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'

     ELSE 'You are using all CPUs assigned to O/S. GOOD!'

   END as 'CPU Usage Desc'

----------------------------------------------------------------------------------------------------------------

GO

 

 

 

2020-03-03/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, VM, Virtual Machine, CPU 할당, 가상머신 SQL, SQL on VM, dm_os_schedulers, dm_os_sys_info

SQL Server 복원 성능 최적화

 

·       Version : SQL Server

 

SQL Server에서 백업 파일을 복원할때 빠르게 복원하기 위한 최적화 방법을 소개한다. 방법을 사용한다고 해서 무조건 빠르게 복원되지는 않으며, 사용할 있는 시스템 리소스에 따라 최적화된 옵션을 제공함으로써 빠르게 복원할 있게 유도 하는 것이다.

 

데이터베이스 백업 복원에 대한 통계를 확인하기 위해 추적 플래그 3213, 3605 설정한다.

DBCC TRACEON (3213, -1)

DBCC TRACEON (3605, -1)

 

데이터베이스를 복원하면, SQL 이벤트 로그에서 아래와 같은 내용을 확인할 있다.

 

기본 설정을 사용하면  최대 전송크기는 1024K 이고 버퍼수는 6인것을 확인할 있다. 이때 사용되는 버퍼 공간은 6MB이다.

버퍼공간 = 최대 전송크기 X 버퍼

 

여기서 주목해야 부분은 메모리 제한(Memory limit) 이다. 현재 필자의 메모리 제한은 4095 MB (사용자 마다 다름)이지만 사용되는 버퍼 공간은 6MB 이다. 따라서 버퍼 공간을 늘려 복원 속도를 높일 있다. 최대 전송 크기 버퍼수 변경은 데이터베이스 복원시 추가 매개변수로 사용할 있다.

아래 예시는 최대 메모리 제한까지 사용하기 위해서 MAXTRANSFERSIZE 4096K 설정하고, BUFFERCOUNT 1000으로 설정하였다.

RESTORE DATABASE [DB] FROM DISL = N'D:\DB\BACKUP\DB.BAK' WITH REPLACE, STATS = 5, MAXTRANSFERSIZE = 4194302, BUFFERCOUNT = 1000

 

추가 매개변수를 사용하여 데이터베이스를 복원할 경우, 복원의 속도는 빨라지겠지만 동일 서버에서 운영되는 다른 서비스에 영향을 미칠 수도 있다. 해당 옵션을 사용하기 전에 시스템의 리소스 가용능력, 사용량등을 확인할 있도록 한다.

중요한것은 실제 운영 환경에 반영하기전에 테스트 환경에서 먼저 검증을 있도록 한다.

 

[참고자료]

·       Optimizing Backup and Restore Performance in SQL Server : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190954(v=sql.105)?redirectedfrom=MSDN

 

 

2020-02-28/ Sungwook Kang / http://sungwookkang.com

 

 

 

SQL Server, MS SQL, Database Restore, SQL Server Recovery Step, 데이터베이스 복원, Database Recovery Process, BUFFERCOUNT, MAXTRANSFERSIZE

+ Recent posts