Réglage des paramètres de postgresql.conf pour des performances optimales en lecture et écriture

PostgreSQL fonctionne généralement de manière acceptable avec les valeurs par défaut fournies, mais « acceptable » peut se transformer en lectures lentes, écritures irrégulières ou latence aléatoire une fois que le trafic réel arrive. Le fichier postgresql.conf est l'endroit où vous définissez le budget de ressources de base : la quantité de mémoire que PostgreSQL peut utiliser pour le cache partagé, la quantité que chaque opération de requête peut utiliser avant de déborder sur le disque, l'agressivité avec laquelle les points de contrôle écrivent les pages modifiées, et les indices que le planificateur reçoit sur la machine sous-jacente.

L'erreur que je vois le plus souvent est de traiter le réglage de PostgreSQL comme une liste de nombres magiques. Quelqu'un copie shared_buffers = 25% de la RAM, pousse work_mem à une valeur élevée, double max_connections, et espère que la base de données deviendra plus rapide. Parfois ça marche. Parfois, elle commence à swaper pendant un travail de rapport ou atteint un mur pendant les points de contrôle.

La façon la plus sûre est de régler à partir des symptômes. Les lectures sont-elles lentes parce que le jeu de travail n'est pas en cache ? Les rapports débordent-ils les tris sur le disque ? Les écritures s'accumulent-elles pendant les points de contrôle ? Trop de connexions d'application se disputent-elles la mémoire ? Ce guide parcourt les paramètres qui comptent généralement en premier, avec des exemples que vous pouvez adapter plutôt que copier aveuglément.

Comprendre les paramètres mémoire essentiels

Une gestion efficace de la mémoire est primordiale pour les systèmes de bases de données hautes performances. PostgreSQL utilise diverses zones mémoire, dont deux des plus critiques sont shared_buffers pour la mise en cache des données fréquemment consultées et work_mem pour les opérations internes des requêtes.

shared_buffers

shared_buffers est sans doute l'un des paramètres mémoire les plus importants à régler. Il définit la quantité de mémoire dédiée que PostgreSQL utilise pour mettre en cache les blocs de données. Ces blocs incluent les données des tables, les données des index et les catalogues système. Lorsqu'une requête demande des données, PostgreSQL vérifie d'abord shared_buffers. Si les données s'y trouvent (un hit de cache), elles sont récupérées beaucoup plus rapidement que si elles devaient être lues depuis le disque.

Impact sur les performances

• Performances en lecture : Une valeur plus élevée de shared_buffers augmente la probabilité de hits de cache, réduisant considérablement les E/S disque pour les charges de travail à forte lecture. Cela se traduit par des réponses aux requêtes plus rapides.
• Performances en écriture : shared_buffers contient également des pages « modifiées » (blocs de données qui ont été modifiés mais pas encore écrits sur le disque). Un tampon plus grand peut absorber plus d'écritures, permettant au système de les regrouper en moins d'écritures, mais plus volumineuses, sur le disque, améliorant ainsi le débit d'écriture. Cependant, s'il est trop grand, il peut entraîner des temps de point de contrôle plus longs et des pics d'E/S accrus pendant les points de contrôle.

Directives de réglage

• Point de départ : Une recommandation courante est de définir shared_buffers à 25% de votre RAM physique totale. Par exemple, sur un serveur avec 16 Go de RAM, shared_buffers serait de 4 Go.
• Systèmes avec grande RAM : Sur les serveurs avec 64 Go+ de RAM, allouer 25% peut être excessif. PostgreSQL s'appuie également sur le cache du système de fichiers du système d'exploitation. Au-delà d'un certain point, augmenter shared_buffers peut offrir des rendements décroissants car le cache du système d'exploitation peut gérer efficacement une grande partie du cache restant. Dans de tels cas, 15-20% peuvent être suffisants, laissant plus de RAM pour le cache du système d'exploitation ou work_mem.
• Surveillance : Gardez un œil sur le taux de hits de cache dans pg_stat_database, mais ne considérez pas un pourcentage comme une preuve que tout va bien. Un taux de hits élevé peut cacher quelques requêtes très coûteuses, et un taux plus faible peut être normal pour des travaux par lots qui analysent de grandes tables une seule fois. Surveillez également le comportement des points de contrôle et la latence du disque.

Exemple de configuration

Pour définir shared_buffers à 4 Go dans postgresql.conf :

shared_buffers = 4GB

Astuce : Après avoir modifié shared_buffers, vous devez redémarrer le service PostgreSQL pour que les modifications prennent effet.

Une vérification pratique après l'avoir modifié :

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

Si vous avez augmenté shared_buffers et que l'application attend toujours les lectures disque, le problème peut être la forme de la requête, les index manquants, le gonflement des tables, ou un jeu de travail plus grand que la mémoire. Plus de cache ne remplace pas un meilleur plan d'exécution.

work_mem

work_mem spécifie la quantité maximale de mémoire à utiliser par une opération de requête (comme un tri ou une table de hachage) avant d'écrire des données temporaires sur le disque. Cette mémoire est allouée par session, par opération. Si une requête complexe implique plusieurs opérations de tri ou de hachage, elle pourrait potentiellement consommer work_mem plusieurs fois au sein d'une même session.

Impact sur les performances

• Requêtes complexes : work_mem impacte significativement les requêtes impliquant ORDER BY, GROUP BY, DISTINCT, les jointures de hachage et la matérialisation. Lorsqu'une opération de tri ou de hachage dépasse la limite work_mem, PostgreSQL déborde les données excédentaires vers des fichiers disque temporaires, entraînant une exécution beaucoup plus lente.
• Concurrence : Étant donné que work_mem est alloué par opération et par session, une valeur globale élevée de work_mem combinée à de nombreuses requêtes complexes concurrentes peut rapidement épuiser la RAM disponible, entraînant du swapping et une dégradation sévère des performances.

Directives de réglage

• Évitez les valeurs globales excessives : Ne définissez pas aveuglément work_mem à une valeur très élevée globalement. Considérez plutôt la concurrence typique de votre application et l'empreinte mémoire de vos requêtes les plus gourmandes en ressources.
• Surveillance des débordements disque : Utilisez EXPLAIN ANALYZE sur les requêtes problématiques. Recherchez des lignes comme Sort Method: external merge Disk: NkB ou HashAggregate batches: N (disk) qui indiquent que work_mem était insuffisant et que les données ont été débordées sur le disque.
• Réglage ciblé : Pour des rapports spécifiques de longue durée ou des travaux par lots, envisagez de définir work_mem au niveau de la session avant d'exécuter la requête, plutôt que globalement. Cela permet une utilisation mémoire plus élevée pour cette requête spécifique sans impacter les autres sessions concurrentes.

Exemple de configuration

Pour définir work_mem à 16 Mo globalement dans postgresql.conf :

work_mem = 16MB

Pour définir work_mem pour une session spécifique (par exemple, dans psql ou une connexion d'application) :

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

Attention : Soyez prudent lorsque vous augmentez work_mem. Si 100 requêtes concurrentes ont chacune besoin de 1 Go de work_mem, cela représente 100 Go de RAM ! Testez toujours les modifications dans un environnement de staging et surveillez l'utilisation mémoire de votre système.

Une façon plus réaliste d'utiliser work_mem est de garder la valeur globale modeste, puis de l'augmenter uniquement pour les sessions de reporting connues :

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

Ce modèle est plus sûr que d'augmenter la valeur globale pour chaque requête web. Une application web avec de nombreuses requêtes courtes a besoin d'une utilisation mémoire prévisible. Un rapport nocturne peut se permettre un budget par requête plus important.

Gestion des performances d'écriture et de la durabilité avec les points de contrôle

Les points de contrôle sont un mécanisme essentiel dans PostgreSQL pour garantir la durabilité des données et gérer le journal des transactions (WAL - Write-Ahead Log). Ils synchronisent périodiquement les blocs de données modifiés de shared_buffers vers le disque, marquant le point jusqu'auquel toutes les modifications précédentes ont été écrites sur le stockage permanent.

checkpoint_timeout

checkpoint_timeout définit le temps maximum entre les points de contrôle WAL automatiques. Les points de contrôle se produisent également si la quantité de segments WAL générés depuis le dernier point de contrôle dépasse max_wal_size.

Impact sur les performances

• Points de contrôle fréquents (checkpoint_timeout court) : Conduit à des pics d'E/S plus fréquents lorsque les pages modifiées sont vidées sur le disque. Bien que cela réduise le temps de récupération après un crash (moins de WAL à rejouer), cela peut impacter négativement les performances de la charge de travail active en raison d'une activité d'écriture concentrée.
• Points de contrôle peu fréquents (checkpoint_timeout long) : Réduit la fréquence des pics d'E/S, conduisant à des performances plus fluides pendant le fonctionnement normal. Cependant, cela signifie que plus de données pourraient devoir être rejouées à partir du WAL en cas de crash, entraînant des temps de récupération de base de données plus longs. Cela nécessite également une valeur max_wal_size plus grande pour stocker les segments WAL accumulés.

Directives de réglage

• Équilibre : L'objectif est de trouver un équilibre entre des performances continues fluides et un temps de récupération acceptable. De nombreux systèmes de production commencent autour de 5 à 15 minutes, puis s'ajustent en fonction du volume WAL et des objectifs de récupération.
• Interaction avec max_wal_size : Ces deux paramètres fonctionnent ensemble. Si checkpoint_timeout est long mais que max_wal_size est trop petit, les points de contrôle seront déclenchés par max_wal_size plus fréquemment que par checkpoint_timeout. Ajustez max_wal_size pour qu'il soit suffisamment grand pour permettre à checkpoint_timeout d'être le déclencheur principal.
• Surveillance : Utilisez pg_stat_bgwriter pour observer les compteurs checkpoints_timed et checkpoints_req. checkpoints_timed devrait être significativement plus élevé que checkpoints_req (points de contrôle demandés en raison des limites de taille WAL) si votre checkpoint_timeout est le déclencheur principal.

Exemple de configuration

Pour définir checkpoint_timeout à 10 minutes dans postgresql.conf :

checkpoint_timeout = 10min
# Pensez également à ajuster max_wal_size en conséquence
max_wal_size = 4GB # Exemple, ajustez en fonction de la charge de travail

Bonne pratique : Visez à ce que les points de contrôle soient principalement déclenchés par checkpoint_timeout plutôt que par max_wal_size. Cela fournit des modèles d'E/S plus prévisibles. Si max_wal_size déclenche fréquemment des points de contrôle, augmentez sa valeur.

Vérifiez le modèle avec :

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

Si checkpoints_req augmente rapidement, PostgreSQL effectue des points de contrôle parce que le WAL a dépassé max_wal_size, pas parce que le minuteur a expiré. Cela se manifeste souvent par des rafales d'E/S en écriture. Augmenter max_wal_size peut lisser la charge de travail, mais peut également augmenter le temps de récupération après un crash car plus de WAL peut devoir être rejoué.

Paramètres du planificateur et du WAL qui méritent d'être vérifiés

Trois paramètres se situent souvent à côté des gros paramètres mémoire et de point de contrôle.

effective_cache_size n'est pas de la mémoire allouée par PostgreSQL. C'est une estimation du planificateur de la quantité de cache probablement disponible à travers les tampons partagés de PostgreSQL et le cache du système de fichiers du système d'exploitation. S'il est défini trop bas, le planificateur peut éviter les analyses d'index car il suppose que les lectures seront coûteuses. Sur un serveur de base de données dédié, un point de départ courant est une grande fraction de la RAM, mais la bonne valeur dépend de ce qui s'exécute d'autre sur l'hôte.

effective_cache_size = 12GB

maintenance_work_mem affecte les opérations de maintenance telles que CREATE INDEX, ALTER TABLE ADD FOREIGN KEY et VACUUM. Il n'est pas utilisé par les tris de requêtes normaux de la même manière que work_mem. Si les constructions d'index sont péniblement lentes pendant les fenêtres de maintenance, augmenter cette valeur pour la session peut aider :

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffers contrôle la mémoire utilisée pour les enregistrements WAL avant qu'ils ne soient écrits. La valeur par défaut est généralement correcte car PostgreSQL peut la dimensionner automatiquement, mais les charges de travail à forte écriture avec de grandes transactions peuvent bénéficier de la vérification si les écritures WAL sont un goulot d'étranglement avant de le modifier. Ne le réglez pas simplement parce qu'il apparaît dans une liste de contrôle.

Points de départ différents pour différentes charges de travail

Pour une application web OLTP, la priorité est une latence stable sous concurrence. Gardez work_mem conservateur, utilisez un pooler de connexions au lieu d'autoriser des milliers de connexions directes, et surveillez les attentes de verrouillage et les mauvais plans avant de blâmer shared_buffers. Un problème typique ressemble à ceci : une version ajoute une requête de tableau de bord avec ORDER BY created_at DESC sur des millions de lignes, la requête déborde sur le disque, et soudainement chaque requête est plus lente parce que la base de données fait des E/S de fichiers temporaires. La solution peut être un index ou une requête plus étroite, pas un work_mem global plus grand.

Pour une base de données d'analyse ou de reporting, les grands tris et les agrégats de hachage sont normaux. Vous pouvez augmenter work_mem pour les rôles de reporting, augmenter maintenance_work_mem pour le travail d'index en masse, et accepter des requêtes plus longues. Le risque est la concurrence. Dix analystes exécutant des rapports gourmands en mémoire en même temps peuvent consommer bien plus de mémoire qu'un seul test de requête réussi ne le suggérait.

Pour un système à forte écriture, les points de contrôle et le WAL comptent davantage. Si l'application a des blocages d'écriture périodiques, vérifiez s'ils coïncident avec les points de contrôle. Regardez également la latence du stockage, la saturation du disque WAL, l'activité d'autovacuum, et si les transactions longues empêchent le nettoyage. Augmenter checkpoint_timeout seul ne résoudra pas un disque qui ne peut pas suivre le volume d'écriture moyen.

Un flux de travail de réglage simple

Commencez par enregistrer la configuration actuelle :

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

Ensuite, capturez les symptômes avant de changer quoi que ce soit. Sauvegardez un ou deux plans de requêtes lents avec EXPLAIN (ANALYZE, BUFFERS). Vérifiez la journalisation des fichiers temporaires si vous soupçonnez des débordements :

log_temp_files = 0

Ce paramètre journalise chaque fichier temporaire, alors utilisez-le avec précaution sur un système occupé ou définissez-le sur un seuil comme 64MB. Si vous voyez beaucoup de gros fichiers temporaires provenant de la même forme de requête, réglez la requête, ajoutez un index, ou augmentez work_mem pour cette charge de travail.

Changez une chose à la fois. Certains paramètres nécessitent un redémarrage, d'autres seulement un rechargement, et certains peuvent être définis par session. PostgreSQL vous indique lequel est lequel :

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

Le contexte postmaster signifie redémarrage. sighup signifie rechargement. user signifie que les modifications au niveau de la session sont possibles.

Conseils de réglage généraux et bonnes pratiques

• Réglage itératif : Commencez par de petites modifications incrémentales. Changez un paramètre à la fois, observez l'impact, puis ajustez davantage si nécessaire. Le réglage n'est pas une tâche ponctuelle mais un processus continu.
• Surveillez tout : Utilisez les vues de statistiques intégrées de PostgreSQL (pg_stat_database, pg_stat_bgwriter, pg_stat_activity), les outils de surveillance au niveau du système d'exploitation (par exemple, iostat, vmstat, top), et des solutions de surveillance externes pour collecter des données sur le CPU, la mémoire, les E/S disque et les performances des requêtes.
• Comprenez votre charge de travail : Votre application est-elle à forte lecture ou à forte écriture ? Effectue-t-elle des requêtes analytiques complexes ou des opérations transactionnelles simples ? Adaptez votre configuration aux caractéristiques spécifiques de votre charge de travail.
• Considérez d'autres paramètres : Bien que shared_buffers, work_mem et checkpoint_timeout soient cruciaux, de nombreux autres paramètres peuvent impacter les performances. Par exemple, effective_cache_size (indices pour le planificateur de requêtes sur le cache OS disponible) et wal_buffers (mémoire pour les enregistrements WAL avant vidage) sont souvent réglés en parallèle de ceux-ci.
• Utilisez EXPLAIN ANALYZE : Cet outil inestimable vous aide à comprendre comment PostgreSQL exécute une requête, identifie les goulots d'étranglement, et peut révéler si work_mem est insuffisant.

Le meilleur travail de réglage PostgreSQL est ennuyeux dans le bon sens : mesurer, changer un paramètre, mesurer à nouveau, et garder une voie de retour. shared_buffers, work_mem et les paramètres de point de contrôle peuvent faire une réelle différence, mais ils fonctionnent avec les plans de requêtes, les index, l'autovacuum, les nombres de connexions et le stockage. Si ces éléments sont malsains, la configuration seule ne sauvera pas la base de données.