Vous avez dit OLTP en mémoire dans SQL Server ?!


Comme nous avons pu déjà le souligner, ce blog se veut un cadre d’échanges et de partage avec la mise en avant de problématiques données assorties de la proposition de scénarios/solutions type prêts à l’usage. Nous vous proposons d’aborder aujourd’hui un sujet un peu connexe avec la disponibilité prochaine de Microsoft SQL Server 2014 début avril, et donc de considérer l’espace de ce billet justement l’une des innovations proposées par SQL Server 2014 pour tirer au mieux partie des ressources matérielles de la plateforme serveur (mémoire et processeurs), à savoir le « in-Memory OLTP », aussi connu par le nom de code « Hekaton ».

(A la date de publication de ce billet, Microsoft SQL Server est RTM (Ready To Manufacture) depuis le 18 mars et vous pouvez toujours télécharger la version Microsoft SQL Server 2014 Community Technology Preview 2 (CTP2) ici.)

A ce propos, J’ai aujourd’hui le plaisir de publier dans ce blog ce billet rédigé par Frédéric Pichaut, ingénieur d’escalade senior au Support Premier Microsoft. Avec près de 20 années de support passes sur SQL Server, et plus spécialement dans les problématiques de performance, Frédéric s’est beaucoup focalisé sur ce secteur. Je profite de cette occasion pour remercier très sincèrement Frédéric pour cette contribution.

Je vous souhaite une bonne lecture de ce billet.

–Philippe

_____________________________________________________________________________________________

SQL Server comporte plusieurs capacités en termes de technologies en mémoire (« In-Memory ») ; ces technologies sont regroupées sous la dénomination (moteur d’analyse) « xVelocity ».

Pour différents types de charges de travail (workload), nous avons ainsi :

  • Les indexes Columnstore dans SQL Server 2012/2014 utilisés plus particulièrement pour PDW (Microsoft SQL Server Parallel Data Warehouse),
  • L’analyse en mémoire (In-Memory Analytics) avec Power Pivot pour Excel 2010 et Excel 2013, ainsi qu’avec Analysis Services 2012 en Modèle tabulaire,
  • Le cache distribué avec Microsoft AppFabric, une solution de cache de tiers intermédiaire (mid-tier),
  • Et nouvellement l’OLTP (Online transaction processing) en mémoire (In-Memory OLTP) pour SQL Server 2014, objet de ce billet.

Si l’OLTP en mémoire constitue l’une des innovations autour de Microsoft SQL Server 2014 pour tirer au mieux parti de la mémoire et processeurs, cette dernière véhicule plusieurs mythes à son sujet comme suit :

  1. L’OLTP en mémoire constitue une réponse aux offres de nos compétiteurs
  2. L’OLTP en mémoire, c’est comme un DBCC PINTABLE en Transact-SQL
  3. L’OLTP en mémoire est un nouveau produit séparé
  4. On peut passer une application en OLTP en mémoire sans aucun changement
  5. Comme les tables sont en mémoire, les données ne sont pas durables ou disponibles pour la haute disponibilité. Tout est perdu après un crash

Regardons plus précisément chacun de ces points ; ce qui nous permettrons de découvrir ce qu’est l’OLTP en mémoire.

L’OLTP en mémoire constitue une réponse aux offres de nos compétiteurs

Dans la pratique, le projet « Hekaton » a commencé il y plus de 4 ans en réponse à l’évolution (anticipée) des besoins Métier et bien sûr pour bénéficier pleinement dans ce contexte des évolutions hardware en la matière (mémoire et processeurs).

Ce projet résulte du fruit d’une collaboration entre le groupe produit SQL Server et Microsoft Research (MSR), laboratoire de recherche de Microsoft (qui voit, dans l’actualité du mois, Leslie Lamport, l’un de ses chercheurs au laboratoire commun Microsoft Research – Inria, se voir décerner le prix Turing 2013 de l’ACM (Association for Computing Machinery), prix considéré comme le « Nobel de l’Informatique ».)

Vous pouvez visionner une petite vidéo d’introduction sur cette collaboration ici.

Dans la pratique, le projet a été initialement présenté lors de la 38ième Conférence Internationale sur les très grosses bases de données avec la publication suivante : High-Performance Concurrency Control Mechanisms for Main-Memory Databases.

La publication finale de MSR sur l’OLTP en mémoire est disponible sur le site de MSR : Hekaton: SQL Server’s Memory-Optimized OLTP Engine.

Passons au second mythe.

L’OLTP en mémoire, c’est comme un DBCC PINTABLE en Transact-SQL

La commande DBCC PINTABLE charge une table complète en mémoire. Les pages de la table ne sont pas vidées de la mémoire.

Pour mémoire, cette fonctionnalité a été introduite dans la version 6.5 de SQL Server dans un souci de performances. L’exécution de cette commande provoque toutefois des effets secondaires indésirables, tels notamment que le risque d’endommagement du pool de tampons.

Cette commande n’est pas obligatoire et a été supprimée (à partir de SQL Server 2012) pour empêcher d’autres problèmes. Dans la pratique, la syntaxe pour cette commande fonctionne toujours mais elle n’affecte pas le serveur.

La structure de la table en mémoire et sa gestion était exactement comme pour les autres tables (locking et latching).

« Hekaton » constitue un nouveau design pour optimiser les opérations sur les données en mémoire. Les pages ne sont plus stockées dans le buffer pool. L’accès aux pages passe d’un mode de verrouillage pessimiste (locking traditionnel) à un mode optimiste (sans locking). Le but est de supprimer la contention sur les pages. Ainsi, plutôt que de bloquer l’accès à une page, on utilise maintenant une notion de version que nous détaillerons plus loin dans ce billet.

L’OLTP en mémoire est un nouveau produit séparé

« Hekaton » est entièrement intégré au moteur SQL Server 2014. Il n’y a pas de composant supplémentaire à acquérir ou à installer.

Pour illustrer cette intégration, on peut représenter la structure de SQL Server avec le schéma suivant. Les tables et indexes sont stockés sur disque et les pages sont en mémoire dans le « Buffer Pool ». Le journal de transaction (Log) doit garder une trace de toutes les modifications de données survenues (Insert, Update, et Delete).

image

Les pages du Buffer Pool sont partagées par plusieurs tables. Leurs accès doivent donc être gérés par un mécanisme (Latching) pour éviter des corruptions en mémoire. L’exécution des commandes et des procédures passent par un « parser ».

Avec l’intégration de « Hekaton », voici la nouvelle organisation du moteur de SQL Server :

image

La nouvelle architecture nous montre que les tables MEMORY_OPTIMIZED sont dans un espace mémoire indépendant du Buffer Pool, avec la possibilité d’avoir des tables durables (persistantes) ou non-durables (volatiles) ; ce qui n’était pas le cas avant.

La structure sur disque de la table est dans un groupe de fichiers (Filegroup) particulier. Les indexes ne sont plus stockés sur les disques mais uniquement gérés en mémoire. Un mode « Interop » permet d’exécuter des requêtes combinant tables traditionnelles et tables en mémoire. Un autre mode, dénommé « Natif », permet d’accéder uniquement aux tables en mémoire par une compilation de procédures en code natif C. Nous allons y revenir.

Poursuivons notre exploration des mythes.

On peut passer une application en OLTP en mémoire sans aucun changement

Ce n’est pas entièrement vrai. Il faut envisager au minimum une modification de syntaxe de création des tables, créer le nouveau groupe de fichiers et apporter quelques modifications au code des procédures.

Si l’application utilise déjà des procédures stockées, les modifications peuvent s’avérer minimes.

La création du nouveau groupe de fichiers s’effectue par les commandes ALTER DATABASE suivantes :

ALTER DATABASE ContosoOLTP ADD FILEGROUP [ContosoOLTP_hk_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE ContosoOLTP

        ADD FILE (NAME = [ContosoOLTP_fs_dir],

        FILENAME = ‘H:\mounthead\Data\ContosoOLTP_fs_dir’) to FILEGROUP [ContosoOLTP_hk_fs_fg];

La création d’une table MEMORY_OPTIMIZED par la commande CREATE TABLE suivante :

CREATE TABLE Customers (

        CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=100000),

        CompanyName nvarchar (40) NOT NULL INDEX IX_CompanyName HASH(CompanyName) WITH (BUCKET_COUNT=65536),

        ContactName nvarchar (30) NOT NULL ,

        ContactTitle nvarchar (30) NOT NULL ,

        Address nvarchar (60) NOT NULL ,

        Ville nvarchar (15) NOT NULL INDEX IX_Ville HASH(Ville) WITH (BUCKET_COUNT=1024),

        Region nvarchar (15) NOT NULL INDEX IX_Region HASH(Region) WITH (BUCKET_COUNT=1024),

        PostalCode nvarchar (10) NOT NULL INDEX IX_PostalCode HASH(PostalCode) WITH (BUCKET_COUNT=100000),

        Country nvarchar (15) NOT NULL ,

        Phone nvarchar (24) NOT NULL ,

        Fax nvarchar (24) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)

Les indexes sur la table ne sont plus de classiques indexes B-Tree mais de type condensat (hash). Le calcul de hachage (hashing) s’effectue par une fonction interne qui est paramétrée par le nombre de compartiments (bucket) défini dans la commande. Concrètement, plus on a de compartiments, moins on a de risques que des collisions existent dans les résultats de la fonction.

On notera aussi l’option de création DURABILITY. Elle détermine si la table est persistante sur disque (SCHEMA_AND_DATA) ou si elle est volatile (DATA_ONLY)

Les procédures stockées en mode natif doivent être de la forme suivante via la commande CREATE PROC :

CREATE PROC InsertCustomers (@CustomerID nchar(5),@CompanyName nvarchar(40),

                        @ContactName nvarchar(30),@ContactTitle nvarchar(30), @Address nvarchar(60),

                        @Ville nvarchar(15),@Region nvarchar(15),@PostalCode nvarchar(10),

                        @Country nvarchar(15),@Phone nvarchar(24),@Fax nvarchar(24))

        WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = ‘ENGLISH’)

        INSERT INTO [dbo].[Customers] VALUES(@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,

          @Ville,@Region,@PostalCode,@Country,@Phone,@Fax);

END

L’outil AMR (analyse, migration et rapport) intégré dans la console d’administration SQL Server Management studio permet d’évaluer sur une application existante la pertinence de passer au mode « In-Memory » et d’évaluer le cas échéant la charge de travail induite par les modifications à apporter.

Il est temps d’aborder notre dernier mythe.

Comme les tables sont en mémoire, les données ne sont pas durables ou disponibles pour la haute disponibilité. Tout est perdu après un crash

Les données sont résidentes sur disque, et survivent à un crash. Comme nous l’avons vu dans le schéma précédent de SQL Server, les données sont stockées dans un groupe de fichiers (FileGroup) particulier, sauf pour les tables volatiles.

Pour prendre en compte le mode de verrouillage optimiste, les données ne sont plus organisées page par page mais à la suite les unes des autres avec une période de validité. Chaque ligne est enregistrée avec un horodatage (TimeStamp) définissant la transaction qui l’a créé. Elle comporte un autre horodatage définissant sa fin de validité.

Quand une ligne est créée, elle se présente ainsi sous la forme :

image

Cette ligne est valable de la transaction 50 à l’ infini.

Si la transaction 200 fait un UPTADTE (Jean, Paris) en (Jean, Toulon) alors la ligne précédente devient :

image

Insérons une nouvelle ligne :

image

La suppression d’une ligne se fait par une modification de l’horodatage de fin de validité, si la transaction 250 fait un DELETE de la ligne :

image

Le groupe de fichiers sur disque est composé par un ensemble de fichiers appelés fichiers DATA et fichiers DELTA. Les DELTA comporte les modifications et les DATA les données valides. Un processus en tâche de fond se charge de faire le ménage et de réorganiser les fichiers pour ce soit transparent.

Un fichier appelé ROOT est créé lors de chaque point de contrôle CHECKPOINT. Ce fichier contient une liste des fichiers DATA et DELTA afin de reconstruire les enregistrements en mémoire après un redémarrage de l’instance ou une restauration de la base. Les données résident uniquement en mémoire en cas de crash ; la relecture du journal de transactions couplé au fichier checkpoint permet de recréer les données.

L’OLTP en mémoire est compatible avec les composants de haute disponibilité de SQL Server, incluant (les groupes de disponibilité) AlwaysOn.

En guise de conclusion

L’OLTP en mémoire est complètement intégré à Microsoft SQL Server 2014.

Comme nous venons de le voir, son objectif n’est pas de charger de grosses tables en mémoire mais de supprimer la contention sur des tables clés de l’application. Ce traitement est complétement ACID (Atomicity, Consistency, Isolation, Durability). Ainsi, les données sont préservées en cas d’arrêt et la concurrence d’accès est gérée.

Les indexes sont optimisés en mémoire et les procédures qui n’accèdent qu’à ces tables peuvent être compilées en mode natif pour augmenter encore la performance.

Lors de précédentes présentations, nous avons eu la chance de montrer une application avec les résultats suivants en termes de performances en passant au mode Interop en premier (table en mémoire, procédures normales) et ensuite au mode natif.

clip_image002

Dans cette illustration, nous sommes ainsi passés de 1500 transactions par seconde à 20000 transactions par seconde en mode Interop puis à 66000 transactions par seconde en mode natif.

Nous espérons que ce billet sur l’OLTP en mémoire vous aura donné envie d’approfondir cette innovation et technologie apportée par SQL Server 2014.

Pour cela, vous pouvez commencer par visionner sur le sujet le webcast de la session Deep Dive Performance, le In-Memory dans SQL Server lors des derniers Microsoft TechDays 2014.

Pour aller plus loin dans la compréhension des différents éléments discutés ici, nous vous invitons à lire :

Ceci conclut ce billet d’introduction à l’OLTP en mémoire, une des innovations de SQL Server 2014.

Comments (0)