[SQL Server] In-Memory OLTP : Checkpoint File Pairs, fusion et cycle de vie

Ce billet aborde le cycle de vie des CFPs (Checkpoint File Pairs) et l’impact des opérations de fusion.

Vous pouvez faire un tour ici, si vous souhaitez en apprendre plus sur l’In-Memory OLTP.

 

Rappel sur les CFPs

Nous avons vu dans un billet précédent que des paires de fichiers checkpoint (ou CFP, pour Checkpoint File Pairs : paires de fichiers de données et delta) sont utilisées pour permettre la récupération de données en cas de redémarrage d’une instance, la mémoire étant par essence volatile. Leur utilisation par le moteur In-Memory OLTP concerne principalement les tables durables optimisées en mémoire (créées avec l’option SCHEMA_AND_DATA).

Chaque fois qu’une transaction est validée, le moteur In-Memory OLTP les archive dans le journal des transactions. Et chaque fois qu’un checkpoint est lancé, les données sont déplacées du journal des transactions vers le groupe de fichiers filestream, au sein du container du checkpoint où sont stockées la paire de fichiers de données et delta. De cette façon, lors de la phase de récupération de la base de données concernée (après redémarrage de l’instance ou restauration, par exemple), les données issues de ces fichiers sont alors récupérées, avec la portion active du journal des transactions, pour reconstruire le contenu de la table optimisée en mémoire à un état suffisamment à jour.

On rappelle qu’un fichier de données (~128 Mo) contient les données insérées et qu’un fichier delta contient des marqueurs pointant sur des données supprimées. Et qu’en cas de modification d’une données, sa précédente version est supprimée (marquée dans le delta) et sa nouvelle ajoutée (dans le fichier de données).

 

De l’importance d’une opération de fusion pour les CFPs

Fonctionnement d’une opération de fusion

Les métadonnées de chaque CFP sont stockées dans une structure de données interne de type array, contenant 8192 entrées (4096 dans SQL Server 2014 CTP2). Les entrées sont classées par ordre transactionnel, permettant une récupération propre et efficace des données d’une base de données avec table(s) optimisée(s) durable(s) en mémoire.

De fortes activités OLTP peuvent conduire à une gestion contre-performante du stockage des CFPs, dont les plus connues sont les suivantes :

  • Augmentation exponentielle du nombre d’entrées à traiter pouvant prolonger la durée des traitements des processus internes (i.e., recherche d’un fichier delta afin de supprimer une ligne de données), et de facto impacter la consommation des ressources systèmes (CPU,…).
  • Augmentation exponentielle de la volumétrie disque. En effet, dans la mesure où les lignes de données des tables durables optimisées en mémoire ne sont pas complètement supprimées du fichier de données (un marqueur étant simplement ajouté dans son fichier delta), un gaspillage des ressources disques peut être observé, ce qui peut impacter les temps de récupération de données.

C’est dans ce contexte qu’une opération interne de fusion joue un rôle très important, puisqu’elle permet de fusionner les « vieux » CFPs fermés de façon à pouvoir compacter la structure de données array.

Une opération de fusion, en environnement In-Memory OLTP, fonctionne en prenant un ou plusieurs CFPs fermés, appelés merge sources, afin de les fusionner, ce qui donnera lieu à un CFP appelé merged target. Toutes les entrées de chaque fichier delta des merge sources sont exploitées afin de filtrer les lignes marquées pour la suppression. De cette façon, lors de la fusion, seules les lignes utiles sont prises en compte dans le merged target, tandis que celles inutiles sont routées – via les merge sources fraîchement « déchargées » de leurs lignes à fusionner – vers le garbage collector qui les supprimera.

L’évaluation et sélection des CFPs par l’opération de fusion se fait via une règle interne de fusion fonctionnant comme suit :

  • Une fusion est opérée si 2 ou plusieurs CFPs consécutifs peuvent être consolidés. C’est-à-dire, si le merged target peut avoir une taille « idéale » pouvant correspondre aux fichiers de données et delta, sachant que :
    • Pour les machines <= 16 Go de RAM, un fichier de données et un fichier delta ont respectivement une taille idéale fixée à 16 Mo et 1 Mo.
    • Pour les machines > 16 Go de RAM, 128 Mo pour un fichier de données, 8 Mo pour un fichier delta.
  • Un simple CFP peut être auto-fusionné (ou compacté) si le fichier de données cible est supérieur à 256 Mo et que plus de la moitié des lignes sont marquées comme supprimées.

Exemple simple : supposons que l’on possède 4 CFPs adjacents (entre parenthèse, le poucentage de remplissage) : CFP0 (10%), CFP1 (70%), CFP2 (30%), CFP3 (40%). Les CFPs choisis pour la fusion seront CFP0 et CFP1, le fait de rajouter CFP2 ne permettant pas d’avoir un merged target avec une taille idéale correspondante (>100% de la taille idéale).

 

Les différents états transitifs des CFPs

Avant, pendant et après une opération de fusion, les CFPs adoptent différents états transitifs qui constituent leur cycle de vie. Le tableau ci-dessous en donne leur description :

Etat Description
PRECREATED Ensemble de CFPs est préalloué afin de réduire les temps d’allocation de nouveaux fichiers au cours de l’exécution d’une transaction. Le nombre de CFPs pré-alloués est principalement déterminé en fonction du nombre d’ordonnanceurs ou de processeurs logiques.
UNDER CONSTRUCTION Ensemble de CFPs qui stocke les nouvelles lignes de données, ou susceptibles d’être supprimées, depuis le dernier checkpoint.
ACTIVE Ensemble de CFPs contenant les lignes insérées ou supprimées depuis le précédent checkpoint. Concrètement, il s’agit des lignes requises avant l’application de la partie active du journal des transactions au (re)démarrage ou à la récupération de la base de données.
MERGE TARGET Ensemble des CFPs résultats d’une fusion inter-CFPs.
MERGED SOURCE Ensemble des CFPs marquées comme fusionnées lors de l’opération de fusion.
REQUIRED FOR BACKUP/HA Ensemble des CFPs fusionnées requises lors de la récupération (via une sauvegarde, par exemple) d’une base de données avec table(s) optimisée(s) en mémoire.
IN TRANSITION TO TOMBSTONE Ensemble des CFPs jugées inutiles par le moteur In-Memory OLTP et destinées à être traitées par le garbage collector FILESTREAM. En attente de transition vers l’état TOMBSTONE.
TOMBSTONE Ensemble des CFPs traitées par le garbage collector FILESTREAM. Au cours de cette opération, les fichiers inutiles sont supprimés.
Plus de détails ici : http://technet.microsoft.com/en-us/library/gg492195.aspx.

 

Analyse pratique du cycle de vie des CFPs

Contexte

Nous allons effectuer quelques démonstrations techniques sur une machine dotée de 8 Go de RAM et 4 CPUs, ainsi que d’une instance SQL Server 2014 RTM, afin de présenter les états transitifs des CFPs avant, pendant et après une fusion.

  • Création d’une base de données supportant l’In-Memory OLTP (par défaut, en mode de récupération FULL):
USE master
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'InMemoryDB')
DROP DATABASE InMemoryDB
GO
CREATE DATABASE InMemoryDB
 ON PRIMARY
	(NAME = InMemoryDB_Data,
	 FILENAME=N'D:\SQL Server\MSSQL2014\SQL\SQL Data\InMemory_Data.mdf',
	 SIZE = 250MB,
	 FILEGROWTH = 100MB),
	 FILEGROUP InMemoryDB_InMemoryDB CONTAINS MEMORY_OPTIMIZED_DATA
	(NAME = InMemoryDB_InMemoryDB,
	 FILENAME=N'D:\SQL Server\MSSQL2014\SQL\SQL Data\InMemoryDB_InMemoryDB.mdf')
 LOG ON
	(NAME=InMemoryDB_Log,
	 FILENAME=N'D:\SQL Server\MSSQL2014\SQL\SQL Logs\InMemory_Log.ldf',
	 SIZE = 100MB,
	 FILEGROWTH = 50MB)
GO

 

  • Création d’une table durable optimisée en mémoire :
USE InMemoryDB
GO
IF OBJECT_ID('InMemoryTableData','U') IS NOT NULL
DROP TABLE InMemoryTableData
GO
CREATE TABLE InMemoryTableData
	(InMemoryTableData_ID INT NOT NULL,
	 InMemoryTableData_Name VARCHAR(8000) NOT NULL,
	CONSTRAINT PK_InMemoryTableData PRIMARY KEY NONCLUSTERED HASH (InMemoryTableData_ID)
	WITH (BUCKET_COUNT = 10000))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
  • Désactivation de la fusion automatique, afin de mieux contrôler les fusions et analyser le cycle de vie des CFPs :
DBCC TRACEON(9851,-1)
GO
  • Lancement d’une première sauvegarde complète de la base de données, afin de pouvoir initier une sauvegarde transactionnelle par la suite :
BACKUP DATABASE InMemoryDB
TO DISK=N'D:\SQL Server\MSSQL2014\SQL\SQL Backups\BAK\ÌnMemoryDB\InMemoryDB_empty.bak'
GO

 

  • Alimentation de la table de 10000 lignes de données :
USE InMemoryDB
GO
DECLARE @i INT=0
WHILE (@i < 10000)
	BEGIN
		INSERT INTO InMemoryTableData
        	VALUES (@i,REPLICATE ('Sayonara', 8000))
		SET @i += 1;
	END
GO

 

 

Entrée dans le vif du sujet

La base de données et sa table durable optimisée en mémoire, il ne nous reste plus qu’à effectuer différents tests autour des CFPs. Pour ce faire, commençons par utiliser la DMV sys.dm_db_xtp_checkpoint_files :

 

USE InMemoryDB
GO
SELECT file_type_desc
	  ,state_desc
	  ,internal_storage_slot
	  ,file_size_in_bytes
	  ,file_size_used_in_bytes
	  ,inserted_row_count
	  ,deleted_row_count
FROM sys.dm_db_xtp_checkpoint_files
ORDER BY container_id, file_type_desc, upper_bound_tsn
GO

Le résultat sera du lancement de la requête ci-dessus sera le suivant (pour des raisons de lisibilité, le rendu des résultats a été tronqué) :

Comme on peut le souligner (à titre informatif, en tout 28 fichiers ont été créés) :

  • 8 fichiers de données sont dans un état PRECREATED, et idem pour 8 fichiers delta (partiellement visibles ci-dessus). Il s’agit de paires de fichiers (CFPs) préalloués en vue de futures nouvelles insertions. Le nombre est fixé en fonction du nombre de processeurs logiques (ici, 4), avec 8 au minimum. L’ensemble de ces fichiers de données fait bien 128 Mo et ceux delta 8 Mo.
  • 6 fichiers de données sont dans un état UNDER CONSTRUCTION. Il s’agit des fichiers venant de fraîchement stocker les 10 000 lignes insérées. Leurs « équivalents » delta sont également UNDER CONSTRUCTION, mais logiquement vides (car pas encore d’opérations de modifications ou de suppressions).
  • La taille de chaque fichier de données et delta est respectivement de 16 et 1 Mo, la machine de test étant dotée d’une capacité de RAM inférieure à 16 Go.

Maintenant, lançons un checkpoint manuel :

CHECKPOINT
GO

 

Si l’on relance notre requête DMV précédente, nous obtiendrons le résultat suivant :

On note que les paires de fichiers initialement à un état UNDER CONSTRUCTION viennent de repasser à un état ACTIVE. Cela signifie que chaque paire fait désormais partie d’un checkpoint durable (celui qui vient d’être lancé), ce qui implique sa fermeture. Sur les 16 Mo pré-alloués à chaque fichier de données concerné par le stockage de lignes, seuls 5 ont 15,15 Mo d’utilisés (soit un taux de remplissage avoisinant les 95%), et le 6ème 4,6 Mo (près de 29%).

Les CFPs constitués de la paire de fichiers (de données et delta) fermés seront potentiellement sélectionnés pour une fusion future.

Procédons maintenant à la suppression de 5000 lignes d’enregistrements :

USE InMemoryDB
GO
DELETE TOP (5000)
FROM InMemoryTableData
GO

 

Après avoir lancé un checkpoint manuel, relançons notre requête DMV suivante (en ne tenant pas compte des fichiers préalloués) :

 

USE InMemoryDB
GO
SELECT file_type_desc
	  ,state_desc
	  ,internal_storage_slot
	  ,file_size_in_bytes
	  ,file_size_used_in_bytes
	  ,inserted_row_count
	  ,deleted_row_count
	  ,lower_bound_tsn
	  ,upper_bound_tsn
FROM sys.dm_db_xtp_checkpoint_files
ORDER BY container_id, file_type_desc, upper_bound_tsn
GO

Ce qui donnera :

On peut souligner que des fichiers delta ont été sollicités pour traiter la suppression de 5000 lignes d’enregistrements. La colonne internal_storage_slot permet de savoir à quel fichier de données correspond chaque fichier delta, et vice-versa.

Les colonnes lower_bound_lsn et upper_bound_lsn permettent de determiner respectivement la limite inférieure et la limite supérieure de transactions de chaque fichier.

Lançons maintenant une fusion manuelle des CFPs via la procédure stockée système sys.sp_xtp_merge_checkpoint_files en tenant compte :

 

USE InMemoryDB
GO
EXEC sys.sp_xtp_merge_checkpoint_files
'InMemoryDB', 1888, 10007
GO

Si on relance notre précédente requête DMV, le résultat sera le suivant :

On remarque la présence d’une nouvelle CFP dont la paire de fichiers de données et delta est marquée MERGE TARGET.

Le lancement d’un checkpoint permettra de déterminer les CFPs qui ont été utilisées pour la fusion (état MERGED SOURCE) :

Comme on peut le souligner, les fichiers de la nouvelle CFP fusionnée ont transité vers un état ACTIVE, tandis que ceux qui ont contribué à la fusion sont marqués MERGED SOURCE. De là, on peut déduire que les CFPs de slot 0, 1, 2, 3 et 4 ont fusionné pour former celui de slot 7. Le fait que l’espace utilisé (~30 Mo) soit supérieur à ce qui est attendu (~16 Mo) s’explique par le fait que la désactivation de la fusion automatique a également mis en stand-by la règle de fusion. En temps normal, toutes les CFPs sélectionnées pour la fusion (dans notre exemple) n’auraient pas été choisies si elles provoquaient un dépassement de la taille idéale attendue pour le CFP résultant de la fusion.

Les CFPs marqués MERGED SOURCE transiteront vers un état REQUIRED FOR BACKUP/HA en cas de checkpoint automatique ou sauvegarde transactionnelle, par exemple comme ci-dessous :

CHECKPOINT
GO
BACKUP LOG InMemoryDB
TO DISK=N'D:\SQL Server\MSSQL2014\SQL\SQL Backups\TRN\InMemoryDB\InMemoryDB_log.trn'
GO

 

Ce qui donnera, en cas de relance de notre requête DMV :

L’état REQUIRED FOR BACKUP/HA permet de garantir que les CFPs marquées comme telles ne seront pas transitée vers le garbage collector pour suppression, car destinées à être prises en compte dans de prochaines sauvegardes afin de permettre d’assurer l’intégrité de la base de données en cas de restauration future. Vous pouvez simuler quelques sauvegardes supplémentaires précédées de checkpoints manuels pour rendre les CFPs concernées éligibles à une traitement par le garbage collector, ce qui donnera :

Les CFPs marquées IN TRANSITION TO TOMBSTONE ne sont plus utiles pour l’intégrité de la base de données, et peuvent donc être supprimées. Pour ce faire, il faut qu’elles passent à un état TOMBSTONE afin que le garbage collector puisse les traiter. Il suffit de lancer un ou plusieurs checkpoints suivis d’une sauvegarde transactionnelle, comme vu plus haut. Une relance de notre requête DMV donnera le résultat suivant :

Rappelons que les différents tests effectués ont été réalisés dans un contexte où la fusion automatique a été désactivée. De plus, en temps normal, un checkpoint est automatiquement lancé à chaque sauvegarde (complète, transactionnelle,…) ou à chaque fois que 512 Mo de transactions sont générées.

Afin d’une part, de disposer d’un meilleur plan de restauration d’une base de données In-Memory OLTP et d’autre part, de mieux gérer la volumétrie des transactions et CFPs, il est recommandé, en production, d’avoir un plan de maintenance doté d’un job de sauvegardes transactionnelles configuré pour être lancé de façon régulière.

 

Pour aller plus loin…

Ce billet a permis de comprendre d’une part, le cycle de vie des CFPs et d’autre part, le fonctionnement d’une opération de fusion de CFPs.

Gardez œil ici pour d’autres billets autour de l’In-Memory OLTP.

 

 

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