Rapide configuration de PostgreSQL

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
44
|
Mois de parution
octobre 2009
|


Résumé
PostgreSQL et son fichier de configuration de 17 Ko sur 503 lignes : rien de moins que 180 et quelques paramètres. Cela n'aide clairement pas un débutant à se lancer. Pourtant, il faut savoir que seule une grosse dizaine de paramètres sont essentiels à configurer. Le reste n'a pour cible que les cas très particuliers.

Body

1. Paramètres de connexion

Par défaut, PostgreSQL n'écoute que sur l'interface locale. Donc, personne ne peut se connecter sur le serveur PostgreSQL à partir d'une autre machine. Si vous avez besoin d'autoriser l'accès externe (ce qui est généralement le cas), il vous faudra indiquer les interfaces (adresses IP) sur lesquelles PostgreSQL doit attendre les connexions. Pour cela, vous devez modifier le paramètre listen_addresses. Généralement, ce paramètre est passé à * pour indiquer toutes les interfaces. Mais, si vous voulez n'autoriser qu'une seule interface, c'est tout à fait possible.

Si vous voulez autoriser les connexions SSL, il vous faut activer le paramètre du même nom. Attention, cela ne veut pas dire ensuite que toutes les connexions seront en SSL. Pour forcer l'utilisation du SSL, vous devrez tout d'abord configurer cela dans le fichier des authentifications (dont le nom est pg_hba.conf).

Enfin, dernier paramètre de cette catégorie : max_connections. PostgreSQL bloque le nombre maximum de connexions simultanées grâce à ce paramètre. Par défaut, il est à 100, ce qui est une valeur généralement correcte. Il ne faut pas hésiter à l'augmenter, tout en sachant que des valeurs supérieures à 1000 ne sont pas conseillées. Il vaudra mieux utiliser un pooler de connexions dans ce cas.

2. Paramètres mémoire

Un serveur de bases de données doit ses performances principalement à sa gestion de la mémoire. En effet, le but est de fournir des données et le meilleur moyen de les donner rapidement, c'est de les avoir déjà en mémoire.

Il faut considérer avec PostgreSQL deux types de mémoire : la mémoire partagée par les processus serveur et la mémoire individuelle de chaque processus.

La mémoire partagée concerne principalement le cache disque : celui des fichiers de données et celui des journaux de transactions. La taille du premier dépend du paramètre shared_buffers, et la taille du second concerne le paramètre wal_buffers. Pour un serveur dédié, un quart de la mémoire est un bon départ pour le paramètre shared_buffers. Pour les autres, il faudra voir la mémoire utilisée par les autres processus pour en déduire ce qui est laissé à PostgreSQL. wal_buffers est beaucoup plus simple à configurer. Pour une instance qui sera fortement utilisée par de nombreux utilisateurs, il est intéressant de l'augmenter à une valeur généralement comprise entre 1 et 8 Mo.

La mémoire individuelle est utilisée dans deux contextes : les tris et hachages, et les opérations de maintenance. Pour les opérations de tris et de hachages, le paramètre work_mem permet d'indiquer la quantité de mémoire utilisable par chaque processus. Si jamais le processus a besoin de plus, les données déjà triées/hachées sont enregistrées sur disque avant de réutiliser la mémoire déjà attribuée. Augmenter cette valeur permet donc d'éviter l'utilisation du disque, mais il ne faut pas l'augmenter trop, car chaque client peut l'utiliser. La valeur est donc à mettre en adéquation avec le nombre de clients maximum possibles (paramètre max_connections) et avec la complexité des requêtes (si une même requête demande plusieurs opérations de tris et/ou de hachage, la mémoire réclamée sera d'autant plus importante). Quant aux opérations de maintenance, maintenance_work_mem permet d'augmenter la mémoire disponible. Les opérations en question sont le VACUUM, la création d'index et la création de clés étrangères. Dans ce cas, on peut mettre des valeurs plus importantes que pour le work_mem. La principale raison est que ces opérations ont généralement besoin de beaucoup plus de mémoire que les tris et les hachages. La seconde raison est que le nombre d'utilisateurs qui exécuteront des opérations de maintenance au même instant est tellement limité qu'il est difficile de croire que plus de deux personnes le feront en même temps.

3. Journaux de transactions

Pour des raisons de performances, il est souvent intéressant d'augmenter le nombre des journaux de transactions. En effet, PostgreSQL ne déclenche les écritures sur les fichiers de données (une opération appelée « CHECKPOINT ») qu'à partir du moment où un certain délai est écoulé ou quand un certain nombre de journaux sont écrits. Un paramètre permet de configurer cela : checkpoint_segments. Il vaut 3 par défaut, et il est généralement raisonnable de l'augmenter au moins à 10.

Du coup, comme nous repoussons le moment du CHECKPOINT, donc de l'écriture dans les fichiers de données, la quantité de données à écrire sera beaucoup plus importante. Du coup, pour éviter un pic d'écriture au moment du CHECKPOINT, nous allons demander à diluer les écritures dans le temps jusqu'au prochain CHECKPOINT. Disons qu'il faut 4 minutes entre deux CHECKPOINT, plutôt que d'écrire les x Mo de données en quelques secondes, bloquant en gros le reste de l'activité, nous allons faire les écritures de façon à ce que ces x Mo soient écrits en un peu moins de 4 minutes. Le paramètre pour cela est checkpoint_completion_target et nous indiquons le pourcentage du temps d'écriture entre deux CHECKPOINT. Généralement, il se révèle intéressant de le configurer à 0,9.

4. Planificateur

Deux paramètres sont forcément à considérer pour une meilleure planification des plans de requêtes.

effective_cache_size est la taille du cache disque du système d'exploitation. Cela permet au planificateur d'estimer la probabilité pour qu'une table et un index soient en cache. Plus la valeur de ce paramètre est importante, plus les parcours d'index sont valorisés.

random_page_cost est le coût d'accès à une page aléatoire sur disque. Pour les disques récents et rapides, diminuer cette valeur jusqu'à 2 peut apporter un plus très conséquent, car là aussi cela favorisera les parcours d'index.

5. Journalisation

Même s'il ne s'agit pas de performances, une bonne journalisation des traces est essentielle pour savoir ce qui se passe sur le serveur. Il n'y a que deux paramètres essentiels. log_destination doit être placé à syslog pour en tirer toutes les possibilités. La rotation des journaux de traces sera réalisée avec un outil comme Logrotate. lc_messages doit être configuré à C pour s'assurer que les messages seront en anglais. Ceci est nécessaire pour trouver facilement, via un moteur de recherche sur Internet, des informations sur les messages enregistrées dans les traces. C'est aussi nécessaire quand on veut de l'aide des développeurs.

6. Récapitulatif

Le tableau Tab.1 présente les quelques paramètres discutés ci-dessus d'une façon plus directe.

Type

Paramètre

Nouvelle valeur

Commentaires

Connexions

listen_addresses

« * »

autorise les connexions par toutes les interfaces réseau du serveur

ssl

on

autorise l'utilisation de connexions chiffrées avec SSL

max_connections

100

ne jamais dépasser 1000

Mémoire partagée

shared_buffers

¼ de la RAM

pour un serveur dédié

wal_buffers

8MB


Mémoire par processus

work_mem

10MB

ne pas dépasser 100 Mo

maintenance_work_mem

au maximum 1GB

pour un serveur dédié ayant au moins 4 Go

Journaux de transactions

checkpoint_segments

10


checkpoint_completion_target

0.9


Planificateur

effective_cache_size

2/3 de la RAM

pour un serveur dédié

random_page_cost

entre 2 et 4

2 pour les disques rapides, 4 pour les disques lents

Traces

log_destination

syslog


lc_messages

« C »


Tab.1 : Les paramètres essentiels à configurer

Conclusion

Ce très court article n'est là que pour indiquer les quelques paramètres essentiels à une bonne configuration d'un serveur PostgreSQL. Il sera toujours possible dans un second temps d'affiner le paramétrage, notamment pour les paramètres non présentés dans cet article.


Par le même auteur

Évolution de PostgreSQL en version 11

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
101
|
Mois de parution
mars 2019
|
Domaines
Résumé
La version 11 de PostgreSQL est sortie le 18 octobre. Une première version corrective est déjà sortie et la seconde est prévue pour le 14 février 2019. Cette nouvelle version est principalement une version 10 améliorée : rien de bien révolutionnaire, mais de nouvelles fonctionnalités qui aident bien.

Performances et supervision avec PostgreSQL en version 10

Magazine
Marque
GNU/Linux Magazine
Numéro
220
|
Mois de parution
novembre 2018
|
Domaines
Résumé
Après avoir présenté les nouveautés majeures de la version 10, il nous reste à nous pencher sur les performances et la supervision. Cette version apporte beaucoup d’améliorations sur ces deux sujets. Les nouveautés en termes de supervision raviront les administrateurs cherchant à mieux comprendre le comportement de PostgreSQL. Quant aux améliorations de performance, elles sont nombreuses, la plus attendue étant un meilleur support de la parallélisation. Deux excellentes raisons pour installer cette version 10… avant de passer à la version 11 !

La version 10 de PostgreSQL et la réplication

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
99
|
Mois de parution
novembre 2018
|
Domaines
Résumé
Depuis la version 9.0, les développeurs de PostgreSQL améliorent sans cesse la réplication interne. Cette version ne fait pas exception, et continue à proposer de nouvelles options sur la réplication physique. Elle ajoute aussi un type de réplication attendu depuis longtemps, la réplication logique.

PostgreSQL 10, quelques nouvelles fonctionnalités

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
97
|
Mois de parution
juillet 2018
|
Domaines
Résumé
La version 10 de PostgreSQL est sortie depuis quelques mois, il est temps de faire un tour des nouvelles fonctionnalités et des changements majeurs. Ce premier article va discuter de différents points particulièrement intéressants, mais les trois grosses nouveautés, à savoir le partitionnement, la réplication logique et les améliorations au niveau performance et supervision seront abordées chacune dans leur propre article.

Partitionnement avec PostgreSQL 10

Magazine
Marque
GNU/Linux Magazine
Numéro
217
|
Mois de parution
juillet 2018
|
Domaines
Résumé
Le partitionnement dans PostgreSQL a toujours été un contournement d’autres fonctionnalités pour arriver à une séparation plus ou moins invisible, du point de vue de l’application, des données d’une table sur plusieurs tables. Il y eut de nombreuses tentatives au fil des ans pour améliorer cela, mais il y eut autant d’échecs... sauf avec cette version 10 qui réussit haut la main ce challenge. Cet article explique les différentes améliorations liées au partitionnement en version 10. On y voit aussi que certaines limitations restent présentes.

PostgreSQL 9.6 : les fonctionnalités moteurs

Magazine
Marque
GNU/Linux Magazine
Numéro
201
|
Mois de parution
février 2017
|
Domaines
Résumé
Dans un précédent article (voir GNU/Linux Magazine n°198), nous avons pu voir la nouveauté phare de la version 9.6 de PostgreSQL. Mais il ne s’agit pas de la seule amélioration de cette nouvelle version. Les développeurs ont passé aussi beaucoup de temps sur l’API de gestion des tables distantes, sur les possibilités offertes par la réplication physique, et sur plusieurs optimisations du moteur. Ils ont aussi travaillé sur différents aspects de l’administration comme la sauvegarde et la sécurité.