史丹利好熱

 

上一篇進行間接檢查點(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警語:

 

---

參考:

Delayed Durability in SQL Server 2014

Control Transaction Durability