Optimalizace DB v informanm systmu Petr Sobotka Optimalizace

Optimalizace DB v informačním systému Petr Sobotka Optimalizace

Úvod • Ukázka možného užití DB v IS • Typy tabulek v IS • Číselníky • Faktové tabulky • Pro konfiguraci, bezpečnost, resourcy… • Reporting • Zpřístupnění dat (např. klientovi) • Pro práci, přehled, zákonem vynucené… • Často bývá zdroj potřeby optimalizovat Petr Sobotka Optimalizace 2

Úvod • Co může vyvolat potřebu optimalizovat • Refactoring – Typicky dost času na naplánování a otestování • Testeři objeví problém – Času již méně… • Klient nahlásí problém – Mělo být již včera… • Neustálý boj • DB časem roste (hlavně faktové tabulky) • Nároky klientů se také zvyšují Petr Sobotka Optimalizace 3

Nebezpečí optimalizace • Typicky je to zásah do již fungujícího systému • Možné zanesení chyb – nutnost řádně testovat, ale někdy není čas • Možné zpomalení jiných procesů, někdy až fatální • Stojí čas a prostředky • Kdo to zaplatí? • Jsou na to zdroje (MD, stroje…)? • Třeba s tím počítat již při jejich plánování Petr Sobotka Optimalizace 4

Optimalizovat? • Někdy nutné pro chod systému • Pokud ne, třeba zvážit • vynucené smluvně, slíbené či „bylo by dobré, kdyby…“ • Vztah s klientem a dobrá pověst vs. vynaložené náklady navíc • Optimalizovat co nejdříve • Cena s časem roste • Některé věci lze již při návrhu a tvorbě Petr Sobotka Optimalizace 5

Co optimalizovat • Třeba najít úzké hrdlo • Nemá smysl optimalizovat místa, kde se příliš neprojeví • Nemusí být ale vždy vše přípustné (např. části systému pod správou jiné společnosti, vazby s jinými systémy…) • Optimalizovat lze ledacos, např. • Výběr DB (ne všechny DB jsou stavěné na stejnou zátěž nebo způsob použití) • Schéma DB • Indexy • Kód dotazu • Plány exekuce • Okolní prostředí • HW Petr Sobotka Optimalizace 6

Schéma DB • Odstranění redundancí • Normální formy (normalizace) • Šetří místo, méně šancí udělat chybu („jedna pravda“) • Velké vs. malé tabulky • Větší tabulka znamená větší místo v operační paměti pro každý řádek • Menší tabulky je ale zase potřeba častěji joinovat • Přidaní redundance k urychlení (je ale proti NF) • Předpočítané sloupce (např. agregované hodnoty) • Předpočítané tabulky (může ušetřit joiny a výpočty) • Ale zabírá více místa, zvětšuje tabulky, větší možnost výskytu chyb… třeba zvážit, kde se vyplatí! Petr Sobotka Optimalizace 7

Schéma DB • Triggery bývají drahé • lépe použít akce puštené přímo v updatovacích procedurách • Používat co nejmenší datové typy • Méně místa, snadněji do operační paměti • U sloupců i indexů • Používat (pokud lze) typy omezené délky • Snižuje fragmentaci v případě častých deletů a updatů (mezery různých délek a záznamy taky) Petr Sobotka Optimalizace 8

Indexy • Struktury, které urychlují hledání v DB • Některé typy (záleží na DB, které poskytuje) • B-Stromy – Vhodný pro intervalové dotazy a použitelný pro přímé porovnání hodnot s nízkou kardinalitou – Na pořadí sloupců záleží! (index na A, B lze použít pro A, B či A) – Vložené sloupce pro rychlejší přístup – Klastrovaný vs. neklastrovaný • Hashovací – Vhodný pro přímé porovnání • Bitmapový – Bitmapa Řádky x Hodnoty (vysokých kardinalit), ideální pro přímé srovnání s hodnotou přes vícero takových sloupců Petr Sobotka Optimalizace 9

Indexy • Třeba udržovat, aby plně efektivní! • Ideálně v pravidelných intervalech rebuild (joby v méně vytíženou dobu) • Vhodný je také občasný monitoring adminem • Ne vždy jen pozitivní dopad • Zabírají místo • Zpomalují inserty, delety a updaty – Pro hromadné úpravy je ovšem možno je dočasně vypnout (např. v době, kdy minimální provoz) Petr Sobotka Optimalizace 10

Partitioning • Partitioning (někdy tzv. horizontální) • Dle hodnot „některého“ sloupce je možné rozdělit tabulku na více částí – např. u klastrovaných tabulek v MSSQL to musí být část primárního klíče či klastrovaného indexu… • Při dotazech s touto tabulkou se pak většinou vystačí jen s částí a tedy se může pracovat s méně daty • Části mohou dokonce být uloženy v různých DB, takže i na jiném disku nebo stroji a lze je zabalit (např. lze použít, aby stará, téměř nepoužívaná, data faktové tabulky nenafukovala tolik DB) Petr Sobotka Optimalizace 11

Kód dotazu • Na vyhodnocení SQL příkazu se lze dívat jako na běžný programovací jazyk • Třeba minimalizovat počet drahých operací (např. konverze, TRUNCATE TABLE místo DELETE FROM…) • Obzvláště pozor na cykly a kurzory • Dlouhá transakce trvá neúměrně dlouho! • Kde možné, tak dobré rozdělit do více běhů • Ale příliš mnoho drobných transakcí také pomalé • Často stejný dotaz zapsaný jiným kódem odlišně vyhodnocován • Má jiný plán exekuce Petr Sobotka Optimalizace 12

Plány exekuce • Možných plánů příliš mnoho • Všechny možné kombinace JOINů, filtrů… • Nevyplatí se procházet všechny • Plánování typicky využívá statistik, které si DB uchovává • Plány stejného kódu dotazu se mohou časem měnit a to ne vždy k lepšímu! • V každém rozumném grafickém správci DB lze plán odhadnout či zobrazit spolu s exekucí • Je možné použít i přímých příkazů (např. explain v My. SQL) Petr Sobotka Optimalizace 13

Plány exekuce • Uloženým procedurám lze definovat vlastní plán • Ale lépe jen v krajních případech (případné změny v DB mohou tento plán zneefektivnit a jen uživatel ho může změnit) • Jak dosáhnout, aby byly použity indexy? • Úpravou kódu dotazu – Např. konverze na sloupci indexu ve WHERE klauzuli může způsobit, že index nepoužit • Doporučením indexu v dotazu (use index v My. SQL) • Cache • Ukládány výsledky vyhodnocení každého dotazu, užité indexy… • Poddotazy apod. , které stejný kód, stačí vyhodnotit jednou Petr Sobotka Optimalizace 14

Okolní prostředí • Cache může mít i aplikační server • S vhodným nastavením expirace dotazů • Pomalá či zahlcovaná síť • S tím i ta nejoptimalizovanější DB nic nezmůže • Vhodné užívání DB • Minimalizace počtu dotazů • Ale také posílaní co nejméně dat zpět • Pozor na timeouty v okolním prostředí! • Omezení náročnosti kladených dotazů Petr Sobotka Optimalizace 15

Okolní prostředí • Aplikace nemusí stíhat prezentovat data, když příliš mnoho • Stránkování • Omezení rozsahu vstupních parametrů Petr Sobotka Optimalizace 16

Scheduling (rozvrhování) • Raději jen pokud nejde jinak • Lze měnit priority přístupu k tabulce některých SQL příkazů • DELETE, INSERT, UPDATE… • Normálně zápis zablokuje ostatní za sebou a počká, až dočtou ty před ním • Lze docílit, aby čtení nebylo blokováno, ale psaní předběhlo… – LOW_PRIORITY a HIGH_PRIORITY v My. SQL – Na jednotlivý příkaz nebo změna v celé DB Petr Sobotka Optimalizace 17

HW • Již pro administrátory • Správný chod vyžaduje důsledný monitoring • Lze objevit řadu problémů, než eskalují • Chování DB i stroje, kde běží se časem podstatně mění! • Dostatek paměti a výpočetní síly • Dle rostoucích požadavků • Dostatek místa na disku • Čištění logů, temp adresářů pro integraci… Petr Sobotka Optimalizace 18

HW • Někdy se DB s jinými aplikacemi navzájem dusí • Rozmístit na více strojů, alespoŇ virtuálních • Zálohy co nejvíce vs. ztráta výkonu, když běží • Monitoring běhu služeb po pádech či restartech • Občas některé nenaběhnou • Lze částyečně automatizovat • Také možnost využít profesionálních datacenter… Petr Sobotka Optimalizace 19