11Mar, 2025
Optimisation des Checkpoints PostgreSQL : Réduire l’Impact sur les Performances

 

Les checkpoints  dans PostgreSQL sont des moments où toutes les données modifiées en mémoire (buffers) sont écrites sur disque pour garantir la durabilité des données et minimiser le temps de récupération en cas de crash. Bien qu’ils soient essentiels, ils peuvent engendrer une activité I/O importante et affecter les performances si mal configurés. Cet article détaille comment optimiser les checkpoints et investiguer leur impact.

Paramètres Clés pour l’Optimisation

Voici les principaux paramètres à ajuster dans le fichier postgresql.conf :

  1. checkpoint_timeout :

    • Définit l’intervalle maximal entre deux checkpoints automatiques.

    • Par défaut, il est fixé à 5 minutes. Augmenter cette valeur réduit la fréquence des checkpoints, mais augmente le temps de récupération après un crash.

  2. max_wal_size :

    • Contrôle la taille maximale des fichiers WAL entre deux checkpoints.

    • Augmenter cette valeur diminue la fréquence des checkpoints, mais nécessite plus d’espace disque.

  3. checkpoint_completion_target :

    • Fraction du temps entre deux checkpoints allouée pour écrire les buffers sales.

    • Une valeur proche de 1 répartit mieux la charge I/O, réduisant l’impact sur les performances.

  4. wal_buffers :

    • Quantité de mémoire dédiée aux données WAL avant leur écriture sur disque.

    • Augmenter cette valeur est utile pour les charges d’écriture élevées.

  5. checkpoint_flush_after :

    • Taille après laquelle les pages modifiées sont écrites sur disque pendant un checkpoint.

    • Ajuster cette valeur peut réduire les pics d’I/O.

 

Requêtes pour Investiguer les Checkpoints 

1. Fréquence des Checkpoints

Pour analyser la fréquence des checkpoints :

SELECT
total_checkpoints,
seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints
FROM (
SELECT
EXTRACT(EPOCH FROM (now() – pg_postmaster_start_time())) AS seconds_since_start,
(checkpoints_timed + checkpoints_req) AS total_checkpoints
FROM pg_stat_bgwriter
) AS sub;

Si les checkpoints sont trop fréquents (moins de quelques minutes), augmentez max_wal_size.

2. Analyse des Buffers Écrits

Pour examiner le volume de données écrit lors des checkpoints :

SELECT
buffers_checkpoint,
buffers_clean,
buffers_backend
FROM pg_stat_bgwriter;

 

  • buffers_checkpoint : Écritures pendant les checkpoints.

  • buffers_clean : Écritures effectuées par le background writer.

  • buffers_backend : Écritures réalisées par les sessions utilisateur (idéalement faibles).

 

3. Surveillance via les Logs

Activez log_checkpoints pour enregistrer chaque checkpoint dans les logs :

ALTER SYSTEM SET log_checkpoints = ‘on’;

Cela permet de suivre leur fréquence et leur durée.

 

Stratégies d’Optimisation 

  1. Réduire la Fréquence des Checkpoints

    • Augmentez checkpoint_timeout (par exemple, à 15 minutes) et max_wal_size.

    • Cela réduit l’activité I/O liée aux checkpoints mais augmente le temps de récupération.

  2. Répartir la Charge I/O

    • Réglez checkpoint_completion_target à une valeur proche de 0.9 pour étaler l’écriture sur toute la période du checkpoint.

  3. Améliorer le Contexte Matériel

    • Utilisez des disques SSD pour minimiser l’impact des écritures.

    • Ajustez effective_io_concurrency si votre stockage prend en charge plusieurs opérations simultanées.

  4. Surveiller et Ajuster

    • Surveillez régulièrement via pg_stat_bgwriter.

    • Testez différentes configurations dans un environnement de staging avant application en production.

 

La Configuration des Checkpoints 

La configuration des checkpoints n’est pas une science exacte. Elle dépend fortement de l’activité de votre base de données, de la charge de travail, et de l’infrastructure matérielle. Voici quelques points à considérer pour adapter les paramètres à votre environnement spécifique :

1. Charge de Travail

  • Charge d’Écriture Élevée : Si votre base de données traite beaucoup d’écritures (par exemple, un système de journalisation ou de traitement de transactions), vous devrez probablement augmenter max_wal_size et ajuster checkpoint_completion_target pour étaler les écritures.

  • Charge de Lecture Élevée : Pour les bases principalement orientées lecture, les checkpoints ont moins d’impact. Vous pouvez réduire max_wal_size et augmenter checkpoint_timeout pour limiter les opérations I/O inutiles.

2. Taille de la Base de Données

  • Grandes Bases de Données : Si votre base de données est volumineuse, augmentez max_wal_size pour éviter des checkpoints trop fréquents. Cependant, assurez-vous que votre disque dispose de suffisamment d’espace pour stocker les fichiers WAL.

  • Petites Bases de Données : Pour les bases plus petites, des valeurs plus modestes pour max_wal_size et min_wal_size peuvent suffire.

3. Infrastructure Matérielle

  • Disques SSD : Les SSD supportent mieux les charges I/O élevées. Vous pouvez augmenter checkpoint_completion_target pour étaler les écritures sans craindre de ralentissements significatifs.

  • Disques Mécaniques (HDD) : Sur des disques plus lents, réduisez la fréquence des checkpoints en augmentant checkpoint_timeout et max_wal_size, tout en ajustant checkpoint_completion_target pour éviter les pics d’I/O.

4. Temps de Récupération Acceptable

  • Temps de Récupération Court : Si vous avez besoin d’un temps de récupération rapide en cas de crash, diminuez checkpoint_timeout et max_wal_size pour que les données soient écrites plus fréquemment.

  • Temps de Récupération Plus Long : Si un temps de récupération plus long est acceptable, augmentez ces paramètres pour réduire la charge I/O.

5. Surveillance Continue

Les paramètres optimaux peuvent évoluer avec le temps. Utilisez des outils comme pg_stat_bgwriter pour surveiller l’activité des checkpoints et ajuster les paramètres en conséquence. Par exemple :

  • Si buffers_checkpoint est élevé, envisagez d’augmenter max_wal_size.

  • Si les checkpoints sont trop fréquents, augmentez checkpoint_timeout.

 

Exemple Pratique 

Voici un exemple de configuration optimisée pour une base avec une charge d’écriture élevée :

checkpoint_timeout = 15min                                   # Augmenté pour réduire la fréquence des checkpoints
max_wal_size = 2GB                                               # Adapté à une charge d’écriture modérée
min_wal_size = 512MB                                           # Taille minimale pour éviter des réductions trop fréquentes
checkpoint_completion_target = 0.9                    # Écritures étalées sur 90% de l’intervalle
wal_buffers = 16MB                                                # Suffisant pour la plupart des charges de travail
checkpoint_flush_after = 256kB                           # Réduit les pics d’I/O sur les disques mécaniques

Cette configuration étale les écritures, réduit la fréquence des checkpoints et optimise l’utilisation de la mémoire et du disque.

La configuration des checkpoints doit être soigneusement adaptée à votre environnement spécifique. Une approche contextuelle, basée sur l’activité de votre base de données, la taille de vos données, et votre infrastructure matérielle, est essentielle pour optimiser les performances tout en garantissant la durabilité des données. Surveillez régulièrement les indicateurs clés et ajustez les paramètres au fil du temps pour maintenir un équilibre optimal entre performance et fiabilité.