PostgreSQL PgBouncer Connection Pooling (Verbindungsbündelung) konfigurieren

Einführung

In diesem Lab lernen Sie, wie Sie PgBouncer für Connection Pooling (Verbindungsbündelung) mit PostgreSQL konfigurieren und verwenden. PgBouncer fungiert als schlanker Connection Pooler (Verbindungsbündler) und sitzt zwischen Ihrer Anwendung und der PostgreSQL-Datenbank, um Verbindungen effizient zu verwalten. Dies reduziert den Overhead (Mehraufwand) beim Erstellen neuer Verbindungen für jede Anfrage und verbessert die Leistung erheblich, insbesondere für Anwendungen mit häufigen, kurzlebigen Datenbankverbindungen.

Das Lab führt Sie durch die Konfiguration von PgBouncer, einschließlich der Erstellung einer Konfigurationsdatei mit Datenbankverbindungsdetails und Pool-Einstellungen. Anschließend verbinden Sie sich über PgBouncer mit der Datenbank, simulieren mehrere Client-Verbindungen, um das Pooling in Aktion zu beobachten, und überwachen die Leistung von PgBouncer, um die Vorteile des Connection Pooling (Verbindungsbündelung) zu verstehen.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/sys_version("Get Server Version") subgraph Lab Skills postgresql/db_access -.-> lab-550957{{"PostgreSQL PgBouncer Connection Pooling (Verbindungsbündelung)"}} postgresql/sys_version -.-> lab-550957{{"PostgreSQL PgBouncer Connection Pooling (Verbindungsbündelung)"}} end

PgBouncer konfigurieren und mit PostgreSQL verbinden

In diesem Schritt konfigurieren Sie PgBouncer für Connection Pooling (Verbindungsbündelung) und verbinden sich über PgBouncer mit Ihrer PostgreSQL-Datenbank.

Eine PgBouncer-Konfigurationsdatei erstellen:

Erstellen Sie zunächst ein Verzeichnis, in dem die PgBouncer-Konfigurationsdatei gespeichert werden soll:
```
mkdir -p ~/project/pgbouncer
cd ~/project/pgbouncer
```
Erstellen Sie dann die Konfigurationsdatei pgbouncer.ini mit nano:
```
nano pgbouncer.ini
```
Fügen Sie die folgende Konfiguration in die Datei pgbouncer.ini ein:
```
[databases]
mydb = host=127.0.0.1 port=5432 dbname=postgres user=postgres

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
user = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL
default_pool_size = 20
max_client_conn = 100
```
- [databases] definiert die Datenbankverbindung. mydb ist ein Alias.
- [pgbouncer] konfiguriert PgBouncer. listen_addr und listen_port sind die Adresse und der Port, an dem PgBouncer lauscht. pool_mode ist auf transaction gesetzt.
Drücken Sie Ctrl+O, um die Datei zu speichern, und dann Ctrl+X, um nano zu verlassen.
Eine Benutzerlistendatei erstellen:

PgBouncer benötigt eine Benutzerlistendatei zur Authentifizierung. Erstellen Sie userlist.txt:
```
nano userlist.txt
```
Fügen Sie die folgende Zeile hinzu und ersetzen Sie labex_password durch das tatsächliche Passwort für den Benutzer postgres (Sie können es mit sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'labex_password';") setzen:
```
"postgres" "labex_password"
```
Drücken Sie Ctrl+O, um die Datei zu speichern, und dann Ctrl+X, um nano zu verlassen.
PgBouncer starten:

Starten Sie PgBouncer mit der Konfigurationsdatei:
```
pgbouncer pgbouncer.ini
```
Lassen Sie dieses Terminal geöffnet. Öffnen Sie ein neues Terminal für die nächsten Schritte.
Über PgBouncer mit PostgreSQL verbinden:

Verbinden Sie sich im neuen Terminal über PgBouncer mit PostgreSQL mit psql:
```
psql -h 127.0.0.1 -p 6432 -U postgres -d postgres
```
Geben Sie das Passwort ein, das Sie in userlist.txt festgelegt haben. Wenn dies erfolgreich ist, sehen Sie die psql-Eingabeaufforderung.
```
psql (13.3, server 1.18.0)
Type "help" for help.

postgres=#
```

Beispielstabelle und Daten erstellen

Nachdem Sie über PgBouncer mit PostgreSQL verbunden sind, erstellen wir eine Beispielstabelle und fügen einige Daten ein.

Eine Tabelle erstellen:

Führen Sie den folgenden SQL-Befehl aus, um eine Tabelle namens users zu erstellen:
```
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);
```
Sie sollten die folgende Ausgabe sehen:
```
CREATE TABLE
```

Daten in die Tabelle einfügen:

Fügen Sie einige Beispieldaten in die Tabelle users ein:

INSERT INTO users (name, email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]');

Sie sollten die folgende Ausgabe sehen:

INSERT 0 3

Die Daten überprüfen:

Überprüfen Sie, ob die Daten korrekt eingefügt wurden, indem Sie die Tabelle abfragen:

SELECT * FROM users;

Sie sollten die eingefügten Daten sehen:

 id |  name   |       email
----+---------+-----------------------
  1 | Alice   | [email protected]
  2 | Bob     | [email protected]
  3 | Charlie | [email protected]
(3 rows)

Simulieren mehrerer Client-Verbindungen

In diesem Schritt simulieren Sie mehrere Client-Verbindungen zur PostgreSQL-Datenbank über PgBouncer.

Ein Skript zum Verbinden und Abfragen erstellen:

Erstellen Sie ein Skript namens connect_and_query.sh in Ihrem Verzeichnis ~/project:
```
nano ~/project/connect_and_query.sh
```
Fügen Sie das folgende Skript in die Datei ein:
```
#!/bin/bash

psql -h 127.0.0.1 -p 6432 -U postgres -d postgres -c "SELECT COUNT(*) FROM users;"
```
Dieses Skript verbindet sich über PgBouncer mit der Datenbank und führt eine Abfrage aus, um die Anzahl der Benutzer zu zählen.

Machen Sie das Skript ausführbar:
```
chmod +x ~/project/connect_and_query.sh
```
Gleichzeitige Verbindungen simulieren:

Verwenden Sie xargs, um das Skript mehrmals gleichzeitig auszuführen:
```
seq 5 | xargs -P 5 -I {} ~/project/connect_and_query.sh
```
Geben Sie das Passwort ein, wenn Sie dazu aufgefordert werden. Sie sollten die Anzahl der Benutzer mehrmals ausgedruckt sehen.

Überwachen der Pooling-Leistung

In diesem Schritt überwachen Sie die Pooling-Leistung von PgBouncer.

Mit der PgBouncer-Administrationsdatenbank verbinden:

Verbinden Sie sich mit der pgbouncer-Datenbank:
```
psql -h 127.0.0.1 -p 6432 -U postgres -d pgbouncer
```
Geben Sie das Passwort ein, wenn Sie dazu aufgefordert werden.
PgBouncer-Statistiken anzeigen:

Führen Sie den folgenden SQL-Befehl aus, um die Statistiken anzuzeigen:
```
SHOW STATS;
```
Dies zeigt Statistiken über die Aktivität von PgBouncer an, einschließlich der Gesamtzahl der Anfragen (total requests), der empfangenen Bytes (received bytes) und der gesendeten Bytes (sent bytes).
Pool-Statistiken anzeigen:

Führen Sie den folgenden SQL-Befehl aus, um die Pool-Statistiken anzuzeigen:
```
SHOW POOLS;
```
Dies zeigt Statistiken über den Verbindungspool (connection pool) an, einschließlich aktiver Client-Verbindungen (active client connections), wartender Client-Verbindungen (waiting client connections), aktiver Server-Verbindungen (active server connections) und inaktiver Server-Verbindungen (idle server connections).

Durch die Untersuchung dieser Statistiken können Sie verstehen, wie PgBouncer Verbindungen verwaltet und potenzielle Engpässe (bottlenecks) identifizieren.
psql beenden:
```
\q
```

Zusammenfassung

In diesem Lab haben Sie PgBouncer für Connection Pooling (Verbindungsbündelung) konfiguriert, eine Beispiel-Tabelle und -Daten erstellt, mehrere Client-Verbindungen simuliert und die Leistung von PgBouncer überwacht. Sie haben gelernt, wie PgBouncer Datenbankverbindungen effizient verwaltet, den Overhead reduziert und die Leistung für Anwendungen mit häufigen, kurzlebigen Verbindungen verbessert. Durch die Überwachung der Statistiken von PgBouncer können Sie dessen Konfiguration optimieren und eine effiziente Verbindungsbündelung sicherstellen.