Optimisation des performances de votre serveur PostgreSQL

PostgreSQL, un système de gestion de bases de données relationnelles d'entreprise open source, est reconnu pour sa robustesse et sa fiabilité. Apprécié par de nombreuses organisations, il se distingue par son support des types de données avancés, sa conformité au modèle ACID, son extensibilité et ses fonctionnalités de sécurité performantes. Étant donné que PostgreSQL est souvent au cœur d'applications critiques, il est essentiel pour les administrateurs de bases de données de disposer d'un système de surveillance efficace. Celui-ci permet de détecter les points faibles et d'ajuster la configuration en fonction des besoins, afin d'optimiser les performances et garantir une fiabilité maximale.

Examinons les principaux paramètres de configuration pour optimiser le serveur de base de données PostgreSQL :

Configuration de la Mémoire

Paramètres	shared_buffers
Description	Les tampons partagés constituent une section dédiée de la mémoire, conçue pour mettre en cache les blocs d’index et les données fréquemment consultés. Leur utilisation permet au système de base de données d’améliorer ses performances en réduisant les accès au disque, qui sont souvent plus lents. Remarque : L’augmentation de ce paramètre nécessite également une augmentation de max_wal_size pour gérer le volume accru de données lors des checkpoints.
Valeur par défaut	128 Mo
Recommandé	25 % de la RAM

Paramètres	work_mem
Description	La mémoire de travail dans PostgreSQL correspond à la mémoire allouée pour exécuter des opérations individuelles, telles que le tri ou le hachage. Elle stocke des structures de données temporaires et des résultats intermédiaires générés au cours de ces opérations. La quantité de mémoire requise dépend de la complexité des opérations, de la taille des données traitées et de la mémoire disponible sur le système. Une allocation insuffisante de mémoire de travail peut entraîner l'utilisation de fichiers temporaires sur disque, ce qui ralentit considérablement les performances. Remarque : Postgres 13 introduit une nouvelle configuration, appelée hash_mem_multiplier, offrant une option supplémentaire pour optimiser l'allocation de la mémoire.
Valeur par défaut	4 Mo
Recommandé	Une valeur raisonnable doit être déterminée en fonction de la quantité de données interrogées et mises en mémoire. Il est également important de prendre en compte le paramètre max_connections avant de fixer une valeur, car plusieurs connexions peuvent s'exécuter simultanément. (RAM totale utilisée = work_mem * connexions simultanées). Trouver la valeur optimale peut être complexe, mais une valeur par défaut de 64 Mo est souvent un bon point de départ universel.

Paramètres	maintenance_work_mem
Description	Le paramètre maintenance_work_mem détermine la quantité de mémoire allouée aux opérations de maintenance, telles que VACUUM, la création d’index et l’ajout de clés étrangères. Comme une seule de ces opérations peut être exécutée à la fois dans une session de base de données, il est recommandé de définir une valeur supérieure à work_mem. Des valeurs plus élevées peuvent considérablement améliorer les performances, notamment lors des opérations d’aspiration et de restauration des sauvegardes de la base de données.
Valeur par défaut	64 Mo
Recommandé	Il est important de noter que lorsque autovacuum est actif, la mémoire consommée est calculée comme suit : mémoire consommée = autovacuum_max_workers x maintenance_work_mem. Cette valeur peut être de 512 Mo.

Configuration de la Mémoire Cache

Paramètres	effective_cache_size
Description	Le paramètre effective_cache_size détermine la quantité de mémoire que le serveur de base de données peut utiliser pour mettre en cache les données et les blocs d'index. Il représente une estimation de la mémoire disponible utilisée par le cache du système de fichiers de l'OS et d'autres processus actifs sur le même système. Ce paramètre est essentiel pour le planificateur de requêtes, qui l’utilise pour évaluer le coût des différents plans d’exécution. En estimant précisément la quantité de données pouvant être mises en cache, il permet au planificateur de prendre des décisions optimales pour sélectionner le plan d'exécution le plus efficace.
Valeur par défaut	4 Go
Recommandé	50% de la mémoire

Paramètres Worker

Paramètres	max_worker_processes
Description	Le paramètre max_worker_processes dans PostgreSQL détermine le nombre maximal de processus worker d’arrière-plan pouvant s’exécuter simultanément. Ces processus jouent un rôle essentiel dans diverses fonctions, telles que l’exécution parallèle des requêtes, la gestion des connexions et la réalisation de tâches en arrière-plan.
Valeur par défaut	8
Recommandé	Il est recommandé d'utiliser 50 % des processeurs pour que le système puisse fonctionner de manière adéquate.

Paramètres	max_parallel_workers
Description	Le paramètre max_parallel_workers parameter dans PostgreSQL définit la limite maximale de processus worker parallèles pouvant être actifs simultanément. Ces processus sont essentiels pour améliorer les performances lors de l’exécution parallèle des requêtes et d’autres opérations. Une configuration précise de ce paramètre est cruciale, car elle influence directement l’utilisation des ressources et les performances globales du système.
Valeur par défaut	2
Recommandé	Il est recommandé d'utiliser 50 % des processeurs pour que le système puisse fonctionner de manière adéquate.

Paramètres	max_parallel_workers_per_gather
Description	Le paramètre max_parallel_workers_per_gather dans PostgreSQL détermine le nombre maximal de processus worker parallèles pouvant être alloués à une seule opération. Il fixe une limite haute pour les workers chargés d’exécuter les scans de tables et les plans de requêtes en parallèle. Ce paramètre a un impact significatif sur les performances des requêtes parallèles et nécessite une configuration réfléchie, adaptée aux ressources système et aux exigences de la charge de travail.
Valeur par défaut	2
Recommandé	Les workers parallèles sont déduits du pool de processus défini par max_worker_processes, dans la limite de max_parallel_workers.

Autres

Paramètres	max_wal_size
Description	Le paramètre max_wal_size dans PostgreSQL fixe une limite haute à la taille des WAL (write-ahead log) que le système de base de données peut utiliser. Le WAL joue un rôle fondamental dans la gestion des transactions, garantissant la cohérence et la durabilité des données, même en cas de panne ou de défaillance du système. La configuration de ce paramètre permet de fixer la taille maximale pour l'augmentation des WAL entre deux points de vérification automatique. La taille des WAL peut excéder max_wal_size sous certaines circonstances, comme une surcharge du serveur, une commande archive_command qui échoue, ou une configuration haute pour wal_keep_size.
Valeur par défaut	1 Go
Recommandé	Augmenter ce paramètre peut augmenter le temps nécessaire pour le rejeu suite à un crash.

Paramètres	default_statistics_target
Description	Le paramètre de configuration default_statistics_target spécifie le niveau de détail utilisé par l'optimiseur de requêtes lors de la collecte de statistiques sur la base de données. Le nombre de lignes examinées par l'optimiseur pour déterminer le plan d'exécution de requête le plus efficace est régi par ce paramètre. Il détermine la taille de l'échantillon utilisé par l'optimiseur lors de l'analyse d'une table à cette fin.
Valeur par défaut	*Taille de l’échantillon = 300 default_statistics_target**
Recommandé	Des valeurs plus élevées augmentent le temps nécessaire pour travailler sur ANALYZE, mais peuvent améliorer la qualité des estimations du planificateur.

Attention : La modification de configurations peut avoir un impact significatif sur le système. Il est fortement recommandé de tester les changements sous une charge simulée avant de les appliquer à l’environnement de production.

Applications Manager est un outil puissant pour surveiller les performances des bases de données PostgreSQL. Grâce à ses fonctionnalités de surveillance avancées, il permet aux administrateurs de garantir le bon fonctionnement de la base de données avec un minimum de temps d’arrêt et des coûts opérationnels maîtrisés à long terme. Vous pouvez configurer des alertes sur des métriques critiques et recevoir des notifications lorsque les seuils prédéfinis sont dépassés. Ces alertes servent de signal pour ajuster vos configurations et optimiser les performances, garantissant ainsi une expérience utilisateur fluide et sans interruption.

Pour améliorer les performances de votre serveur PostgreSQL, rien ne vaut une expérience pratique avec Applications Manager

Téléchargez l'essai gratuit de 30 jours dès maintenant !