Optimalizace DB v informačním systému

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

2. Ú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 Optimalizace

3. Ú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í Optimalizace

4. 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í Optimalizace

5. 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ě Optimalizace

6. 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 Optimalizace

7. 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í! Optimalizace

8. 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) Optimalizace

9. 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ů Optimalizace

10. 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) Optimalizace

11. 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) Optimalizace

12. 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 Optimalizace

13. 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 MySQL) Optimalizace

14. 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 MySQL) • Cache • Ukládány výsledky vyhodnocení každého dotazu, užité indexy… • Poddotazy apod., které stejný kód, stačí vyhodnotit jednou Optimalizace

15. 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ů Optimalizace

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

17. 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 MySQL • Na jednotlivý příkaz nebo změna v celé DB Optimalizace

18. 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… Optimalizace

19. 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… Optimalizace

20. Můžeme jít dále • OLAP • Data Mining • DW, DM • DWA • … Optimalizace

21. Dotazy? Optimalizace

22. Zdroje • MySQL Query Optimization • Paul Dubois, 2005 • http://msdn.microsoft.com • http://technet.microsoft.com Optimalizace