SQL Server 2014–Base de données et première table Hekaton

Avant d’entrer dans le vif du sujet, il est intéressant de rappeler ce que représente le projet Hekaton et faire le point sur la (nouvelle) terminologie.

Tout d’abord, le projet Hekaton, initial il a plus de 4 ans répond à un constat : le prix de la mémoire chute drastiquement alors que la latence disque (puisque l’on sait très bien que la capacité ne fait qu’augmenter) stagne. Il fallait donc imaginer un nouvel axe de recherche pour améliorer les performance d’une base de données. Et quoi de plus normal de commencer à balayer devant sa porte en cherchant ce qui consomme le plus de temps et de ressources sur le traitement d’une transaction ? D’où l’idée de travailler sur un nouveau “moteur” qui passerait outre les problèmes de verrouillage et de pagelatch qui empêchent aujourd’hui certaines bases de données de monter en charge.

Et c’est bien de cela dont il s’agit. La table qui va entrer dans le monde Hekaton va résider en totalité en mémoire, ne subira pas la contrainte du PageLatch (et oui, on oublie le buffer pool …) et on bascule sur un modèle de verrouillage optimiste.

Effectivement, la première chose que l’on peut changer c’est le modèle de stockage. C’est pourquoi il va falloir se familiariser avec de nouveaux acronymes :

  • les tables basées sur disque (disk-based tables) : le modèle “actuel” si l’on peut dire
  • les tables optimisées en mémoire (memory-optimized tables) : les tables qui utilisent la nouvelle structure de données du projet Hekaton
  • les procédures stockées compilées en code natif : il sera dorénavant possible de créer des procédures stockées dont la compilation sera directement faire en code natif (en C) et injectées dans la base de données
  • les transactions Cross-container qui vont utiliser des tables basées sur disque et des tables optimisées en mémoire
  • le mode interop qui permettra à des requêtes TSQL ou procédures stockées classiques d’attaquer des tables optimisées en mémoire

Un petit schéma valant mieux qu’un long discours, on peut résumer Hékaton avec le diagramme suivant :

image

On distingue clairement que les mémory-optimized tables n’entrent pas dans le buffer pool et disposent de leur propre modèle de stockage.

On peut également déduire du schéma que l’application cliente, passant par la couche TDS, ne sera pas impactée par le changement de mode de stockage des tables ou l’utilisation de code natif. Tout sera transparent.

Les présentations étant faites, place à l’installation. Eh bien, rien d’extraordinaire : Hekaton fait partie intégrante de SQL Server 2014 (par contre côté édition , je miserais sur Entreprise), donc un setup “normal” de SQL Server suffit.

image

Je ne vais pas détailler le processus ! Vous savez faire.
On lance management studio et l’on se connecte.

imageimage

La CTP1 de SQL Server 2014 ne concerne que les nouveautés liées à Hekaton. Et certaines fonctionnalités ne seront opérationnelles qu’avec la CTP2 ou ultérieurement. Seule le classement BIN2 est utilisable et les range index (index non cluster sur des colonnes autres que la PK) ne peuvent pas être crées.

Pour ce premier test, je vais simplement créer une base, créer une table, et ajouter quelques lignes, une fois dans un modèle disk-based table et une fois dans le modèle memory-otpimized table.
Le test est effectué sur une VM disposant de 8 cores et 8 GB de RAM.

image

Ah, les copies d’écran ne vous intéressent pas ? OK, place au code.

Création des 2 bases de données (il est tout a fait possible de créer des tables Hekaton et des disk-based tables dans la même base de données):



CREATE DATABASE SampleDB_DiskTable 
ON 

PRIMARY (NAME = SampleDB_DiskTable_data, 
         FILENAME = 'C:\Data\SampleDB_DiskTable_data.mdf', size=500MB)
 
LOG ON (NAME = SampleDB_DiskTable_log, 
        FILENAME='C:\Data\SampleDB_DiskTable_log.ldf', size=500MB)

COLLATE Latin1_General_100_BIN2;

ALTER DATABASE SampleDB_DiskTable SET RECOVERY SIMPLE;
GO


CREATE DATABASE SampleDB_Hekaton 
ON 

PRIMARY (NAME = SampleDB_Hekaton_data, 
	     FILENAME = 'C:\Data\SampleDB_Hekaton_data.mdf', size=500MB),
 
FILEGROUP SampleDB_Hekaton_fs_fg CONTAINS MEMORY_OPTIMIZED_DATA
	(NAME = SampleDB_Hekaton_hk_fs_dir, 
	 FILENAME = 'C:\Data\SampleDB_Hekaton_fs_dir')
 
LOG ON (NAME = SampleDB_Hekaton_log, 
        FILENAME='C:\Data\SampleDB_Hekaton_log.ldf', size=500MB)

COLLATE Latin1_General_100_BIN2;
GO

ALTER DATABASE SampleDB_Hekaton SET RECOVERY SIMPLE;
GO

J’ai volontairement positionné le journal de transaction à 500 MB pour ne pas subir d’accroissement automatique.

Sur l’ordre SQL de création de la base de données version Hekaton, notez la partie CONTAINS MEMORY_OPTIMIZED_DATA qui va créer le répertoire correspondant sur disque, comme le fait l’ajout d’un filegroup de file streaming. Il est impératif d’ajouter au minimum 1 fichier (répertoire) dans la FileGroup memory optimized afin de pouvoir créer une table memory-optimized. Notez aussi que l’on ne peut disposer que d’une seul groupe de fichier memory-optimized.

Niveau stockage des données, voici ce qu’il faut retenir. Le mécanisme sous jacent au stockage Hekaton repose sur le FileStream. Hekaton utilise 3 types de fichiers pour son stockage (donc en plus des tradictionnel MDF/NDF/LDF). L’ajout d’information dans le journal de transaction ne se fait que lors du commit, ce qui permet encore une fois de gagner en performance si la transaction est annulée.

  • Les fichiers DATA FILES: d’une taille approximative de 128 MB, ils sont organisés en page de 256 KB et ne stockent QUE les données insérées, dans un ordre chronologique. Si un update est exécuté, alors une nouvelle version de l’enregistrement est ajouté dans un DATA FILE. Une fois le fichier plein, il est fermé et passe en lecture seule. Un nouveau fichier sera ouvert et l’espace disque préalloué. Retenez aussi qu’une transaction ne peut pas être enregistrée sur deux fichiers DATA.
  • Les fichiers DELTA FILES: leur taille n’et pas constante, ils sont organisés en pages de 4KB. Ces fichiers ne contiennent que les ID des enregistrements supprimés, accompagnés des informations (un timestamp) de début et de fin de validité de la donnée.
  • Les fichiers ROOT: un nouveau fichier ROOT est créé lors de chaque CHECKPOINT. Le fichier contient une liste de fichiers DATA et DELTA afin de reconstruire les enregistrements en mémoire après un restart l’instance ou un restore de la base. Les données résident uniquement en mémoire en cas de crash, la relecture du journal de transactions couplé au checkpoint file permet de recréer les données.

Chaque update d’enregistrement génère une nouvelle version de celui ci, accompagné d’un TimeStamp de début et fin de validité. Régulièrement, le garbage collector va supprimer les versions d’enregistrement dont les TimeStamp de fin sont antérieurs à la plus ancienne transaction susceptible d’accéder à ces données.

Les checkpoint manuels forcent la fermeture anticipée d’un fichier DATA. Ce phénomène engendre une augmentation du stockage. La réduction de l’emprunte disque s’effectue au moyen de l’opération Merge (fusion).

Ce processus fusionne 2 (ou plus) paires de fichiers DATA/DELTA en une seule paire.

Cette opération s’exécute en tâche de fond, mais il est également possible de l’appeler explicitement afin de réduire le stockage après une opération particulière ou bien de réduire le temps nécessaire à la récupération d’une base de données (recovery)

Les index, eux, ne disposent d’aucun stockage physique. Il n’occupent pas d’espace sur disque car ils résident seulement en mémoire. De fait, ils sont recréés lors du démarrage de la base de données qui peut s’en trouver rallongé et disposent d’une nouvelle structure (BW-trees), donc exit les B-tree utilisés jusque alors.

Les index ne dupliquent pas les informations de la table, ils ne font que pointer sur les données de celle ci.

Afin de reproduire le même test dans chaque base, je vais préparer quelques données issus de la base AdventureWorks que vous pouvez retrouver sur codeplex. Le script suivant est à exécuter sur chacune des bases.


CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO


SELECT FirstName,LastName
INTO [Contact]
FROM [AdventureWorks].[Person].[Contact];
GO 
	
SELECT [City]      
INTO [Address]	
FROM [AdventureWorks].[Person].[Address];
GO		

SELECT [OrderDate] 
INTO [SalesOrderHeader]
FROM [AdventureWorks].[Sales].[SalesOrderHeader];
GO

 

Maintenant la création des tables dans les bases correspondantes :


USE SampleDB_DiskTable
GO
CREATE TABLE [TestTable] 
( 
	[TestTableID] INT  PRIMARY KEY CLUSTERED,
	[Name] varchar(100) not null  , 
	[City] varchar(50) not null, 
	[LastModified] datetime not null
) 
GO



USE SampleDB_Hekaton
GO
CREATE TABLE [TestTable] 
( 
	[TestTableID] INT not null PRIMARY KEY NONCLUSTERED 
                                   HASH WITH (BUCKET_COUNT = 2000000),
	[Name] varchar(100) not null, 
	[City] varchar(50) not null, 
	[LastModified] datetime not null
	-- index non cluster non présents dans la CTP1
	--,INDEX TestTable_nci_CityLastModified NONCLUSTERED ([City],[LastModified]) 
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

 

Notez l’écriture de la PK de la table Hekaton qui contient la notion de buckets qui vont contenir les valeurs hashées de la PK. On peut raisonnablement partir sur 2x le nombre de lignes prévisionnel pour définir le nombre de buckets.

Maintenant on ajoute quelques enregistrements dans cette table :

;With 
Names AS -- 1 227 708 rows
(
	SELECT Nom,
	       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RID 
	FROM (
			SELECT c1.[FirstName] + ' ' + c2.[LastName] AS Nom 
			FROM (
					SELECT DISTINCT [FirstName] FROM [Contact] 
				 ) c1
			CROSS JOIN 
			(
					SELECT DISTINCT [LastName] FROM [Contact] 
				 ) c2
		) as t
),
Cities AS -- 1 265 000 rows
(
	SELECT [City],ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RID
	FROM (	SELECT DISTINCT [City]      
			FROM [Address]
		) as t
	CROSS APPLY dbo.GetNums(2200)
)
,
MyDates AS -- 1 686 000 rows
(
	SELECT n,[OrderDate],
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as RID
	FROM (
		SELECT DISTINCT [OrderDate] FROM [SalesOrderHeader]
		) as t
	CROSS APPLY dbo.GetNums(1500)
)
INSERT INTO [TestTable] (Name, City,LastModified, TestTableID)
SELECT Nom,City,OrderDate,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
FROM Names n
INNER JOIN Cities c on n.RID = c.RID
INNER JOIN MyDates d on d.RID = n.RID

Cette requête permet d’ajouter 1.227.708 enregistrements dans la table. Le Nom fait office de clé primaire.

Le temps d’exécution laisse entrevoir la rapidité des memory-optimized tables :

  • Hekaton : 20 secondes
  • disk-based table : 2 minutes et 8 secondes. Et j’ai du ajouter l’option MAXDOP 1, sans quoi la requête aboutit (ou pas !) après des dizaines de minutes ! La TempDB est très fortement utilisée et le journal de transaction de la base également.

imageimage

Ce gain de perf est principalement du a la modification effectuée au niveau de l’enregistrement des informations dans le journal de transaction.

image

En utilisant un simple SELECT INTO depuis la CTE, le temps d’exécution se rapproche de ce que permet la table Hekaton.

Je n’ai pas cherché à optimiser la TempDB. Mais je doute que l’on retombe sur des temps comparables au modèle Hekaton.

Le journal de transaction pour une table memory-Optimized ne contient que des informations logiques, aucune information relative aux modifications physiques. De plus, aucune information liée à l’UNDO n’est présente, puisque l’on n’enregistre une transaction que lorsque celle ci est commitée. Aucune information relative aux index non plus (puisqu’il ne sera pas nécessaire de faire du REDO).

Dans le journal de transactions, les opérations impliquant des tables Hekaton sont clairement identifiées. Pour les sceptiques, un petit script qui crée une nouvelle base accompagnée d’une table nous permet de voir ces informations.


CREATE DATABASE HekatonLogFile_DB ON  
 PRIMARY (	
	NAME = [Hekaton_DB_hk_fs_data], 
	FILENAME = 'C:\data\HekatonLogFile_DB_data.mdf'), 

 FILEGROUP [Hekaton_DB_hk_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA (
	NAME = [Hekaton_DB_hk_fs_dir],  
	FILENAME = 'C:\HekatonLogFile_DB_hk_fs_dir')

 LOG ON (
	name = [hktest_log], 
	Filename='C:\data\HekatonLogFile_DB.ldf', size=100MB)
 COLLATE Welsh_100_BIN2 
GO

USE HekatonLogFile_DB
GO

CREATE TABLE [dbo].[TestTable]
( [c1] int NOT NULL, 
  [c2] char(100) NOT NULL,  

  CONSTRAINT [PK_TestTable] PRIMARY KEY NONCLUSTERED HASH ([c1]) 
		WITH(BUCKET_COUNT = 1000000)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Les données présentes dans le journal de transaction version Hekaton reflètent la création de la base et de la table :

image

Lorsque l’on lance un CHEKPOINT, l’opération MERGE est lancée

image

On aperçoit dans le journal de transaction les opérations demandées au Filestream, le stockage sous-jacent de Hekaton.

image

On insère ensuite 2 enregistrements dans la table et on regarde à nouveau le contenu du journal de transaction :

image

In distingue clairement la transaction dans fn_dblog (première partie de la copie d’écran) et fn_dblog_xtp pour Hekaton. Le timestamp de la transaction est visible.

Un INSERT et un UPDATE dans la table, et les informations apparaissent nettement :

image

Encapsulés dans une transactions, un INSERT et un UPDATE sont bien considérés aussi comme une seule transaction par la table Hekaton (heureusement …)

image

image

Notez le TimeStamp qui ne fait que croitre. Le garbage collector utilisera ces informations pour savoir quels enregistrements peuvent être supprimés des physiquement des fichiers DATA (le DELETE n’inscrit des infos que dans les fichiers DELTA) et ensuite permettre au MERGE de réduire l’espace en fusionnant des paires de fichiers DATA et DELTA.

Si une transaction attaque une disk-table et une table hekaton, alors le journal de transaction montre clairement qu’aucune information n’est inscrite pour une table Hekaton, alors qu’un update sur une disk-table doit enregistrer la transaction et la compensation, même si un ROLLBACK s’en suit.

image

L’ordre TRUCNATE ne fonctionne pas avec une table memory-optimized, amis les DELETE sont rapides (un billet à venir sur ce sujet).

image

La DMV sys.dm_db_xtp_checkpoint_files semble encore buggée, je n’ai aucune taille sur les fichiers …

image

Si l’on exécute un CHECKPOINT et que l’on attend quelques secondes, on constate que l’opération MERGE a bien eu lieu, une paire de fichiers a été supprimée :

image

Okay, maintenant que nous savons créer une base et des tables, reste à faire fonctionner ce moteur à plein régime :  le point fort de Hekaton reste la montée en charge et la réduction de la contention (page latches dus à des INSERT/UPDATE/DELETE concurrents).

Un prochain billet abordera les procédures stockées compilées en code natif, la montée en charge et les performances, de manière globale …  Sourire

Stay tuned.

A propos Christophe

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

4 commentaires pour SQL Server 2014–Base de données et première table Hekaton

  1. Merci pour cette introduction très instructive

  2. Excellent article, très clair et utile pour un petit B.I. Men comme moi, Merci Beaucoup !

  3. Ping : SQL Server 2014–Procédure stockée en code natif | Christophe LAPORTE – Consultant SQL Server

  4. Ping : Retour sur le PASS Summit | SQL Server dans le détail

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