Jak zwiększyć maksymalną liczbę połączeń w postgresach?

109

Używam Postgres DB dla mojego produktu. Podczas wstawiania wsadowego za pomocą slicka 3 otrzymuję komunikat o błędzie:

org.postgresql.util.PSQLException: KRYTYCZNY: przepraszam, już za dużo klientów.

Moja operacja wsadowa będzie obejmowała ponad tysiące rekordów. Maksymalna liczba połączeń dla moich postgresów to 100.

Jak zwiększyć maksymalną liczbę połączeń?

postgresql

— Strumień
źródło

stackoverflow.com/questions/9798705/…

— user_0

Użyj puli połączeń, takiej jak pgBouncer lub pgPool

— Frank Heikens

1. Najpierw sprawdź, czy oprogramowanie pośredniczące nie utrzymuje otwartych zbyt wielu połączeń lub czy nie przecieka. 2. (być może) następnie użyj puli połączeń. 3. (prawie) nigdy nie zwiększaj liczby dozwolonych połączeń w przypadku tego typu problemu, w większości przypadków pogorszy to sytuację. (chyba że jesteś absolutnie pewny)

— dołącz do

Odpowiedzi:

221

Samo zwiększenie max_connectionsto zły pomysł. Musisz zwiększyć shared_buffersi kernel.shmmaxrównież.

Rozważania

max_connectionsokreśla maksymalną liczbę jednoczesnych połączeń z serwerem bazy danych. Wartość domyślna to zazwyczaj 100 połączeń.

Przed zwiększeniem liczby połączeń może być konieczne skalowanie wdrożenia. Ale wcześniej powinieneś rozważyć, czy naprawdę potrzebujesz zwiększonego limitu połączeń.

Każde połączenie PostgreSQL zużywa pamięć RAM do zarządzania połączeniem lub korzystającym z niego klientem. Im więcej masz połączeń, tym więcej pamięci RAM będziesz używać, którą można zamiast tego użyć do uruchomienia bazy danych.

Dobrze napisana aplikacja zazwyczaj nie wymaga dużej liczby połączeń. Jeśli masz aplikację, która wymaga dużej liczby połączeń, rozważ użycie narzędzia takiego jak pg_bouncer, które może gromadzić połączenia dla Ciebie. Ponieważ każde połączenie zużywa pamięć RAM, powinieneś starać się zminimalizować ich użycie.

Jak zwiększyć maksymalną liczbę połączeń

1. Zwiększ max_connectionishared_buffers

w /var/lib/pgsql/{version_number}/data/postgresql.conf

zmiana

max_connections = 100
shared_buffers = 24MB

max_connections = 300
shared_buffers = 80MB

shared_buffersParametr konfiguracyjny określa, ile pamięci jest dedykowany do PostgreSQL do użytku na buforowanie danych .

Jeśli masz system z 1 GB lub więcej pamięci RAM, rozsądną wartością początkową dla shared_buffers jest 1/4 pamięci w systemie.
jest mało prawdopodobne, że więcej niż 40% pamięci RAM będzie działać lepiej niż mniejsza ilość (np. 25%)
Pamiętaj, że jeśli twój system lub kompilacja PostgreSQL jest 32-bitowa, ustawienie shared_buffers powyżej 2 ~ 2,5 GB może nie być praktyczne.
Zwróć uwagę, że w systemie Windows duże wartości dla shared_buffers nie są tak skuteczne i możesz uzyskać lepsze wyniki, utrzymując je na stosunkowo niskim poziomie i częściej używając pamięci podręcznej systemu operacyjnego. W systemie Windows przydatny zakres to 64 MB do 512 MB .

2. Zmień plik kernel.shmmax

Musiałbyś zwiększyć maksymalny rozmiar segmentu jądra, aby był nieco większy niż shared_buffers.

W pliku /etc/sysctl.confustaw parametr jak pokazano poniżej. postgresqlZacznie obowiązywać po ponownym uruchomieniu (poniższy wiersz powoduje, że jądro jest maksymalne 96Mb)

kernel.shmmax=100663296

Bibliografia

Połączenia Postgres Max i udostępnione bufory

Dostrajanie serwera PostgreSQL

— Ankit
źródło

świetna odpowiedź. kilka pytań ... w jaki sposób 100663296 równa się 96 MB? i dlaczego zmieniamy shmmax, co to robi?

— Robbo_UK,

100663296 bajtów = 96 MB (binarnie). shmmax to maksymalny rozmiar segmentu pamięci współdzielonej . Teraz, ponieważ zwiększyliśmy rozmiar współdzielonych buforów, musimy zmienić shmmax, aby pomieścić zwiększoną pamięć do buforowania.

— Ankit

@Robbo_UK 96 * 1024 * 1024 = 100663296

— skrebbel

Możesz użyć PGTune, aby pomóc Ci określić te ustawienia dla swojego systemu

— Yoan Tournade,

Sprawdź bieżące ustawienie kernel.shmmax ( cat /proc/sys/kernel/shmmax) przed ich zmianą. W nowoczesnych systemach jest już ustawiony absurdalnie wysoko i nie należy go zmieniać. Mój jest ustawiony 18446744073692774399domyślnie na Ubuntu 18.04.

— Carl Zulauf

Dodając do wspaniałej odpowiedzi Winnie,

Jeśli ktoś nie może znaleźć lokalizacji pliku postgresql.conf w twojej konfiguracji, zawsze możesz zapytać samego postgres.

SHOW config_file;

Dla mnie sama zmiana max_connections była sztuczką.

— Jinxer Albatross
źródło

Dziękuję Ci. W Ubuntu 18.04 jest to/etc/postgresql/11/main/postgresql.conf

— gies0r

zmień zmienną max_connections w pliku postgresql.conf znajdującym się w / var / lib / pgsql / data lub / usr / local / pgsql / data /

— Archana
źródło

Dlaczego pg_hba.conf? Nie ma żadnego parametru dotyczącego liczby połączeń, tylko sposób połączenia. postgresql.conf z drugiej strony ... Ale setki połączeń to zły pomysł, nie rób tego, używaj puli połączeń lub cierpią na problemy z wydajnością. PostgreSQL 8.3 jest EOL od wielu lat, prosimy o używanie aktualnej wersji.

— Frank Heikens,