Dotaz: perzistence provadeciho planu v pgsql

[honza]

rad bych se zeptal, jak spravuje pgsql provadeci plany pri pouziti libpq. Vyuziva se pri opetovnem pouziti toho sameho dotazu jiz vyhotoveny provadeci plan.

Jsou vubec plany sdileny vice uzivateli a preziji reconnect?

Odpovědi

[cronin]

Plan dotazu zavisi nielen od dotazu samotneho, ale aj nastaveni aktualnej session a aktualneho stavu databazy.

[dad]

pri pouziti libpq je prece pravdepodobnost, ze dotaz bude stejny miziva. Proto bych se divil, ze by byla na backendu pro libpq realiziovana sprava jiz vytvorenych planu. Neco jineho by bylo, kdyby bylo mozno odsadit z libpq parametrizovane dotazy, ale o tom nevim.

[okbob]

a) pokud nepouzijete pooling, tak plany nejsou sdileny nikdy.

b) pokud chcete pouzit provadeci plan opakovane, je nutne pouzit prepared statements. Ty jsou v postgresql omezene na session - proto je nutny ten pooling, diky kteremu se jedna session pouzije opakovane.

Pavel

 

[Ivan]

Pokud vim tak exekucni plany sdili jedine Oracle. Cela myslenka sdileni exekucnich planu vychazi z predpokladu, ze autor aplikace neni idiot a ze pouziva rozumne mnozstvi SQL statementu. V Oracle se rozlisuje tzv. soft parse(plan se najde podle hashe SQL v SGA) a hard parse(cely exekucni plan se vymysli a ulozi do SGA). Proti sdileni exekucnich planu mam jeden priklad z praxe. V DB je tabulka T a ma sloupce (PK1, ANAME1, AVALUE1, ..., ANAME24, AVALUE24). Inserty do teto tabulky vklada nejaky JAVA sr*c, ktery dynamicky "vymysli" SQL statement podle toho ktere sloupce jsou insertu zrovna pouzity. Hadam ze programator ktery tohle vymyslel byl na sebe hodne pysny, kdyz to odladil. Tabulka T ma 24 "atributu" => existuje 2^24 (16,7 milionu) ruznych insertu. A  LRU cache Oracle se poctive snazi udrzet exekucni plany vsech insertu, bohuzel ma SGA omezenou velikost a proto se ji to nikdy nemuze podarit a z SGA "vypadavaji" i SQL statementy, ktere by tam mely zustat. Na RACu se navic sdili exekucni plany i mezi nody, takze kazde parsovani vyzaduje komunikaci po siti mezi nody clusteru.

Na druhou stranu treba Informix exekucni plany nesdili. Kazda session si drzni svoje exekucni plany. Proti tehle strategii mam zase jiny protipriklad z praxe. Nekdo rekl JAVA programatorovi ze je dobre pouzivat prepared SQL statementy, buhuzel uz mu nikdo nerekl ze pokud pouzitje Prepare, tak taky musi pouzit Release. Jinak si bude Informixova session pamatovat exekucni plan dotazu navzdy. JAVA programator predpokladal ze uvolneni prostredku za nej vyresi GC. Cim system bezi dele, tim vice pameti zabiraji exekucni plany jednotlivych SQL dotazu. Za tyden je to cca 8GB RAM. Ten system stal spoustu penez, vsechno je navrzeno aby to bylo vysoce dostupne a presto je to potreba kazdy tyden restartovat.

Prvni priklad by v pohode fungoval na Informixu a ten druhy zase na Oracle. Takze sdileni exekucnich planu neni zadna vyhra, casto to beh zrychli ale nekdy to muze spusobit spoustu problemu.

 

 

[podlesh]

DB2 také ukládá zkompilované dynamické příkazy v cache (package cache).

Velmi užitečná věc, nějak se mi nezdá že by to opravdu žádná jiná databáze neměla (tedy kromě MySQL). Prakticky to lze vidět právě v případě, kdy někdo tu cache zahltí generováním obrovského množství různých dotazů (nejhorší mor je lepení hodnot přímo do dotazu místo použití otazníku).

[podlesh]

Z toho také plyne, že pokud by se původní dotaz týkal DB2, tak by odpovědi byly všechny ano. Bohužel s postgresem neporadím.

[z/OS]

DB2 9.7 LUW umi volitelne prevadet dotazy na jejich otaznikove varianty aby se sdilena cache pro dotazy lepe vyuzila.

[podlesh]

More Efficient Queries with Literals: DB2 has always made good use of resources by not needing to recompile new queries that are identical to queries already in the package cache. It now considers queries that are the same except for literals in the predicate (where clause) to also be identical. This is especially useful in OLTP workloads that execute lots of queries with different predicate literals because it can significantly reduce the amount of prepare time.

Pěkné. Je vidět že v IBM konečně myslí i na PHPkáře.

Ale na vynalézavé javisty si nikdo nepřijde - stačí generovat proměnný počet otazníku

[okbob]

V postgresu se jednu chvíli uvažovalo o tom, že by se sdílení plánů zavedlo - prakticky v tom není žádný problém - uložit plán lze (prepared statement) a sdílená cache je v pg k dispozici také. Proti byli někteří významní DBA, kteří tvrdí, že použítí sdílených plánů může vést k horšímu výkonu - v případě cost based optimalizace. V tomto případě optimální plán se mění v závislosti na parametrech. Pokud by docházelo k implicitnímu použítí plánů z cache, tak by nepochybně byly některé prováděcí plány neoptimální. Něco jiného je rule based optimalizace. Tam je optimální plán nezávislý na parametrech a tudíž snadno může být sdílen. Jako větší zlo se považuje špatný prováděcí plán než opakované provádění optimalizace. Proto přístup pg je takový, že plán není uložen v cache, ale do cache se ukládají všechny informace, které jsou potřeba pro vygenerování plánu - tudíž opakované generování plánu je velice rychlé - samosebou pomalejší než načtení plánu z cache. Pokud by pak bylo potřeba skutečně bombardovat server SQL příkazy, pak lze použít prepared statements.

Mimochodem MS SQL Server také sdílí prováděcí plány.

 

[honza]

dekuji za reakce a dodam kratke vysvetleni. Ve firme se uvazuje o prechodu ze stareho servru informix (7.x) na neco noveho. 

Dosavadni software vyuzivalo ESQL a jak ze starych casu znamo, je v prelozenem kodu jiz vytvoreny plan 'obsazen'. Nenastava tedy problem, ze by se musela provadet sprava vyhotovenych planu. ESQL-statements jsou samozrejme svou podstatou jiz 'prepere', o to se staraji jiz nutnost pouziti host-variables.

Pri analyze pgsql jsme se puvodne domnivali, ze prepare statements je mozno pouzit pouze na urovni SPI. Nyni po studiu nekterych clanku (napr. p.Zak/root) a po prispevku pana Stehuleho zde tomu rozumim tak, za i libpq podporuje prepare-statements, _ale_ o spravu planu se musi starat uzivatelske software (pgsql databaze to nedela sama).

Mimojine, na libpq jsme prisli oklikou, protoze jsme samozrejme chteli zkusit vyuzit ESQL podporu v postgresql a k nasemu prekvapeni ta implementace vubec nepracuje tak, kak se to drive delalo (prelozeny kod plan neobsahuje) - a dalsi analyzou jsme zjistili, ze ESQL implementace v pgsql vyuziva libpq, tam je mozno tedy vyuzit prepare statement ale v kratkosti jsme nikde nenasli tu spravu tech planu v ESQL interfaci.

Podle prispevku pana Stehuleho to vypada, ze by nase starost, aby nas kod byl jiz 'hotovy k pouziti' , neni tak opravnena, podle jinych prispevku zde to ovsem jine databaze delaji. Takze vlastne zase clovek nevi co

Rad bych se ohledne pgsql zeptal jeste na jednu vlastnost. V informix bylo mozne napr. v SELECT statement pouzit jako host_variable normalni C-strukturu. Tato struktura byla samozrejme 'chytre' vytvorena tak, ze jeji prvky byly prave obsahy nacitanych udaju z tabulky. (priklad - select a,b,c,d from t.x ...) SELECT statement tedy nacetl najednou vsechny udaje (obsah sloupcu a,b,c,d do prvku struktury) bez toho, aniz by se musel v nejake smycce dodany vysledek rozhodit  do jednotlivych promenych.

Existuje nejaky podobny mechanismus v pgsql.

[okbob]

Já jsem nikdy nedělal ani s Informixem a stejně tak esql, takže Vám moc neporadím. Nevím proč byste ale v esql pg měli si explicitně hrát s prováděcími plány - důležitý je SQL příkaz - prováděcí plán se vygeneruje až když je nutné a na straně serveru. Správu plánu řešíte pouze u dynamického SQL.

Z konference pg je ovšem znát, že lidi co psali v esql na informixu požívají esql i na PostgreSQL. Pro pochopení věcí bych ale doporučoval dočasně zapomenout na Informix. Řekl bych, že zdrojáky esql budou stejné - vlastní implementace, resp. to co vyleze pak dost jiné.