PgBouncer

PgBouncer PostgreSQL e' un potente DBMS relazionale Open Source noto per la robustezza e la ricchezza di funzionalita'.
PgBouncer e' un tool Open Source esterno per il pooling delle connessioni trasparente ed ottimizzato alle applicazioni che utilizzano PostgreSQL.
PgBouncer e' al tempo stesso facile da configurare e molto efficiente: e' quindi un'ottima scelta come connection pooler per ridurre in modo significativo il numero di sessioni connesse ad un database PoostgreSQL.

Questa pagina descrive le principali modalita' di configurazione ed utilizzo di PgBouncer.

Un documento introduttivo su PostgreSQL e' Introduzione a PostgreSQL, mentre un documento piu' completo e' Qualcosa in piu' su PostgreSQL. Utile e' anche conoscere la Streaming replication PostgreSQL.

PostgreSQL

Architettura PostgreSQL Un breve accenno all'architettura di PostgreSQL puo' essere utile...

Il processo postmaster e' il processo principale che si occupa della gestione delle connessioni ed e' il "padre" di tutti i processi, sia di sistema (eg. wal writer) sia quelli relativi alle connessione utente. Tutti i processi girano come utente postgres ed eseguono un attach al segmento di shared memory su cui vengono mantenuti buffer e lock.

I processi utente sono facilmente individuabili (eg. con il comando ps -efa) poiche' nella command line riportano il database utilizzato, l'IP(porta) del client e lo stato. Gli altri processi sono quelli di background di postgres.

La configurazione dei parametri di PostgreSQL si effettua modificando il file postgresql.conf mentre la configurazione degli accessi si effettua nel file pg_hba.conf e quindi a livello di database con i comandi SQL di GRANT.

Il processo postmaster e' in LISTEN sulla porta socket 5432 (e' il default ma puo' essere cambiata) e per ogni connessione attiva un processo utente. Quando il numero di connessioni e' elevato il carico sulla memoria del sistema ospite puo' essere significativo.
Nella fase iniziale della connessione di un client vengono scambiati gli hash delle password (in modo crittografato se indicato nel file pg_hba) ma tutta la successiva trasmissione dati avviene in chiaro con il protocollo TCP.

La fase di creazione di una sessione Postgres e' pesante ed ha una certa latenza. Inoltre un grande numero di connessioni impone limiti alla memoria allocabile sia per le aree comuni di cache che per le aree di sort/lavoro di ogni sessione.
Per tali motivi limitare il numero di sessioni aperte al DB migliora le prestazioni complessive del sistema.

PgBouncer

PgBouncer fornisce la funzione di connection pooling per PostgreSQL in modo molto efficiente, con un basso utilizzo di risorse e con diverse possibilita' di configurazione.

Possono essere adottati tre diverse strategie:

PgBouncer ha un utilizzo minimale di memoria per sessione ed ha una latenza molto bassa, permette la riconfigurazione della maggior parte dei parametri online, permette il restart ed anche l'upgrade senza interrompere le sessioni utente.
PgBouncer non ha funzioni di proxy, load balancing, ... se necessarie debbo essere implementate con altri tool.

Come riportato la modalita' transaction pooling puo' essere utilizzata solo se l'applicazione non utilizza alcune funzionalita' di PostgreSQL. Tra i piu' importanti: SET/RESET, LISTEN/NOTIFY, WITH HOLD CURSOR, PREPARE, LOAD, session advisory locks, ...
Ancora piu' forti sono i limiti alle applicazioni se si utilizza lo statement pooling: il commit va eseguito ad ogni statement. Questa modalita' e' adatta solo ad alcuni casi d'uso.

Architettura

PgBouncer con PostgreSQL HAProxy e PgBouncer con PostgreSQL

Le applicazioni si collegano a PgBouncer come se fosse un DB Postgres e PgBouncer si collega al DB come se fosse un'applicazione.
PgBouncer gira come un unico processo con un unico thread. Nonostante questo PgBouncer e' in grado di gestire senza problemi migliaia di connessioni a PostgreSQL riducendone il numero in modo significativo.

E' possibile installare PgBouncer sui web/application server oppure sui nodi che ospitano la base dati oppure su server/VM/container dedicati. Dato il basso impegno di memoria e di risorse generalmente non si utilizza una struttura dedicata e si preferisce una delle altre due modalita'. In qualche caso e' anche possibile adottarle entrambe.

PgBouncer fornisce solo la funzionalita' di connection pooling e quindi si colloca tra le applicazioni ed il database Postgres.
E' molto comune utilizzare PgBouncer con altri strumenti come HAProxy o Pgpool-II: PgBouncer esegue il pooling delle connessioni ed HAProxy effettua il load balancing.

Installazione

E' possibile installare PgBouncer partendo dai sorgenti, ma e' molto piu' semplice installare il software utilizzando il repository http://www.pgbouncer.net/yum con
 yum install pgbouncer

Quindi i comandi per attivare il servizio sono:

systemctl enable pgbouncer.service systemctl start pgbouncer.service

Configurazione

Il file principale di configurazione e' pgbouncer.ini gia' disponibile con l'installazione ma da configurare.

Il file e' organizzato in sesioni. Vediamo quindi alcuni dei parametri piu' significativi:

;;;
;;; PgBouncer configuration file
;;;

[databases]
 db1 = host=localhost port=5432 dbname=exercises

[pgbouncer]
 listen_port = 6432
 listen_addr = localhost
 auth_type = md5
 auth_file = userlist.txt
;auth_hba_file =
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 admin_users = postgres
 pool_mode = session
 max_client_conn = 500
 default_pool_size = 500

Il listen_addr=localhost e' corretto se PgBouncer e' installato sull'Application Server, se l'installazione e' sul DB Server va impostato listen_addr=*. Il pool_mode scelto e' quello meno aggressivo ma non ha impatti sulle applicazioni. Il default_pool_size puo' essere mantenuto molto piu' basso del max_client_conn se si utilizza un pooling piu' aggressivo. Il file identificato con il parametro auth_hba_file e' l'analogo del file pg_hba.conf e puo' essere configurato per l'autenticazione delle sessioni utente.

Le possibilita' di configurazione sono ampie ed il file di configurazione le riporta tutte nei commenti.

Utilizzo

Dal punto di vista dell'utilizzo applicativo accedere direttamente ad un database PostgreSQL o attraverso PgBouncer e' indifferente se si utilizza il session pooling.
Tipicamente la porta socket in ascolto di PostgreSQL e' la 5432 mentre quella PgBouncer e' la 6432, ma sono entrambe valori che possono essere cambiati.

Per gestire PgBouncer basta collegarsi al database pgbouncer con uno degli utenti amministrativi configurati o con l'utenza speciale pgbouncer. Tra i comandi utili per controllare lo stato: SHOW HELP, SHOW CLIENTS, SHOW SERVERS, SHOW POOLS, SHOW DATABASES, SHOW STATS. Con il comando RELOAD viene rietto il file di configurazione.

Versioni

La versione consigliata di PgBouncer e' sempre l'ultima disponibile. PgBouncer e' disponibile su Unix/Linux, Windows, MacOS e supporta tutte le versioni di PostgreSQL. Sul sito ufficiale sono riportate tutte le versioni di PgBouncer.

(Sources: Source download GitHub )

Version
Status
Features
Last release
Date (from)
Date (to)
Notes
1 Production (1.6 2015-08): load user password from PostgreSQL, pooling mode per-database and per-user 1.20.02007-032023-07
0 EOF 0

Varie ed eventuali

Quando il numero di connessioni a PostgreSQL e' elevato e' sempre opportuno utilizzare un connection pooling. In molti casi si utilizzano connection pool applicativi ma e' anche possibile utilizzare tool esterni.
Un altro importante tool per gestire le connessioni a Postgres e' Pgpool-II. Entrambe sono ottimi strumenti Open Source ed hanno alcune funzionalita' equivalenti. Ma vi sono anche importanti differenze che li rendono indicati a casi d'uso differenti: PgBouncer e' piu' efficiente ed ha piu' possibilita' di configurazione nella gestione dei connection pool, Pgpool-II e' un proxy e fornisce funzionalita' di alta affidabilita' e di bilanciamento di carico. Infatti in qualche caso possono essere utili entrambe nella stessa architettura finale: Pgpool-II e PgBouncer nella stessa architettura PostgreSQL

Nella figura di esempio PgBouncer e' utilizzato per il connection pooling, Pgpool-II e' utilizzato per l'HA ed il load balancing di tre database PostgreSQL in streaming replication.


Titolo: PgBouncer
Livello: Esperto (4/5)
Data: 31 Ottobre 2022 🎃 Halloween
Versione: 1.0.2 - 25 Dicembre 2022 🎄
Autore: mail [AT] meo.bogliolo.name