SQL Server – Durabilité retardée

Plus tôt dans la journée j’au publiée une petite vidéo au sujet de cette fonction méconnue de SQL Server : la durabilité retardée, ou Delayed Durability.

Lors des audits de performance que je mène chez mes clients, l’attente de type WRITELOG est une des attentes les plus fréquemment rencontrée. Cette attente est liée à la difficulté que rencontre SQL Server pour écrire les transactions dans le fichier LDF, le fameux journal des transactions.

Lorsque je suis d’humeur joueuse, je propose alors à mon client de tirer au sort els transactions qui seront autorisées à écrire dans le journal de transaction, et donc à satisfaire au « D » de « ACID » (Atomicité, Consistance, Isolation et Durabilité). Car en effet, le sous-système disque, principalement le volume qui héberge le journal de transactions doit avoir la capacité de supporter les IOs demandés par SQL Server, sous peine de provoquer de sérieux ralentissements. L’écriture dans le journal étant synchrone, tant que la transaction n’a pas été persistée dans le fichier LDF, souvenez vous que l’on fonctionne sous régime de WAL (Write Ahead Logging), il n’est pas possible de rendre l amain à l’application.

Donc lorsque le volume qui supporte le journal de transaction est la source des problèmes de performance, il n’existe que peu de solutions, mis à part l’augmentation de performance hardware … Sauf à soulager les IOs, d’où la blague qui consiste à tirer au sort les transactions autorisées à perdurer. Mais l’idée n’est pas farfelue pour autant car il suffit parfois de jeter un œil aux statistiques d’usage des index pour se rendre compte à quel point certains sont inutiles. Or, à chaque INSERT, UPDATE ou DELETE, l’index va lui aussi être modifié et générer une entrée dans le journal de transaction …

L’exemple suivant est assez flagrant, une table comportant plusieurs index noncluster supporte une charge conséquente en écriture, mais jamais les index sont utilisés pour la lecture …

Diminue les IOs, et donc la pression sur le journal de transaction peut être censé. Par exemple, Hekaton propose de type de fonctionnements. En effet le stables InMemory dans SQL Server permettent une durabilité de type SCHEMA_ONLY, autrement dit, on n’écrit JAMAIS dans le journal de transactions, seule la structure de la table est persistante. Il est possible d’utiliser ces tables « Temporaires » dans bien des cas.

Mais qu’en est-il pour les tables de type Row Disk Based, par opposition à InMemory. On ne va pas enregistrer une transaction sur 2, on est bien d’accord. Et contrairement à des problématiques de PAGEIOLATCH qui peuvent être amoindries par un ajout de mémoire, l’augmentation de capacité du BUFFER POOL ne changera rien à la donne si vous souffres d’attente de type WRITELOG.

Il faut donc se tourner vers une fonctionnalité méconnue apparue avec SQL Server 2014 qui permet d’opter pour une durabilité retardée. En fait, rendre l’IO d’écriture dans le journal de transaction asynchrone, et donc permettre à l’application de poursuivre le traitement sans attendre l’écriture physique sur disque.

Ci-dessous el bout de code m’ayant servi lors de démos :

USE MASTER
GO
DROP DATABASE IF EXISTS [DemoDelayedDurability];
CREATE DATABASE [DemoDelayedDurability];
GO

USE [master]
GO
ALTER DATABASE [DemoDelayedDurability] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [DemoDelayedDurability] 
MODIFY FILE ( NAME = N'DemoDelayedDurability', SIZE = 1GB )
GO
ALTER DATABASE [DemoDelayedDurability] 
MODIFY FILE ( NAME = N'DemoDelayedDurability_log', SIZE = 512MB )
GO

USE [DemoDelayedDurability]
GO
CREATE TABLE TestTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Val VARCHAR(100)
)
GO

Une fois la base et la table créées, place au test. Simple. 50 000 inserts dans la table. La première exécution en durabilité retardée désactivée, le mode historique dans SQL Server.

USE [DemoDelayedDurability]
GO
SET NOCOUNT ON
DECLARE @counter AS INT = 0
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter < 50000)
BEGIN
    BEGIN TRAN
        INSERT INTO TestTable VALUES( @counter)
        SET @counter = @counter + 1
    COMMIT
END
SELECT DATEDIFF(MILLISECOND, @start, GETDATE() ) [Durable_Insert in msec] 

SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@spid
ORDER BY wait_time_ms DESC;

Résultat : 4 secondes d’exécution, dont près de 3 secondes d’attente de type WRITELOG, 50 000 attentes, comme par hasard …

La durabilité retardée est une option de base de données qui peut prendre les valeurs Forcée ou Autorisée.

En mode Forcé, toutes les transactions se verront faire des IOs asynchrones sur le fichier LDF.
En mode Autorisé, le choix est donné au développeur d’activer ou non la fonctionnalité, transaction par transaction.
Car il ne faut pas perdre de vue que lorsque l’on opte pour ce mode de fonctionnement, on s’expose à une perte potentielle de données en cas de crash dans les millisecondes ou secondes qui suivent la transaction.

On opte donc pour le mode Allowed.

USE [master] 
GO
ALTER DATABASE [DemoDelayedDurability] 
SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO

Et on rejoue le même test, attention à l’ordre COMMIT qui est modifié.

USE [DemoDelayedDurability]
GO
SET NOCOUNT ON
DECLARE @counter AS INT = 0
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter < 50000)
    BEGIN
    BEGIN TRAN
        INSERT INTO TestTable VALUES( @counter)
        SET @counter = @counter + 1
    COMMIT WITH (DELAYED_DURABILITY = ON)
    END
SELECT DATEDIFF(MILLISECOND, @start, GETDATE()) [DelayedDurability_Insert in msec] 

SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@spid
ORDER BY wait_time_ms DESC;

Immédiatement la différence de performance saute aux yeux, plus d’attente de type WRITELOG

Ce test a été menu sur une machine dont le disque dur est de type SSD sur protocole NVMe, donc relativement rapide.

Mais plus le sous système disque est lent, plus le phénomène est présent, et donc grève les performances de l’application.

J’ai reçu un commentaire qui m’a conduit à écrire ce post en complément de la petite vidéo, justement en lien avec des systèmes aux performances disque limitées. Ce qui suit n’est donc pas présent dans la vidéo publiée.

Afin de mettre en exergue le phénomène, j’ai choisi de mener un test similaire (sur 10000 itérations seulement) sur Azure SQL Edge, mon SQL Server qui s’exécute dans un conteneur Docker sur un Raspberry Pi (on cumule donc les « problèmes »). L’installation se passait ici.

La création de la base et de la table sont strictement identiques. On exécute le premier test, durabilité retardé désactivée.

Plus d’une minute ont été nécessaires à l’exécution des 10 000 itérations d’insert. On voit les limites d’une carte SD, même de bonne facture.
Notez au passage l’attente de type PREEMPTIVE_OS_FLUSHFILEBUFFERS, apparue avec SQL Server 2017 pour les environnements Linux pour marquer effectivement le fait que l’on force cette écriture synchrone sur disque.

A présent un test avec durabilité retardé. Le constat est sans appel, moins de 5 secondes.

Il n’y a plus d’attente de type WRITELOG. Problème « résolu ».

Et donc, le commentaire reçu faisait état de problème similaire, des insertions multiples, mais il n’était pas possible de tolérer une perte de données, et donc l’obligation de conserver une durabilité immédiate.

Alors que faire ? Il ne reste pas grand-chose dans la botte du DBA. Peut être une revue de code afin de revoir la notion de transaction. Car le Flush des LOG Blocks sur disque s’effectue aussi en liaison avec le commit, donc une multitude de « petites » transactions va provoquer plus de « petits » IOs, compliqués à gérer pour le système, alors que si l’on déplace la transaction pour englober la boule, dans notre exemple, et donc exécuter l’ensemble des inserts dans UNE SEULE transaction, alors, le temps d’exécution n’est pas ridicule, loin de là.

L’exemple montre l’exécution en durabilité retardée, mais en positionnant e paramètre à OFF, le temps d’exécution est quasi similaire, avec … 1 seule attente de type WRITELOG. Logique mon cher Watson !

Cette « technique » d’optimisation est assez fréquente pour des bases hébergées sur SQL Azure, mais reste d’actualité pour tous les autres systèmes …

Comprendre le fonctionnement de SQL Server est important si vous souhaitez optimiser les performances de votre application. Si vous souhaitez une formation, n’hésitez pas à me contacter.

Happy Delayed Durability !

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM/MCSM SQL Server
Cet article, publié dans SQL Azure, SQL Server, est tagué , . Ajoutez ce permalien à vos favoris.

Votre commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. Déconnexion /  Changer )

Photo Google

Vous commentez à l’aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s