Konfigurieren von PostgreSQL-Verbindungs-Pooling mit PgBouncer für hochverkehrige Anwendungen

Einführung

Wenn PostgreSQL-Datenbanken hohe Verbindungsvolumes bewältigen müssen, kann die Performance rapide abnehmen. Jede Datenbankverbindung verbraucht Systemressourcen, und PostgreSQL hat praktische Grenzwerte für gleichzeitige Verbindungen. PgBouncer, ein leichtgewichtiger Connection-Pooler, löst dieses Problem, indem er einen Pool von Datenbankverbindungen unterhält und sie effizient an Clientanwendungen verteilt.

Warum Connection Pooling wichtig ist

Das Verbindungsproblem

• Ressourcenverbrauch: Jede PostgreSQL-Backend-Prozess verbraucht 5-10 MB RAM
• Verbindungslimits: Der Standardwert für max_connections liegt typischerweise zwischen 100 und 200
• Herstellungszeit: Das Erstellen neuer Verbindungen dauert 1-5 ms
• Context‑Switching: Zu viele Prozesse führen zu CPU‑Thrashing

Vorteile von PgBouncer

• Reduziert die Anzahl der Datenbankverbindungen um das 10- bis 100‑fache
• Ermöglicht tausende gleichzeitige Clientverbindungen mit minimaler Overhead
• Bietet Verbindung Warteschlangen während Lastspitzen
• Unterstützt mehrere Pooling‑Modi für unterschiedliche Anwendungsfälle

Installation und Grundkonfiguration

PgBouncer installieren

Unter Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

Unter CentOS/RHEL:

sudo yum install pgbouncer

Unter macOS:

brew install pgbouncer

Verzeichnisstruktur

/etc/pgbouncer/
  ├── pgbouncer.ini        # Hauptkonfigurationsdatei
  └── 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]
; Modus für Verbindungspooling
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

; Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Performance
max_prepared_statements = 0

Verständnis der Pooling‑Modi

1. Session‑Pooling (pool_mode = session)

• Verhalten: Eine Verbindung wird einem Client für die gesamte Sitzung zugewiesen
• Anwendungsfall: Anwendungen, die temporäre Tabellen oder vorbereitete Anweisungen nutzen
• Effizienz: Niedrig (1:1 Verbindungsratio)

pool_mode = session

2. Transaction‑Pooling (pool_mode = transaction) – Empfohlen

• Verhalten: Eine Verbindung wird nach jeder Transaktion an den Pool zurückgegeben
• Anwendungsfall: Die meisten Webanwendungen mit kurzen Transaktionen
• Effizienz: Hoch (10- bis 100‑fache Reduktion)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. Statement‑Pooling (pool_mode = statement)

• Verhalten: Eine Verbindung wird nach jeder Anweisung zurückgegeben
• Anwendungsfall: Einfache Leseanweisungen ohne Transaktionen
• Effizienz: Maximale Reduktion (sehr restriktiv)

pool_mode = statement
; Mit Vorsicht verwenden – bricht mehrstufige Transaktionen

Authentifizierung einrichten

userlist.txt erstellen

PgBouncer benötigt eine separate Authentifizierungsdatei. Erstellen Sie einen MD5‑Hash und fügen Sie ihn zu userlist.txt hinzu.

Beispiel userlist.txt:

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

Fortgeschrittene Authentifizierung mit auth_query

Authentifizierung direkt über PostgreSQL abfragen:

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

Optimale Konfiguration für Hochverkehr

Größen des Verbindungspools

Formel zur Pool‑Sizing:

default_pool_size = (num_cores × 2) + effective_spindle_count

Für einen 4‑Kern‑Server mit SSD:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

Komplett konfigurierte Produktionsdatei

[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

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

; Performance
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

Anwendungsspezifische Verbindungszeichenketten

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

Administrationskonsole‑Befehle

Verbinden Sie sich mit der PgBouncer‑Administrationskonsole:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Wichtige 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"

Lösung:

max_client_conn = 5000
default_pool_size = 50

Problem 2: Hoher cl_waiting‑Zähler

Lösungen:

1. Pool‑Größe erhöhen
2. Langsame Abfragen optimieren
3. Reserve‑Pool hinzufügen

Problem 3: Fehler bei vorbereiteten Anweisungen

Lösung:

max_prepared_statements = 0

Beispiele für Performance‑Einflüsse

Vor PgBouncer

• 500 gleichzeitige Anfragen → 500 PostgreSQL‑Verbindungen
• Datenbanklast: 95 % CPU, 8 GB RAM
• Antwortzeit: durchschnittlich 250 ms

Nach PgBouncer

• 500 gleichzeitige Anfragen → 25 PostgreSQL‑Verbindungen
• Datenbanklast: 35 % CPU, 1 GB RAM
• Antwortzeit: durchschnittlich 80 ms
• Ergebnis: 3× schneller, 70 % weniger Ressourcenverbrauch

Fazit

PgBouncer ist unverzichtbar, um PostgreSQL‑Anwendungen zu skalieren. Es reduziert die Verbindungsoverhead um über 90 %, unterstützt 10‑ bis 100‑fach mehr Clients und verbessert die Antwortzeiten dramatisch. Beginnen Sie mit dem Transaction‑Pooling‑Modus und passen Sie die Konfiguration basierend auf den Monitoring‑Ergebnissen an.