La question classique qu'un développeur pose à son administrateur de base de données ou qu'un propriétaire d'entreprise pose à un consultant PostgreSQL sonne presque toujours la même : « Pourquoi les requêtes mettent-elles autant de temps à être traitées dans la base de données ? »
Ensemble de raisons traditionnelles :
- algorithme inefficace
lorsque vous décidez de REJOINDRE plusieurs CTE sur quelques dizaines de milliers d'enregistrements - statistiques non pertinentes
si la répartition réelle des données dans le tableau est déjà très différente de celle collectée par ANALYZE la dernière fois - "brancher" sur les ressources
et il n'y a plus assez de puissance de calcul dédiée du CPU, des gigaoctets de mémoire sont constamment pompés ou le disque ne peut pas répondre à tous les « désirs » de la base de données - blocage des processus concurrents
Et si les blocages sont assez difficiles à détecter et à analyser, alors pour tout le reste, nous avons besoin plan de requête, qui peut être obtenu en utilisant (Il vaut mieux, bien sûr, tout de suite EXPLIQUER (ANALYSER, TAMPONS)...) Ou .
Mais, comme indiqué dans la même documentation,
« Comprendre un plan est un art, et le maîtriser demande une certaine expérience… »
Mais vous pouvez vous en passer si vous utilisez le bon outil !
À quoi ressemble généralement un plan de requête ? Quelque chose comme ca:
Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (relname = $1)
Filter: (oid = $0)
Buffers: shared hit=4
InitPlan 1 (returns $0,$1)
-> Limit (actual time=0.019..0.020 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 5
Buffers: shared hit=1ou comme ceci:
"Append (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
" Buffers: shared hit=3"
" CTE cl"
" -> Seq Scan on pg_class (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
" Buffers: shared hit=3"
" -> Limit (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> CTE Scan on cl (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> Limit (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
" Buffers: shared hit=2"
" -> CTE Scan on cl cl_1 (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
" Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"Mais lire le plan sous forme de texte « à partir de la feuille » est très difficile et peu clair :
- est affiché dans le nœud somme par ressources de sous-arbre
c'est-à-dire que pour comprendre combien de temps il a fallu pour exécuter un nœud particulier, ou combien exactement cette lecture de la table a fait remonter les données du disque, vous devez en quelque sorte soustraire l'un de l'autre - le temps du nœud est nécessaire multiplier par des boucles
oui, la soustraction n'est pas l'opération la plus complexe qui doit être faite "dans la tête" - après tout, le temps d'exécution est indiqué comme moyenne pour une exécution d'un nœud, et il peut y en avoir des centaines - eh bien, et tout cela ensemble nous empêche de répondre à la question principale - alors qui "le maillon faible"?
Lorsque nous avons essayé d’expliquer tout cela à plusieurs centaines de nos développeurs, nous avons réalisé que, vu de l’extérieur, cela ressemblait à ceci :
Et cela signifie que nous avons besoin...
Outil
Dans ce document, nous avons essayé de rassembler tous les mécanismes clés qui aident à comprendre « qui est à blâmer et quoi faire » en fonction du plan et de la demande. Eh bien, partagez une partie de votre expérience avec la communauté.
Rencontrer et utiliser -
Visibilité des plans
Est-il facile de comprendre le plan quand il ressemble à ceci ?
Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms
Pas vraiment
Mais comme ça, sous forme abrégéequand les indicateurs clés sont séparés, c’est beaucoup plus clair :
Mais si le plan est plus compliqué, il viendra à la rescousse répartition du temps sur un diagramme circulaire par nœuds :
Eh bien, pour les options les plus difficiles, il est pressé d'aider tableau de progression:
Par exemple, il existe des situations tout à fait non triviales où un plan peut avoir plusieurs racines réelles :
Indices structurels
Eh bien, si toute la structure du plan et ses points sensibles sont déjà disposés et visibles, pourquoi ne pas les souligner au développeur et les expliquer en « langue russe » ?
Nous avons déjà rassemblé quelques dizaines de modèles de recommandations de ce type.
Profileur de requêtes ligne par ligne
Maintenant, si vous superposez la requête d'origine sur le plan analysé, vous pouvez voir combien de temps a été consacré à chaque instruction individuelle - quelque chose comme ceci :
...ou même comme ceci :
Substitution de paramètres dans une requête
Si vous avez « joint » non seulement une requête au plan, mais également ses paramètres de la ligne DETAIL du journal, vous pouvez en plus la copier dans l'une des options :
- avec substitution de valeurs dans la demande
pour une exécution directe sur votre base et un profilage ultérieurSELECT 'const', 'param'::text; - avec substitution de valeur via PREPARE/EXECUTE
pour émuler le travail du planificateur, lorsque la partie paramétrique peut être ignorée - par exemple, lorsque vous travaillez sur des tables partitionnéesDEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
Archives des plans
Collez, analysez, partagez avec vos collègues ! Les plans resteront dans les archives et vous pourrez y revenir ultérieurement :
Mais si vous ne souhaitez pas que d’autres voient votre projet, n’oubliez pas de cocher la case « ne pas publier dans les archives ».
Dans les articles suivants, je parlerai des difficultés et des décisions qui surviennent lors de l'analyse d'un plan.
Source: habr.com