[SQL Server] Un peu de théorie sur les journaux de transactions…

Pour un premier post technique autour de SQL Server, nous allons aborder la problématique des journaux de transactions. Il s’agit d’un des sujets les plus mal maîtrisés sur SQL Server.

Qu’est-ce-qu’un journal de transactions ?

Un journal de transactions est un fichier LDF (Log Database File) permettant d’archiver toutes les transactions s’étant exécutées au sein de la base de données concernée, ainsi que toutes les modifications faites par ces transactions. Le journal de transactions est un élément critique de l’architecture d’une base de données SQL Server.

De ce fait, le supprimer peut conduire à de (très) graves problèmes de performances au sein de la base de données, que ce soit au niveau de son intégrité ou de sa récupération à un état cohérent.
De plus, du fait des fortes activités en écritures du fichier de journal de transactions, celui-ci doit être placé dans un disque séparé de celui du (ou des) fichier(s) de donnée(s) (afin de limiter les conflits en I/O), et privilégiant les performances en écritures (RAID 1+0, RAID 1 ou, de préférence, RAID10).

Le journal de transactions LDF est architecturalement constitué de ce qu’on appelle des VLFs (Virtual Log Files) qui sont gérés de manière séquentielle par le moteur SQL. Un VLF est une unité de stockage logique de transactions au sein du LDF.

Il existe 2 types de VLFs :

  • Le VLF dit « actif », qui est un VLF contenant, au moins, une transaction pas encore validée.
  • Le VLF dit « inactif » qui est un VLF ne contenant que des transactions ayant été validées.
Il peut exister, au sein du LDF, autant de VLF que le moteur SQL peut en créer. Toutefois, un trop grand nombre de VLFs (i.e., plus de 50) peut nuire aux performances des transactions, c’est donc pourquoi une bonne maintenance du LDF est souvent nécessaire (nous aborderons cela dans un autre billet). A noter que l’utilisation de la commande DBCC LOGINFO(LaBD) peut permettre de connaître le nombre de VLFs au sein du fichier LDF de la base de données LaBD, ainsi que leur statut (dont une valeur 0 indique que le VLF est inactif, et une valeur 2 qu’il est actif).

Au sein d’un VLF, chaque transaction contient des enregistrements identifiés de façon unique par ce qu’on appelle un LSN (Log Sequence Number). Les LSN sont ordonnés de sorte que la modification décrite par l’enregistrement référencé par LSN2 se réalise dans la base après celle décrite par LSN1.

Les LSN sont utilisés par le moteur SQL lors de la constitution de la chaîne des sauvegardes transactionnelles, et plus exactement de leur restauration.

Notez bien que le journal de transactions travaille de manière cyclique. Cela signifie que s’il atteint la fin d’un VLF (appelons-le VLF4), et qu’avant celui-ci se trouvent des VLFs inactifs tronqués, il va donc réutiliser ces derniers au lieu d’utiliser le VLF succédant à VLF4 (i.e., VLF5). Toutefois, si, par contre, il n’y a pas de VLFs tronqués avant VLF4, il va alors utiliser VLF5. Et ainsi de suite…

Au sein d’un LDF, par abus de langage, la différence entre la notion de journal physique et celle de journal logique est que le premier renvoie à l’ensemble des VLFs constituant le LDF tandis que le second renvoie à l’ensemble des VLFs non-tronqués, et donc utilisés.

Comment le moteur SQL utilise-t-il le journal de transactions ?

Quand des changements sont effectués au sein d’une base de données, qu’il s’agisse d’une transaction explicite ou d’une transaction auto-validée, ces changements sont d’abord écrits, sur dur, au sein du fichier LDF, puis les pages de données sont changées en mémoire. Une fois que l’enregistrement des changements est journalisé, la transaction concernée est considérée comme complète (pas validée, mais complète). Les pages de données, quant à elles, seront modifiées/écrites sur le disque plus tard par le lazy writer ou par un point de contrôle (checkpoint)[1].

Le VLF contenant la transaction en question est considéré comme actif    jusqu’à ce que les pages de données qui ont été modifiées en mémoire par ladite transaction sont écrites sur le disque (fichier MDF ou NDF). Et une fois que cela arrive, la transaction est validée, et le VLF est considéré comme inactif (en admettant que toutes ses autres transactions sont également validées). Et une fois le VLF rendu inactif, il n’est plus utile pour la récupération de la base de données, cette dernière ayant déjà récupéré tous les changements qui lui sont associés.

Quand une transaction est annulée, le journal de transactions LDF est utilisé pour annuler les modifications faites par ladite transaction.

Quand une instance SQL Server est redémarrée, le moteur SQL utilise le journal de transactions LDF pour voir si, au moment où l’instance a été arrêtée, il y a eu des transactions qui ont été complétées, mais dont les changements associés n’ont pas été écrits sur le disque, ou s’il y a des transactions qui n’ont pas été totalement complétées. S’il trouve des transactions complétées et dont les changements n’ont pas été pris en compte, il va les rejouer (on parle de « REDO »), tandis que s’il trouve des transactions qui n’ont pas été totalement complétées, il va les annuler (on parle d’ « UNDO »).

Ces choix du moteur SQL lui permettent de s’assurer que la base de données soit à un état suffisamment consistant après le redémarrage.

En dernier recours, les sauvegardes transactionnelles peuvent être utilisées pour récupérer la base de données à un état point-in-time.

Pour finir, le moteur SQL utilise également le journal de transactions LDF pour mener à bien les opérations de réplication, de log shipping ou de database mirroring.

Modes de récupération et journal de transactions

Cette section est importante car elle explique le comportement du moteur SQL par rapport au journal de transactions LDF selon le mode de récupération choisi. Outre le fait qu’un point de contrôle peut intervenir suite à un événement déclencheur (i.e., redémarrage de l’instance SQL Server, lancement manuel de CHECKPOINT, ajout/suppression de fichiers de base de données, sauvegardes…), un point de contrôle automatique peut être lancé en fonction de l’état du journal de transactions LDF selon le mode de récupération. C’est également ce qui va être abordé dans cette section.

          Mode de récupération SIMPLE

Au sein de ce mode, les opérations dites « lourdes » (commandes DBCC, BULK INSERT, etc…) sont minimalement journalisées. Et les VLFs sont gardés seulement pour assurer l’intégrité de la base de données, et non sa récupération.

Quand un point de contrôle est lancé par le moteur SQL, tous les VLFs inactifs sont tronqués (libérés) afin d’être soit supprimés, soit réutilisés.

Du côté des points de contrôle automatiques, et par rapport au journal de transactions LDF, le moteur SQL en lance un quand le journal de transactions LDF est saturé à plus de 70% ou, par défaut[2], quand il (le moteur) en ressent la nécessité, ce qui va tronquer le journal de transactions LDF.

L’utilisation de ce mode de récupération est la façon la plus simple de gérer le journal de transactions, puisque le moteur SQL se charge de tronquer régulièrement les VLFs inactifs après chaque validation transactionnelle.

En contrepartie, il n’y a aucune possibilité de réaliser des sauvegardes transactionnelles, et donc d’effectuer une restauration point-in-time.

          Mode de récupération FULL

Au sein de ce mode de récupération, toutes les opérations, sans exception, sont journalisées. De plus, les VLFs sont gardés pour assurer, à la fois, l’intégrité de la base de données et sa récupération.

Les VLF inactifs sont retenus, au sein du LDF, jusqu’à la prochaine sauvegarde ou opération de troncature.

Du côté des points de contrôle automatiques, et par rapport au journal de transactions LDF, le moteur SQL en lance un chaque fois qu’une troncature a lieu, ou qu’une sauvegarde transactionnelle est lancée.

L’utilisation de ce mode de récupération, bien qu’elle permette d’avoir une stratégie de restauration optimale, offre plus de difficultés dans la gestion du journal de transactions, ce dernier pouvant grossir de façon exponentielle et continue.

          Mode de récupération BULK-LOGGED

Ce mode de récupération est équivalent au mode de récupération FULL, à la différence que les opérations « lourdes » sont minimalement journalisées. De plus, les VLFs sont gardés pour assurer, à la fois, l’intégrité de la base de données et sa récupération

Quand des opérations « lourdes » (i.e., insertions de masse,…) sont minimalement journalisées, le détail complet des transactions associées n’est pas écrit au sein du journal de transactions LDF, cependant tous les segments et allocations de pages sont toujours journalisés.

Du côté des points de contrôle automatiques, le comportement du moteur SQL est le même qu’en mode de récupération FULL.

L’utilisation de ce mode de récupération, bien qu’elle permette d’économiser de l’espace au niveau du journal de transactions LDF ne permet pas de restauration point-in-time. De plus, les sauvegardes transactionnelles sont beaucoup plus grosses (parfois plus grosses que le LDF lui-même) étant donné qu’elles contiennent tout le détail des pages de données modifiées par les opérations « lourdes » depuis la dernière sauvegarde transactionnelle.

En résumé…

Nous avons vu dans ce premier article ce qu’était un journal de transactions, et la façon dont SQL Server les gérait. Nous aborderons dans un prochain article, différentes approches de gestion efficace d’un journal de transactions.


[1]Le lazy writer est un processus d’arrière-plan qui permet de garantir qu’un certain nombre de pages libres en mémoire soient prêtes à recevoir des données. Le checkpoint est un processus permettant de garantir un temps optimal de récupération de la base de données, grâce à l’écriture sur disque des pages « sales » (ou dirty pages, qui sont des pages ayant été modifiées en mémoire mais pas encore écrites sur disque).

[2]La valeur de recovery interval peut être paramétrée dans les propriétés de l’instance SQL Server. Par défaut, elle est à 0, ce qui signifie que le point de contrôle sera fera automatiquement de façon dynamique, le moteur SQL choisissant une valeur appropriée selon l’activité sur l’instance. Une valeur manuellement paramétrée à 1 signifie que le point de contrôle aura lieu, dans la mesure du possible, toutes les minutes. Laisser la valeur par défaut à 0 est recommandé.

Publicités

Laisser un 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 )

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 )

Photo Google+

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

Connexion à %s