Dans cet article, je vais envisager d'augmenter la vitesse des rapports. Par rapport, j'entends toute requête vers une base de données qui utilise des fonctions d'agrégation. Je vais également aborder les questions liées aux ressources consacrées à la production et au support des rapports, humains et machines.
Dans les exemples, j'utiliserai un ensemble de données contenant 52 608 000 enregistrements.
En utilisant l'exemple de réserves analytiques pas difficiles, je démontrerai que même un ordinateur faible peut être transformé en un bon outil pour analyser une quantité «décente» de données sans trop d'effort.
Après avoir mis en place des expériences pas compliquées, nous verrons qu'un tableau régulier n'est pas une source appropriée pour les requêtes analytiques.
Si le lecteur peut facilement déchiffrer les abréviations OLTP et OLAP, il peut être judicieux d'aller directement à la section
ColumnstoreDeux approches pour travailler avec des données
Ici, je serai bref, car Il y a plus qu'assez d'informations sur ce sujet sur Internet.
Ainsi, au plus haut niveau, il n'y a que deux approches pour travailler avec des données: OLTP et OLAP.
OLTP - peut être traduit par un traitement de transaction instantané. En fait, nous parlons de traitement en ligne de transactions courtes qui fonctionnent avec une petite quantité de données. Par exemple, enregistrer, mettre à jour ou supprimer une commande. Dans la grande majorité des cas, une commande est une quantité de données extrêmement faible, pendant le traitement de laquelle vous ne pouvez pas avoir peur des longs verrous imposés par les SGBDR modernes.
OLAP - peut être traduit par le traitement analytique d'un grand nombre de transactions à la fois. Tout rapport utilise cette approche particulière, car dans la grande majorité des cas, le rapport produit des chiffres résumés et agrégés pour certaines sections.
Chaque approche possède sa propre technologie. Par exemple, pour OLTP, il s'agit de PostgreSQL et pour OLAP, il s'agit de Microsoft SQL Server Analysis Services. Alors que PostgresSQL utilise un format bien connu pour stocker des données dans des tableaux, plusieurs formats différents ont été inventés pour OLAP. Ce sont des tables multidimensionnelles, un compartiment rempli de paires clé-valeur et mon magasin de colonnes préféré. À propos de ce dernier plus en détail ci-dessous.
Pourquoi deux approches sont-elles nécessaires?
Il a été noté que tout entrepôt de données devait tôt ou tard faire face à deux types de charges: la lecture fréquente (écriture et mise à jour, bien sûr aussi) de très petites quantités de données et une lecture rare, mais de très grandes quantités de données. En fait, c'est une activité, par exemple, du box-office et du chef. La caisse, qui fonctionne toute la journée, remplit le stockage de petits morceaux de données, tandis qu'à la fin de la journée, le volume accumulé, si l'entreprise se porte bien, atteint une taille impressionnante. À son tour, le gestionnaire à la fin de la journée veut savoir combien d'argent le box-office a gagné par jour.
Donc, dans OLTP, nous avons des tables et des index. Ces deux outils sont parfaits pour enregistrer l'activité au box-office avec tous les détails. Les index permettent une recherche rapide d'une commande précédemment enregistrée, il est donc facile de modifier une commande. Mais afin de satisfaire les besoins du leader, nous devons prendre en compte la quantité totale de données accumulées par jour. De plus, en règle générale, le gestionnaire n'a pas besoin de tous les détails de toutes les commandes. Ce qu'il a vraiment besoin de savoir, c'est combien d'argent le box-office rapportait en général. Peu importe où se trouvait la billetterie, quand il y avait une pause déjeuner, qui y travaillait, etc. OLAP existe alors, de sorte que dans un court laps de temps, le système peut répondre à la question - combien l'entreprise a gagné dans son ensemble sans lecture séquentielle de chaque commande et de tous ses détails. OLAP peut-il utiliser les mêmes tables et index que OLTP? La réponse est non, du moins elle ne devrait pas. Tout d'abord, car OLAP n'a tout simplement pas besoin de tous les détails enregistrés dans les tableaux. Ce problème est résolu en stockant des données dans d'autres formats autres que des tableaux bidimensionnels. Deuxièmement, les informations analysées sont souvent dispersées sur différentes tables, ce qui implique leurs multiples associations, y compris les associations de type auto-jointure. Pour résoudre ce problème, ils développent en règle générale un schéma de base de données spécial. Ce schéma est optimisé pour la charge OLAP, ainsi que le schéma normalisé normal pour la charge OLTP.
Que se passe-t-il lorsque OLAP utilise un schéma OLTP
En fait, j'ai introduit cette section afin que cet article réponde clairement à mes propres exigences pour le format de ce matériel, c'est-à-dire problème, solution, conclusion.
Nous énumérons un certain nombre d'inconvénients de l'utilisation de schémas OLTP pour l'analyse des données.
- Trop d'index.
Souvent, vous devez créer des index spéciaux pour prendre en charge les rapports. Ces index implémentent un schéma de stockage de données OLAP. Ils ne sont pas utilisés par la partie OLTP de l'application, tout en exerçant une charge sur celle-ci, nécessitant un support constant et occupant de l'espace disque. - La quantité de données lues dépasse la quantité requise.
- Absence d'un schéma de données clair.
Le fait est que souvent les informations soumises par les rapports sous une forme unique sont réparties dans différents tableaux. Ces informations nécessitent une transformation constante à la volée. L'exemple le plus simple est le montant des revenus, qui consiste en espèces et en espèces. Un autre exemple frappant est la hiérarchie des données. Parce que le développement d'applications est progressif et on ne sait pas toujours ce qui sera nécessaire à l'avenir, la même hiérarchie de sens peut être stockée dans différentes tables. Et tandis que l'acquisition à la volée est activement utilisée dans OLAP, ce sont des choses légèrement différentes. - Complexité excessive des requêtes.
Parce que Un schéma OLTP diffère d'un schéma OLAP. Une couche logicielle fortement liée est nécessaire pour amener le schéma de données OLTP à la bonne forme. - Complexité du support, du débogage et du développement.
En général, nous pouvons dire que plus la base de code est complexe, plus il est difficile de la maintenir dans un état sain. Ceci est un axiome. - La complexité de la couverture du test.
De nombreuses copies sont cassées en raison des discussions sur la façon d'obtenir une base de données pleine de tous les scripts de test, mais il vaut mieux dire que le fait d'avoir un schéma de données plus simple que la tâche de couvrir avec des tests est simplifié plusieurs fois. - Débogage des performances sans fin.
Il y a une forte probabilité que l'utilisateur commande un rapport «lourd» pour le serveur de base de données. Cette probabilité augmente avec le temps. Il convient de noter que OLAP est également sujet à ce problème, mais contrairement à OLTP, la ressource OLAP dans ce domaine est beaucoup plus élevée.
ColumnstoreCet article se concentrera sur le format de stockage columnstore, mais sans détails de bas niveau. Les autres formats mentionnés ci-dessus méritent également l'attention, mais c'est un sujet pour un autre article.
En fait, le format columnstore est connu depuis 30 ans, mais il n'a été implémenté dans le SGBDR que récemment. L'essence de columnstore est que les données ne sont pas stockées dans des lignes, mais dans des colonnes. C'est-à-dire sur une page (tous connus 8 Ko), le serveur enregistre les données d'un seul champ. Et donc avec chaque champ de la table à son tour. Ceci est nécessaire pour que vous n'ayez pas à lire d'informations supplémentaires. Imaginons une table avec 10 champs et une requête qui n'a qu'un seul champ spécifié dans l'instruction SELECT. S'il s'agissait d'une table régulière enregistrée dans un format basé sur des lignes, le serveur serait obligé de lire les 10 champs, mais n'en retournerait qu'un. Il s'est avéré que le serveur lisait 9 fois plus d'informations que nécessaire. Columnstore résout complètement ce problème, car le format de stockage vous permet de lire un seul champ ordonné. Tout cela se produit parce que l'unité de stockage dans un SGBDR est une page. C'est-à-dire le serveur écrit et lit toujours au moins une page. La seule question est de savoir combien de champs y sont présents.
Comment Columnstore peut vraiment aider
Pour répondre à cela, il faut avoir des chiffres exacts. Attrapons-les. Mais quels chiffres peuvent donner une image précise?
- La quantité d'espace disque.
- Performances des requêtes.
- Tolérance aux pannes.
- Facilité de mise en œuvre.
- Quelles nouvelles compétences un développeur doit-il avoir pour travailler avec de nouvelles structures.
Espace disque
Créons un tableau simple, remplissons-le de données et vérifions combien d'espace cela prend.
create foreign table cstore_table ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz');
Comme vous l'avez remarqué, j'ai créé une table externe. Le fait est que PostgreSQL n'a pas de support de colonne intégré. Mais PostgreSQL dispose d'un puissant système d'extensions. L'un d'eux permet de créer des tables columnstore. Liens à la fin de l'article.
- pglz - indique à l'extension que les données doivent être compressées à l'aide de l'algorithme intégré dans PostgreSQL;
- trd - temps de transaction;
- op, it, wh - coupes ou mesures analytiques;
- m1, m2, m3, m4, m5 - indicateurs ou mesures numériques;
Insérons une quantité «décente» de données et voyons combien d'espace il faut sur le disque. Dans le même temps, nous vérifions les performances de l'insert. Parce que Je mets mes expériences sur un ordinateur portable, je suis un peu organique dans la quantité de données. De plus, ce qui est encore bon, j'utiliserai le disque dur exécutant le système d'exploitation invité Fedora 30. Hôte OS - Windows 10 Home Edition. Processeur Intel Core 7. Le SE invité a reçu 4 Go de RAM. Version PostgreSQL - PostgreSQL 10.10 sur x86_64-pc-linux-gnu, compilé par gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64 bits. J'expérimenterai un ensemble de données avec le nombre d'enregistrements 52 608 000.
explain (analyze) insert into cstore_table select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;
Le plan de mise en œuvre sera le suivant
Insérer sur cstore_table (coût = 0,01..24902714242540.01 lignes = 1000000000000000 largeur = 150) (temps réel = 119560.456..119560.456 lignes = 0 boucles = 1)
----> Boucle imbriquée (coût = 0,01..24902714242540,01 lignes = 1000000000000000 largeur = 150) (temps réel = 1,823..22339,976 lignes = 52608000 boucles = 1)
----------> Fonction Scan sur generate_series d (coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 0,151..2,198 lignes = 1096 boucles = 1)
----------> Materialise (coût = 0,01..27284555030.01 lignes = 1000000000000 largeur = 16) (temps réel = 0,002..3,196 lignes = 48000 boucles = 1096)
----------------> Boucle imbriquée (coût = 0,01..17401742530,01 lignes = 1000000000000 largeur = 16) (temps réel = 1,461..15,072 lignes = 48000 boucles = 1)
----------------------> Scan de fonction sur generate_series it (coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 1,159..2,007 lignes = 4000 boucles = 1)
----------------------> Matérialiser (coût = 0,01..26312333,01 lignes = 10000000000 largeur = 12) (temps réel = 0,000..0,001 lignes = 12 boucles = 4000)
----------------------------> Boucle imbriquée (coût = 0,01 à 16429520,01 lignes = 1 000 000 000 de largeur = 12) (temps réel = 0,257 .0.485 lignes = 12 boucles = 1)
----------------------------------> Analyse des fonctions sur generate_series wh (coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 0,046 à 0,049 lignes = 3 boucles = 1)
----------------------------------> Matérialiser (coût = 0,01..28917,01 lignes = 1000000 largeur = 8) (temps réel = 0,070..0,139 lignes = 4 boucles = 3)
---------------------------------------> Boucle imbriquée (coût = 0,01..20010.01 lignes = 1000000 largeur = 8) (temps réel = 0,173 à 0,366 rangées = 4 boucles = 1)
-------------------------------------------> Scan de fonction sur l'opérande generate_series ( coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 0,076..0,079 lignes = 2 boucles = 1)
---------------------------------------------> Scan de fonction sur generate_series org (coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 0,043..0,047 lignes = 2 boucles = 2)
Temps de planification: 0,439 ms
Temps d'exécution: 119692,051 ms
Délai total - 1,994867517 minutes
Temps de création de l'ensemble de données - 22,339976 secondes
Temps d'insertion - 1,620341333 minutes
Je n'ai pas réussi à évaluer l'espace disque occupé à l'aide des fonctions PostgreSQL. Je ne sais pas pourquoi, mais en affichant 0. C'est peut-être le comportement standard pour les tables externes. Utilisé pour ce gestionnaire de fichiers. Ainsi, le volume d'espace disque occupé est de 226,2 Mo. Pour évaluer beaucoup ou un peu, comparons-le avec un tableau régulier.
explain (analyze) create table rbstore_table as select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 1095) as d;
Le plan de mise en œuvre sera le suivant
Boucle imbriquée (coût = 0,01..22402714242540,01 lignes = 1000000000000000 largeur = 44) (temps réel = 0,585..23781.942 lignes = 52608000 boucles = 1)
---> Analyse des fonctions sur generate_series d (coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 0,091..2,130 lignes = 1096 boucles = 1)
---> Matérialiser (coût = 0,01..27284555030,01 lignes = 1000000000000 largeur = 16) (temps réel = 0,001..3,574 lignes = 48000 boucles = 1096)
----------> Boucle imbriquée (coût = 0,01..17401742530,01 lignes = 1000000000000 largeur = 16) (temps réel = 0,489..14,044 lignes = 48000 boucles = 1)
----------------> Scan de fonction sur generate_series it (coût = 0,00..10,00 lignes = 1000 largeur = 4) (temps réel = 0,477..1,352 lignes = 4000 boucles = 1 )
----------------> Matérialiser (coût = 0,01..26312333,01 lignes = 1000000000 largeur = 12) (temps réel = 0,000..0,001 lignes = 12 boucles = 4000)
----------------------> Boucle imbriquée (coût = 0,01 à 16429520,01 lignes = 1 000 000 000 de largeur = 12) (temps réel = 0,010 à 0,019 lignes = 12 boucles = 1)
----------------------------> Fonction Scan sur generate_series wh (coût = 0,00..10,00 lignes = 1000 largeur = 4) (réel temps = 0,003..0,003 lignes = 3 boucles = 1)
----------------------------> Matérialiser (coût = 0,01..28917,01 lignes = 1000000 largeur = 8) (temps réel = 0,002. .0.004 lignes = 4 boucles = 3)
----------------------------------> Boucle imbriquée (coût = 0,01..20010,01 lignes = 1000000 largeur = 8 ) (temps réel = 0,006..0,009 lignes = 4 boucles = 1)
----------------------------------------> Scan de fonction sur l'oper generate_series (coût = 0,00 ..10.00 lignes = 1000 largeur = 4) (temps réel = 0,002..0,002 lignes = 2 boucles = 1)
----------------------------------------> Scan de fonction sur org generate_series (coût = 0,00 ..10.00 lignes = 1000 largeur = 4) (temps réel = 0,001..0,001 lignes = 2 boucles = 2)
Temps de planification: 0,569 ms
Temps d'exécution: 378883,989 ms
Le temps consacré à la mise en œuvre de ce plan ne nous intéresse pas, car dans la vraie vie, de tels inserts ne sont pas supposés. Nous voulons savoir combien d'espace disque cette table occupe. Ayant répondu à la demande de fonctions système, j'ai reçu 3,75 Go.
Donc, cstore_table - 226 Mo, rbstore_table - 3,75 Go. La différence de 16,99 fois est frappante, mais il est peu probable que la même différence puisse être obtenue en production, principalement en raison de la distribution des données. En règle générale, cette différence sera moindre et sera environ 5 fois.
Mais attendez, personne n'utilise de données brutes dans un format basé sur des lignes à des fins d'analyse. Par exemple, ils essaient d'utiliser des données indexées pour les rapports. Et parce que Les données "brutes" seront toujours, vous devez comparer les tailles avec les tailles des indices. Créons au moins un index. Soit un index sur le champ date et le type d'opération - trd + op.
Donc, je n'ai indexé que deux champs, et l'index a pris 1583 Mo, ce qui est beaucoup plus que le cstore_table. Mais, en règle générale, plusieurs index sont requis pour la charge OLAP. Il conviendra de noter ici que le cstore_table n'a pas besoin d'indexation supplémentaire. Cette table agit comme un index couvrant toutes les requêtes.
De tout ce qui précède, une conclusion simple peut être tirée - en utilisant des tables columnstore, vous pouvez réduire la quantité d'espace disque utilisé.
Performances des requêtes
Pour évaluer les performances, exécutons une requête qui renvoie des données récapitulatives pour un mois spécifique pour un type d'opération spécifique.
explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd = '2011-01-01' and op = 1;
Le plan de mise en œuvre sera le suivant
Agrégat (coût = 793602,69..793602,70 lignes = 1 largeur = 32) (temps réel = 79,708..79,708 lignes = 1 boucles = 1)
--Buffers: hit partagé = 44226
---> Analyse étrangère sur cstore_table (coût = 0,00..793544,70 lignes = 23197 largeur = 5) (temps réel = 23,209..76,628 lignes = 24000 boucles = 1)
-------- Filtre: ((trd = '2011-01-01' :: date) AND (op = 1))
-------- Lignes supprimées par le filtre: 26000
-------- Fichier CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16417
-------- Taille du fichier CStore: 120818897
-------- Tampons: hit partagé = 44226
Temps de planification: 0,165 ms
Temps d'exécution: 79,887 ms
Et
explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd = '2011-01-01' and op = 1;
Le plan de mise en œuvre sera le suivant
Agrégat (coût = 40053.80..40053.81 lignes = 1 largeur = 8) (temps réel = 389.183..389.183 lignes = 1 boucles = 1)
--Buffers: lecture partagée = 545
---> Index Scan utilisant trd_op_ix sur rbstore_table (coût = 0,56..39996,70 lignes = 22841 largeur = 4) (temps réel = 55,955..385,283 lignes = 24000 boucles = 1)
-------- Index Cond: ((trd = '2011-01-01 00:00:00' :: horodatage sans fuseau horaire) ET (op = 1))
-------- Tampons: lecture partagée = 545
Temps de planification: 112,175 ms
Temps d'exécution: 389,219 ms
389,219 ms contre 79,887 ms. Ici, nous voyons que même sur une quantité relativement petite de données columnstore, une table est beaucoup plus rapide qu'un index sur une table basée sur des lignes.
Modifions la demande et essayons d'obtenir l'unité pour toute l'année 2011.
explain (analyze, costs, buffers) select sum(m1) from cstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1;
Le plan de mise en œuvre sera le suivant
Agrégat (coût = 946625.58..946625.59 lignes = 1 largeur = 32) (temps réel = 3123.604..3123.604 lignes = 1 boucles = 1)
--Buffers: hit partagé = 44226
---> Analyse étrangère sur cstore_table (coût = 0,00..925064,70 lignes = 8624349 largeur = 5) (temps réel = 21,728..2100,665 lignes = 8760000 boucles = 1)
-------- Filtre: ((trd> = '2011-01-01' :: date) AND (trd <= '2011-12-31' :: date) AND (op = 1))
-------- Lignes supprimées par le filtre: 8760000
-------- Fichier CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16411
-------- Taille du fichier CStore: 120818897
-------- Tampons: hit partagé = 44226
Temps de planification: 0,212 ms
Temps d'exécution: 3123,960 ms
Et
explain (analyze, costs, buffers) select sum(m1) from rbstore_table where trd between '2011-01-01' and '2011-12-31' and op = 1;
Le plan de mise en œuvre sera le suivant
Finaliser l'agrégat (coût = 885214.33..885214.34 lignes = 1 largeur = 8) (temps réel = 98512.560..98512.560 lignes = 1 boucles = 1)
--Buffers: hit partagé = 2565 lu = 489099
---> Rassembler (coût = 885214.12..885214.33 lignes = 2 largeur = 8) (temps réel = 98427.034..98523.194 lignes = 3 boucles = 1)
-------- Travailleurs prévus: 2
-------- Lancement des travailleurs: 2
-------- Tampons: hit partagé = 2565 lu = 489099
---------> Agrégat partiel (coût = 884214.12..884214.13 lignes = 1 largeur = 8) (temps réel = 97907.608..97907.608 lignes = 1 boucles = 3)
-------------- Tampons: hit partagé = 2565 lu = 489099
---------------> Scan Seq parallèle sur rbstore_table (coût = 0,00..875264,00 lignes = 3580047 largeur = 4) (temps réel = 40820,004..97405,250 lignes = 2920000 boucles = 3)
--------------------- Filtre: ((trd> = '2011-01-01 00:00:00' :: horodatage sans fuseau horaire) ET (trd <= '2011-12-31 00:00:00' :: horodatage sans fuseau horaire) ET (op = 1))
-------------------- Lignes supprimées par le filtre: 14616000
-------------------- Tampons: hit partagé = 2565 lu = 489099
Temps de planification: 7,899 ms
Temps d'exécution: 98523,278 ms
98523,278 ms contre 3123,960 ms. Un index partiel pourrait peut-être nous aider, mais il vaut mieux ne pas le risquer et créer une structure basée sur les lignes appropriée dans laquelle les valeurs prédéfinies seront stockées.
Agrégats manuels
Une structure appropriée pour les agrégats manuels pourrait être une table régulière basée sur des lignes contenant des valeurs précalculées. Par exemple, il peut contenir un enregistrement relatif à 2011 avec le type d'opération égal à 1, tandis que dans les champs m1, m2, m3, m4 et m5 la valeur agrégée sera stockée précisément pour ces sections analytiques. Ainsi, disposant d'un ensemble suffisant d'agrégats et d'indices, les requêtes analytiques acquièrent des performances sans précédent. Fait intéressant, Microsoft SQL Server Analysis Services dispose d'un assistant spécial qui vous permet de configurer le nombre et la profondeur des valeurs précalculées.
Cette solution présente les avantages suivants:
- Analyses en temps réel.
Veuillez ne pas confondre le terme «analytique en temps réel». Ici, nous parlons du fait que l'augmentation de l'unité se produit sur une période de temps acceptable dans la grande majorité des cas.
En fait, ce plus est controversé, mais n'en parlons pas. Le fait demeure. L'architecture de la solution est telle que les unités restent «fraîches» presque toujours. - Indépendance totale vis-à-vis du volume de données.
C'est un plus très sérieux. Quelle que soit la quantité de données traitées, tôt ou tard, elles seront traitées et les agrégats reçus. - Complexité relative.
Pour obtenir des analyses en temps réel et une indépendance du volume de données, la solution doit utiliser des technologies avancées telles que le multithreading et la gestion manuelle des verrous au niveau du SGBD. - Test de difficulté.
Ici, nous parlons à la fois de tests unitaires et de tests manuels. Je pense que le lecteur ne devrait pas expliquer que l'identification des erreurs de multithreading n'est pas une tâche facile. - Augmentation de l'espace disque requis.
L'utilisation réelle de columnstore
Ici, nous devons à nouveau plonger dans la théorie et analyser plus en détail la question de savoir ce que sont les données analytiques.
Prenez le chef d'entreprise moyen. En règle générale, il / elle s'inquiète de deux questions globales: "Comment ça se passe en ce moment?" et "Qu'est-ce qui a changé récemment?".
Pour répondre à la question «Comment vont les choses en ce moment», nous n'avons absolument pas besoin de données historiques. C'est-à-dire peu importe comment les choses se sont passées il y a un mois.
Afin de se tenir au courant du pouls, la question est souvent posée. Ce type d'analyse de données est dit opérationnel.
Pour répondre à la question «Ce qui a changé récemment», nous avons besoin de données historiques précises. De plus, en règle générale, l'analyse est effectuée aux mêmes intervalles de temps. Par exemple, un mois est comparé à un mois, d'une année à l'autre, etc. Bien sûr, le système ne doit pas limiter l'utilisateur à la possibilité de comparer des périodes arbitraires, mais un tel cas doit être reconnu comme rare, car comparer une année fermée avec une moitié non clôturée n'a pas de sens. Une caractéristique distinctive de l'analyse comparative est qu'elle n'est pas requise aussi souvent qu'elle est opérationnelle. Nous appellerons ce type d'analyse historique.
De toute évidence, l'analyse opérationnelle devrait se produire rapidement. En conséquence, il impose des exigences élevées en matière de performances. Alors que pour l'analyse historique, de telles exigences ne peuvent pas être avancées. Bien que la performance de l'analyse historique devrait rester à un niveau très élevé. Au moins pour que le système d'analyse lui-même reste compétitif.
Ainsi, selon deux types d'analyses, on peut distinguer deux types de données analytiques: les données opérationnelles et historiques. Du côté de l'utilisateur, il ne devrait pas être visible avec quelles données particulières il travaille en ce moment.
C'est à partir de ces considérations que dans les serveurs de bases de données, la possibilité est apparue de diviser les tables en sections distinctes.
En ce qui concerne le magasin de colonnes, il est possible de mélanger des sections dans des formats basés sur des lignes et des magasins de colonnes. Il est connu que les données d'analyse opérationnelle sont sujettes à de fréquentes modifications, ce qui empêche leur stockage au format columnstore. Et étant donné que les données opérationnelles ne se produisent pas trop, elles peuvent être stockées sous forme de lignes.
Les données historiques ne changent pas. Il y a beaucoup de ces données, et donc le format columnstore leur convient mieux. Rappelez-vous que les performances des requêtes en gras sur une source columnstore sont supérieures à celles d'une source basée sur des lignes.
Regardons un exemple de tout ce qui précède.
Ci-dessous, je crée la table principale de l'entrepôt et y attache les sections d'analyse opérationnelle et historique.
create table warehouse ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) partition by range(trd); create foreign table historycal_data ( trd date, org int, op int, it int, wh int, m1 numeric(32, 2), m2 numeric(32, 2), m3 numeric(32, 2), m4 numeric(32, 2), m5 numeric(32, 2) ) server cstore_server options(compression 'pglz'); insert into historycal_data select '2010-01-01'::date + make_interval(days => d) as trd , op , org , wh , it , 100 as m1 , 100 as m2 , 100 as m3 , 100 as m4 , 100 as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, (1095 - 31)) as d; analyze historycal_data; create table operational_data as select ('2012-12-01'::date + make_interval(days => d))::date as trd , op , org , wh , it , 100::numeric(32, 2) as m1 , 100::numeric(32, 2) as m2 , 100::numeric(32, 2) as m3 , 100::numeric(32, 2) as m4 , 100::numeric(32, 2) as m5 from generate_series(0, 1) as op cross join generate_series(1, 2) as org cross join generate_series(1, 3) as wh cross join generate_series(1, 4000) as it cross join generate_series(0, 30) as d; create index trd_op_ix on operational_data (trd, op); analyze operational_data; alter table warehouse attach partition operational_data for values from ('2012-12-01') to ('2112-01-01'); alter table warehouse attach partition historycal_data for values from ('2010-01-01') to ('2012-12-01');
Tout est prêt. Essayons de commander quelques rapports. Commençons par commander les données pour un jour du mois en cours.
explain (analyze, costs, buffers) select sum(m1) from warehouse where trd = '2012-12-01' and op = 1;
Agrégat (coût = 15203,37..15203,38 lignes = 1 largeur = 32) (temps réel = 17,320..17,320 lignes = 1 boucles = 1)
--Buffers: hit partagé = 3 lecture = 515
---> Ajouter (coût = 532,59..15140,89 lignes = 24991 largeur = 5) (temps réel = 1,924..13,838 lignes = 24000 boucles = 1)
------- Tampons: hit partagé = 3 lecture = 515
---------> Bitmap Heap Scan sur operation_data (coût = 532,59..15140,89 lignes = 24991 largeur = 5) (temps réel = 1,924..11,992 lignes = 24000 boucles = 1)
--------------- Vérifiez à nouveau Cond: ((trd = '2012-12-01' :: date) AND (op = 1))
--------------- Blocs de tas: exact = 449
--------------- Tampons: hit partagé = 3 lecture = 515
----------------> Scan d'index bitmap sur trd_op_ix (coût = 0,00..526,34 lignes = 24991 largeur = 0) (temps réel = 1,877..1,877 lignes = 24000 boucles = 1 )
--------------------- Index Cond: ((trd = '2012-12-01' :: date) AND (op = 1))
--------------------- Tampons: hit partagé = 2 lecture = 67
Temps de planification: 0,388 ms
Temps d'exécution: 100,941 ms
Nous allons maintenant commander des données pour l'ensemble de 2012, dans lesquelles le nombre de transactions est de 8 784 000.
explain (analyze, costs, buffers) select sum(m1) from warehouse where trd between '2012-01-01' and '2012-12-31' and op = 1;
Agrégat (coût = 960685.82..960685.83 lignes = 1 largeur = 32) (temps réel = 4124.681..4124.681 lignes = 1 boucles = 1)
--Buffers: hit partagé = 45591 lu = 11282
---> Ajouter (coût = 0,00..938846,60 lignes = 8735687 largeur = 5) (temps réel = 66,581..3036,394 lignes = 8784000 boucles = 1)
--------- Tampons: hit partagé = 45591 lu = 11282
----------> Analyse étrangère sur historycal_data (coût = 0,00..898899,60 lignes = 7994117 largeur = 5) (temps réel = 66,579..2193.801 lignes = 8040000 boucles = 1)
--------------- Filtre: ((trd> = '2012-01-01' :: date) AND (trd <= '2012-12-31' :: date) AND (op = 1))
--------------- Lignes supprimées par le filtre: 8040000
--------------- Fichier CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- Taille du fichier CStore: 117401470
--------------- Tampons: hit partagé = 42966
----------> Seq Scan sur données_opérationnelles (coût = 0,00..39947,00 lignes = 741570 largeur = 5) (temps réel = 0,019..284,824 lignes = 744000 boucles = 1)
--------------- Filtre: ((trd> = '2012-01-01' :: date) AND (trd <= '2012-12-31' :: date) AND (op = 1))
--------------- Lignes supprimées par le filtre: 744000
--------------- Tampons: hit partagé = 2625 lu = 11282
Temps de planification: 0,256 ms
Temps d'exécution: 4125,239 ms
Au final, voyons ce qui se passe si l'utilisateur veut, par exemple, sans intention malveillante, ordonner un rapport sur toutes les transactions du système, dont il y a 52 608 000.
explain (analyze, costs, buffers) select sum(m1) from warehouse
Agrégat (coût = 672940.20..672940.21 lignes = 1 largeur = 32) (temps réel = 15907.886..15907.886 lignes = 1 boucles = 1)
--Buffers: hit partagé = 17075 lu = 11154
---> Ajouter (coût = 0,00 à 541420,20 lignes = 52608000 largeur = 5) (temps réel = 0,192 à 9115,144 lignes = 52608000 boucles = 1)
--------- Tampons: hit partagé = 17075 lu = 11154
----------> Analyse étrangère sur historycal_data (coût = 0,00..512633,20 lignes = 51120000 largeur = 5) (temps réel = 0,191..5376,449 lignes = 51120000 boucles = 1)
--------------- Fichier CStore: / var / lib / pgsql / 10 / data / cstore_fdw / 14028/16448
--------------- Taille du fichier CStore: 117401470
--------------- Tampons: hit partagé = 14322
----------> Seq Scan sur données_opérationnelles (coût = 0,00..28787,00 lignes = 1488000 largeur = 5) (temps réel = 0,032..246,978 lignes = 1488000 boucles = 1)
--------------- Tampons: hit partagé = 2753 lu = 11154
Temps de planification: 0,157 ms
Temps d'exécution: 15908,096 ms
Veuillez noter que j'écris toujours mon article, comme si de rien n'était. Je n'ai même pas eu à redémarrer mon ordinateur portable pas si puissant avec disque dur et 4 Go de RAM. Bien que la question de la consommation des ressources nécessite une étude plus approfondie.
Tolérance aux pannes
En partie, la tolérance aux pannes a été testée au moment d'écrire ces lignes. Mon ordinateur portable est vivant, et, en général, je n'ai remarqué aucun ralentissement dans son travail, à part les habituels.
Que le lecteur me pardonne le fait que je n'ai pas eu le temps de régler en détail la question de la tolérance aux pannes, mais je peux dire que l'extension en question a une tolérance aux pannes - une sauvegarde est possible.
Facilité de mise en œuvre
Il s'est avéré que lors de la création d'une table qui stocke des données dans un format columnstore, il n'y a pas d'options autres qu'un algorithme de compression. La compression elle-même est absolument nécessaire.
Le format lui-même a une certaine structure. En définissant les paramètres appropriés, vous pouvez obtenir une certaine accélération des requêtes analytiques ou ajuster le degré de compression des informations.
Comme démontré ci-dessus, la création d'une table columnstore n'est pas un problème du tout. L'extension peut fonctionner avec 40 types de données PostgreSQL. Les webinaires ont parlé de tous les types pris en charge par PostgreSQL.
Quelles nouvelles compétences un développeur doit-il avoir pour travailler avec de nouvelles structures
Le développeur SQL n'a pas besoin de compétences particulières pour écrire des requêtes dans des tables columnstore. Une telle table est visible dans toutes les requêtes, comme une table régulière basée sur des lignes. Bien que cela n'exclut pas la nécessité d'optimiser les requêtes.
Conclusion
Dans cet article, j'ai montré comment une table avec un format de stockage columnstore peut être utile. Cela économise de l'espace disque et des requêtes analytiques hautes performances. La facilité de travail avec la table réduit automatiquement le coût de création d'un entrepôt de données analytiques à part entière, car son utilisation ne nécessite pas le développement d'algorithmes complexes et difficiles à déboguer. Les tests sont simplifiés.
Malgré le fait que les expériences présentées ci-dessus inspirent l'optimisme, de nombreux problèmes n'ont pas été résolus. Par exemple, quel plan de requête sera généré lorsque la table columnstore rejoint d'autres tables. J'espère continuer ce travail dans la prochaine partie. Le nombre de pièces dépendra du comportement de cstore_fdw sur des données plus ou moins réelles.
Liens vers des documents supplémentaires
Brève revue cstore_fdwcstore_fdw sur githubFeuille de route cstore_fdw