Optimalizace - 13. díl

Optimalizace je velmi důležité téma a optimalizace v datovém skladu může být občas trochu odlišná od optimalizace v OLTP systémech. Důležité je zmínit exekuční plán, statistiky, způsoby joinování, materializaci, indexy a partitioning. Toto je jedna z náročnějších kapitol na čtení a je poměrně obsáhlá – hodí se mít vhled do teorie grafů.

Exekuční plán

Když vytvořím databázový dotaz. Existuje k němu několik exekučních plánů. Databáze je vytvoří na pozadí a vybere z nich ten, o kterém si myslí, že je nejlepší (A né vždy si to myslí správně a je jí potřeba popostrčením navést ke správnému řešení).

Každý plán má určitou cenu, kterou databáze vypočte na základě statistik, které si ukládá k tabulkám. Statistiky tabulek si lze zobrazit pomocí view pg_stat_user_tables.

/* Zobrazení statistik
   pg_catalog je specifikum PostgreSQL a je podobné information_schema, obsahuje občas něco navíc
*/
SELECT *
FROM pg_catalog.pg_stat_user_tables

Pokud chci vynutit aktualizaci statistik, použiju příkaz ANALYZE pro konkrétní tabulku.

VACUUM

Se statistikami souvisí příkaz VACUUM (Specifické pro PostgreSQL). Ten odstraňuje mrtvé řádky a zajišťuje lepší uspořádanost tabulky. VACUUM má několik typů: Standardní, ANALYZE a FULL – existují i další ale tyhle tři jsou nejpodstatnější

Samotný VACUUM zajišťuje možné znovupoužití mrtvých řádků – řádky, které jsou smazané, ale nejsou smazané fyzicky. Pokud neuvedu konkrétní tabulku, VACUUM se provádí nad všemi tabulkami.

VACUUM ANALYZE kombinuje příkaz VACUUM a příkaz ANALYZE. Tedy nejdřív vyčistím tabulku a pak přepočtu statistiku tabulky.

VACUUM FULL je agresivnější forma VACUUM, která vyžaduje zámek nad celou tabulkou. Fyzicky přesune zbývající živé řádky a zajistí tak lepší uspořádání tabulky. Jde o náročnější operaci a vyžaduje více místa na disku.

VACUUM je v podstatě taková defragmentace, je dobré VACCUM čas od času udělat stejně tak přepočítat statistiky. Obzvlášť pokud se často mění data v tabulkách. Naštěstí existuje autovacuum, který to celé automatizuje za mě. Je potřeba správně nakonfigurovat – například spouštění v čase, kdy je databáze méně vytížená.

/* Vynucení aktualizace statistik tabulky */
ANALYZE l2.DIM_CALENDAR;
 
/* Zjištění nastavení autovacuum */ 
SHOW autovacuum;

Způsob uložení dat

Existuje několik způsobů pro uložení dat. Prostě jen tabulka (heap table), tabulka s indexem, index organized table.

Heap table

Prostě tabulka. Data ukládána jak se databázi zlíbí – není zaručené pořadí dat. Pokud filtruji data nad tabulkou provádím FULL TABLE SCAN.

Clustered index

Clustered index definuje fyzické pořadí dat v tabulce podle hodnot klíče. Každá tabulka může mít pouze jeden clustered index. Index obecně zrychluje SELECT, ale zpomaluje INSERT a UPDATE.

Non-clustered index

Struktura mimo tabulku s odkazem do tabulky. Můžu jich mít kolik chci. Rychlejší INSERT a UPDATE než u clustered indexu, ale stále pomalejší než u heap table.

Index organized table

B-tree index, kdy v listech stromu jsou přímo hodnoty z tabulky a nikoliv odkazy na tabulku.

Compound key index

Takový trik, kdy do klíče přidám i neklíčovou hodnotu, která je rovnou v indexu a ušetřím tak dotaz z indexu do tabulky. Takto je vhodné použít jen velmi často používané sloupce a je vhodné si velmi dobře rozmyslet, kdy se mi to vyplatí.

Indexy obecně

Typicky jsou indexy B(*) stromy – hodně rozvětvené a ne moc hluboké s propojenými listy (hodí se na filtry intervalu – podmínka s > nebo <). Používají se pří vysokém počtu hodnot ve sloupci (typicky ID). Ovšem existuje i varianta bitmapového indexu, která se hodí pro nízký počet hodnot.

Bitmap index

Bitmapové indexování je odlišný způsob indexování od B stromů. Hodí se na dotazy s WHERE s více podmínkami. Většinou zabírají méně místa než B stromy, ale vyžadují větší výkon.

Fungují jako binární odpovědi ANO/NE na nějakou otázku, př.: Je výrobek typu ‚ponorka‘? No a v indexu se všechny záznamy označí 1/0 dle odpovědi.

Defragmentace indexů

S časem dochází k fragmentaci indexů. Jsou fyzicky na disku uloženy v jiném pořadí než jsou uspořádány v indexu. A následně se zpomalují i SELECTy. Indexy je tak potřeba jednou za čas defragmentovat. V PostgreSQL se dá použít příkaz REINDEX, v MSSQL REBUILD.

DWH a indexy

Co se týče datového skladu a indexů, někdy je vhodné je nepoužívat, kvůli zpomalení INSERTu při nočním loadu. A pokud je používám je někdy rychlejší index zrušit, provést INSERT a pak index znovu vytvořit. Samotná stavba indexu z existujících dat je rychlejší než vkládání velkého množství dat do indexu. Použití indexu v DWH je nutné velmi dobře promyslet a nezapomínat na jejich údržbu.

Implementace join

Implementací joinu je několik variant. Nejdůležitější je nested loop join, merge join a hash join. Každý se hodí na jiný typ tabulek a funguje na jiném principu.

Nested loop join

Nested loop join používá nejjednodušší přístup. Jsou to vlastně 2 for cykly. Dejme tomu, že mám tabulku A a B. Procházím cyklem tabulku A a v dalším vnořeném cyklu procházím tabulku B a pokud platí podmínka spojení řádek bude na výstupu.

Tento přístup se dá použít na menší tabulky – má nejmenší režii, ale jeho výkon není moc dobrý. Pokud je v joinu složitější podmínka na spojení, tak to ale může být jediný algoritmus, který ji dokáže vyhodnotit.

Složitost ~ m.n (zanedbávám konkrétní algoritmus a, že tabulky mohou být různě velké)

Merge join

Merge join funguje na přincipu seřazení obou tabulek a následnému spojení. Pokud už jsou množiny seřazeny dle joinovacího klíče řadící část odpadá a stačí jen spojování. Ovšem ne vždy tomu tak je. Algortimus má vyšší režii, a tak pro menší vstupy se nemusí vyplatit.

Složitost ~ n+m + n log n + m log m (n na spojení a složitost řadících algorimtů se pohybuje okolo n log n)

Hash join

Hash join funguje na principu hashe. Na začátku se vytvoří hashovací tabulka a na základě hashovací tabulky se data propojí. Má vyšší režii a musí se jedna z tabulek vejít celá do paměti.

V praxi ho používám tak, že pokud najdu v exekuční plánu nested loop join na velkých tabulkách (deset tisíce, spíš až statisíce a více záznamů) nahradím ho za hash join. Pro použití stačí připsat do joinu jen slovíčko hash (u merge obdobně).

Složitost ~ n+m

Pokud situaci pro představu zjednoduším (tabulky jsou stejně velké), dá se dobře časová složitost vizualizovat na následujícím grafu. Na grafu je vidět, že je nejdříve nejlepší nested loop join, ale se zvyšujícím se počtem dat se pořadí obrací. Na ose X je velikost vstupních dat a na ose Y je časová složitost. Vizualizace na Desmos.

Červený -> Nested loop join, zelený -> Merge join, modrý -> Hash join

Materializace

Materializce znamená, vezmu část SELECTu (view, subquery, …) a jeho výsledek uložím do tabulky. A danou část SELECTu nahradím danou tabulkou. Novou mezi tabulku je třeba obnovovat. V datovém skladu stačí frekvence 1x denně. Bude to prostě nová tabulka, kterou zařadím do ranního loadu. Materializace se hodí například pokud delší dotaz spouštím několikrát denně, tak se to spočítá jen jednou. Nebo když nějaký dotaz má složitější subquery, která je v něm použita vícekrát.

Nemám rád kouzelné slovíčko WITH. Je to primárně z důvodu optimalizace. Pokud je součástí dotazu, který optimalizuji, tak mi WITH znesnadňuje práci. Většinou na něm exekuční plán nefunguje úplně dobře a nelze s WITH úplně dobře hintovat (použití slůvka hash, merge, …). Daleko raději použiji právě než WITH novou tabulku.

Partitioning

Partitioning zajišťuje fyzické oddělení data na disku. Stačí si představit, že tabulka je jeden soubor. Partitioning umožňuje rozdělit tento soubor do více souborů. Typicky se jedná o rozdělení dle časového údaje. Dejme tomu, že rozdělím tabulky dle roků. Výhoda je v tom, že pokud se dotazuji na konkrétní rok, tak se fyzicky čte pouze ze souboru s daným rokem a šetří se čas čtením ostatních souborů.

A teď abych Vám trochu zamotal hlavu. To co jsem popsal výše je horizontální partitioning. Ale existuje ještě vertikální partitioning. Horizontální partitioning fyzicky odděloval řádky tabulky, vertikální odděluje fyzicky sloupce – může se to někdy hodit. Typickým příkladem je oddělení BLOBů.

Obecné rady k optimalizaci

Je správně datový model? S tím nemusí jít nic dělat, ale pokud je špatně datový model tak nám to moc práci neulehčí.

Sloupce by měly držet správné datové typy. Varchar je poslední možnost. Číslo je int/decimal a pod., datum je date/datetime. Také je dobré se v dotazu odkázat na konkrétní sloupce a nepoužívat *.

V joinech by neměli být složité podmínky (třeba OR). Vyvarujte se použítí funkcí v JOINech a WHERE.

Dejte pozor na zámky (locky) – může být jeden z důvodů proč dotaz nedobíhá.

Odsimulujte si celý dotaz – včetně prováděcího plánu.

Pokud uvidíte v plánu nested loop join u velkých tabulek – zbavte se ho a nahraďte hash joinem.

Je správně nastavený index?

Pokud nic z předhozího nepomohlo zamyslete se nad použitím některé z následujících technik.

Materializace – vytvoříte tabulku, která se 1x denně napočítá a dotaz se tak počítá jen jednou denně.
Index – je třeba myslet na režii při vytváření indexu.
Partitions – rozdělení tabulky na partitions. Mělo by být až poslední řešení – typicky se tabulka rozděluje dle datumu.