Maîtrisez les outils et bonnes pratiques DevOps - DevOps Knowledge Hub Maîtrisez les outils et bonnes pratiques DevOps - DevOps Knowledge Hub

Configuration du regroupement de connexions PostgreSQL avec PgBouncer pour les applications à fort trafic

Découvrez comment configurer le regroupement de connexions PgBouncer pour PostgreSQL afin de gérer des milliers de connexions simultanées, de réduire la surcharge des ressources et d'améliorer considérablement les performances des applications.

Configuration du pool de connexions PostgreSQL avec PgBouncer pour les applications à fort trafic

Introduction

Lorsque les bases de données PostgreSQL font face à un volume élevé de connexions, les performances peuvent se dégrader rapidement. Chaque connexion à la base de données consomme des ressources système, et PostgreSQL impose des limites pratiques sur les connexions simultanées. PgBouncer, un système de pooling de connexions léger, résout ce problème en maintenant un pool de connexions à la base de données et en les distribuant efficacement aux applications clientes.

Pourquoi le Pooling de Connexions est Important

Le Problème des Connexions

  • Surcharge de Ressources : Chaque processus backend PostgreSQL consomme 5 à 10 Mo de mémoire
  • Limites de Connexion : max_connections par défaut est généralement de 100 à 200
  • Coût de Démarrage : La création de nouvelles connexions prend 1 à 5 ms chacune
  • Commutation de Contexte : Trop de processus provoquent un stress CPU (thrashing)

Avantages de PgBouncer

  • Réduit le nombre de connexions à la base de données de 10 à 100 fois
  • Permet des milliers de connexions clientes avec une surcharge minimale
  • Fournit une file d'attente des connexions lors des pics de charge
  • Supporte plusieurs modes de pooling pour différents cas d'utilisation

Installation et Configuration de Base

Installation de PgBouncer

Sur Ubuntu/Debian :

sudo apt update
sudo apt install pgbouncer

Sur CentOS/RHEL :

sudo yum install pgbouncer

Sur macOS :

brew install pgbouncer

Structure des Répertoires

/etc/pgbouncer/
  ├── pgbouncer.ini        # Configuration principale
  └── userlist.txt         # Identifiants d'authentification

Configuration du Fichier

Configuration de base pgbouncer.ini

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

[pgbouncer]
; Mode de pooling de connexion
pool_mode = transaction

; Connexions maximales
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Réseau
listen_addr = 0.0.0.0
listen_port = 6432

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

; Journalisation
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Performance
max_prepared_statements = 0

Compréhension des Modes de Pooling

1. Pooling de Session (pool_mode = session)

  • Comportement : La connexion est assignée au client pour toute la session
  • Cas d'utilisation : Applications utilisant des tables temporaires, des instructions préparées
  • Efficacité : Faible (ratio de connexion 1:1)
pool_mode = session

2. Pooling de Transaction (pool_mode = transaction) - Recommandé

  • Comportement : La connexion est retournée au pool après chaque transaction
  • Cas d'utilisation : La plupart des applications web avec de courtes transactions
  • Efficacité : Élevée (réduction de 10 à 100 fois)
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. Pooling d'Instruction (pool_mode = statement)

  • Comportement : La connexion est retournée après chaque instruction
  • Cas d'utilisation : Requêtes en lecture seule simples sans transactions
  • Efficacité : Maximale (mais très restrictive)
pool_mode = statement
; À utiliser avec prudence - brise les transactions multi-instructions

Configuration de l'Authentification

Création de userlist.txt

PgBouncer nécessite un fichier d'authentification séparé. Générez le hash MD5 et ajoutez-le à userlist.txt.

Exemple userlist.txt :

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

Utilisation de auth_query PostgreSQL (Avancé)

Interrogez PostgreSQL directement pour l'authentification :

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

Configuration Optimale pour Haut Trafic

Dimensionnement du Pool de Connexions

Formule pour le dimensionnement du pool :

default_pool_size = (num_cores × 2) + effective_spindle_count

Pour un serveur 4 cœurs avec SSD :

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

Configuration de Production Complète

[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

; Limites de connexion
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

; Réseau
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Sécurité
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; Journalisation
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

Chaîne de Connexion de l'Application

Avant PgBouncer

# Connexion directe PostgreSQL
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

Après PgBouncer

# Connexion via PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Surveillance et Gestion

Commandes de la Console Admin

Connexion à la console d'administration de PgBouncer :

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Commandes essentielles :

-- Afficher les statistiques du pool
SHOW POOLS;

-- Afficher les connexions actives
SHOW CLIENTS;
SHOW SERVERS;

-- Afficher la configuration
SHOW CONFIG;

-- Recharger la configuration
RELOAD;

Dépannage des Problèmes Courants

Problème 1 : "no more connections allowed"

Solution :

max_client_conn = 5000
default_pool_size = 50

Problème 2 : Compteur cl_waiting élevé

Solutions :

  1. Augmenter la taille du pool
  2. Optimiser les requêtes lentes
  3. Ajouter un pool de réserve

Problème 3 : Erreurs d'instructions préparées

Solution :

max_prepared_statements = 0

Exemples d'Impact sur les Performances

Avant PgBouncer

  • 500 requêtes concurrentes → 500 connexions PostgreSQL
  • Charge de la base de données : 95% CPU, 8 Go RAM
  • Temps de réponse : 250 ms en moyenne

Après PgBouncer

  • 500 requêtes concurrentes → 25 connexions PostgreSQL
  • Charge de la base de données : 35% CPU, 1 Go RAM
  • Temps de réponse : 80 ms en moyenne
  • Résultat : 3 fois plus rapide, 70% de ressources en moins

Conclusion

PgBouncer est essentiel pour faire évoluer les applications PostgreSQL. Il réduit la surcharge des connexions de 90%+, supporte 10 à 100 fois plus de clients et améliore considérablement les temps de réponse. Commencez avec le mode de pooling de transaction et ajustez en fonction de la surveillance.