🔝 Retour au Sommaire

19.4.2. Saturation des ressources (CPU, RAM, I/O)

Introduction : Les trois piliers de la performance

Imaginez PostgreSQL comme une cuisine professionnelle :

Le CPU est le chef cuisinier qui prépare les plats (exécute les requêtes)
La RAM est le plan de travail où tout est à portée de main (données en cache)
L'I/O (disque) est le garde-manger où sont stockés tous les ingrédients (données persistantes)

Quand l'un de ces trois éléments sature, toute la performance s'effondre. Dans ce chapitre, nous allons apprendre à diagnostiquer et résoudre ces saturations.

Comprendre la saturation : Qu'est-ce que c'est ?

Une saturation de ressource se produit quand une composante du système atteint ou dépasse sa capacité maximale :

CPU saturé : Le processeur travaille à 100% et ne peut pas traiter plus de requêtes
RAM saturée : La mémoire est pleine, le système commence à utiliser le swap (disque)
I/O saturé : Les disques ne peuvent plus traiter les lectures/écritures assez rapidement

Les symptômes généraux

⏱️ Lenteur généralisée : Toutes les requêtes deviennent lentes
📊 Timeouts : Les applications reçoivent des erreurs de timeout
🔥 Augmentation de la latence : Ce qui prenait 10ms prend maintenant 5 secondes
🚨 Alertes de monitoring : Vos outils de surveillance se déclenchent

Partie 1 : Saturation CPU

Qu'est-ce que le CPU fait dans PostgreSQL ?

Le CPU exécute toutes les opérations de calcul :

Analyse des requêtes (parsing)
Planification des requêtes (query planning)
Exécution des jointures, tris, agrégations
Compression/décompression des données
Calculs sur les index
Traitement des fonctions PL/pgSQL

Symptômes d'une saturation CPU

# Vérifier l'utilisation CPU globale du système
top
# ou
htop

# Vérifier l'utilisation CPU des processus PostgreSQL
ps aux | grep postgres | head -20

Signes d'alerte :

CPU constamment au-dessus de 80-90% d'utilisation
Processus PostgreSQL consomment la majorité du CPU
Load average élevé (supérieur au nombre de cœurs)

Diagnostiquer : Identifier les requêtes gourmandes en CPU

Méthode 1 : Via pg_stat_activity (état en temps réel)

SELECT
    pid,
    usename,
    state,
    query,
    (now() - query_start) AS duration
FROM pg_stat_activity  
WHERE state = 'active'  
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start  
LIMIT 10;

Explication :

Les requêtes qui tournent depuis longtemps (duration élevée) sont suspectes
Si state = 'active', la requête consomme actuellement du CPU
Exclure les requêtes de monitoring (pg_stat_activity) pour éviter la récursion

Méthode 2 : Via pg_stat_statements (vue historique)

-- Installation de l'extension (à faire une seule fois)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Trouver les requêtes les plus gourmandes en CPU
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    stddev_exec_time,
    rows
FROM pg_stat_statements  
ORDER BY total_exec_time DESC  
LIMIT 10;

Colonnes importantes :

total_exec_time : Temps CPU total cumulé (en millisecondes)
mean_exec_time : Temps moyen par exécution
calls : Nombre de fois que la requête a été exécutée
max_exec_time : Pire cas d'exécution

Interpréter les résultats :

Haute total_exec_time + basse mean_exec_time → Requête très fréquente, optimiser pour réduire les appels
Haute mean_exec_time → Requête lente, optimiser la requête elle-même
Haute max_exec_time → Comportement erratique, investiguer les cas extrêmes

Méthode 3 : Via l'extension pg_stat_kcache (métriques système)

-- Installation (nécessite compilation ou package système)
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;

-- Requêtes avec plus de détails CPU système
SELECT
    pss.query,
    pss.calls,
    pss.total_exec_time,
    psk.user_time,    -- Temps CPU en mode utilisateur
    psk.system_time,  -- Temps CPU en mode noyau
    psk.reads,        -- Lectures disque
    psk.writes        -- Écritures disque
FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk USING (userid, dbid, queryid)  
ORDER BY psk.user_time + psk.system_time DESC  
LIMIT 10;

Résoudre une saturation CPU

Solution 1 : Optimiser les requêtes lentes

-- Analyser une requête spécifique
EXPLAIN (ANALYZE, BUFFERS)  
SELECT * FROM orders  
WHERE user_id = 12345 AND status = 'pending';

Points d'attention dans EXPLAIN :

Seq Scan sur grande table → Ajouter un index
Nested Loop coûteux → Vérifier les jointures
Sort avec grande quantité de données → Augmenter work_mem ou ajouter index

Solution 2 : Ajouter des index manquants

-- Identifier les tables sans index bien utilisés
SELECT
    schemaname,
    tablename,
    seq_scan,              -- Nombre de scans séquentiels
    seq_tup_read,          -- Lignes lues en scan séquentiel
    idx_scan,              -- Nombre de scans d'index
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_tup
FROM pg_stat_user_tables  
WHERE seq_scan > 0  
ORDER BY seq_tup_read DESC  
LIMIT 10;

Interprétation :

seq_scan élevé avec grande table → Index manquant probable
seq_tup_read / seq_scan > 1000 → Chaque scan lit beaucoup de lignes, très inefficace

Créer un index :

-- Exemple : index sur colonne fréquemment filtrée
CREATE INDEX CONCURRENTLY idx_orders_user_id  
ON orders(user_id);  

-- Index composé pour requêtes multi-colonnes
CREATE INDEX CONCURRENTLY idx_orders_user_status  
ON orders(user_id, status);

Note : CONCURRENTLY permet de créer l'index sans bloquer les écritures (mais prend plus de temps).

Solution 3 : Limiter les connexions actives

-- Voir le nombre de connexions actives
SELECT
    state,
    count(*)
FROM pg_stat_activity  
GROUP BY state;

Si trop de connexions sont actives (ex: 300 connexions pour 4 cœurs CPU) :

-- Dans postgresql.conf, limiter les connexions
max_connections = 100

-- Utiliser un connection pooler (PgBouncer)
-- PgBouncer peut gérer 10000 connexions applicatives
-- et n'utiliser que 100 connexions PostgreSQL réelles

Solution 4 : Utiliser le parallélisme PostgreSQL

PostgreSQL peut paralléliser certaines requêtes sur plusieurs cœurs :

-- Activer le parallélisme (déjà activé par défaut en général)
SET max_parallel_workers_per_gather = 4;  
SET max_parallel_workers = 8;  

-- Vérifier si une requête utilise le parallélisme
EXPLAIN SELECT count(*) FROM large_table;
-- Recherchez "Parallel Seq Scan" dans le plan

Attention : Le parallélisme consomme plus de ressources, à utiliser judicieusement.

Partie 2 : Saturation RAM (Mémoire)

Rôle de la RAM dans PostgreSQL

La RAM sert principalement à :

Cache des données (shared_buffers) : Évite les lectures disque répétées
Mémoire de travail (work_mem) : Pour tris, hash tables, jointures
Maintenance (maintenance_work_mem) : Pour VACUUM, CREATE INDEX
Connexions : Chaque connexion consomme de la RAM

Symptômes d'une saturation RAM

# Vérifier l'utilisation mémoire globale
free -h
# ou
vmstat 1 5

# Vérifier si le système utilise le swap (TRÈS MAUVAIS pour PostgreSQL)
cat /proc/meminfo | grep -i swap

Signes d'alerte :

Mémoire disponible < 10% de la RAM totale
Swap utilisé (signe critique de saturation)
OOM Killer (Out Of Memory) tue des processus PostgreSQL
Ralentissement soudain et généralisé

Diagnostiquer : Identifier la consommation mémoire

Méthode 1 : Paramètres de configuration PostgreSQL

-- Voir la configuration mémoire actuelle
SHOW shared_buffers;  
SHOW work_mem;  
SHOW maintenance_work_mem;  
SHOW effective_cache_size;  

-- Version détaillée
SELECT
    name,
    setting,
    unit,
    context
FROM pg_settings  
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',  
               'effective_cache_size', 'max_connections');

Règles empiriques (pour un serveur dédié à PostgreSQL) :

shared_buffers : 25% de la RAM totale (ex: 8GB pour 32GB de RAM)
effective_cache_size : 50-75% de la RAM totale
work_mem : RAM / (max_connections * 2 à 4)
maintenance_work_mem : 512MB à 2GB

Méthode 2 : Identifier les requêtes gourmandes en mémoire

-- Requêtes utilisant beaucoup de work_mem
-- (nécessite log_temp_files configuré)
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements  
WHERE query LIKE '%ORDER BY%'  
   OR query LIKE '%GROUP BY%'
   OR query LIKE '%DISTINCT%'
ORDER BY mean_exec_time DESC  
LIMIT 10;

Ces types de requêtes utilisent work_mem pour :

Tris (ORDER BY)
Agrégations (GROUP BY)
Hash joins
Opérations DISTINCT

Méthode 3 : Vérifier l'efficacité du cache

-- Cache hit ratio (doit être > 95%)
SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(
        sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
        2
    ) AS cache_hit_ratio
FROM pg_statio_user_tables;

Interprétation :

> 99% : Excellent, presque tout est en cache
95-99% : Bon, acceptable
< 95% : Problème, trop de lectures disque, augmentez shared_buffers ou optimisez les requêtes

Méthode 4 : Analyser les temporary files

Quand work_mem est insuffisant, PostgreSQL écrit temporairement sur disque :

-- Dans postgresql.conf, activer la journalisation
log_temp_files = 0  -- Log tous les fichiers temporaires

-- Puis consulter les logs PostgreSQL
-- Exemple de ligne de log :
-- LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp12345.0", size 52428800

Requête pour trouver les bases avec beaucoup de fichiers temporaires :

SELECT
    datname,
    temp_files,
    temp_bytes,
    pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database  
WHERE temp_files > 0  
ORDER BY temp_bytes DESC;

Si temp_files et temp_bytes sont élevés → augmentez work_mem.

Résoudre une saturation RAM

Solution 1 : Augmenter shared_buffers

-- Dans postgresql.conf
shared_buffers = 8GB  -- Environ 25% de la RAM

-- Redémarrage nécessaire
-- sudo systemctl restart postgresql

Attention : Ne pas dépasser 40% de la RAM pour shared_buffers. Au-delà, la performance se dégrade.

Solution 2 : Augmenter work_mem (avec précaution)

-- Global (dans postgresql.conf)
work_mem = 64MB

-- Ou par session pour requêtes spécifiques
SET work_mem = '256MB';  
SELECT * FROM large_table ORDER BY created_at;  
RESET work_mem;  

-- Ou par transaction
BEGIN;  
SET LOCAL work_mem = '512MB';  
-- Requête lourde ici
COMMIT;

Calcul important :

Mémoire potentielle = work_mem × max_connections × 2 à 4

Si work_mem = 256MB et max_connections = 200 :

Consommation possible = 256MB × 200 × 3 = 150GB !

⚠️ Risque d'OOM ! Augmentez work_mem progressivement et surveillez.

Solution 3 : Optimiser les requêtes pour réduire l'usage mémoire

-- ❌ MAUVAIS : Tri en mémoire de toute la table
SELECT * FROM logs ORDER BY created_at DESC;

-- ✅ BON : Utiliser un index pour éviter le tri
CREATE INDEX idx_logs_created ON logs(created_at DESC);  
SELECT * FROM logs ORDER BY created_at DESC;  

-- ❌ MAUVAIS : Distinct sur grande table sans index
SELECT DISTINCT user_id FROM events;

-- ✅ BON : Utiliser GROUP BY avec index
SELECT user_id FROM events GROUP BY user_id;

Solution 4 : Réduire le nombre de connexions

Chaque connexion consomme de la RAM (~10MB par connexion) :

-- Voir les connexions actives et leur état
SELECT
    datname,
    state,
    count(*)
FROM pg_stat_activity  
GROUP BY datname, state;  

-- Fermer les connexions inactives
-- Configurer dans postgresql.conf
idle_in_transaction_session_timeout = 60000  -- 60 secondes

Utiliser PgBouncer :

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction  
max_client_conn = 10000  
default_pool_size = 25

Solution 5 : Désactiver le swap pour PostgreSQL

Le swap tue la performance de PostgreSQL :

# Vérifier l'utilisation du swap
swapon --show

# Désactiver le swap (temporaire)
sudo swapoff -a

# Désactiver définitivement (dans /etc/fstab, commenter la ligne swap)

Alternative : Configurer le système pour minimiser le swap :

# Réduire la tendance à swapper (vm.swappiness)
sudo sysctl vm.swappiness=1  
echo "vm.swappiness = 1" | sudo tee -a /etc/sysctl.conf

Partie 3 : Saturation I/O (Disque)

Rôle de l'I/O dans PostgreSQL

Les disques stockent :

Les données : Tables, index, TOAST
Les WAL (Write-Ahead Logs) : Journal des transactions
Les fichiers temporaires : Quand work_mem est dépassé

Types d'opérations I/O

Lectures : Accès aux tables et index non en cache
Écritures : INSERT, UPDATE, DELETE, VACUUM, checkpoints
WAL : Écriture continue du journal transactionnel
Synchronisation : fsync, sync des données sur disque

Symptômes d'une saturation I/O

# Vérifier les I/O par disque
iostat -x 1 5
# Colonnes importantes :
# - %util : Utilisation du disque (> 80% = saturé)
# - await : Latence moyenne (> 10ms = lent)
# - r/s, w/s : Opérations lecture/écriture par seconde

# Voir les processus gourmands en I/O
iotop

# Statistiques de disque détaillées
dstat --disk --io --filesystem

Signes d'alerte :

%util constamment > 80-90%
Latence d'I/O (await) > 10-20ms pour SSD, > 50ms pour HDD
Queue depth élevée (beaucoup d'opérations en attente)

Diagnostiquer : Identifier les sources d'I/O

Méthode 1 : Via pg_stat_database (statistiques globales)

SELECT
    datname,
    blks_read,          -- Blocs lus depuis le disque
    blks_hit,           -- Blocs lus depuis le cache
    tup_returned,       -- Lignes retournées
    tup_fetched,        -- Lignes réellement récupérées
    tup_inserted,       -- Insertions
    tup_updated,        -- Mises à jour
    tup_deleted,        -- Suppressions
    blk_read_time,      -- Temps passé à lire (si track_io_timing = on)
    blk_write_time      -- Temps passé à écrire
FROM pg_stat_database  
WHERE datname = current_database();

Activer le tracking du temps I/O :

-- Dans postgresql.conf
track_io_timing = on
-- Puis recharger : SELECT pg_reload_conf();

Méthode 2 : Via pg_statio_user_tables (I/O par table)

SELECT
    schemaname,
    tablename,
    heap_blks_read,     -- Blocs lus depuis disque
    heap_blks_hit,      -- Blocs lus depuis cache
    idx_blks_read,      -- Index lus depuis disque
    idx_blks_hit,       -- Index lus depuis cache
    toast_blks_read,    -- TOAST lu depuis disque
    toast_blks_hit,     -- TOAST lu depuis cache
    CASE
        WHEN heap_blks_read + heap_blks_hit > 0
        THEN round(100.0 * heap_blks_hit / (heap_blks_read + heap_blks_hit), 2)
        ELSE 0
    END AS cache_hit_ratio
FROM pg_statio_user_tables  
ORDER BY heap_blks_read DESC  
LIMIT 10;

Interprétation :

Tables avec heap_blks_read élevé → Causent beaucoup de lectures disque
cache_hit_ratio < 95% → Problème de cache, considérez augmenter shared_buffers

Méthode 3 : Via pg_stat_statements avec I/O timing

SELECT
    query,
    calls,
    total_exec_time,
    blk_read_time,      -- Temps I/O lecture (ms)
    blk_write_time,     -- Temps I/O écriture (ms)
    shared_blks_read,   -- Blocs lus depuis disque
    shared_blks_hit,    -- Blocs trouvés en cache
    round(
        100.0 * shared_blks_hit / NULLIF(shared_blks_read + shared_blks_hit, 0),
        2
    ) AS cache_hit_ratio
FROM pg_stat_statements  
WHERE blk_read_time > 0  
ORDER BY blk_read_time + blk_write_time DESC  
LIMIT 10;

Les requêtes avec blk_read_time élevé sont celles qui attendent le disque.

Méthode 4 : Analyser les checkpoints

Les checkpoints synchronisent la mémoire vers le disque et peuvent causer des pics d'I/O :

-- Voir les statistiques de checkpoints
SELECT
    checkpoints_timed,      -- Checkpoints planifiés
    checkpoints_req,        -- Checkpoints forcés (mauvais signe)
    checkpoint_write_time,  -- Temps d'écriture (ms)
    checkpoint_sync_time,   -- Temps de sync (ms)
    buffers_checkpoint,     -- Buffers écrits
    buffers_clean,          -- Buffers nettoyés par bgwriter
    buffers_backend,        -- Buffers écrits directement par backends
    buffers_backend_fsync   -- fsync par backends (très mauvais)
FROM pg_stat_bgwriter;

Analyser les résultats :

checkpoints_req élevé → Checkpoints trop fréquents, augmentez max_wal_size
buffers_backend_fsync > 0 → Problème grave, backends attendent le disque

Dans les logs PostgreSQL (avec log_checkpoints = on) :

LOG:  checkpoint starting: time  
LOG:  checkpoint complete: wrote 16384 buffers (25.0%);  
      0 WAL file(s) added, 0 removed, 5 recycled;
      write=25.123 s, sync=0.456 s, total=25.634 s;
      sync files=165, longest=0.123 s, average=0.003 s

Méthode 5 : Surveiller le WAL

Le WAL (Write-Ahead Log) est critique pour les performances d'écriture :

-- Volume de WAL généré
SELECT
    pg_current_wal_lsn(),
    pg_walfile_name(pg_current_wal_lsn());

-- Nouveauté PG 18 : Statistiques I/O et WAL par backend
SELECT
    pid,
    usename,
    query,
    backend_type,
    wal_bytes,          -- Octets de WAL générés
    wal_records,        -- Nombre d'enregistrements WAL
    wal_fpi             -- Full Page Images
FROM pg_stat_activity  
WHERE backend_type = 'client backend'  
ORDER BY wal_bytes DESC  
LIMIT 10;

Résoudre une saturation I/O

Solution 1 : Augmenter le cache pour réduire les lectures

-- Augmenter shared_buffers (nécessite redémarrage)
-- Dans postgresql.conf
shared_buffers = 16GB  -- Ajustez selon votre RAM

Solution 2 : Optimiser les checkpoints

-- Dans postgresql.conf
max_wal_size = 4GB              -- Augmente l'intervalle entre checkpoints  
checkpoint_timeout = 15min      -- Augmente le délai maximum  
checkpoint_completion_target = 0.9  -- Étaler les écritures sur 90% de l'intervalle

Effet : Checkpoints moins fréquents et plus étalés = moins de pics d'I/O.

Solution 3 : Séparer les WAL sur un disque dédié

Si possible, placez les WAL sur un disque SSD séparé :

# Déplacer le répertoire pg_wal
# 1. Arrêter PostgreSQL
sudo systemctl stop postgresql

# 2. Déplacer pg_wal vers disque rapide
sudo mv /var/lib/postgresql/data/pg_wal /mnt/fast-ssd/pg_wal

# 3. Créer un lien symbolique
sudo ln -s /mnt/fast-ssd/pg_wal /var/lib/postgresql/data/pg_wal

# 4. Redémarrer PostgreSQL
sudo systemctl start postgresql

Solution 4 : Utiliser FILLFACTOR pour réduire les UPDATE

Les UPDATE en PostgreSQL créent de nouvelles versions de lignes. Avec FILLFACTOR, vous réservez de l'espace pour ces mises à jour :

-- Table avec beaucoup d'UPDATE
ALTER TABLE users SET (fillfactor = 80);

-- Reconstruire la table pour appliquer
VACUUM FULL users;
-- ou
CLUSTER users USING users_pkey;

Explication : Chaque page de données gardera 20% d'espace libre pour les futures mises à jour, réduisant le besoin de créer de nouvelles pages.

Solution 5 : Partitionnement pour distribuer l'I/O

Partitionner de grandes tables peut améliorer les performances I/O :

-- Exemple : Partitionnement par date
CREATE TABLE logs (
    id SERIAL,
    message TEXT,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Créer des partitions
CREATE TABLE logs_2024_11 PARTITION OF logs
    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

CREATE TABLE logs_2024_12 PARTITION OF logs
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

Avantage : Les requêtes sur une seule partition lisent moins de données.

Solution 6 : Utiliser des tablespaces

Distribuez les tables sur différents disques physiques :

-- Créer un tablespace sur un disque rapide
CREATE TABLESPACE fast_storage
    LOCATION '/mnt/nvme-ssd/pgdata';

-- Déplacer une table gourmande en I/O
ALTER TABLE large_table SET TABLESPACE fast_storage;

-- Déplacer les index d'une table
ALTER INDEX large_table_pkey SET TABLESPACE fast_storage;

Solution 7 : Compression et TOAST

Pour réduire les I/O, compressez les données volumineuses :

-- Activer la compression pour colonne volumineuse
ALTER TABLE documents
    ALTER COLUMN content SET STORAGE EXTENDED;  -- Compression + TOAST

-- Ou compression uniquement
ALTER TABLE documents
    ALTER COLUMN content SET STORAGE MAIN;  -- Compression, pas de TOAST

Nouveauté PG 18 : Algorithmes de compression améliorés.

Solution 8 : VACUUM et maintenance régulière

Un VACUUM régulier réduit le bloat et améliore l'I/O :

-- VACUUM manuel sur table problématique
VACUUM ANALYZE large_table;

-- Vérifier l'autovacuum
SELECT
    schemaname,
    tablename,
    last_vacuum,
    last_autovacuum,
    n_dead_tup,     -- Lignes mortes
    n_live_tup      -- Lignes vivantes
FROM pg_stat_user_tables  
WHERE n_dead_tup > 10000  
ORDER BY n_dead_tup DESC;

Configurer autovacuum plus agressif :

-- Dans postgresql.conf
autovacuum_max_workers = 6  
autovacuum_naptime = 10s  -- Vérifier toutes les 10 secondes  

-- Nouveauté PG 18 : Ajustement dynamique
autovacuum_vacuum_max_threshold = 1000000

Solution 9 : Upgrade vers SSD NVMe

Si vous êtes sur HDD :

HDD : ~100-200 IOPS, latence 5-10ms
SSD SATA : ~10,000-50,000 IOPS, latence < 1ms
SSD NVMe : ~100,000-1,000,000 IOPS, latence < 0.1ms

Un upgrade matériel peut résoudre radicalement les problèmes d'I/O.

Solution 10 : Activer l'I/O asynchrone (Nouveauté PG 18)

-- Dans postgresql.conf
io_method = 'worker'  -- Utilise les I/O asynchrones (AIO)

-- Redémarrage nécessaire

Avantage : Jusqu'à 3× plus rapide pour les opérations I/O intensives.

Surveiller les ressources en temps réel

Script de monitoring global

Voici un script SQL utile pour avoir une vue d'ensemble :

-- Vue globale : CPU, RAM, I/O
WITH cpu_stats AS (
    SELECT
        count(*) FILTER (WHERE state = 'active') AS active_queries,
        count(*) AS total_connections,
        max(EXTRACT(EPOCH FROM (now() - query_start))) AS longest_query_sec
    FROM pg_stat_activity
    WHERE pid != pg_backend_pid()
),
cache_stats AS (
    SELECT
        round(
            100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0),
            2
        ) AS cache_hit_ratio
    FROM pg_statio_user_tables
),
io_stats AS (
    SELECT
        sum(blk_read_time) AS total_read_time_ms,
        sum(blk_write_time) AS total_write_time_ms
    FROM pg_stat_statements
)
SELECT
    cpu.active_queries,
    cpu.total_connections,
    round(cpu.longest_query_sec::numeric, 2) AS longest_query_sec,
    cache.cache_hit_ratio,
    io.total_read_time_ms,
    io.total_write_time_ms,
    pg_size_pretty(pg_database_size(current_database())) AS db_size
FROM cpu_stats cpu, cache_stats cache, io_stats io;

Outils de monitoring externes

1. Prometheus + Grafana

# Installer postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz  
tar xvfz postgres_exporter-0.15.0.linux-amd64.tar.gz  
cd postgres_exporter-0.15.0.linux-amd64  

# Configurer la connexion
export DATA_SOURCE_NAME="postgresql://user:password@localhost:5432/postgres?sslmode=disable"

# Lancer
./postgres_exporter

Dashboards Grafana recommandés :

PostgreSQL Database (ID: 9628)
PostgreSQL Overview (ID: 12485)

2. pgBadger (Analyse de logs)

# Installer pgBadger
sudo apt install pgbadger

# Configurer les logs dans postgresql.conf
log_min_duration_statement = 0  # Log toutes les requêtes  
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '  
log_checkpoints = on  
log_connections = on  
log_disconnections = on  
log_lock_waits = on  
log_temp_files = 0  

# Analyser les logs
pgbadger /var/log/postgresql/postgresql-*.log -o /tmp/report.html

# Ouvrir le rapport
firefox /tmp/report.html

3. pg_top (Monitoring en temps réel)

# Installer pg_top
sudo apt install pgtop

# Lancer
pg_top -h localhost -U postgres -d mydb

# Interface interactive comme 'top' pour PostgreSQL

Tableau récapitulatif : Symptômes et solutions

Ressource	Symptôme principal	Diagnostic	Solution rapide
CPU	Lenteur généralisée, CPU > 90%	`pg_stat_statements` ordre par `total_exec_time`	Optimiser requêtes lentes, ajouter index
RAM	Swap utilisé, OOM killer	`free -h`, cache hit ratio < 95%	Augmenter `shared_buffers`, optimiser `work_mem`
I/O	Latence élevée, `%util` > 80%	`iostat -x`, `pg_statio_user_tables`	Augmenter cache, optimiser checkpoints, SSD

Configuration de référence pour éviter les saturations

Pour un serveur avec 32GB RAM, 8 cœurs CPU, SSD

# postgresql.conf - Configuration optimisée

# CONNEXIONS
max_connections = 100  
superuser_reserved_connections = 5  

# MÉMOIRE
shared_buffers = 8GB                    # 25% de RAM  
work_mem = 64MB                         # À ajuster selon workload  
maintenance_work_mem = 2GB              # Pour VACUUM, CREATE INDEX  
effective_cache_size = 24GB             # 75% de RAM  

# WAL
wal_buffers = 16MB  
max_wal_size = 4GB  
min_wal_size = 1GB  
checkpoint_timeout = 15min  
checkpoint_completion_target = 0.9  

# I/O
effective_io_concurrency = 200          # Pour SSD  
random_page_cost = 1.1                  # Pour SSD (4.0 pour HDD)  
io_method = 'worker'                     # Nouveauté PG 18  

# QUERY TUNING
default_statistics_target = 100  
track_io_timing = on  

# AUTOVACUUM
autovacuum = on  
autovacuum_max_workers = 4  
autovacuum_naptime = 10s  

# LOGGING (pour debugging)
log_min_duration_statement = 1000       # Log requêtes > 1s  
log_checkpoints = on  
log_connections = on  
log_disconnections = on  
log_lock_waits = on  
log_temp_files = 0

Checklist de diagnostic rapide

Quand un problème de performance survient, suivez ces étapes :

1. Identifier la ressource saturée (2 minutes)

# CPU
top  
ps aux | grep postgres | head -10  

# RAM
free -h  
cat /proc/meminfo | grep -i swap  

# I/O
iostat -x 1 3

2. Identifier les requêtes problématiques (5 minutes)

-- Requêtes actives longues
SELECT pid, usename, query, (now() - query_start) AS duration  
FROM pg_stat_activity  
WHERE state = 'active' AND query NOT LIKE '%pg_stat%'  
ORDER BY duration DESC;  

-- Top requêtes cumulées
SELECT query, calls, total_exec_time, mean_exec_time  
FROM pg_stat_statements  
ORDER BY total_exec_time DESC  
LIMIT 5;

3. Vérifier la santé générale (3 minutes)

-- Cache hit ratio
SELECT round(
    100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0),
    2
) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Connexions
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Locks bloquants
SELECT count(*) FROM pg_stat_activity  
WHERE cardinality(pg_blocking_pids(pid)) > 0;

4. Analyser et agir (temps variable)

Si CPU saturé → Analyser EXPLAIN, ajouter index
Si RAM saturée → Vérifier swap, ajuster work_mem, limiter connexions
Si I/O saturé → Augmenter cache, optimiser checkpoints, considérer SSD

Bonnes pratiques de prévention

1. Monitoring proactif

Mettez en place des alertes avant que les problèmes surviennent :

CPU > 80% pendant 5 minutes
RAM disponible < 15%
Swap utilisé > 0
Cache hit ratio < 95%
Latence I/O > 10ms pour SSD

2. Tests de charge réguliers

# Utiliser pgbench pour tester la capacité
pgbench -i -s 100 mydb  # Initialiser  
pgbench -c 50 -j 4 -T 300 mydb  # 50 clients, 4 threads, 5 minutes

3. Revue de configuration trimestrielle

À mesure que votre application grandit, réévaluez :

max_connections
shared_buffers
work_mem
max_wal_size

4. Maintenance régulière

-- Planifier avec pg_cron
CREATE EXTENSION pg_cron;

-- VACUUM quotidien des grandes tables
SELECT cron.schedule('vacuum-logs', '0 2 * * *', 'VACUUM ANALYZE logs');

-- Réindexer hebdomadairement
SELECT cron.schedule('reindex', '0 3 * * 0', 'REINDEX DATABASE mydb');

Ressources complémentaires

Documentation PostgreSQL : Resource Consumption
PGTune : pgtune.leopard.in.ua - Génère une configuration optimale
explain.depesz.com : Analyseur visuel de plans EXPLAIN
Wiki PostgreSQL : Tuning Your PostgreSQL Server

Résumé des points clés

✅ CPU, RAM, I/O : Les trois piliers de la performance PostgreSQL

✅ Diagnostiquer avant d'agir : Utilisez pg_stat_statements, pg_stat_activity, iostat

✅ Cache hit ratio : Doit être > 95%, sinon augmentez shared_buffers

✅ work_mem : Ajustez avec précaution, peut exploser la consommation RAM

✅ Checkpoints : Étalez-les (max_wal_size, checkpoint_completion_target)

✅ I/O asynchrone (PG 18) : Activation simple, gains majeurs

✅ Swap = ennemi : Désactivez-le ou minimisez-le (vm.swappiness=1)

✅ Monitoring continu : Prometheus, Grafana, pgBadger pour anticiper les problèmes

✅ Tests de charge : Validez votre configuration sous charge réaliste

Conclusion

La saturation des ressources est l'une des causes les plus fréquentes de dégradation de performance dans PostgreSQL. En comprenant comment diagnostiquer et résoudre les saturations de CPU, RAM et I/O, vous êtes maintenant équipé pour :

Identifier rapidement la ressource problématique
Diagnostiquer les requêtes ou configurations responsables
Appliquer les solutions appropriées (optimisation, configuration, matériel)
Prévenir les futurs problèmes par monitoring et maintenance

N'oubliez pas : mesurez avant d'optimiser, et surveillez après avoir changé. Les performances de base de données sont un processus itératif d'amélioration continue.

Prochaine étape : 19.4.3 - Transaction Wraparound (XID exhaustion) et prévention

⏭️ Transaction Wraparound (XID exhaustion) et prévention

FilesExpand file tree

04.2-saturation-ressources.md

Latest commit

History

04.2-saturation-ressources.md

File metadata and controls

19.4.2. Saturation des ressources (CPU, RAM, I/O)

Introduction : Les trois piliers de la performance

Comprendre la saturation : Qu'est-ce que c'est ?

Les symptômes généraux

Partie 1 : Saturation CPU

Qu'est-ce que le CPU fait dans PostgreSQL ?

Symptômes d'une saturation CPU

Diagnostiquer : Identifier les requêtes gourmandes en CPU

Méthode 1 : Via pg_stat_activity (état en temps réel)

Méthode 2 : Via pg_stat_statements (vue historique)

Méthode 3 : Via l'extension pg_stat_kcache (métriques système)

Résoudre une saturation CPU

Solution 1 : Optimiser les requêtes lentes

Solution 2 : Ajouter des index manquants

Solution 3 : Limiter les connexions actives

Solution 4 : Utiliser le parallélisme PostgreSQL

Partie 2 : Saturation RAM (Mémoire)

Rôle de la RAM dans PostgreSQL

Symptômes d'une saturation RAM

Diagnostiquer : Identifier la consommation mémoire

Méthode 1 : Paramètres de configuration PostgreSQL

Méthode 2 : Identifier les requêtes gourmandes en mémoire

Méthode 3 : Vérifier l'efficacité du cache

Méthode 4 : Analyser les temporary files

Résoudre une saturation RAM

Solution 1 : Augmenter shared_buffers

Solution 2 : Augmenter work_mem (avec précaution)

Solution 3 : Optimiser les requêtes pour réduire l'usage mémoire

Solution 4 : Réduire le nombre de connexions

Solution 5 : Désactiver le swap pour PostgreSQL

Partie 3 : Saturation I/O (Disque)

Rôle de l'I/O dans PostgreSQL

Types d'opérations I/O

Symptômes d'une saturation I/O

Diagnostiquer : Identifier les sources d'I/O

Méthode 1 : Via pg_stat_database (statistiques globales)

Méthode 2 : Via pg_statio_user_tables (I/O par table)

Méthode 3 : Via pg_stat_statements avec I/O timing

Méthode 4 : Analyser les checkpoints

Méthode 5 : Surveiller le WAL

Résoudre une saturation I/O

Solution 1 : Augmenter le cache pour réduire les lectures

Solution 2 : Optimiser les checkpoints

Solution 3 : Séparer les WAL sur un disque dédié

Solution 4 : Utiliser FILLFACTOR pour réduire les UPDATE

Solution 5 : Partitionnement pour distribuer l'I/O

Solution 6 : Utiliser des tablespaces

Solution 7 : Compression et TOAST

Solution 8 : VACUUM et maintenance régulière

Solution 9 : Upgrade vers SSD NVMe

Solution 10 : Activer l'I/O asynchrone (Nouveauté PG 18)

Surveiller les ressources en temps réel

Script de monitoring global

Outils de monitoring externes

1. Prometheus + Grafana

2. pgBadger (Analyse de logs)

3. pg_top (Monitoring en temps réel)

Tableau récapitulatif : Symptômes et solutions

Configuration de référence pour éviter les saturations

Pour un serveur avec 32GB RAM, 8 cœurs CPU, SSD

Checklist de diagnostic rapide

1. Identifier la ressource saturée (2 minutes)

2. Identifier les requêtes problématiques (5 minutes)

3. Vérifier la santé générale (3 minutes)

4. Analyser et agir (temps variable)

Bonnes pratiques de prévention

1. Monitoring proactif

2. Tests de charge réguliers

3. Revue de configuration trimestrielle

4. Maintenance régulière

Ressources complémentaires

Résumé des points clés

Conclusion