Too many connections - jak zwiększyć limit i po co pooling

PostgreSQL

Too many connections - jak zwiększyć limit i po co pooling

W skrócie

Błąd o zbyt wielu połączeniach potrafi położyć aplikację w najgorszym momencie, a pierwszym odruchem zwykle jest podkręcenie limitu do dużej liczby. To rozwiązanie pozorne, bo każde połączenie kosztuje pamięć i moc serwera. Pokażemy Ci, dlaczego limit istnieje, jak bezpiecznie go zmienić, gdy naprawdę trzeba, i dlaczego pula połączeń rozwiązuje problem lepiej niż samo zwiększanie liczby. Temat dotyczy każdego, kogo baza obsługuje wiele równoległych klientów.

Jak to wygląda w praktyce

W logu aplikacji i w dzienniku bazy pojawia się komunikat mówiący, że jest zbyt wiele klientów naraz i że przekroczono dozwoloną liczbę połączeń. Nowe żądania nie mogą się połączyć, a użytkownicy widzą błędy albo długie oczekiwanie. Co ciekawe, ruch wcale nie musiał wzrosnąć skokowo, bo problem często narasta powoli, w miarę jak połączenia zostają otwarte i nie wracają do puli. Bywa też, że nie możesz się zalogować nawet Ty, administrator, bo wolne miejsca są zajęte przez zwykłe sesje aplikacji.

Dlaczego tak się dzieje

W PostgreSQL każde połączenie klienta obsługuje osobny proces serwera. To model prosty i solidny, ale ma swoją cenę: proces zajmuje pamięć i zasoby systemu operacyjnego niezależnie od tego, czy akurat coś robi, czy tylko czeka bezczynnie. Dlatego istnieje parametr max_connections, który ogranicza liczbę jednoczesnych połączeń. Część slotów jest dodatkowo rezerwowana na połączenia administracyjne przez parametr superuser_reserved_connections, żebyś mógł się zalogować nawet przy pełnej bazie. Limit wyczerpuje się najczęściej nie dlatego, że masz realnie tak wielu aktywnych użytkowników, tylko dlatego, że aplikacja albo pula po jej stronie otwiera połączenia i nie zwalnia ich w porę. Do tego dochodzą sesje, które wiszą w stanie bezczynności, czasem w środku otwartej transakcji, i blokują slot bez żadnej pracy. Samo podniesienie limitu do dużej liczby przenosi wtedy problem na pamięć serwera, bo setki procesów potrafią ją wyczerpać.

Jak to rozwiązać krok po kroku

1. Najpierw zobacz, co faktycznie zajmuje połączenia. Wykonaj SELECT count(*) FROM pg_stat_activity;, a potem obejrzyj kolumnę state, żeby odróżnić sesje aktywne od tych w stanie idle i idle in transaction.
2. Znajdź i posprzątaj sesje wiszące bezczynnie w otwartej transakcji, bo to one najczęściej marnują sloty. Sprawdź, dlaczego aplikacja zostawia otwarte transakcje, i napraw to po stronie kodu.
3. Jeśli winna jest pula po stronie aplikacji, ogranicz jej maksymalny rozmiar. Wiele klientów utrzymuje zbyt duże pule, które w sumie przekraczają limit bazy.
4. Rozważ pooler połączeń przed bazą, na przykład PgBouncer. Utrzymuje on niewielką liczbę realnych połączeń do PostgreSQL i multipleksuje na nie ruch od wielu klientów, dzięki czemu baza nie musi obsługiwać setek procesów.
5. Dopiero gdy realna liczba potrzebnych połączeń jest naprawdę wysoka, podnieś parametr max_connections w pliku konfiguracyjnym i wykonaj restart, bo ten parametr nie zmienia się przez samo przeładowanie. Rób to z rozwagą i z uwzględnieniem dostępnej pamięci.
6. Po zmianie obserwuj zużycie pamięci serwera, żeby wyższy limit nie doprowadził do sytuacji, w której system operacyjny zaczyna zabijać procesy bazy.

Jak sprawdzić, że zadziałało

Aktualny limit potwierdzisz poleceniem SHOW max_connections;, a liczbę zajętych slotów zapytaniem zliczającym wiersze w pg_stat_activity. Po wdrożeniu poolera zobaczysz, że liczba realnych połączeń do PostgreSQL jest stabilna i niska, mimo że aplikacja obsługuje dużo więcej klientów. Błąd o zbyt wielu połączeniach powinien zniknąć, a Ty jako administrator powinieneś móc się zalogować w każdej chwili dzięki zarezerwowanym slotom. Dobrym sprawdzianem jest obserwacja przez pewien czas, czy liczba sesji w stanie idle in transaction nie rośnie, bo jej wzrost oznacza, że źródło problemu wróci.

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