Rapide configuration de PostgreSQL

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.

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.