Skip to content

Latest commit

 

History

History
1118 lines (842 loc) · 33.8 KB

File metadata and controls

1118 lines (842 loc) · 33.8 KB

🔝 Retour au Sommaire

16.13.3. Configuration WAL (Write-Ahead Log)

Introduction au WAL

Le WAL (Write-Ahead Log) est l'un des mécanismes les plus fondamentaux de PostgreSQL. C'est le système qui garantit la durabilité et la fiabilité de vos données, même en cas de panne soudaine du serveur.

Dans cette section, nous allons explorer trois paramètres critiques qui contrôlent le comportement du WAL :

  • wal_level : Le niveau de détail des informations enregistrées
  • max_wal_size : La taille maximale du WAL avant déclenchement d'un checkpoint
  • checkpoint_timeout : L'intervalle de temps entre les checkpoints

🎯 Pourquoi c'est important : Une mauvaise configuration du WAL peut soit compromettre la sécurité de vos données, soit causer de graves problèmes de performance. Comprendre ces paramètres est essentiel pour tout administrateur PostgreSQL.


Qu'est-ce que le WAL ? (Concept Fondamental)

Le Principe du Journal de Bord

Imaginez que vous êtes un comptable et que vous gérez un registre financier. Avant de modifier votre grand livre principal (qui est lourd et complexe à maintenir), vous notez d'abord toutes les transactions dans un petit carnet de notes séquentiel. Ce carnet, c'est le WAL.

💡 Analogie : Le WAL est comme un journal de bord où PostgreSQL note tout ce qui se passe avant de modifier réellement les fichiers de données. C'est la technique du "write-ahead" (écrire en avance).

Pourquoi Écrire en Avance ?

Problème sans WAL :

1. Application demande : INSERT INTO users VALUES (...)
2. PostgreSQL modifie directement le fichier de données
3. 💥 PANNE ÉLECTRIQUE avant que l'écriture soit complète
4. ❌ Fichier de données corrompu, données perdues

Solution avec WAL :

1. Application demande : INSERT INTO users VALUES (...)
2. PostgreSQL écrit d'abord dans le WAL (fichier séquentiel, rapide)
3. PostgreSQL confirme : "Transaction validée ✅"
4. Plus tard, PostgreSQL met à jour le fichier de données
5. 💥 PANNE ÉLECTRIQUE
6. Au redémarrage : PostgreSQL rejoue le WAL → ✅ Données récupérées !

Les Avantages du WAL

  1. Durabilité (ACID - Durability) : Les transactions validées sont garanties persistantes
  2. Performance : Écriture séquentielle (WAL) est plus rapide qu'aléatoire (fichiers de données)
  3. Réplication : Le WAL peut être envoyé à d'autres serveurs pour réplication
  4. Point-In-Time Recovery (PITR) : Restauration à n'importe quel moment dans le passé
  5. Crash Recovery : Récupération automatique après une panne

Anatomie Physique du WAL

Le WAL est composé de segments (fichiers) stockés dans le répertoire pg_wal/ :

/var/lib/postgresql/18/main/pg_wal/
├── 000000010000000000000001  (16 MB)
├── 000000010000000000000002  (16 MB)
├── 000000010000000000000003  (16 MB)
└── ...

Caractéristiques :

  • Chaque segment fait 16 MB (par défaut, configurable à la compilation)
  • Les segments sont nommés séquentiellement
  • PostgreSQL écrit toujours de manière séquentielle (très efficace sur HDD et SSD)
  • Les anciens segments sont soit recyclés, soit archivés (selon configuration)

1. wal_level : Le Niveau de Détail du Journal

Qu'est-ce que c'est ?

wal_level définit la quantité d'informations que PostgreSQL va enregistrer dans le WAL. Plus le niveau est élevé, plus d'informations sont stockées, ce qui permet plus de fonctionnalités mais génère plus de volume de données.

Les Valeurs Possibles

Valeur Description Volume WAL Cas d'Usage
minimal Minimum vital pour crash recovery ⬇️ Très faible ⚠️ Déconseillé (limité)
replica Permet la réplication physique 📊 Normal ✅ Serveur avec replicas physiques
logical Permet la réplication logique 📈 Élevé ✅ Réplication logique, CDC

Détails de Chaque Niveau

minimal (Déprécié et Limité)

wal_level = minimal  # ⚠️ NE PAS UTILISER EN PRODUCTION

Enregistre :

  • Les modifications minimales pour le crash recovery
  • Juste assez pour récupérer après une panne

Ne permet PAS :

  • ❌ Réplication (ni physique ni logique)
  • ❌ Point-In-Time Recovery (PITR)
  • ❌ Standby servers (hot standby)
  • ❌ Archivage WAL

Quand l'utiliser :

  • Presque jamais en production
  • Peut-être pour des tests locaux sans importance

⚠️ Avertissement : minimal est un piège ! Il semble attrayant (moins de WAL), mais vous perdez toute capacité de réplication et de sauvegarde avancée. Évitez-le absolument en production.

replica (Recommandé par Défaut)

wal_level = replica  # ✅ DÉFAUT dans PostgreSQL moderne

Enregistre :

  • Toutes les informations nécessaires pour la réplication physique
  • Métadonnées sur les pages modifiées
  • État des verrous pour hot standby

Permet :

  • ✅ Réplication physique (streaming replication)
  • ✅ Standby servers avec hot standby (lecture possible)
  • ✅ Point-In-Time Recovery (PITR)
  • ✅ Archivage WAL pour backups
  • ✅ pg_basebackup

Volume WAL : Normal, raisonnable pour la plupart des cas

Quand l'utiliser :

  • Par défaut pour 90% des installations
  • Serveurs avec réplication physique
  • Serveurs nécessitant PITR
  • Production standard

logical (Pour Réplication Logique)

wal_level = logical  # Pour réplication logique et CDC

Enregistre :

  • Tout ce que replica enregistre, PLUS :
  • Informations sur les lignes modifiées (ancien/nouveau état)
  • Détails permettant de reconstruire les requêtes SQL logiquement

Permet :

  • ✅ Tout ce que replica permet, PLUS :
  • ✅ Réplication logique (publications/subscriptions)
  • ✅ Change Data Capture (CDC)
  • ✅ Logical Decoding pour streaming d'événements
  • ✅ Outils comme Debezium, pglogical

Volume WAL : ~10-30% plus élevé que replica

Quand l'utiliser :

  • Réplication sélective (certaines tables seulement)
  • Migration entre versions majeures avec zéro downtime
  • Change Data Capture pour microservices
  • Event sourcing / architecture événementielle
  • Intégration avec systèmes externes (Kafka, etc.)

Comparaison Visuelle du Volume WAL

Même charge de travail (1000 transactions) :

minimal:  ████░░░░░░░░░░░░░░░░  (~20 MB)  
replica:  ████████████░░░░░░░░  (~60 MB)  ← RECOMMANDÉ  
logical:  ████████████████░░░░  (~80 MB)  

Comment Choisir ?

Arbre de Décision :

Avez-vous besoin de réplication logique ou CDC ?
├─ OUI → wal_level = logical
└─ NON → Avez-vous besoin de réplication physique ou PITR ?
    ├─ OUI → wal_level = replica ✅ (Défaut recommandé)
    └─ NON → wal_level = replica quand même ! (pour la flexibilité future)

🎯 Recommandation : Utilisez wal_level = replica par défaut. Passez à logical seulement si vous avez un besoin spécifique de réplication logique.

Changement de wal_level

-- Vérifier la valeur actuelle
SHOW wal_level;

-- Modifier (nécessite un REDÉMARRAGE)
ALTER SYSTEM SET wal_level = 'logical';

-- Appliquer
SELECT pg_reload_conf();  -- ❌ NE SUFFIT PAS !

-- Il FAUT redémarrer PostgreSQL
sudo systemctl restart postgresql

⚠️ Important : Changer wal_level nécessite un redémarrage complet de PostgreSQL.


2. max_wal_size : La Taille Maximale du WAL

Qu'est-ce que c'est ?

max_wal_size définit la quantité totale de WAL que PostgreSQL va accumuler avant de déclencher un checkpoint pour libérer de l'espace.

Comprendre les Checkpoints

Un checkpoint est une opération où PostgreSQL :

  1. Écrit toutes les pages modifiées en mémoire vers les fichiers de données sur disque
  2. Marque un point de référence dans le WAL
  3. Permet de recycler les anciens segments WAL

💡 Analogie : Imaginez que vous prenez des notes dans un carnet (WAL). De temps en temps, vous recopiez proprement ces notes dans un classeur permanent (fichiers de données), puis vous pouvez arracher les pages du carnet et les recycler. C'est un checkpoint !

Le Dilemme des Checkpoints

Checkpoints fréquents (max_wal_size faible) :

  • ✅ Moins d'espace disque utilisé pour le WAL
  • ✅ Recovery rapide après crash (moins de WAL à rejouer)
  • ❌ Performance dégradée (I/O intensifs fréquents)
  • ❌ Pics de latence visibles par les applications

Checkpoints espacés (max_wal_size élevé) :

  • ✅ Meilleures performances (moins d'I/O)
  • ✅ Latence plus stable
  • ❌ Plus d'espace disque pour le WAL
  • ❌ Recovery plus lent après crash

Valeur par Défaut

max_wal_size = 1GB  # Défaut dans PostgreSQL moderne

Historique :

  • PostgreSQL 9.4 et antérieurs : Paramètres complexes (checkpoint_segments)
  • PostgreSQL 9.5+ : Simplifié avec max_wal_size

Valeurs Recommandées

Type de Charge Écritures/sec Recommandation Explication
Faible (Lecture intensive) < 100 1-2 GB Défaut suffisant
Modéré (OLTP léger) 100-1000 2-4 GB Checkpoints plus espacés
Élevé (OLTP intensif) 1000-10000 4-8 GB Absorber les pics d'écriture
Très élevé (ETL, Bulk Load) > 10000 8-16 GB Minimiser les checkpoints
Data Warehouse Variable 4-8 GB Compromis

Calcul Pratique

Formule de base :

max_wal_size = (Volume écriture par minute) × (Minutes entre checkpoints souhaités)

Exemple :

  • Votre base génère 500 MB de WAL par minute
  • Vous voulez des checkpoints toutes les 10 minutes maximum
  • max_wal_size = 500 MB × 10 = 5 GB

Impact sur les Performances

Benchmark typique (OLTP, 100 connexions) :

max_wal_size Checkpoints/heure TPS moyen Latence P99
1 GB 24 5,200 150 ms
4 GB 6 5,800 95 ms
8 GB 3 6,100 80 ms
16 GB 1-2 6,200 75 ms

Observation : Passer de 1 GB à 8 GB réduit la latence P99 de 47% !

Configuration

# Dans postgresql.conf
max_wal_size = 4GB

Avantage : Peut être modifié sans redémarrage (reload suffit)

ALTER SYSTEM SET max_wal_size = '4GB';  
SELECT pg_reload_conf();  

Surveillance

-- Voir les checkpoints récents
SELECT * FROM pg_stat_bgwriter;

-- Nombre de checkpoints déclenchés par max_wal_size vs temps
SELECT
    checkpoints_timed,    -- Déclenchés par checkpoint_timeout
    checkpoints_req,      -- Déclenchés par max_wal_size (trop de WAL)
    ROUND(100.0 * checkpoints_req / (checkpoints_timed + checkpoints_req), 2) AS pct_req
FROM pg_stat_bgwriter;

Interprétation :

  • checkpoints_req élevé → max_wal_size est trop petit, augmentez-le
  • checkpoints_timed élevé → Les checkpoints sont principalement dus au temps (normal)

Objectif : Avoir checkpoints_req représentant moins de 10% du total.


3. checkpoint_timeout : L'Intervalle de Temps

Qu'est-ce que c'est ?

checkpoint_timeout définit l'intervalle de temps maximum entre deux checkpoints. Même si max_wal_size n'est pas atteint, un checkpoint sera déclenché après cette durée.

Pourquoi un Timeout ?

Sans timeout, si votre base a peu d'écritures, vous pourriez :

  • Accumuler des semaines de WAL sans checkpoint
  • Avoir un recovery extrêmement long après crash
  • Risquer de manquer d'espace disque

Le timeout garantit un checkpoint régulier pour limiter la fenêtre de recovery.

Valeur par Défaut

checkpoint_timeout = 5min  # Défaut PostgreSQL

Signification : Au moins un checkpoint toutes les 5 minutes, même si le WAL n'est pas plein.

Valeurs Recommandées

Contexte Recommandation Explication
OLTP production 10-15 minutes Bon compromis performance/recovery
Data Warehouse 15-30 minutes Charges moins fréquentes
Dev/Test 5 minutes Défaut OK
Haute disponibilité 5-10 minutes Recovery rapide prioritaire
Bulk load / ETL 30 minutes Minimiser les interruptions

Le Compromis Performance vs Recovery

checkpoint_timeout court (5 minutes) :

  • ✅ Recovery rapide (max 5 min de WAL à rejouer)
  • ✅ Moins de WAL à conserver
  • ❌ Checkpoints plus fréquents → plus d'I/O

checkpoint_timeout long (30 minutes) :

  • ✅ Moins d'interruptions
  • ✅ Meilleures performances d'écriture
  • ❌ Recovery lent (jusqu'à 30 min de WAL à rejouer)
  • ❌ Plus d'espace disque pour le WAL

Relation avec max_wal_size

Ces deux paramètres travaillent ensemble :

Un checkpoint se déclenche quand :
    (WAL accumulé >= max_wal_size) OU (Temps écoulé >= checkpoint_timeout)

Exemple avec des chiffres :

max_wal_size = 4GB  
checkpoint_timeout = 15min  

Scénario 1 : Charge légère

  • Génération : 100 MB WAL/minute
  • Après 15 minutes : 1.5 GB de WAL
  • ⏰ Checkpoint déclenché par timeout (15 min atteints avant 4 GB)

Scénario 2 : Charge intense

  • Génération : 400 MB WAL/minute
  • Après 10 minutes : 4 GB de WAL
  • 📊 Checkpoint déclenché par max_wal_size (4 GB atteints avant 15 min)

Configuration

# Dans postgresql.conf
checkpoint_timeout = 15min

Avantage : Peut être modifié sans redémarrage

ALTER SYSTEM SET checkpoint_timeout = '15min';  
SELECT pg_reload_conf();  

Surveillance et Ajustement

Voir les Statistiques de Checkpoint

-- Vue d'ensemble des checkpoints
SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,  -- Temps total d'écriture (ms)
    checkpoint_sync_time,   -- Temps de synchronisation (ms)
    buffers_checkpoint,     -- Nombre de buffers écrits
    buffers_clean,          -- Buffers écrits par bgwriter
    maxwritten_clean,       -- Bgwriter stoppé (trop écrire)
    buffers_backend,        -- Buffers écrits par backends (⚠️ mauvais)
    stats_reset
FROM pg_stat_bgwriter;

Indicateurs clés :

  • checkpoints_timed : Déclenchés par timeout (normal)
  • checkpoints_req : Déclenchés par max_wal_size (si trop élevé, augmenter max_wal_size)
  • buffers_backend : Si > 0, problème de performance (shared_buffers ou bgwriter)

Logs de Checkpoint

# Dans postgresql.conf
log_checkpoints = on

Dans les logs :

LOG: checkpoint starting: time  
LOG: checkpoint complete: wrote 15234 buffers (23.4%);  
     0 WAL file(s) added, 0 removed, 3 recycled;
     write=2.847 s, sync=0.123 s, total=3.012 s;
     sync files=142, longest=0.045 s, average=0.001 s;
     distance=4096 kB, estimate=4096 kB

Analyse :

  • wrote X buffers : Quantité de données écrites
  • write=X.X s : Temps d'écriture
  • distance=X kB : WAL généré depuis dernier checkpoint

Configuration Combinée : Le Trio Gagnant

Tableau des Configurations Recommandées

Serveur OLTP Production (16 GB RAM, SSD)

# WAL Configuration - OLTP optimisé
wal_level = replica                    # Réplication physique activée  
max_wal_size = 4GB                     # Absorber les pics d'écriture  
checkpoint_timeout = 10min             # Checkpoints espacés  

# Paramètres complémentaires
wal_compression = on                   # Compresser le WAL (PostgreSQL 14+)  
wal_buffers = 16MB                     # Buffer WAL en mémoire  
checkpoint_completion_target = 0.9     # Étaler les checkpoints sur 90% du timeout  

Explication :

  • wal_level = replica : Permet réplication + PITR
  • max_wal_size = 4GB : Checkpoints toutes les 10-15 min en charge normale
  • checkpoint_timeout = 10min : Sécurité, recovery < 10 min
  • checkpoint_completion_target = 0.9 : Étale l'I/O des checkpoints sur 9 minutes (90% de 10 min)

Serveur Data Warehouse (64 GB RAM, RAID SSD)

# WAL Configuration - Analytique
wal_level = replica                    # Réplication si besoin  
max_wal_size = 8GB                     # Gros volumes, checkpoints espacés  
checkpoint_timeout = 20min             # Recovery non critique  

# Optimisations
wal_compression = on  
wal_buffers = 32MB                     # Plus de buffer  
checkpoint_completion_target = 0.9  

Explication :

  • max_wal_size = 8GB : Absorber les gros ETL sans checkpoints intempestifs
  • checkpoint_timeout = 20min : Les requêtes sont longues, les checkpoints peuvent l'être aussi

Serveur avec Réplication Logique (32 GB RAM)

# WAL Configuration - Réplication logique + CDC
wal_level = logical                    # ⚠️ Niveau logical requis  
max_wal_size = 6GB                     # Plus de WAL généré avec logical  
checkpoint_timeout = 15min             # Compromis  

# Spécifique réplication logique
max_replication_slots = 10             # Nombre de subscribers  
max_wal_senders = 10                   # Processus de réplication  
wal_sender_timeout = 60s               # Timeout connexion replica  

Explication :

  • wal_level = logical : Nécessaire pour publications/subscriptions
  • max_wal_size = 6GB : Compensate le volume WAL accru (~30% plus)

Serveur Développement (8 GB RAM, SSD)

# WAL Configuration - Développement
wal_level = replica                    # Par défaut, flexible  
max_wal_size = 2GB                     # Moins de données, moins de WAL  
checkpoint_timeout = 5min              # Défaut OK  

# Logs pour apprentissage
log_checkpoints = on

Paramètres Complémentaires Importants

checkpoint_completion_target

checkpoint_completion_target = 0.9  # Valeur entre 0.0 et 1.0

Rôle : Étale l'écriture des buffers sales (dirty buffers) sur une fraction du checkpoint_timeout.

Exemple :

  • checkpoint_timeout = 10min
  • checkpoint_completion_target = 0.9
  • Résultat : L'écriture se fait progressivement sur 9 minutes au lieu d'un burst instantané

Impact :

  • Valeur basse (0.5) → I/O en burst, pics de latence
  • Valeur haute (0.9) → I/O lissé, latence stable ✅

Recommandation : 0.9 dans 99% des cas.

wal_buffers

wal_buffers = -1  # Auto (par défaut, = 1/32 de shared_buffers, entre 64kB et 16MB)
# Ou manuel :
wal_buffers = 16MB

Rôle : Quantité de mémoire pour bufferiser le WAL avant écriture disque.

Recommandations :

  • -1 (auto) suffit dans la plupart des cas
  • Si charge d'écriture TRÈS intensive : 16-32 MB

wal_compression

wal_compression = on  # PostgreSQL 14+

Rôle : Compresse les pages complètes (full-page writes) dans le WAL.

Impact :

  • ✅ Réduction du volume WAL de 20-40%
  • ✅ Moins d'I/O disque
  • ❌ Léger overhead CPU (~1-2%)

Recommandation : Activez-le sauf si CPU est le goulot d'étranglement.

wal_sync_method

wal_sync_method = fdatasync  # Défaut sur Linux, optimal

Valeurs possibles :

  • fdatasync (Linux) ✅
  • fsync (compatible mais plus lent)
  • open_datasync (certains Unix)
  • open_sync (compatible)

Recommandation : Gardez fdatasync sur Linux.


Monitoring et Diagnostics WAL

1. Vue Globale des Statistiques WAL

-- Statistiques complètes du WAL
SELECT
    pg_walfile_name(pg_current_wal_lsn()) as current_wal_file,
    pg_current_wal_lsn() as current_lsn,
    pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024 / 1024 AS wal_generated_mb,
    (SELECT setting FROM pg_settings WHERE name = 'max_wal_size') as max_wal_size,
    (SELECT setting FROM pg_settings WHERE name = 'checkpoint_timeout') as checkpoint_timeout;

2. Taux de Génération WAL

-- Créer une baseline
CREATE TABLE wal_baseline AS  
SELECT  
    now() as measured_at,
    pg_current_wal_lsn() as lsn,
    pg_stat_get_wal_buffers_full() as buffers_full
FROM pg_stat_wal;

-- 5 minutes plus tard...
WITH current_state AS (
    SELECT
        now() as measured_at,
        pg_current_wal_lsn() as lsn
)
SELECT
    EXTRACT(EPOCH FROM (c.measured_at - b.measured_at)) / 60 AS minutes_elapsed,
    pg_wal_lsn_diff(c.lsn, b.lsn) / 1024 / 1024 AS wal_mb_generated,
    (pg_wal_lsn_diff(c.lsn, b.lsn) / 1024 / 1024) /
        (EXTRACT(EPOCH FROM (c.measured_at - b.measured_at)) / 60) AS wal_mb_per_minute
FROM wal_baseline b, current_state c;

3. Analyser les Checkpoints

-- Ratio checkpoints timed vs requested
WITH checkpoint_stats AS (
    SELECT
        checkpoints_timed,
        checkpoints_req,
        ROUND(100.0 * checkpoints_req /
            NULLIF(checkpoints_timed + checkpoints_req, 0), 2) AS pct_requested,
        checkpoint_write_time,
        checkpoint_sync_time
    FROM pg_stat_bgwriter
)
SELECT
    *,
    CASE
        WHEN pct_requested > 50 THEN '⚠️ Augmenter max_wal_size'
        WHEN pct_requested > 20 THEN '⚡ Considérer augmentation max_wal_size'
        ELSE '✅ Configuration OK'
    END as recommendation
FROM checkpoint_stats;

Interprétation :

  • pct_requested < 10% : ✅ Excellent
  • pct_requested 10-20% : 🟡 Acceptable
  • pct_requested > 20% : 🟠 Augmenter max_wal_size
  • pct_requested > 50% : 🔴 Problème, augmenter max_wal_size immédiatement

4. Vérifier l'Espace Disque WAL

# Taille totale du répertoire pg_wal
du -sh /var/lib/postgresql/18/main/pg_wal/

# Nombre de fichiers WAL
ls /var/lib/postgresql/18/main/pg_wal/ | wc -l
-- Via SQL (PostgreSQL 18+)
SELECT
    COUNT(*) as wal_files,
    pg_size_pretty(SUM(size)) as total_size,
    pg_size_pretty(AVG(size)) as avg_file_size
FROM pg_ls_waldir();

5. Dashboard de Monitoring Complet

-- Vue d'ensemble pour monitoring quotidien
WITH wal_stats AS (
    SELECT
        pg_current_wal_lsn() as current_lsn,
        pg_walfile_name(pg_current_wal_lsn()) as current_file
),
checkpoint_stats AS (
    SELECT
        checkpoints_timed,
        checkpoints_req,
        ROUND(100.0 * checkpoints_req /
            (checkpoints_timed + checkpoints_req), 2) AS pct_req,
        pg_size_pretty(buffers_checkpoint * 8192) as data_written,
        checkpoint_write_time / 1000.0 as write_time_sec,
        checkpoint_sync_time / 1000.0 as sync_time_sec
    FROM pg_stat_bgwriter
),
config AS (
    SELECT
        (SELECT setting FROM pg_settings WHERE name = 'wal_level') as wal_level,
        (SELECT setting FROM pg_settings WHERE name = 'max_wal_size') as max_wal_size,
        (SELECT setting FROM pg_settings WHERE name = 'checkpoint_timeout') as checkpoint_timeout
)
SELECT
    'WAL Configuration' as metric_group,
    json_build_object(
        'wal_level', c.wal_level,
        'max_wal_size', c.max_wal_size,
        'checkpoint_timeout', c.checkpoint_timeout,
        'current_wal_file', w.current_file
    ) as configuration,
    json_build_object(
        'checkpoints_timed', cs.checkpoints_timed,
        'checkpoints_requested', cs.checkpoints_req,
        'pct_requested', cs.pct_req || '%',
        'data_written', cs.data_written,
        'avg_write_time', ROUND(cs.write_time_sec, 2) || 's',
        'avg_sync_time', ROUND(cs.sync_time_sec, 2) || 's'
    ) as statistics
FROM wal_stats w, checkpoint_stats cs, config c;

Troubleshooting : Problèmes Courants

Problème 1 : Checkpoints Trop Fréquents

Symptôme :

SELECT
    checkpoints_req / (checkpoints_timed + checkpoints_req) * 100 AS pct_req
FROM pg_stat_bgwriter;
-- Résultat : > 50%

Dans les logs :

LOG: checkpoint starting: xlog  
LOG: checkpoint complete: ... distance=4096 kB, estimate=4096 kB  

Cause : max_wal_size est trop petit pour la charge d'écriture.

Solution :

# Augmenter progressivement
max_wal_size = 8GB  # Double la valeur actuelle
ALTER SYSTEM SET max_wal_size = '8GB';  
SELECT pg_reload_conf();  

Problème 2 : Pics de Latence Périodiques

Symptôme : Toutes les X minutes, latence applicative explose.

Cause : Checkpoints provoquent des I/O bursts.

Solution :

# Étaler les écritures
checkpoint_completion_target = 0.9  # Au lieu de 0.5

# Espacer les checkpoints
checkpoint_timeout = 15min  # Au lieu de 5min  
max_wal_size = 4GB  # Augmenter  

Problème 3 : Espace Disque WAL Épuisé

Symptôme :

ERROR: could not write to file "pg_wal/xlogtemp.12345": No space left on device  
PANIC: could not write to WAL file  

Causes possibles :

  1. Slots de réplication bloqués
-- Identifier les slots qui retiennent le WAL
SELECT
    slot_name,
    slot_type,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 AS mb_behind
FROM pg_replication_slots  
ORDER BY mb_behind DESC;  

Solution :

-- Supprimer le slot bloqué (⚠️ Perte réplication)
SELECT pg_drop_replication_slot('slot_name');
  1. archive_command échoue
-- Vérifier l'archivage
SELECT * FROM pg_stat_archiver;

Solution : Corriger le script d'archivage ou désactiver temporairement.

  1. max_wal_size trop élevé + activité intense

Solution : Augmenter l'espace disque ou réduire temporairement max_wal_size.

Problème 4 : Recovery Très Lent Après Crash

Symptôme : PostgreSQL met 30+ minutes à démarrer après une panne.

Cause : Trop de WAL à rejouer (checkpoint_timeout trop élevé, max_wal_size trop grand).

Solution :

# Réduire la fenêtre de recovery
checkpoint_timeout = 5min   # Au lieu de 30min  
max_wal_size = 2GB          # Au lieu de 16GB  

Trade-off : Performances légèrement réduites en échange d'une recovery rapide.

Problème 5 : Réplication en Retard (Lag)

Symptôme :

SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(sent_lsn, write_lsn) / 1024 / 1024 AS write_lag_mb,
    pg_wal_lsn_diff(write_lsn, flush_lsn) / 1024 / 1024 AS flush_lag_mb,
    pg_wal_lsn_diff(flush_lsn, replay_lsn) / 1024 / 1024 AS replay_lag_mb
FROM pg_stat_replication;
-- Lag > 1 GB

Causes :

  1. Réseau lent entre primary et replica
  2. Replica sous-dimensionné (I/O, CPU)
  3. WAL généré trop rapidement

Solutions :

# Sur le primary
wal_compression = on  # Réduire le volume WAL  
wal_keep_size = 4GB   # Garder plus de WAL pour les replicas lents  

# Sur le replica
max_wal_senders = 5   # Plus de processus de réplication

Optimisations Avancées

1. WAL Compression (PostgreSQL 14+)

wal_compression = on  
wal_compression_level = 1  # PostgreSQL 15+ (1-9, défaut = -1 = auto)  

Bénéfices :

  • Réduction volume WAL : 20-40%
  • Moins d'I/O disque
  • Réplication plus rapide (moins de données réseau)

Coût :

  • 1-2% CPU supplémentaire
  • Négligeable sur serveurs modernes

Recommandation : ✅ Activez-le par défaut.

2. Full Page Writes (FPW) - Attention !

full_page_writes = on  # ⚠️ NE JAMAIS DÉSACTIVER EN PRODUCTION

Rôle : Après chaque checkpoint, la première modification d'une page est enregistrée entièrement dans le WAL (pas juste le delta).

Pourquoi : Protection contre la corruption en cas de partial page write.

⚠️ DANGER : Désactiver full_page_writes = off :

  • ✅ Réduit le volume WAL de 50-70%
  • ❌ Risque de corruption de données après crash
  • ❌ Uniquement acceptable si filesystem garantit atomic writes (rare)

Recommandation : TOUJOURS laisser à on sauf configuration très spécifique avec ZFS ou similaire.

3. WAL sur Partition Séparée

Best Practice Production : Placer pg_wal sur un disque/partition séparé(e).

Avantages :

  • I/O WAL séquentiel non perturbé par I/O tables (aléatoire)
  • Optimisation possible : HDD pour données, SSD pour WAL
  • Isolation des pannes

Configuration :

# Créer un lien symbolique vers une partition SSD dédiée
mv /var/lib/postgresql/18/main/pg_wal /mnt/wal-ssd/pg_wal  
ln -s /mnt/wal-ssd/pg_wal /var/lib/postgresql/18/main/pg_wal  

4. Tuning Avancé pour SSD NVMe

# Configuration ultra-performante pour SSD NVMe rapide
wal_level = replica  
max_wal_size = 8GB  
checkpoint_timeout = 15min  
checkpoint_completion_target = 0.9  

# Spécifique SSD NVMe
wal_sync_method = fdatasync  
wal_buffers = 32MB  
wal_writer_delay = 10ms       # Défaut 200ms, réduire pour SSD  
wal_writer_flush_after = 1MB  # Défaut 1MB, OK pour SSD  

# Nouveau PostgreSQL 18 : I/O asynchrone
io_method = 'worker'  # ou 'io_uring' sur Linux

Checklist de Configuration WAL

✅ Configuration de Base (Minimum)

# Obligatoire
wal_level = replica              # Réplication + PITR  
max_wal_size = 4GB               # Ajusté à la charge  
checkpoint_timeout = 10min       # Compromis recovery/performance  
checkpoint_completion_target = 0.9  # Lisser les I/O  

# Monitoring
log_checkpoints = on             # Tracer les checkpoints

✅ Configuration Production Standard

# WAL Core
wal_level = replica  
max_wal_size = 4GB  
checkpoint_timeout = 10min  
checkpoint_completion_target = 0.9  

# Optimisations
wal_compression = on             # PostgreSQL 14+  
wal_buffers = 16MB  
wal_sync_method = fdatasync      # Linux  

# Réplication
max_wal_senders = 5  
wal_keep_size = 2GB              # Garder du WAL pour replicas  

# Monitoring
log_checkpoints = on

✅ Configuration Haute Performance (I/O Intensif)

# WAL Core
wal_level = replica  
max_wal_size = 8GB               # Grande fenêtre  
checkpoint_timeout = 15min       # Checkpoints espacés  
checkpoint_completion_target = 0.9  

# Optimisations
wal_compression = on  
wal_buffers = 32MB               # Plus de buffer  
wal_writer_delay = 10ms          # Flush rapide (SSD)  
wal_sync_method = fdatasync  

# Réplication
max_wal_senders = 10  
wal_keep_size = 4GB  

# Monitoring
log_checkpoints = on

✅ Configuration Réplication Logique

# WAL Core
wal_level = logical              # ⚠️ Requis pour logical replication  
max_wal_size = 6GB               # 30% plus pour compenser le volume  
checkpoint_timeout = 15min  
checkpoint_completion_target = 0.9  

# Réplication logique
max_replication_slots = 10  
max_wal_senders = 10  
wal_sender_timeout = 60s  

# Optimisations
wal_compression = on  
wal_buffers = 16MB  

# Monitoring
log_checkpoints = on

Résumé et Points Clés

🎯 Les 3 Paramètres Essentiels

  1. wal_level : Le niveau d'information

    • replica pour 90% des cas (réplication physique + PITR)
    • logical seulement si réplication logique ou CDC nécessaire
    • ⚠️ Nécessite un redémarrage
  2. max_wal_size : La taille du "tampon" WAL

    • 1 GB (défaut) → 4-8 GB (production)
    • Plus grand = checkpoints plus espacés = meilleures performances
    • Attention à l'espace disque et temps de recovery
    • ✅ Modifiable sans redémarrage
  3. checkpoint_timeout : L'intervalle de temps

    • 5 min (défaut) → 10-15 min (production)
    • Plus grand = moins d'interruptions
    • Attention au temps de recovery
    • ✅ Modifiable sans redémarrage

📊 Règles d'Or

  1. Équilibre Performance vs Recovery :

    • Production OLTP : max_wal_size = 4GB, checkpoint_timeout = 10min
    • Data Warehouse : max_wal_size = 8GB, checkpoint_timeout = 20min
  2. Monitoring Critique :

    • checkpoints_req < 10% du total → ✅ Bon
    • checkpoints_req > 20% du total → 🔴 Augmenter max_wal_size
  3. Toujours Activer :

    • wal_compression = on (PostgreSQL 14+)
    • log_checkpoints = on
    • checkpoint_completion_target = 0.9
  4. Ne Jamais :

    • Désactiver full_page_writes en production
    • Mettre wal_level = minimal en production
    • Ignorer les alertes d'espace disque WAL

🔍 Surveillance Continue

-- Requête quotidienne de sanity check
SELECT
    'Checkpoints' as metric,
    checkpoints_timed as timed,
    checkpoints_req as requested,
    ROUND(100.0 * checkpoints_req / (checkpoints_timed + checkpoints_req), 2) || '%' as pct_req,
    CASE
        WHEN checkpoints_req::float / (checkpoints_timed + checkpoints_req) > 0.2
        THEN '⚠️ Augmenter max_wal_size'
        ELSE '✅ OK'
    END as status
FROM pg_stat_bgwriter;

Pour Aller Plus Loin

Documentation Officielle

Articles Approfondis

  • "Understanding PostgreSQL WAL" - 2ndQuadrant
  • "Tuning Checkpoints for Performance" - Percona Blog
  • "PostgreSQL WAL Deep Dive" - CrunchyData

Outils

  • pg_stat_bgwriter : Vue système pour checkpoints
  • pg_stat_archiver : Surveillance archivage WAL
  • pgBadger : Analyse logs incluant checkpoints
  • Prometheus + postgres_exporter : Métriques WAL en temps réel

Lectures Recommandées

  • "PostgreSQL: Up and Running" - Chapitre WAL et Réplication
  • "The Internals of PostgreSQL" - Section 9 (WAL)

Conclusion

La configuration du WAL est un équilibre délicat entre :

  • Performance (checkpoints espacés, I/O lissé)
  • Sécurité (durabilité des transactions)
  • Recovery (temps de récupération après crash)
  • Espace disque (volume WAL conservé)

Les trois paramètres wal_level, max_wal_size, et checkpoint_timeout forment le trio fondamental de cette configuration. Bien les comprendre et les ajuster selon votre charge de travail peut améliorer significativement les performances et la fiabilité de votre système PostgreSQL.

Prochaine étape : Dans la section 16.13.4, nous explorerons la configuration de l'autovacuum, un autre mécanisme critique de PostgreSQL qui travaille en symbiose avec le WAL pour maintenir la santé de votre base de données !


⏭️ Configuration autovacuum