Slow Query Log

Le Slow Query Log enregistre toutes les requêtes dépassant un seuil de durée configuré — outil essentiel pour identifier requêtes problematic en production et prioritiser optimization work.

Par SGBD :
- **MySQL/MariaDB** : `slow_query_log = 1`, `slow_query_log_file = /var/log/mysql/slow.log`, `long_query_time = 1` (seconds), `log_queries_not_using_indexes = 1`, `min_examined_row_limit = 100`. Tool d'analyse : `pt-query-digest` (Percona Toolkit) — aggregate slow log, ranking queries par total time impact.
- **PostgreSQL** : `log_min_duration_statement = 1000` (ms) — log queries plus de 1s, dans postgres log standard. Better : extension `pg_stat_statements` — aggregate stats sur toutes queries (not just slow), top by total time, mean time, calls count.
- **SQL Server** : Extended Events ou Query Store (depuis 2016) — capture queries automatique, dashboard top consumers.
- **MongoDB** : Profiler (`db.setProfilingLevel(1, { slowms: 100 })`), system.profile collection.
- **Elasticsearch** : slow log per index, search et indexing thresholds.
- **Oracle** : AWR (Automatic Workload Repository), v_active_session_history view.

Metrics à surveiller :
(1) **Total time** — query qui run 0.5s mais 10000 fois/jour = 5000s total, plus impact que query 5s qui run 10 fois.
(2) **Mean time** — slow individuel queries.
(3) **Rows examined / rows returned** — high ratio = inefficient (full scan when index could help).
(4) **Locks waited** — contention.

Workflow optimization :
(1) **Aggregate** slow log par query template (avec pt-query-digest ou pg_stat_statements).
(2) **Identify top 10** par total time impact.
(3) For each : EXPLAIN → identify issue (missing index, bad join, ineffective filter).
(4) **Add index** ou rewrite query.
(5) **Verify** improvement (re-EXPLAIN, monitor real prod).
(6) **Avoid premature optimization** — focus on actual bottlenecks via logs vs guessing.

Production caveat : enable slow log adds overhead — log file growth concern (rotate carefully). pg_stat_statements low overhead recommandé. Compétences DP-300.