IBAnalyst: Porozumění vaší databázi

Dmitri Kuzmenko, [email protected], poslední aktualizace 31. března 2014

S InterBase pracuji od roku 1994. Tehdy byla většina databází malá a nevyžadovala žádné ladění. Samozřejmě se vyskytly případy, kdy jsem musel změnit ibconfig na serveru a překonfigurovat hardware nebo operační systém, ale to bylo téměř vše, co jsem mohl pro ladění výkonu udělat.

Před čtyřmi lety začala naše společnost poskytovat technickou podporu a školení uživatelů InterBase. Práce s mnoha produkčními databázemi mě také naučila mnoho různých věcí. Většina toho, co jsem se naučil, se však týkala aplikací - používání parametrů transakcí, optimalizace dotazů a výsledných sad.

Samozřejmě jsem už delší dobu věděl o nástroji gstat, který poskytuje informace o statistikách databáze. Pokud jste se někdy podívali na výstup gstat nebo si o něm přečetli v opguide.pdf, víte, že statistický výstup vypadá jen jako hromada čísel a nic jiného. Dobře, můžete zjistit informace o fragmentaci konkrétní tabulky nebo indexu, ale jaké další užitečné informace lze získat?

Naštěstí jsem se před prací s InterBase zajímal o různé datové struktury, o to, jak jsou ukládány a jaké algoritmy používají. To mi pomohlo interpretovat výstup gstat. Tehdy jsem se rozhodl napsat nástroj, který by dokázal analyzovat výstup gstat, aby pomohl s laděním databáze nebo alespoň identifikoval příčinu problémů s výkonem.

Dlouhý příběh, ale výsledkem bylo vytvoření nástroje IBAnalyst. I přes své zkušenosti mi stále umožňuje nacházet velmi zajímavé věci nebo problémy s výkonem v různých databázích.

Reálné systémy mají běhový výkon, který kolísá jako vlna. Amplituda takových „vln“ může být nízká nebo vysoká, takže můžete vidět, jak se výkon liší den ode dne (nebo hodinu od hodiny). Skutečný výkon závisí na mnoha faktorech, včetně návrhu aplikace, konfigurace serveru, souběžnosti transakcí, verzovaného odpadu v databázi a tak dále. Abyste zjistili, co se v databázi děje (pozitivní i negativní aspekty výkonu), měli byste se alespoň čas od času podívat na statistiky databáze.

Podívejme se na možnosti nástroje IBAnalyst. IBAnalyst může převzít statistiky z gstat nebo Services API a sestavit z nich zprávu, která vám poskytne úplné informace o databázi, jejích tabulkách a indexech. Obsahuje průběžná upozornění, která jsou k dispozici při procházení statistik; zahrnuje také komentáře s tipy a doporučující zprávy.

Informace o databázi

Obrázek 1 Souhrn statistik databáze

Souhrn zobrazený na obrázku 1 poskytuje obecné informace o vaší databázi. Zobrazená upozornění nebo komentáře jsou založeny na pečlivě shromážděných znalostech získaných z velkého počtu reálných produkčních databází.

Poznámka: Všechny obrázky v tomto článku obsahují statistiky gstat, které byly převzaty z reálné produkční databáze (se souhlasem jejích vlastníků).

Jak jsem řekl dříve, surové statistiky databáze vypadají záhadně a je obtížné je interpretovat. IBAnalyst jasně zvýrazňuje případné problémy žlutě nebo červeně a detail problému lze přečíst jednoduše umístěním kurzoru na příslušnou položku a přečtením zobrazeného tipu. Co můžeme z výše uvedeného obrázku zjistit? Toto je databáze s dialektem 3 a velikostí stránky 4096 bajtů. Před šesti až osmi lety vývojáři používali výchozí velikost stránky 1024 bajtů, ale v novější době by taková malá velikost stránky mohla vést k mnoha problémům s výkonem. Protože tato databáze má velikost stránky 4k, není zobrazeno žádné upozornění, protože tato velikost stránky je v pořádku.

Dále vidíme, že parametr Forced Write je nastaven na OFF a je označen červeně. InterBase 4.x a 5.x měly tento parametr ve výchozím nastavení ON. Forced Writes je metoda zápisové mezipaměti: když je ON, zapisuje změněná data okamžitě na disk, ale OFF znamená, že zápisy budou operačním systémem po neznámou dobu uloženy v jeho souborové mezipaměti. InterBase 6 vytváří databáze s Forced Writes OFF.

Proč je to ve zprávě IBAnalyst označeno červeně? Odpověď je jednoduchá - použití asynchronních zápisů může způsobit poškození databáze v případě výpadku napájení, operačního systému nebo serveru.

Tip: Je zajímavé, že moderní rozhraní HDD (ATA, SATA, SCSI) nevykazují žádný výrazný rozdíl ve výkonu při nastavení Forced Write na On nebo Off(1).

Další položkou ve zprávě je záhadný „interval sweepu“. Pokud je kladný, nastavuje velikost mezery mezi nejstarší (2) a nejstarší snapshot transakcí, při které je engine upozorněn na potřebu spustit automatický sběr odpadu. Na některých systémech způsobí dosažení tohoto prahu efekt „náhlé ztráty výkonu“, a proto se někdy doporučuje nastavit interval sweepu na 0 (úplné zakázání automatického sweepu). Zde je interval sweepu označen žlutě, protože hodnota mezery sweepu je záporná, což může být ve statistikách InterBase 6.0, Firebird a Yaffil, ale ne v InterBase 7.x. Pokud je hodnota mezery sweepu větší než interval sweepu (pokud interval sweepu není 0), bude položka intervalu sweepu ve zprávě označena červeně s příslušným tipem.

Následujících 8 řádků prozkoumáme jako skupinu, protože všechny zobrazují aspekty stavu transakcí v databázi:

Nejstarší transakce je nejstarší nepotvrzená transakce. Jakákoli nižší čísla transakcí patří potvrzeným transakcím a pro takové transakce nejsou k dispozici žádné verze záznamů. Čísla transakcí vyšší než nejstarší transakce patří transakcím, které mohou být v jakémkoli stavu. Tato transakce se také nazývá „nejstarší zajímavá transakce“, protože zamrzne, když je transakce ukončena rollbackem, a server nemůže v tu chvíli vrátit její změny.
Nejstarší snapshot - nejstarší aktivní (tj. dosud nepotvrzená) transakce, která existovala na začátku transakce, která je aktuálně nejstarší „zajímavou“ transakcí. Označuje nejnižší číslo snapshot transakce, která má zájem o verze záznamů.
Nejstarší aktivní - nejstarší aktuálně aktivní transakce (3).
Další transakce - číslo transakce, které bude přiřazeno nové transakci.
Aktivní transakce - IBAnalyst zobrazí upozornění, pokud je číslo nejstarší aktivní transakce o 30 % nižší než denní počet transakcí. Statistiky neříkají, zda mezi nejstarší aktivní a další transakcí existují nějaké další aktivní transakce, ale takové transakce tam být mohou. Obvykle, pokud se nejstarší aktivní transakce zasekne, existují dva možné důvody: a) nějaká transakce je aktivní dlouhou dobu, nebo b) návrh aplikace umožňuje transakcím běžet dlouhou dobu. Oba důvody brání sběru odpadu a spotřebovávají prostředky serveru.
Transakce za den - to se vypočítá z další transakce, vydělené počtem dní, které uplynuly od vytvoření databáze do okamžiku získání statistik. To může být správné pouze pro produkční databáze nebo pro databáze, které jsou pravidelně obnovovány ze zálohy, což způsobí resetování číslování transakcí.

Jak jste se již dozvěděli, pokud existují nějaká upozornění, jsou zobrazena jako barevné řádky s jasnými a popisnými tipy, jak problém opravit nebo mu předejít.

Je třeba poznamenat, že statistiky databáze nejsou vždy užitečné. Statistiky shromážděné během práce a údržbových operací mohou být bezvýznamné.

Neshromažďujte statistiky, pokud:

Jste právě obnovili databázi
Provedli jste zálohu (gbak -b db.gdb) bez přepínače -g
Nedávno jste provedli ruční sweep (gfix -sweep)

Statistiky získané při takových příležitostech budou prakticky nepoužitelné. Je také pravda, že během běžné práce mohou nastat okamžiky, kdy je databáze v perfektním stavu, například když aplikace vytvářejí menší zatížení databáze než obvykle (uživatelé jsou na obědě nebo je klidné období pracovního dne).

Jak poznáte, že je s databází něco v nepořádku?

Vaše aplikace mohou být navrženy tak dobře, že budou vždy pracovat správně s transakcemi a daty, nevytvářet mezery sweepu, nehromadit mnoho aktivních transakcí, neudržovat dlouhé snapshoty a tak dále. Obvykle se to nestává (omlouvám se, kolegové).

Nejčastějším důvodem je, že vývojáři testují své aplikace pouze se dvěma nebo třemi současnými uživateli. Když je aplikace poté použita v produkčním prostředí s patnácti nebo více současnými uživateli, může se databáze chovat nepředvídatelně. Samozřejmě, víceuživatelský režim může fungovat dobře, protože většinu víceuživatelských konfliktů lze testovat se dvěma nebo třemi současně běžícími aplikacemi. Při větším počtu uživatelů však mohou nastat problémy se sběrem odpadu. Takové potenciální problémy lze zachytit, pokud shromažďujete statistiky databáze ve správných okamžicích.

Informace o tabulkách

Podívejme se na další ukázkový výstup z nástroje IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Obrázek 2 Statistiky tabulek

Pohled na statistiky tabulek v IBAnalyst je také velmi užitečný. Může ukázat, které tabulky mají mnoho verzí záznamů, kde bylo provedeno velké množství aktualizací/mazání, fragmentované tabulky, s fragmentací způsobenou aktualizací/mazáním nebo blobem, a tak dále. Můžete vidět, které tabulky jsou často aktualizovány a jaká je velikost tabulky v megabajtech. Většina těchto upozornění je přizpůsobitelná.

V tomto příkladu databáze existuje několik problémů. Především žlutá barva ve sloupci VerLen upozorňuje, že prostor zabraný verzemi záznamů je větší než prostor zabraný samotnými záznamy. To může být důsledkem aktualizace mnoha polí v záznamu nebo hromadného mazání. Podívejte se na řádky, ve kterých je sloupec MaxVers označen modře. To ukazuje, že je uložena pouze jedna verze na záznam, a proto je problém způsoben hromadným mazáním. Hodnota ve sloupci Versions ukazuje, kolik záznamů bylo smazáno.

Dlouhotrvající aktivní transakce bránící sběru odpadu jsou hlavním důvodem degradace výkonu. Pro některé tabulky může existovat mnoho verzí, které jsou stále „používané“. Server nemůže rozhodnout, zda jsou skutečně používány, protože aktivní transakce potenciálně potřebují kteroukoli nebo všechny tyto verze. Server proto tyto verze nepovažuje za odpad a pokaždé, když transakce čte záznam, trvá stále déle sestavit správný záznam z mnoha verzí. Na obrázku 2 můžete vidět dvě tabulky, které mají počet verzí třikrát vyšší než počet záznamů. Pomocí těchto informací můžete také zkontrolovat, zda skutečnost, že vaše aplikace tyto tabulky tak často aktualizují, je záměrná nebo důsledek chyby.

Pohled na indexy

Indexy používá databázový engine k vynucení primárních klíčů, cizích klíčů a jedinečných omezení. Také urychlují načítání dat. Jedinečné indexy jsou pro načítání dat nejlepší, ale míra přínosu nejedinečných indexů závisí na rozmanitosti indexovaných dat.

Například se podívejte na ADDR_ADDRESS_IDX6. Především samotný název indexu naznačuje, že byl vytvořen ručně. Pokud byly statistiky získány prostřednictvím Services API s informacemi o metadatech, můžete vidět, které sloupce jsou indexovány (v IBAnalyst 1.83 a novějších). U zkoumaného indexu můžete vidět, že má 34999 klíčů, TotalDup je 34995 a MaxDup je 25056. Oba sloupce duplicit jsou označeny červeně. To proto, že mezi všemi klíči v tomto indexu jsou pouze 4 jedinečné hodnoty klíčů, jak je vidět ve sloupci Uniques. Navíc největší řetězec duplicit (klíč ukazující na záznamy se stejnou hodnotou sloupce) je 25056 - tj. téměř všechny klíče ukládají jednu ze čtyř jedinečných hodnot. Výsledkem je, že tento index by mohl:

Snížit rychlost procesu obnovy. Dobře, třicet pět tisíc klíčů není pro moderní databáze a hardware velký problém, ale dopad by měl být přesto zaznamenán.
Zpomalit garbage collection. Indexy s nízkým počtem unikátních hodnot mohou zpomalit garbage collection až desetkrát ve srovnání s plně unikátním indexem. Tento problém byl vyřešen v InterBase 7.1/7.5 a Firebird 2.0.
Způsobovat zbytečné čtení stránek, když optimalizátor čte index. Závisí to na hodnotě hledané v konkrétním dotazu - hledání pomocí indexu s vyšší hodnotou MaxDup bude pomalejší. Hledání hodnoty ve sloupci s menším počtem duplicitních hodnot bude rychlejší, ale pouze vy víte, že je sloupec indexovaný.

Proto IBAnalyst upozorňuje na takové indexy, označuje je červeně a žlutě a zahrnuje je do zprávy Doporučení. Bohužel většina „špatných“ indexů je automaticky vytvořena pro vynucení omezení cizích klíčů. V některých případech lze tento problém vyřešit zabráněním pomocí triggerů mazání nebo aktualizací primárních klíčů v číselníkových tabulkách. Pokud ale není možné takové změny implementovat, IBAnalyst vám při každém zobrazení statistik ukáže „špatné“ indexy na cizích klíčích.

Zprávy

Není potřeba pokaždé procházet celou zprávu, hledat barvy buněk a číst nápovědy pro nová varování. Přímější a podrobnější informace lze získat pomocí funkce Doporučení v IBAnalyst. Stačí načíst statistiky a přejít do nabídky Zprávy/Zobrazit doporučení. Tato zpráva poskytuje analýzu krok za krokem, včetně podrobnějších popisných varování o vynucených zápisech, intervalu sweep, aktivitě databáze, stavu transakcí, velikosti stránek databáze, provádění sweep, stránkách inventáře transakcí, fragmentovaných tabulkách, tabulkách s mnoha verzemi záznamů, hromadných mazáních/aktualizacích, hlubokých indexech, indexech nevhodných pro optimalizátor, zbytečných indexech a dokonce i prázdných tabulkách. Všechny tyto informace a doprovodná doporučení jsou dynamicky vytvářeny na základě načtených statistik.

Jako příklad výstupu zprávy se podívejme na zprávu vygenerovanou pro statistiky databáze, které jste viděli dříve v tomto článku:

„Celková velikost stránek inventáře transakcí (TIP) je velká - 94 kilobajtů nebo 23 stránek. Transakce Read_committed používá globální TIP, ale snapshot transakce vytvářejí vlastní kopie TIP v paměti. Velká velikost TIP může zpomalit výkon. Zkuste spustit sweep ručně (gfix -sweep), abyste zmenšili velikost TIP.“

Zde je další citace z části zprávy o tabulkách/indexech:

„Počet verzovaných tabulek: 8. Velké množství verzí záznamů obvykle zpomaluje výkon. Pokud je v tabulce mnoho verzí záznamů, garbage collection nefunguje nebo záznamy nejsou čteny žádným příkazem select. Můžete zkusit select count(*) na těchto tabulkách pro vynucení garbage collection, ale to může trvat dlouho (pokud existuje mnoho verzí a neunikátních indexů) a může to být neúspěšné, pokud existuje alespoň jedna transakce, která má o tyto verze zájem.

Zde je seznam tabulek s poměrem verzí/záznamů větším než 3:


Tabulka	Záznamy	Verze	Velikost Rec/Vers
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Shrnutí

IBAnalyst je neocenitelný nástroj, který uživateli pomáhá provádět podrobnou analýzu statistik databáze Firebird nebo InterBase a identifikovat možné problémy s databází z hlediska výkonu, údržby a interakce aplikace s databází. Převádí nesrozumitelné statistiky databáze do snadno pochopitelné grafické podoby a automaticky navrhuje rozumná doporučení pro zlepšení výkonu databáze a usnadnění její údržby.

1 InterBase 7.5 a Firebird 1.5 mají speciální funkce, které mohou pravidelně zapisovat neuložené stránky, pokud je Vynucené zápisy vypnuto.

2 Nejstarší transakce je stejná jako nejstarší zajímavá transakce, zmiňovaná všude. Výstup gstat tuto transakci nezobrazuje jako „zajímavou“.

3 Ann Harrison říká, že Nejstarší aktivní je nejstarší transakce, která byla aktivní, když začala aktuální nejstarší aktivní transakce. Pro aplikace zde není velký rozdíl.