PostgreSQL posiada starannie dobraną konfiguracje domyślną, aby zapewnić jego działanie na każdym zainstalowanym środowisku, spełniającym minimalne wymagania silnika. Z tego powodu zawsze zaleca się, aby jednym z pierwszych kroków po zakończeniu instalacji tego silnika baz danych było dostrojenie i skonfigurowanie kilku parametrów. Ich modyfikacja pozwoli administratorowi baz danych uzyskać maksymalną wydajność PostgreSQL.
Poniżej omówione zostaną cztery najważniejsze parametry: shared_buffers, wal_buffers, effective_cache_size i maintenance_work_mem. Przed przystąpieniem do lektury, rzuć też okiem na: Architektura PostgreSQL!
shared_buffers
PostgreSQL używa „podwójnego buforowania”, co oznacza, że PostgreSQL używa własnego wewnętrznego bufora oraz buforowania wejścia-wyjścia jądra systemowego. Dane przechowuje się w pamięci dwukrotnie.
Bufor PostgreSQL nazywa się shared_buffers i określa, ile dedykowanej pamięci systemowej PostgreSQL będzie używać do buforowania.
Ze względu na wybór projektowy PostgreSQL mający na celu zapewnienie kompatybilności na wszystkich obsługiwanych maszynach i systemach operacyjnych, wartość ta jest domyślnie ustawiona na stosunkowo niski poziom. Dlatego zwiększenie wartości shared_buffers jest jednym z ustawień, które mają największy wpływ na ogólną wydajność na większości nowoczesnych systemów operacyjnych.
Nie ma jednej zalecanej konkretnej wartości dla shared_buffers, ale obliczenie wartości dla konkretnego systemu nie jest szczególnie trudne.
Ogólnie rzecz biorąc, wartość shared_buffers powinna wynosić około 25% całkowitej pamięci RAM systemu dla dedykowanego serwera baz danych. Wartość shared_buffers nigdy nie powinna być ustawiana tak, aby rezerwować całą pamięć RAM systemu dla PostgreSQL. Wartość powyżej 25% pamięci RAM systemu może być przydatna, jeśli na przykład jest ustawiona tak, aby cały zestaw roboczy bazy danych zmieścił się w pamięci podręcznej, co znacznie zmniejszy ilość czasu potrzebnego na odczyt z dysku.
Większa wartość shared_buffers może zwiększyć wydajność w przypadkach dużej ilości odczytów. Rodzi jednocześnie ryzyko degradacji wydajności PostgreSQL podczas zapisów. Należy pamiętać, że zawartość bufora jest regularnie zrzucana na dysk. Im większy jest bufor, tym więcej danych musimy zapisywać na dysk, obniżając jego przepustowość.
wal_buffers
Write-Ahead Logging (WAL) to standardowa metoda zapewnienia integralności danych. Podobnie jak wprzypadku ustawienia shared_buffers, PostgreSQL zapisuje rekordy WAL w buforach, a następnie buforowane dane są zapisywane na dysk.
Domyślny rozmiar bufora jest określany przez ustawienie wal_buffers i początkowo wynosi 16 MB. Jeśli system, który jest dostosowywany, ma dużą liczbę równoczesnych połączeń, to wyższa wartość wal_buffers może zapewnić lepszą wydajność PostgreSQL.
effective_cache_size
Ustawienie effective_cache_size ma reputację skomplikowanego ustawienia w PostgreSQL i dlatego często pozostawia się je domyślnej wartości.
Wartość effective_cache_size dostarcza „przybliżoną szacunkową” ilość pamięci dostępnej do buforowania dysku przez system operacyjny i samą bazę danych. Oczywiście po uwzględnieniu tego, co jest używane przez sam system operacyjny i inne aplikacje.
Ta wartość jest używana tylko przez optymalizator zapytań PostgreSQL do określania, czy plany zapytań zmieszczą się w pamięci RAM. Przeciętna wartość effective_cache_size wynosiłaby połowę całkowitej pamięci dostępnej w systemie. Najczęściej wartość ta jest ustawiana na 75% całkowitej pamięci systemowej na dedykowanym pod bazę danych serwerze.
Jeśli wartość effective_cache_size jest zbyt niska, optymalizator zapytań może zdecydować się nie używać niektórych indeksów, nawet jeśli znacznie przyspieszyłyby one zapytania.
Jeżeli chcesz nauczyć się konfigurować parametry PostgreSQL, sprawdź kompleksowy Kurs Praktyczna Administracja PostgreSQL!
work_mem
Wartość work_mem jest używana do złożonych operacji sortowania i określa maksymalną ilość pamięci, która ma być użyta per operacja.
Kiedy wartość work_mem jest odpowiednio dostrojona, większość operacji sortowania i łączenia tabel jest wykonywana w znacznie szybszej pamięci, a nie zapisywana i odczytywana z wolnego dysku. Wydajność PostgreSQL w dużej mierze opiera się właśnie o ten parametr.
Jednak ważne jest, aby upewnić się, że wartość work_mem nie jest ustawiona zbyt wysoko, ponieważ może „zawężać” dostępną pamięć w systemie, gdy użytkownicy wykonują operacje sortowania.
Ustawienie work_mem na wartość 4MB, sugerowało by że dana sesja ma 4MB na swoje sortowania. Nic bardziej mylnego, w praktyce jest to 4MB na każdą z operacji w zapytaniu. Pojedyncze zapytanie SQL, posiadające np. 3 JOINY i jedną klauzule ORDER BY śmiało może użyć 16MB pamięci – po 4MB na każdą operację.
Ze względu na to istotne zastrzeżenie, idealne jest ustawienie globalnej wartości work_mem na stosunkowo niski poziom. Jeśli zachodzi potrzeba, zawsze możemy zwiększać parametr ten na poziomie pojedynczej sesji użytkownika, przy pomocy komendy SET.
maintenance_work_mem
Podczas gdy work_mem określa, ile pamięci jest używane do złożonych operacji sortowania, maintenance_work_mem określa, ile pamięci jest używane do rutynowych zadań konserwacyjnych, takich jak VACUUM, CREATE INDEX i podobne. Zadania te służą do optymalizacji struktury PostgreSQL.
W przeciwieństwie do work_mem, tylko jedna z tych operacji wykonuje się w danym momencie per sesja. W rezultacie większość systemów nie ma wielu tych procesów uruchomionych równocześnie. Zazwyczaj można bezpiecznie ustawić wartość parametru dużo wyższą niż work_mem. Większa ilość dostępnej pamięci może poprawić wydajność odkurzania i przywracania kopii bazy danych.
Domyślna wartość maintenance_work_mem to 64 MB.