PostgreSQL - Konfiguracja pgBouncer

PgBouncer umożliwia stworzenie puli połączeń określonej "min_pool_size", które zostaną nawiązane do klastra postgresa "na stałe", z możliwością powiększenia kontrolowanego do "default_pool_size" / "pool_size" jeżeli zajdzie taka potrzeba i zamykania połączeń nieaktywnych przez "server_idle_timeout" do limitu "min_pool_size". A w sytuacjach awaryjnych, jeżeli w kolejce będą znajdowały się połączenia, które nie zostały obsłużone przez "reserve_pool_timeout", otworzy zapasowe połączenia, do maksymalnie "reserve_pool_size". Dzięki temu, że w pgBouncer połączenia nawiązane są tylko raz, a następnie mogą być wielokrotnie ponownie wykorzystane przez różnych klientów. Unikamy potrzeby ciągłego nawiązywania nowych połączeń, co jest dość kosztowną operacją.
Ponadto, pozwala na stworzenie kolejki dla połączeń przychodzących do pgbouncera "max_client_conn", które będą obsługiwane po kolei przez połączenia z puli.

Mamy dostępne trzy tryby pracy, "pool_mode":

• session - ustawienie domyślne, połączenie jest zwracane do puli po zakończeniu sesji. W tym trybie możemy uniknąć kosztu nawiązania nowego połączenia do klastra, ponieważ pgBouncer stale je utrzymuje i przekazuje kolejnym połączeniom w kolejce,
• transaction - połączenie wraca do puli po zakończeniu transakcji commitem lub rollbackiem. Najbardziej użyteczny tryb, dzięki niemu jedno połączenie z puli może obsłużyć wiele różnych klientów, dzięki czemu postgres jest w stanie obsłużyć znacznie większą liczbę klientów niż limit w max_connections,
• statement - połączenie obsługuje pojedyncze zapytania SQL, po czym zwraca sesję do puli. Najwydajniejsza opcja pozwalająca osiągnąć najlepsze wyniki, jeżeli chodzi o wydajność, ale kosztem ograniczenia zapytań w transakcji do jednego.

pgbouncer wymaga uwierzytelniania podczas ustanawiania połączenia. - Sposób uwierzytelniania ustawiamy parametrem "auth_type", obsługiwane opcje to:

• cert - połączenie musi być nawiązane z wykorzystaniem TLS, a klient musi okazać ważny certyfikat. Nazwa użytkownika jest pobierana z wartości CommonName z certyfikatu.,
• md5 - hash md5 dla użytkowników korzystających z tego algorytmu,
• scram-sha-256 - analogicznie dla algorytmu kryptującego scram-sha-256,
• plain - zapisanie hasła "czystym" tekstem, bez żadnych dodatkowych zabezpieczeń,
• trust - bez uwierzytelniania, każdy użytkownik wypisany w pliku "userlist.txt" może się połączyć,
• any - również bez uwierzytelniania, zadana nazwa użytkownika jest ignorowana, a pgbouncer korzysta z użytkownika podanego w definicji połączenia w pgbouncer.ini,
• hba - podobnie jak w przypadku pg_hba.conf, pgbouncer sprawdza w pliku auth_hba_file, który użytkownik może się zalogować oraz z jakim typem uwierzytelniania, metoda ta pozwala na zdefiniowanie różnych typów uwierzytelniania,
• pam - uwierzytelnianie po stronie linuxa, auth_file jest ignorowany w tej metodzie oraz nie można jej ustawić w trybie hba.

[Host 4: pgbouncer]

Przykład prostej konfiguracji. W miejsce host=pg1 podajmy naszego aktualnego lidera patroni. W tym pliku będą już sekcje "[databases]" i "[pgbouncer]". Trzeba je wykomentować przed wklejeniem poniższego lub stworzyć ten plik na nowo.

# /etc/pgbouncer/pgbouncer.ini



[databases]

postgres = host=pg1 port=5432 dbname=postgres min_pool_size=10 pool_size=20

* = host=pg1 port=5432 pool_size=20



[pgbouncer]

logfile = /var/log/postgresql/pgbouncer.log

pidfile = /var/run/postgresql/pgbouncer.pid

unix_socket_dir = /var/run/postgresql



listen_addr = *

listen_port = 6432

pool_mode = transaction

max_client_conn = 1000

reserve_pool_size = 5

reserve_pool_timeout = 5

server_idle_timeout = 60

max_db_connections = 100



auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

admin_users = postgres

Do pliku userlist.txt kopiujemy hash md5 hasła dla użytkownika, który powinien móc się połączyć z postgresem. Możemy go znaleźć w widoku pg_shadow na dowolnym z hostów Patroni:

postgres=# \x

Expanded display is on.

postgres=# select * from pg_shadow ;

-[ RECORD 1 ]+------------------------------------

usename        | postgres

usesysid      | 10

usecreatedb - | t

usesuper      | t

userepl        | t

usebypassrls | t

passwd        | md5a9f68b14e99c2c1fc6b28e1291488e53

valuntil      |

useconfig     | 

Wartości z kolumn usename oraz passwd przenosimy do pliku /etc/pgbouncer/userlist.txt na hoście 4 - czyli tam, gdzie pgbouncer. Jeżeli plik nie istnieje, należy go stworzyć:

sudo vi /etc/pgbouncer/userlist.txt

"postgres" "md5a9f68b14e99c2c1fc6b28e1291488e53"

Uruchamiamy pgbouncer za pomocą usługi pgbouncer.service:

sudo systemctl start pgbouncer

Przetestujmy teraz, jaką wydajność możemy osiągnąć dla tego samego klastra za pomocą pgbench.

[Host 1,2,3,4: wszystkie serwery patroni i pgbouncer]

Przed testem wykonajmy inicjalizację danych pgbench na serwerze lidera patroni.
Dla poprawnego działania pgbench musimy zainstalować jako użytkownik systemowy z uprawnieniami do sudo na wszystkich serwerach patroni i hoscie pgbouncer:

sudo apt install postgresql-client-15 postgresql-15

[Host 1,2 lub 3: aktualny lider klastra patroni]

Następnie inicjalizujemy bazę z przykładowymi danymi (z poziomu użytkownika systemowego postgres):

pgbench -i -s 10

[Host 4: pgbouncer]

Gdyby podczas poniższych testów Postgresql pytał nas o hasło, podajemy to, które skonfigurowaliśmy w sekcji "authentication" w pliku - /etc/patroni/config.yml - dowolnego z serwerów klastra Patroni, a najlepiej z serwera, który uruchomiliśmy jako pierwszy i który zainicjował klaster. Jeśli mamy za sobą nasz rozdział o Patroni, to tam ustawialiśmy hasło na "haslo_postgres".

1. Test za pomocą pgbench łącząc się bezpośrednio do postgresa. Wykorzystane parametry to:
   • h - adres serwera,
   • p - port, na którym działa postgres,
   • S - test za pomocą samych zapytań SELECT,
   • c - liczba nawiązanych połączeń,
   • P - interwał, w jakim będzie wyświetlany "progress", informacje o liczbie transakcji na sekundę oraz średnie opóźnienie,
   • T - czas trwania testu,
   • C - każda transakcja będzie otwierała nowe połączenie.

   postgres@ubuntu:~$ pgbench -h <nazwa_hosta_lidera_patroni> -p 5432 -S -c 80 -P 5 -T 30 -C
   
   pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
   
   starting vacuum...end.
   
   progress: 5.0 s, 257.0 tps, lat 288.754 ms stddev 174.237
   
   progress: 10.0 s, 255.8 tps, lat 308.288 ms stddev 183.066
   
   progress: 15.0 s, 252.1 tps, lat 307.900 ms stddev 212.583
   
   progress: 20.0 s, 306.1 tps, lat 255.459 ms stddev 148.380
   
   progress: 25.0 s, 259.8 tps, lat 300.960 ms stddev 179.012
   
   progress: 30.0 s, 257.8 tps, lat 307.302 ms stddev 159.319
   
   transaction type: <builtin: select only>
   
   scaling factor: 1000
   
   query mode: simple
   
   number of clients: 80
   
   number of threads: 1
   
   duration: 30 s
   
   number of transactions actually processed: 8023
   
   latency average = 293.352 ms
   
   latency stddev = 176.923 ms
   
   average connection time = 3.650 ms
   
   tps = 265.967665 (including reconnection times)

2. Połączenia przez pgbouncera, tutaj pomijamy parametr "-h" dlatego, że łączymy się lokalnie. Jako hasło podajemy hasło użytkownika postgres w bazie danych (to z configa Patroni). Nie przejmujemy się, że łączymy się w tym przypadku lokalnie, nie działa to na korzyść Pgbouncera, ponieważ ten i tak dystrybuuje połączenia do serwerów po sieci.

   postgres@ubuntu:~$ pgbench -p 6432 -S -c 80 -P 5 -T 30 -C
   
   pgbench (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
   
   starting vacuum...end.
   
   progress: 5.0 s, 3692.3 tps, lat 10.438 ms stddev 12.292
   
   progress: 10.0 s, 3609.6 tps, lat 10.520 ms stddev 6.427
   
   progress: 15.0 s, 4323.0 tps, lat 9.355 ms stddev 5.307
   
   progress: 20.0 s, 3041.1 tps, lat 12.704 ms stddev 8.346
   
   progress: 25.0 s, 2347.5 tps, lat 16.138 ms stddev 9.176
   
   progress: 30.0 s, 3262.3 tps, lat 11.846 ms stddev 6.609
   
   transaction type: <builtin: select only>
   
   scaling factor: 1000
   
   query mode: simple
   
   number of clients: 80
   
   number of threads: 1
   
   duration: 30 s
   
   number of transactions actually processed: 101414
   
   latency average = 11.446 ms
   
   latency stddev = 8.498 ms
   
   average connection time = 0.280 ms
   
   tps = 3379.950548 (including reconnection times)

   Łącząc się przez pgbouncera, otrzymaliśmy dwunastokrotnie większą liczbę transakcji na sekundę, pomimo wykorzystania puli tylko 20 połączeń, które obsługiwały 80 połączeń przychodzących z pgbench.