[SQL Server]内存缓存数据写入磁盘(三)延迟持久性Delayed Durability(和魔鬼交易)
发表于 2019-09-13 | 分类于 后端 | 没有评论
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:tempdbFlushDiskDb.mdf' , SIZE = 8092KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FlushDiskDb_log', FILENAME = N'C:tempdbFlushDiskDb_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:tempdbFlushDiskDb.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:tempdblogFlushEvent.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:tempdblogFlushEvent*.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:tempdblogFlushEvent*.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警语:
如果不能容忍Data Loss，不建议使用Delayed Durability(If you cannot tolerate any data loss, you should not use delayed durability on your tables)