PostgreSQL PgBouncer Connection Pooling konfigurieren

Einführung

In diesem Lab lernen Sie, wie Sie PgBouncer, einen leichtgewichtigen Connection Pooler für PostgreSQL, einrichten und verwenden. Connection Pooling ist eine entscheidende Technik zur Verbesserung der Datenbankleistung, insbesondere für Anwendungen, die viele kurzlebige Verbindungen verarbeiten. Durch die Verwaltung eines Pools wiederverwendbarer Datenbankverbindungen reduziert PgBouncer den Overhead für den Aufbau neuer Verbindungen für jede Client-Anfrage.

Sie beginnen mit der Installation und Konfiguration von PgBouncer, wobei Sie die notwendigen Konfigurations- und Benutzerauthentifizierungsdateien erstellen. Als Nächstes starten Sie den PgBouncer-Dienst und testen die Verbindung zu Ihrer PostgreSQL-Datenbank über den Pooler. Anschließend verwenden Sie pgbench, ein Standard-Benchmarking-Tool für PostgreSQL, um eine Client-Last zu simulieren und zu beobachten, wie PgBouncer die Verbindungen verwaltet. Abschließend lernen Sie, wie Sie sich mit der PgBouncer-Admin-Konsole verbinden, um dessen Leistung zu überwachen und Verbindungsstatistiken anzuzeigen.

PgBouncer konfigurieren

In diesem Schritt erstellen Sie die notwendigen Konfigurationsdateien für PgBouncer. Dies beinhaltet die Definition der Datenbankverbindungszeichenfolge und die Einrichtung einer Authentifizierungsdatei für Benutzer.

Erstellen Sie zunächst ein dediziertes Verzeichnis für Ihre PgBouncer-Konfigurationsdateien innerhalb Ihres Projektordners.

mkdir -p /home/labex/project/pgbouncer
cd /home/labex/project/pgbouncer

Erstellen Sie als Nächstes die Hauptkonfigurationsdatei pgbouncer.ini mit dem nano-Editor.

nano pgbouncer.ini

Fügen Sie die folgende Konfiguration in den Editor ein. Diese Konfiguration teilt PgBouncer mit, wie es sich mit Ihrer PostgreSQL-Datenbank verbinden und auf welchem Port es auf Client-Verbindungen lauschen soll.

[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /home/labex/project/pgbouncer/userlist.txt
admin_users = postgres
pidfile = /home/labex/project/pgbouncer/pgbouncer.pid
logfile = /home/labex/project/pgbouncer/pgbouncer.log
pool_mode = session
default_pool_size = 20
max_client_conn = 100

Lassen Sie uns die wichtigsten Parameter überprüfen:

[databases]: Definiert die Verbindungszeichenfolge für die Zieldatenbank.
listen_port: Der Port, auf dem PgBouncer lauschen wird (6432). Ihre Anwendung wird sich mit diesem Port verbinden.
auth_type: Gibt die Authentifizierungsmethode an. md5 ist eine gängige Wahl.
auth_file: Der Pfad zur Datei, die Benutzernamen und Passwörter enthält.
admin_users: Eine durch Kommas getrennte Liste von Benutzern, denen die Verbindung zur PgBouncer-Admin-Konsole gestattet ist.
pool_mode: Auf session gesetzt, was bedeutet, dass eine Serververbindung einem Client für die gesamte Sitzung zugewiesen wird.

Drücken Sie Strg+O, um die Datei zu speichern, dann Enter und Strg+X, um nano zu beenden.

Erstellen Sie nun die Authentifizierungsdatei userlist.txt, auf die Sie in der Konfiguration verwiesen haben.

nano userlist.txt

Fügen Sie die folgende Zeile zur Datei hinzu. Das Passwort labex_password wurde während der Einrichtungsphase des Labs für den Benutzer postgres festgelegt.

"postgres" "labex_password"

Speichern und beenden Sie den Editor, indem Sie Strg+O, Enter und Strg+X drücken.

Sie haben nun PgBouncer erfolgreich konfiguriert, um Verbindungen für Ihre PostgreSQL-Datenbank zu verwalten.

PgBouncer starten und Verbindung testen

Nachdem die Konfiguration eingerichtet ist, besteht der nächste Schritt darin, den PgBouncer-Dienst zu starten und zu überprüfen, ob Sie sich darüber mit der PostgreSQL-Datenbank verbinden können.

Starten Sie in Ihrem Terminal PgBouncer als Daemon (-d-Flag) unter Verwendung der von Ihnen erstellten Konfigurationsdatei.

pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Sie können mit dem folgenden Befehl überprüfen, ob der PgBouncer-Prozess läuft. Sie sollten den pgbouncer-Prozess in der Liste sehen.

ps aux | grep pgbouncer

Die Ausgabe wird in etwa so aussehen:

labex      1234  0.0  0.0  12345   678 ?        Ss   12:00   0:00 pgbouncer -d /home/labex/project/pgbouncer/pgbouncer.ini

Testen Sie nun die Verbindung, indem Sie psql verwenden, um sich mit dem Port zu verbinden, auf dem PgBouncer lauscht (6432), nicht mit dem Standard-PostgreSQL-Port (5432).

psql -h 127.0.0.1 -p 6432 -U postgres -d postgres

Sie werden nach dem Passwort gefragt. Geben Sie labex_password ein. Wenn die Verbindung erfolgreich ist, sehen Sie die psql-Eingabeaufforderung.

Um zu bestätigen, dass Sie mit der Datenbank verbunden sind, führen Sie eine einfache Abfrage aus, um die PostgreSQL-Version zu überprüfen.

SELECT version();

Die Ausgabe zeigt die Version Ihres PostgreSQL-Servers an und bestätigt, dass PgBouncer Ihre Verbindung erfolgreich weitergeleitet hat.

                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0...
(1 row)

Beenden Sie schließlich die psql-Shell, indem Sie \q eingeben und Enter drücken.

\q

Client-Last mit pgbench simulieren

Um das Connection Pooling in Aktion zu beobachten, verwenden Sie pgbench, ein Standard-Benchmarking-Tool, das mit PostgreSQL geliefert wird. pgbench kann mehrere Clients simulieren, die gleichzeitig auf die Datenbank zugreifen.

Zuerst müssen Sie die pgbench-Umgebung initialisieren. Dies erstellt einige Tabellen und füllt sie mit Beispieldaten. Führen Sie den folgenden Befehl aus und stellen Sie sicher, dass Sie sich über den PgBouncer-Port (6432) verbinden.

pgbench -i -h 127.0.0.1 -p 6432 -U postgres postgres

Sie werden nach dem Passwort (labex_password) gefragt. Das Flag -i initialisiert die Datenbank für Benchmarking. Sie sollten eine Ausgabe sehen, die anzeigt, dass die Tabellen erstellt und gefüllt wurden.

dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.01 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.13 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.06 s, vacuum 0.04 s, primary keys 0.02 s).

Führen Sie nun das Benchmark aus, um eine Last zu simulieren. Der folgende Befehl simuliert 10 gleichzeitige Clients (-c 10), wobei jeder Client 300 Transaktionen ausführt (-t 300).

pgbench -c 10 -t 300 -h 127.0.0.1 -p 6432 -U postgres postgres

Geben Sie erneut das Passwort ein, wenn Sie dazu aufgefordert werden. Das Benchmark wird einige Sekunden laufen und dann eine Zusammenfassung der Ergebnisse anzeigen, einschließlich der Anzahl der Transaktionen pro Sekunde (tps).

pgbench (14.18 (Ubuntu 14.18-0ubuntu0.22.04.1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 300
number of transactions actually processed: 3000/3000
latency average = 5.935 ms
initial connection time = 1.342 ms
tps = 1685.027854 (without initial connection time)

Dieser Test hat erheblichen Datenverkehr über PgBouncer erzeugt, den wir im nächsten Schritt untersuchen werden.

PgBouncer-Statistiken überwachen

PgBouncer stellt eine spezielle administrative Datenbank bereit, mit der Sie seine Aktivitäten überwachen und Statistiken über die Connection Pools einsehen können.

Verbinden Sie sich mit der PgBouncer-Admin-Konsole. Beachten Sie, dass der Datenbankname pgbouncer lautet.

psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer

Geben Sie bei Aufforderung das Passwort labex_password ein.

Sobald Sie verbunden sind, können Sie spezielle SHOW-Befehle ausführen. Zeigen Sie zuerst die Gesamtstatistiken für alle Datenbanken an.

SHOW STATS;

Dieser Befehl zeigt kumulative Daten seit dem letzten Start von PgBouncer an. Sie sehen Spalten wie total_xact_count (Gesamtzahl der Transaktionen) und total_query_count, die nun hohe Werte vom pgbench-Test aufweisen sollten.

pgbouncer=## SHOW STATS;
 database  | total_xact_count | total_query_count | total_received | total_sent | total_xact_time | total_query_time | total_wait_time | avg_xact_count | avg_query_count | avg_recv | avg_sent | avg_xact_time | avg_query_time | avg_wait_time
-----------+------------------+-------------------+----------------+------------+-----------------+------------------+-----------------+----------------+-----------------+----------+----------+---------------+----------------+---------------
 pgbouncer |                1 |                 1 |              0 |          0 |               0 |                0 |               0 |              0 |               0 |        0 |        0 |             0 |              0 |             0
 postgres  |             3019 |             21033 |        2860191 |     561691 |        17758077 |         16667957 |          277707 |             36 |             250 |    34117 |     6700 |          5882 |            792 |          3312
(2 rows)

Als Nächstes verwenden Sie den Befehl SHOW POOLS, um den Zustand der Connection Pools in Echtzeit anzuzeigen.

SHOW POOLS;

Dieser Befehl liefert einen Schnappschuss der Connection Pools und zeigt aktive und inaktive Verbindungen sowohl für Clients als auch für Server an.

cl_active: Client-Verbindungen, die aktiv mit einer Serververbindung verknüpft sind.
sv_active: Serververbindungen, die derzeit in Gebrauch sind.
sv_idle: Serververbindungen, die inaktiv sind und von einem neuen Client verwendet werden können.

 database  |   user    | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
 pgbouncer | pgbouncer |         1 |          0 |             0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement
 postgres  | postgres  |         0 |          0 |             0 |         0 |      10 |       0 |         0 |        0 |       0 |          0 | session
(2 rows)

Durch die Untersuchung dieser Statistiken können Sie verstehen, wie effektiv PgBouncer Verbindungen verwaltet und wiederverwendet, um Client-Anfragen zu bedienen.

Beenden Sie die psql-Shell, wenn Sie fertig sind.

\q

Zusammenfassung

In diesem Lab haben Sie PgBouncer für das PostgreSQL Connection Pooling erfolgreich konfiguriert und verwendet. Sie haben gelernt, wie Sie die Konfigurationsdatei pgbouncer.ini und die Authentifizierungsdatei userlist.txt erstellen. Sie haben den PgBouncer-Dienst gestartet und verifiziert, dass er Verbindungen zu Ihrer PostgreSQL-Datenbank weiterleiten kann. Mithilfe von pgbench haben Sie eine realistische Client-Last simuliert und anschließend die Ergebnisse über die administrative Konsole von PgBouncer überwacht. Dies lieferte praktische Einblicke in die Funktionsweise des Connection Poolings und wie es überwacht werden kann, um eine effiziente Datenbankleistung sicherzustellen.