Par défaut, PostgreSQL n'est pas configuré pour la charge de travail. Les valeurs par défaut sont définies pour garantir que PostgreSQL est fonctionnel partout avec le moins de ressources. Il existe des paramètres par défaut pour tous les paramètres de la base de données. La responsabilité principale d'un administrateur de base de données ou d'un développeur est de configurer PostgreSQL pour s'adapter à la charge de leur système. Dans ce blog, nous décrirons les recommandations de base pour régler les paramètres de la base de données PostgreSQL afin d'améliorer les performances de la base de données en fonction de la charge de travail.
Gardez à l'esprit que tout en optimisant la configuration de votre serveur PostgreSQL améliore les performances, le concepteur de base de données doit également être prudent lors de l'écriture de requêtes. Si les requêtes effectuent une analyse complète de la table où un index peut être utilisé, ou effectuent des jointures lourdes ou des opérations d'agrégation coûteuses, le système peut toujours fonctionner de manière médiocre, même si les paramètres de la base de données sont correctement configurés. Lors de l'écriture de requêtes de base de données, il est important de prêter attention aux performances.
Cependant, les paramètres de base de données sont également très importants, alors examinons les huit qui ont le plus grand potentiel d'amélioration des performances.
Options PostgreSQL personnalisées
PostgreSQL utilise son propre tampon, et utilise également un noyau IO tamponné. Cela signifie que les données sont stockées en mémoire deux fois, d'abord dans le tampon PostgreSQL, puis dans le tampon du noyau. Contrairement à d'autres bases de données, PostgreSQL ne fournit pas d'E / S directes. C'est ce qu'on appelle la double mise en mémoire tampon. Le tampon PostgreSQL est appelé
shared_buffer , qui est le paramètre personnalisé le plus efficace pour la plupart des systèmes d'exploitation. Ce paramètre définit la quantité de mémoire allouée que PostgreSQL utilisera pour la mise en cache.
La valeur par défaut de shared_buffer est définie très bas et vous n'en tirerez pas beaucoup d'avantages. En effet, certaines machines et certains systèmes d'exploitation ne prennent pas en charge des valeurs plus élevées. Mais dans la plupart des machines modernes, vous devez augmenter cette valeur pour des performances optimales.
La valeur recommandée est de 25% de la RAM totale de l'ordinateur. Vous devez essayer des valeurs inférieures et supérieures, car dans certains cas, vous pouvez obtenir de bonnes performances avec un paramètre supérieur à 25%. Mais la configuration réelle dépend de votre machine et de l'ensemble de données de travail. Si votre jeu de données de travail peut facilement tenir dans votre RAM, vous pouvez augmenter la valeur de shared_buffer afin qu'il contienne l'intégralité de votre base de données et que l'ensemble de données de travail puisse être dans le cache. Cependant, vous ne voulez évidemment pas réserver toute la RAM pour PostgreSQL.
Il est à noter que dans les environnements de production, de bonnes performances accordent vraiment une grande importance à shared_buffer, bien que des tests doivent toujours être effectués pour atteindre le bon équilibre.
Vérification de la valeur de shared_buffertestdb=
Remarque : soyez prudent, car certains noyaux ne prennent pas en charge une valeur plus élevée , en particulier sous Windows.wal_buffers
PostgreSQL écrit d'abord les entrées du WAL (journal de pré-enregistrement) dans les tampons, puis ces tampons sont vidés sur le disque. La taille de tampon par défaut définie par
wal_buffers est de 16 Mo. Mais si vous disposez de nombreuses connexions simultanées, une valeur plus élevée peut améliorer les performances.
effective_cache_size
effective_cache_size fournit une estimation de la mémoire disponible pour la mise en cache du disque. Ce n'est qu'une indication, pas la quantité exacte de mémoire ou de cache allouée. Il n'alloue pas de mémoire réelle, mais indique à l'optimiseur la quantité de cache disponible dans le noyau. Si ce paramètre est défini trop bas, le planificateur de requêtes peut décider de ne pas utiliser certains index, même s'ils sont utiles. Par conséquent, définir une grande valeur est toujours logique.
work_mem
Ce paramètre est utilisé pour un tri complexe. Si vous devez effectuer un tri complexe, augmentez la valeur de
work_mem pour obtenir de bons résultats. Le tri en mémoire est beaucoup plus rapide que le tri des données sur disque. La définition d'une valeur très élevée peut entraîner un goulot d'étranglement de la mémoire pour votre environnement, car cette option concerne l'opération de tri de l'utilisateur. Par conséquent, si de nombreux utilisateurs tentent d'effectuer des opérations de tri, le système mettra en évidence:
work_mem * total sort operations
pour tous les utilisateurs. La définition de ce paramètre globalement peut entraîner une très grande utilisation de la mémoire. Par conséquent, il est fortement recommandé de le modifier au niveau de la session.
work_mem = 2 Mo testdb=
Le nœud de tri de demande initial est évalué au 514431.86. Le coût est une unité calculée arbitrairement. Pour la demande ci-dessus, nous n'avons work_mem que 2 Mo. À des fins de test, augmentons cette valeur à 256 Mo et voyons si cela affecte le coût.
work_mem = 256 Mo testdb=
Le coût de la demande est passé de 514431,86 à 360617,36, soit une diminution de 30%.
maintenance_work_mem
maintenance_work_mem est un paramètre de mémoire utilisé pour les tâches de maintenance. La valeur par défaut est 64 Mo. La définition d'une grande valeur aide dans des tâches telles que VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY et ALTER TABLE.
maintenance_work_mem = 10 Mo postgres=
maintenance_work_mem = 256 Mo postgres=
Le temps de création d'index est de 170091,371 ms si le paramètre maintenance_work_mem est défini à seulement 10 Mo, mais il diminue à 111274,903 ms lorsque nous augmentons le paramètre maintenance_work_mem à 256 Mo.
synchronous_commit
Utilisé pour garantir qu'une validation de transaction attend qu'un WAL écrive sur le disque avant de renvoyer un état d'achèvement réussi au client. Il s'agit d'un compromis entre performances et fiabilité. Si votre application est conçue de manière à ce que les performances soient plus importantes que la fiabilité, désactivez
synchronous_commit . Dans ce cas, la transaction est validée très rapidement car elle n'attendra pas la réinitialisation du fichier WAL, mais la fiabilité sera compromise. En cas de défaillance d'un serveur, les données peuvent être perdues même si le client a reçu un message indiquant que la validation de la transaction s'est terminée avec succès.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL écrit des modifications dans le WAL. Le processus de point de contrôle vide les données dans des fichiers. Cette action est effectuée lorsqu'un point d'arrêt (CHECKPOINT) se produit. Cette opération est coûteuse et peut entraîner un grand nombre d'opérations d'E / S. L'ensemble de ce processus implique des opérations de lecture / écriture coûteuses sur le disque. Les utilisateurs peuvent toujours démarrer la tâche de point de contrôle (CHECKPOINT) si nécessaire, ou automatiser le démarrage à l'aide des paramètres
checkpoint_timeout et
checkpoint_completion_target .
Le paramètre checkpoint_timeout est utilisé pour définir le temps entre les points d'arrêt WAL. Une valeur trop basse réduit le temps de récupération après un crash car davantage de données sont écrites sur le disque, mais elle réduit également les performances car chaque point de contrôle consomme finalement des ressources système précieuses.
checkpoint_completion_target est la fraction du temps entre les points de contrôle pour terminer un point de contrôle. Les points de contrôle à haute fréquence peuvent affecter les performances. Pour terminer le travail de point de contrôle en douceur,
checkpoint_timeout doit être faible. Sinon, le système d'exploitation accumulera toutes les pages sales jusqu'à ce que le rapport soit observé, puis produira une réinitialisation importante.
Conclusion
Il existe plus d'options que vous pouvez modifier pour obtenir de meilleures performances, mais elles ont moins d'impact que celles mises en évidence ici. En fin de compte, nous devons toujours nous rappeler que tous les paramètres ne sont pas pertinents pour tous les types d'applications. Certaines applications fonctionnent mieux lors de la définition des options, d'autres non. Les paramètres de la base de données PostgreSQL doivent être adaptés aux besoins spécifiques de l'application et du système d'exploitation dans lequel elle s'exécute.