Azure SQL Data Warehouse est un moteur MPP (Massive Parallel Processing) en PaaS disponible sur sur Azure. C’est la version PaaS de l’édition appliance APS (anciennement PDW). Comme tout moteur MPP (Tera Data, Presto, Impala, …), l’objectif est de pouvoir exécuter des requêtes SQL de type Data Warehousing sur de très gros volumes de données (plusieurs dizaines de To voire un Po ou plus) dans des temps relativement courts, idéalement en dessous de la seconde.

Azure SQL Data Warehouse était jusqu’ici principalement vendu pour des cas d’usage batch plutôt que de l’interactive Query. C’est pourtant pour ce use case que nous avons implémenté une solution technique basée sur Azure SQL Data Warehouse et qui est en production depuis plus de 6 mois.

A l’heure où j’écris cet article, nous recevons 250 millions de lignes par jour et disposons de plus de 80 milliards de lignes dans la table de fait. 15000 utilisateurs possibles, plus de 1200 visiteurs uniques par jour pour 34000 requêtes avec une médiane sur l’ensemble des requêtes autour de 1s.

La difficulté du projet réside dans le besoin de pouvoir filtrer sur la plupart des champs dont le filtre sur une profondeur de date sans limite, et d’afficher des agrégats de type non additif comme des Distinct Count (mais pas seulement heureusement).

Côté performance :

Prenons en exemple le mois précédent (avril 2018).

Sur le graphique 1, nous voyons qu’en moyenne sur les jours ouvrés pas loin de 1500 visiteurs uniques se connectent et lancent environ 30000 requêtes.

Arrêtons-nous sur la journée avec le plus de trafic sur le mois d’avril, soit le 3 avril et regardons le détail par heure des requêtes envoyées au Data Warehouse. Nous pouvons voir que le nombre maximal de requêtes simultanées par seconde est plus intense le matin. Et de fait la médiane des temps d’exécution des procédures stockées à ce même moment augmente assez fortement.

Il est important d’analyser les chiffres pour comprendre ce qui se passe. La médiane est calculée sur l’ensemble des requêtes seconde par seconde. Au moment où la médiane est égale à 16 173ms, il y a 5 requêtes concurrentes dont 1 requête prenant beaucoup plus de temps.

Pour expliquer l’hétérogénéité des temps d’exécution, il faut savoir plusieurs choses :

• Les requêtes peuvent ne pas brasser le même volume de données. Le volume maximal qu’une requête peut brasser est de 1,5 milliard de lignes.
• Autre point, la complexité des requêtes est assez hétérogène.
• Les IOs constituent un goulet d’étranglement.

Néanmoins les performances restent bonnes, au total le temps médian d’exécution sur l’ensemble des exécutions est de 800ms. C’est-à-dire que la moitié des requêtes prennent moins de 800ms, ce qui est plutôt satisfaisant.

L’architecture du Projet :

Le projet est composé de 4 grandes parties :

• Une paire de Data Warehouse en DW1200 pour répondre aux requêtes utilisateurs.
• Une Wep API composée d’un App Service (5), d’un Storage Account (6) et d’une base SQL Azure (4) dont l’objectif est de permettre des opérations sur les Data Warehouse, import, plans de maintenance, cache warmup, accès aux vues systèmes (DMV), etc …
• Le Data Lake Store (1) permet de pouvoir centraliser toutes les données issues des systèmes sources.
• Un système de BI dit traditionnel en full PAAS basé sur une base SQL Database Cores v2 Gen 5 (7) et d’un modèle Tabular en Direct Query déployé sur Azure Analysis Services (9). La collecte du datamart de supervision est réalisé via des Pipelines Data Factory v2 (8) faisant tourner des packages SSIS grâce à Integration Runtime.

Les clés de la réussite du projet :

Le succès du projet tient à quelques détails en voici quelques éléments révélés :

• Système de maintenance dynamique des partitions

Vis à vis de notre workload, la meilleure clé candidate pour le partitionnement n’était pas le temps (ce qui aurait simplifié les choses), mais le producteur puisque cette information est systématiquement présente dans la clause where. Et donc partitionner par cette clé permet de pouvoir sélectionner une unique partition sans recourir à une opération « Clustered ColumnStore Index Scan » beaucoup moins performante.

Nous ne pouvions pas envisager de créer une partition par producteur parce que le nombre de producteur était trop important et surtout les indexes Clustered ColumStore, pour atteindre leur meilleur niveau de compression et donc être performant, ont besoin d’avoir au moins 1 millions de lignes par partition et par distribution. Le nombre de distribution étant de 60 que les partitions fassent au moins 60 millions de lignes.

Ainsi il est intéressant d’essayer de maintenir un nombre de ligne proche de 60 millions dans chacune des partitions ou d’un multiple de 60.

Chaque jour nous recevons 250 millions de lignes faisant grossir chacune des partitions. Adapter dynamiquement le schéma de partitionnement était donc crucial.

L’ajout de partition se fait par le split d’une partition existante. Alors qu’il est possible spliter une partition non vide sur un index rowstore il n’est pas possible de le faire sur un index Clustered ColumnStore. Il est donc nécessaire de vider la partition avant de le faire.

Dans le but de conserver une continuité de service totale, nous avons mis en place un algo (Cf image ci-dessous) basé sur 2 tables supplémentaires, une table de split afin de copier les données dans le nouveau schéma cible. Une table de purge afin de pouvoir détacher la partition de la table à partitionner en les attachant à la table de purge.

Une fois la partition détachée (vidé donc de la table principale) il est possible de spliter la table comme attendue et de pouvoir affecter (SWITCH) les partitions de la table de split vers la table à partitionner.

Cette opération est assez délicate puisque pour ne pas perdre de données il faut être capable en cas d’erreur de pouvoir remettre la table à partitionner dans son schéma initial, si besoin, et de réattacher la partition détachée.

• Table d’agrégat et distribution adaptée à chaque typologie de requête

Puisque nous avions plusieurs familles de requête, nous avons pris le parti de répliquer la donnée afin de réduire le nombre de lignes pour chacune de ces familles et surtout d’avoir des clés de distributions (HASH) adaptées pour chacune de ces familles dans le but d’optimiser le plan d’exécution des requêtes en évitant les opérations DMS (Data Movement Service) coûteuses comme peut l’être l’opération ShuffleMove qui consiste à déplacer une partie des données entre chacun des nœuds afin de pouvoir finaliser les opération d’agrégation (GROUP BY) au niveau de chaque Compute Node.

Ainsi une requête SELECT TOP 10 MonId, SUM(MaMesure) AS MaMesure FROM MaTable GROUP BY MonId tirera profit de requêter une table agrégée par MonId plutôt que par MonChampQuiNaRienAVoirAvecMaRequete.

• Plan de maintenance pour maintenir les indexes Clustered ColumnStore (CCSI) et les statistiques :

A chaque chargement la qualité des indexes Clustered Columnstore diminue avec l’apparition de lignes dans le Delta Store, c’est-à-dire des lignes stockées en mode Row et en attente de plus de 102 400 lignes pour pouvoir rejoindre un Row Group compressée en colonne. L’idéal est d’avoir un nombre de lignes moyen par rowgroup qui tend vers 1 048 476 lignes. Et pour pouvoir avoir une bonne qualité de compression, il est important d’avoir suffisamment de mémoire et pour le garantir, il faut augmenter la classe de ressource et également limiter le dégré maximal de parallélisme à 1 (MAXDOP 1).

Les statistiques sont essentielles pour orienter l’optimiseur SQL dans le choix d’un bon plan d’exécution notamment dans l’utilisation des opérateurs WHERE et GROUP BY. De ce fait la stratégie en termes de statistiques est déterminante dans les performances et le maintien de statistiques à jour l’est tout autant.

• Doublement du nombre de Data Warehouse pour éviter de perturber les lectures pendant l’alimentation et l’application des plans de maintenance.

Le stockage sur SQL Data Warehouse, en Gen 1 comme en Gen2 d’ailleurs, est du remote storage constitué de disque P30 empilés, qui sont des disques SSD de 1To de 5000 IOPS et environ 200Mo/s.

Le Rebuild des indexes sur SQL Data Warehouse ne proposent pas encore ce que son équivalent SMP On-Premise peut proposer à savoir la capacité de Rebuild Online de l’index afin de permettre de ne pas bloquer l’accès aux données.

Le Data Warehouse possède un nombre de slot de ressource limité et un nombre de requête simultanée maximum de 32 requêtes dans notre performance Tier (DW1200) qui peut monter à 128 requêtes simultannées en DW6000 (Gen 1) par exemple.

Charger des données sur un data warehouse accédé en même temps et de manière intensive par des utilisateurs est une mauvaise idée. Outre les éventuels verrous qui pourraient bloquer les requêtes concurrentes, la pression sur le disque et le fait de prendre des slots de ressources et des slots d’exécution de requête fait que dans un mode Interactive Query pour un service continu 24H/24, 7J/7, il est indispensable d’envisager de doubler les data warehouse et de construire une mécanique de SWITCH entre les Data Warehouse pour rediriger les utilisateurs sur le Data Warehouse frais, avec les indexes rebuildés, les statistiques à jour et les données montées en cache.

• Système de préchauffage permettant de monter les pages de données en mémoire

Le goulet d’étranglement de tout système de gestion de données ce sont les IOs. Donc limiter la pression disque en montant en mémoire le maximum de page de données est très important pour le niveau de performance des requêtes. De ce fait avant de mettre à disposition le Data Warehouse il est indispensable de passer un script/procédure stockée de cache warmup.

• Limites de mémoire et de concurrence

La gestion de la concurrence sur SQL Data Warehouse est très importante, puisque selon le performance tier, il y a un nombre de slot de ressource maximum. Un slot est une part du montant total des ressources CPU et mémoire du « cluster ». Les IOs ne sont pas concernées par cette limitation.

Ce système de limitation est basé sur la technologie Ressource Governor, mais la mise en place est très nettement simplifiée puisqu’il suffit d’attribuer des logins à des rôles pour les affecter à une classe de ressource.

Il existe 2 types de classe de ressources, les classes statiques qui permettent une plus grande précision dans la gestion des ressources, puisqu’il existe 8 classes statiques (de staticrc10 à staticrc80) et les classes dynamiques qui s’adaptent en fonction de la mise à l’échelle du service. A noter que cela ne fonctionne qu’en Gen2. En Gen1 les classes dynamiques ne sont pas vraiment dynamiques à l’heure où j’écris cet article. Le Gen1 ne proposant que 4 classes de ressources : smallrc, mediumrc, largerc et xlargerc, il est recommandé d’utiliser directement les classes statiques.

Sur notre projet, nous utilisons à date un performance tier DW1200 (Gen1) soit 288Go de mémoire pour 48 emplacements de concurrence et 32 requêtes simultanées.

Lorsque nous chargeons et faisons toutes nos opérations de maintenance et de mise en cache nous utilisons un login dans une classe de ressource staticrc80 ce qui prend 32 slots sur 48, soit 32/48^e sur 288Go de RAM soit 192Go de RAM utilisable pour nos opérations. Il n’est effectivement pas possible d’utiliser la totalité des ressources.

En ce qui concerne les accès en lecture, nous essayons de maximiser les possibilités de concurrence, nous utilisons donc le smallrc (égal au staticrc10) qui prend 1/48^e des ressources soit 6Go de ressource.

Quoi qu’il en soit même si nous pouvons ouvrir 48 sessions smallrc, seules 32 requêtes peuvent s’exécuter en parallèle, les 16 sessions attendent qu’un emplacement d’exécution se libère.

• Activation du pool de connexions .NET

Notre front étant codé en PHP et hébergé sur un serveur Linux, nous utilisons le driver PHP pour nous connecter à SQL Data Warehouse. Lors de nos tests nous avions remarqué que le temps d’ouverture de connexion était très élevé, entre 500ms et 800ms. Sur un temps d’exécution médian à 800ms, ce temps d’ouverture est vraiment très impactant. Le driver PHP de Microsoft pour SQL Server utilise le pool de connexion ODBC mais sur Linux il n’est pas activé par défaut et il est nécessaire de modifier un fichier de configuration (odbcinst.ini) et de positionner Pooling à True et de mettre une valeur positive à CPTimeout :

[ODBC]
Pooling=Yes

[ODBC Driver 13 for SQL Server]
CPTimeout=<int value>

L’utilisation des pools de connexion permet de passer de 500ms à 10 ou 20ms sur la phase d’ouverture de sessions. En mode Interactive Query ce n’est évidemment pas négligeable.

• Import des données en CTAS à partir de table externe (Polybase) à partir de fichiers compressés stockés sur Data Lake Store.

Sur notre projet, les données à importer sont des fichiers plats dumpés par source dans des répertoires distincts dans un Data Lake Store. Dans chaque répertoire, les fichiers plats ont 1 million de lignes sur 40 colonnes distribuées entre des types int, decimal, date, et quelques char(2) ou char(3) et sont compressé en gz.

Sur SQL Data Warehouse, à partir de la documentation officielle il est difficile de savoir si Polybase est configuré en Scale-out group, avec le moteur Polybase et le service DMS installé sur le control node et des services DMS installées sur tous les compute node.

J’ai réalisé un essai à partir d’un répertoire avec 166 510 663 lignes.

J’ai chargé les données à partir d’une table externe basée sur ce répertoire. Et j’ai fait un CTAS de cette table externe avec le query hint MAXDOP 1, sur 2 modes de stockage :

1. Un stockage en heap (sans index) et en distribution round robin (tourniquet)
2. Un stockage en CCSI, avec une distribution sur une clé de hachage avec une forte cardinalité.

J’ai testé plusieurs performance tier en Gen 1 (DW400, DW1200, DW3000) et en Gen 2 (DW1000C et DW2000C).  Chaque test a été exécuté 2 fois et le temps affiché est une moyenne des 2 temps.

On s’aperçoit dans ce graphique d’une part que :

1. Plus on multiplie le nombre de nœud (compute node) plus on charge rapidement les données.
2. Le nombre de compute node n’est pas le seul facteur de vitesse.[i]
3. Il est plus rapide de charger les données en heap, qu’en CCSI.
4. Plus on monte en configuration c’est-à-dire avec beaucoup plus de mémoire, plus l’écart entre insertion en heap ou en CCSI est faible.

A noter une anomalie, le chargement des données dans une table distribuée par hachage et avec un indexe CCSI en DW6000C ne respecte pas les règles décrites ci-avant.

• Création d’un ordonnanceur maison permettant de gérer une file d’attente et de lancer des traitements en séquence.

Pour scheduler et ordonnancer les traitements nous avons pensé en première intention à Azure Logic App et Azure Data Factory. Mais nous n’avons pas retenu ces produits et avons pris le parti de développer une Web API pour plusieurs raisons :

La première c’est que nous voulions maitriser le faire qu’aucun traitement ne puisse être parallélisé. Exemple, un rebuild index lancé pendant un chargement. Ou un chargement lancé alors qu’un autre chargement est en cours. Nous aurions pu malgré tout géré cette contrainte avec les 2 outils sus-cités.

Le deuxième c’est que nous voulions centraliser le managment du data warehouse via un webservice, afin de permettre à un tiers de pouvoir ajouter une liste de traitement à réaliser (import, rebuild, mise à jour statistic, synchronisation des tables, cache warmup,), d’être notifié à chaque étape du traitement, d’obtenir les logs d’exécution par retour d’un appel de webservice en JSON, mais aussi le paramétrage du job, ….

Faire un développement personnalisé plutôt que d’utiliser un éditeur de workflow nous donnait plus de souplesse pour l’implémentation des demandes du clients.

Conclusion et perspective d’avenir du projet :

J’ai eu le plaisir pendant ces 2 années de projet (en pointillé) de voir les améliorations apportées au service (avantage du PAAS). Pour n’en citer que 2 :

• La distribution par réplication qui existait chez son grand frère PDW et qui s’est fait attendre et qui pénalisait grandement les jointures entre la table de fait et les dimensions. Mais ceci a été corrigé.
• Ensuite le déblocage de la limitation de 32 requêtes concurrentes pour 128 requêtes. Cette évolution récente permet d’envisager des applications de plus grandes envergures.

Dans un futur proche, nous serons d’ailleurs amenés à gérer de plus en plus d’utilisateurs concurrents. Nous avons donc développé un utilitaire pour simuler une montée en charge de la plateforme. Nous devons dans les semaines qui viennent définir l’architecture qui pourra garantir des performances iso à ce que nous connaissons aujourd’hui mais pour un plus grand nombre d’utilisateurs.

---

• [i] En effet, la configuration DW1000C n’a que 2 compute node alors que la DW400 en a 4. Seulement, les compute node en Gen2 ont une configuration plus musclée, 300 Go par nœud contre 25Go en Gen1. De plus il y a des caches sur des disques NVMe.