23 dodatkowe sposoby na przyspieszenie Firebird

Alexey Kovyazin, IBSurgeon, [email protected], 08-Jan-2019

Tłumaczenia: Portuguese

Dlaczego „23 więcej"?

Niektórzy z Was pamiętają artykuł « 45 sposobów na przyspieszenie Firebird», który został opublikowany w maju 2016 roku. Teraz nadszedł czas, aby opublikować kolejną serię wskazówek i trików, opartych głównie na doświadczeniu z optymalizacji i utrzymania baz danych Firebird oraz serwerów z dużą liczbą połączeń (1000+).

1. Ustaw opcje zasilania na Wysoka wydajność w Windows Server 2016 i 2019

Domyślnie Windows Server ma plan zasilania ustawiony na „Zrównoważony", co nie jest odpowiednie dla serwerów baz danych. Ustaw go na „Wysoka wydajność" i zyskaj około +20% wydajności dla operacji intensywnie korzystających z CPU. Można to ustawić online, bez restartu lub ponownego uruchomienia. Poniższy obrazek pokazuje wykres CPU, który demonstruje przewagę planu zasilania „Wysoka wydajność":

Więcej szczegółów na temat naszych testów z planami zasilania Windows można znaleźć tutaj.

2. Włącz «Interakcja z pulpitem» dla Classic na Windows

Jeśli używasz Firebird z architekturą Classic na Windows, zaznacz pole wyboru «Zezwól usłudze na interakcję z pulpitem». Bez tego ustawienia zasób «pulpit sterty» jest ograniczony przez Windows, a Firebird nie może otworzyć więcej niż 250-300 połączeń (zależy od metadanych bazy danych i związanego z tym zużycia pamięci) - pojawi się błąd Out Of Memory.

3. Uwaga: Kontroler domeny

Problem polega na tym, że Windows z rolą Kontrolera domeny wyłącza pamięć podręczną zapisu na dysku z bazą danych Active Directory.

Wpływa to na Firebird na różne sposoby (oczywiście także na inne aplikacje) i wykazuje znacznie gorszą wydajność niż na serwerach bez ról Active Directory.

Należy pamiętać, że ten problem dotyczy tak popularnych wersji Windows, jak Windows Small Business Server 2011, a także innych wersji z DC.

4. Zwiększ limit «max open files» na Linux

Jeśli używasz Linux jako serwera baz danych, nie zapomnij dostroić limitów dla Firebird. Sprawdź limity dla swojego procesu Firebird (SuperServer lub SuperClassic) za pomocą następującego polecenia:

cat /proc//limits

i zwróć uwagę na linię z maksymalną liczbą otwartych plików.

Firebird może używać do 4 uchwytów na połączenie, a jeśli masz coś takiego:

Max open files 4096 4096 files

oznacza to, że całkowita liczba połączeń obsługiwanych przez proces Firebird będzie ograniczona do około 1000.

Należy pamiętać - jeśli masz 4 bazy danych na serwerze, połączenie dla każdej bazy danych jest liczone.

Ustaw więcej - zalecam 65535.

Nie zapomnij sprawdzić ponownie po restarcie procesu Firebird: czy zostało zastosowane, czy nie.

Dla architektury Classic konieczne jest sprawdzenie i zwiększenie limitów dla użytkownika «firebird».

5. Używaj nowoczesnego Linux

Tak, rozumiem, że ta rada jest trywialna, ale wielokrotnie widziałem dobrą poprawę wydajności po migracji z CentOS 6 do 7, Ubuntu 12 do 16 (na tym samym sprzęcie!), więc teraz jest to obowiązkowe zalecenie dla serwerów baz danych z więcej niż 250-300 połączeniami. Nowoczesny Linux jest warunkiem wstępnym przed dalszymi krokami optymalizacji.

Zalecane wersje Linux: CentOS 7.x oraz Ubuntu 16, 18.

6. Zarezerwuj 40% RAM na pamięć podręczną plików na Windows

Menedżer pamięci OS ma implikacje dotyczące alokacji pamięci, a domyślnie Windows wymaga 40% RAM na pamięć podręczną plików.

Niestety, słabe narzędzie Windows Task Manager pokazuje pamięć używaną na pamięć podręczną plików jako «wolną», a niektórzy administratorzy próbują sprawić, aby Firebird zużywał całą tę wolną pamięć, więc ustawiają parametr DefaultDBCachePage w firebird.conf na bardzo wysokie wartości, co zwykle prowadzi do swapowania.

Zawsze używaj narzędzia RAMMap, aby zobaczyć rzeczywiste użycie pamięci na Windows.

Empiryczna zasada dla Windows Server (dedykowanego do użytku jako serwer Firebird) jest następująca: pamięć Firebird (Working Set) powinna być mniejsza niż 40% całkowitego RAM. Jeśli całkowity rozmiar working sets dla wszystkich procesów przekracza 50%, Windows może rozpocząć swap.

Uwaga: „zarezerwować" oznacza tutaj nie tylko „nie ustawiać zbyt wielu buforów stron w Firebird", ale także, co ważne, ograniczyć użycie pamięci przez inne oprogramowanie. Na przykład, jeśli masz MS Exchange lub MSSQL na tym samym serwerze co Firebird, upewnij się, że ograniczysz ich apetyty na pamięć.

Jeśli jesteś zainteresowany szczegółami, nagrałem webinar poświęcony zarządzaniu pamięcią w Firebird:

• Część 1. https://www.youtube.com/watch?v=ZBmLgbYt4aM
• Część 2, https://www.youtube.com/watch?v=-1.DF2FnU-A

7. Zarezerwuj 30% RAM na pamięć podręczną plików na Linux

Linux działa z pamięcią podręczną plików w inny sposób niż Windows i ogólnie ilość RAM używana na pamięć podręczną plików może być znacznie mniejsza niż na Windows, bez zauważalnej degradacji wydajności Firebird. Jednak aby zagwarantować wysoką wydajność systemu z dużą liczbą połączeń, zwłaszcza na Classic i SuperClassic, dobrym pomysłem będzie zarezerwowanie 30% RAM na pamięć podręczną plików.

8. Używaj irqbalance na Linux

irqbalance często poprawia wydajność Firebird i równoważenie obciążenia CPU na serwerach z dużą liczbą rdzeni.

9. Dla maszyn wirtualnych - uważaj na Memory Overcommit

Maszyna wirtualna może być skonfigurowana tak, aby miała więcej pamięci niż fizycznie istnieje na hoście - dzięki funkcji znanej jako Memory Overcommit (nazwa może być różna w różnych systemach wirtualizacji). Oznacza to, że w przypadku szczytu zużycia pamięci (na VM z serwerem baz danych lub na sąsiedniej VM) może rozpocząć się swap, co doprowadzi do znacznych opóźnień. Dla wysokowydajnej VM przeznaczonej na serwer baz danych cała pamięć powinna być statyczna.

10. Dla maszyn wirtualnych - sprawdź limity VM

Często VM są tworzone z domyślnymi limitami CPU i IO, które mogą być bardzo niskie, jak 50 IOPS i 10% CPU. Sprawdź ustawienia swojej VM serwera i usuń wszelkie limity - wysokowydajny serwer baz danych powinien mieć całe możliwe CPU, przepustowość i IO.

11. Wyczyść pliki tymczasowe Firebird

Firebird tworzy wiele plików tymczasowych dla różnych operacji: sortowania, przetwarzania BLOB, śledzenia. Pliki te są przechowywane w następujących lokalizacjach: na Windows C:\ProgramData\firebird, na Linux / tmp / firebird

Normalnie te pliki powinny być czyszczone automatycznie, jednak czasami tak się nie dzieje (na przykład w przypadku restartu serwera).

Sprawdzaj te foldery okresowo i czyść stare pliki - może być wiele GB nieaktualnych plików fb_NNN, a ich wyczyszczenie zwolni miejsce na dysku systemowym.

12. Nie zapomnij włączyć pamięci podręcznej plików przy dużej pamięci podręcznej Firebird

Jak wiesz, pamięć podręczna Firebird (zwana także «buforami stron») jest określana przez parametr DefaultDBCachePages w firebird.conf/databases.conf lub bezpośrednio w nagłówku bazy danych.

W Firebird 3 SuperServer rozmiar tej pamięci podręcznej może być ustawiony bardzo wysoko, ale ważne jest, aby pamiętać o innym parametrze: FileSystemCacheThreshold.

Jeśli FileSystemCacheThreshold jest mniejszy niż DefaultDBCachePages lub bufory stron, pamięć podręczna plików systemu operacyjnego nie będzie używana, co może prowadzić do problemów z wydajnością.

W 99% przypadków lepiej jest mieć włączoną pamięć podręczną plików.

Aby to zapewnić, zawsze ustawiaj parametry zgodnie z następującą zasadą:

• DefaultDBCachePages = X
• FileSystemCacheThreshold = X+ N, N>1

Zdarzają się rzadkie przypadki, gdy wyłączenie pamięci podręcznej plików poprawia wydajność - jeśli masz taki przykład, skontaktuj się ze mną - [email protected]!

13. Przyspiesz bazę danych zabezpieczeń

Każde połączenie z bazą danych Firebird nawiązuje połączenie z bazą danych zabezpieczeń (security3.fdb w przypadku Firebird 3) i wykonuje kilka odczytów i zapisów (strony transakcji, strona nagłówka). Jeśli masz częste połączenia, wydajność Twojej bazy danych zabezpieczeń może stać się problemem.

Jako minimum możesz zrobić następujące rzeczy:

• Zwiększ bufory stron dla securityN.fdb (empiryczne optimum to 256 buforów)
• Przenieś security3.fdb na szybki dysk (to standardowa funkcja w Firebird 3, w 2.5 będzie wymagać ponownej instalacji)

Następnie możesz ustawić Forced Writes OFF dla bazy danych zabezpieczeń - małe ryzyko uszkodzenia nie jest w tym przypadku problemem.

Najbardziej radykalnym sposobem jest uczynienie bazy danych zabezpieczeń tylko do odczytu - wyeliminuje to wszystkie zapisy do niej.

Jeśli nie zmieniasz często użytkowników w bazie danych zabezpieczeń, to najlepsze rozwiązanie.

14. Wypróbuj SuperClassic na Firebird 3

W Firebird 3 architektura SuperServer była mocno reklamowana jako ostateczne rozwiązanie wydajnościowe, ale istnieją pewne typy obciążeń, które wykazują lepszą wydajność z SuperClassic (ale nie Classic - zawsze działa wolniej niż SuperServer/SuperClassic).

Jak przeprowadzić ten eksperyment w bezpieczny sposób? Wykonaj poniższe kroki:

Aby wypróbować SuperClassic

1. Ustaw w firebird.conf
2. ServerMode=SuperClassic
3. DefaultDbCachePages=1024
4. gfix -buff 0
5. Zrestartuj Firebird

Aby wrócić do SuperServer

1. Ustaw w firebird.conf
2. ServerMode=SuperServer
3. DefaultDbCachePages=N # N*rozmiar strony*liczba_baz < 25% RAM
4. FileSystemCacheThreshold = N+1
5. gfix -buff 0
6. Zrestartuj Firebird

Proszę napisz do mnie ([email protected]) o wynikach eksperymentu, jestem zainteresowany zobaczeniem wyników.

15. Duża baza danych? Zwiększ rozmiar strony

Domyślnie bazy danych Firebird mają następujące rozmiary stron:

• 2.5 - 4096 bajtów
• 3.0 - 8192 bajtów

Jednak maksymalny rozmiar strony to 16K (w 4.0 - 32K).

Dla baz danych większych niż 100Gb w 95% przypadków lepiej jest mieć największy dostępny rozmiar strony, aby:

• Zmniejszyć głębokość indeksów. Zaleca się, aby indeksy miały głębokość mniejszą lub równą 3. Indeksy o głębokości 4 i 5 będą znacznie wolniejsze
• Zwiększyć wykorzystanie RAM. Pamięć podręczna Firebird jest określana w stronach, 1000 stron z rozmiarem strony 8K będzie 8Mb rzeczywistej pamięci, a z 16K - 16Mb.
• Zmniejszyć liczbę stron systemowych. Przyspieszy to dostęp do rekordów dużych tabel (mniej skoków wskaźnik-wskaźnik-strona danych) i pomoże w przygotowaniu dużych zapytań SQL. Aby zwiększyć rozmiar strony bazy danych, bazę należy zarchiwizować narzędziem gbak, a następnie przywrócić z parametrem -page ( gbak -c -page 16384).

Uwaga: jeśli masz bazę danych z wieloma małymi blobami, zwiększenie rozmiaru strony może zmniejszyć fragmentację lub zwiększyć fragmentację i trudno przewidzieć, czy zwiększy to, czy zmniejszy wydajność.

16. Nie używaj flagi no_reserve

Flaga no_reserve sprawia, że Firebird nie rezerwuje wolnego miejsca (30%) na stronach danych dla możliwych wersji rekordów, które występują po UPDATE lub DELETE. Ta flaga pozwala na bardziej kompaktowe przechowywanie danych (a rozmiar bazy danych jest również mniejszy), ale w przypadku UPDATE/DELETE wszystkie zmiany trafiają na nową stronę danych. W rezultacie w bazie danych z flagą no_reserve operacje UPDATE/DELETE są wolniejsze.

Tak więc, jeśli Twoja baza danych nie jest tylko do odczytu, zalecam usunięcie flagi no_reserve.

Jak sprawdzić, czy jest ustawiona - zobacz linię Attributes w wyniku

gstat -h database

Jak wyłączyć:

gfix -use reserve database

Po tym poleceniu nowe strony danych będą tworzone z zarezerwowanym miejscem.

Jednak aby osiągnąć pełny efekt, konieczne jest wykonanie kopii zapasowej bazy danych za pomocą gbak, a następnie przywrócenie - w tym przypadku wszystkie strony danych będą z zarezerwowanym miejscem.

Uwaga: rozmiar bazy danych wzrośnie po usunięciu flagi no_reserve oraz po kopii zapasowej/przywróceniu.

17. Ustaw wysoki początkowy rozmiar tabeli blokad Firebird

Tabela blokad to mechanizm Firebird, który służy do synchronizacji dostępu do wewnętrznych obiektów silnika.

Tabela blokad Firebird może rosnąć automatycznie, ale jej zwiększanie jest powolną operacją, która może prowadzić do mikro-zawieszeń. Tabela blokad może tylko rosnąć, zaczynając od rozmiaru początkowego (ustawianego w firebird.conf).

Aby uniknąć wielokrotnego zwiększania tabeli blokad, dobrym pomysłem będzie monitorowanie jej rozmiaru na koniec okresu pracy (dnia, tygodnia itp.), a następnie ustawienie go jako rozmiaru początkowego w firebird.conf.

LockMemSize=99999999

Dla Twojej informacji: LockMemSize w systemach o wysokim obciążeniu z ~1000 użytkownikami zwykle wynosi poniżej 200 MB.

18. Użyj fb_lock_print, aby policzyć liczbę połączeń z bazą danych

Uzyskiwanie liczby połączeń z bazą danych to częste zadanie dla programistów baz danych: może być konieczne na przykład do celów licencyjnych.

Często programiści używają zapytania SELECT count(*) FROM MON$ATTACHMENTS, aby uzyskać tę wartość, ale nie jest to optymalny sposób: częste zapytania do tabel MON$ mogą obciążać bazę danych, więc lepiej użyć alternatywy:

Uruchom

fb_lock_print -d nazwa_bazy | alias

i sprawdź wartość Owners - pokaże ona aktualną liczbę połączeń z bazą danych.

19. Unikaj niepotrzebnych LEFT JOIN

Często widzę zapytania z konstrukcją taką jak ta:

T1 LEFT JOIN T2 ON (…) WHERE T2.Field_condition

Zasadniczo warunek na T2 wyklucza NULL-e z wyniku LEFT JOIN T2, więc można zmienić LEFT JOIN na INNER JOIN - nie wpłynie to na wynik zapytania.

INNER JOIN daje więcej swobody optymalizatorowi Firebird, a w nowoczesnych wersjach Firebird jest optymalizowany znacznie lepiej niż LEFT.

Ma to szczególne znaczenie w następujących przypadkach:

• Brak warunku dla T1 w klauzuli WHERE
• T2 jest małą tabelą

20. Unikaj niepotrzebnego liczenia rekordów

Innym częstym błędem w złożonych zapytaniach do baz danych i procedurach składowanych jest używanie select count() tylko do sprawdzenia, czy rekord istnieje.

Następujące zapytanie odczyta wszystkie rekordy zgodnie z warunkiem1:

(select count(*)…. where condition11) >0

Lepiej użyć tej konstrukcji:

Exists(select first 1 id where condition1)

Jeśli condition1 zwraca więcej niż 1 rekord, proponowana opcja będzie znacznie szybsza, ponieważ nie odczytuje wszystkich rekordów - zatrzymuje się po pobraniu pierwszego rekordu.

21. Unikaj niepotrzebnego sortowania w procedurach składowanych

Sortowanie wyników zapytania wewnątrz procedury składowanej powinno być uzasadnione logiką biznesową.

Na przykład w poniższej procedurze składowanej klauzula ORDER BY jest bezużyteczna z punktu widzenia logiki biznesowej, ale dodaje niepotrzebną operację sortowania.

create or alter procedure NEW_PROCEDURE
returns (
    SUMX double precision)
as
declare variable _amount double precision;
begin
for select T1.amount from Table1 t1 where ....
    order by id
    into :_amount
    do
    begin
     sumx=sumx+_amount
    end;
  suspend;
end

Sprawdź swój kod PSQL pod kątem podobnych sytuacji i usuń niepotrzebne ORDER BY (a także distinct i UNION).

22. Nie trzymaj zapytań w stanie przygotowanym bez potrzeby

Często widzimy 500-1000 przygotowanych instrukcji w każdym połączeniu (można to sprawdzić za pomocą zapytań MON$).

Zdecydowana większość z nich wykonuje się tylko raz, a potem po prostu siedzi w pamięci RAM, zwiększając zestaw roboczy Firebird i spowalniając zapytania MON$.

Zalecenie jest takie, aby trzymać zapytania SQL w stanie przygotowanym tylko wtedy, gdy mają być uruchamiane wiele razy, lub jeśli ich czas przygotowania jest duży (może tak być w przypadku bardzo dużych zapytań z wieloma joinami i dostępem do ogromnych tabel).

23. Zawsze zamykaj zapytania z dużym sortowaniem

Dopóki zapytanie SQL z sortowaniem (ORDER BY, GROUP BY, UNION, distinct) nie jest zamknięte, Firebird przechowuje posortowane rekordy w pamięci. Rozmiar pamięci przeznaczonej na sortowanie jest ustawiany parametrem TempCacheLimit w firebird.conf, domyślnie wynosi 64 MB.

Nawet przy zwiększonym TempCacheLimit, długo działające zapytania z dużą liczbą posortowanych rekordów ostatecznie zużyją całą przydzieloną pamięć, a w rezultacie sortowanie przejdzie do plików tymczasowych (tj. na dysk). W efekcie może to prowadzić do znacznego spowolnienia.

Zalecenie jest takie, aby zamykać wszystkie takie zapytania w odpowiednim czasie.

Pytania?

Zapraszam do kontaktu w razie jakichkolwiek pytań: [email protected]!