Maintenant que nous avons installé un serveur PostgreSQL [1], il est essentiel de se pencher sur sa configuration. Nous n’allons pas lister tous les paramètres, uniquement les plus importants. Cela touchera à peu près toutes les thématiques du serveur.
PostgreSQL dispose de plusieurs fichiers de configuration. Le fichier postgresql.conf permet de configurer le moteur de bases de données. Il est secondé par le fichier postgresql.auto.conf. Ce dernier est rempli via l’instruction SQL ALTER SYSTEM SET et vidé par l’instruction ALTER SYSTEM RESET. Le fichier postgresql.auto.conf est lu après le fichier postgresql.conf. Les paramètres qu’il contient surchargeront les mêmes paramètres du fichier postgresql.conf.
Les fichiers pg_hba.conf et pg_ident.conf permettent de configurer les droits d’accès aux bases, avec notamment la méthode d’authentification.
Ces quatre fichiers feront l’objet des deux premières parties. Une troisième partie abordera les extensions indispensables à installer.
1. Configuration du moteur de bases de données
1.1 Connexions
PostgreSQL accepte des connexions via deux types de sockets : la socket IP et la socket Unix (y compris sous Windows maintenant).
Pour les connexions IP, PostgreSQL écoute sur toutes les interfaces indiquées dans le paramètre listen_addresses. Par défaut, à localhost pour éviter toute connexion externe dès l’installation, il comprendra soit une liste des adresses IP correspondant aux interfaces réseau du serveur soit au caractère * remplaçant plus simplement la liste complète des interfaces lors du démarrage du service PostgreSQL.
Le port est utilisé pour les deux types de sockets. En effet, dans le cas de la socket Unix, le port est utilisé dans le nommage du fichier socket. Sa valeur par défaut est 5432 (tout du moins si elle n’a pas été modifiée avant la compilation du serveur). Il est nécessaire de la changer si plus d’une instance est installée sur un serveur physique ou virtuel. En effet, plusieurs processus ne peuvent pas écouter sur le même port.
Le paramètre ssl est un booléen indiquant si nous souhaitons activer l’utilisation du SSL pour chiffrer la communication entre le serveur et le client. Sa valeur par défaut est off, car son utilisation a un coût : en effet, cela consomme du temps processeur. Son activation ne force pas l’utilisation de SSL.
PostgreSQL est un système multiprocessus. À chaque demande de connexion, le processus postmaster lance un processus fils pour gérer la demande de connexion. Si l’authentification échoue, le processus quitte. Si, par contre, elle réussit, le processus se met en attente d’une requête du client qui vient de se connecter. Chaque fois que le processus reçoit une requête du client, il l’exécute, renvoie le résultat au client et se met en attente de la prochaine requête à exécuter pour ce client. Le processus ne s’arrête que lorsque le client se déconnecte. Pour éviter que trop de processus ne soient exécutés, un paramètre permet de configurer le nombre maximum de connexions possibles à un instant t. Ce paramètre s’appelle max_connections et vaut 100 par défaut. Il est possible de l’augmenter, mais il convient d’être raisonnable. En effet, plus vous acceptez de connexions en parallèle, plus le nombre de CPU devra être important et plus la consommation mémoire risque d’être forte.
Pour s’assurer que les administrateurs soient toujours capables de se connecter, un petit nombre de slots de connexions leur est réservé. Ce nombre se configure avec le paramètre superuser_reserved_connections, qui vaut 3 par défaut. Donc seuls 97 utilisateurs « standards » peuvent être connectés en même temps.
Pour résumer, une configuration habituelle de ces paramètres serait :
1.2 Mémoire
PostgreSQL étant multiprocessus, il utilise deux types de mémoires : la mémoire privée pour certains travaux spécifiques à un processus et la mémoire partagée pour communiquer avec les autres processus, mais aussi, et surtout, pour maintenir un cache disque.
La volumétrie de ce cache disque des fichiers de données dépend du paramètre shared_buffers. Sa valeur initiale est de 128 Mo, et la coutume revient à le configurer par défaut à un quart de la mémoire vive du serveur pour un serveur dédié à une instance. Si d’autres applications ou d’autres instances sont utilisées sur ce serveur, il faut soustraire leur utilisation de mémoire de la mémoire totale avant de diviser le résultat par quatre. Ce cache disque constitue une grande part de la mémoire partagée statique demandée par PostgreSQL. Les autres morceaux de mémoire partagée sont difficilement configurables et, de toute façon, constituent une volumétrie insignifiante.
Il existe un autre cache disque, pour les journaux de transactions cette fois. Le but de ce cache est d’y mettre quelques journaux de transactions, en tout cas au minimum le journal actif à un instant t. De ce fait, ce cache est d’une volumétrie beaucoup moins importante que celui des fichiers de données, étant donné qu’un journal de transaction fait seulement 16 Mo. Le paramètre associé, wal_buffers, vaut par défaut -1. Ainsi la volumétrie du cache est calculée automatiquement au démarrage de PostgreSQL, avec une valeur maximale de 16 Mo. Dans les systèmes subissant de très nombreuses écritures, il est fréquent de le configurer à 32 ou 64 Mo.
La consommation en mémoire privée est configurable via trois paramètres. Le paramètre work_mem est le plus souvent utilisé. Ce paramètre est utilisé pour réaliser des opérations de tri et de hachage en mémoire. Pour le tri, pensez aux clauses ORDER BY, DISTINCT, GROUP BY, mais aussi à l’algorithme de jointure Merge Join. Pour le hachage, cela concerne l’algorithme de jointure Hash Join ainsi que certains GROUP BY. Chaque opération de tri et de hachage peut allouer au maximum cette quantité de mémoire représentée par le paramètre work_mem. Cette allocation partielle ou complète se fait lors de l’exécution d’une requête, la mémoire étant rendue dès la fin de l’exécution. Si jamais l’exécution du tri nécessite plus de mémoire, le tri se fera alors sur disque. Dans le cas du hachage, il n’est malheureusement pas possible de passer par des fichiers temporaires, et nous aboutirons alors à une consommation de mémoire bien plus importante que prévu.
Le hachage utilise plus de mémoire que le tri. Pour éviter d’avoir une valeur trop importante pour le paramètre work_mem tout en permettant aux hachages d’utiliser plus de mémoire, les développeurs de PostgreSQL ont ajouté le paramètre hash_mem_multiplier en version 13. La quantité de mémoire utilisable pour un hachage résultera de la multiplication de la valeur de ce paramètre avec la valeur du paramètre work_mem. Le paramètre hash_mem_multiplier vaut 1 par défaut en version 13 et 14, et 2 à partir de la version 15. Il y a peu de retours d’expérience sur l’utilisation de ce paramètre. Il convient donc de rester prudent.
La mémoire indiquée par ces deux paramètres est allouée uniquement si nécessaire, et désallouée une fois la requête exécutée. Cette mémoire est allouée pour chaque requête qui en a besoin, donc il est possible de voir cette mémoire allouée plusieurs fois en même temps, une fois pour chaque requête. La consommation de la mémoire peut donc augmenter fortement, d’autant plus qu’il y aura de requêtes exécutées en même temps.
Certaines opérations de maintenance disposent de leur propre paramètre de configuration pour la mémoire. Ce paramètre s’appelle maintenance_work_mem et vaut 64 Mo par défaut. Les opérations de maintenance concernées sont le nettoyage des tables (commande VACUUM), la création d’index (commandes CREATE INDEX et REINDEX) ainsi que l’ajout de clé étrangère (commande ALTER TABLE ... ADD FOREIGN KEY). Ce sont des opérations qui s’exécutent peu en parallèle et il est donc plus facile de leur donner plus de mémoire individuellement. On peut considérer qu’un maximum de cinq opérations de maintenance peut s’exécuter en parallèle. Il est donc assez aisé d’augmenter la valeur de ce paramètre pour atteindre entre 128 Mo et 1 Go. Dépasser 1 Go est possible quand le serveur dispose de beaucoup de mémoire, mais il faut savoir que la commande VACUUM utilisera toujours au maximum 1 Go.
Le paramètre autovacuum_work_mem permet de surcharger le paramètre maintenance_work_mem pour tous les VACUUM exécutés par l’autovacuum. Son but est de permettre une configuration mémoire plus importante pour l’autovacuum. Sa valeur par défaut est -1, ce qui fait qu’il récupère la valeur du paramètre maintenance_work_mem.
Dans l’article précédent, nous avions indiqué qu’il était intéressant d’utiliser les Huge Pages. Cela ne veut pas dire pour autant qu’il faut configurer le paramètre huge_pages à la valeur on. En effet, cette valeur est un peu risquée. À la valeur on, PostgreSQL forcera l’utilisation des Huge Pages. Si le système d’exploitation n’est pas configuré pour les utiliser, le démarrage de PostgreSQL échouera. La valeur try est plus intelligente dans le sens où PostgreSQL essaie de les utiliser, mais démarre quand même si le système refuse de le laisser les utiliser. Mieux vaut moins de performances que pas de service du tout.
Il existe un dernier cache disque, un peu particulier vu qu’il est privé au processus. Ce cache disque va contenir les blocs des tables et index temporaires (donc pour les objets déclarés avec la clause TEMPORARY). La volumétrie de ce cache se configure avec le paramètre temp_buffers, dont la valeur par défaut est de 4 Mo. Si les applications qui utilisent l’instance ne font pas usage d’objets temporaires, il n’est pas nécessaire de modifier la valeur de ce paramètre. Si, par contre, elles en font usage, il pourrait être intéressant de chercher à connaître la taille de ces objets temporaires pour configurer au mieux ce paramètre.
Le paramètre max_prepared_transactions précise le nombre de transactions préparées concurrentes à un instant t. Nous parlons bien ici de transactions préparées et non pas de requêtes préparées. Cela doit concerner 0,1 % des utilisateurs de PostgreSQL et encore, je pense que je surestime. En tout cas, si vous ne savez pas ce que c’est, ce paramètre doit rester à la valeur 0. Mettre une valeur positive peut apporter des problèmes assez gênants (comme des verrous qui ne disparaissent pas après redémarrage).
Pour résumer, une configuration habituelle de ces paramètres serait :
1.3 Parallélisation
Certaines actions exécutées pour une requête peuvent être parallélisées. Par exemple, la lecture d’une table ou d’un index, le calcul d’un agrégat, la réalisation d’un tri sont des opérations parallélisables. Le niveau de parallélisation dépend du paramètre max_parallel_workers_per_gather qui vaut par défaut 2. Sur un serveur disposant de nombreux CPU et exécutant des requêtes complexes, il peut être intéressant d’augmenter la valeur de ce paramètre. Il faudra bien sûr le faire en adéquation avec le nombre de CPU. Il faudra aussi faire attention au nombre maximum de processus de parallélisation. Ce dernier est configuré avec le paramètre max_parallel_workers, dont la valeur par défaut (8) pourra être augmentée si le nombre de CPU est plus important.
La parallélisation des entrées/sorties disques est aussi prise en compte par PostgreSQL. Le paramètre effective_io_concurrency permet d’indiquer le nombre de disques disponibles sur un système RAID. Ce paramètre est aussi configurable par tablespace. Si le répertoire de données de PostgreSQL se trouve stocké sur un (ou plusieurs) disque SSD, ce paramètre pourra être configuré à une valeur de 100, voire plus.
Pour résumer, une configuration habituelle de ces paramètres serait :
1.4 Checkpoints
Pour éviter de déclencher trop de checkpoints, notamment pour les systèmes recevant beaucoup d’écritures, il est intéressant de revoir à la hausse les paramètres checkpoint_timeout et max_wal_size. Le premier indique la durée maximale avant l’exécution d’un checkpoint. Le deuxième indique la quantité d’écriture dans les journaux de transactions avant d’exécuter un checkpoint. Il permet ainsi de prendre en compte des périodes d’écritures massives. Il est important que les checkpoints soient déclenchés après expiration du délai. Il faut donc tout d’abord configurer le paramètre checkpoint_timeout à une durée raisonnable (ni trop basse pour éviter trop de checkpoints, ni trop haute pour que le rejeu après un crash ne soit pas trop long). Une fois ce paramètre configuré, il faudra configurer max_wal_size à une valeur assez haute pour qu’il ne déclenche pratiquement pas de checkpoints.
Le paramètre checkpoint_completion_target indique le ratio de durée d’écritures des données entre deux checkpoints. Sa valeur par défaut était 0,5 (soit 50%) avant la version 15. On la configurait systématiquement à 0,9. Les développeurs ont fini par nous donner raison en changeant la valeur par défaut à 0,9 en version 15.
Pour résumer, une configuration habituelle de ces paramètres serait :
1.5 Optimisation des requêtes
Sur ce thème, il existe deux paramètres essentiels à connaître.
Le paramètre random_page_cost indique le coût d’accès à une page d’un index. Ce coût est généralement plus important que le coût d’accès à une page d’une table du fait de la façon dont ces deux types d’objets sont stockés sur disque. Une table est lue séquentiellement : le bloc 1 puis le bloc 2 puis le bloc 3, etc. Un index est lu non séquentiellement : le bloc 1 va permettre de savoir où se trouve la racine de l’arbre, ce qui occasionnera la lecture du bloc A après un déplacement de la tête de lecture, puis on ira chercher la branche se trouvant sur le bloc B, ce qui occasionnera de nouveau un déplacement de la tête de lecture, etc. Or, ce qui prend du temps lors d’une lecture sur un disque, c’est justement de déplacer la tête de lecture. Il est donc raisonnable d’avoir le coût d’une lecture de table plus faible que le coût d’une lecture d’index (évidemment, quand on compare la même quantité d’informations lues). Cependant, dans les deux cas, il est aussi rapide de lire une table et un index : quand la base est entièrement en mémoire (pas de tête de lecture en mémoire), et quand la base est stockée sur des disques SSD ou RAM (là aussi, pas de tête de lecture).
Donc dans ces deux cas, il est possible de rapprocher très fortement la valeur du coût d’accès d’un index (paramètre random_page_cost, par défaut à 4) de celui du coût d’accès d’une table (paramètre seq_page_cost, par défaut à 1). Donc par exemple, si vous utilisez du SSD pour le stockage de la base (ce qui est de plus en plus fréquent), vous pouvez configurer random_page_cost à 1,2 ou 1,1.
Il existe bien d’autres paramètres de coût. Je ne connais et n’ai lu aucune explication rationnelle pour configurer ces paramètres. Il est donc préférable de rester prudent sur leur configuration et de tester fortement toute modification.
L’autre paramètre essentiel est effective_cache_size. PostgreSQL ne sait rien de la configuration matérielle du serveur. Ce paramètre permet de lui indiquer qu’il y a de la mémoire en dehors de la mémoire partagée, et notamment du shared_buffers. Ce paramètre doit normalement correspondre à l’addition de la taille de son cache disque (donc de la valeur du paramètre shared_buffers) et de celui du système d’exploitation. Une approximation habituelle est de prendre les deux tiers de la mémoire. Attention, PostgreSQL n’alloue pas cette mémoire, c’est uniquement pour qu’il estime la probabilité d’avoir des objets volumineux en mémoire.
Si l’application qui utilise une base de données de cette instance passe par un ORM (style Hibernate) pour accéder à PostgreSQL, alors il est essentiel de configurer le paramètre cursor_tuple_fraction à 1. En effet, les ORM utilisent très souvent les curseurs, quelle que soit la requête. Il est important que le planificateur soit au courant pour qu’il optimise au mieux l’exécution des requêtes.
Pour résumer, une configuration habituelle de ces paramètres serait :
1.6 Traces de PostgreSQL
Il est essentiel de bien configurer les traces de PostgreSQL tout de suite après son installation. C’est le gros vecteur d’informations, que ce soit suite à un crash ou pour connaître l’activité du serveur ou encore pour s’assurer de bonnes performances. Par défaut, un serveur PostgreSQL va tracer toute erreur qui survient. Par contre, les traces sur l’activité normale sont réduites au minimum par défaut. Il est donc important d’activer ces traces complémentaires.
Je n’aborderais pas la question de la destination des traces. Que vous choisissiez la sortie des erreurs (au format texte, CSV ou JSON), le démon syslog ou le journal des événements, cela n’a guère d’importance. Néanmoins, si vous choisissez la sortie des erreurs, et que vous laissez PostgreSQL gérer lui-même la rotation des fichiers de trace, avoir un fichier par jour est une bonne chose, mais faites bien attention à ne pas avoir de très gros fichiers, qui seraient difficiles à visualiser ou à rechercher dans un éditeur de texte. Le paramètre log_rotation_size devrait avoir comme valeur maximale 1 Go. Au-delà, c’est se compliquer la vie. Toujours dans le cas de la sortie des erreurs, n’oubliez pas de configurer l’outil logrotate pour supprimer et/ou compresser les fichiers très anciens. Le plus simple actuellement est d’utiliser la sortie des erreurs, au format texte, et le processus de gestion des traces de PostgreSQL. C’est ce que propose la configuration de fin de chapitre.
Plusieurs paramètres permettent d’activer la trace de certaines activités :
- log_autovacuum_min_duration pour tracer l’activité des autovacuum workers ;
- log_checkpoints pour tracer les checkpoints ;
- log_connections pour tracer les connexions ;
- log_disconnections pour tracer les déconnexions ;
- log_lock_waits pour tracer les verrous en attente depuis plus d’une seconde (d’après la configuration du paramètre deadlock_timeout) ;
- log_temp_files pour tracer les créations de fichiers temporaires.
Toutes ces traces doivent normalement pouvoir être activées en permanence sans risque.
Quand les traces sont envoyées sur la sortie des erreurs, un préfixe par défaut est ajouté, suivant la configuration du paramètre log_line_prefix. Il est possible de le configurer avec différentes informations, mais il est important que cette configuration permette l’utilisation de pgbadger (https://pgbadger.darold.net/), outil d’analyse automatique des traces. Une configuration basique serait :
À propos de cet outil (et de tous les outils d’analyse automatique des traces), ne pas oublier de configurer les traces en anglais. En effet, ces outils d’analyse automatique comme pgBadger ne comprennent que l’anglais. Pour que les traces soient enregistrées en anglais, vérifier que le paramètre lc_messages est bien configuré à C ou en_US.UTF8.
Pour résumer, une configuration habituelle de ces paramètres serait :
1.7 Statistiques d’activité
Les statistiques d’activité sont essentielles à la bonne maintenance d’une instance PostgreSQL, car elles permettent la supervision de cette instance. Certaines métriques de ces activités sont enregistrées par défaut, mais d’autres, ajoutées plus récemment, ne le sont pas.
La vue pg_stat_activity indique la requête en cours d’exécution (ou, suivant le cas, la dernière exécutée). Cependant, cette requête est tronquée automatiquement si elle fait plus d’un certain nombre d’octets. Ce nombre est configuré avec le paramètre track_activity_query_size, qui vaut par défaut 1024. 1 Ko pour une requête, ça peut paraître gros, mais c’est en fait très petit. De plus, la mémoire que cela prend reste très faible. Nous pouvons donc facilement multiplier par dix la valeur par défaut. Cela ne nous permettra pas d’avoir toutes les requêtes entières, mais la majorité devrait être disponible.
Toujours dans cette vue se trouve une colonne queryid. Cette colonne est l’identifiant de la requête. Cependant, cet identifiant n’est pas calculé par défaut. Pour cela, il faut configurer le paramètre compute_query_id à la valeur on ou installer l’extension pg_stat_statements (cette dernière étant fortement conseillée, nous en parlerons d’ailleurs dans la dernière partie de cet article).
Chronométrer les opérations disques est un plus intéressant. Pour cela, il faut configurer les paramètres track_io_timing et track_wal_io_timing à on. Attention, suivant les systèmes d’exploitation, ce chronométrage peut prendre du temps. Un moyen de le tester sans risque est d’utiliser l’outil pg_test_timing.
Si l’application utilise des procédures stockées ou des fonctions, il est intéressant de configurer à on le paramètre track_functions. Cela permettra de savoir quelle fonction est utilisée, combien de fois elle l’a été et sa durée totale. Il sera ainsi possible de connaître les fonctions dont l’exécution est la plus longue.
Pour résumer, une configuration habituelle de ces paramètres serait :
1.8 Et pour le reste
Le but de cet article n’est pas de détailler tous les paramètres, mais uniquement les essentiels.
Un paramètre dont nous n’avons pas encore parlé est cluster_name. Il est clairement optionnel, mais il se révèle une bonne pratique, surtout si plusieurs instances de PostgreSQL sont installées sur le même serveur. Le texte indiqué comme valeur de ce paramètre est ajouté au nom des processus d’une même instance, permettant ainsi d’identifier immédiatement à quelle instance appartient tel processus.
Deux autres paramètres dont nous n’avons pas parlé, et pour cause... il ne faut pas modifier leur valeur. Il s’agit de fsync et autovacuum. Leur désactivation est possible, et sert dans de très rares cas. Ma vision de consultant est assez simple : s’ils sont désactivés, dans 99 % des cas, c’est soit une erreur, soit un oubli.
Enfin, certains paramètres sont spécifiques à des contextes que nous étudierons dans de prochains articles, comme par exemple l'archivage, la réplication, la restauration.
1.9 Détails sur la configuration
Il existe différents moyens de faire cette configuration : en modifiant les paramètres dans le fichier postgresql.conf, en ajoutant les paramètres dans un fichier à part et en ajoutant une simple directive include dans le fichier principal, en utilisant plusieurs fichiers tous placés dans un même répertoire, voire même en utilisant l’ordre SQL ALTER SYSTEM.
Ma préférence (très personnelle) va nettement au fichier supplémentaire. Une concaténation des configurations proposées dans cette première partie est enregistrée dans le fichier linuxpratique.conf. Dans le fichier postgresql.conf, il faut ajouter la directive suivante :
Elle permet, comme son nom l’indique, de charger notre fichier de configuration.
Il ne reste plus qu’à redémarrer PostgreSQL pour que nos modifications soient prises en compte. Il est à noter que, parfois, un simple rechargement suffit, mais vu le nombre de paramètres modifiés, un redémarrage est préférable.
2. Configuration des accès
Pour les accès, deux fichiers se partagent la configuration. Le premier, pg_hba.conf, ressemble beaucoup à la configuration d’un pare-feu. Nous y indiquons qui a le droit de se connecter où, et à partir de quels adresses IP. Le second, pg_ident.conf, établit une correspondance entre les utilisateurs d’un système d’authentification comme LDAP et les utilisateurs de la base de données. Ce dernier est très peu utilisé. Nous n’en parlerons donc pas plus.
Le fichier pg_hba.conf sera vu lors des prochains articles. En effet, un système de supervision, un serveur de sauvegarde ou un serveur secondaire ont besoin de se connecter à une instance PostgreSQL. Et pour qu’ils puissent se connecter, il faut que le fichier pg_hba.conf soit configuré de façon à autoriser leurs connexions.
3. Extensions par défaut
Ce n’est pas à proprement parler de la configuration, mais néanmoins, il existe une extension qui devrait toujours être installée sur un serveur PostgreSQL : pg_stat_statements. Cette extension permet de connaître les requêtes SQL exécutées sur l’instance. Son activation se fait en l’ajoutant dans la liste des extensions à charger au démarrage. Pour cela, il faut modifier la configuration du paramètre shared_preload_libraries pour y ajouter pg_stat_statements. Plusieurs paramètres permettent ensuite de configurer finement cette extension. Le mieux est d’aller consulter la page descriptive de cette extension.
Une autre extension peut se révéler très intéressante. Il s’agit de pg_prewarm, outil de préchargement du cache disque de PostgreSQL. En le chargeant au démarrage, il peut restaurer le cache tel qu’il était à l’arrêt précédent, permettant ainsi de ne pas démarrer avec un cache froid.
La configuration des deux se fait ainsi :
dans le fichier linuxpratique.conf.
Ceci fait, il restera à redémarrer PostgreSQL.
Conclusion
Nous avons enfin un serveur PostgreSQL configuré. Nous allons maintenant pouvoir mettre en place des outils de sauvegarde et de supervision.
Référence
[1] https://connect.ed-diamond.com/linux-pratique/lp-139/installation-de-postgresql
