Jak bezpiecznie zmienić shared_buffers i work_mem

PostgreSQL

Jak bezpiecznie zmienić shared_buffers i work_mem

W skrócie

shared_buffers i work_mem to dwa najczęściej dotykane parametry pamięci w PostgreSQL, a zarazem dwa, przy których najłatwiej zrobić sobie krzywdę. Pierwszy wpływa na start serwera, drugi potrafi po cichu zsumować się do ilości pamięci, której serwer nie ma. Wyjaśnimy Ci, czym te parametry się różnią, jak je zmienić bezpiecznie i jak sprawdzić, że zmiana pomogła, a nie zaszkodziła. Temat jest dla każdego, kto stroi wydajność bazy na własnym serwerze.

Jak to wygląda w praktyce

Podnosisz shared_buffers do dużej wartości, restartujesz serwer, a on nie wstaje i w logu widzisz błąd o pamięci współdzielonej. Albo zwiększasz work_mem, żeby przyspieszyć sortowania, i faktycznie pojedyncze zapytanie działa szybciej, ale przy większym ruchu serwerowi zaczyna brakować pamięci i system operacyjny zabija procesy bazy. Bywa też, że zmieniasz parametr i nic się nie dzieje, bo akurat ten wymagał restartu, a Ty zrobiłeś tylko przeładowanie. Wspólny objaw to rozczarowanie: albo baza nie startuje, albo zmiana nie pomaga, albo pomaga jednemu zapytaniu kosztem stabilności całości.

Dlaczego tak się dzieje

shared_buffers to jeden wspólny obszar pamięci, w którym PostgreSQL trzyma podręczne kopie stron danych, żeby nie sięgać za każdym razem na dysk. Jest przydzielany raz, przy starcie serwera, dlatego jego zmiana wymaga restartu, a zbyt duża wartość może przekroczyć limity pamięci współdzielonej systemu i uniemożliwić start. work_mem działa na odwrót. To limit pamięci na pojedynczą operację, taką jak sortowanie czy budowa tablicy skrótów przy łączeniu, wewnątrz jednego zapytania. Jeśli zapytanie ma kilka takich operacji naraz, każda z nich może wziąć do work_mem, więc jedno zapytanie potrafi zużyć wielokrotność tej wartości. Do tego dochodzi mnożnik połączeń: przy wielu równoległych sesjach, z których każda wykonuje ciężkie zapytania, łączne zapotrzebowanie to work_mem razy liczba operacji razy liczba połączeń. Dlatego bezpieczne shared_buffers to zwykle ułamek pamięci serwera, a work_mem trzyma się umiarkowanie, bo jego skutki się zwielokrotniają.

Jak to rozwiązać krok po kroku

1. Zacznij od poznania stanu. Odczytaj obecne wartości poleceniami SHOW shared_buffers; i SHOW work_mem; oraz sprawdź, ile pamięci ma serwer, żeby mieć punkt odniesienia.
2. shared_buffers ustaw jako rozsądny ułamek pamięci maszyny, pamiętając, że reszta jest potrzebna systemowi i cache dyskowemu systemu operacyjnego. Zmianę wprowadź w pliku konfiguracyjnym albo przez ALTER SYSTEM.
3. Po zmianie shared_buffers wykonaj restart serwera, bo ten parametr działa dopiero od nowego startu. Zaplanuj to w oknie serwisowym.
4. work_mem podnoś ostrożnie i małymi krokami. Zamiast dużej wartości globalnej rozważ ustawienie jej tylko tam, gdzie trzeba, na przykład dla konkretnej roli raportowej albo w sesji przez SET tuż przed ciężkim zapytaniem.
5. Przy szacowaniu bezpiecznego work_mem weź pod uwagę mnożnik: pomnóż go przez spodziewaną liczbę operacji w zapytaniu i przez liczbę równoległych połączeń, żeby suma nie przekroczyła dostępnej pamięci.
6. work_mem nie wymaga restartu, więc po zmianie w pliku wystarczy przeładowanie konfiguracji, a zmiana ustawiona w sesji działa od razu. Wprowadzaj zmiany pojedynczo i obserwuj skutki.

Jak sprawdzić, że zadziałało

Nowe wartości potwierdzisz ponownym SHOW shared_buffers; i SHOW work_mem;. Skuteczność work_mem zweryfikujesz na konkretnym zapytaniu za pomocą EXPLAIN z opcją ANALYZE. W planie zwróć uwagę, czy operacje sortowania i łączenia mieszczą się w pamięci, czy zapisują dane tymczasowe na dysk. Zniknięcie zapisu na dysk przy ciężkiej operacji to znak, że work_mem jest już wystarczający. Dla shared_buffers dobrym sygnałem jest to, że serwer wstał bez błędów pamięci współdzielonej, a przy dłuższej obserwacji częściej odczytujesz dane z bufora niż z dysku. Cały czas pilnuj zużycia pamięci serwera, bo prawdziwym sprawdzianem bezpieczeństwa jest brak zabijania procesów bazy przy realnym obciążeniu.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5