Pooling de connexions PgBouncer pour PostgreSQL

Introduction

Dans ce laboratoire, vous apprendrez comment configurer et utiliser PgBouncer, un gestionnaire de connexions léger pour PostgreSQL. La gestion des connexions (connection pooling) est une technique essentielle pour améliorer les performances de la base de données, en particulier pour les applications qui gèrent de nombreuses connexions de courte durée. En gérant un pool de connexions de base de données réutilisables, PgBouncer réduit la surcharge liée à l'établissement de nouvelles connexions pour chaque requête client.

Vous commencerez par installer et configurer PgBouncer, en créant les fichiers de configuration et d'authentification des utilisateurs nécessaires. Ensuite, vous démarrerez le service PgBouncer et testerez la connexion à votre base de données PostgreSQL via le gestionnaire de connexions. Vous utiliserez ensuite pgbench, un outil d'évaluation comparative standard de PostgreSQL, pour simuler une charge client et observer comment PgBouncer gère les connexions. Enfin, vous apprendrez comment vous connecter à la console d'administration de PgBouncer pour surveiller ses performances et afficher les statistiques de connexion.

Configurer PgBouncer

Dans cette étape, vous allez créer les fichiers de configuration nécessaires pour PgBouncer. Cela implique de définir la chaîne de connexion à la base de données et de configurer un fichier d'authentification pour les utilisateurs.

Tout d'abord, créez un répertoire dédié pour vos fichiers de configuration PgBouncer dans votre dossier de projet.

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

Ensuite, créez le fichier de configuration principal, pgbouncer.ini, à l'aide de l'éditeur nano.

nano pgbouncer.ini

Collez la configuration suivante dans l'éditeur. Cette configuration indique à PgBouncer comment se connecter à votre base de données PostgreSQL et sur quel port écouter pour les connexions client.

[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

Examinons les paramètres clés :

• [databases]: Définit la chaîne de connexion à la base de données cible.
• listen_port: Le port sur lequel PgBouncer écoutera (6432). Votre application se connectera à ce port.
• auth_type: Spécifie la méthode d'authentification. md5 est un choix courant.
• auth_file: Le chemin vers le fichier contenant les noms d'utilisateur et les mots de passe.
• admin_users: Une liste d'utilisateurs séparés par des virgules autorisés à se connecter à la console d'administration de PgBouncer.
• pool_mode: Défini sur session, ce qui signifie qu'une connexion serveur est attribuée à un client pour toute la durée de la session.

Appuyez sur Ctrl+O pour enregistrer le fichier, puis sur Entrée, et Ctrl+X pour quitter nano.

Créez maintenant le fichier d'authentification userlist.txt auquel vous avez fait référence dans la configuration.

nano userlist.txt

Ajoutez la ligne suivante au fichier. Le mot de passe labex_password a été défini pour l'utilisateur postgres lors de la phase de configuration du laboratoire.

"postgres" "labex_password"

Enregistrez et quittez l'éditeur en appuyant sur Ctrl+O, Entrée, puis Ctrl+X.

Vous avez maintenant configuré avec succès PgBouncer pour gérer les connexions à votre base de données PostgreSQL.

Démarrer PgBouncer et tester la connexion

Une fois la configuration en place, l'étape suivante consiste à démarrer le service PgBouncer et à vérifier que vous pouvez vous connecter à la base de données PostgreSQL via celui-ci.

Dans votre terminal, démarrez PgBouncer en mode démon (-d flag) en utilisant le fichier de configuration que vous avez créé.

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

Vous pouvez vérifier que le processus PgBouncer est en cours d'exécution avec la commande suivante. Vous devriez voir le processus pgbouncer listé.

ps aux | grep pgbouncer

La sortie ressemblera à ceci :

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

Maintenant, testez la connexion en utilisant psql pour vous connecter au port sur lequel PgBouncer écoute (6432), et non au port PostgreSQL par défaut (5432).

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

Il vous sera demandé le mot de passe. Entrez labex_password. Si la connexion réussit, vous verrez l'invite psql.

Pour confirmer que vous êtes connecté à la base de données, exécutez une requête simple pour vérifier la version de PostgreSQL.

SELECT version();

La sortie affichera la version de votre serveur PostgreSQL, confirmant que PgBouncer a correctement géré votre connexion.

                                                 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)

Enfin, quittez le shell psql en tapant \q et en appuyant sur Entrée.

\q

Simuler la charge client avec pgbench

Pour observer le fonctionnement du pooling de connexions, vous utiliserez pgbench, un outil d'évaluation standard inclus avec PostgreSQL. pgbench peut simuler plusieurs clients accédant simultanément à la base de données.

Tout d'abord, vous devez initialiser l'environnement pgbench. Cela crée quelques tables et les remplit avec des données d'exemple. Exécutez la commande suivante, en vous assurant de vous connecter via le port PgBouncer (6432).

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

Il vous sera demandé le mot de passe (labex_password). Le drapeau -i initialise la base de données pour l'évaluation. Vous devriez voir une sortie indiquant que les tables ont été créées et remplies.

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).

Maintenant, exécutez l'évaluation pour simuler une charge. La commande suivante simule 10 clients concurrents (-c 10), chaque client exécutant 300 transactions (-t 300).

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

Encore une fois, entrez le mot de passe lorsqu'il est demandé. L'évaluation s'exécutera pendant quelques secondes, puis affichera un résumé des résultats, y compris le nombre de transactions par seconde (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)

Ce test a généré un trafic important via PgBouncer, que nous allons examiner à l'étape suivante.

Surveiller les statistiques de PgBouncer

PgBouncer fournit une base de données administrative spéciale qui vous permet de surveiller son activité et de consulter des statistiques sur les pools de connexions.

Connectez-vous à la console d'administration de PgBouncer. Notez que le nom de la base de données est pgbouncer.

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

Entrez le mot de passe labex_password lorsqu'il vous est demandé.

Une fois connecté, vous pouvez exécuter des commandes spéciales SHOW. Tout d'abord, affichez les statistiques globales pour toutes les bases de données.

SHOW STATS;

Cette commande affiche les données cumulées depuis le dernier démarrage de PgBouncer. Vous verrez des colonnes telles que total_xact_count (nombre total de transactions) et total_query_count, qui devraient maintenant avoir des valeurs élevées suite au test pgbench.

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)

Ensuite, pour voir l'état des pools de connexions en temps réel, utilisez la commande SHOW POOLS.

SHOW POOLS;

Cette commande fournit un instantané des pools de connexions, montrant les connexions actives et inactives pour les clients et les serveurs.

• cl_active : Connexions client activement liées à une connexion serveur.
• sv_active : Connexions serveur actuellement utilisées.
• sv_idle : Connexions serveur inactives et prêtes à être utilisées par un nouveau client.

 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)

En examinant ces statistiques, vous pouvez comprendre l'efficacité avec laquelle PgBouncer gère les connexions et les réutilise pour répondre aux requêtes des clients.

Quittez le shell psql lorsque vous avez terminé.

\q

Résumé

Dans ce laboratoire, vous avez configuré et utilisé avec succès PgBouncer pour le pooling de connexions PostgreSQL. Vous avez appris à créer le fichier de configuration pgbouncer.ini et les fichiers d'authentification userlist.txt. Vous avez démarré le service PgBouncer et vérifié qu'il pouvait proxy les connexions vers votre base de données PostgreSQL. En utilisant pgbench, vous avez simulé une charge client réaliste, puis surveillé les résultats à l'aide de la console d'administration de PgBouncer. Cela vous a donné un aperçu pratique du fonctionnement du pooling de connexions et de la manière dont il peut être surveillé pour garantir des performances efficaces de la base de données.