Jak dobrać shared_buffers, work_mem i effective_cache_size

PostgreSQL

Jak dobrać shared_buffers, work_mem i effective_cache_size

W skrócie

Trzy parametry pamięci decydują o tym, czy PostgreSQL wykorzystuje serwer, czy dusi się na ustawieniach z pudełka. shared_buffers to własny bufor bazy, work_mem to pamięć na sortowania i haszowanie w pojedynczej operacji, a effective_cache_size to podpowiedź dla planera, ile danych i tak siedzi w pamięci systemu. Źle dobrane, każdy z nich potrafi spowolnić pracę nawet o rzędy wielkości. Pokazujemy, jak je ustawić z głową.

Jak to wygląda w praktyce

Na świeżej instalacji baza często siedzi na 128 MB shared_buffers niezależnie od tego, czy serwer ma 4, czy 128 GB RAM. Objawy to zapytania, które skanują te same tabele wolno mimo powtórzeń, bo dane nie mieszczą się w buforze i ciągle wracają z dysku. Przy zbyt małym work_mem widzimy w EXPLAIN (ANALYZE, BUFFERS) wpisy Sort Method: external merge Disk - sortowanie nie zmieściło się w pamięci i poleciało na dysk. Przy zbyt niskim effective_cache_size planer woli sekwencyjne skany zamiast indeksów, bo błędnie zakłada, że odczyt losowy będzie drogi. Efektem jest baza, która wolno pracuje, choć free -h pokazuje góry wolnej pamięci.

Dlaczego tak się dzieje

PostgreSQL celowo startuje z bardzo zachowawczymi ustawieniami, żeby uruchomił się na dowolnym, nawet malutkim serwerze. To nasze zadanie dopasować je do maszyny. shared_buffers to pamięć, którą baza rezerwuje na własny cache stron - reszty i tak używa system operacyjny na swój cache plikowy. work_mem jest przydzielany na każdą operację sortowania lub haszowania osobno, więc zapytanie z kilkoma sortowaniami i wieloma połączeniami równoległymi może zużyć jego wielokrotność. Dlatego nie ustawiamy go absurdalnie wysoko globalnie. effective_cache_size niczego nie rezerwuje - to wyłącznie informacja dla planera o tym, ile danych realnie da się trzymać w pamięci, łącząc bufor bazy i cache systemu. Gdy jest za niski, planer podejmuje ostrożne, ale wolne decyzje.

Jak to rozwiązać krok po kroku

1. Ustal, ile RAM ma serwer i ile z niego przeznaczasz na bazę. Załóż, że na dedykowanym serwerze bazy oddajemy jej rozsądną większość pamięci, ale zostawiamy zapas na system i na sesje.
2. Ustaw shared_buffers na około 25 procent RAM. Dla serwera z 32 GB będzie to ALTER SYSTEM SET shared_buffers = '8GB';. Większe wartości rzadko poprawiają wynik, bo system i tak buforuje resztę.
3. Ustaw effective_cache_size na około 60-75 procent RAM, na przykład ALTER SYSTEM SET effective_cache_size = '24GB';. To nie rezerwuje pamięci, więc możesz podać odważnie.
4. Dobierz work_mem ostrożnie. Zamiast liczyć globalnie, wyjdź od typowego zapytania. Bezpieczny punkt startu to kilkadziesiąt MB, na przykład ALTER SYSTEM SET work_mem = '64MB';, i podnoś tylko dla zapytań, które realnie sortują na dysku.
5. Dla operacji administracyjnych, takich jak VACUUM czy budowa indeksu, ustaw osobno maintenance_work_mem, na przykład ALTER SYSTEM SET maintenance_work_mem = '1GB';. To przyspiesza tworzenie indeksów.
6. Przeładuj konfigurację: parametry wymagające restartu, jak shared_buffers, wymuszają systemctl restart postgresql, a work_mem i effective_cache_size wystarczy przeładować przez SELECT pg_reload_conf();.

Jak sprawdzić, że zadziałało

Po zmianach potwierdź wartości: SHOW shared_buffers;, SHOW work_mem; oraz SHOW effective_cache_size;. Następnie wróć do wolnego zapytania i wykonaj EXPLAIN (ANALYZE, BUFFERS). Zniknięcie wpisu external merge Disk na rzecz Sort Method: quicksort Memory oznacza, że sortowanie zmieściło się w pamięci. Jeśli planer zaczął wybierać skany po indeksie tam, gdzie wcześniej robił sekwencyjny odczyt całej tabeli, to znak, że effective_cache_size przekazał mu właściwy obraz. Miarą ostateczną jest krótszy czas wykonania mierzony w tym samym EXPLAIN ANALYZE.

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