Jak skonfigurować PgBouncer, gdy brakuje połączeń

PostgreSQL

Jak skonfigurować PgBouncer, gdy brakuje połączeń

W skrócie

Objaw jest znajomy: pod obciążeniem aplikacja rzuca "sorry, too many clients already", choć baza wcale nie jest przemęczona zapytaniami. Odruchowo podnosisz max_connections - i robisz gorzej, bo każde połączenie kosztuje pamięć i przełączanie kontekstu. Właściwym lekarstwem jest pooler połączeń, a w świecie PostgreSQL standardem jest lekki PgBouncer, który jednym uchwytem obsługuje setki klientów, trzymając do bazy tylko garść realnych połączeń.

Jak to wygląda w praktyce

Aplikacja webowa albo mikroserwis otwiera nowe połączenie na każde żądanie, robi jedno krótkie zapytanie i zamyka. Przy skoku ruchu liczba równoczesnych połączeń przebija max_connections i baza zaczyna odrzucać klientów komunikatem "FATAL: sorry, too many clients already". Podnosisz limit do kilku tysięcy - błąd znika na chwilę, ale serwer zaczyna zjadać pamięć, rośnie zużycie procesora na samą obsługę procesów, a wydajność spada, mimo że faktycznej pracy zapytaniowej jest niewiele. Widać to w pg_stat_activity: mnóstwo połączeń w stanie idle, które nic nie robią, a tylko zajmują sloty.

Dlaczego tak się dzieje

PostgreSQL stosuje model proces-na-połączenie: dla każdego klienta uruchamia osobny proces backendu. Taki proces kosztuje pamięć i zasoby jądra, a jego założenie nie jest darmowe. Model świetnie sprawdza się przy umiarkowanej liczbie trwałych połączeń, ale fatalnie przy tysiącach krótkich - bo płacisz za ciągłe tworzenie i niszczenie procesów oraz za setki bezczynnych backendów. Parametr max_connections nie jest więc wartością "im więcej, tym lepiej": każdy slot rezerwuje pamięć niezależnie od tego, czy jest używany. PgBouncer rozwiązuje to jako cienki pośrednik. Utrzymuje mały zestaw stałych połączeń do bazy i wypożycza je klientom tylko na czas, gdy realnie potrzebują serwera. Tysiąc klientów aplikacji może więc dzielić dwadzieścia realnych połączeń, bo w danej chwili aktywnych jest zwykle niewiele.

Jak to rozwiązać krok po kroku

1. Zainstaluj PgBouncer z repozytorium systemowego (na Debian/Ubuntu: apt install pgbouncer). To osobny, bardzo lekki proces, może stać na tym samym serwerze co baza.
2. W pliku pgbouncer.ini zdefiniuj bazę docelową w sekcji [databases], na przykład sklep = host=127.0.0.1 port=5432 dbname=sklep. To pod tą nazwą aplikacja będzie się łączyć do poolera.
3. Wybierz tryb poolingu przez pool_mode. Dla typowej aplikacji webowej najbardziej oszczędny jest transaction (połączenie wraca do puli po każdej transakcji). Jeśli aplikacja używa prepared statements albo cech przypiętych do sesji, bezpieczniejszy jest session.
4. Ustaw rozmiary puli: default_pool_size to liczba realnych połączeń do bazy na parę użytkownik-baza (rozsądnie kilkanaście do kilkudziesięciu), a max_client_conn to ilu klientów PgBouncer w ogóle przyjmie (może iść w tysiące).
5. Skonfiguruj uwierzytelnianie: wskaż auth_type (na przykład scram-sha-256) i plik auth_file z użytkownikami oraz ich skrótami haseł, w formacie akceptowanym przez PgBouncer.
6. Uruchom usługę i przekieruj aplikację na port poolera (domyślnie 6432) zamiast na 5432 - w connection stringu zmieniasz tylko host i port, reszta zostaje.
7. Dostrój max_connections na samej bazie tak, by z zapasem pokryło sumę pooli PgBouncera plus połączenia administracyjne - dzięki poolerowi ta wartość może być niska, a nie rozdmuchana do tysięcy.

Jak sprawdzić, że zadziałało

PgBouncer ma własną pseudo-bazę administracyjną. Połącz się z nią (psql -h 127.0.0.1 -p 6432 -U uzytkownik pgbouncer) i wykonaj SHOW POOLS; - zobaczysz, ilu klientów jest aktywnych i ile realnych połączeń serwerowych faktycznie używa pooler. Polecenie SHOW STATS; pokaże ruch zapytań przez poolera. Po stronie samej bazy sprawdź SELECT count(*) FROM pg_stat_activity; - powinno być znacznie mniej połączeń niż liczba klientów aplikacji, na poziomie zbliżonym do rozmiaru puli. Ostateczny dowód sukcesu to zniknięcie błędu "too many clients" pod obciążeniem, przy niewielkim i stabilnym zużyciu pamięci serwera - zamiast rosnącej liczby bezczynnych backendów widzisz stały, mały zestaw połączeń, które realnie pracują.

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