Delayed Durability是SQL Server 2014的新功能，在某些Transaction log負載較大的情境中，如果願意延遲一點ACID靈魂中的持久性(Durability)，同時也有接受可能的Data Loss風險準備，也許可以用延遲持久性和魔鬼交換Transaction log寫入效能。
上一篇進行間接檢查點(Indirect CheckPoint)之後，我們從效能角度觀察了等候時間統計的DMV(sys.dm_os_wait_stats)，由於第二篇的測試情境是大量資料寫入，發現了大量WRITELOG的WaitType!
前面我們曾討論到，SQL Server為了確保完整性(Atomicity)及持久性(Durability)，一個交易的Commit完成，是將寫入Buffer Pool中的”Log” Flush到Disk上才算完成，也就是write-ahead log (WAL)。
之前曾有個客戶的I/O瓶頸是在Transaction Log寫入磁碟，在SQL Server 2014多了一種延遲持久性的作法，概念有點像我們AP使用非同步方式寫LOG，交易確認將資料及紀錄寫到Buffer後，交易不再等到Transaction log寫入磁碟才算整個commit。
使用警語:
Delayed Durability作法是用ACID的100%持久性來和Log I/O效能作交換，就像一種和魔鬼的交易，德國民間傳說中，浮士德(Faustus)用了靈魂換取了魔鬼的合約，重新擁有了青春和享樂。
測試Delayed Durability會分別以完整持久性(Full) vs 延遲持久性(Delayed)在幾種特定交易活動來比較效能的差異。
- 大量單筆交易寫入
- 整批交易寫入
- 整批交易更新
另外我們也嘗試人工、彈性及資料庫備份來觸發及觀察log Flush。
建立資料庫並建立擴充事件觀察Transaction log flush活動
1.建立測試資料庫、復原模式為完整並進行完整備份
CREATE DATABASE [FlushDiskDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'FlushDiskDb', FILENAME = N'C:\temp\db\FlushDiskDb.mdf' , SIZE = 8092KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FlushDiskDb_log', FILENAME = N'C:\temp\db\FlushDiskDb_log.ldf' , SIZE = 8092KB , FILEGROWTH = 10%)
GO
USE [master]
GO
ALTER DATABASE [FlushDiskDb] SET RECOVERY FULL WITH NO_WAIT
GO
BACKUP DATABASE [FlushDiskDb] TO DISK = N'C:\temp\db\FlushDiskDb.bak' WITH NOFORMAT, NOINIT,
NAME = N'FlushDiskDb-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
資料庫準備好了!
2.建立擴充事件觀察log Flush活動
CREATE EVENT SESSION [logFlushEvent] ON SERVER
ADD EVENT sqlserver.databases_log_flush(
ACTION(package0.event_sequence,sqlserver.is_system))
ADD TARGET package0.event_file
(
SET FILENAME = N'C:\temp\db\logFlushEvent.xet'
)
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
ALTER EVENT SESSION [logFlushEvent] ON SERVER STATE=START
擴充事件也準備好了!
SSMS管理工具 > 管理 > 擴充事件 > 工作階段
寫入前資料庫log狀態
use FlushDiskDb
DBCC LOGINFO
只有4個VLF
DBCC SQLPERF(logspace)
Log Size: 8MB
執行大量的單筆交易寫入(對照組)
USE [FlushDiskDb]
--建立資料表t1
IF (object_id('t1')) is not null
DROP TABLE t1;
create table t1
(
c1 int identity,
c2 varchar(30)
)
--紀錄開始時間
DECLARE @START DATETIME = GETDATE();
--執行100,000筆寫入
Declare @COUNT INT = 0;
WHILE (@COUNT < 100000)
BEGIN
SET @COUNT = @COUNT + 1
INSERT INTO t1
VALUES ('T' + CONVERT(VARCHAR, @COUNT))
END
--(4)列印執行時間
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行時間: 25秒
查詢log flush次數:
SELECT count(*)
FROM sys.fn_xe_file_target_read_file('C:\temp\db\logFlushEvent*.xet', null , null, null);
增加了10萬次log flush，也就是有10萬次磁碟活動。
寫入後資料庫log狀態(對照組)
use FlushDiskDb
DBCC LOGINFO
45個VLF(好像有點太多，可以調整檔案初始及成長allocate size，避免太過破碎)
資料庫Log Size:
DBCC SQLPERF(logspace)
10萬筆交易，Log將近增加了400MB
啟用延遲持久性(DELAYED_DURABILITY)實驗組
--資料庫內所有的交易都啟用延遲寫入
ALTER DATABASE FlushDiskDb SET DELAYED_DURABILITY = FORCED
執行寫入
--紀錄開始時間
DECLARE @START DATETIME = GETDATE();
--執行100,000筆寫入
Declare @COUNT INT = 0;
WHILE (@COUNT < 100000)
BEGIN
SET @COUNT = @COUNT + 1
INSERT INTO t1
VALUES ('T' + CONVERT(VARCHAR, @COUNT))
END
--(4)列印執行時間
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行時間: 1.3秒
查詢log flush次數:
SELECT count(*)
FROM sys.fn_xe_file_target_read_file('C:\temp\db\logFlushEvent*.xet', null , null, null);
10萬筆交易寫入，Log Flush事件只增加了1.3 萬次
寫入速度從25秒到1.3秒，飛快的完成10萬筆資料寫入，接近20倍的速度獲利，很像 BulkCopy的無敵速度，F1中的延遲煞車果然強大。
雖然延遲了Log flush到Disk的時間，但實際觀察log fush事件，大約在80秒後，全部的log也都寫入磁碟了。
*如果用Process Monitor觀察，也可以發現磁碟寫入的活動不再這麼頻繁。
手動執行Flush log
sys.sp_flush_log
觀察Log Size
DBCC SQLPERF(logspace)
Log只增加86(490-404)MB
彈性啟用延遲持久性
在線上交易資料庫的環境下要啟用延遲持久性需要過人的勇氣，但我們也還可以有另一個平衡的選擇，用彈性的方式在交易層級啟用。
只要在交易commit時加上語法: COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);
--允許資料庫內交易使用延遲寫入
ALTER DATABASE FlushDiskDb SET DELAYED_DURABILITY = ALLOWED
--紀錄開始時間
DECLARE @START DATETIME = GETDATE();
--執行100000筆寫入
Declare @COUNT INT = 0;
WHILE (@COUNT < 100000)
BEGIN
SET @COUNT = @COUNT + 1
BEGIN TRANSACTION
INSERT INTO t1
VALUES ('T' + CONVERT(VARCHAR, @COUNT))
COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);
END
--列印執行時間
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行時間: 1.7秒
依然在秒間處理完畢!
批次寫入(insert into select )的比較
此時來源資料表已經有30萬筆資料了
先建立兩個空的資料表，待會讓實驗組及對照組寫入!
IF (object_id('t2')) is not null
DROP TABLE t2;
create table t2
(
c1 int,
c2 varchar(30)
)
IF (object_id('t3')) is not null
DROP TABLE t3;
create table t3
(
c1 int,
c2 varchar(30)
)
實驗組:
--強制延遲寫入
ALTER DATABASE FlushDiskDb SET DELAYED_DURABILITY = FORCED
DECLARE @START DATETIME = GETDATE();
INSERT INTO t2 select * FROM T1
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行結果:
對照組:
--關閉延遲寫入
ALTER DATABASE FlushDiskDb SET DELAYED_DURABILITY = DISABLED
DECLARE @START DATETIME = GETDATE();
INSERT INTO t3 select * FROM T1
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行結果:
都是0.4秒的秒殺，整批匯入資料情境下，看起來差異不大。
批次更新比較(Batch Update)
實驗組
--強制延遲寫入
ALTER DATABASE FlushDiskDb SET DELAYED_DURABILITY = FORCED
DECLARE @START DATETIME = GETDATE();
UPDATE t2 set c2 = 'FORCED'
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行結果:
對照組
--關閉延遲寫入
ALTER DATABASE FlushDiskDb SET DELAYED_DURABILITY = DISABLED
DECLARE @START DATETIME = GETDATE();
UPDATE t3 set c2 = 'DISABLED'
PRINT 'execution time(millisecond):' + CONVERT(VARCHAR, DATEDIFF(ms, @START, getdate()))
執行結果:
5倍獲利
資料庫備份是否會觸發log flush活動?
馬上備份!
看起來還是有觸發log flush
小結:
- 對於大量單筆交易有接近20倍的效能提升(1.3 vs 25秒)
- 對於整批更新的交易有5倍的效能提升(0.9秒 vs 5.4秒)。
- 如果資料庫的復原模式只能選FULL，除了DELAYED_DURABILITY，也可以試試調校T-SQL語法或是AP架構讓LOG的寫入的數目壓低。
很喜歡百敬老師說的效能調校兩面刃，完整持久性與延遲持久性各有優缺點，使用Delayed Durability前，我們需要注意機器異常時所造成的Data loss，在可接受的特定交易中彈性使用是理想的選擇。
Msdn警語:
