🐘 PostgreSQL – Monitoring & Tuning (avec PMM)

Métriques “vitales” à suivre

TOP MÉTRIQUES POSTGRESQL À SURVEILLER

1️⃣ Charge & Concurrence
- Nombre de connexions actives vs max_connections
- Sessions en attente de lock (pg_locks, wait_event)
- Longueur moyenne des transactions

2️⃣ Performance requêtes
- Temps de réponse moyen / 95e / 99e percentile
- Requêtes les plus coûteuses (pg_stat_statements)
- Nombre de requêtes / seconde (QPS)

3️⃣ Autovacuum & Bloat
- Tables les plus gonflées (bloat estimation)
- Age des transactions (wraparound risk)
- Autovacuum n_tup_ins / n_tup_upd / n_tup_del

4️⃣ I/O & Buffers
- Hit ratio du cache (shared_buffers)
- Temps de lecture disque vs RAM
- Checkpoints et taille du WAL

5️⃣ Santé globale
- Lag de réplication (si replicas)
- Erreurs dans les logs PostgreSQL
- Redémarrages du service PostgreSQL

Rôles des différentes briques

PMM ne remplace pas un APM complet, mais couvre très bien la couche base de données.

Panorama rapide

Exemples de requêtes utiles

-- Voir les requêtes longues en cours (> 30 s)
SELECT pid, usename, datname, state,
       now() - query_start AS runtime,
       query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND now() - query_start > interval '30 seconds'
ORDER BY runtime DESC;

-- Hit ratio global par base
SELECT datname,
       blks_hit, blks_read,
       round(100 * blks_hit::numeric / NULLIF(blks_hit + blks_read,0), 2) AS cache_hit_ratio
FROM pg_stat_database
ORDER BY cache_hit_ratio ASC;

-- Index jamais utilisés
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Top requêtes à optimiser

-- Top 20 requêtes par temps cumulé
SELECT query,
       calls,
       round(total_time::numeric, 2) AS total_ms,
       round(mean_time::numeric, 2) AS mean_ms,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- Top requêtes par temps moyen (avec au moins 100 appels)
SELECT query,
       calls,
       round(mean_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE calls >= 100
ORDER BY mean_time DESC
LIMIT 20;

Stratégie : trier par temps total (impact global), puis par temps moyen pour repérer les requêtes pathologiques.

auto_explain : logger des plans d’exécution

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'

# Paramétrage de base
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_verbose = on
auto_explain.log_buffers = on
auto_explain.log_timing = on

Attention à ne pas logguer toutes les requêtes en détail (coût I/O énorme). L’idée est de cibler les requêtes lentes et de pouvoir analyser leurs plans sans perturber la prod.

Surveiller & ajuster

-- Derniers vacuums/analyses par table
SELECT relname, last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

-- Tables les plus “gonflées” (exemple simple)
SELECT schemaname, relname,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Stratégie : identifier les tables qui se gonflent le plus (beaucoup de mises à jour/suppressions), ajuster les paramètres autovacuum_vacuum_* par table si nécessaire, et surveiller les temps de vacuum via PMM.

Detecter les indexes à problème

-- Index jamais scannés
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

-- Tables où il manque probablement des indexes
SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND idx_scan = 0
ORDER BY seq_scan DESC;

Un bon monitoring des indexes combine ces vues avec des dashboards (PMM, Grafana) pour suivre l’évolution dans le temps, pas juste un snapshot.

WAL & checkpoints

Checkpoints trop fréquents = I/O en dents de scie. Trop rares = recovery plus long, plus de WAL à rejouer.

checkpoint_timeout       (5min à 15min)
max_wal_size             (taille totale WAL avant checkpoint)
checkpoint_completion_target (0.7 à 0.9)

Suivre via PMM les métriques liées aux checkpoints et au WAL pour ajuster sans être à l’aveugle.

Architecture PMM

[PMM]

PostgreSQL & OS
   |
   | (exporters / agents)
   v
PMM Server (Prometheus + Grafana)
   |
   +-- UI Web (dashboards, explore, alert manager)
   |
   +-- Intégration alerting (email, Slack, webhooks...)

Enregistrer un serveur PostgreSQL

# Sur le serveur PostgreSQL
sudo pmm-admin config --server-url=https://admin:pass@pmm-server:443

# Ajouter l’instance PostgreSQL
sudo pmm-admin add postgresql \
  --username=monitoring_user \
  --password=******** \
  --query-source=pgstatmonitor \
  mypg-prod

Une fois l’instance ajoutée, les dashboards PostgreSQL de PMM commencent à se remplir automatiquement.