Optimalizace DB v informačním systému

Petr Sobotka

Petr Sobotka Optimalizace 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

Petr Sobotka 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í

Petr Sobotka 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í

Petr Sobotka 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ě

Petr Sobotka 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

Petr Sobotka 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í!

Petr Sobotka 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)

Petr Sobotka 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ů

Petr Sobotka 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)

Petr Sobotka 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)

Petr Sobotka 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

Petr Sobotka 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)

Petr Sobotka 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

Petr Sobotka 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ů

Petr Sobotka Optimalizace 16

Okolní prostředí

• Aplikace nemusí stíhat prezentovat data, když příliš mnoho

• Stránkování• Omezení rozsahu vstupních parametrů

Petr Sobotka 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

Petr Sobotka 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…

Petr Sobotka 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…

Petr Sobotka Optimalizace 20

Můžeme jít dále

• OLAP

• Data Mining

• DW, DM

• DWA

• …

Petr Sobotka Optimalizace 21

Dotazy?

Petr Sobotka Optimalizace 22

Zdroje

• MySQL Query Optimization• Paul Dubois, 2005

• http://msdn.microsoft.com

• http://technet.microsoft.com