Guide complet d'optimisation des performances des bases de données — EXPLAIN, query planning, statistiques, paramètres mémoire, parallélisme, connection pooling, caching, et monitoring.
Guide complet d'optimisation des performances des bases de données — EXPLAIN, query planning, statistiques, paramètres mémoire, parallélisme, connection pooling, caching, et monitoring.
Compétence Optimisation des Performances des Bases de Données
Vue d'ensemble
L'optimisation des performances d'une base de données est un processus itératif : mesurer → identifier le goulot → corriger → mesurer à nouveau. Cette compétence couvre l'analyse des plans d'exécution, la configuration mémoire, le parallélisme, le connection pooling, le caching, le monitoring, et l'optimisation continue.
Quand l'utiliser
Activez cette compétence lorsque l'utilisateur :
Se plaint de requêtes lentes (pages qui chargent en > 1s)
Doit optimiser les configurations mémoire d'une base existante
Veut configurer un pool de connexions (PgBouncer, ProxySQL)
A besoin de mettre en place un cache (Redis, memcached)
Veut monitorer les performances (pg_stat_statements, PMM, slow query log)
Doit dimensionner une nouvelle instance pour un workload connu
1. Analyse des Requêtes Lentes
1.1 PostgreSQL — EXPLAIN
-- Plan d'exécution de base
EXPLAIN (ANALYZE, BUFFERS, TIMING, SETTINGS)
SELECT m.*, c.nom
FROM mesures m
JOIN capteurs c ON m.capteur_id = c.id
WHERE m.timestamp >='2025-06-01'AND m.temperature >100ORDERBY m.timestamp DESC
LIMIT 100;
Lire un plan EXPLAIN :
Nœud
Signification
Action
Seq Scan
Parcours complet de la table
Ajouter un index
Index Scan
Recherche dans l'index + lecture table
Vérifier si covering possible
Index Only Scan
Tout dans l'index, pas de lecture table
Excellent !
Bitmap Heap Scan
Construction d'un bitmap + scan
Moyen, souvent améliorable
Nested Loop
Boucle pour chaque ligne externe
Bon si peu de lignes externes
Hash Join
Table hachée en mémoire
Bon pour des jointures size > 100k
Merge Join
Tri + fusion
Bon si déjà trié
Sort
Tri explicite
Ajouter un index ou ORDER BY couvert
Materialize
Matérialisation d'un CTE/Subquery
Souvent signe de mauvaise optimisation
1.2 MySQL — EXPLAIN
EXPLAIN ANALYZE
SELECT m.*, c.nom
FROM mesures m
JOIN capteurs c ON m.capteur_id = c.id
WHERE m.timestamp >='2025-06-01'AND m.temperature >100ORDERBY m.timestamp DESC;
Lecture :
type : ALL = table scan, ref = index lookup, eq_ref = PK lookup, range = range scan
rows : estimation du nombre de lignes examinées
Extra : Using index = covering, Using filesort = tri disque, Using temporary = table temporaire
import redis, json, time
r = redis.Redis(host='localhost', port=6379, decode_responses=True)
defget_capteur(capteur_id, force_refresh=False):
cache_key = f"capteur:{capteur_id}"# Refresh forcé (invalidation)if force_refresh:
r.delete(cache_key)
# Cache-aside pattern
cached = r.get(cache_key)
if cached isnotNoneandnot force_refresh:
return json.loads(cached)
start = time.time()
data = query_db(f"SELECT * FROM capteurs WHERE id = {capteur_id}")
query_time = (time.time() - start) * 1000# Mettre en cache seulement si la requête était lenteif query_time > 100: # > 100ms
r.setex(cache_key, 300, json.dumps(data))
return data
6.2 Cache du plans (Prepared Statements)
-- PostgreSQL : cache des plans-- PREPARE + EXECUTE évite de re-planifierPREPARE capteur_query(INT) ASSELECT*FROM capteurs WHERE id = $1;
EXECUTE capteur_query(42);
-- Le plan est mis en cache pour les 5 premières exécutions-- (configurable avec plan_cache_mode = force_custom_plan)
7. Statistiques et Autovacuum
7.1 PostgreSQL — Statistiques
-- Vérifier les statistiquesSELECT
attname,
n_distinct, -- cardinalité estimée
correlation, -- corrélation physique (1.0 = parfaitement trié)
null_frac, -- fraction de NULLs
avg_width -- taille moyenne en bytesFROM pg_stats
WHERE tablename ='mesures'ORDERBY attname;
7.2 Autovacuum tuning
# postgresql.confautovacuum = onautovacuum_max_workers = 4autovacuum_naptime = '1min'autovacuum_vacuum_threshold = 50autovacuum_vacuum_scale_factor = 0.01# 1% de lignes mortesautovacuum_analyze_scale_factor = 0.005# 0.5% de changementsautovacuum_vacuum_cost_limit = 2000autovacuum_vacuum_cost_delay = 2# ms# Per-table override pour les tables chaudes
ALTER TABLE mesures SET (
autovacuum_vacuum_scale_factor = 0.005,
autovacuum_analyze_scale_factor = 0.001,
autovacuum_vacuum_cost_limit = 5000
);
8. Monitoring
8.1 PostgreSQL — pg_stat_statements
CREATE EXTENSION IF NOTEXISTS pg_stat_statements;
-- Top 10 des requêtes par temps totalSELECT
queryid,
ROUND(total_exec_time::NUMERIC/1000, 2) AS total_sec,
ROUND(mean_exec_time::NUMERIC, 2) AS mean_ms,
calls,
ROUND(100.0* shared_blks_hit / GREATEST(shared_blks_hit + shared_blks_read, 1), 1) AS cache_hit_ratio,
ROUND(rows/ GREATEST(calls, 1)) AS avg_rows,
query
FROM pg_stat_statements
WHERE query NOTLIKE'%pg_stat%'ORDERBY total_exec_time DESC
LIMIT 10;
8.2 Table de monitoring complet
-- Tableau de bord perfWITH perf AS (
SELECT
(SELECTcount(*) FROM pg_stat_activity WHERE state !='idle') AS connexions_actives,
(SELECTcount(*) FROM pg_locks WHERENOT granted) AS verrous_bloquants,
(SELECT ROUND(xact_commit::NUMERIC/ GREATEST(xact_commit + xact_rollback, 1) *100, 1)
FROM pg_stat_database WHERE datname = current_database()) AS taux_succes_transactions,
(SELECT ROUND(100.0*sum(heap_blks_hit) / GREATEST(sum(heap_blks_hit) +sum(heap_blks_read), 1), 1)
FROM pg_statio_user_tables) AS cache_hit_ratio
)
SELECT*FROM perf;
8.3 Outils de monitoring
SGBD
Outil
Commande
PostgreSQL
pg_stat_statements
Intégré
PostgreSQL
pg_top
pg_top
PostgreSQL
pgBadger
pgbadger /var/log/postgresql/postgresql*.log
MySQL
PMM (Percona)
docker run percona/pmm-server
MySQL
MySQLTuner
mysqltuner
MySQL
innotop
innotop
MongoDB
mongostat
mongostat -h localhost:27017
MongoDB
mongotop
mongotop -h localhost:27017
Redis
redis-cli info
redis-cli INFO stats
Tout
Prometheus + Grafana
Exporters dédiés
Pièges Courants
work_mem trop grand.work_mem est multiplié par le nombre de connexions et le nombre de tris simultanés. work_mem = 1GB avec 200 connexions = 200 GB potentiels. Un crash est probable.
Pas de connection pooling. PostgreSQL supporte mal > 200 connexions directes (fork par connexion). Toujours mettre PgBouncer ou ProxySQL en face.
Planificateur trompé par des stats obsolètes. Autovacuum insuffisant = statistiques obsolètes = mauvais plans. Vérifier last_analyze dans pg_stat_user_tables.
Requêtes N+1. 100 requêtes individuelles au lieu d'une jointure. Détectable dans le slow query log : 100 requêtes identiques dans la même seconde.
Paramètres de configuration copiés depuis internet sans validation. Les configs "magiques" (pgTune, MySQLTuner) sont des points de départ, pas des réponses absolues. Tester avec votre workload réel.
Oublier que le cache OS existe.effective_cache_size ne réserve PAS de mémoire — il informe le planner de la taille du cache OS. Le fixer trop bas = sous-estimation des index-only scans.
Checklist
EXPLAIN ANALYZE vérifié sur les requêtes les plus lentes (top 10)
Slow query log activé avec seuil à 500ms (500ms)
shared_buffers configuré à 25% RAM, effective_cache_size à 75%
Connection pooling en place (PgBouncer/ProxySQL)
pg_stat_statements actif et monitoré
Statistiques autovacuum à jour (last_analyze < 1 jour)
Index inutilisés identifiés et supprimés
Cache Redis/memcached en amont des requêtes fréquentes
Prepared statements utilisées pour les requêtes répétitives
Benchmark établi avant/après chaque optimisation majeure
