🔝 Retour au Sommaire

14.7.2. pg_stat_kcache : Métriques système (CPU, I/O)

Introduction

Lorsque vous optimisez une base de données PostgreSQL, comprendre les requêtes SQL lentes ne suffit pas. Il faut aussi savoir comment ces requêtes utilisent les ressources système : processeur (CPU), mémoire, et entrées/sorties disque (I/O).

pg_stat_kcache est une extension PostgreSQL qui comble précisément ce besoin en enrichissant les statistiques de requêtes avec des métriques système au niveau du noyau (kernel).

Le problème sans pg_stat_kcache

Imaginez cette situation :

-- Requête A : 100ms
SELECT * FROM orders WHERE date > NOW() - INTERVAL '1 day';

-- Requête B : 100ms
SELECT * FROM products WHERE stock = 0;

Les deux requêtes prennent 100ms. Sont-elles équivalentes en termes de charge système ?

Non ! L'une peut être :

CPU-intensive : Calculs complexes, tris en mémoire
I/O-intensive : Lecture massive depuis le disque

Sans pg_stat_kcache, vous ne savez pas quelle ressource est réellement consommée. Vous optimiseriez à l'aveugle.

La solution : pg_stat_kcache

pg_stat_kcache ajoute à chaque requête trackée des métriques comme :

⏱️ Temps CPU (user + system)
💾 I/O physiques (lectures et écritures disque)
📊 Page faults (défauts de page)

Ainsi, vous pouvez distinguer :

Requête A : 100ms = 90ms CPU + 10ms I/O wait → Optimiser l'algorithme  
Requête B : 100ms = 10ms CPU + 90ms I/O wait → Ajouter un index, augmenter la RAM

pg_stat_kcache transforme les statistiques SQL en diagnostics système exploitables.

Qu'est-ce que pg_stat_kcache ?

Définition

pg_stat_kcache (PostgreSQL Statistics Kernel Cache) est une extension PostgreSQL qui :

S'intègre à pg_stat_statements (l'extension standard de statistiques de requêtes)
Récupère des métriques système au niveau kernel via les appels système Linux
Associe ces métriques à chaque requête SQL exécutée

Architecture

┌─────────────────────────────────────────────┐
│        Application / Utilisateur            │
└──────────────────┬──────────────────────────┘
                   │ Requête SQL
                   ▼
┌─────────────────────────────────────────────┐
│            PostgreSQL Server                │
│                                             │
│  ┌──────────────────────────────────┐       │
│  │     pg_stat_statements           │       │
│  │  (Statistiques de requêtes)      │       │
│  │  • Temps d'exécution             │       │
│  │  • Nombre d'appels               │       │
│  │  • Plans d'exécution             │       │
│  └──────────────┬───────────────────┘       │
│                 │                           │
│  ┌──────────────▼───────────────────┐       │
│  │       pg_stat_kcache             │       │
│  │  (Métriques système)             │       │
│  │  • Temps CPU                     │       │
│  │  • I/O disque                    │       │
│  │  • Page faults                   │       │
│  └──────────────┬───────────────────┘       │
└─────────────────┼───────────────────────────┘
                  │
                  ▼
┌─────────────────────────────────────────────┐
│         Kernel Linux (Système)              │
│  • Compteurs CPU (getrusage)                │
│  • Statistiques I/O (/proc/[pid]/io)        │
│  • Métriques mémoire                        │
└─────────────────────────────────────────────┘

Pourquoi "kcache" ?

Le nom kcache vient de "kernel cache", car l'extension interroge les statistiques du noyau système (kernel) pour obtenir des informations sur :

L'utilisation du cache système
Les accès disque réels (I/O physiques)
Le temps CPU consommé

Concepts fondamentaux : Comprendre les métriques

Avant de plonger dans pg_stat_kcache, comprenons les métriques système qu'il expose.

1. Temps CPU (CPU Time)

Le temps CPU représente le temps réellement utilisé par le processeur pour exécuter des instructions.

User Time vs System Time

Le temps CPU se divise en deux catégories :

Type	Description	Exemple d'opérations
User Time	Temps passé en mode utilisateur (code applicatif)	Tri de données, calculs, jointures, agrégations
System Time	Temps passé en mode noyau (appels système)	Lecture/écriture fichiers, allocation mémoire, gestion réseau

Formule :

Temps CPU total = User Time + System Time

Temps CPU vs Temps réel (Wall-clock time)

Distinction importante :

Temps réel : Temps écoulé du début à la fin de la requête (ce que vous voyez avec \timing dans psql)
Temps CPU : Temps où le CPU a réellement travaillé pour cette requête

Exemple :

Requête SELECT : 1000ms de temps réel
                 100ms de temps CPU

→ 900ms d'attente : I/O disque, verrous, réseau, etc.
→ 100ms de calcul effectif par le CPU

Pourquoi cette différence ? Pendant les 900ms d'attente, le CPU ne fait rien pour cette requête. Il attend :

Que le disque lise les données
Qu'un verrou soit libéré
Qu'un réseau réponde (dans le cas de Foreign Data Wrappers)

2. I/O disque (Disk I/O)

Les I/O disque représentent les opérations de lecture et écriture physiques sur le disque.

Lectures (Reads)

Métrique	Description
Physical reads	Lectures réelles depuis le disque (lent)
Logical reads	Lectures depuis le cache mémoire (rapide)

Objectif d'optimisation : Maximiser les logical reads (cache hit) et minimiser les physical reads.

Écritures (Writes)

Type	Description
Dirty pages	Pages modifiées en mémoire, pas encore écrites
Write I/O	Écritures réelles vers le disque

Note : PostgreSQL utilise le WAL (Write-Ahead Log) pour optimiser les écritures.

Pourquoi mesurer les I/O ?

Les I/O sont le principal goulot d'étranglement des bases de données :

Opération	Latence typique
Accès RAM	~100 nanosecondes
Accès SSD	~100 microsecondes (1000× plus lent)
Accès HDD	~10 millisecondes (100 000× plus lent)

Une seule lecture disque peut coûter plus cher que 1000 opérations en RAM.

3. Page Faults (Défauts de page)

Un page fault survient lorsque PostgreSQL demande une page mémoire qui n'est pas en RAM.

Types de page faults

Type	Description	Impact
Minor page fault	La page est en mémoire mais non mappée	Faible (quelques microsecondes)
Major page fault	La page doit être chargée depuis le disque	Élevé (plusieurs millisecondes)

Indicateur :

Beaucoup de minor faults : Gestion mémoire normale
Beaucoup de major faults : Manque de RAM, thrashing possible

Résumé visuel des métriques

┌─────────────────────────────────────────────────────────┐
│              Exécution d'une requête                    │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  Temps réel (1000ms)                                    │
│  ═══════════════════════════════════════════════════    │
│                                                         │
│  ┌──────┐         ┌────┐      ┌────────┐     ┌────┐     │
│  │ CPU  │  I/O    │CPU │ Lock │  I/O   │ CPU │CPU │     │
│  │ 50ms │ wait    │20ms│ wait │  500ms │wait │30ms│     │
│  └──────┘ 200ms   └────┘ 100ms└────────┘50ms └────┘     │
│                                                         │
│  CPU total = 50 + 20 + 30 = 100ms                       │
│  I/O total = 200 + 500 = 700ms                          │
│  Wait (locks) = 100 + 50 = 150ms                        │
└─────────────────────────────────────────────────────────┘

Installation et configuration de pg_stat_kcache

Prérequis

Système d'exploitation : Linux (pg_stat_kcache utilise des APIs spécifiques Linux)
PostgreSQL : Version 9.4 ou supérieure
pg_stat_statements : Doit être installé et activé

Note : pg_stat_kcache ne fonctionne pas sur Windows ou macOS car il dépend d'interfaces Linux (/proc/[pid]/io).

Étape 1 : Installer l'extension

Sur Debian/Ubuntu

# Installer le paquet
sudo apt-get install postgresql-contrib

# Ou pour une version spécifique
sudo apt-get install postgresql-18-pg-stat-kcache

Sur Red Hat/CentOS

sudo yum install pg_stat_kcache_18

Compilation depuis les sources (si non disponible en paquet)

# Télécharger
git clone https://github.com/powa-team/pg_stat_kcache.git  
cd pg_stat_kcache  

# Compiler
make  
sudo make install

Étape 2 : Configuration PostgreSQL

Éditer le fichier postgresql.conf :

# 1. Charger les extensions au démarrage
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'

# 2. Configuration de pg_stat_statements (prérequis)
pg_stat_statements.max = 10000  
pg_stat_statements.track = all  
pg_stat_statements.track_utility = on  

# 3. Pas de configuration spécifique pour pg_stat_kcache
# (il hérite de pg_stat_statements)

Explication :

shared_preload_libraries : Charge les extensions au démarrage du serveur
pg_stat_statements.max : Nombre max de requêtes distinctes trackées
pg_stat_statements.track = all : Track toutes les requêtes (même dans les fonctions)

Étape 3 : Redémarrer PostgreSQL

# Redémarrage nécessaire pour charger les extensions
sudo systemctl restart postgresql

# Ou selon votre installation
sudo pg_ctlcluster 18 main restart

Étape 4 : Créer les extensions dans la base

Se connecter à la base de données cible :

-- Créer pg_stat_statements d'abord
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Puis créer pg_stat_kcache
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;

Vérification de l'installation

-- Vérifier que les extensions sont actives
SELECT * FROM pg_extension  
WHERE extname IN ('pg_stat_statements', 'pg_stat_kcache');  

-- Vérifier que les vues sont disponibles
\d pg_stat_kcache

Résultat attendu :

         View "public.pg_stat_kcache"
      Column       |  Type   | Collation | ...
-------------------+---------+-----------+----
 queryid           | bigint  |           |
 top               | boolean |           |
 userid            | oid     |           |
 dbid              | oid     |           |
 plan_user_time    | double  |           |
 plan_system_time  | double  |           |
 exec_user_time    | double  |           |
 exec_system_time  | double  |           |
 reads             | bigint  |           |
 writes            | bigint  |           |
 ...

Comprendre les vues et les colonnes

pg_stat_kcache expose principalement une vue : pg_stat_kcache.

Structure de la vue pg_stat_kcache

Colonnes de liaison

Colonne	Type	Description
`queryid`	bigint	Identifiant unique de la requête (hash)
`userid`	oid	OID de l'utilisateur ayant exécuté la requête
`dbid`	oid	OID de la base de données
`top`	boolean	true si requête de niveau top (pas dans une fonction)

Note : Ces colonnes permettent de joindre avec pg_stat_statements.

Colonnes de métriques CPU

Colonne	Type	Unité	Description
`plan_user_time`	double	secondes	Temps CPU user pour la phase de planification
`plan_system_time`	double	secondes	Temps CPU système pour la phase de planification
`exec_user_time`	double	secondes	Temps CPU user pour l'exécution
`exec_system_time`	double	secondes	Temps CPU système pour l'exécution

Formules dérivées :

-- Temps CPU total de planification
plan_user_time + plan_system_time

-- Temps CPU total d'exécution
exec_user_time + exec_system_time

-- Temps CPU total (plan + exec)
plan_user_time + plan_system_time + exec_user_time + exec_system_time

Colonnes de métriques I/O

Colonne	Type	Unité	Description
`reads`	bigint	octets	Nombre d'octets lus depuis le disque
`writes`	bigint	octets	Nombre d'octets écrits sur le disque
`user_time`	double	secondes	Temps CPU user total (deprecated, utiliser exec_user_time)
`system_time`	double	secondes	Temps CPU système total (deprecated)

Conversion en unités lisibles :

-- Convertir les octets en MB
reads / (1024.0 * 1024.0) AS reads_mb  
writes / (1024.0 * 1024.0) AS writes_mb  

-- Convertir en GB
reads / (1024.0 * 1024.0 * 1024.0) AS reads_gb

Jointure avec pg_stat_statements

La vraie puissance de pg_stat_kcache vient de sa combinaison avec pg_stat_statements :

SELECT
    pss.query,                              -- Texte de la requête
    pss.calls,                              -- Nombre d'exécutions
    pss.mean_exec_time,                     -- Temps moyen d'exécution (ms)
    pss.total_exec_time,                    -- Temps total cumulé (ms)

    -- Métriques CPU de pg_stat_kcache
    psk.exec_user_time * 1000 AS cpu_user_ms,
    psk.exec_system_time * 1000 AS cpu_system_ms,
    (psk.exec_user_time + psk.exec_system_time) * 1000 AS cpu_total_ms,

    -- Métriques I/O de pg_stat_kcache
    psk.reads / (1024 * 1024) AS reads_mb,
    psk.writes / (1024 * 1024) AS writes_mb

FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk  
    ON pss.queryid = psk.queryid
    AND pss.userid = psk.userid
    AND pss.dbid = psk.dbid
ORDER BY pss.total_exec_time DESC  
LIMIT 20;

Cas d'usage pratiques

1. Identifier les requêtes CPU-intensives

Objectif : Trouver les requêtes qui consomment le plus de temps CPU.

SELECT
    LEFT(pss.query, 80) AS query_preview,
    pss.calls,
    ROUND(pss.mean_exec_time::numeric, 2) AS avg_time_ms,

    -- Temps CPU total (en secondes)
    ROUND((psk.exec_user_time + psk.exec_system_time)::numeric, 2) AS total_cpu_sec,

    -- Temps CPU par exécution (en millisecondes)
    ROUND((psk.exec_user_time + psk.exec_system_time) * 1000 / pss.calls, 2) AS cpu_per_call_ms,

    -- Pourcentage du temps passé en CPU
    ROUND(100.0 * (psk.exec_user_time + psk.exec_system_time) /
          (pss.total_exec_time / 1000.0), 1) AS cpu_percent

FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk USING (queryid, userid, dbid)  
WHERE pss.calls > 10  
ORDER BY (psk.exec_user_time + psk.exec_system_time) DESC  
LIMIT 15;

Interprétation :

cpu_percent	Signification	Action recommandée
> 80%	Requête CPU-bound	Optimiser l'algorithme, réduire les calculs, indexer
50-80%	Mixte CPU/I/O	Optimiser les deux aspects
< 20%	Requête I/O-bound	Optimiser les accès disque, ajouter des index, augmenter le cache

Exemple de résultat :

query_preview                                    | calls | avg_time_ms | total_cpu_sec | cpu_per_call_ms | cpu_percent
-------------------------------------------------+-------+-------------+---------------+-----------------+------------
SELECT COUNT(*) FROM orders GROUP BY product_id | 5000  | 250         | 1200          | 240             | 96

→ 96% du temps en CPU : Cette requête fait beaucoup de calculs. Solutions possibles :

Ajouter un index sur product_id
Créer une vue matérialisée avec les agrégations pré-calculées
Utiliser un index GIN si c'est pour de la recherche full-text

2. Identifier les requêtes I/O-intensives

Objectif : Trouver les requêtes qui font beaucoup d'accès disque.

SELECT
    LEFT(pss.query, 80) AS query_preview,
    pss.calls,
    ROUND(pss.mean_exec_time::numeric, 2) AS avg_time_ms,

    -- I/O total (en MB)
    ROUND((psk.reads / 1024.0 / 1024.0)::numeric, 2) AS total_reads_mb,
    ROUND((psk.writes / 1024.0 / 1024.0)::numeric, 2) AS total_writes_mb,

    -- I/O par appel (en KB)
    ROUND((psk.reads / 1024.0 / pss.calls)::numeric, 1) AS reads_per_call_kb,

    -- Ratio I/O wait
    ROUND(100.0 * (1 - (psk.exec_user_time + psk.exec_system_time) /
          (pss.total_exec_time / 1000.0)), 1) AS io_wait_percent

FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk USING (queryid, userid, dbid)  
WHERE pss.calls > 10  
ORDER BY psk.reads DESC  
LIMIT 15;

Interprétation :

io_wait_percent	Signification	Action recommandée
> 80%	I/O-bound	Ajouter des index, augmenter shared_buffers, optimiser le cache
50-80%	Mixte	Optimiser à la fois la requête et les I/O
< 20%	CPU-bound	Focus sur l'optimisation algorithmique

Exemple de résultat :

query_preview                           | calls | avg_time_ms | total_reads_mb | reads_per_call_kb | io_wait_percent
----------------------------------------+-------+-------------+----------------+-------------------+----------------
SELECT * FROM logs WHERE date > $1      | 1000  | 500         | 5000           | 5120              | 95

→ 95% du temps en attente I/O, 5MB lus par appel : Cette requête scan une grosse table sans index.

Solutions :

Créer un index sur la colonne date
Partitionner la table logs par date (Range partitioning)
Archiver les vieilles données

3. Comparer les performances avant/après optimisation

Scénario : Vous avez ajouté un index. Quel est l'impact réel ?

-- 1. Reset des statistiques AVANT optimisation
SELECT pg_stat_statements_reset();

-- 2. Exécuter les tests de charge
-- ... (votre application / tests)

-- 3. Capturer les métriques AVANT
CREATE TEMP TABLE metrics_before AS  
SELECT  
    queryid,
    query,
    calls,
    total_exec_time,
    exec_user_time + exec_system_time AS total_cpu_time,
    reads,
    writes
FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk USING (queryid, userid, dbid);  

-- 4. Appliquer l'optimisation (ex: CREATE INDEX)
CREATE INDEX idx_orders_date ON orders(date);

-- 5. Reset et re-test
SELECT pg_stat_statements_reset();
-- ... (re-exécuter les mêmes tests)

-- 6. Comparer
SELECT
    b.query,

    -- Amélioration du temps total
    ROUND(100.0 * (b.total_exec_time - a.total_exec_time) /
          b.total_exec_time, 1) AS time_improvement_pct,

    -- Amélioration CPU
    ROUND(100.0 * (b.total_cpu_time - a.total_cpu_time) /
          b.total_cpu_time, 1) AS cpu_improvement_pct,

    -- Réduction des I/O
    ROUND(100.0 * (b.reads - a.reads) / b.reads, 1) AS io_reduction_pct

FROM metrics_before b  
JOIN (  
    SELECT
        queryid, calls, total_exec_time,
        exec_user_time + exec_system_time AS total_cpu_time,
        reads, writes
    FROM pg_stat_statements pss
    JOIN pg_stat_kcache psk USING (queryid, userid, dbid)
) a USING (queryid)
WHERE b.calls > 10  
ORDER BY time_improvement_pct DESC;

Exemple de résultat :

query                                    | time_improvement_pct | cpu_improvement_pct | io_reduction_pct
-----------------------------------------+----------------------+---------------------+-----------------
SELECT * FROM orders WHERE date > $1     | 85                   | 20                  | 95

→ Interprétation :

Temps total réduit de 85% ✅
CPU réduit de 20% (faible impact, normal)
I/O réduits de 95% ✅ (impact majeur de l'index)

Conclusion : L'optimisation a principalement réduit les I/O, ce qui est cohérent avec l'ajout d'un index.

4. Détecter les requêtes avec un mauvais ratio CPU/temps réel

Objectif : Identifier les requêtes qui passent beaucoup de temps à "attendre" plutôt qu'à calculer.

SELECT
    LEFT(pss.query, 80) AS query_preview,
    pss.calls,
    ROUND(pss.mean_exec_time::numeric, 2) AS avg_time_ms,

    -- Temps CPU moyen par appel
    ROUND((psk.exec_user_time + psk.exec_system_time) * 1000 / pss.calls, 2) AS cpu_per_call_ms,

    -- Temps d'attente moyen (temps réel - temps CPU)
    ROUND(pss.mean_exec_time -
          (psk.exec_user_time + psk.exec_system_time) * 1000 / pss.calls, 2) AS wait_time_ms,

    -- Ratio d'efficacité (plus c'est haut, plus c'est efficace)
    ROUND(100.0 * (psk.exec_user_time + psk.exec_system_time) * 1000 /
          (pss.total_exec_time), 1) AS efficiency_pct

FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk USING (queryid, userid, dbid)  
WHERE pss.calls > 10  
ORDER BY efficiency_pct ASC  -- Les moins efficaces en premier  
LIMIT 20;

Interprétation :

efficiency_pct	Cause probable	Investigation
< 10%	I/O ou verrous	Vérifier les index, pg_locks, autovacuum
10-30%	I/O + calculs	Optimisation mixte
> 70%	Principalement CPU	Optimisation algorithmique

Exemple :

query_preview                        | avg_time_ms | cpu_per_call_ms | wait_time_ms | efficiency_pct
-------------------------------------+-------------+-----------------+--------------+---------------
UPDATE products SET stock = stock-$1 | 2000        | 50              | 1950         | 2.5

→ 2.5% d'efficacité : La requête passe 1950ms sur 2000ms à attendre !

Causes possibles :

Verrous (row-level locks) → Vérifier avec pg_locks
Checkpoints lents → Vérifier log_checkpoints
Disque saturé → Vérifier iostat, iotop

5. Surveiller l'évolution dans le temps

Objectif : Créer une base de données historique des métriques.

-- Créer une table d'historique
CREATE TABLE query_metrics_history (
    snapshot_time TIMESTAMP DEFAULT NOW(),
    queryid BIGINT,
    query TEXT,
    calls BIGINT,
    total_time_ms NUMERIC,
    avg_time_ms NUMERIC,
    total_cpu_sec NUMERIC,
    reads_mb NUMERIC,
    writes_mb NUMERIC
);

-- Job quotidien (via pg_cron ou cron système)
INSERT INTO query_metrics_history  
SELECT  
    NOW(),
    pss.queryid,
    LEFT(pss.query, 200),
    pss.calls,
    pss.total_exec_time,
    pss.mean_exec_time,
    psk.exec_user_time + psk.exec_system_time,
    psk.reads / 1024.0 / 1024.0,
    psk.writes / 1024.0 / 1024.0
FROM pg_stat_statements pss  
JOIN pg_stat_kcache psk USING (queryid, userid, dbid)  
WHERE pss.calls > 10;  

-- Analyse des tendances
SELECT
    DATE_TRUNC('day', snapshot_time) AS day,
    query,
    AVG(avg_time_ms) AS avg_time_trend,
    AVG(total_cpu_sec) AS avg_cpu_trend,
    AVG(reads_mb) AS avg_reads_trend
FROM query_metrics_history  
WHERE snapshot_time > NOW() - INTERVAL '30 days'  
GROUP BY 1, 2  
ORDER BY 1 DESC, 3 DESC;

Bénéfice : Détecter les régressions de performance au fil du temps.

Limitations et considérations

Limitations techniques

1. Support Linux uniquement

pg_stat_kcache utilise des interfaces spécifiques Linux :

/proc/[pid]/io pour les métriques I/O
getrusage() pour les métriques CPU

Conséquence : Ne fonctionne pas sur Windows, macOS, BSD.

Alternative : Utiliser les métriques système globales (Prometheus, node_exporter) mais sans corrélation par requête.

2. Overhead de monitoring

pg_stat_kcache ajoute un léger overhead :

Appels système supplémentaires pour chaque requête
Impact estimé : 0.5-2% de CPU supplémentaire

Recommandation : Acceptable en production, mais à évaluer selon votre contexte.

3. Précision des métriques

Les métriques sont cumulatives et approximatives :

Arrondis dus à la granularité du kernel
Possible attribution imprécise en cas de forte concurrence
Les métriques I/O peuvent ne pas refléter 100% des accès disque (cache filesystem)

Utilisation : Pour les tendances et comparaisons relatives, pas pour des mesures absolues au microseconde près.

Considérations opérationnelles

1. Taille de pg_stat_statements.max

Si pg_stat_statements.max est trop petit, les anciennes entrées sont évincées.

Conséquence : Perte d'historique, métriques incomplètes.

Recommandation :

pg_stat_statements.max = 10000  # Minimum  
pg_stat_statements.max = 50000  # Pour grandes applications

2. Reset des statistiques

Les métriques sont cumulatives depuis le dernier reset ou redémarrage.

Commandes :

-- Reset complet (pg_stat_statements + pg_stat_kcache)
SELECT pg_stat_statements_reset();

-- Attention : perte de tout l'historique !

Stratégie : Sauvegarder les métriques dans une table historique avant reset.

3. Interprétation en environnement conteneurisé

Dans Docker/Kubernetes, les métriques peuvent être faussées :

Limites CPU (cgroups) non reflétées
I/O du conteneur vs I/O de l'hôte

Recommandation : Corréler avec les métriques du conteneur (cAdvisor, Kubernetes metrics).

Intégration avec d'autres outils

1. PoWA (PostgreSQL Workload Analyzer)

PoWA est une suite d'outils qui intègre naturellement pg_stat_kcache.

Qu'est-ce que PoWA ?

PoWA collecte et visualise les métriques PostgreSQL dans le temps, incluant :

pg_stat_statements
pg_stat_kcache
pg_qualstats (statistiques sur les prédicats WHERE)
pg_wait_sampling (échantillonnage des wait events)

Installation rapide

# Installer PoWA
sudo apt-get install postgresql-18-powa

# Créer l'extension
CREATE EXTENSION powa;  
CREATE EXTENSION pg_stat_kcache;  

# Démarrer l'interface web PoWA-web
pip install powa-web  
powa-web

Interface web : Graphiques interactifs montrant l'évolution de CPU, I/O, et temps d'exécution par requête.

2. Grafana + Prometheus

Exporter les métriques pg_stat_kcache vers Prometheus.

Utiliser postgres_exporter

# Dans postgres_exporter, ajouter une query custom
- name: pg_stat_kcache
  query: |
    SELECT
      queryid,
      exec_user_time,
      exec_system_time,
      reads,
      writes
    FROM pg_stat_kcache

Visualisation Grafana :

Graphiques de CPU par requête
Graphiques d'I/O par requête
Alertes sur seuils (ex: > 10s CPU total)

3. pgBadger

pgBadger peut partiellement extraire des métriques système depuis les logs, mais c'est moins précis que pg_stat_kcache.

Complémentarité :

pgBadger : Analyse historique des logs (texte des requêtes, erreurs)
pg_stat_kcache : Métriques système temps réel, précises, par requête

Utilisation combinée :

pg_stat_kcache pour identifier les requêtes problématiques
pgBadger pour analyser les logs et le contexte d'exécution

4. Datadog, New Relic (APM)

Les outils APM peuvent ingérer les métriques pg_stat_kcache via des intégrations.

Exemple avec Datadog :

# datadog.yaml - Custom queries
custom_queries:
  - query: |
      SELECT
        query,
        calls,
        total_exec_time,
        exec_user_time + exec_system_time AS cpu_time
      FROM pg_stat_statements pss
      JOIN pg_stat_kcache psk USING (queryid, userid, dbid)
    columns:
      - name: query
        type: tag
      - name: calls
        type: gauge
      - name: cpu_time
        type: gauge

Bonnes pratiques

1. Définir des seuils d'alerte

Métrique	Seuil d'alerte	Action
CPU total par requête	> 10 secondes	Optimiser l'algorithme
I/O reads par requête	> 100 MB	Ajouter des index, revoir le cache
I/O wait %	> 80%	Problème de disque ou index manquants
Efficiency %	< 10%	Investigation approfondie (locks, I/O)

2. Automatiser la collecte

Script quotidien (via cron ou pg_cron) :

-- Sauvegarder les top 50 requêtes
INSERT INTO query_metrics_history  
SELECT NOW(), * FROM (  
    SELECT
        pss.queryid, pss.query, pss.calls, pss.total_exec_time,
        psk.exec_user_time, psk.exec_system_time, psk.reads, psk.writes
    FROM pg_stat_statements pss
    JOIN pg_stat_kcache psk USING (queryid, userid, dbid)
    ORDER BY pss.total_exec_time DESC
    LIMIT 50
) top_queries;

3. Corréler avec les métriques système

Ne jamais analyser pg_stat_kcache en isolation :

Vérifier la charge CPU globale (top, htop)
Vérifier les I/O globaux (iostat -x 1)
Vérifier la mémoire (free -h, cache hit ratio)

Exemple de corrélation :

pg_stat_kcache : 5 requêtes consomment 90% du CPU  
htop : CPU à 100%, load average = 20  
→ Conclusion : Ces 5 requêtes saturent le serveur

4. Documenter les optimisations

Template de documentation :

## Optimisation : Requête lente SELECT orders

**Date** : 2025-11-21  
**Problème identifié** :  
- Temps d'exécution : 2.5s
- CPU : 200ms (8%)
- I/O wait : 2.3s (92%)
- Reads : 500MB par appel

**Cause racine** : Scan séquentiel sur table orders (5M lignes), pas d'index sur date.

**Solution appliquée** :
CREATE INDEX idx_orders_date ON orders(created_at);

**Résultat** :
- Temps d'exécution : 150ms (94% plus rapide)
- CPU : 50ms
- I/O wait : 100ms (96% de réduction)
- Reads : 5MB par appel

5. Former les développeurs

Sensibiliser l'équipe aux métriques système :

Expliquer la différence CPU vs I/O
Montrer comment interpréter les métriques
Intégrer l'analyse dans le code review

Exemple de checklist PR :

[ ] Requêtes SQL optimisées
[ ] Métriques pg_stat_kcache vérifiées en staging
[ ] Pas de régression de performance (temps, CPU, I/O)
[ ] Index appropriés créés

Comparaison avec d'autres outils

pg_stat_kcache vs EXPLAIN ANALYZE

Critère	pg_stat_kcache	EXPLAIN ANALYZE
Usage	Monitoring continu	Analyse ponctuelle
Métriques	CPU, I/O réels	Plan d'exécution, estimations
Overhead	Très faible	Élevé (peut ralentir la requête)
Historique	Oui (cumulatif)	Non
Niveau	Toutes les requêtes	Une requête à la fois

Complémentarité :

pg_stat_kcache pour identifier les requêtes problématiques
EXPLAIN ANALYZE pour comprendre pourquoi elles sont lentes

pg_stat_kcache vs auto_explain

auto_explain : Extension qui log automatiquement le plan des requêtes lentes.

Critère	pg_stat_kcache	auto_explain
Activation	Toujours actif	Seulement si seuil dépassé
Output	Métriques SQL	Plans d'exécution dans les logs
Analyse	Requêtes SQL	Lecture des logs
Performance	Faible impact	Impact modéré

Utilisation combinée :

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache,auto_explain'

auto_explain.log_min_duration = 1000  # Log si > 1s  
auto_explain.log_analyze = on  
auto_explain.log_buffers = on

pg_stat_kcache vs outils système (iostat, top)

Critère	pg_stat_kcache	iostat/top
Granularité	Par requête SQL	Global (serveur)
Corrélation	SQL ↔ ressources	Non
Historique	Oui (cumulatif)	Non (snapshot)
Setup	Extension PostgreSQL	Outils système standard

Les deux sont nécessaires :

pg_stat_kcache : Identifier quelle requête consomme
iostat/top : Vérifier la santé globale du système

Dépannage (Troubleshooting)

Problème 1 : Extension non disponible

Symptôme :

CREATE EXTENSION pg_stat_kcache;  
ERROR:  could not open extension control file "...": No such file or directory

Causes possibles :

Extension non installée
Mauvaise version PostgreSQL

Solution :

# Vérifier disponibilité
apt search pg_stat_kcache

# Installer la bonne version
sudo apt install postgresql-18-pg-stat-kcache

Problème 2 : Vue vide

Symptôme :

SELECT * FROM pg_stat_kcache;
-- Retourne 0 lignes

Causes :

shared_preload_libraries mal configuré
Extension chargée après le démarrage
Pas d'activité depuis le démarrage

Solution :

-- Vérifier que l'extension est chargée
SHOW shared_preload_libraries;
-- Doit contenir 'pg_stat_kcache'

-- Si absent, éditer postgresql.conf et redémarrer

Problème 3 : Métriques I/O à zéro

Symptôme :

SELECT * FROM pg_stat_kcache;
-- reads et writes = 0 pour toutes les requêtes

Cause : Sur certains systèmes, /proc/[pid]/io nécessite des permissions spéciales.

Solution :

# Vérifier les permissions
sudo cat /proc/self/io

# Si erreur "Permission denied", ajuster les permissions
# (nécessite root ou modification du système)

Problème 4 : Overhead trop élevé

Symptôme : CPU du serveur augmente après activation de pg_stat_kcache.

Solution :

Réduire pg_stat_statements.max (moins de requêtes trackées)
Désactiver temporairement si nécessaire :

-- Désactiver temporairement
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';  
SELECT pg_reload_conf();  
-- (nécessite redémarrage pour effet complet)

Conclusion

Ce qu'il faut retenir

pg_stat_kcache est un outil essentiel pour comprendre la consommation réelle des ressources système par vos requêtes SQL.

Points clés :

✅ Complète pg_stat_statements avec des métriques CPU et I/O
✅ Permet de distinguer les requêtes CPU-bound vs I/O-bound
✅ Aide à prioriser les optimisations selon l'impact système
✅ Faible overhead, adapté à la production
✅ Intégration facile avec d'autres outils (PoWA, Grafana, APM)

Limitations :

⚠️ Linux uniquement
⚠️ Métriques cumulatives, pas temps réel pur
⚠️ Nécessite pg_stat_statements

Workflow recommandé

1. Identifier les requêtes lentes (pg_stat_statements)
   ↓
2. Analyser le profil système (pg_stat_kcache)
   ↓
3. Déterminer si CPU-bound ou I/O-bound
   ↓
4. Appliquer l'optimisation appropriée :
   - CPU-bound → Optimiser algorithme, index
   - I/O-bound → Index, cache, partitionnement
   ↓
5. Valider l'amélioration (comparer avant/après)
   ↓
6. Documenter et monitorer dans le temps

Prochaines étapes

Après avoir maîtrisé pg_stat_kcache, vous pouvez explorer :

PoWA : Interface graphique pour pg_stat_kcache
pg_wait_sampling : Analyse des wait events
pg_qualstats : Statistiques sur les prédicats WHERE
auto_explain : Logging automatique des plans lents
Prometheus + Grafana : Dashboards de métriques en temps réel

pg_stat_kcache transforme les statistiques de requêtes en diagnostics système exploitables. C'est un pilier de l'observabilité PostgreSQL moderne.

Ressources complémentaires

Documentation officielle

GitHub : https://github.com/powa-team/pg_stat_kcache
PoWA Project : https://powa.readthedocs.io/
pg_stat_statements : https://www.postgresql.org/docs/current/pgstatstatements.html

Communauté

PostgreSQL Performance Mailing List : pgsql-performance@postgresql.org
Reddit : r/PostgreSQL
Stack Overflow : Tag [postgresql-performance]

Glossaire

Terme	Définition
User Time	Temps CPU passé en mode utilisateur (code applicatif)
System Time	Temps CPU passé en appels système (kernel)
Wall-clock Time	Temps réel écoulé du début à la fin
I/O wait	Temps passé à attendre les opérations disque
Page fault	Défaut de page mémoire (accès à une page non chargée)
Physical reads	Lectures réelles depuis le disque
Logical reads	Lectures depuis le cache mémoire
CPU-bound	Limité par la capacité de calcul du CPU
I/O-bound	Limité par la vitesse des accès disque
Kernel	Noyau du système d'exploitation
getrusage()	Appel système pour obtenir l'utilisation des ressources

⏭️ Prometheus + postgres_exporter + Grafana

FilesExpand file tree

07.2-pg-stat-kcache.md

Latest commit

History

07.2-pg-stat-kcache.md

File metadata and controls

14.7.2. pg_stat_kcache : Métriques système (CPU, I/O)

Introduction

Le problème sans pg_stat_kcache

La solution : pg_stat_kcache

Qu'est-ce que pg_stat_kcache ?

Définition

Architecture

Pourquoi "kcache" ?

Concepts fondamentaux : Comprendre les métriques

1. Temps CPU (CPU Time)

User Time vs System Time

Temps CPU vs Temps réel (Wall-clock time)

2. I/O disque (Disk I/O)

Lectures (Reads)

Écritures (Writes)

Pourquoi mesurer les I/O ?

3. Page Faults (Défauts de page)

Types de page faults

Résumé visuel des métriques

Installation et configuration de pg_stat_kcache

Prérequis

Étape 1 : Installer l'extension

Sur Debian/Ubuntu

Sur Red Hat/CentOS

Compilation depuis les sources (si non disponible en paquet)

Étape 2 : Configuration PostgreSQL

Étape 3 : Redémarrer PostgreSQL

Étape 4 : Créer les extensions dans la base

Vérification de l'installation

Comprendre les vues et les colonnes

Structure de la vue pg_stat_kcache

Colonnes de liaison

Colonnes de métriques CPU

Colonnes de métriques I/O

Jointure avec pg_stat_statements

Cas d'usage pratiques

1. Identifier les requêtes CPU-intensives

2. Identifier les requêtes I/O-intensives

3. Comparer les performances avant/après optimisation

4. Détecter les requêtes avec un mauvais ratio CPU/temps réel

5. Surveiller l'évolution dans le temps

Limitations et considérations

Limitations techniques

1. Support Linux uniquement

2. Overhead de monitoring

3. Précision des métriques

Considérations opérationnelles

1. Taille de pg_stat_statements.max

2. Reset des statistiques

3. Interprétation en environnement conteneurisé

Intégration avec d'autres outils

1. PoWA (PostgreSQL Workload Analyzer)

Qu'est-ce que PoWA ?

Installation rapide

2. Grafana + Prometheus

Utiliser postgres_exporter

3. pgBadger

4. Datadog, New Relic (APM)

Bonnes pratiques

1. Définir des seuils d'alerte

2. Automatiser la collecte

3. Corréler avec les métriques système

4. Documenter les optimisations

5. Former les développeurs

Comparaison avec d'autres outils

pg_stat_kcache vs EXPLAIN ANALYZE

pg_stat_kcache vs auto_explain

pg_stat_kcache vs outils système (iostat, top)

Dépannage (Troubleshooting)

Problème 1 : Extension non disponible

Problème 2 : Vue vide

Problème 3 : Métriques I/O à zéro

Problème 4 : Overhead trop élevé

Conclusion