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 konfiguracji Firebirda po zalecenia dotyczące optymalizacji SQL. Ta lista nie jest kompletnym odniesieniem do optymalizacji Firebirda i zakłada, że rozumiesz podstawy działania Firebirda, takie jak plany wykonania, zarządzanie transakcjami i statystyki wydajności zapytań.

Prosimy o ostrożne stosowanie tych wskazówek i zweryfikowanie ich efektu przed wdrożeniem do produkcji.

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

1. Umieść bazę danych na dysku SSD

Umieść swoją bazę danych na dysku SSD. Dysk SSD zapewnia znacznie lepszy losowy dostęp do danych (random IO) niż tradycyjne dyski. Losowy dostęp jest kluczowy dla odczytu i zapisu danych rozproszonych w dużym pliku bazy danych - większość operacji bazodanowych wymaga intensywnego równoległego losowego dostępu.

2. Użyj RAID 10

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

3. Sprawdź BBU

Jeśli używasz kontrolera RAID, sprawdź, czy ma zainstalowaną i działającą baterię podtrzymującą (Backup Battery Unit - BBU) - niektórzy producenci 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 możesz sprawdzić status BBU w narzędziu konfiguracji RAID.

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

Jeśli używasz kontrolera RAID z zainstalowaną baterią BBU (oraz serwera z UPS), sprawdź, czy jego pamięć podręczna jest ustawiona na write-back (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 uszkodzonych bloków i innych problemów sprzętowych (w tym przegrzewania). Problemy sprzętowe mogą znacząco obniżyć wydajność operacji wejścia/wyjścia i prowadzić do uszkodzeń bazy danych.

7. Użyj 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ą one lepiej skalować się, wykorzystując wszystkie rdzenie procesora.

8. Użyj SuperServer 3.0 w Firebird 3

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

9. Zwiększ pamięć podręczną stron bufora

Zwiększ rozmiar pamięci podręcznej stron bufora (parametr DefaultDBCachePages) z 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. Nie ustawiaj jednak wartości pamięci podręcznej stron bufora zbyt wysoko - synchronizacja pamięci podręcznej ma swój koszt, a pomysł umieszczenia całej bazy danych w pamięci RAM poprzez dostrojenie tej wartości nie zadziała. Użyj wstępnie zoptymalizowanych plików konfiguracyjnych Firebirda tutaj: /pl/optimized-firebird-configuration/

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

Zwiększ wartość parametru TempCacheLimit w pliku 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, użyj zoptymalizowanych plików konfiguracyjnych z punktu #9.

11. Wyłącz Forced Writes (z ostrożnością!)

Jeśli masz intensywną aktywność wstawiania lub aktualizacji (możesz to sprawdzić za pomocą HQbird MonLogger, szczegóły na stronie 60 Podręcznika użytkownika HQbird), i jeśli masz zainstalowany UPS oraz replikację w celu ochrony przed awariami sprzętu, rozważ ustawienie Forced Writes na OFF - może to zwiększyć szybkość operacji zapisu nawet 3-krotnie.

12. Zwiększ liczbę slotów haszujących dla Classic/SuperClassic

Zwiększ wartość parametru LockHashSlots dla Classic i SuperClassic z domyślnych 1009 do większej liczby pierwszej (na przykład 30011) - zmniejszy to kolejki w wewnętrznym mechanizmie blokowania.

13. Użyj powinowactwa procesora (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 wersji 2.5 może używać różnych rdzeni procesora do przetwarzania żądań dla określonych baz danych.

14. Użyj szybkiego dysku dla przestrzeni tymczasowej

Ustaw pierwszą część parametru TempDirectory w pliku firebird.conf na szybki dysk - SSD lub dysk RAM. Skróci 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 operacje odczytu i zapisu podczas tworzenia kopii zapasowej, zwiększy szybkość tworzenia kopii i zmniejszy obciążenie głównego dysku. Jest to szczególnie ważne, gdy kopie zapasowe są tworzone podczas pracy użytkowników z bazą danych. Więcej szczegółów na temat konfiguracji sprzętu dla Firebirda 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 reaktywuj je po wstawieniu lub aktualizacji. Operacja przebudowy indeksu może być szybsza niż wiele aktualizacji indeksu.

17. Użyj Globalnych Tabel Tymczasowych (GTT) do szybkich wstawień

Aby przyspieszyć wstawianie i aktualizacje, używaj Globalnych Tabel Tymczasowych do masowego wstawiania dużych zestawów rekordów, a następnie przenoś rekordy do tabeli stałej. Może to być bardzo skuteczne - wstaw rekordy do GTT, przetwórz je wstępnie, a następnie przenieś do tabeli trwałej.

18. Unikaj niepotrzebnych indeksów

Używaj mniejszej liczby indeksów dla tabel z intensywnymi operacjami wstawiania i aktualizacji. Każdy indeks dodaje znaczący narzut dla operacji wstawiania, aktualizacji, usuwania i czyszczenia (garbage collection) - może to oznaczać 3-4 dodatkowe odczyty i zapisy stron przy wstawianiu/aktualizowaniu/usuwaniu/czyszczeniu pojedynczego rekordu dla każdego indeksu.

19. Zastąp UDF-y wbudowanymi funkcjami

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

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 przechowują wersji rekordów z czyszczenia (garbage collection) i mogą działać w nieskończoność: nie wpływają na wydajność bazy danych.

21. Używaj krótkich transakcji zapisu i pozbądź się WSZYSTKICH długo działających

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

Im krótsza transakcja zapisu, tym lepiej. Krótkie transakcje przechowują proporcjonalnie mniej wersji rekordów z czyszczenia niż długo działające. Niestety, nawet pojedyncza długo działająca transakcja (na przykład pozostawiona otwarta z narzędzia deweloperskiego) może zniweczyć dobry efekt wszystkich innych krótkich transakcji zapisu. Dlatego musisz monitorować długo działające transakcje i naprawiać 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óra aplikacja ją uruchomiła, jaki adres IP, znacznik czasu jej rozpoczęcia), oraz narzędzia HQbird MonLogger, aby zobaczyć pełną listę długo działających aktywnych transakcji i ich statystyki wejścia/wyjścia. Ponadto, jeśli używasz komponentów/bibliotek dostępu do baz danych, które mogą buforować zestawy rekordów, używaj buforowanych aktualizacji (cached updates).

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

Unikaj sytuacji, w których jeden rekord ma wiele wersji - 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, sortowanie po „Max Version”). Używaj kombinacji wstawiania i zaplanowanego usuwania starych rekordów zamiast wielokrotnych aktualizacji tego samego rekordu.

23. Używaj PREPARE poprawnie

Używaj przygotowanych zapytań (prepared statements) do wykonywania zapytań SQL, w których zmieniają się tylko parametry - na przykład wykonaj prepare przed pętlą takich zapytań. Przygotowanie może zająć znaczący czas (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 swoim sterowniku bazy danych) - zatwierdzaj transakcje co najmniej po 1000 operacjach lub więcej. Każde zatwierdzenie transakcji wykonuje kilka operacji odczytu/zapisu na bazie danych, dlatego częste zatwierdzanie zmniejsza wydajność bazy danych.

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

Jeśli używasz konstrukcji WHERE fieldX IN (Stała1, Stała2,… StałaN) i istnieje indeks na fieldX, Firebird użyje indeksu tyle razy, ile stałych znajduje się na liście IN. Wyłącz wyszukiwanie indeksowe, zamieniając fieldX na wyrażenie +0: WHERE fieldX+0 IN (Stała1, Stała2,… StałaN), lub dla ciągów znaków użyj fieldX||''

26. Zastąp IN zapytaniem JOIN

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

27. Używaj LEFT JOIN we właściwy sposób

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

28. Ogranicz pobieranie zapytań SELECT

Zawsze staraj się ograniczać duże wyniki zapytań SELECT za pomocą klauzul FIRST… SKIP lub ROWS. Jeśli zapytanie nie jest zaprojektowane specjalnie jako raport (który wymaga wydrukowania/eksportowania wszystkich rekordów), zwykle wystarczy pokazać pierwsze 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 wyświetlenia), 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ęci jest za mało, na dysku). Jeśli więc w SELECT znajduje się 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 wyświetlenia 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 w celu uniknięcia 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 ciągi znaków w VARCHAR, duże w BLOB

Do przechowywania krótkich danych znakowych używaj VARCHAR, do przechowywania długich tekstów używaj BLOB. VARCHAR-y są szybsze dla małych fragmentów danych, ponieważ są przechowywane w rekordzie, a cały rekord jest odczytywany podczas tego samego cyklu wejścia/wyjścia, 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. BLOB-y są przechowywane poza rekordem i wymagają dodatkowego cyklu wejścia/wyjścia do ich odczytania, a ich przewaga uwidacznia się przy odczycie i zapisie długich ciągów znaków.

32. Wyklucz kolumny BLOB z dużych zapytań SELECT

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

33. Używaj BIGINT dla kluczy podstawowych i unikalnych

Używaj typu BIGINT dla automatycznie inkrementowanych kluczy podstawowych i unikalnych oraz dla identyfikatorów wszystkich typów. Operacje na BIGINT są najszybsze, a BIGINT ma wystarczającą pojemność do przechowywania prawie wszystkich zakresów danych.

34. Nie używaj typów VARCHAR dla kluczy

Nie używaj typu VARCHAR dla identyfikatorów, chyba że jest to naprawdę konieczne - operacje na nich są znacznie mniej wydajne niż na kolumnach całkowitoliczbowych. Szczególnie unikaj identyfikatorów GUID - ze względu na losowy rozkład wartości GUID, operacje INSERT/UPDATE na kluczach Primary/Unique z GUID mogą być nawet 20 razy wolniejsze niż w przypadku liczb całkowitych.

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 automatycznie wykonywać takie przeliczanie statystyk indeksów zgodnie z pożądanym harmonogramem (zwykle raz w tygodniu).

36. Używaj puli połączeń

Jeśli połączenia z bazą danych Firebird są krótkie (co jest 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żywaj opcji LINGER, aby utrzymać pamięć podręczną aktywną przez określony czas - pozwoli to utrzymać często używane strony w pamięci podręcznej, nawet jeśli nie będzie innych połączeń. Na przykład ALTER DATABASE SET LINGER TO 60 utrzyma pamięć podręczną przez 60 sekund po zakończeniu ostatniego połączenia.

38. Używaj HASH JOIN

W Firebird 3.0, w przypadku łączenia dużych i małych tabel, HASH JOIN może być znacznie szybszy niż zwykłe złączenie wykorzystujące „zagnieżdżoną pętlę” z indeksem. Aby skłonić optymalizator Firebird do użycia HASH JOIN, dodaj +0 w warunku złączenia: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Sprawdź wynik optymalizacji przed wdrożeniem jej 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 wyświetlaniem kolumny i funkcji agregującej dla niej, używaj funkcji okienkowych (analitycznych) - jest to szybsze niż podzapytanie lub dwa 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 podstawowego lub unikalnego.

43. Unikaj częstych zapytań do tabel monitorujących

Nie uruchamiaj zbyt często zapytań do tabel monitorujących Firebird (MON$) - 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 podręcznika HQbird User Guide).

44. Używaj opcji NO_AUTO_UNDO dla masowych operacji INSERT/UPDATE

Jeśli wykonujesz wiele poleceń DML (Update/Insert/Delete) w ramach tej samej transakcji, Firebird scala dziennik wycofania (undo-log) każdego polecenia z dziennikiem wycofania transakcji. Aby przyspieszyć masowe operacje DML, rozpocznij transakcję z opcją «NO AUTO UNDO», aby nie scalać dzienników wycofania każdego polecenia z dziennikiem wycofania 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ązywane jest wolniej niż zwykłe połączenie.

Zamiast podsumowania

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

Skontaktuj się z nami

Masz pytania? Nie wahaj się skontaktować z nami przez e-mail!