Kontakty
IBSurgeon
Tato stránka byla strojově přeložena. Přečtěte si anglický originál. English

Knihovna IBSurgeon

IBAnalyst: Tipy a triky

Tento text byl původně napsán v roce 2012, platí pro verze 1.0 - 2.5, ve verzích 3.0-5.0 došlo k mnoha změnám, které nemohly být zohledněny. Přečtěte si prosím dokumentaci nebo nás kontaktujte pro podporu: [email protected].

Některé otázky, na které není odpovězeno v doporučeních a/nebo nápovědě IBAnalyst:

1. Jak znovu vytvořit indexy na PRIMARY, FOREIGN nebo UNIQUE omezeních?

A: Pro verze Firebird 1.0-2.5. Ano, nemůžete použít ALTER INDEX xxx INACTIVE/ACTIVE na indexech omezení. Pokud vidíte hluboký nebo fragmentovaný index na tomto omezení, můžete použít speciální trik (používaný gbak při obnově):

RDB$INDICES má příznak RDB$INDEX_INACTIVE, který je null nebo 0, pokud je index aktivní (po CREATE INDEX nebo ALTER INDEX ACTIVE). 1 znamená, že index je neaktivní (po ALTER INDEX INACTIVE). Existuje ale také hodnota 3, která se používá k označení neaktivních indexů na omezeních. Můžete tedy nastavit RDB$INDEX_INACTIVE=3 pro tento index, provést COMMIT, a poté vrátit hodnotu na 0 a znovu provést commit - index bude znovu vytvořen.

Pro Firebird 3.0-5.0 - jednoduše proveďte ALTER INDEX indexname ACTIVE

2. Použil jsem všechna doporučení IBAnalyst, ale nepomohlo to k urychlení dotazů.

A: Toto je samostatný problém, se kterým IBAnalyst nemůže pomoci. Zde mohou být 2 příčiny problému:

  1. Indexy mají zastaralé statistiky. Statistiky indexů můžete obnovit příkazem SET STATISTICS INDEX xxx (více podrobností http://www.ibase.ru/proc_selectivity/).

  2. Pro některou podmínku použitou v dotazu prostě neexistuje vhodný index.

  3. Dotazy jsou velmi složité, nebo optimalizátor nemůže dotaz optimalizovat, takže je nutné dotaz refaktorovat.

  4. V některých případech uvidíte “fragmentované tabulky” hned po obnově.

Normálně Firebird a InterBase (bez parametru -use_all_space) rezervují asi 25% místa na datových stránkách pro budoucí vkládání, aktualizace nebo mazání (pro umístění verzí záznamů). Ale při jakékoli velikosti databázové stránky (1, 2, 4 nebo 8 kB) uvidíte ~50% fragmentaci u tabulek, které mají malou velikost záznamu (asi ~12-20 bajtů, například tabulka se 2 celočíselnými poli má průměrnou velikost záznamu = 12 bajtů).

To je v pořádku, považujte to za nějaké magické číslo serveru (nebo chování).

Takže pokud máte takové tabulky s malými záznamy, můžete:

a) ignorovat varování “fragmentováno” pro tyto tabulky

b) snížit “fragmentaci %” na 45%, například, v dialogu Možnosti IBAnalyst.

4. Verze záznamů pro tabulku, která nesmí být aktualizována

Pokud vidíte verze záznamů u tabulky, která nesmí být aktualizována (například tabulka s nějakým logem událostí) - nebojte se, tyto verze jsou generovány mazáním.

Takže budete vědět, kolik aktuálních záznamů je v tabulce a kolik záznamů bylo smazáno.

To platí pouze pokud MaxVer = 1. Pokud je > 1, pak je tato tabulka aktualizována nějakou aplikací. Pokud jste si opravdu jisti, že tato tabulka nesmí být nikdy aktualizována, je lepší nastavit trigger “before update” s výjimkou, abyste zjistili, která aplikace provádí aktualizace.

5. Bloby mohou způsobit fragmentaci tabulky.

Engine ukládá bloby 3 různými způsoby:

  1. Pokud se obsah blobu vejde na datovou stránku (dostatek volného místa), bude uložen na této datové stránce poblíž svého záznamu (nebo verze).

  2. Pokud se obsah blobu nevejde na datovou stránku, bude uložen na samostatné stránce.

  3. Pokud se v případě 2 blob nevejde na jednu datovou stránku, je vytvořena stránka ukazatelů, která ukazuje na příslušné stránky blobu.

Případ 1 nastává v závislosti na uložené velikosti blobu a velikosti databázové stránky. Například pokud máte velikost stránky 4K a bloby s průměrnou velikostí ~5K, nejsou uloženy na datových stránkách, ale na dalších stránkách blobu.

Ale pokud zazálohujete databázi a obnovíte ji s velikostí stránky 8K, bloby se vejdou na datovou stránku a budou uloženy se záznamy, což způsobí vysokou fragmentaci záznamů.

IBAnalyst označuje tyto tabulky jako Pale (sloupec Records) a nápověda ukazuje odhadované záznamy pro tuto tabulku (na základě počtu datových stránek) a skutečnou průměrnou hodnotu zaplnění (%).

Pokud váš dotaz čte z této tabulky jakákoli pole kromě blobů, přirozené prohledávání, spojení nebo agregace poběží velmi pomalu.

Jediné řešení, jak se tomu vyhnout: vytvořte další tabulku (propojenou 1-1 s původní tabulkou) a přesuňte do ní všechny sloupce blobů, které mají průměrnou velikost menší než velikost stránky.

V tomto případě se nepokoušejte zálohovat/obnovovat s větší velikostí stránky! To způsobí, že bloby, které se nevešly na datové stránky při aktuální velikosti stránky, budou při obnově s větší velikostí stránky umístěny na datové stránky. Vaše tabulky s bloby tak budou fragmentovanější než dříve.

Také se nedoporučuje obnovovat s menší velikostí stránky, protože to může snížit výkon pro indexy a tabulky bez blobů.

Také byste se neměli snažit změnit pole blobů na pole varchar - pole varchar jsou vždy ukládána jako součást záznamu, takže záznam může mít 2 nebo více fragmentů (být umístěn na 2 nebo více datových stránkách), pokud se nevejde na datovou stránku.

p.s. IBAnalyst může tyto tabulky hlásit “omylem”, například tabulka měla pole blobů s daty, ale byla odstraněna ze struktury tabulky. Bohužel pro toto varování neexistuje konfigurovatelná možnost, protože to počítáme přesně z dat hlášených serverem (statistiky).

6. Vztah VerLen a RecLength

a) VerLen >= 90% RecLength: verze, které vidíte ve sloupci Version, jsou většinou mazání záznamů. Čím více záznamů je smazáno, tím menší bude RecLength (až 0 bajtů). VerLen může být také větší než RecLen, pokud aktualizujete tabulku s většími řetězcovými daty, než byla uložena v původních záznamech.

b) VerLen <= 80% RecLength: verze jsou většinou aktualizace záznamů.

Tyto případy nemůžeme rozlišit přesněji, protože statistiky ukazují průměrnou velikost záznamu a verze pro celou tabulku, zatímco viditelný počet verzí pro souběžné transakce se může lišit.

7. Proč IBAnalyst označuje některé indexy jako “špatné”?

Indexy s hodnotou selektivity nižší než 0,01 jsou v IBAnalyst označeny jako “špatné” (viz nápověda zobrazení Index). Existuje několik důvodů, proč označit konkrétní index jako špatný:

  1. Selektivita tohoto indexu je nižší než 0,01. Teoreticky by optimalizátor neměl tento index používat, ale používá ho, pokud neexistují žádné jiné indexy (pro where, order by nebo join klauzuli, přinejmenším).

  2. Takový index způsobuje velmi pomalou garbage collection. Tento problém neexistuje v InterBase 7.1/7.5 a bude opraven ve Firebirdu 2.0.

  3. Tento index zpomaluje proces obnovy a je vytvářen velmi pomalu (create/alter index active). To je proto, že řetězec čísel záznamů je velký pro jeden klíč indexu.

  4. Pokud je tento index použit v where klauzuli, využití paměti bude záviset na hledané hodnotě (velikost bitmasky). Protože řetězec záznamů může být velký (mnoho duplicit klíčů), spotřeba paměti bude také velká.

  5. Pokud je tento index použit v “order by” a mnoho duplicit je většinou v nižších hodnotách klíčů (v závislosti na pořadí řazení indexu), bude mnoho čtení stránek indexu, což zpomalí dotaz.

To je důvod, proč IBAnalyst nemůže ignorovat existenci takových indexů.

Nejhorší případ pro index je, když má sloupec Uniques = 1, tj. všechny hodnoty pro indexovaný sloupec jsou stejné. Tyto indexy jsou uvedeny v “Useless indices” na stránce Summary.

Samozřejmě pro vaši aplikaci může být takový index “dobrý”. Například pokud záznamy mají příznak “archiv” v nějakém sloupci a vaše aplikace vyhledává podle indexu na tomto sloupci pouze aktuální, ne archivovaná data. Takže je na vás, zda máme pravdu, když tento index označujeme jako “špatný”, nebo ne.

8. Co když je “špatný” index vytvořen omezením Foreign Key?

Předchozí odstavec ukazuje, že je lepší zahodit “špatné” indexy (pokud je nepoužíváte k vyhledávání klíčů s menším počtem duplicit než jiné klíče). Ale pokud je takový index vytvořen cizím klíčem, můžete ho zahodit pouze zrušením cizího klíče. Zrušení cizího klíče zakáže kontrolu vztahů, což může být nepřijatelné.

Můžete nahradit FK triggery, ale s určitými omezeními. FK kontroluje vztahy záznamů pomocí indexu a index “vidí” všechny klíče pro všechny záznamy nezávisle na stavu transakcí. Ale triggery fungují pouze v kontextu transakce klienta. Takže při nahrazení FK triggery si musíte být jisti, že:

  • Záznamy nebudou mazány z hlavní tabulky, nebo budou mazány v režimu “snapshot table reserving”
  • Sloupec použitý PK v hlavní tabulce nebude nikdy modifikován. Můžete to omezit triggerem before update.

Pokud tyto podmínky dodržíte, můžete zrušit konkrétní Foreign Key. Samozřejmě nevytvářejte index ručně na tomto sloupci.

9. Proč v řádku procent verze dat je pouze 12 megabajtů dat, ale mám databázi 140 megabajtů?

  1. IBAnalyst zde ukazuje “čistý” objem dat, bez započtení ostatních databázových struktur (indexy, metadata…) a fragmentace stránek.

  2. Po obnově InterBase a Firebird ponechávají nějaké volné místo (15-25%) na datových stránkách pro rychlejší budoucí aktualizace/mazání.

  3. Existuje specifické chování serveru, kdy ponechává datové stránky fragmentované asi o 50%, pokud je velikost záznamu tabulky nízká, asi 11-22 bajtů.

10. Jak zlepšit výkon optimalizátoru v případě častých aktualizací

Statistiky indexů jsou uloženy ve sloupci RDB$INDICES.RDB$STATISTICS a jsou aktualizovány 3 způsoby:

  1. SET STATISTICS INDEX

  2. ALTER INDEX ACTIVE, nebo CREATE INDEX …

  3. proces obnovy (všechny indexy jsou znovu vytvářeny stejně jako “ALTER INDEX ACTIVE”)

Optimalizátor používá tyto statistické informace k přípravě dotazů. Pomocí hodnot statistik může optimalizátor rozhodnout, že index je “dostatečně dobrý” nebo “nepoužitelný” pro získávání záznamů.

Pokud statistiky nebyly dlouhou dobu aktualizovány, může optimalizátor vytvořit špatný plán, protože stávající hodnoty statistik neodpovídají skutečnému stavu, protože data v tabulce se mohou výrazně změnit (například počet záznamů se zvýšil 5-10krát, nebo naopak všechny záznamy byly smazány).

Můžete nahradit špatný automatický plán dotazu explicitním PLAN pro konkrétní dotaz, ale to není dobrý přístup, protože data se mohou po vytvoření plánu výrazně změnit.

Alternativní (a správný) způsob je pravidelně obnovovat statistiky pomocí příkazu SET STATISTICS pro všechny indexy. Můžete naplánovat spuštění SQL skriptu pro obnovení statistik pomocí ISQL nebo použít hotový nástroj gidx (pouze Windows).

Pokud máte některé tabulky s periodicky znovu načítanými různými záznamy, tento přístup nepomůže. Zvažme příklad:

  • Tabulka A je načítána daty 4-5krát denně.
  • Po zpracování načtených dat jsou všechny záznamy v tabulce A smazány.

V tomto případě můžeme vidět 2 správné hodnoty statistik pro indexy na tabulce A - když je načtena daty, a když je prázdná. Takže statistiky přepočítané na načtené tabulce budou nepoužitelné, když je tabulka prázdná, a naopak.

Abyste se tomu vyhnuli, musíte přepočítat statistiky pro indexy na tabulce A pouze tehdy, když je tabulka naplněna daty. Nejlépe před spuštěním dotazů na tuto tabulku.

Od verze 1.91 IBAnalyst zobrazuje rozdíl statistik indexů a umožňuje je kdykoli přepočítat. Nejprve se musíte podívat na informace o záznamech tabulky - je to obvyklý průměrný počet záznamů nebo ne. Pokud ano, můžete selektivitu indexu bezpečně přepočítat. Pokud ne - možná bude lepší se statistik indexů nedotýkat, protože to může způsobit, že optimalizátor vytvoří ještě horší plány dotazů.