De klassieke vraag die een ontwikkelaar aan zijn DBA voorlegt of die een bedrijfseigenaar aan een PostgreSQL-consultant stelt, klinkt bijna altijd hetzelfde: “Waarom duurt het zo lang voordat verzoeken in de database zijn voltooid?”
Traditionele reeks redenen:
- inefficiënt algoritme
wanneer u besluit om meerdere CTE's samen te voegen over een paar tienduizenden records - irrelevante statistieken
als de feitelijke verdeling van de gegevens in de tabel al heel anders is dan de gegevens die de vorige keer door ANALYZE zijn verzameld - "plug" op bronnen
en er is niet langer voldoende specifieke rekenkracht van de CPU, er worden voortdurend gigabytes aan geheugen gepompt, of de schijf kan niet alle ‘behoeften’ van de database bijhouden - blokkeren van concurrerende processen
En als blokkades behoorlijk moeilijk te vangen en te analyseren zijn, dan hebben we al het andere nodig vraagplan, die kan worden verkregen met behulp van (Het is natuurlijk beter om meteen UIT TE LEGGEN (ANALYSE, BUFFERS) ...) of .
Maar zoals vermeld in dezelfde documentatie,
“Een plan begrijpen is een kunst, en om het onder de knie te krijgen is enige ervaring vereist...”
Maar je kunt ook zonder, als je het juiste gereedschap gebruikt!
Hoe ziet een queryplan er doorgaans uit? Zoiets:
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=1of zoals dit:
"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"Maar het plan in tekst “vanaf het blad” lezen is erg moeilijk en onduidelijk:
- wordt weergegeven in het knooppunt som per subboombronnen
dat wil zeggen, om te begrijpen hoeveel tijd het kostte om een bepaald knooppunt uit te voeren, of hoeveel precies deze lezing uit de tabel gegevens van de schijf opleverde, moet je op de een of andere manier de een van de ander aftrekken - knooppunttijd nodig vermenigvuldigen met lussen
ja, aftrekken is niet de meest complexe bewerking die "in het hoofd" moet worden uitgevoerd - de uitvoeringstijd wordt immers aangegeven als gemiddeld voor één uitvoering van een knooppunt, en er kunnen er honderden zijn - Nou, en dit alles bij elkaar weerhoudt ons ervan de hoofdvraag te beantwoorden: wie dan wel "de zwakste schakel"?
Toen we dit allemaal aan enkele honderden van onze ontwikkelaars probeerden uit te leggen, realiseerden we ons dat het er van buitenaf ongeveer zo uitzag:
En dat betekent dat we nodig hebben...
Gereedschap
Daarin hebben we geprobeerd alle belangrijke mechanismen te verzamelen die helpen begrijpen “wie de schuldige is en wat te doen” volgens het plan en het verzoek. Nou, en deel een deel van je ervaring met de community.
Ontmoet en gebruik -
Zichtbaarheid van plannen
Is het plan gemakkelijk te begrijpen als het er zo uitziet?
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
Niet echt.
Maar zoals dit, in verkorte vormwanneer de belangrijkste indicatoren gescheiden zijn, is het veel duidelijker:
Maar als het plan ingewikkelder is, komt hij te hulp tijdverdeling in cirkeldiagram per knooppunten:
Welnu, voor de moeilijkste opties heeft hij haast om te helpen voortgangsgrafiek:
Er zijn bijvoorbeeld nogal niet-triviale situaties waarin een plan meer dan één feitelijke wortel kan hebben:
Structurele aanwijzingen
Welnu, als de hele structuur van het plan en de pijnpunten ervan al zijn vastgelegd en zichtbaar zijn, waarom zou u ze dan niet aan de ontwikkelaar onder de aandacht brengen en ze in de "Russische taal" uitleggen?
We hebben al een paar dozijn van dergelijke aanbevelingssjablonen verzameld.
Lijn-voor-lijn queryprofiler
Als u nu de oorspronkelijke vraag over het geanalyseerde plan heen legt, kunt u zien hoeveel tijd aan elke afzonderlijke verklaring is besteed, ongeveer als volgt:
...of zelfs zo:
Parameters in een verzoek vervangen
Als u niet alleen een verzoek aan het plan hebt "bijgevoegd", maar ook de parameters ervan uit de DETAIL-regel van het logboek, kunt u dit bovendien naar een van de opties kopiëren:
- met waardevervanging in het verzoek
voor directe uitvoering op uw basis en verdere profileringSELECT 'const', 'param'::text; - met waardevervanging via PREPARE/EXECUTE
om het werk van de planner te emuleren, wanneer het parametrische deel kan worden genegeerd, bijvoorbeeld bij het werken aan gepartitioneerde tabellenDEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
Archief van plannen
Plakken, analyseren, delen met collega's! De plannen blijven gearchiveerd en u kunt er later naar terugkeren:
Maar als u niet wilt dat anderen uw plan zien, vergeet dan niet het vakje ‘niet publiceren in archief’ aan te vinken.
In de volgende artikelen zal ik het hebben over de moeilijkheden en beslissingen die zich voordoen bij het analyseren van een plan.
Bron: www.habr.com