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

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

Tyto tipy prosím aplikujte s opatrností a před nasazením do produkce ověřte jejich účinek.

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

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

Umístěte svou databázi na SSD. SSD disky poskytují mnohem lepší náhodné IO než tradiční disky. Náhodné IO 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é IO.

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í bateriovou jednotku (BBU) - někteří výrobci BBU ve výchozím nastavení neposkytují. Bez BBU řadič zakáže cache a RAID pracuje velmi pomalu, dokonce pomaleji než běžné SATA disky. Obvykle můžete stav BBU zkontrolovat v nástroji pro konfiguraci RAID.

4. Nastavte zápisovou cache na write-back

Pokud používáte RAID řadič s nainstalovanou BBU (a server s UPS), zkontrolujte, že je jeho cache nastavena na write-back (ne write-through). „Write-back“ umožňuje zápisovou cache řadiče.

5. Povolte čtecí cache

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

6. Zkontrolujte diskový subsystém

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

7. Použijte SuperClassic nebo Classic ve Firebirdu 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 Firebirdu 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. Zvyšte cache stránkových bufferů

Zvyšte velikost cache stránkových bufferů (parametr DefaultDBCachePages) z výchozích hodnot. 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 představa, že touto hodnotou umístíte celou databázi do RAM, nebude fungovat. Použijte předoptimalizované konfigurační soubory Firebirdu zde: /cs/optimized-firebird-configuration/

10. Zvyšte velikost paměti pro třídicí operace

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

11. Vypněte Forced Writes (s opatrností!)

Pokud máte intenzivní vkládací nebo aktualizační aktivitu (můžete ji zkontrolovat pomocí HQbird MonLogger, podrobnosti viz strana 60 Uživatelské příručky HQbird), a pokud máte nainstalovanou UPS a replikaci pro ochranu před selháním hardwaru, zvažte nastavení Forced Writes na OFF - může to zvýšit rychlost zápisových operací až 3krát.

12. Zvyšte počet hash slotů pro Classic/SuperClassic

Zvyšte hodnotu parametru LockHashSlots pro Classic a SuperClassic z výchozích 1009 na nějaké velké prvočíslo (například 30011) - sníží se tím fronty ve vnitřní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íží se tím čas velkých třídění - například při obnově databáze.

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

Ukládejte zálohy databáze na vyhrazený fyzický disk (RAID). Oddělí se tím čtecí a zápisové IO během zálohování, zvýší se rychlost zálohování a sníží se zatížení hlavního disku. Je to obzvláště důležité, když jsou zálohy pořizovány, zatímco uživatelé pracují s databází. Více podrobností o hardwarové konfiguraci pro Firebird najdete 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řebudování indexu může být rychlejší než mnoho aktualizací indexu.

17. Použijte globální dočasné tabulky pro rychlé vkládání

Pro urychlení vkládání a aktualizací použijte globální dočasné tabulky pro hromadné vkládání velkých sad záznamů a poté přeneste záznamy 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á významnou 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 k 3-4 dalším čtením a zápisům stránek pro každý index.

19. Nahraďte UDF vestavěnými funkcemi

Nahraďte volání UDF voláními vestavěných funkcí. V nedávných verzích Firebirdu 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 transakce pouze pro čtení pro čtecí operace

Používejte transakce pouze pro čtení pro operace, které nemění záznamy (tj. SELECTy) s izolačním režimem = 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é zápisové transakce a zbavte se VŠECH dlouhoběžících

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

Čím kratší je zapisovatelná transakce, tím lépe. Krátké transakce zadržují proporcionálně méně verzí záznamů před garbage collection než dlouhoběžící. Bohužel i jediná dlouhoběžící transakce (například ponechaná otevřená z vývojového nástroje) může zhatit dobrý efekt všech ostatních krátkých zapisovatelných transakcí. Proto musíte monitorovat dlouhoběžící transakce a opravit příslušná místa ve zdrojovém kódu. Použijte nástroj HQbird DataGuard pro příjem upozornění o nejstarší aktivní transakci v databázi Firebird (která aplikace ji spustila, jakou IP adresu, časové razítko jejího startu) a nástroj HQbird MonLogger pro zobrazení úplného seznamu dlouhoběžících aktivních transakcí a jejich IO statistik. Také, pokud používáte databázové přístupové komponenty/knihovny, které mohou cacheovat sady záznamů, použijte cached updates.

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

Vyhněte se situacím, kdy má jeden záznam mnoho verzí - Firebird pracuje mnohem pomaleji s dlouhými řetězci záznamů. (Chcete-li vidět, 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řaďte podle „Max Version“). Použijte kombinaci vkládání a plánovaného mazání starých záznamů místo vícenásobných aktualizací stejného záznamu.

23. Používejte PREPARE správně

Používejte připravené příkazy 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řipravení dotazu pouze jednou výrazně zvýší celkový výkon.

24. Necommitujte příliš často během hromadné operace vkládání/aktualizace

V případě hromadné operace INSERT/UPDATE/DELETE necommitujte transakci po každé změně (může se to stát, pokud používáte možnost auto commit ve vašem databázovém ovladači) - commitujte transakci alespoň po 1000 operacích nebo více. Každý commit transakce provádí několik čtecích/zápisových IO operací proti 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í pomocí 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 pomocí 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 Firebirdu. Transformujte vnořené IN na joiny.

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

Pokud používáte LEFT OUTER joiny, explicitně umístěte tabulky v joinu od nejmenší po 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í navržen specificky jako sestava (která vyžaduje vytištění/export všech záznamů), obvykle stačí zobrazit prvních 10-100 záznamů. Načítávejte pouze potřebné záznamy.

29. Specifikujte méně 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 k zobrazení), tak v klauzuli ORDER BY. Firebird slučuje sloupce z SELECT a klauzulí ORDER BY/GROUP BY a třídí je v paměti (nebo, pokud není paměť dostatečná, na disku). Pokud je tedy v SELECT dlouhý VARCHAR, velikost třídicích souborů může být opravdu velká (mnoho gigabajtů). Snížení počtu polí pouze na ta, která musí být tříděna, a pozdější join s velkými poli k zobrazení může výrazně (x3-x10) zvýšit rychlost dotazu s ORDER BY/GROUP BY.

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

Dalším způsobem, jak optimalizovat SQL dotaz s tříděním, je použít odvozené tabulky, abyste se vyhnuli zbytečným třídicím operacím. 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 do VARCHAR, dlouhé do BLOB

Pro ukládání krátkých znakových dat používejte VARCHAR, pro ukládání dlouhých textů používejte BLOB. Varchary jsou rychlejší pro malé kusy dat, protože jsou uloženy v záznamu a celý záznam je přečten během stejného IO cyklu, a pokud je velikost záznamu menší než 2/3 velikosti databázové stránky, je celý záznam uložen na stejné databázové stránce. BLOB jsou uloženy mimo záznam a vyžadují další kolo IO pro jejich přečtení; jejich výhoda se projevuje 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 pozdní binding s poddotazy pro selektivní zobrazení informací z BLOB (například zobrazení obsahu dokumentu).

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

Používejte typ BIGINT pro auto-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. Obzvláště se vyhněte GUID jako identifikátorům - kvůli náhodnému rozložení hodnot GUID mohou být operace INSERT/UPDATE s primárními/jedinečnými klíči GUID až 20krát pomalejší než s celými čísly.

35. Přepočítejte statistiky indexů

Pravidelně přepočítávejte statistiky indexů. Aktualizujte statistiky indexů pro tabulky s častými nebo masivními změnami pomocí příkazu SET STATISTICS, což umožní optimalizátoru Firebird vybrat 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 databázová připojení k databázi Firebird krátká (typické pro webové stránky), použijte connection pool - například v PHP použijte funkci ibase_pconnect místo ibase_connect.

37. Používejte volbu LINGER ve Firebirdu 3.0

Pokud jsou databázová připojení krátká a používáte Firebird 3+, použijte volbu LINGER k udržení cache aktivní po stanovenou dobu - udrží často používané stránky v cache, i když nebudou žá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 Firebirdu 3.0 může být při spojování velkých a malých tabulek HASH JOIN mnohem rychlejší než běžné spojení, které používá „nested loop“ s indexem. Aby optimalizátor Firebird použil 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 ověřte výsledek optimalizace!

39. Označte vhodné PSQL funkce jako DETERMINISTIC

Označte své PSQL funkce (ve Firebirdu 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 Firebirdu 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 zvýšení rychlosti 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 Příručky HQbird).

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 urychlení hromadných operací DML spusťte transakci s volbou „NO AUTO UNDO“, aby se undo-logy jednotlivých příkazů neslučovaly s undo-logem transakce.

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

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

Mí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ází.

Kontaktujte nás

Máte nějaké dotazy? Neváhejte nás kontaktovat e-mailem!