f4ffe3410a
Problème : toutes les requêtes du dashboard WHERE detected_at >= now() - INTERVAL N
faisaient un full scan car ml_detected_anomalies avait ORDER BY (src_ip) sans
partition ni index temporel.
Changements :
- 06_ml_tables.sql :
* ml_detected_anomalies : PARTITION BY toYYYYMMDD(detected_at)
→ élagage de partitions journalières sur toutes les requêtes temporelles
* INDEX idx_detected_at (minmax) → skip des granules hors plage
* INDEX idx_threat_level set(8) → skip pour countIf(threat_level = ...)
* INDEX idx_bot_name bloom_filter → skip pour bot_name != ''
* ttl_only_drop_parts = 1 → TTL par suppression de partition entière
* ml_all_scores : même traitement (PARTITION BY + 2 indexes)
- 04_mv_http_logs.sql :
* http_logs : INDEX idx_src_ip bloom_filter(0.01)
→ les requêtes WHERE src_ip = X (analysis.py, variability.py) sautent
~90% des granules sans scanner toute la plage temporelle
* INDEX idx_ja4 bloom_filter(0.01) → idem pour filtres JA4
- 05_aggregation_tables.sql :
* agg_host_ip_ja4_1h : PROJECTION proj_by_ip ORDER BY (src_ip, window_start, ...)
→ investigation_summary.py et rotation.py (WHERE src_ip = X) utilisent
automatiquement la projection au lieu de scanner tous les window_start
- 10_perf_indexes.sql (nouveau) :
* Migration ALTER TABLE pour instances existantes
* ADD INDEX + MATERIALIZE INDEX pour les 4 tables
* ADD PROJECTION + MATERIALIZE PROJECTION pour agg_host_ip_ja4_1h
* Note : PARTITION BY sur table existante nécessite recréation (documenté)
Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
114 lines
5.7 KiB
SQL
114 lines
5.7 KiB
SQL
-- =============================================================================
|
||
-- 10_perf_indexes.sql — Migrations de performance (indexes + vues matérialisées)
|
||
--
|
||
-- Ce fichier applique les optimisations de performance sur des tables existantes.
|
||
-- Il est idempotent : les ADD INDEX / ADD PROJECTION échouent silencieusement
|
||
-- si l'index existe déjà (IF NOT EXISTS non disponible pour les indexes, utiliser
|
||
-- la commande IF NOT EXISTS en ClickHouse 24+, sinon ignorer les erreurs ALREADY_EXISTS).
|
||
--
|
||
-- Usage :
|
||
-- Pour une installation fraîche, ces optimisations sont déjà incluses dans les
|
||
-- fichiers 04/05/06_*.sql. Ce fichier sert aux migrations d'instances existantes.
|
||
--
|
||
-- clickhouse-client --multiquery < 10_perf_indexes.sql
|
||
--
|
||
-- Sections :
|
||
-- 1. ml_detected_anomalies — indexes de saut temporels et sémantiques
|
||
-- 2. ml_all_scores — index de saut temporel
|
||
-- 3. http_logs — bloom filter sur src_ip et ja4
|
||
-- 4. agg_host_ip_ja4_1h — projection per-IP pour les requêtes d'investigation
|
||
-- =============================================================================
|
||
|
||
-- NOTE : PARTITION BY ne peut pas être ajouté via ALTER TABLE dans ClickHouse.
|
||
-- Pour bénéficier du partitionnement journalier sur une table existante, il faut
|
||
-- recréer la table et réinsérer les données :
|
||
--
|
||
-- RENAME TABLE ja4_processing.ml_detected_anomalies
|
||
-- TO ja4_processing.ml_detected_anomalies_old;
|
||
-- (recréer avec 06_ml_tables.sql)
|
||
-- INSERT INTO ja4_processing.ml_detected_anomalies
|
||
-- SELECT * FROM ja4_processing.ml_detected_anomalies_old;
|
||
-- DROP TABLE ja4_processing.ml_detected_anomalies_old;
|
||
|
||
|
||
-- =============================================================================
|
||
-- 1. ml_detected_anomalies — indexes de saut
|
||
-- =============================================================================
|
||
|
||
-- Index minmax sur detected_at : permet au moteur de sauter les granules
|
||
-- (blocs de 8192 lignes) dont le min/max de detected_at est en dehors
|
||
-- de la plage temporelle demandée (ex: WHERE detected_at >= now() - INTERVAL 24 HOUR).
|
||
ALTER TABLE ja4_processing.ml_detected_anomalies
|
||
ADD INDEX IF NOT EXISTS idx_detected_at detected_at TYPE minmax GRANULARITY 4;
|
||
|
||
-- Index set(8) sur threat_level : stocke au maximum 8 valeurs distinctes
|
||
-- par granule. Permet de sauter les granules qui ne contiennent pas la valeur
|
||
-- filtrée (ex: WHERE threat_level = 'CRITICAL' skip les granules sans CRITICAL).
|
||
ALTER TABLE ja4_processing.ml_detected_anomalies
|
||
ADD INDEX IF NOT EXISTS idx_threat_level threat_level TYPE set(8) GRANULARITY 4;
|
||
|
||
-- Index bloom_filter sur bot_name : skip des granules pour bot_name != ''
|
||
-- et les filtres par nom exact (ex: WHERE bot_name = 'Googlebot').
|
||
ALTER TABLE ja4_processing.ml_detected_anomalies
|
||
ADD INDEX IF NOT EXISTS idx_bot_name bot_name TYPE bloom_filter() GRANULARITY 4;
|
||
|
||
-- Matérialiser les indexes sur les données existantes (opération asynchrone).
|
||
-- ClickHouse traite ça en tâche de fond ; la table reste accessible en lecture.
|
||
ALTER TABLE ja4_processing.ml_detected_anomalies MATERIALIZE INDEX idx_detected_at;
|
||
ALTER TABLE ja4_processing.ml_detected_anomalies MATERIALIZE INDEX idx_threat_level;
|
||
ALTER TABLE ja4_processing.ml_detected_anomalies MATERIALIZE INDEX idx_bot_name;
|
||
|
||
|
||
-- =============================================================================
|
||
-- 2. ml_all_scores — index de saut temporel
|
||
-- =============================================================================
|
||
|
||
ALTER TABLE ja4_processing.ml_all_scores
|
||
ADD INDEX IF NOT EXISTS idx_detected_at detected_at TYPE minmax GRANULARITY 4;
|
||
ALTER TABLE ja4_processing.ml_all_scores
|
||
ADD INDEX IF NOT EXISTS idx_threat_level threat_level TYPE set(8) GRANULARITY 4;
|
||
|
||
ALTER TABLE ja4_processing.ml_all_scores MATERIALIZE INDEX idx_detected_at;
|
||
ALTER TABLE ja4_processing.ml_all_scores MATERIALIZE INDEX idx_threat_level;
|
||
|
||
|
||
-- =============================================================================
|
||
-- 3. http_logs — bloom filter sur src_ip et ja4
|
||
--
|
||
-- http_logs est ORDER BY (time, src_ip, dst_ip, ja4).
|
||
-- Les requêtes de type WHERE src_ip = X AND time >= ... ne peuvent pas utiliser
|
||
-- l'index de tri efficacement (src_ip n'est pas en première position).
|
||
-- Un bloom filter permet de sauter les granules qui ne contiennent pas l'IP
|
||
-- recherchée, réduisant la quantité de données lues de ~90% en pratique.
|
||
-- =============================================================================
|
||
|
||
ALTER TABLE ja4_logs.http_logs
|
||
ADD INDEX IF NOT EXISTS idx_src_ip src_ip TYPE bloom_filter(0.01) GRANULARITY 4;
|
||
ALTER TABLE ja4_logs.http_logs
|
||
ADD INDEX IF NOT EXISTS idx_ja4 ja4 TYPE bloom_filter(0.01) GRANULARITY 4;
|
||
|
||
ALTER TABLE ja4_logs.http_logs MATERIALIZE INDEX idx_src_ip;
|
||
ALTER TABLE ja4_logs.http_logs MATERIALIZE INDEX idx_ja4;
|
||
|
||
|
||
-- =============================================================================
|
||
-- 4. agg_host_ip_ja4_1h — projection per-IP
|
||
--
|
||
-- ORDER BY actuel : (window_start, src_ip, ja4, host)
|
||
-- → efficace pour les requêtes heatmap (GROUP BY window_start)
|
||
-- → inefficace pour les requêtes d'investigation (WHERE src_ip = X)
|
||
--
|
||
-- La projection stocke les données dans un ordre alternatif (src_ip en premier)
|
||
-- et est utilisée automatiquement par ClickHouse quand src_ip est filtré.
|
||
-- Coût : ~2× l'espace disque pour cette table (acceptable vu sa taille).
|
||
-- =============================================================================
|
||
|
||
ALTER TABLE ja4_processing.agg_host_ip_ja4_1h
|
||
ADD PROJECTION IF NOT EXISTS proj_by_ip (
|
||
SELECT * ORDER BY (src_ip, window_start, ja4, host)
|
||
);
|
||
|
||
-- Matérialiser la projection sur les données existantes.
|
||
ALTER TABLE ja4_processing.agg_host_ip_ja4_1h
|
||
MATERIALIZE PROJECTION proj_by_ip;
|