Comment benchmarker PostgreSQL pour une performance optimale

À mesure que l’adoption de PostgreSQL augmente, les administrateurs de bases de données (DBAs) et les développeurs doivent souvent évaluer ses performances pour s’assurer que leurs applications fonctionnent efficacement sous différents charges de travail. Le benchmarking des performances est un processus critique qui mesure la capacité de PostgreSQL à gérer des charges variables, aidant à identifier les goulots d’étranglement et les domaines à optimiser. Cet article explore les outils, les métriques et les scénarios de test pour vous aider à benchmarker PostgreSQL comme un pro.

Pourquoi benchmarker PostgreSQL ?

Le benchmarking vous permet de :

1. Mesurer le débit et la latence de votre base de données sous des charges de travail spécifiques.
2. Identifier les goulots d’étranglement matériels ou de configuration.
3. Comparer l’impact des optimisations comme les changements d’index ou les réécritures de requêtes.
4. Simuler des scénarios du monde réel tels que l’activité élevée d’utilisateurs simultanés ou les écritures massives de données.

Métriques clés à suivre

Lors du benchmarking PostgreSQL, concentrez-vous sur ces métriques :

• TPS (Transactions Par Seconde) : Mesure le nombre de transactions que la base de données complète en une seconde.
• IOPS (Opérations d’Entrée/Sortie Par Seconde) : Suit l’activité du disque.
• Latence : Mesure le temps pris pour exécuter des requêtes, ce qui impacte l’expérience utilisateur.
• Utilisation des Ressources : Suit l’utilisation du CPU, de la mémoire et du disque pendant le benchmark.

Outils pour le benchmarking de PostgreSQL

1. pgbench

Qu’est-ce que pgbench ?

pgbench est l’outil de benchmark intégré de PostgreSQL. Il simule des clients concurrents exécutant des transactions et mesure les performances de la base de données.

Installation

Il est inclus dans les installations de PostgreSQL. Pour vérifier, exécutez :

bash
pgbench --version

Démarrage

1. Initialiser une base de données de benchmark :

bash
pgbench -i -s 50 mydb

Ici, -s définit le facteur d’échelle, qui détermine la taille de l’ensemble de données.

2. Exécuter un benchmark simple :

bash
pgbench -c 10 -j 2 -T 60 mydb

• -c 10 : Nombre de connexions clients.
• -j 2 : Nombre de threads.
• -T 60 : Durée du benchmark en secondes.

Exemple de sortie :

transaction type: TPC-B (sort of)
scaling factor: 50
number of clients: 10
number of threads: 2
duration: 60 s
tps = 1420.123 (excluding connections establishing)

2. Sysbench

Pourquoi utiliser Sysbench ?

Sysbench est un outil de benchmarking polyvalent pour bases de données et systèmes. Il offre plus de flexibilité que pgbench pour des charges de travail personnalisées.

Installation

Installez Sysbench avec la commande suivante :

bash
sudo apt-get install sysbench

Démarrage

1. Préparer le benchmark :

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --tables=10 --table-size=1000000 \
  oltp_read_write prepare

2. Exécuter le benchmark :

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --threads=4 \
  --time=60 oltp_read_write run

3. pg_stat_statements

Qu’est-ce que pg_stat_statements ?

Une extension PostgreSQL qui suit les performances des requêtes et les statistiques d’exécution. Bien qu’elle ne simule pas les charges de travail, elle aide à analyser les requêtes lentes pendant les benchmarks.

Configuration

1. Activez l’extension dans postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

2. Rechargez la configuration et créez l’extension:

CREATE EXTENSION pg_stat_statements;

Utilisation

Exécutez la requête suivante pour identifier les instructions longues:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

Scénarios de Benchmarking

Ci-dessous est une représentation visuelle des résultats de benchmark pour trois scénarios : charges de lecture intensive, charges d’écriture intensive et charges mixtes. Le diagramme des transactions par seconde (TPS) démontre la capacité de PostgreSQL à gérer des transactions concurrentes de manière efficace, tandis que le diagramme de latence illustre le temps pris pour l’exécution des requêtes en millisecondes.

Transactions par Seconde de PostgreSQL (TPS)

Latence des Requêtes PostgreSQL

Types de Charges de Travail

1. Charges de Travail Lourdes en Lecture

Objectif: Tester les performances de la base de données sous une activité de lecture élevée.

Configuration: Utilisez pgbench avec des transactions en lecture seule par défaut:

bash
pgbench -c 50 -T 120 -S mydb

• -S: Exécuter uniquement des requêtes SELECT.
• -c 50: Simuler 50 clients concurrents.

2. Charges de Travail Lourdes en Écriture

Objectif: Mesurer les performances de la base de données avec des insertions ou mises à jour fréquentes.

Configuration: Modifiez le benchmark pour inclure des écritures:

bash
pgbench -c 20 -j 4 -T 120 -N mydb

• -N : Exécute des requêtes non SELECT.

3. Chargements de travail mixtes Lecture/Écriture

Objectif : Simuler un chargement de travail réel mélangeant lectures et écritures.

Configuration : Utiliser une configuration équilibrée :

bash
pgbench -c 30 -j 4 -T 180 mydb

Optimiser PostgreSQL pour de meilleurs résultats de benchmark

Ajuster les paramètres de mémoire

Ajuster ces paramètres dans postgresql.conf :

shared_buffers = 25% of system memory
work_mem = 4MB
maintenance_work_mem = 64MB

Activer l’exécution parallèle des requêtes

Ajuster ces paramètres dans postgresql.conf :

max_parallel_workers_per_gather = 4

Optimiser les entrées/sorties disque

Utiliser des SSD pour les fichiers WAL et ajuster ces paramètres :

wal_buffers = 16MB
synchronous_commit = off

Exemples de résultats et interprétation

Scénario : 50 clients simultanés exécutant un chargement de travail à dominante lecture pendant 60 secondes.

Sortie :

tps = 2500.456 (excluding connections establishing)

Interprétation : La base de données est capable de gérer 2500 transactions par seconde sous ce chargement de travail.

Si le TPS est inférieur à ce qui est attendu, analyser les plans de requête en utilisant EXPLAIN ANALYZE pour identifier les goulets d’étranglement de performance.

Conclusion

Benchmarking PostgreSQL est une méthode puissante pour identifier les limitations de performance et optimiser votre base de données pour diverses charges de travail. Des outils comme pgbench et sysbench, combinés aux informations de pg_stat_statements, vous permettent de simuler des scénarios réels et d’ajuster finement les configurations PostgreSQL.

En maîtrisant ces outils et techniques, vous pouvez garantir que votre instance PostgreSQL offre des performances élevées pour les applications à lecture intensive et à écriture intensive.