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

[AWS RDS] Modify RDS instance type

-          AWS RDS 인스턴스 타입 변경 (업그레이드 또는 다운그레이드)

 

l  Version : Amazon RDS

 

AWS RDS 환경에서는 운영중인 RDS 인스턴스의 용량 증설 감소(Scale-Up / Scale-Down) 작업을 Management Console 통해서 매우 간단하게 변경할 있다. 아래 순서에 따라 인스턴스를 변경할 있다.

1.       변경할 인스턴스를 선택하고 Modify버튼을 클릭한다.

 

2.       DB Instance size 항목에서 변경할 인스턴스 타입을 선택 한다. 글에서는 db.r5.large 인스턴스를 db.t3.medium으로 다운그레이드 한다.

 

3. 아래 위치한 Continue 버튼을 클릭한다.

4. 변경하려는 인스턴스 타입을 다시 한번 확인하고, Scheduling of modifications 항목에서 정해진 시간에 수정사항을 적용할 것인지, 아니면 즉시 적용할 것인지 선택 한다. 글에서는 즉시 변경 (Apply immediately) 선택한다.

 

 

5. 인스턴스가 수정되고 있는 상황을 확인할 있다.

 

즉시 변경을 선택하였더라도 인스턴스의 종류, 백업할 데이터 양에 따라 수초에서 수분이 걸릴 있다. 따라서 일정 시간 다운타임이 발생한다. 하지만 이러한 다운타임은 예방하기 위해서는 데이터베이스를 이중화 구성하여 롤링 업그레이드 방식을 통해서 하나씩 업그레이드 하면서 failover 역할을 변경하면 다운타임을 예방할 있다.

높은 성능의 인스턴스에서 낮은 성능의 인스턴스로 변경할 간혹 아래와 같은 오류가 발생할 있다.

 

이때에는 Performance Insights 항목에서 Enable Performance Insights 항목을 선택해제하고, 적용한 인스턴스 타입을 변경할 있도록 한다. 이번 실습에서 db.r5.large에서 db.t3.medium으로 먼저 선택하면 해당 항목이 숨겨져서 보이지 않게 되는데, 실제 변경시에는 오류를 반환한다.

 

 

[참고자료]

l  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Enabling.html

 

2022-03-23 / Sungwook Kang / http://sungwookkang.com

 

 

AWS RDS, SQL Server, RDS 인스턴스 타입 변경, Modify RDS Instance type

[AWS RDS] Modify RDS SQL Server Standard Edition to Enterprise Edition

-          AWS RDS SQL Server 스탠다드 에디션에서 엔터프라이즈 에디션으로 변경

 

l  Version : Amazon RDS for SQL Server

 

Microsoft SQL Server 다양한 버전으로 제공되며 버전은 고유한 기능, 성능 가격 옵션을 제공한다. 설치하는 버전도 특정 요구사항에 따라 다르다. 일부 고객은 높은 메모리와 고가용성 기능을 활용하기 위해 SQL Server Amazon RDS Standard Edition에서 Amazon RDS Enterprise Edition으로 변경을 원할 있다. 이번 글은 RDS SQL Server 스탠다드 에디션에서 RDS SQL Server 엔터프라이즈 에디션으로 업그레이드 하는 방법에 대해서 살펴 본다.

 

업그레이드를 위해서 작업자는 아래의 권한을 가지고 있어야 한다.

l  Amazon RDS for SQL Server

l  AWS Management Console 접근 권한

l  SQL Server Management Studio

 

업그레이드 프로세스는 아래와 같은 단계가 포함된다.

l  기존 RDS SQL Server Standard Edition 인스턴스의 스냅샷 생성

l  스냅샷을 RDS SQL Server Enterprise Edition으로 복원

l  RDS SQL Server Enterprise 인스턴스 확인

 

먼저 콘솔을 통해 SQL Server RDS 버전을 수정하는 방법을 안내한다. 기존 RDS for SQL Server 인스턴스의 스냅샷을 만든 다음 다른 버전의 SQL Server 복원한다. SQL Server Management Studio에서 RDS 버전을 확인할 있다.

1.        Amazon RDS 콘솔에서 데이터베이스를 선택

2.        데이터베이스를 선택하고 작업 메뉴에서 스냅샷 생성을 선택

3.        스냅샷 이름을 입력, 스냅샷을 생성

4.        스냅샷 페이지에서 스냅샷이 성공적으로 생성되었는지 확인하고 상태가 사용 가능한지 확인

5.        스냅샷을 선택하고 작업 메뉴에서 스냅샷 복원을 선택

6.        DB 사양에서 SQL Server 버전(SQL Server Enterprise Edition) 선택

7.        DB 인스턴스 식별자에 인스턴스 이름을 입력

8.        인스턴스 클래스를 선택

9.        DB 인스턴스 복원 선택

10.    데이터베이스 복원 진행

11.    데이터베이스가 복원된 SQL Server 버전 확인

 

AWS CLI 통해서도 SQL Server RDS 인스턴스를 업그레이드 있다.  아래 스크립트를 실행하여 DB 스냅샷을 생성한다.

aws rds create-db-snapshot ^
db-instance-identifier mydbinstance ^
db-snapshot-identifier mydbsnapshot

 

아래 스크립트를 실행하여 스냅샷에서 데이터베이스를 복원한다.

aws rds restore-db-instance-from-db-snapshot ^
db-instance-identifier mynewdbinstance ^
db-snapshot-identifier mydbsnapshot^
engine sqlserver-ee

 

업그레이드가 완료되면 향후 비용이 발생하지 않도록 기존 RDS Standard Edition 삭제 하도록 한다.

 

[참고자료]

l  https://dataintegration.info/modify-an-amazon-rds-for-sql-server-instance-from-standard-edition-to-enterprise-edition

 

 

2022-03-21 / Sungwook Kang / http://sungwookkang.com

 

 

AWS RDS, SQL Server, AWS SQL Server, Modify SQL Server Edition, 에디션 변경

'AWS' 카테고리의 다른 글

[AWS RDS] RDS Proxy  (0) 2022.03.23
[AWS RDS] Modify RDS instance type  (0) 2022.03.23
[AWS Aurora] Aurora I/O Planning  (0) 2022.03.20
[AWS Aurora] Aurora Storage Engine  (0) 2022.03.19
[AWS RDS MySQL] RDS MySQL와 Aurora MySQL 차이점  (0) 2022.03.18

+ Recent posts