Comment tirer le meilleur parti des réglages mémoire de Postgres

• La base de données Postgres utilise une grande quantité de RAM. Pour produire un ensemble de résultats, elle passe par des étapes comme la correspondance d’index, la recherche des lignes pertinentes dans les tables, ainsi que la fusion, le filtrage, l’agrégation et le tri des tuples, et toutes ces étapes dépendent de la mémoire
• Pour optimiser l’utilisation mémoire de Postgres, il faut exploiter au maximum la RAM disponible tout en ajustant efficacement les différents types d’allocation mémoire, et éviter que l’OS ne mette fin aux processus à cause d’une consommation excessive

Sharing is Caring

• La plus grande partie de la RAM associée à Postgres est shared_buffers, qui représente les lignes des tables et index les plus fréquemment consultés. Cela repose sur une heuristique qui attribue un score selon la fréquence d’utilisation
  • shared_buffers est une valeur fixe allouée au démarrage de Postgres et ne contribue pas aux problèmes mémoire imprévus
  • La valeur par défaut est de 128MB
  • Mais l’OS peut ne pas le considérer comme de la mémoire préallouée, donc définir une valeur très élevée, jusqu’à la quantité totale de RAM de l’instance, peut être risqué
• Sur les systèmes de production, la recommandation la plus courante pour shared_buffers est de 25% de la RAM disponible. C’est un bon point de départ pour la plupart des systèmes, à ajuster selon le matériel
  • Les benchmarks montrent que la recommandation des 25% est généralement suffisante, mais cela peut varier selon l’usage de la base de données
  • Par exemple, les systèmes de reporting peuvent avoir un faible taux de hit du cache à cause de requêtes ad hoc complexes, et offrir de légèrement meilleures performances avec un réglage plus bas
• L’extension pg_buffercache permet de savoir précisément quelles tables et quels index sont présents dans les buffers partagés. En vérifiant le nombre de pages utilisées dans le buffer, on peut ajuster la valeur de shared_buffers
  • Si le cache de buffers n’est pas utilisé à 100%, le réglage est peut-être trop élevé, et on peut réduire la taille de l’instance ou la valeur de shared_buffers
  • S’il est à 100% et que seule une partie de nombreuses tables est mise en cache, il peut être intéressant d’augmenter progressivement la valeur jusqu’à observer des rendements décroissants
• La nouvelle vue pg_stat_io de Postgres 16 peut aussi aider à ajuster shared_buffers. Elle permet de voir le taux de hit ainsi que les lectures/écritures des backends clients
  • Si le ratio lecture/écriture se rapproche de 1, cela peut indiquer que Postgres fait continuellement tourner les mêmes pages dans shared_buffers. Augmenter shared_buffers peut aider à réduire ce thrashing
• Si l’on commence à dépasser 50% de la RAM système, il faut envisager d’augmenter la taille de l’instance, car Postgres a toujours besoin de mémoire pour les sessions utilisateur et les requêtes associées

Working Memory

• L’autre grande part de la mémoire utilisée par Postgres pour faire réellement le travail correspond à la mémoire de travail, contrôlée par le paramètre work_mem
  • La valeur par défaut est de 4MB, et c’est souvent l’un des premiers réglages que l’on modifie pour accélérer l’exécution des requêtes
  • Mais si l’OS tue Postgres à cause de messages de type « mémoire insuffisante », on pourrait être tenté d’augmenter work_mem, alors que cela ne ferait qu’aggraver le problème. Cela augmente la quantité de RAM consommée par Postgres et rend ces arrêts encore plus probables
• Beaucoup interprètent la « mémoire de travail » comme une allocation unique dédiée à l’ensemble des opérations qu’effectue Postgres pendant une requête, alors qu’en réalité cela peut être bien plus
  • Chaque étape (nœud) reçoit sa propre instance de work_mem. Par exemple, avec la valeur par défaut de 4MB, une requête nécessitant 4 nœuds peut consommer jusqu’à 16MB de RAM
  • Sur un serveur chargé, si 100 requêtes de ce type s’exécutent en même temps, le calcul des résultats peut à lui seul utiliser jusqu’à 1.6GB de RAM. Des requêtes plus complexes peuvent demander encore plus de RAM selon le nombre de nœuds nécessaires à l’exécution
• En utilisant la commande EXPLAIN pour examiner le plan d’exécution d’une requête, on peut voir comment Postgres l’exécute et tous les nœuds nécessaires pour produire la sortie
  • En combinaison avec l’extension pg_stat_statements, cela permet d’isoler les requêtes les plus actives et d’estimer l’utilisation mémoire totale liée à work_mem
• Si work_mem est réglé trop bas, les lignes ou résultats intermédiaires qui ne tiennent pas en RAM débordent sur le disque, ce qui est nettement plus lent
  • On peut consulter la vue pg_stat_database pour voir la taille cumulée et le nombre de tous les fichiers temporaires écrits sur disque ; si la taille moyenne est raisonnable, on peut augmenter work_mem de cette quantité
• Pour estimer grossièrement la quantité de RAM disponible par session, on peut utiliser la formule suivante : (80% de la RAM totale - shared_buffers) / (max_connections)
  • Par exemple, avec 16GB de RAM, 4GB de buffers partagés et 100 connexions maximales, on dispose d’environ 88MB par session
  • En divisant ensuite cette valeur par le nombre moyen de nœuds du plan de requête, on obtient un bon réglage pour work_mem

Ongoing Maintenance

• La dernière composante ajustable de l’utilisation RAM de Postgres ressemble à la mémoire de travail, mais concerne spécifiquement la maintenance, avec un paramètre au nom proche : maintenance_work_mem
  • Sa valeur par défaut est de 64MB, et elle détermine la quantité de RAM dédiée à des opérations comme VACUUM, CREATE INDEX ou ALTER TABLE ADD FOREIGN KEY
• Comme elle est limitée à une tâche par session et qu’il est peu probable d’avoir de nombreuses tâches simultanées, il est généralement considéré comme assez sûr d’utiliser des valeurs plus élevées
  • Ces opérations de maintenance peuvent consommer énormément de mémoire et se terminent beaucoup plus vite si elles peuvent travailler entièrement en RAM ; il est donc très courant de régler cette valeur à 1GB ou 2GB
• Le point important à surveiller est le processus d’autovacuum de Postgres, qui marque les tuples morts pour permettre leur réutilisation ultérieure
  • L’autovacuum lance des tâches d’arrière-plan jusqu’à la limite autovacuum_max_workers, chacune pouvant utiliser une instance complète de maintenance_work_mem
  • Sur la plupart des serveurs avec suffisamment de RAM libre, 1GB de mémoire de maintenance reste sûr, mais s’il y a peu de RAM, il faut être plus prudent
  • Il existe notamment un paramètre distinct, autovacuum_work_mem, pour limiter spécifiquement les workers d’autovacuum
  • Les workers d’autovacuum de Postgres ne peuvent pas utiliser plus de 1GB, donc configurer autovacuum_work_mem au-delà de cette valeur n’a aucun effet

Session Pooling

• Le moyen le plus simple de réduire la consommation mémoire est d’imposer une limite logique aux allocations potentielles
  • Postgres fonctionne actuellement comme un moteur basé sur des processus, donc chaque session utilisateur reçoit un processus physique plutôt qu’un thread
  • Par conséquent, chaque connexion entraîne un certain surcoût en RAM et contribue aux changements de contexte
  • D’où la recommandation habituelle : régler max_connections à au plus 4 fois le nombre de threads CPU disponibles. Cela minimise le temps passé à basculer les sessions entre CPU et limite naturellement la quantité totale de RAM que les sessions peuvent consommer
• Si toutes les sessions exécutent des requêtes et que chaque nœud représente une allocation de work_mem, l’utilisation maximale théorique de la mémoire de travail est connections * nodes * work_mem
  • Il n’est pas toujours possible de réduire la complexité des requêtes, mais on peut en général réduire le nombre de connexions
  • Ce n’est pas toujours aussi simple lorsqu’une application ouvre en permanence un grand nombre de sessions, ou quand plusieurs microservices distincts dépendent de Postgres
• La formule work_mem * max_connections * 5 donne une estimation grossière de la quantité maximale de RAM que l’instance Postgres peut allouer aux sessions utilisateur pour traiter des requêtes de base, en supposant que toutes les connexions sont actives
  • Si le serveur n’a pas assez de RAM pour cette valeur, il faut envisager de réduire l’un des facteurs ou d’augmenter la RAM
  • L’estimation de 5 nœuds par requête en moyenne peut ne pas correspondre à votre application ; il faut donc l’ajuster selon vos besoins une fois que vous connaissez mieux vos plans d’exécution
• L’étape suivante consiste à introduire un pooler de connexions comme PgBouncer
  • Il découple les connexions clientes de la base de données et réutilise les sessions Postgres coûteuses entre plusieurs clients
  • Lorsqu’il est correctement configuré, des centaines de clients peuvent partager quelques dizaines de connexions Postgres sans impact sur l’application
  • Il a été observé que PgBouncer peut multiplexer plus de 1000 connexions en 40 à 50 connexions de cette manière, réduisant fortement la consommation mémoire globale liée au surcoût des processus

Reducing Bloat

• L’un des aspects les plus difficiles à suivre en matière d’utilisation mémoire est probablement le bloat des tables
  • Postgres utilise le contrôle de concurrence multiversion (MVCC) pour représenter les données dans son système de stockage
  • Autrement dit, chaque fois qu’une ligne de table est modifiée, Postgres crée quelque part dans la table une autre copie de cette ligne et l’étiquette avec un nouveau numéro de version
  • Le processus VACUUM de Postgres marque les anciennes versions de lignes comme espace « inutilisé » afin que de nouvelles versions puissent y être placées
• Postgres dispose d’un processus d’arrière-plan d’autovacuum qui cherche en continu ces allocations réutilisables afin d’éviter que les tables ne grossissent sans limite
  • Mais parfois, surtout sur les systèmes de grande taille, la configuration par défaut ne suffit pas et cette maintenance peut prendre du retard
  • Le résultat peut être des tables « gonflées » par d’anciennes données, avec plus de lignes mortes que de lignes vivantes
• Lorsqu’une table est fortement affectée par le bloat, il faut considérer son impact sur les buffers partagés
  • Si chaque page ne contient qu’une seule ligne active et plusieurs lignes mortes, alors une requête ayant besoin de 10 lignes devra faire entrer 10 pages dans les buffers partagés, gaspillant ainsi beaucoup de mémoire qui pourrait servir à autre chose
  • Si la demande sur ces lignes est particulièrement forte, leur fréquence d’accès les maintiendra dans les buffers partagés, ce qui dégrade fortement l’efficacité du cache
• Il existe beaucoup de requêtes qui circulent sur Internet pour estimer le bloat des tables, mais le seul moyen de voir concrètement à quoi ressemblent les pages d’une table est d’utiliser l’extension pgstattuple
• Si free_percent dépasse 30%, il peut être nécessaire de rendre l’autovacuum plus agressif. Si la valeur est largement supérieure à 30%, mieux vaut supprimer complètement le bloat
  • À l’heure actuelle, la seule méthode officiellement prise en charge pour cela est la commande VACUUM FULL, qui reconstruit essentiellement la table. Toutes les lignes actives sont déplacées vers un nouvel emplacement et l’ancienne copie gonflée est abandonnée
  • Ce processus applique un verrou d’accès exclusif pendant toute sa durée, ce qui implique presque toujours une forme d’indisponibilité
• Une alternative est l’extension pg_repack, prise en charge par Tembo
  • Cet outil en ligne de commande peut réorganiser les tables pour éliminer le bloat de façon entièrement en ligne, sans verrou exclusif
  • Comme cet outil existe en dehors du cœur de Postgres et modifie le stockage des tables et des index, son usage est souvent considéré comme avancé
  • Il est recommandé de bien le tester dans un environnement hors production avant utilisation
• On peut aller encore plus loin en réorganisant l’ordre des colonnes pour maximiser le nombre de lignes par page, une sorte de Tetris des colonnes
  • C’est probablement un niveau d’optimisation extrême, mais cela peut être une stratégie viable dans les environnements où l’on a la liberté de reconstruire les tables de cette façon

The Balancing Act

• Configurer correctement tous ces paramètres et ressources relève à la fois de l’art et de la science
  • Nous avons vu comment mesurer l’utilisation réelle des buffers partagés et comment vérifier si la mémoire de travail est réglée trop bas
  • Mais que faire si, comme c’est souvent le cas, le matériel disponible ou le budget est limité ? C’est là que la part d’« art » devient nécessaire
• En situation de manque de mémoire, il peut être nécessaire de réduire un peu shared_buffers pour libérer de la place à work_mem. Ou bien de réduire les deux
  • Si l’application a besoin d’un grand nombre de sessions, il peut être plus judicieux de réduire work_mem ou d’introduire un pool de connexions afin d’éviter que des sessions concurrentes n’accumulent de larges allocations de RAM
  • Si l’on avait auparavant augmenté maintenance_work_mem en supposant qu’il y aurait assez de RAM pour tout, il peut aussi être plus logique de le réduire. Il y a beaucoup de paramètres à prendre en compte
• Sur les instances à faible mémoire, même les recommandations ci-dessus peuvent ne pas suffire. Dans ce cas, il est recommandé de suivre l’ordre d’actions suivant pour maximiser l’usage mémoire tout en évitant l’épuisement des ressources :
  1. Ajouter un pooler de connexions et réduire max_connections. C’est le moyen le plus rapide et le plus simple de diminuer la consommation maximale de ressources
  2. Utiliser EXPLAIN sur les requêtes les plus fréquentes rapportées par pg_stat_statements afin de trouver le nombre maximal de nœuds des requêtes, et non la moyenne. Régler ensuite work_mem à une valeur inférieure ou égale à (80% de la RAM totale - shared_buffers) / (max_connections * nombre maximal de nœuds du plan)
  3. Ramener maintenance_work_mem et autovacuum_work_mem à leur valeur par défaut de 64MB. Si les tâches de maintenance deviennent trop lentes et qu’il est possible d’utiliser davantage de RAM, envisager des augmentations par paliers de 8MB
  4. Utiliser l’extension pg_buffercache pour examiner la quantité de tables stockées dans shared_buffers. Étudier attentivement chaque table et chaque index pour voir s’il est possible de réduire cela, par exemple via l’archivage de données ou la modification des requêtes pour consommer moins d’informations. Cela peut inclure VACUUM FULL ou pg_repack pour compacter les pages utilisées par des tables activement gonflées
  5. Si pg_buffercache montre que shared_buffers est plein et ne peut plus être réduit sans évincer des pages actives, utiliser la colonne usagecount pour prioriser les pages les plus actives. Comme cette colonne prend des valeurs de 1 à 5, se concentrer sur les pages utilisées 3 à 5 fois peut permettre de réduire shared_buffers sans impact majeur sur les performances
  6. Enfin, provisionner un matériel plus puissant. Si la base de données a besoin de plus de RAM pour la charge actuelle et que réduire les paramètres ci-dessus dégraderait trop fortement les performances du système, il est généralement plus logique de monter en gamme