Delayed Durability. Der Mythos über die Grenze des maximalen Datenverlusts

14. Februar 2017

Delayed Durability ist ein Feature, mit dem man das D in ACID ausschalten bzw. verzögern kann (verfügbar ab SQL Server 2014). Mit Delayed Durability werden die Commits asynchron ausgeführt, der Client bekommt also eine Erfolgsmeldung bevor die Protokolldatensätze für die Transaktion auf den Datenträger geschrieben wurden.

MSDN sagt sehr deutlich, dass das Feature nur dann verwendet werden kann, wenn der Datenverlust aus Anwendungssicht akzeptabel ist. Manchmal trifft man im Netz Aussagen (SQL SentryOne, SQL Performance), dass der Datenverlust bei aktivierter Delayed Durability auf etwa 7Mb begrenzt ist. Diese Zahl kommt durch Multiplizierung der Größe eines Log Buffers (60Kb) mit der maximalen Anzahl der gestarteten, aber noch nicht abgeschlossenen IO Operationen (ab SQL Server 2012 max. 112 ausstehende IO Operationen. S. dazu die SQL PASS Session SQL Server Transaction Log Internals).

Diese Überlegung hat aber einen logischen Fehler. Falls eine Transaktion es nicht schafft, ihren Commit-Status (LOP_COMMIT_XACT Satz) zu persistieren, wird sie in der Rollbackphase während der Wiederherstellung zurückgerollt. Die gesamten Daten der Transaktion (und sie können beliebig groß sein) gehen dann verloren.

Damit es nicht bei der gedanklichen Übung bleibt, habe ich die Situation nachgespielt.

Aufbau der Testumgebung

Als Erstes benötigen wir eine virtuelle Maschine (z.B. Hyper-V) mit 2 virtuellen Festplatten.

image

In der Hyper-V habe ich den SQL Server installiert (2014 in diesem Beispiel, aber auch 2016 sollte sich identisch verhalten). Folgendes T-SQL Skript legt mir eine Datenbank an; die LDF-Datei (Transaction Log) wird auf der zweiten Festplatte angelegt, damit ich später ein langsames IO System und einen Systemcrash simulieren kann. Mit DELAYED_DURABILITY = FORCED verwenden alle Transaktionen das neue Feature. Das automatische Anlegen und Aktualisieren von Statistiken ist ausgeschaltet, um mögliche Nebeneffekte zu minimieren. In der Datenbank wird eine Tabelle mit einer Integer-Spalte angelegt.

CREATE DATABASE DelayedDurabilityTest ON PRIMARY 
( NAME = N'DelayedDurabilityTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DelayedDurabilityTest.mdf' , SIZE = 204800KB , FILEGROWTH = 0)
LOG ON 
( NAME = N'DelayedDurabilityTest_log', FILENAME = N'E:\DelayedDurabilityTest_log.ldf' , SIZE = 30720KB , FILEGROWTH = 0)
GO
ALTER DATABASE [DelayedDurabilityTest] SET COMPATIBILITY_LEVEL = 120
ALTER DATABASE [DelayedDurabilityTest] SET AUTO_CREATE_STATISTICS OFF
ALTER DATABASE [DelayedDurabilityTest] SET AUTO_UPDATE_STATISTICS OFF 
ALTER DATABASE [DelayedDurabilityTest] SET RECOVERY SIMPLE 
ALTER DATABASE [DelayedDurabilityTest] SET DELAYED_DURABILITY = FORCED 
GO
USE [DelayedDurabilityTest]
CREATE TABLE SimpleTable(MyInt INT NOT NULL);

 

Um die Schreibaktivität im Transaction Log zu erzeugen, werden Datensätze in die Tabelle SimpleTable eingefügt. Es ist nicht ganz trivial auszurechnen, wie viel Transaction Log im Durchschnitt ein INSERT produzieren wird. Ich habe stattdessen die Größe der LDF-Datei auf 30 Mb begrenzt und habe die Datensätze eingefügt, bis nach dem 99846. INSERT die Fehlermeldung “Msg 9002, Level 17, State 4, Line 10. The transaction log for database ‘DelayedDurabilityTest’ is full due to ‘ACTIVE_TRANSACTION’” erschienen ist. Mit folgendem Skript führe ich in einer Transaktion 70000 INSERTs durch, was ungefähr 21 Mb Transaction Log entspricht.

SET NOCOUNT ON;
BEGIN TRAN
DECLARE @counter INT = 0;

WHILE @counter<70000
BEGIN
    INSERT dbo.SimpleTable VALUES(@counter);
    SET @counter=@counter+1;
END
COMMIT

 

Bevor ich das obige Skript ausführe, reduziere ich über den Hyper-V Manager die IO Performanz der virtuellen Festplatte, auf der sich der Transaction Log befindet, auf mickrige 3 IOPS (24 Kb pro Sekunde).

image

Das Skript wird über SQLCMD ausgeführt.

echo %time% > before.txt
copy before.txt \\HYPERVHOST\TestForDelayedDurability\before.txt
sqlcmd -S localhost -E -d DelayedDurabilityTest -i InsertSimpleTable.sql
echo %time% > after.txt
copy after.txt \\HYPERVHOST\TestForDelayedDurability\after.txt
rem /*SQLCMD returned, commit successful from client point of view. Shutdown VM*/
copy after.txt \\HYPERVHOST\TestForDelayedDurability\trigger_vm_shutdown.txt

 

Um den Systemcrash zu simulieren, habe ich zuerst versucht nach dem SQLCMD Aufruf in Hyper-V den SQL Server Dienst herunterzufahren (net stop MSSQLSERVER) oder mit Diskpart die Festplatte offline zu setzen (select disk 1 | offline disk).

Im ersten Fall hat der SQL Server Dienst gewartet, bis Protokolldatensätze doch auf die Festplatte geschrieben wurden, bevor er gestoppt wurde. Laut MSDN ist jedoch dieses Verhalten nicht garantiert:

“For delayed durability, there is no difference between an unexpected shutdown and an expected shutdown/restart of SQL Server. Like catastrophic events, you should plan for data loss. In a planned shutdown/restart some transactions that have not been written to disk may first be saved to disk, but you should not plan on it.”

Im zweiten Fall wurde die virtuelle Festplatte auch nicht sofort offline genommen. Vermutlich, weil zuerst die ausstehenden IO Kommandos vom SQL Server abgearbeitet wurden.

Um den Ablauf doch zeitlich möglichst eng zu machen und Reproduzierbarkeit zu erleichtern, kopiere ich nach dem SQLCMD Aufruf eine Datei “trigger_vm_shutdown.txt” auf das Host System. Auf dem Host läuft ein Powershell-Skript, das die Hyper-V herunterfährt und somit einen Systemcrash simuliert.

while (!(Test-Path 'C:\Temp\TestForDelayedDurability\trigger_vm_shutdown.txt')) { Start-Sleep -m 200 }
Remove-Item 'C:\Temp\TestForDelayedDurability\trigger_vm_shutdown.txt'
Stop-VM –Name SQL2014 –TurnOff

 

Nach dem erneuten Starten der virtuellen Maschine sieht man, dass die Transaktion, obwohl aus Clientsicht erfolgreich abgeschlossen, den Neustart doch nicht überlebt hat.

image

Fazit

Datenverlust bei Transaktionen, die im Delayed Durability-Kontext ausgeführt werden, ist nicht durch eine bestimmte Größe begrenzt und kann theoretisch beliebig groß sein. Deswegen kommt die Verwendung von Delayed Durability nur dann in Frage, wenn der Datenverlust der kompletten Daten solcher Transaktionen akzeptabel ist.

Update 2017-03-19: Den Datenverlust kann man auf unterschiedliche Weise definieren. Im Artikel ist unter Datenverlust der Fall gemeint, wenn die Benutzerdaten nach einem Ausfall nicht in dem Stand sind, den man nach einer aus Benutzersicht abgeschlossenen Transaktion erwarten würde. Wenn die Datenbank im Full Recovery Model betrieben wird, kann man die Daten einer aus SQL Server Sicht nicht abgeschlossenen Transaktion aus dem Transaction Log mit nicht zu unterschätzenden Aufwand rekonstruieren. Die letzten 7Mb (unter Umständen weniger) des Transaction Logs gehen endgültig verloren.