ProHoster > Blog > podávání > O čem EXPLAIN mlčí a jak to přimět mluvit

O čem EXPLAIN mlčí a jak to přimět mluvit

Klasická otázka, kterou předkládá vývojář svému DBA nebo majitel firmy konzultantovi PostgreSQL, zní téměř vždy stejně: "Proč trvá dokončení požadavků v databázi tak dlouho?"

Tradiční sada důvodů:

  • neefektivní algoritmus
    když se rozhodnete PŘIPOJIT SE k několika CTE přes několik desítek tisíc záznamů
  • zastaralé statistiky
    pokud je skutečná distribuce dat v tabulce již velmi odlišná od distribuce, kterou naposledy shromáždila ANALYZE
  • „zapojování“ zdrojů
    a již není dostatek vyhrazeného výpočetního výkonu CPU, gigabajty paměti jsou neustále čerpány nebo disk nestíhá držet krok se všemi „žádostmi“ databáze
  • blokování z konkurenčních procesů

A pokud je docela obtížné zachytit a analyzovat blokování, pak pro všechno ostatní potřebujeme plán dotazů, který lze získat pomocí Operátor EXPLAIN (Je samozřejmě lepší okamžitě VYSVĚTLIT (ANALÝZA, VYROVNÁVÁNÍ) ...) Nebo modul auto_explain.

Ale jak je uvedeno ve stejné dokumentaci,

„Porozumět plánu je umění a jeho zvládnutí vyžaduje určitou dávku zkušeností...“

Ale můžete se bez něj obejít, pokud použijete správný nástroj!

Jak obvykle vypadá plán dotazů? Něco takového:

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=1

nebo jako toto:

"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"

Ale čtení plánu v textu „z listu“ je velmi obtížné a nejasné:

  • se zobrazí v uzlu součet podle zdrojů podstromu
    to znamená, abyste pochopili, kolik času trvalo spuštění konkrétního uzlu nebo kolik přesně toto čtení z tabulky přineslo data z disku, musíte nějak odečíst jeden od druhého
  • je potřeba čas uzlu násobit smyčkami
    ano, odečítání není nejsložitější operace, kterou je nutné provést „v hlavě“ – koneckonců doba provedení je uvedena jako průměr na jedno provedení uzlu a mohou jich být stovky
  • no a to všechno dohromady nám brání odpovědět na hlavní otázku – tak kdo "nejslabší článek"?

Když jsme se to vše pokusili vysvětlit několika stovkám našich vývojářů, uvědomili jsme si, že to zvenčí vypadá asi takto:

O čem EXPLAIN mlčí a jak to přimět mluvit

A to znamená, že potřebujeme...

Nástroj

V něm jsme se pokusili shromáždit všechny klíčové mechaniky, které pomáhají pochopit „kdo je vinen a co dělat“ podle plánu a požadavku. No a podělte se o část svých zkušeností s komunitou.
Seznamte se a použijte - vysvětlit.tensor.ru

Viditelnost plánů

Je snadné pochopit plán, když vypadá takto?

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

Ne opravdu.

Ale takhle, ve zkrácené podoběkdyž jsou klíčové indikátory odděleny, je to mnohem jasnější:

O čem EXPLAIN mlčí a jak to přimět mluvit

Pokud je ale plán složitější, přijde na pomoc piechart rozložení času podle uzlů:

O čem EXPLAIN mlčí a jak to přimět mluvit

No, u těch nejobtížnějších možností spěchá na pomoc graf pokroku:

O čem EXPLAIN mlčí a jak to přimět mluvit

Existují například zcela netriviální situace, kdy plán může mít více než jeden skutečný kořen:

O čem EXPLAIN mlčí a jak to přimět mluvitO čem EXPLAIN mlčí a jak to přimět mluvit

Strukturální vodítka

No, pokud je celá struktura plánu a jeho bolavá místa již rozvržena a viditelná, proč je nezvýraznit developerovi a nevysvětlit je „ruským jazykem“?

O čem EXPLAIN mlčí a jak to přimět mluvitJiž jsme shromáždili několik desítek takových šablon doporučení.

Profiler dotazů řádek po řádku

Pokud nyní překryjete původní dotaz do analyzovaného plánu, můžete vidět, kolik času bylo vynaloženo na každý jednotlivý příkaz - něco takového:

O čem EXPLAIN mlčí a jak to přimět mluvit

...nebo i takto:

O čem EXPLAIN mlčí a jak to přimět mluvit

Nahrazení parametrů do požadavku

Pokud jste k plánu „připojili“ nejen požadavek, ale i jeho parametry z řádku DETAIL protokolu, můžete jej dodatečně zkopírovat v jedné z možností:

  • se substitucí hodnoty v dotazu
    pro přímé provedení na vaší základně a další profilování

    SELECT 'const', 'param'::text;
  • se substitucí hodnoty přes PREPARE/EXECUTE
    emulovat práci plánovače, kdy parametrickou část lze ignorovat - například při práci na rozdělených tabulkách

    DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);

Archiv plánů

Vkládejte, analyzujte, sdílejte s kolegy! Plány zůstanou archivovány a můžete se k nim později vrátit: vysvětlit.tensor.ru/archive

Pokud ale nechcete, aby ostatní viděli váš plán, nezapomeňte zaškrtnout políčko „nepublikovat v archivu“.

V následujících článcích budu hovořit o obtížích a rozhodnutích, která vznikají při analýze plánu.

Zdroj: www.habr.com

Přidat komentář