Postgres strojenie produkcji, które zmniejsza opóźnienia

postgresql.confw stanie domyślnym został zaprojektowany tak, aby nigdy nie zawieść instalacji i nie absorbować ruchu. Historyczna wartość shared_buffers, 128 MB, umożliwia uruchomienie Postgres na minimalnej maszynie bez rezerwowania żadnych krytycznych zasobów. max_connections o wartości 100 mieści się na małym współdzielonym serwerze. Żadne z nich nie zostało wybrane dla rzeczywistego obciążenia.

Zatem problemem nie jest to, że Postgres jest domyślnie źle ustawiony: po prostu nigdy nie był ustawiony dla Ciebie. W poniższych sekcjach omówiono ustawienia w kolejności, w której są one najbardziej opłacalne, od najczęstszych wąskich gardeł (połączenia) do najwolniej występujących (WAL).

Pierwszy projekt to nie pamięć, to połączenia. Każde połączenie Postgres otwiera dedykowany proces serwera, który zużywa pamięć RAM i czas procesora kontekstowego, nawet jeśli jest bezczynny. Zwiększanie max_connections, aby uniknąć błędów typu „zbyt wiele połączeń”, przesuwa problem: powyżej pewnej liczby jednoczesnych aktywnych połączeń rywalizacja o procesor powoduje zmniejszenie opóźnienia wszystkich żądań, w tym najszybszych.

Prawidłowe podejście odwraca zwykłą kolejność: skaluj max_connections na rzeczywistej współbieżności serwera, a następnie wchłonij współbieżność po stronie aplikacji za pomocą modułu pulowego, takiego jak PgBouncer, do pool_mode=transaction. Pooler multipleksuje setki połączeń klientów na kilka faktycznie aktywnych połączeń z serwerem. W naszym dedykowanym artykule szczegółowo opisano mechanizm trybu transakcyjnego i jego limity (przygotowane zestawienia, LISTEN/NOTIFY) oraz porównanie PgBouncer, Supavisor i PgCat w celu wyboru implementacji.

max_connections to parametr kontekstowy „postmaster”: zmiana go wymaga pełnego restartu serwera, a nie prostego przeładowania. W dedykowanych instancjach Postgres Aurabase (plany Pro i Enterprise, jeden klaster CNPG na projekt) to ustawienie jest konfigurowane na poziomie planu, a nie pozostawiane na wartości domyślnej. Ponowne uruchomienie nie jest prostą operacją do powtórzenia w produkcji, co uzasadnia ten wybór etapami, a nie stałą wartością. Wzór na wybór własnej wartości wraz z jej limitami jest tematem osobnego artykułu: size max_connections.

Cztery ustawienia pamięci ważą więcej niż wszystkie pozostałe razem wzięte: shared_buffers, effective_cache_size, work_mem i maintenance_work_mem. Pierwsze trzy określają, ile danych Postgres przechowuje w pamięci przed powrotem na dysk; czwarty określa szybkość tworzenia PRÓŻNI lub indeksu.

shared_buffers ustawia wewnętrzną pamięć podręczną współdzieloną przez wszystkie połączenia. Punktem odniesienia powszechnie dokumentowanym w projekcie PostgreSQL jest około 25% pamięci RAM dostępnej na dedykowanym serwerze bazy danych. Poza tym zyski maleją, a kontrolę przejmuje pamięć podręczna dysku systemu operacyjnego. effective_cache_size niczego nie przydziela: jest to szacunkowa wartość przekazana planiście zapytań dotycząca całkowitej pamięci dostępnej dla pamięci podręcznej (łącznie Postgres i system operacyjny). Niedowymiarowanie popycha program planujący w kierunku skanowania sekwencyjnego, podczas gdy indeks będzie w dużej mierze buforował; obecny benchmark wynosi od 50 do 75% pamięci RAM.

work_mem to najczęstsza pułapka. To nie jest limit globalny. Każda operacja sortowania lub mieszania w zapytaniu może zająć jego własny udział, a zapytanie z wieloma łączeniami może zarezerwować swój udział kilka razy. Zbyt duża wartość w połączeniu z wysokim max_connections może wyczerpać pamięć RAM serwera przy jednoczesnym obciążeniu, nawet jeśli każde żądanie rozpatrywane osobno wydaje się rozsądne. maintenance_work_mem, odwrotnie, może pozostać znacznie bardziej hojny: dotyczy tylko operacji konserwacyjnych (VACUUM, CREATE INDEX), które rzadko są ze sobą zbieżne.

Tylko shared_buffers wymaga ponownego uruchomienia. Pozostałe trzy są ładowane ponownie na gorąco, w tym dla izolowanej sesji: SET work_mem = '64MB'; na czas trwania pojedynczego żądania zachłannego, bez wpływu na ustawienia globalne.

Nigdy nie wyłączaj automatycznej próżni w produkcji, nawet tymczasowo, aby „zwolnić zasoby” podczas szczytowego obciążenia. Postgres używa MVCC: każda AKTUALIZACJA i każde DELETE pozostawiają martwą linię, którą może odzyskać tylko próżnia. Bez tego tabele się rozrastają, indeksy ulegają degradacji, a plany wykonania stopniowo się pogarszają, a błędy nie są widoczne aż do późnej pory.

Domyślna wartośćautovacuum_vacuum_scale_factor (20% martwych wierszy przed wyzwoleniem) jest odpowiednia dla małej tabeli, a nie tabeli wymagającej intensywnego zapisu, zawierającej wiele milionów wierszy. W tabeli zawierającej 10 milionów wierszy to 20% reprezentuje 2 miliony martwych wierszy zgromadzonych przed pierwszym przebiegiem. Obniżaj ten próg tabela po tabeli, zamiast zmieniać ogólną wartość całej bazy danych.

-- Obniż próg na stole z dużą ilością zapisu, a nie ogólnie
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000
);

Advisor zintegrowany z Aurabase Studio sprawdza tę konfigurację przy każdej analizie projektu, w taki sam sposób, jak tabele bez kluczy podstawowych lub nieindeksowanych kluczy obcych. Jest to raczej wyraźny sygnał niż cicha degradacja odkryta zbyt późno.

Większość problemów z opóźnieniami w produkcji nie wynika ani z procesora, ani z pamięci RAM: wynikają one z braku lub źle wybranego indeksu. Przed dotknięciem pojedynczego parametru postgresql.conf, EXPLAIN (ANALYZE, BUFFERS) w danym zapytaniu pozostaje najbardziej wiarygodną diagnozą. Zmniejsza opóźnienia żądań Postgres na długo przed dodaniem zasobów.

-- Przed dotknięciem konfiguracji zdiagnozuj powolne zapytanie
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = '…'
ORDER BY o.created_at DESC
LIMIT 20;

Seq Scan w wielomilionowej tabeli, gdzie oczekiwano Index Scan, prawie zawsze sygnalizuje problem z indeksem. Najczęściej pojawiają się trzy przyczyny: brak indeksu, typ kolumny niezgodny z istniejącym indeksem lub nieaktualne statystyki po masowym imporcie bez ANALYZE. Dodanie pamięci RAM lub zwiększenie work_mem czasami ukrywa ten objaw na małej ilości danych; problem pojawia się ponownie, gdy tylko stół się powiększy.

Aby zlokalizować te żądania bez przeszukiwania ich jedno po drugim, pg_stat_statements agreguje statystyki wykonania wszystkich żądań serwera. Typowa pułapka: rozszerzenie musi najpierw zostać wymienione w shared_preload_libraries, parametrze kontekstowym „postmaster”, który wymaga ponownego uruchomienia. Bez tego kroku CREATE EXTENSION pg_stat_statements; po cichu powiedzie się, ale nic nie zbierze.

# Wymaga całkowitego ponownego uruchomienia (parametr „postmaster”)
shared_preload_libraries = 'pg_stat_statements'

-- Po ponownym uruchomieniu serwera w każdej bazie danych do audytu:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

To jest dokładnie ten błąd, który backend Aurabase zwraca, gdy nie ma tego rozszerzenia: wyraźna wiadomość zamiast cichej pustej listy, którą można pomylić z „brakem powolnego zapytania”. Studio Advisor idzie dalej: automatycznie klasyfikuje każde żądanie o średnim czasie dłuższym niż 150 ms jako ostrzeżenie, a powyżej 500 ms jako krytyczne. Progi te opierają się na tych samych statystykach pg_stat_statements.

Punkt kontrolny zmusza Postgres do zapisania na dysk wszystkich stron zmodyfikowanych w pamięci od czasu poprzedniej. Domyślnie ten zapis może skupiać się na zbyt krótkim oknie czasowym. Rezultatem jest zauważalny wzrost opóźnienia dysku po stronie aplikacji, rodzaj okresowego spowolnienia, które trudno powiązać z konkretnym żądaniem.

checkpoint_completion_target kontroluje rozprzestrzenianie się tego zapisu w przedziale między dwoma punktami kontrolnymi. Brakuje szczegółu w starszych listach kontrolnych: PostgreSQL 14, wydany w 2021 r., zmienił wartość domyślną z 0,5 na 0,9. W instancji PostgreSQL 16, takiej jak dedykowane klastry dzierżawców Aurabase, to ustawienie jest już domyślnie poprawne; ręczne dostosowywanie ma sens tylko w wersji wcześniejszej niż 14. Zobacz nasze porównanie PostgreSQL 16 vs 17 vs 18 dla innych zmian wersji wpływających na strojenie.

max_wal_size działa w tym samym kierunku: zbyt niska wartość powoduje częstsze występowanie punktów kontrolnych niż oczekiwano, nawet jeśli checkpoint_timeout nie zostało jeszcze osiągnięte. Zwiększenie tej wartości zmniejsza częstotliwość punktów kontrolnych, kosztem dłuższego czasu odzyskiwania po awarii, ponieważ jest więcej WAL do odtworzenia. Kompromis, o którym należy decydować w oparciu o tolerancję dla niedostępności, a nie wartość uniwersalną.

Niniejsza lista kontrolna nie jest jednorazowym audytem, ​​który należy raz sprawdzić przed wejściem do produkcji. Podwojenie wolumenu lub potrojenie ruchu powoduje, że testy porównawcze wybrane podczas uruchamiania stają się przestarzałe, często bez wyraźnych błędów, a jedynie postępującą degradacją opóźnienia p95.

Trzy sygnały wymagają dalszego monitorowania. pg_stat_statements identyfikuje żądania, które z czasem ulegają pogorszeniu. pg_stat_activity zgłasza zablokowane lub wyjątkowo długie zapytania, a stosunek aktywnych połączeń do max_connections przewiduje nasycenie, zanim wygeneruje błędy po stronie aplikacji.

Karta Obserwacja w Studio obejmuje część tej podstawy dla dowolnego projektu Aurabase i nie wymaga instalowania narzędzi innych firm. Wyświetla listę wolnych żądań, umożliwia anulowanie lub zakończenie aktywnego żądania przez PID i wyświetla wskaźnik nasycenia puli, który wyświetla ostrzeżenie powyżej 80% wykorzystania. W instancji hostowanej samodzielnie to samo monitorowanie jest budowane ręcznie, z aktywowanym pg_stat_statements i podłączonym do niego zewnętrznym narzędziem monitorującym.

Osiem ustawień w kolejności, w jakiej są najbardziej opłacalne, wraz z tym, co musisz wiedzieć, zanim ich dotkniesz.

Podane tutaj progi, takie jak 150 ms i 80% nasycenia puli monitorowane przez Aurabase Studio Advisor, są punktem wyjścia zweryfikowanym przez kod, a nie uniwersalną prawdą. Jedynym sędzią ostatecznym pozostaje Twój faktyczny zarzut. Aby dokładnie określić rozmiar max_connections, zamiast kierować się ogólną zasadą, poświęcony jest artykułowi szczegółowo opisującemu wzór i jego ograniczenia.

Trzy pytania, które systematycznie pojawiają się po pierwszym zastosowaniu listy kontrolnej.