45 sposobów na przyspieszenie bazy danych Firebird

Tutaj znajdziesz listę wskazówek dotyczących wydajności bazy danych Firebird w różnych obszarach — od sprzętu/systemu operacyjnego i dostrajania konfiguracji Firebird po zalecenia dotyczące optymalizacji SQL. Ta lista nie jest kompletnym przewodnikiem po optymalizacji Firebird i zakłada, że rozumiesz podstawy działania Firebird, takie jak plany wykonania, zarządzanie transakcjami i statystyki wydajności zapytań.

Stosuj te wskazówki ostrożnie i zweryfikuj ich efekt przed wdrożeniem na produkcję.

Nasza firma (IBSurgeon) oferuje kompleksową usługę optymalizacji wydajności bazy danych.

1. Umieść bazę danych na SSD

Umieść bazę danych na SSD. Dysk SSD zapewnia znacznie lepsze losowe IO niż tradycyjne dyski. Losowe IO jest krytyczne dla odczytu i zapisu danych rozproszonych w dużym pliku bazy danych — większość operacji na bazie danych wymaga intensywnego równoległego losowego IO.

2. Używaj RAID 10

Jeśli używasz RAID1 lub RAID5, rozważ RAID10 — jest o 15-25% szybszy.

3. Sprawdź BBU

Jeśli używasz kontrolera RAID, sprawdź, czy ma zainstalowaną i działającą jednostkę baterii podtrzymującej (BBU) — niektórzy dostawcy nie dostarczają BBU domyślnie. Bez BBU kontroler wyłącza pamięć podręczną, a RAID działa bardzo wolno, nawet wolniej niż zwykłe dyski SATA. Zazwyczaj status BBU można sprawdzić w narzędziu konfiguracyjnym RAID.

4. Ustaw pamięć podręczną zapisu na write-back

Jeśli używasz kontrolera RAID z zainstalowanym BBU (oraz serwera z UPS), sprawdź, czy jego pamięć podręczna jest ustawiona na write-back (a nie write-through). „Write-back” włącza pamięć podręczną zapisu kontrolera.

5. Włącz pamięć podręczną odczytu

Jeśli używasz kontrolera RAID, sprawdź, czy ma włączoną pamięć podręczną odczytu.

6. Sprawdź podsystem dyskowy

Sprawdź swoje dyski pod kątem złych bloków i innych problemów sprzętowych (w tym przegrzewania). Problemy sprzętowe mogą znacznie obniżyć wydajność IO i prowadzić do uszkodzeń bazy danych.

7. Używaj SuperClassic lub Classic w Firebird 2.5

Jeśli używasz Firebird 2.5 SuperServer z wieloma połączeniami, spróbuj użyć SuperClassic lub Classic — mogą skalować się lepiej, wykorzystując wszystkie rdzenie CPU.

8. Używaj SuperServer 3.0 w Firebird 3.

Jeśli używasz Classic lub SuperClassic w 2.5, rozważ migrację do Firebird 3.0 SuperServer — teraz może wykorzystywać wiele rdzeni i łączyć to z zaletami współdzielonej pamięci podręcznej.

9. Zwiększ pamięć podręczną buforów stron

Zwiększ rozmiar pamięci podręcznej buforów stron (parametr DefaultDBCachePages) powyżej wartości domyślnych. Dla 2.5 SuperServer zalecamy 10000 stron, dla 3.0 SuperServer — 50000 stron, dla Classic i SuperClassic — od 256 do 2048 stron. Jednak nie ustawiaj wartości pamięci podręcznej buforów stron zbyt wysoko — synchronizacja pamięci podręcznej ma swój koszt, a pomysł umieszczenia całej bazy danych w RAM poprzez dostrojenie tej wartości nie zadziała. Skorzystaj ze wstępnie zoptymalizowanych plików konfiguracyjnych Firebird tutaj: /pl/optimized-firebird-configuration/

10. Zwiększ rozmiar pamięci dla operacji sortowania

Zwiększ wartość parametru TempCacheLimit w firebird.conf — określa on rozmiar pamięci podręcznej przestrzeni tymczasowej do sortowania. Wartości domyślne są zbyt niskie (8Mb dla Classic i 64Mb dla SuperServer), użyj co najmniej 64Mb dla Classic i 1Gb dla SuperServer i SuperClassic. Ponownie skorzystaj ze zoptymalizowanych plików konfiguracyjnych z punktu #9.

11. Ustaw Forced Writes na Off (ostrożnie!)

Jeśli masz intensywną aktywność wstawiania lub aktualizacji (możesz to sprawdzić za pomocą HQbird MonLogger, szczegóły znajdziesz na stronie 60 HQbird User Guide), a masz zainstalowany UPS i replikację chroniącą przed awariami sprzętowymi, rozważ ustawienie Forced Writes na OFF — może to zwiększyć szybkość operacji zapisu nawet 3-krotnie.

12. Zwiększ liczbę slotów hash dla Classic/SuperClassic

Zwiększ wartość parametru LockHashSlots dla Classic i SuperClassic z domyślnej 1009 do jakiejś dużej liczby pierwszej (na przykład 30011) — zmniejszy to kolejki w wewnętrznym mechanizmie blokad.

13. Używaj CPU Affinity dla Super Server 2.5

Jeśli używasz SuperServer 2.5, ustaw parametr CPUAffinity na wartość równą liczbie używanych baz danych: SuperServer w 2.5 może używać różnych rdzeni CPU do przetwarzania żądań dla określonych baz danych.

14. Używaj szybkiego dysku dla przestrzeni tymczasowej

Ustaw pierwszą część parametru TempDirectory w firebird.conf na szybki dysk — SSD lub dysk RAM. Zmniejszy to czas dużych sortowań — na przykład podczas przywracania bazy danych.

15. Przechowuj kopie zapasowe bazy danych na innym dysku

Przechowuj kopie zapasowe bazy danych na dedykowanym dysku fizycznym (RAID). Oddzieli to IO odczytu i zapisu podczas tworzenia kopii zapasowej, zwiększy szybkość tworzenia kopii zapasowej i zmniejszy obciążenie głównego dysku. Jest to szczególnie ważne, gdy kopie zapasowe są tworzone, gdy użytkownicy pracują z bazą danych. Więcej szczegółów na temat konfiguracji sprzętowej dla Firebird można znaleźć w " Firebird Hardware Guide".

16. Dezaktywuj indeksy dla masowych wstawień

Jeśli wstawiasz lub aktualizujesz wiele rekordów (ponad 25% tabeli), dezaktywuj indeksy dla tabeli, do której wstawiane są rekordy, i aktywuj je ponownie po wstawieniu lub aktualizacji. Operacja odbudowy indeksu może być szybsza niż wiele aktualizacji indeksu.

17. Używaj Global Temporary Tables dla szybkich wstawień

Aby przyspieszyć wstawianie i aktualizacje, używaj Global Temporary Tables do masowych wstawień dużych zestawów rekordów, a następnie przenieś rekordy do tabeli trwałej. Wstawianie rekordów do GTT, ich wstępne przetworzenie, a następnie przeniesienie do tabeli trwałej może być bardzo efektywne.

18. Unikaj niepotrzebnych indeksów

Używaj mniej indeksów dla tabel z intensywnym wstawianiem i aktualizacjami. Każdy indeks dodaje znaczny narzut dla operacji wstawiania, aktualizacji, usuwania i garbage collection — przy wstawianiu/aktualizacji/usuwaniu/czyszczeniu pojedynczego rekordu może wystąpić 3-4 dodatkowe odczyty i zapisy stron dla każdego indeksu.

19. Zastąp UDF wywołaniami funkcji wbudowanych

Zastąp wywołania UDF wywołaniami funkcji wbudowanych. W ostatnich wersjach Firebird dodano wiele funkcji wbudowanych, które oferują funkcjonalność wcześniej dostępną tylko w bibliotekach UDF. Zastąp takie funkcje tam, gdzie to możliwe, ponieważ funkcje wbudowane działają do 3 razy szybciej niż UDF.

20. Używaj transakcji tylko do odczytu dla operacji odczytu

Używaj transakcji tylko do odczytu dla operacji, które nie zmieniają rekordów (tj. SELECT) z trybem izolacji = read committed. Takie transakcje nie zachowują wersji rekordów przed garbage collection i mogą działać bezterminowo: nie wpływają na wydajność bazy danych.

21. Używaj krótkich transakcji zapisu i pozbądź się WSZYSTKICH długotrwałych

Używaj krótkich transakcji zapisywalnych (dla operacji INSERT/UPDATE/DELETE).

Im krótsza transakcja zapisywalna, tym lepiej. Krótkie transakcje zachowują proporcjonalnie mniej wersji rekordów przed garbage collection niż długotrwałe. Niestety, nawet pojedyncza długotrwała transakcja (na przykład pozostawiona otwarta przez narzędzie deweloperskie) może zniweczyć dobry efekt wszystkich innych krótkich transakcji zapisywalnych. Dlatego musisz monitorować długotrwałe transakcje i poprawić odpowiednie miejsca w kodzie źródłowym. Użyj narzędzia HQbird DataGuard, aby otrzymywać alerty o najstarszej aktywnej transakcji w bazie danych Firebird (które aplikacje ją rozpoczęły, jaki adres IP, znacznik czasu jej rozpoczęcia), oraz narzędzia HQbird MonLogger, aby zobaczyć pełną listę długotrwałych aktywnych transakcji i ich statystyki IO. Ponadto, jeśli używasz komponentów/bibliotek dostępu do bazy danych, które mogą buforować zestawy rekordów, używaj cached updates.

22. Unikaj długich łańcuchów rekordów

Unikaj sytuacji, gdy jeden rekord ma wiele wersji rekordów — Firebird działa znacznie wolniej z długimi łańcuchami rekordów. (aby zobaczyć, ile wersji rekordów mają niektóre tabele i jaki jest najdłuższy łańcuch rekordów, możesz użyć narzędzia HQbird IBAnalyst, zakładka Tables, sortuj po “Max Version”). Używaj kombinacji wstawień i zaplanowanego usuwania starych rekordów zamiast wielokrotnych aktualizacji tego samego rekordu.

23. Używaj PREPARE prawidłowo

Używaj przygotowanych instrukcji do uruchamiania zapytań SQL, w których zmieniają się tylko parametry — na przykład wykonaj prepare przed pętlą takich zapytań. Prepare może zająć znaczną ilość czasu (szczególnie dla dużych tabel), a przygotowanie zapytania tylko raz znacznie zwiększy ogólną wydajność.

24. Nie wykonuj COMMIT zbyt często podczas operacji masowego wstawiania/aktualizacji

W przypadku operacji masowego INSERT/UPDATE/DELETE nie zatwierdzaj transakcji po każdej zmianie (może się to zdarzyć, jeśli używasz opcji auto commit w sterowniku bazy danych) — zatwierdzaj transakcje co najmniej po 1000 operacjach lub więcej. Każde zatwierdzenie transakcji wykonuje kilka operacji odczytu/zapisu IO na bazie danych, dlatego częste zatwierdzenia obniżają wydajność bazy danych.

25. „Wyłącz” indeksy, jeśli używasz IN z wieloma stałymi

Jeśli używasz konstrukcji WHERE fieldX IN (Constant1, Constant2,… ConstantN) i istnieje indeks na fieldX, Firebird użyje indeksu tyle razy, ile stałych jest na liście IN. Wyłącz wyszukiwanie indeksu, zamieniając fieldX na wyrażenie +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), lub, dla łańcuchów, użyj fieldX||''

26. Zastąp IN przez JOIN

Unikaj używania zapytań z zagnieżdżonym WHERE IN(SELECT… WHERE IN (SELECT.. WHERE IN() )), może to zdezorientować optymalizator Firebird. Przekształć zagnieżdżone IN w złączenia.

27. Używaj LEFT JOIN w prawidłowy sposób

Jeśli używasz złączeń LEFT OUTER, jawnie umieszczaj tabele w złączeniu od najmniejszej do największej.

28. Ogranicz pobieranie zapytań SELECT

Zawsze staraj się ograniczać duże wyjście dla zapytań SELECT za pomocą klauzul FIRST… SKIP lub ROWS. Jeśli zapytanie nie jest zaprojektowane specjalnie jako raport (który wymaga wydrukowania/wyeksportowania wszystkich rekordów), zazwyczaj wystarczy pokazać najlepsze 10-100 rekordów. Pobieraj tylko niezbędne rekordy.

29. Określ mniejszą liczbę kolumn w SELECT z ORDER BY/GROUP BY

Zmniejsz liczbę kolumn i ich łączną szerokość w zapytaniach z ORDER BY/GROUP BY zarówno w części SELECT (tj. pola do pokazania), jak i w klauzuli ORDER BY. Firebird łączy kolumny z klauzul SELECT i ORDER BY/GROUP BY i sortuje je w pamięci (lub, jeśli pamięć jest niewystarczająca, na dysku). Więc jeśli w SELECT jest długi VARCHAR, rozmiar plików sortowania może być naprawdę duży (wiele gigabajtów). Zmniejszenie liczby pól tylko do tych, które muszą być sortowane, i późniejsze złączenie z dużymi polami do pokazania może znacznie (x3-x10) zwiększyć szybkość zapytania z ORDER BY/GROUP BY.

30. Używaj tabel pochodnych do optymalizacji SELECT z ORDER BY/GROUP BY

Innym sposobem optymalizacji zapytania SQL z sortowaniem jest użycie tabel pochodnych, aby uniknąć niepotrzebnych operacji sortowania. Zamiast

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

użyj następującej modyfikacji:

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. Przechowuj krótkie łańcuchy w VARCHAR, duże w BLOB

Aby przechowywać krótkie dane znakowe, używaj VARCHAR, aby przechowywać długie teksty, używaj BLOB. Varchary są szybsze dla małych fragmentów danych, ponieważ są przechowywane w rekordzie, a cały rekord jest odczytywany podczas tego samego cyklu IO, a jeśli rozmiar rekordu jest mniejszy niż 2/3 rozmiaru strony bazy danych, cały rekord jest przechowywany na tej samej stronie bazy danych. BLOBy są przechowywane poza rekordem i wymagają dodatkowej rundy IO do odczytu, a swoją przewagę pokazują przy odczycie i zapisie długich łańcuchów.

32. Wyklucz kolumny BLOB z dużych SELECT

Wyklucz kolumny BLOB z dużych SELECT. Użyj pewnego rodzaju późnego wiązania z podzapytaniami, aby selektywnie pokazywać informacje z BLOB (na przykład pokazać zawartość dokumentu).

33. Używaj BIGINT dla kluczy głównych i unikalnych

Używaj typu BIGINT dla automatycznie zwiększanych kluczy głównych i unikalnych oraz dla identyfikatorów wszystkich typów. Operacje z BIGINT są najszybsze, a BIGINT ma wystarczającą pojemność do przechowywania prawie wszystkich zakresów danych.

34. Nie używaj VARCHARów jako kluczy

Nie używaj VARCHAR do identyfikatorów, chyba że jest to naprawdę konieczne — operacje na nich są znacznie mniej wydajne niż na kolumnach całkowitych. W szczególności unikaj GUIDów jako identyfikatorów — z powodu losowego rozkładu wartości GUID operacje INSERT/UPDATE z kluczami Primary/Unique opartymi na GUID mogą być 20 razy wolniejsze niż z liczbami całkowitymi.

35. Przeliczaj statystyki indeksów

Regularnie przeliczaj statystyki indeksów. Aktualizuj statystyki indeksów dla tabel z częstymi lub masowymi zmianami za pomocą polecenia SET STATISTICS — pozwala to optymalizatorowi Firebird wybierać lepsze plany SQL. HQbird Firebird DataGuard może wykonywać takie przeliczanie statystyk indeksów automatycznie według zadanego harmonogramu (zwykle raz w tygodniu).

36. Używaj puli połączeń

Jeśli połączenia z bazą danych Firebird są krótkie (typowe dla stron internetowych), używaj puli połączeń — na przykład w PHP używaj funkcji ibase_pconnect zamiast ibase_connect

37. Używaj opcji LINGER w Firebird 3.0

Jeśli połączenia z bazą danych są krótkie i używasz Firebird 3+, użyj opcji LINGER, aby utrzymać cache aktywny przez określony czas — pozwoli to zachować często używane strony w cache, nawet jeśli nie będzie innych połączeń. Na przykład ALTER DATABASE SET LINGER TO 60 utrzyma cache przez 60 sekund po zakończeniu ostatniego połączenia.

38. Używaj HASH JOINów

W Firebird 3.0, w przypadku łączenia dużej i małej tabeli, HASH JOIN może być znacznie szybszy niż zwykłe złączenie wykorzystujące «nested loop» z indeksem. Aby skłonić optymalizator Firebird do użycia HASH join, użyj +0 w warunku złączenia: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Sprawdź wynik optymalizacji przed wdrożeniem na produkcję!

39. Oznaczaj odpowiednie funkcje PSQL jako DETERMINISTIC

Oznaczaj swoje funkcje PSQL (w Firebird 3+), które nie mają parametrów i zwracają stałe wartości, słowem kluczowym DETERMINISTIC. Funkcje deterministyczne są obliczane i buforowane w zakresie bieżącego zapytania.

40. Używaj funkcji analitycznych (okienkowych) w Firebird 3.0

Jeśli wykonujesz SELECT z jednoczesnym wyprowadzeniem jakiejś kolumny i funkcji agregującej dla niej, użyj funkcji okienkowych (analitycznych) — jest to szybsze niż podzapytanie lub 2 zapytania. Na przykład:

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

zastąp przez

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

41. Używaj przełącznika -se dla gbak

Używaj przełącznika -se, aby zwiększyć szybkość tworzenia kopii zapasowej i/lub przywracania gbak nawet o 20%, na przykład

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

42. WHERE CURRENT OF

Najszybszym sposobem przetwarzania rekordów pobranych przez kursor w PSQL jest klauzula ‘where current of <>’. Jest szybsza niż ‘where rb$db_key = :v_db_key’ i znacznie szybsza niż wyszukiwanie za pomocą klucza primary lub unique.

43. Unikaj częstych zapytań do tabel monitoringu

Nie uruchamiaj zapytań do tabel monitoringu Firebird (MON$) zbyt często — takie zapytania zużywają znaczne zasoby i mogą znacznie obniżyć wydajność głównej logiki biznesowej. Zalecamy uruchamianie zapytań MON$ nie częściej niż raz na minutę. Do ciągłego monitorowania zapytań/transakcji/połączeń Firebird używaj narzędzia HQbird PerfMon, które obsługuje Trace API (szczegóły na stronie 66 HQbird User Guide).

44. Używaj opcji NO_AUTO_UNDO dla masowych wstawień/aktualizacji

Jeśli wykonujesz wiele poleceń DML (Update/Insert/Delete) w ramach tej samej transakcji, Firebird scala undo-log każdego polecenia z undo-logiem transakcji. Aby przyspieszyć masowe operacje DML, rozpocznij transakcję z opcją «NO AUTO UNDO», aby nie scalać undo-logów każdego polecenia z undo-logiem transakcji.

45. Nie używaj uwierzytelniania SRP w Firebird 3, jeśli go nie potrzebujesz

Nie używaj uwierzytelniania użytkowników SRP (Firebird 3.0+), jeśli naprawdę go nie potrzebujesz — połączenie z uwierzytelnianiem SRP nawiązuje się wolniej niż zwykłe połączenie.

Zamiast podsumowania

Optymalizacja wydajności wymaga uwzględnienia wielu czynników i może być naprawdę trudna. Jeśli wypróbowałeś wszystkie powyższe rozwiązania, rozważ skorzystanie z profesjonalnej usługi optymalizacji wydajności bazy danych.

Skontaktuj się z nami

Masz jakieś pytania? Nie wahaj się skontaktować z nami przez email!