Strojenie PostgreSQL

Strojenie PostgreSQL

PostgreSQL jest najbardziej rozwiniętą opensource’ową bazą danych. Jej nieco mniejszą popularność w porównaniu z np. MySQL można tłumaczyć chęcią używania przez deweloperów gotowych rozwiązań, które z reguły oparte są MySQL (LAMP).

Ja jednak jestem zaprzysiężonym obrońcą PostgreSQL i jej ogromnej elastyczności. Baza ta może służyć jako źródło danych małej strony, średniej aplikacji biznesowej, czy być częścią ogromnego klastra bazodanowego (poguglaj nt. Skype’a i jego planów dotyczących obsługi miliarda użytkowników za pomocą tej bazy). Elastyczność mechanizmu idzie jednak w parze ze wzrostem poziomu skomplikowania konfiguracji i tzw. strojenia bazy.

Poniżej przedstawię podstawowe wskazówki, dotyczące konfiguracji PostgreSQL do pracy jako źródło danych aplikacji biznesowej dla małych i średnich przedsiębiorstw. Będzie to dedykowany serwer z procesorem wielordzeniowym (2+) oraz dość dużą ilością pamięci RAM (4GB+). Serwer będzie obsługiwał tzw. bazę OLTP (ang. On-line Transaction Processing), która charakteryzuje się dużą ilością transakcji zapisu i odczytu. W aplikacjach biznesowych będziemy kłaść nacisk na zachowanie integralności danych i efektywność. Jako system operacyjny przyjmiemy Debian Linux (lub pokrewne).

Konfiguracja PostgreSQL, jaka dostarczana jest wraz z pakietem dla Debiana, dostosowana jest do “średnich potrzeb użytkownika” i często nie trzeba jej zmieniać. Jednak w poszczególnych przypadkach, bardziej lub mniej delikatne strojenie może być przydatne.

Proces strojenia należy zacząć od określenia wymagań, jakim ma sprostać baza. Troszkę poszedłem na łatwiznę ograniczając sobie już w założeniach, co baza będzie robić. Teraz czas na resztę.

1. Strojenie silnika bazy danych.

Postgresql.conf oferuje dostęp do szeregu opcji procesu postmaster, którymi możemy zmienić zachowanie całego silnika:

  • max_connections – maksymalna ilość jednocześnie otwartych połączeń. Ustawiamy zależnie od mechanizmu, który będzie się z bazą łączył. W przypadku aplikacji webowej, wykorzystywanej w średniej firmie, czy serwera aplikacji JBoss warto rozważyć wartość około 100 jednoczesnych połączeń. Lepiej odrzucić połączenie, jeśli skończy nam się pula wolnych, niż ryzykować obniżenie wydajności całego systemu. Często także stosuje się tzw. poolery połączeń, czyli mechanizmy, które utrzymują określoną ilość otwartych połączeń z bazą, tak aby klient nie musiał zajmować się kosztownym otwieraniem i zamykaniem połączenia.
  • shared_buffers – ilość przydzielonej pamięci cache całego serwera. Jest to podstawowa opcja, którą można znacząco zwiększyć wydajność bazy. Ustawiana na około 25% rozmiaru dostępnej pamięci RAM. Większość testów wykazuje, że przekroczenie 33% powoduje powolne obniżenie wydajności
  • effective_cache_size – ilość pamięci, jaka będzie przeznaczana na utworzenie cache zapytania. Także ta wartość decyduje o tym, czy planer zapytania użyje indeksów. Ogólnie ustawiana na 50% dostępnej pamięci RAM
  • work_mem – ilość pamięci dostępna dla pojedynczego klienta. Wartość ma bardzo duże znaczenie przy sortowaniu wyników, czy hashowaniu tabel. Należy pamiętać, że jest to wartość przydzielana każdemu klientowi. Jeśli więc kilka połączonych klientów wykonuje jednocześnie operacje pobierania i sortowania danych, ilość zużytej pamięci zwiększy się n-krotnie.
  • max_fsm_pages – parametr określający dopuszczalną ilość usuniętych wierszy tabel, przy których zmiany nie są zapisywane na dysk. Przy częstych operacjach typu insert/delete należy odpowiednio zwiększyć wartość parametru, aby uniknąć nadmiernej defragmentacji tabeli. Parametr ten został usunięty w wersji 8.4.
  • fsync – włącza lub wyłącza synchronizację transakcji na dysk. Wyłączenie synchronizacji zwiększa wydajność, ale może doprowadzić do uszkodzenia danych. Tym parametrem zajmiemy się w dalszej części.
  • random_page_cost– określa koszt odczytu sekwencyjnego z dysku. Im większa wartość, z tym większym prawdopodobieństwem planer zastosuje sekwencyjny odczyt, zamiast indeksowanego. Zwiększanie wartości ma sens tylko przy naprawdę szybkich dyskach serwera.
  • wal_buffers – określa rozmiar pliku WAL. Dobrym rozwiązaniem będzie ustawienie go na 16MB
  • checkpoint_completion_target – parametr wskazuje kiedy rozpocząć zapis do nowego pliku WAL. Od wersji 8.3 określamy go jako wskaźnik rozproszenia zapisu między plikami po osiągnięciu x% zajętości rozmiaru z reguły 16MB). Warto go ustawić na 0.9

W przypadku serwerów produkcyjnych należy także pamiętać o ograniczeniu poziomu logowania do niezbędnego minimum. Każda dodatkowa operacja I/O na dyskach powoduje ograniczenie wydajności.

Te podstawowe parametry bazują w dużej mierze na pamięci współdzielonej serwera. Większość systemów ma jednak ograniczoną maksymalną ilość pamięci typu shared do bardzo małych wartości (Linux – zwykle do ok 32MB).
W systemach linuksowych możemy (i powinniśmy!) te wartości zwiększyć do naszych potrzeb za pomocą wpisów w plik sysctl.conf:

kernel.shmall = 524288
kernel.shmmax = 2147483648
kernel.shmni = 4096

Wartość shmmax reprezentuje maksymalny rozmiar w bajtach pojedynczego segmentu pamięci współdzielonej.
Na systemach 32-bitowych ta wartość nie powinna przekraczać 4294967295 (na 64-bitowych to ograniczenie rzecz jasna jest “nieco” większe :).
Wartość shmall ogólnie można przyjąć jako ceil(shmmax/4096).
Dla systemów 64-bitowych z większą ilością pamięci RAM, można te wartości zwiększyć.

Po zapisaniu pliku sysctl.conf wprowadzamy zmiany do jądra poleceniem

sysctl -p

Warto jeszcze zająć się parametrem wal_sync_method (ang. write ahead log …). Parametr kontroluje mechanizm, który zapisuje na dysk transakcję, zanim tak naprawdę transakcja zostanie zakończona i sfinalizowana.

Jeśli ktokolwiek zdecyduje się na “grzebanie” z tym parametrem, należy pamiętać o możliwości utraty danych. Jego zmiana może być usprawiedliwiona, jeśli dysponujemy bardzo dobrym kontrolerem SCSI czy RAID, który nie kłamie (tak, tak), że zapisał już dane na dysk.

Do celów testowych warto zaobserwować zmiany, jakie wprowadza parametr fsync=off, ale na systemach produkcyjnych nie zaleca się wyłączania synchronizacji, chyba, że bardzo ufamy swojemu dostawcy energii elektrycznej, zasilaczom awaryjnym, sprzętowi itd.

2. Strojenie struktury bazy danych.

Administrator bazy danych powinien zawsze współpracować z deweloperami. Oto kilka wskazówek ogólnych dotyczących struktury danych:

  • Utrzymuj tabele z możliwie małą ilością kolumn. Nie zawsze potrzebujesz przecież wszystkich danych. Naprawdę warto rozszerzać tabele.
  • Stosuj indeksy. To trywialne zdawałoby się zalecenie, często nie jest przestrzegane. “Przecież mój mega-kosmiczny ORM dba o bazę, prawda?” Nic bardziej błędnego. Twórz indeksy i pamiętaj, aby klucze, po których łączysz tabele były możliwie tego samego typu (lub baza umożliwiała ich cast’owanie).
  • Używaj często EXPLAIN ANALYZE, jest to podstawa przy pisaniu i optymalizacji zapytań.
  • Ograniczaj pobierane dane do minimum. Często pomagają w tym tzw. kursory, umożliwiając pobieranie danych po jednym wierszu.

3. Sprzęt.

PostgreSQL bardzo lubi wiele rdzeni w procesorze. Każde zapytanie od połączonego klienta będzie w miarę możliwości kierowane do odrębnego rdzenia, co znacząco podnosi wydajność. Jednak jeśli chodzi o skalowanie samej maszyny rozważ taką kolejność:

  • Przede wszystkim odrębna maszyna do obsługi bazy danych – to pierwszy krok w skalowaniu. Oddziel źródło danych od serwera, gdzie uruchomiona jest aplikacja.
  • Pamięć RAM – zwiększenie powyżej 4GB pozwoli na obsługę naprawdę dużych baz, czy uruchamianie kilku instancji serwera.
  • Dyski – SCSI w RAID 1+0, możliwie na kontrolerze MEGARAID. To pozwoli na zwiększenie ilości zapisywanych transakcji/s oraz podniesie bezpieczeństwo danych.
  • CPU z dużą ilością cache pozwoli na dodatkowy “boost”.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.