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 > SQL Server Tip' 카테고리의 다른 글
| SQL Server 에서 AWS S3에 직접 백업하기 (0) | 2022.10.28 |
|---|---|
| VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0) | 2020.03.04 |
| SQL Server 복원 성능 최적화 (0) | 2020.02.29 |
| SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0) | 2020.02.28 |
| SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0) | 2019.09.24 |
