Graphiques, rapports et analyses - tout cela est en quelque sorte présent dans le back-office de toute entreprise, même très petite. Lorsqu'elles sont encombrées dans des tableaux réguliers dans Excel / Numbers / Libre, mais que les données ne sont toujours pas très volumineuses, les solutions traditionnelles pour les besoins internes de l'entreprise sont souvent construites à l'aide de bases de données relationnelles telles que PostgreSQL, MySQL ou MariaDB.
Ces bases de données sont gratuites, grâce à SQL, elles peuvent facilement s'intégrer à d'autres composants du système, elles sont populaires et la plupart des développeurs et des analystes peuvent travailler avec elles. La charge (trafic et volumes) qu'ils peuvent digérer est suffisamment volumineuse pour tenir facilement jusqu'à ce que l'entreprise puisse se permettre des solutions plus complexes (et coûteuses) pour les analyses et les rapports.
Position de départ
Cependant, même dans une technologie qui a été étudiée à plusieurs reprises, il y a toujours différentes nuances qui peuvent soudainement ajouter aux soucis des ingénieurs. En plus de la fiabilité, le problème le plus fréquemment mentionné avec les bases de données est leur performance. De toute évidence, avec une augmentation de la quantité de données, le taux de réponse DB diminue, mais si cela se produit de manière prévisible et est cohérent avec l'augmentation de la charge, alors ce n'est pas si mal. Vous pouvez toujours voir à l'avance quand la base de données commence à exiger de l'attention et planifier une mise à niveau ou une transition vers une base de données fondamentalement différente. Bien pire si les performances de la base de données se dégradent de manière imprévisible.
Le sujet de l'amélioration des performances des bases de données est aussi ancien que le monde et très vaste, et dans cet article, je voudrais me concentrer sur une seule direction. À savoir, sur l'évaluation de l'efficacité des plans de requête dans une base de données PostgreSQL, ainsi que sur la modification de cette efficacité au fil du temps pour rendre le comportement du planificateur de base de données plus prévisible.
Malgré le fait que bon nombre des éléments qui seront discutés s'appliquent à toutes les versions récentes de cette base de données, les exemples ci-dessous correspondent à la version 11.2, cette dernière pour le moment.
Avant de plonger dans les détails, il est logique de faire une digression et de dire quelques mots sur l'origine des problèmes de performances dans les bases de données relationnelles. De quoi s'occupe exactement la base de données lorsqu'elle "ralentit"? Le manque de mémoire (un grand nombre d'accès au disque ou au réseau), un processeur faible, ce sont tous des problèmes évidents avec des solutions claires, mais quoi d'autre peut affecter la vitesse d'exécution des requêtes?
Rafraîchissez vos souvenirs
Pour que la base de données réponde à la requête SQL, elle doit créer un plan de requête (dans quelles tables et colonnes pour voir quels index sont nécessaires, quoi choisir à partir de là, avec quoi comparer, combien de mémoire est requise, etc.). Ce plan est formé sous la forme d'un arbre, dont les nœuds ne sont que quelques opérations typiques, avec une complexité de calcul différente. En voici quelques-unes, par exemple (N est le nombre de lignes avec lesquelles effectuer l'opération):
| Fonctionnement | Que fait-on | Coût |
|---|
| SELECT ... WHERE ... opérations de récupération de données |
| Scan Seq | Nous chargeons chaque ligne du tableau et vérifions l'état. | O (N) |
Balayage d'index
(indice b-tree) | Les données sont directement dans l'index, nous recherchons donc par condition les éléments nécessaires de l'index et prenons les données à partir de là. | O (log (N)), recherche un élément dans une arborescence triée. |
Balayage d'index
(indice de hachage) | Les données sont directement dans l'index, nous recherchons donc par condition les éléments nécessaires de l'index et prenons les données à partir de là. | O (1), recherche d'un élément dans une table de hachage, à l'exclusion du coût de création de hachages |
| Analyse de tas de bitmap | Nous sélectionnons les numéros des lignes nécessaires par index, puis nous chargeons uniquement les lignes nécessaires et effectuons avec eux des contrôles supplémentaires. | Scan Index + Scan Seq (M),
Où M est le nombre de lignes trouvées après le balayage d'index. On suppose que M << N, c'est-à-dire index est plus utile que Seq Scan. |
| Opérations de jointure (JOIN, SELECT à partir de plusieurs tables) |
| Boucle imbriquée | Pour chaque ligne du tableau de gauche, recherchez une ligne appropriée dans le tableau de droite. | O (N 2 ).
Mais si l'un des tableaux est beaucoup plus petit que l'autre (dictionnaire) et ne croît pratiquement pas avec le temps, alors le coût réel peut diminuer jusqu'à O (N). |
| Hash Join | Pour chaque ligne des tableaux de gauche et de droite, nous considérons le hachage, ce qui réduit le nombre de recherches d'options de connexion possibles. | O (N), mais dans le cas d'une fonction de hachage très inefficace ou d'un grand nombre de champs identiques pour la connexion, il peut y avoir O (N 2 ) |
| Fusionner la jointure | Par condition, nous trions les tableaux de gauche et de droite, après quoi nous combinons les deux listes triées | O (N * log (N))
Coûts de tri + parcourir la liste. |
| Opérations d'agrégation (GROUP BY, DISTINCT) |
| Agrégat de groupe | Nous trions la table en fonction de la condition d'agrégation, puis dans la liste triée, nous groupons les lignes adjacentes. | O (N * log (N)) |
| Agrégat de hachage | Nous considérons le hachage pour la condition d'agrégation pour chaque ligne. Pour les lignes avec le même hachage, nous effectuons une agrégation. | O (N) |
Comme vous pouvez le voir, le coût d'une requête dépend beaucoup de la façon dont les données sont situées dans les tables et de la façon dont cet ordre correspond aux opérations de hachage utilisées. La boucle imbriquée, malgré son coût en O (N
2 ), peut être plus rentable que Hash Join ou Merge Join lorsque l'une des tables jointes dégénère en une ou plusieurs lignes.
En plus des ressources CPU, le coût comprend également l'utilisation de la mémoire. Les deux étant des ressources limitées, le planificateur de requêtes doit trouver un compromis. Si deux tables sont mathématiquement plus rentables à joindre via une jointure par hachage, mais qu'il n'y a tout simplement pas de place en mémoire pour une si grande table de hachage, la base de données peut être forcée d'utiliser la fusion par jointure, par exemple. Une boucle imbriquée "lente" ne nécessite généralement pas de mémoire supplémentaire et est prête à produire des résultats juste après le lancement.
Le coût relatif de ces opérations apparaît plus clairement sur le graphique. Ce ne sont pas des nombres absolus, juste un rapport approximatif de différentes opérations.
Le graphique de boucle imbriquée "commence" ci-dessous, car il ne nécessite pas de calculs supplémentaires, d'allocation de mémoire ou de copie de données intermédiaires, mais il a un coût O (N
2 ). Merge Join et Hash Join ont des coûts initiaux plus élevés, cependant, après quelques valeurs N, ils commencent à battre Nested Loop à temps. L'ordonnanceur essaie de choisir le plan avec le coût le plus bas et sur le graphique ci-dessus adhère à différentes opérations avec différents N (flèche verte en pointillés). Avec le nombre de lignes jusqu'à N1, il est plus rentable d'utiliser la boucle imbriquée, de N1 à N2, il est plus rentable de fusionner la jointure, puis après N2, il devient plus rentable pour Hash Join, mais Hash Join nécessite de la mémoire pour créer des tables de hachage. Et lorsque vous atteignez N3, cette mémoire devient insuffisante, ce qui conduit à l'utilisation forcée de Fusionner Join.
Lors du choix d'un plan, le planificateur estime le coût de chaque opération dans le plan en utilisant un ensemble de coûts relatifs de certaines opérations «atomiques» dans la base de données. Comme, par exemple, des calculs, des comparaisons, le chargement d'une page en mémoire, etc. Voici une liste de certains de ces paramètres de la configuration par défaut, il n'y en a pas beaucoup:
| Coût relatif constant | Valeur par défaut |
|---|
| seq_page_cost | 1.0 |
| random_page_cost | 4,0 |
| cpu_tuple_cost | 0,01 |
| cpu_index_tuple_cost | 0,005 |
| cpu_operator_cost | 0,0025 |
| parallel_tuple_cost | 0,1 |
| parallel_setup_cost | 1000,0 |
Certes, ces constantes seules sont peu nombreuses, vous devez toujours connaître le «N», c'est-à-dire exactement combien de lignes des résultats précédents devront être traitées dans chacune de ces opérations. La limite supérieure est évidente ici - la base de données "sait" combien de données se trouve dans n'importe quelle table et peut toujours calculer "au maximum". Par exemple, si vous avez deux tables de 100 lignes chacune, les joindre peut produire de 0 à 10 000 lignes dans la sortie. En conséquence, la prochaine opération d'entrée peut avoir jusqu'à 10 000 lignes.
Mais si vous connaissez au moins un peu la nature des données des tableaux, ce nombre de lignes peut être prédit avec plus de précision. Par exemple, pour deux tables de 100 lignes de l'exemple ci-dessus, si vous savez à l'avance que la jointure ne produira pas 10 000 lignes, mais les 100 mêmes, le coût estimé de la prochaine opération est considérablement réduit. Dans ce cas, ce plan pourrait être plus efficace que d'autres.
Optimisation prête à l'emploi
Pour que l'ordonnanceur puisse prédire plus précisément la taille des résultats intermédiaires, PostgreSQL utilise la collecte de statistiques sur les tables, qui est accumulée dans pg_statistic, ou dans sa version plus lisible - dans pg_stats. Il est mis à jour automatiquement au démarrage du vide, ou explicitement avec la commande ANALYSER. Ce tableau stocke une variété d'informations sur les données et le type de nature contenus dans les tableaux. En particulier, histogrammes de valeurs, pourcentage de champs vides et autres informations. Le planificateur utilise tout cela pour prédire avec plus de précision la quantité de données pour chaque opération dans l'arborescence du plan, et ainsi calculer plus précisément le coût des opérations et le plan dans son ensemble.
Prenons par exemple la requête:
SELECT t1.important_value FROM t1 WHERE t1.a > 100
Supposons que l'histogramme des valeurs de la colonne «t1.a» révèle que des valeurs supérieures à 100 se trouvent dans environ 1% des lignes du tableau. Nous pouvons alors prédire qu'un tel échantillon renverra environ un centième de toutes les lignes du tableau «t1».
La base de données vous donne la possibilité de consulter le coût prévu du plan via la commande EXPLAIN et l'heure réelle de son fonctionnement - en utilisant EXPLAIN ANALYZE.
Il semble qu'avec les statistiques automatiques, tout devrait bien se passer maintenant, mais il peut y avoir des difficultés. Il existe un
bon article à ce sujet
de Citus Data , avec un exemple de l'inefficacité des statistiques automatiques et de la collecte de statistiques supplémentaires à l'aide de CREATE STATISTICS (disponible avec PG 10.0).
Ainsi, pour l'ordonnanceur, il existe deux sources d'erreurs dans le calcul des coûts:
- Le coût relatif des opérations primitives (seq_page_cost, cpu_operator_cost, etc.) par défaut peut être très différent de la réalité (coût du processeur 0,01, coût de chargement de la page srq - 1 ou 4 pour le chargement de page aléatoire). Loin du fait que 100 comparaisons seront égales à 1 page de chargement.
- Erreur lors de la prévision du nombre de lignes dans les opérations intermédiaires. Le coût réel de l'opération dans ce cas peut être très différent de la prévision.
Dans les requêtes complexes, l'élaboration et la prévision de tous les plans possibles peuvent prendre beaucoup de temps en soi. Quelle est l'utilité de renvoyer des données en 1 seconde si la base de données ne prévoyait qu'une minute de requête? PostgreSQL dispose d'un optimiseur Geqo pour cette situation; c'est un planificateur qui ne construit pas tous les plans possibles, mais commence par quelques plans aléatoires et complète les meilleurs, en prédisant les moyens de réduire les coûts. Tout cela n'améliore pas non plus la précision des prévisions, bien qu'il accélère la recherche d'au moins un plan plus ou moins optimal.
Plans soudains - concurrents
Si tout se passe bien, votre demande est satisfaite dans les plus brefs délais. À mesure que la quantité de données augmente, la vitesse d'exécution des requêtes dans la base de données augmente progressivement, et après un certain temps, en l'observant, vous pouvez prédire à peu près quand il sera nécessaire d'augmenter la mémoire ou le nombre de cœurs de processeur ou d'étendre le cluster, etc.
Mais nous devons tenir compte du fait que le plan optimal a des concurrents avec des coûts d'exécution proches, ce que nous ne voyons pas. Et si la base de données change soudainement le plan de requête en un autre, cela surprend. C'est bien si la base de données passe à un plan plus efficace. Et sinon? Regardons l'image, par exemple. Il s'agit du coût prévu et en temps réel de la mise en œuvre de deux plans (rouge et vert):
Ici, un plan est affiché en vert et son «concurrent» le plus proche en rouge. La ligne pointillée montre un graphique des coûts projetés, la ligne continue est le temps réel. La flèche grise en pointillés montre la sélection du planificateur.
Supposons qu'un beau vendredi soir, le nombre prévu de lignes dans une opération intermédiaire atteigne N1 et la prévision «rouge» commence à surpasser la prévision «verte». Le planificateur commence à l'utiliser. Le temps d'exécution réel de la requête saute immédiatement (passage d'une ligne continue verte à une ligne rouge), c'est-à-dire que le calendrier de dégradation de la base de données prend la forme d'une étape (ou peut-être d'un «mur»). En pratique, un tel «mur» peut augmenter le temps d'exécution des requêtes d'un ordre de grandeur ou plus.
Il convient de noter que cette situation est probablement plus typique pour le back-office et l'analyse que pour le front-end, car ce dernier est généralement adapté à des requêtes plus simultanées et, par conséquent, utilise des requêtes plus simples dans la base de données, où l'erreur dans les prévisions de plan est moindre. S'il s'agit d'une base de données pour le reporting ou l'analyse, les requêtes peuvent être arbitrairement complexes.
Comment vivre avec ça?
La question se pose: était-il possible d'une manière ou d'une autre de prévoir de tels plans invisibles «sous-marins»? Après tout, le problème n'est pas qu'ils ne sont pas optimaux, mais que le passage à un autre plan peut se produire de manière imprévisible et, selon la loi de la méchanceté, au moment le plus malheureux pour cela.
Malheureusement, vous ne pouvez pas les voir directement, mais vous pouvez rechercher des plans alternatifs en modifiant les poids réels selon lesquels ils sont sélectionnés. Le sens de cette approche est de supprimer de la vue le plan actuel, que l'ordonnanceur considère optimal, de sorte qu'un de ses plus proches concurrents devienne optimal, et ainsi, il pourrait être vu à travers l'équipe EXPLAIN. En vérifiant périodiquement les changements de coûts dans ces «concurrents» et dans le plan principal, vous pouvez évaluer la probabilité que la base de données «passe» bientôt à un autre plan.
En plus de collecter des données sur les prévisions de plans alternatifs, vous pouvez les exécuter et mesurer leurs performances, ce qui donne également une idée du «bien-être» interne de la base de données.
Voyons quels outils nous avons pour de telles expériences.
Tout d'abord, vous pouvez explicitement «interdire» des opérations spécifiques à l'aide de variables de session. Idéalement, ils n'ont pas besoin d'être modifiés dans la configuration et de recharger la base de données, leur valeur ne change que dans la session ouverte en cours et n'affecte pas les autres sessions, vous pouvez donc expérimenter directement avec des données réelles. Voici une liste d'entre eux avec des valeurs par défaut. Presque toutes les opérations sont incluses:
| Opérations utilisées | Valeur par défaut |
|---|
enable_bitmapscan
enable_hashagg
enable_hashjoin
enable_indexscan
enable_indexonlyscan
enable_material
enable_mergejoin
enable_nestloop
enable_parallel_append
enable_seqscan
enable_sort
enable_tidscan
enable_parallel_hash
enable_partition_pruning | sur |
enable_partitionwise_join
enable_partitionwise_aggregate | éteint |
En interdisant ou en autorisant certaines opérations, nous forçons le planificateur à sélectionner d'autres plans que nous pouvons voir avec la même commande EXPLAIN. En fait, l '«interdiction» des opérations n'interdit pas leur utilisation, mais augmente simplement considérablement leur coût. Dans PostgreSQL, chaque opération «interdite» empile automatiquement un coût égal à 10 milliards d'unités conventionnelles. De plus, dans EXPLAIN, le poids total du plan peut s'avérer prohibitif, mais dans le contexte de ces dizaines de milliards, le poids des opérations restantes est clairement visible, car il s'inscrit généralement dans des commandes plus petites.
Deux des opérations suivantes sont particulièrement intéressantes:
- Hash Join. Sa complexité est O (N), mais avec une erreur avec une prévision du montant du résultat, vous ne pouvez pas tenir en mémoire et vous devrez faire Merge Join, avec un coût de O (N * log (N)).
- Boucle imbriquée. Sa complexité est O (N 2 ), par conséquent, l'erreur dans la prévision de taille affecte de manière quadratique la vitesse d'une telle connexion.
Par exemple, prenons quelques chiffres réels à partir de requêtes, dont l'optimisation était engagée dans notre entreprise.
Plan 1. Avec toutes les opérations autorisées, le coût total du plan le plus optimal était de 274962,09 unités.
Plan 2. Avec la boucle imbriquée «interdite», le coût est passé à 40000534153.85. Ces 40 milliards qui constituent l'essentiel du coût représentent 4 fois la boucle imbriquée utilisée, malgré l'interdiction. Et les 534153.85 restants - c'est précisément la prévision du coût de toutes les autres opérations du plan. Il est, comme nous le voyons, environ 2 fois plus élevé que le coût du plan optimal, c'est-à-dire qu'il est assez proche de lui.
Plan 3. Avec le Hash Join «interdit», le coût était de 383253,77. Le plan a vraiment été fait sans utiliser l'opération Hash Join, car nous ne voyons aucun milliard. Son coût est cependant 30% plus élevé que celui de l'optimum, qui est également très proche.
En réalité, les temps d'exécution des requêtes étaient les suivants:
Plan 1 (toutes les opérations autorisées) terminé en ~ 9 minutes.
Plan 2 (avec la boucle imbriquée «interdite») terminé en 1,5 seconde.
Le plan 3 (avec une jointure de hachage «interdite») a été achevé en ~ 5 minutes.
La raison, comme vous pouvez le voir, est la prévision erronée du coût de la boucle imbriquée. En effet, en comparant EXPLAIN avec EXPLAIN ANALYZE, une erreur y est détectée avec la définition de ce N malheureux dans l'opération intermédiaire. Au lieu d'une seule ligne prédite, la boucle imbriquée a rencontré plusieurs milliers de lignes, ce qui a entraîné une augmentation du couple d'exécution de la requête de quelques ordres de grandeur.
Les économies réalisées avec la jointure de hachage «interdite» sont associées au remplacement du hachage par le tri et la fusion de jointure, qui fonctionnaient plus rapidement dans ce cas que la jointure de hachage. A noter que ce plan 2 est en réalité presque deux fois plus rapide que le plan "optimal" 1. Bien qu'il ait été prévu qu'il sera plus lent.
En pratique, si votre demande soudainement (après une mise à niveau de la base de données ou tout simplement) a commencé à s'exécuter beaucoup plus longtemps qu'auparavant, essayez d'abord de refuser la jointure par hachage ou la boucle imbriquée et voyez comment cela affecte la vitesse de la requête. Dans un cas réussi, vous pourrez au moins interdire un nouveau plan non optimal et revenir au précédent rapidement.
Pour ce faire, vous n'avez pas besoin de modifier les fichiers de configuration de PostgreSQL avec un redémarrage de la base de données, il est assez simple dans n'importe quelle console de changer la valeur de la variable souhaitée pour une session ouverte à partir de la base de données. Les sessions restantes ne seront pas affectées, la configuration ne changera que pour votre session actuelle. Par exemple, comme ceci:
SET enable_hashjoin='on'; SET enable_nestloop='off'; SELECT … FROM … ( )
La deuxième façon d'influencer le choix du plan consiste à modifier le poids des opérations de bas niveau. Il n'y a pas de recette universelle ici, mais, par exemple, si vous avez une base de données avec un cache «réchauffé» et que toutes les données sont stockées en mémoire, il est probable que le coût du chargement de page séquentiel ne diffère pas du coût de chargement d'une page aléatoire. Alors que dans la configuration par défaut, random est 4 fois plus cher que séquentiel.
Ou, un autre exemple, le coût conditionnel de l'exécution du traitement parallèle est de 1000 par défaut, tandis que le coût de chargement d'une page est de 1,0. Il est logique de commencer par modifier un seul des paramètres à la fois pour déterminer si cela affecte le choix du plan. Le moyen le plus simple consiste à commencer par définir le paramètre sur 0 ou sur une valeur élevée (1 million).
Cependant, gardez à l'esprit qu'en améliorant les performances d'une demande, vous pouvez les dégrader dans une autre. En général, il existe un large champ d'expériences. Il vaut mieux essayer de les changer un à la fois, un à la fois.
Options alternatives de traitement
Une histoire sur un planificateur serait incomplète sans mentionner au moins deux extensions PostgreSQL.
Le premier est
SR_PLAN , pour enregistrer le plan calculé et forcer son utilisation ultérieure. Cela permet de rendre le comportement de la base de données plus prévisible en termes de choix de plan.
Le second est l'
Adaptive Query Optimizer , qui implémente le feedback au planificateur à partir de l'exécution en temps réel de la requête, c'est-à-dire que le planificateur mesure les résultats réels de la requête exécutée et ajuste ses plans à l'avenir en gardant cela à l'esprit. La base de données est ainsi "auto-ajustée" pour des données et des requêtes spécifiques.
Que fait d'autre la base de données lorsqu'elle ralentit?
Maintenant que nous avons plus ou moins trié la planification des requêtes, nous verrons ce qui peut être amélioré à la fois dans la base de données elle-même et dans les applications qui l'utilisent pour en tirer le maximum de performances.
Supposons que le plan de requête soit déjà optimal. Si nous excluons les problèmes les plus évidents (mémoire insuffisante ou disque / réseau lent), il y a encore des coûts pour le calcul des hachages. Il existe probablement de grandes opportunités pour de futures améliorations de PostgreSQL (en utilisant le GPU ou même les instructions SSE2 / SSE3 / AVX du CPU), mais jusqu'à présent, cela n'a pas été fait et les calculs de hachage n'utilisent presque jamais les capacités matérielles du matériel. Vous pouvez aider un peu dans cette base de données.
Si vous remarquez, par défaut, les index dans PostgreSQL sont créés en tant que b-tree. Leur utilité est qu'ils sont assez polyvalents. Un tel indice peut être utilisé à la fois avec des conditions d'égalité et avec des conditions de comparaison (plus ou moins). Trouver un élément dans un tel index est un coût logarithmique. Mais si votre requête contient uniquement une condition d'égalité, des index peuvent également être créés en tant qu'index de hachage, dont le coût est constant.
De plus, vous pouvez toujours essayer de modifier la demande afin d'utiliser son exécution parallèle. Pour comprendre exactement comment le réécrire, il est préférable de vous familiariser avec la liste des cas où le parallélisme est automatiquement interdit par le planificateur et d'éviter de telles situations.
Le manuel sur ce sujet décrit brièvement toutes les situations, il est donc inutile de les répéter ici.
Que faire si la demande ne parvient toujours pas à faire le parallèle? Il est très triste de voir comment, dans votre puissante base de données multicœur, où vous êtes le seul client, un cœur est occupé à 100% et tous les autres noyaux le regardent. Dans ce cas, vous devez aider la base de données du côté de l'application. Étant donné que chaque session est affectée à son propre noyau, vous pouvez en ouvrir plusieurs et diviser la requête générale en parties, en effectuant des sélections plus courtes et plus rapides, en les combinant en un résultat commun déjà dans l'application. Cela occupera le maximum de ressources CPU disponibles dans la base de données PostgreSQL.
En conclusion, je voudrais noter que les capacités de diagnostic et d'optimisation ci-dessus ne sont que la pointe de l'iceberg, mais elles sont assez faciles à utiliser et peuvent vous aider à identifier rapidement le problème directement sur les données opérationnelles sans risquer de gâcher la configuration ou de perturber le fonctionnement d'autres applications.
Enquêtes réussies, avec des plans précis et courts.