[SQL Server] In-Memory OLTP : concepts généraux

Ce billet offre une présentation d’In-Memory OLTP (aka Hekaton).

Présentation de l’In-Memory OLTP

Qu’est-ce que l’In-Memory OLTP ?

L’In-Memory OLTP est, d’une certaine façon, un moteur de base de données intégré à SQL Server, et optimisé pour les données stockées en mémoire et soumises à des traitements OLTP. Ainsi, avec l’In-Memory OLTP, une ou plusieurs tables sont stockées en mémoire – on parle alors de tables optimisées en mémoire (ou memory optimized tables) – et toute procédure stockée référençant ces tables sont compilées nativement.

Le fait de travailler en mémoire permet à SQL Server d’effectuer ses traitements plus rapidement que sur disque. Mais plus que cela : les données traitées – dans un contexte In-Memory OLTP – suivent un modèle d’accès basé sur le verrouillage optimiste, éliminant ainsi toute contrainte liée aux blocages et aux latches.

Pour en savoir plus sur les accès concurrentiels, vous pouvez faire un tour ici.

In-Memory OLTP et ACIDité

Pour rappel, le concept ACID fait référence à des propriétés transactionnelles, à savoir : l’Atomicité, la Consistance, l’Intégrité et la Durabilité d’une transaction. Dans le contexte de l’In-Memory OLTP, où les traitements se font essentiellement en mémoire, ce concept est toujours pris en compte… mais d’une manière plus spécifique. Ainsi :

  • Pour l’atomicité d’une transaction : les transactions peuvent être validées ou annulées, y compris en cas de sinistre. Le fait de pouvoir avoir des données persistées en mémoire peut assurer leur récupération en cas de redémarrage de l’instance, ou restauration/récupération de la base de données.
  • Pour la consistance : les transactions peuvent travailler sur des données consistantes.
  • Pour l’isolation : In-Memory OLTP utilise un modèle de concurrence basé sur ce que l’on appelle Multi-Version Concurrency Control (MVCC), permettant ainsi d’une part, de se prémunir contre toute lecture sale dans un contexte où l’absence de verrous est de rigueur, et d’autre part, d’accéder à des données valides (suivant, bien sûr, le niveau d’isolation).
  • Pour la durabilité : L’In-Memory OLTP supporte 2 options de durabilité :
    • SCHEMA_AND_DATA, qui est l’option par défaut et qui permet de maintenir la persistance des données en mémoire, de façon à pouvoir les récupérer après arrêt de l’instance ou restauration/récupération de la base de données concernée. La récupération des données se fait grâce à l’utilisation du journal des transactions et de fichiers de checkpoints (ou CFPs, pour Checkpoint File Pairs, objets constitués, chacun, d’un fichier de données et d’un fichier delta, dans le groupe de fichiers filestream ; voir plus loin) maintenus par le moteur In-Memory OLTP. De plus, contrairement à ce qui se fait au sein du moteur de stockage OLTP classique, l’In-Memory OLTP offre un modèle de journalisation plus souple : seules les modifications de données sont journalisées, et en aucun cas au niveau des indexes. Les insertions, elles, ne sont journalisées qu’une seule fois, c’est-à-dire lors de la création des indexes supportés (hash et range).
    • SCHEMA_ONLY, qui permet de faire persister en mémoire que la structure de données de la table cible. Toutefois, la notion de durabilité transactionnelle n’est plus pertinente ici en cas de redémarrage d’une instance, ou en cas d’une restauration/récupération de la base de données concernée.
Vous pouvez faire un tour ici, si vous souhaitez mieux assimiler les principes ACID.

In-Memory OLTP et DBCC PINTABLE

La commande (désormais obsolète depuis SQL Server 2005) DBCC PINTABLE – apparue sous SQL Server 7.0 – permet d’épingler des pages de données du buffer pool ou des tables en mémoire, dans le but d’accélérer les traitements en réduisant les temps I/O. Toutefois, contrairement à une idée reçue, In-Memory OLTP ne fonctionne pas de la même manière.

En effet :

  • Les données des tables optimisées en mémoire ne sont pas organisées en pages et surtout, elles n’utilisent pas le buffer pool (voir architecture de l’In-Memory OLTP, plus loin), contrairement aux tables épinglées en mémoire via DBCC PINTABLE.
  • Le niveau de scalabilité n’est pas le même entre la fonctionnalité DBCC PINTABLE et In-Memory OLTP. Là où le premier utilise des verrous et des latches, le second s’en prémunit parfaitement.
  • En matière de performances, dans la mesure où les tables épinglées par DBCC PINTABLE utilisent le buffer pool, leur volumétrie peut potentiellement impacter la distribution des pages de mémoire disponibles pour d’autres services ou objets. Ainsi, si une table dépasse la volumétrie du buffer pool, un crash de l’instance peut être provoqué, et seul le lancement d’un ordre DBCC UNPINTABLE sera nécessaire pour libérer de l’espace mémoire occupé par ladite table, SQL Server ne le faisant pas de son propre chef.

Il est toutefois possible, par abus de langage, de voir In-Memory OLTP comme une évolution nette (et différente) de DBCC PINTABLE.

Fonctionnement du moteur In-Memory OLTP

Architecture de l’In-Memory OLTP

Le moteur In-Memory OLTP supporte et gère un ensemble d’objets qui constituent son architecture :

  • Tables optimisées en mémoire (memory optimized tables) : tables mises en mémoire et traitées par l’In-Memory OLTP. Contrairement aux tables classiques, les données des tables optimisées en mémoire n’ont pas besoin d’être lues dans le cache à partir du disque, les métadonnées de ces dernières sont stockées dans des catalogues systèmes.
  • Procédures stockées compilées nativement : procédures stockées référençant les tables optimisées en mémoire, et se compilant nativement (en code C).
  • Transactions cross-container : transactions traitant à la fois des tables sur disque et des tables optimisées en mémoire.
  • Mode de requête interop : un tel mode permet à des requêtes classiques (T-SQL et procédures stockées) de traiter des tables optimisées en mémoire.

Le schéma ci-dessous résume son architecture (en gris, les composants déjà présents avant SQL Server 2014 et en orange, ceux constituant le périmètre In-Memory OLTP) :


Comme le montre l’illustration ci-dessus, on note principalement que :

  • Tout accès client passe par le protocole TDS (Tabular Data Stream), que ce soit pour le moteur de stockage In-Memory OLTP ou celui de stockage SQL.
  • Les tables optimisées en mémoire utilisent leur propre espace de stockage, et non le buffer pool. Pour la persistance des données, une collection de fichiers de checkpoints est créée au sein du groupe de fichiers filestream dédié aux tables optimisées en mémoire, afin de garder une trace des changements sur lesdites données et les utiliser au cours du processus de récupération ou de restauration de la base de données.
  • L’accès aux tables optimisées en mémoire est possible pour les requêtes classiques, au même titre que dans le cas d’une table classique, à condition que le mode de requête interop soit utilisé. En revanche, les procédures stockées compilées nativement ne peuvent accéder qu’aux tables optimisées en mémoire, mais ont pour l’avantage d’offrir de bien meilleures performances d’accès du fait de leur mode de compilation.

Tous ces éléments font que les accès clients à la couche TDS ne seront pas altérés par la mise en mémoire d’une table ou l’utilisation d’une procédure stockée compilée en natif.

Notons que la compilation native des objets en mémoire – tables et procédures stockées – génère, sur disque, des fichiers DLL. Ces fichiers – préfixés de « xtp_p » pour les procédures stockées, « xpt_t » pour les tables – sont également recompilés en cas de redémarrage de l’instance ou récupération de la base de données cible. Niveau intérêt, ces fichiers permettent à SQL Server de rechercher et de manipuler plus rapidement des données en mémoire. La DMV dm_os_loaded_modules permet d’avoir leur listing.

Comment l’In-Memory OLTP gère la durabilité des données

Un checkpoint est lancé périodiquement par SQL Server pour les données optimisées en mémoire au sein des fichiers de données et delta qui vont de pair (on parle de CFP, ou Checkpoint File Pairs), afin d’avancer la partie active du journal des transactions. Comme brièvement indiqué plus haut dans ce billet, un tel processus permet aux tables optimisées en mémoire de restaurer ou de récupérer les données au dernier checkpoint lancé, de façon à pouvoir appliquer la partie active du journal des transactions idoine permettant ainsi de mettre à jour la table optimisée en mémoire afin de valider la récupération. Cela est bien sûr valable dans un contexte de durabilité de la table optimisée en mémoire, c’est-à-dire si cette dernière est en mode SCHEMA_AND_DATA.

Le lancement automatique d’un checkpoint est effectué chaque fois que la taille du journal des transactions grossit de 512 Mo depuis le dernier checkpoint.

Le fichier de données et celui delta utilisés par le checkpoint sont localisés dans un container stocké au sein du groupe de fichiers filestream appelé memory-optimized filegroup (ou groupe de fichiers (pour tables) optimisé(es) en mémoire, sur le schéma plus haut). Structurellement parlant :

  • Un fichier de données est constitué de lignes issues d’une (ou plusieurs) table(s) optimisée(s) en mémoire, lignes ayant été insérées par des transactions DML en mode interop ou des procédures stockées compilées nativement. Les lignes sont enregistrées au sein du fichier de données suivant leur ordre transactionnel dans le journal des transactions, rendant l’accès aux données séquentiel, assurant ainsi une meilleure distribution des activités I/O.
  • Un fichier delta va de pair avec un fichier de données. Ainsi, quand les données du fichier de données sont supprimées ou mises-à-jour, les changements sont tracés dans le fichier delta, sachant que la mise-à-jour est un mix entre la suppression (traçage dans le fichier delta) et l’insertion (dans le fichier de données). Dans la mesure où la ligne de données supprimée est déjà présente dans le fichier de données, seules les informations de référence sont stockées dans le fichier delta : inserting_tx_id, row_id, deleting_ix_id. A l’instar du fichier de données, les données du fichier delta sont accédées séquentiellement.

Comme évoqué plus haut, les données des fichiers sont écrites dans ces fichiers de façon séquentielle. L’accès à ces données se fait principalement selon les situations suivantes :

  • Au cours d’une récupération de la base de données : les tables durables optimisées en mémoire sont alimentées par les fichiers de checkpoint en utilisant le contenu des fichiers de données et delta, chargés parallèlement. SQL Server utilise les fichiers delta comme comparatif lui permettant de ne sélectionner que les lignes non-supprimées. Et sitôt les tables durables optimisées en mémoire remplies, SQL Server applique les enregistrements de la partie active du journal des transactions qui n’ont pas été encore pris en compte les fichiers de checkpoint, afin de rendre la récupération complète.
  • Au cours d’une restauration de la base de données : les fichiers de checkpoint sont créés à partir de la sauvegarde de la base – suivi d’une restauration du ou des sauvegardes transactionnelles – puis les données sont chargées en mémoire en parallèle dans les tables durables optimisées en mémoire.
  • Au cours d’une opération de fusion : SQL Server fusionne une ou plusieurs paires de fichiers de données et delta, et en crée une nouvelle paire. La fusion de paires de fichiers de données et de fichiers delta est effectuée lors qu’elles sont en trop grand nombre avec une grande quantité de lignes supprimées et qu’elles peuvent être consolidées à une taille allant jusqu’à 16 Mo pour le fichier de données et 1 Mo pour le fichier delta dans le cas d’une machine avec 16 Go de RAM ou moins ; 128 Mo pour le fichier de données et 16 Mo, le cas échéant. En outre, une fusion est également possible si une paire dépasse les 256 Mo de taille, et possède plus de 50% de lignes supprimées.
Pour avoir le listing des paires de fichiers checkpoint pour un groupe de fichiers mémoire optimisé donné, il est possible d’utiliser la DMV sys.dm_db_xtp_checkpoint_files. Cette DMV indique également l’état des paires de fichiers checkpoint permettant d’avoir une idée de leur cycle de vie : PRECREATED, UNDER CONSTRUCTION, ACTIVE, MERGE TARGET, MERGED SOURCE, REQUIRED FOR BACKUP/HA, IN TRANSITION TO TOMBSTONE, TOMBSTONE.

Cas d’utilisation des fonctionnalités In-Memory OLTP

L’In-Memory OLTP sied beaucoup aux scénarii suivant :

  • Tables avec un niveau d’accès en lecture très élevé : dans la mesure où toute table optimisée en mémoire utilise des fonctions de hachage pour un accès optimal aux données en mémoire, les lectures sont beaucoup plus rapides que dans le cas d’un accès plus « traditionnels » de tables sur disque.
  • Tables intermédiaires pour les traitements ETL : le moteur de stockage In-Memory OLTP permet de travailler sur des tables optimisées en mémoire avec persistance de leur schéma. De telles tables peuvent être réutilisées en cas de redémarrage ou crash d’une instance, comme tables intermédiaires pour des traitements ETL vers des datawarehouses.
  • Tables fréquemment impactées par des accès concurrentiels très élevés, et générant des blocages intempestifs, voire des deadlocks. Exemple : table de commandes où un processus met à jour le statut d’une commande, alors qu’un autre met à jour son prix.

L’outil AMR (Analysis, Migration, Reporting) – présent dans, et à partir de, SQL Server Management Studio 2014 – peut être utilisé afin de « mesurer » l’éligibilité des tables ou/et procédures stockées aux traitements In-Memory OLTP.

Limitations et recommandations

Comme toute technologie qui se respecte, In-Memory OLTP possède ses propres limitations. Les plus connues étant :

  • Certains types de données ne sont pas supportés lors de la création d’une table optimisée en mémoire : image, geography, hierarchyid, ntext, sql_variant, xml, varbinary(max), types de données customisés (UDTs, user data types), datetimeoffset.
  • Côté haute-disponibilité, le database mirroring.
  • Côté options de bases de données, l’auto-close notamment.
  • Côté stockage, la fonctionnalité FILESTREAM.
  • Côté définition de colonnes, FOREIGN KEY, CHECK, UNIQUE, IDENTITY, ROWGUIDCOL, colonnes calculées.
  • Côté indexes, les indexes en cluster, les indexes column store… De plus, toute table optimisée en mémoire ne peut supporter que jusqu’à 8 indexes.
  • Côté définition de tables, les modifications DDL basées sur ALTER TABLE ne sont pas supportées.
  • Les tables optimisées en mémoire en mode SCHEMA_AND_DATA sont limitées à 512 Go.

D’autres limitations existent, comme le non-support de la compression de données, l’exécution de batches multiples via MARS (Multiple Active Result Sets), MERGE, Database Snapshot,…

Côté recommandations :

  • L’Instant File Initialization doit être activé, pour des raisons de performances. Pour plus de détails, voir ici.
  • Il est important d’avoir suffisamment de mémoire afin de supporter les traitements associés au moteur In-Memory OLTP. Plus de détails ici
  • Il faut idéalement disposer d’un espace disque disponible équivalent à 2 fois la taille des tables optimisées en mémoire durables.
  • Les fichiers de checkpoints ne doivent pas être supprimés manuellement. Ce soin doit être laissé à SQL Server. Il est toutefois possible de forcer leur nettoyage en lançant une sauvegarde transactionnelle, par exemple. La contrepartie sera l’ajout de 5 paires de fichiers checkpoint vides (avec une taille de 128 Mo, pour chaque fichier de données de chaque paire).

Pour aller plus loin…

Gardez un oeil ici pour d’autres sujets autour d’In-Memory OLTP.

Vous pouvez également jeter un coup d’œil ici, si vous souhaitez approfondir le sujet. Ainsi que pour le BOL de référence.

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