45 způsobů, jak zrychlit databázi Firebird

Zde najdete seznam tipů pro výkon databáze Firebird v různých oblastech – od hardwaru/OS a ladění konfigurace Firebird až po doporučení pro optimalizaci SQL. Tento seznam není kompletním průvodcem, jak optimalizovat Firebird, a předpokládá, že rozumíte základům fungování Firebird, jako jsou plány provádění, správa transakcí a statistiky výkonu dotazů.

Tyto tipy používejte s opatrností a ověřte jejich účinek, než je nasadíte do produkce.

Naše společnost (IBSurgeon) nabízí komplexní službu optimalizace výkonu databáze.

1. Umístěte databázi na SSD

Umístěte svou databázi na SSD. SSD disk poskytuje mnohem lepší náhodný I/O než tradiční disky. Náhodný I/O je kritický pro čtení a zápis dat rozložených ve velkém databázovém souboru – většina databázových operací vyžaduje intenzivní paralelní náhodný I/O.

2. Použijte RAID 10

Pokud používáte RAID1 nebo RAID5, zvažte RAID10 – je o 15–25 % rychlejší.

3. Zkontrolujte BBU

Pokud používáte RAID řadič, zkontrolujte, že má nainstalovanou a funkční záložní baterii (BBU) – někteří výrobci BBU ve výchozím nastavení nedodávají. Bez BBU řadič vypne cache a RAID funguje velmi pomalu, dokonce pomaleji než běžné SATA disky. Stav BBU obvykle můžete zkontrolovat v nástroji pro konfiguraci RAID.

4. Nastavte write cache na write-back

Pokud používáte RAID řadič s nainstalovanou BBU (a server s UPS), zkontrolujte, že jeho cache je nastavena na write-back (nikoli write-through). „Write-back“ zapíná zapisovací cache řadiče.

5. Zapněte read cache

Pokud používáte RAID řadič, zkontrolujte, že má zapnutou read cache.

6. Zkontrolujte diskový subsystém

Zkontrolujte své disky na vadné bloky a další hardwarové problémy (včetně přehřívání). Hardwarové problémy mohou výrazně snížit výkon I/O a vést k poškození databáze.

7. Použijte SuperClassic nebo Classic ve Firebird 2.5

Pokud používáte Firebird 2.5 SuperServer s mnoha připojeními, zkuste použít SuperClassic nebo Classic – mohou se lépe škálovat využitím všech jader CPU.

8. Použijte SuperServer 3.0 ve Firebird 3.

Pokud používáte Classic nebo SuperClassic ve verzi 2.5, zvažte migraci na Firebird 3.0 SuperServer – nyní může využívat více jader a kombinovat to s výhodami sdílené cache.

9. Zvětšete cache stránkových bufferů

Zvětšete velikost cache stránkových bufferů (parametr DefaultDBCachePages) oproti výchozím hodnotám. Pro 2.5 SuperServer doporučujeme 10000 stránek, pro 3.0 SuperServer – 50000 stránek, pro Classic a SuperClassic – od 256 do 2048 stránek. Nicméně nenastavujte hodnotu cache stránkových bufferů příliš vysoko – synchronizace cache má svou cenu a myšlenka umístit celou databázi do RAM pomocí ladění této hodnoty nebude fungovat. Použijte předoptimalizované konfigurační soubory Firebird zde: /cs/optimized-firebird-configuration/

10. Zvětšete velikost paměti pro operace řazení

Zvětšete hodnotu parametru TempCacheLimit v firebird.conf – určuje velikost cache dočasného prostoru pro řazení. Výchozí hodnoty jsou příliš nízké (8 Mb pro Classic a 64 Mb pro SuperServer), použijte alespoň 64 Mb pro Classic a 1 Gb pro SuperServer a SuperClassic. Opět použijte optimalizované konfigurační soubory z bodu #9.

11. Nastavte Forced Writes na Off (s opatrností!)

Pokud máte intenzivní aktivitu vkládání nebo aktualizací (můžete ji zkontrolovat pomocí HQbird MonLogger, podrobnosti viz strana 60 v HQbird User Guide) a pokud máte nainstalovanou UPS a replikaci pro ochranu před hardwarovými poruchami, zvažte nastavení Forced Writes na OFF – může to zvýšit rychlost zápisových operací až 3krát.

12. Zvětšete počet hash slotů pro Classic/SuperClassic

Zvětšete hodnotu parametru LockHashSlots pro Classic a SuperClassic z výchozích 1009 na nějaké velké prvočíslo (například 30011), sníží to fronty v interním zamykacím mechanismu.

13. Použijte CPU Affinity pro Super Server 2.5

Pokud používáte SuperServer 2.5, nastavte parametr CPUAffinity na hodnotu rovnou počtu používaných databází: SuperServer ve verzi 2.5 může používat různá jádra CPU ke zpracování požadavků pro určité databáze.

14. Použijte rychlý disk pro dočasný prostor

Nastavte první část parametru TempDirectory v firebird.conf na rychlý disk – SSD nebo RAM disk. Sníží to dobu velkých řazení – například když se databáze obnovuje.

15. Ukládejte zálohy databáze na jiný disk

Ukládejte zálohy databáze na vyhrazený fyzický disk (RAID). Oddělí to čtecí a zapisovací I/O během zálohování a zvýší rychlost zálohování a sníží zátěž hlavního disku. To je obzvláště důležité, když se zálohy pořizují, zatímco uživatelé s databází pracují. Více podrobností o hardwarové konfiguraci pro Firebird naleznete v " Firebird Hardware Guide".

16. Deaktivujte indexy pro hromadné vkládání

Pokud vkládáte nebo aktualizujete mnoho záznamů (více než 25 % tabulky), deaktivujte indexy pro tabulku, do které se záznamy vkládají, a po vložení nebo aktualizaci je znovu aktivujte. Operace přestavby indexu může být rychlejší než mnoho aktualizací indexu.

17. Použijte Global Temporary Tables pro rychlé vkládání

Pro zrychlení vkládání a aktualizací použijte Global Temporary Tables pro hromadné vkládání velkých sad záznamů a poté záznamy přeneste do trvalé tabulky. Může být velmi efektivní vkládat záznamy do GTT, předzpracovat je a poté je přesunout do trvalé tabulky.

18. Vyhněte se zbytečným indexům

Používejte méně indexů pro tabulky s intenzivním vkládáním a aktualizacemi. Každý index přidává značnou režii pro operace vkládání, aktualizace, mazání a garbage collection – při vložení/aktualizaci/smazání/vyčištění jednoho záznamu může dojít ke 3–4 dalším čtením a zápisům stránek pro každý index.

19. Nahraďte UDF voláními vestavěných funkcí

Nahraďte volání UDF voláními vestavěných funkcí. V nedávných verzích Firebird bylo přidáno mnoho vestavěných funkcí, které nabízejí funkčnost dříve dostupnou pouze v knihovnách UDF. Nahraďte takové funkce, kde je to možné, protože vestavěné funkce pracují až 3krát rychleji než UDF.

20. Používejte read-only transakce pro čtecí operace

Používejte read-only transakce pro operace, které nemění záznam (tj. SELECTy) s režimem izolace = read committed. Takové transakce nezadržují verze záznamů před garbage collection a mohou běžet neomezeně dlouho: neovlivňují výkon databáze.

21. Používejte krátké zapisovací transakce a zbavte se VŠECH dlouho běžících

Používejte krátké zapisovatelné transakce (pro operace INSERT/UPDATE/DELETE).

Čím kratší zapisovatelná transakce je, tím lépe. Krátké transakce zadržují úměrně méně verzí záznamů před garbage collection než dlouho běžící. Bohužel i jediná dlouho běžící transakce (například ponechaná otevřená z vývojového nástroje) může zničit dobrý efekt všech ostatních krátkých zapisovatelných transakcí. Proto je třeba monitorovat dlouho běžící transakce a opravit příslušná místa ve zdrojovém kódu. Použijte nástroj HQbird DataGuard k získání upozornění na nejstarší aktivní transakci v databázi Firebird (které aplikace ji spustily, jaká IP adresa, časové razítko jejího spuštění) a nástroj HQbird MonLogger k zobrazení úplného seznamu dlouho běžících aktivních transakcí a jejich statistik I/O. Také pokud používáte komponenty/knihovny pro přístup k databázi, které mohou cachovat sady záznamů, použijte cachované aktualizace.

22. Vyhněte se dlouhým řetězcům záznamů

Vyhněte se situacím, kdy jeden záznam má mnoho verzí záznamu – Firebird pracuje s dlouhými řetězci záznamů mnohem pomaleji. (Chcete-li zjistit, kolik verzí záznamů mají některé tabulky a jaký je nejdelší řetězec záznamů, můžete použít nástroj HQbird IBAnalyst, záložka Tables, seřadit podle “Max Version”). Použijte kombinaci vkládání a plánovaného mazání starých záznamů místo mnohočetných aktualizací stejného záznamu.

23. Používejte PREPARE správně

Používejte připravené příkazy (prepared statements) pro spouštění SQL dotazů, kde se mění pouze parametry – například proveďte prepare před smyčkou takových dotazů. Prepare může zabrat značný čas (zejména u velkých tabulek) a příprava dotazu pouze jednou výrazně zvýší celkový výkon.

24. Neprovádějte COMMIT příliš často během hromadné operace insert/update

V případě hromadné operace INSERT/UPDATE/DELETE neprovádějte commit transakce po každé změně (může k tomu dojít, pokud používáte možnost auto commit ve vašem databázovém ovladači) – provádějte commit transakcí alespoň po 1000 operacích nebo více. Každý commit transakce spustí několik čtecích/zapisovacích I/O operací vůči databázi, proto časté commity snižují výkon databáze.

25. „Vypněte“ indexy, pokud používáte IN s mnoha konstantami

Pokud používáte konstrukci WHERE fieldX IN (Constant1, Constant2,… ConstantN) a na fieldX existuje index, Firebird použije index tolikrát, kolik je konstant v seznamu IN. Zakažte vyhledávání v indexu převedením fieldX na výraz +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), nebo pro řetězce použijte fieldX||''

26. Nahraďte IN spojením JOIN

Vyhněte se používání dotazů s vnořenými WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), může to zmást optimalizátor Firebird. Transformujte vnořené IN na spojení.

27. Používejte LEFT JOIN správným způsobem

Pokud používáte LEFT OUTER spojení, výslovně seřaďte tabulky ve spojení od nejmenší k největší.

28. Omezte načítání SELECT dotazů

Vždy se snažte omezit velký výstup SELECT dotazů pomocí klauzulí FIRST… SKIP nebo ROWS. Pokud dotaz není speciálně navržen jako report (který vyžaduje vytištění/export všech záznamů), obvykle stačí zobrazit top 10–100 záznamů. Načítejte pouze nezbytné záznamy.

29. Zadejte menší počet sloupců v SELECT s ORDER BY/GROUP BY

Snižte počet sloupců a jejich celkovou šířku v dotazech s ORDER BY/GROUP BY jak v části SELECT (tj. pole, která se mají zobrazit), tak v klauzuli ORDER BY. Firebird slučuje sloupce z klauzulí SELECT a ORDER BY/GROUP BY a řadí je v paměti (nebo, pokud paměť nestačí, na disku). Takže pokud je v SELECT dlouhý VARCHAR, velikost souborů pro řazení může být opravdu velká (mnoho gigabajtů). Snížení počtu polí pouze na ta, která musí být seřazena, a pozdní spojení s velkými poli, která se mají zobrazit, může výrazně (3–10krát) zvýšit rychlost dotazu s ORDER BY/GROUP BY.

30. Použijte odvozené tabulky k optimalizaci SELECT s ORDER BY/GROUP BY

Dalším způsobem, jak optimalizovat SQL dotaz s řazením, je použití odvozených tabulek k vyhnutí se zbytečným operacím řazení. Místo

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

použijte následující úpravu:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. Ukládejte krátké řetězce ve VARCHAR, dlouhé v BLOBech

Pro ukládání krátkých znakových dat používejte VARCHARy, pro ukládání dlouhých textů používejte BLOBy. Varchary jsou rychlejší pro malé kousky dat, protože jsou uloženy v záznamu a celý záznam se čte během stejného I/O cyklu, a pokud je velikost záznamu menší než 2/3 velikosti databázové stránky, celý záznam je uložen na stejné databázové stránce. BLOBy jsou uloženy mimo záznam a vyžadují další kolo I/O pro jejich načtení a svou výhodu ukazují při čtení a zápisu dlouhých řetězců.

32. Vylučte BLOB sloupce z velkých SELECTů

Vylučte BLOB sloupce z velkých SELECTů. Použijte jakýsi late binding s poddotazy k selektivnímu zobrazení informací z BLOBů (například zobrazení obsahu dokumentu).

33. Použijte BIGINT pro primární a jedinečné klíče

Použijte typ BIGINT pro automaticky inkrementované primární a jedinečné klíče a pro identifikátory všech typů. Operace s BIGINT jsou nejrychlejší a BIGINT má dostatečnou kapacitu pro uložení téměř všech datových rozsahů.

34. Nepoužívejte VARCHAR pro klíče

Nepoužívejte VARCHAR pro identifikátory, pokud to není skutečně nutné – operace s nimi jsou mnohem méně efektivní než s celočíselnými sloupci. Zejména se vyhněte GUIDům jako identifikátorům – kvůli náhodnému rozložení hodnot GUID mohou být operace INSERT/UPDATE s GUID primárními/jedinečnými klíči až 20krát pomalejší než s celými čísly.

35. Přepočítávejte statistiky indexů

Pravidelně přepočítávejte statistiky indexů. Aktualizujte statistiky indexů pro tabulky s častými nebo rozsáhlými změnami pomocí příkazu SET STATISTICS, umožní to optimalizátoru Firebird zvolit lepší SQL plány. HQbird Firebird DataGuard může provádět takový přepočet statistik indexů automaticky podle požadovaného plánu (obvykle jednou týdně).

36. Používejte connection pool

Pokud jsou připojení k databázi Firebird krátká (typické pro webové stránky), používejte connection pool – například v PHP použijte funkci ibase_pconnect místo ibase_connect

37. Používejte volbu LINGER ve Firebird 3.0

Pokud jsou připojení k databázi krátká a používáte Firebird 3+, použijte volbu LINGER pro udržení aktivní cache po zadanou dobu; udrží často používané stránky v cache, i když nebudou existovat žádná další připojení. Například ALTER DATABASE SET LINGER TO 60 udrží cache po dobu 60 sekund po ukončení posledního připojení.

38. Používejte HASH JOIN

Ve Firebird 3.0 může být v případě spojování velkých a malých tabulek HASH JOIN mnohem rychlejší než běžný join, který používá „nested loop“ s indexem. Abyste přiměli optimalizátor Firebird použít HASH join, použijte +0 v podmínce spojení: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Před nasazením do produkce zkontrolujte výsledek optimalizace!

39. Označte příslušné PSQL funkce jako DETERMINISTIC

Označte své PSQL funkce (ve Firebird 3+), které nemají parametry a vracejí konstantní hodnoty, klíčovým slovem DETERMINISTIC. Deterministické funkce jsou vypočítány a uloženy do cache v rámci aktuálního dotazu.

40. Používejte analytické (okenní) funkce ve Firebird 3.0

Pokud spouštíte SELECT se současným výstupem nějakého sloupce a agregované funkce pro něj, použijte okenní (analytické) funkce – je to rychlejší než poddotaz nebo 2 dotazy. Například:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

nahraďte za

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. Používejte přepínač -se pro gbak

Použijte přepínač -se pro zrychlení zálohování a/nebo obnovy gbak až o 20 %, například

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

Nejrychlejší způsob zpracování záznamů načtených kurzorem v PSQL je klauzule ‚where current of <>‘. Je rychlejší než ‚where rb$db_key = :v_db_key‘ a mnohem rychlejší než vyhledávání pomocí primárního nebo jedinečného klíče.

43. Vyhněte se častým dotazům na monitorovací tabulky

Nespouštějte dotazy na monitorovací tabulky Firebird (MON$) příliš často – takové dotazy spotřebovávají značné prostředky a mohou výrazně snížit výkon hlavní obchodní logiky. Doporučujeme spouštět dotazy MON$ ne častěji než jednou za minutu. Pro nepřetržité monitorování dotazů/transakcí/připojení Firebird použijte nástroj HQbird PerfMon, který podporuje Trace API (podrobnosti viz strana 66 HQbird User Guide).

44. Používejte volbu NO_AUTO_UNDO pro hromadné vkládání/aktualizace

Pokud spouštíte mnoho příkazů DML (Update/Insert/Delete) v rámci stejné transakce, Firebird slučuje undo-log každého příkazu s undo-logem transakce. Pro zrychlení hromadných operací DML spusťte transakci s volbou «NO AUTO UNDO», abyste neslučovali undo-logy každého příkazu s undo-logem transakce.

45. Nepoužívejte autentizaci SRP ve Firebird 3, pokud ji nepotřebujete

Nepoužívejte autentizaci uživatelů SRP (Firebird 3.0+), pokud ji skutečně nepotřebujete – připojení s autentizací SRP se navazuje pomaleji než běžné připojení.

Namísto shrnutí

Optimalizace výkonu vyžaduje zvážení mnoha faktorů a může být skutečně záludná. Pokud jste vyzkoušeli všechny výše uvedené věci, zvažte najmutí profesionální služby optimalizace výkonu databáze.

Kontaktujte nás

Máte nějaké otázky? Neváhejte nás kontaktovat prostřednictvím e-mailu!