Rapide configuration de PostgreSQL

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
44
Mois de parution
octobre 2009
Spécialité(s)


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.

 



Article rédigé par

Par le(s) même(s) auteur(s)

Analyse des traces de PostgreSQL avec pgBadger

Magazine
Marque
Linux Pratique
HS n°
Numéro
54
Mois de parution
juin 2022
Spécialité(s)
Résumé

Comme toute application serveur, PostgreSQL enregistre des traces sur les erreurs rencontrées, ainsi que sur son activité. Même si elles sont très compréhensibles, une grosse quantité de traces peut nous perdre dans leur masse. Il est préférable dans ce cas de s’aider d’un outil. Parmi ces outils se trouve pgBadger.

Découvrez comment tirer parti des dernières nouveautés de PostgreSQL

Magazine
Marque
Linux Pratique
Numéro
130
Mois de parution
mars 2022
Spécialité(s)
Résumé

Depuis la version 10, toutes les versions qui ont suivi se sont contentées de petites améliorations de la version précédente. Il n'y a pas eu de fonctionnalités majeures ajoutées, et c'est plutôt une bonne chose. Cela permet de stabiliser le code, et d'améliorer, tranquillement, par petits ajouts, les fonctionnalités déjà présentes.

Les derniers articles Premiums

Les derniers articles Premium

Stubby : protection de votre vie privée via le chiffrement des requêtes DNS

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Depuis les révélations d’Edward Snowden sur l’espionnage de masse des communications sur Internet par la NSA, un effort massif a été fait pour protéger la vie en ligne des internautes. Cet effort s’est principalement concentré sur les outils de communication avec la généralisation de l’usage du chiffrement sur le web (désormais, plus de 90 % des échanges se font en HTTPS) et l’adoption en masse des messageries utilisant des protocoles de chiffrement de bout en bout. Cependant, toutes ces communications, bien que chiffrées, utilisent un protocole qui, lui, n’est pas chiffré par défaut, loin de là : le DNS. Voyons ensemble quels sont les risques que cela induit pour les internautes et comment nous pouvons améliorer la situation.

Surveillez la consommation énergétique de votre code

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Être en mesure de surveiller la consommation énergétique de nos applications est une idée attrayante, qui n'est que trop souvent mise à la marge aujourd'hui. C'est d'ailleurs paradoxal, quand on pense que de plus en plus de voitures permettent de connaître la consommation instantanée et la consommation moyenne du véhicule, mais que nos chers ordinateurs, fleurons de la technologie, ne le permettent pas pour nos applications... Mais c'est aussi une tendance qui s'affirme petit à petit et à laquelle à terme, il devrait être difficile d'échapper. Car même si ce n'est qu'un effet de bord, elle nous amène à créer des programmes plus efficaces, qui sont également moins chers à exécuter.

Donnez une autre dimension à vos logs avec Vector

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Avoir des informations précises et détaillées sur ce qu’il se passe dans une infrastructure, et sur les applications qu'elle héberge est un enjeu critique pour votre business. Cependant, ça demande du temps, temps qu'on préfère parfois se réserver pour d'autres tâches jugées plus prioritaires. Mais qu'un système plante, qu'une application perde les pédales ou qu'une faille de sécurité soit découverte et c'est la panique à bord ! Alors je vous le demande, qui voudrait rester aveugle quand l'observabilité a tout à vous offrir ?

Les listes de lecture

9 article(s) - ajoutée le 01/07/2020
Vous désirez apprendre le langage Python, mais ne savez pas trop par où commencer ? Cette liste de lecture vous permettra de faire vos premiers pas en découvrant l'écosystème de Python et en écrivant de petits scripts.
11 article(s) - ajoutée le 01/07/2020
La base de tout programme effectuant une tâche un tant soit peu complexe est un algorithme, une méthode permettant de manipuler des données pour obtenir un résultat attendu. Dans cette liste, vous pourrez découvrir quelques spécimens d'algorithmes.
10 article(s) - ajoutée le 01/07/2020
À quoi bon se targuer de posséder des pétaoctets de données si l'on est incapable d'analyser ces dernières ? Cette liste vous aidera à "faire parler" vos données.
Voir les 115 listes de lecture

Abonnez-vous maintenant

et profitez de tous les contenus en illimité

Je découvre les offres

Déjà abonné ? Connectez-vous