[SQL Server]内存缓存数据写入磁盘(三)延迟持久性Delayed Durability(和魔鬼交易)

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)


参考:

Delayed Durability in SQL Server 2014

Control Transaction Durability