Mes retours d’expériences
Dans cette partie, je vais partager quelques retours d’expériences significatifs.
Quitte à partager des sujets autant qu’ils soient atypiques ou techniquement intéressants voir « border line ».
Vous trouverez ci-dessous le premier de la série, il y en aura d’autres…
Table des matières
Le projet SOSQL-LOGDATA
Le projet SOSQL-LOGDATA, est un exemple parfait de solution atypique mise en place pour faire face à de gros problèmes de performance en restitution sur deux tables de plus de 60 Milliards de lignes chacune.
Le projet ainsi que les tables ont été renommés dans l’article par souci de confidentialité.
Le contexte du projet SOSQL-LOGDATA (Optimisation de deux tables)
La base de données était hébergée sur un serveur SQL 2012 Entreprise avec 8 CPU, 96Go de mémoire et des disques sur une baie Full Flash.
Les deux tables à optimiser sont :
1-[SoSqlCpgData] 2.6To pour ~60 Milliards de lignes.
2-[SoSqlCpgEvents] 4To pour ~60 Milliards de lignes
L’applicatif qui avait plus de dix ans n’était plus maintenu car personne ne maitrisait au sein de l’entreprise la technologie utilisée à l’époque de sa création à savoir Cold Fusion.
Il y avait donc des prérequis sérieusement limitatifs (voir ci-dessous).
Les prérequis
L’applicatif ne pouvant pas être modifié, il était hors de question de changer le nom et/ou la définition des tables.
La coupure de service devait être réduite au stricte minimum (2 heures maximum).
Les performances en restitution devaient être nettement améliorées, il fallait faire en sorte que les requêtes des rapports qui faisaient des Timeout (applicatif) 9 fois sur 10 ou prenaient parfois plus de 20 minutes aient des temps de réponses en secondes (max 60 secondes, imposé par le client).
Peu de clients (12) utilisaient le module applicatif concerné par ces tables, mais il fallait prévoir l’ajout éventuel de quelques nouveaux clients sachant que cela resterait tout de même limité, une dizaine tout au plus.
Le nombre de lignes globalement insérées par jour était d’environ 45 millions pour 12 clients ce qui n’a rien d’extraordinaire.
L’éventuel ajout de nouveaux clients devait être simplifié au maximum car il n’y avait que des DBA débutants dans l’équipe.
Les différents clients n’utilisaient pas la même plage de temps sur leurs rapports, certains travaillait sur 1 mois glissant d’autres sur 3 mois et plus rarement 6 mois.
Aucune décision n’avait été prise concernant l’archivage, donc tout l’historique devait être conservé dans le cadre de cette optimisation.
L’optimisation des tables devait avoir un impact minimum et ne pas perturber les autres traitements planifiés.
Dernier point, les données insérées par l’applicatif dans ces tables arrivaient par paquets de quelques centaines à quelques milliers de lignes maximum en fonction du trafic (aucun BULK INSERT), il n’y avait aucun UPDATE ou DELETE sur ces tables.
La définition des tables originelles
La solution mise en place
Partant des prérequis et en analysant la définition des tables, il est ressorti trois colonnes significatives.
–Clé technique
[LOG_ID] IDENTITY BIGINT
–ID du client
[BO_ID] INT
–Date de la données envoyée par l’applicatif
[LOG_EXTDATE] DATE
Les deux tables avaient une PK CLUSTER composite sur (LOG_ID, LOG_EXTDATE).
L’idée première qui m’est venue est de partitionner les tables, mais impossible de reconstruire les index et la PK dans le schéma de partition directement sans blocage même avec ONLINE = ON.
Je le sais d’autant plus que j’ai testé cette solution par facilité sur le serveur de test et que tout est tombé.
Le fait de partitionner ne réglait de toute façon en rien le problème d’archivage, l’intérêt du partioning est de pouvoir faire du SWITCH mais comme les clients n’ont pas la même rétention il aurait fallu prendre comme base d’archivage la rétention du client qui travaille avec l’historique le plus large.
Il fallait donc trouver une solution atypique qui permette de s’affranchir de ces problèmes tout en respectant les prérequis.
La solution a donc consisté à mettre en place un système de partition/sous-partition.
j’en vois déjà qui vont se dire « ce mec est fou !, cela n’existe pas sur SQL Server ».
Je sais bien que cela n’existe pas sur SQL Server, mais il était possible dans ce cas précis d’obtenir le même résultat par une approche différente de modélisation.
J’ai donc fait ceci :
1-Creation d’une fonction de partition sur le champ [LOG_EXTDATE]
2-Création du schéma de partition
3-Ajout d’une copie des deux tables pour chaque client (voir la proc en 6) avec une CHECK CONSTRAIN sur [BO_ID], dans le schéma de partition.
4-Création des index d’origine pour chaque table dans le schéma de partition.
5-Création de deux vues partitionnées sur ces tables avec un TRIGGER INSTEAD OFF INSERT pour répartir les données dans les tables en fonction du [BO_ID ] (voir la proc en 6).
6-Creation d’une procédure dans un schéma spécifique (exploitation), qui permet d’ajouter un client et qui prend en charge, la création des tables, index, le CREATE/ALTER des vues et des TRIGGERS des vues.
La première partie des données a été transféré en DELETE OUTPUT par blocs (4000 lignes) dans des transactions explicites (pour ne pas saturer le LOG) jusqu’à arriver pour chaque client à son historique de rétention maximum.
Une fois la rétention maximum atteinte, nous avons fait une coupure de service en HNO pour transférer les données restantes via un export en BCP natif et réimport en BCP dans les différentes tables.
Une fois le transfert terminé, j’ai renommées les tables d’origine avec [_OLD] puis renommées les vues avec le nom des tables d’origine.
Les vues prennent désormais le relais pour les INSERT, SELECT à la place des tables.
Côté performance les temps de réponses sont excellents et comme toutes les requêtes utilisent les colonnes [BO_ID] et [LOG_EXTDATE] l’optimiseur de requête tire vraiment partie du système mis en place.
Plans d'exécution d'une des vues
Les tables et vues après l'optimisation
Les tables de SWITCH
Les tables renommées
Les tables par client (BO_ID)
Les vues avec trigger
Le script pour générer les tables par clients avant d’avoir renommées les vues.
Le script pour générer les tables par clients après le renommage des vues (ajout d’un client).
Conclusion
Il est désormais facile de procéder à l’archivage des données client par client en fonction de la durée de rétention de chacun en faisant un SWITCH de partition car les tables de SWITCH n’ont pas de contrainte CHECK, donc deux tables suffisent.
Il suffit ensuite d’exporter les données en BCP natif, de faire un truncate table (table de switch) et d’archiver les exports BCP sur S3.
J’ajoute que j’en ai profité pour revoir la répartition des Data Files et optimiser le Proportional Fill, mais comme j’ai déjà écrit un article sur ce sujet je ne répète pas ici les étapes à suivre.