Konfiguration des PostgreSQL-Verbindungspoolings mit PgBouncer für hochfrequente Anwendungen

Wenn PostgreSQL-Datenbanken mit hohen Verbindungsvolumen konfrontiert werden, kann die Leistung schnell nachlassen. Jede Client-Verbindung wird einem PostgreSQL-Backend-Prozess zugeordnet, sodass eine ausgelastete Web-App zu viel Speicher und CPU für das Offenhalten von Sitzungen verbrauchen kann. Das PgBouncer-Verbindungspooling reduziert diesen Druck, indem viele Client-Verbindungen eine kleinere Anzahl von Serververbindungen gemeinsam nutzen können.

Warum Verbindungspooling wichtig ist

Das Verbindungsproblem

Ressourcen-Overhead: Jede PostgreSQL-Verbindung hat einen Backend-Prozess und Speicher-Overhead.
Verbindungslimits: max_connections ist begrenzt, und eine zu hohe Erhöhung kann die Datenbank instabiler statt schneller machen.
Startkosten: Das Erstellen neuer Datenbankverbindungen erhöht die Latenz.
Kontextwechsel: Zu viele aktive Backend-Prozesse können CPU verschwenden.

Vorteile von PgBouncer

Ermöglicht vielen Anwendungs-Clients die gemeinsame Nutzung weniger PostgreSQL-Serververbindungen.
Stellt Clients in eine Warteschlange, wenn der Pool ausgelastet ist, anstatt die Datenbank zu überlasten.
Unterstützt Session-, Transaktions- und Statement-Pooling-Modi.
Bietet Betriebsbefehle wie SHOW POOLS, SHOW CLIENTS und RELOAD.

Installation und grundlegende Einrichtung

Installation von PgBouncer

Auf Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

Auf CentOS/RHEL:

sudo yum install pgbouncer

Auf macOS:

brew install pgbouncer

Übliche Dateipfade

/etc/pgbouncer/
  ├── pgbouncer.ini        # Hauptkonfiguration
  └── userlist.txt         # Authentifizierungsdaten

Konfigurationsdatei einrichten

Grundlegende pgbouncer.ini-Konfiguration

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; Verbindungspooling-Modus
pool_mode = transaction

; Maximale Verbindungen
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Netzwerk
listen_addr = 0.0.0.0
listen_port = 6432

; Authentifizierung
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Protokollierung
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Leistung
max_prepared_statements = 0

Die Standardeinstellungen variieren je nach Distribution. Überprüfen Sie daher nach der Installation die installierte Servicedatei und den Konfigurationspfad. Bei vielen Linux-Paketen ist /etc/pgbouncer/pgbouncer.ini die Hauptdatei.

Pooling-Modi verstehen

1. Session-Pooling (pool_mode = session)

Verhalten: Verbindung wird dem Client für die gesamte Sitzung zugewiesen
Anwendungsfall: Anwendungen mit temporären Tabellen, vorbereiteten Anweisungen
Effizienz: Niedrig (1:1-Verbindungsverhältnis)

pool_mode = session

2. Transaktions-Pooling (pool_mode = transaction) - Empfohlen

Verhalten: Verbindung wird nach jeder Transaktion an den Pool zurückgegeben
Anwendungsfall: Die meisten Webanwendungen mit kurzen Transaktionen
Effizienz: Hoch, wenn Ihre App nicht auf Sitzungszustand angewiesen ist

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

Transaktions-Pooling ist eine gute Standardeinstellung für Request/Response-Web-Apps, kann jedoch Annahmen über sitzungsbezogene Funktionen brechen. Seien Sie vorsichtig mit temporären Tabellen, Sitzungsvariablen, Advisory-Locks außerhalb einer Transaktion, LISTEN/NOTIFY und treiberseitigen vorbereiteten Anweisungen, es sei denn, Sie haben diese mit Ihrer PgBouncer-Version und Ihren Einstellungen getestet.

3. Statement-Pooling (pool_mode = statement)

Verhalten: Verbindung wird nach jeder Anweisung zurückgegeben
Anwendungsfall: Einfache schreibgeschützte Abfragen ohne Transaktionen
Effizienz: Maximal (aber sehr restriktiv)

pool_mode = statement
; Mit Vorsicht verwenden - bricht Transaktionen mit mehreren Anweisungen

Authentifizierung einrichten

Erstellen von userlist.txt

PgBouncer benötigt eine separate Authentifizierungsdatei. Generieren Sie den MD5-Hash und fügen Sie ihn zur userlist.txt hinzu.

Beispiel userlist.txt:

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

Für PostgreSQL-ähnliche MD5-Passwörter ist der Wert md5 plus der MD5-Hash von password + username. Verwenden Sie keine gefälschten Hashes in der Produktion; generieren Sie Einträge aus Ihren echten Benutzernamen und Passwörtern oder verwenden Sie eine sicherere Authentifizierungsmethode, die von Ihrer Umgebung unterstützt wird.

Verwendung von PostgreSQL auth_query (Fortgeschritten)

PgBouncer kann PostgreSQL nach Benutzeranmeldeinformationen abfragen, wenn ein Benutzer nicht in auth_file gefunden wird, erfordert jedoch einen auth_user, mit dem sich PgBouncer anmelden kann. Ein minimales Beispiel sieht so aus:

auth_type = md5
auth_user = pgbouncer_auth
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Beschränken Sie die Berechtigungen des Authentifizierungsbenutzers und befolgen Sie die Richtlinien Ihrer PostgreSQL-Version. Viele Teams verwenden eine SECURITY DEFINER-Funktion anstatt direkten Zugriff auf Katalog-Passwortdaten zu gewähren.

Optimale Konfiguration für hohen Datenverkehr

Größe des Verbindungspools

Es gibt keine universelle Formel für die Poolgröße. Beginnen Sie mit einem konservativen Wert basierend auf der Anzahl der Abfragen, die Ihre Datenbank aktiv gut ausführen kann, und passen Sie ihn dann anhand echter Metriken an.

Für eine typische Web-App könnten Sie hier beginnen und anpassen:

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

Beobachten Sie PostgreSQL-CPU, Abfragelatenz, Lock-Wartezeiten und die cl_waiting-Anzahl von PgBouncer. Wenn Clients warten, während die Datenbank im Leerlauf ist, ist der Pool möglicherweise zu klein. Wenn die Datenbank ausgelastet ist, kann eine Vergrößerung des Pools die Situation verschlimmern.

Vollständige Produktionskonfiguration

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; Verbindungslimits
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; Netzwerk
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Sicherheit
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; Protokollierung
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

; Leistung
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

max_prepared_statements = 0 deaktiviert die Nachverfolgung vorbereiteter Anweisungen durch PgBouncer. Neuere PgBouncer-Versionen können protokollebene vorbereitete Anweisungen im Transaktions-Pooling unterstützen, wenn dieser Wert über Null liegt. Sie sollten jedoch Ihren Treiber und Ihre Arbeitslast testen, bevor Sie dies aktivieren.

Verbindungszeichenfolge der Anwendung

Vor PgBouncer

# Direkte PostgreSQL-Verbindung
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

Nach PgBouncer

# Verbindung über PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Überwachung und Verwaltung

Admin-Konsolenbefehle

Verbinden Sie sich mit der PgBouncer-Admin-Konsole:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Wesentliche Befehle:

-- Pool-Statistiken anzeigen
SHOW POOLS;

-- Aktive Verbindungen anzeigen
SHOW CLIENTS;
SHOW SERVERS;

-- Konfiguration anzeigen
SHOW CONFIG;

-- Konfiguration neu laden
RELOAD;

Fehlerbehebung bei häufigen Problemen

Problem 1: "no more connections allowed"

Dies kann bedeuten, dass PgBouncer Client-Verbindungen ablehnt oder PostgreSQL Serververbindungen ablehnt. Überprüfen Sie, wo der Fehler auftritt.

Mögliche Änderungen auf PgBouncer-Seite:

max_client_conn = 5000
default_pool_size = 50

Bevor Sie beide erhöhen, stellen Sie sicher, dass Ihr Betriebssystem-Dateideskriptor-Limit und PostgreSQL max_connections die neuen Gesamtzahlen unterstützen können. PgBouncer selbst benötigt auch ausreichend Dateideskriptoren für Client- und Server-Sockets.

Problem 2: Hohe cl_waiting-Anzahl

Lösungen:

Poolgröße erhöhen
Langsame Abfragen optimieren
Reserve-Pool hinzufügen

Problem 3: Fehler bei vorbereiteten Anweisungen

Wenn Ihre App oder Ihr Treiber vorbereitete Anweisungen verwendet und Sie sich im Transaktions-Pooling-Modus befinden, können Fehler auftreten, wenn eine spätere Anweisung auf einer anderen Serververbindung landet. Optionen umfassen:

Treiberseitige vorbereitete Anweisungen deaktivieren.
Session-Pooling für diese Arbeitslast verwenden.
PgBouncer-Unterstützung für vorbereitete Anweisungen mit einem positiven max_prepared_statements-Wert in einer aktuellen PgBouncer-Version testen.

Konservative Einstellung:

max_prepared_statements = 0

Praktisches Bereitstellungsbeispiel

Stellen Sie sich vor, Ihre App-Server können Hunderte gleichzeitiger HTTP-Anfragen öffnen, aber die Datenbank läuft am besten mit einigen Dutzend aktiven Abfragen. Richten Sie die App auf PgBouncer auf Port 6432, setzen Sie max_client_conn hoch genug für Client-Bursts und halten Sie default_pool_size nahe an der Anzahl der Datenbankverbindungen, die Sie tatsächlich für dieses Datenbank-/Benutzerpaar aktiv haben möchten.

Validieren Sie dann mit:

SHOW POOLS;
SHOW STATS;

Wenn cl_waiting bei normalem Datenverkehr über Null bleibt, untersuchen Sie langsame Abfragen, bevor Sie einfach die Poolgröße erhöhen. PgBouncer schützt PostgreSQL vor Verbindungsstürmen, macht aber langsame SQL-Abfragen nicht günstiger.

Praktische Zusammenfassung

Beginnen Sie mit Transaktions-Pooling für zustandslose Web-Workloads, halten Sie die Poolgrößen bewusst klein und passen Sie sie anhand von PgBouncer- und PostgreSQL-Metriken an. Wenn Ihre App von Sitzungsverhalten oder vorbereiteten Anweisungen abhängt, testen Sie diese Pfade, bevor Sie PgBouncer vor den Produktionsdatenverkehr setzen.