Votre équipe produit dit « les rapports sont lents », et ce qu’elle veut dire c’est : les tableaux de bord expirent, les exportations CSV prennent des minutes,
et le primaire MariaDB ressemble à une machine de minage de cryptomonnaie. Pendant ce temps, la latence au passage en caisse augmente et vous commencez
à recevoir ce genre de bruit de pager qui ruine les week-ends.
Ce n’est pas un mystère. Vous essayez de faire de l’analytique sur un moteur OLTP, sous des contraintes OLTP, avec des modèles de données OLTP,
en prétendant que « c’est juste du SQL ». Ce n’est pas acceptable. C’est coûteux, risqué et généralement évitable.
Pourquoi les « rapports » tuent MariaDB (et pourquoi les index ne sont pas une religion)
MariaDB (et MySQL) excellent pour les charges transactionnelles : petites lectures et écritures, index serrés,
chemins d’accès prévisibles, et modèles de concurrence façonnés par les requêtes utilisateurs. Votre application effectue beaucoup de
« récupérer une commande », « insérer un paiement », « mettre à jour une session ». Le moteur est construit pour rendre cela rapide et sûr.
Les rapports sont différents. Les rapports font des scans larges, de grosses jointures, des agrégations groupées, et des motifs proches des fenêtres
qui parcourent des millions de lignes pour résumer « tout ». Ils apparaissent aussi au pire moment :
fin de mois, fin de trimestre, lundi matin, juste quand le trafic monte en flèche.
OLTP vs analytique : ce n’est pas juste des « requêtes plus grosses »
La performance OLTP concerne la latence et la contention. La performance analytique concerne le débit et la bande passante :
à quelle vitesse peut-on scanner, décoder, filtrer et agréger les données. Ce sont des physiques différentes.
- Row store vs column store : MariaDB stocke des lignes. L’analytique veut des colonnes. Scanner 2 colonnes sur 50 ne devrait pas vous forcer à lire 50.
- Dépendance aux index : l’OLTP s’appuie sur les index. L’analytique préfère souvent les scans avec exécution vectorisée et stockage colonne compressé.
- Schémas de concurrence : l’OLTP ce sont beaucoup de requêtes courtes ; l’analytique peut être peu de requêtes longues qui monopolisent CPU, mémoire et I/O.
- Réalité du modèle de données : les schémas normalisés sont propres pour les écritures ; les schémas dénormalisés sont plus rapides pour les agrégats et les jointures à la volée.
Le vrai dommage : les rapports ne sont pas seulement lents, ils déstabilisent la base
« Lent » est agaçant. « Lent et déstabilisant » est un incident. Une requête de rapport peut :
- Évincer votre working set OLTP chaud du buffer pool.
- Inonder le disque de lectures (et des écritures undo/redo si des tables temporaires débordent).
- Créer du lag de réplication (les écritures ont lieu, mais la réplique n’arrive pas à les appliquer).
- Déclencher des verrous de métadonnées de manière gênante lors de changements de schéma.
- Causer une saturation CPU qui rend toute requête lente, y compris celles qui comptent vraiment.
Si vous avez vécu avec le « ajoutez juste un index », voici le piège : les index accélèrent les requêtes sélectives.
Les rapports ne sont souvent pas sélectifs. Ou ils sont sélectifs sur un champ à faible cardinalité qui rend les index moins utiles.
Ou ils joignent de grandes tables d’une manière qui rend impossible le « bon » index car il n’existe pas de bon index.
Blague n°1 : Ajouter des index pour corriger les rapports, c’est comme ajouter des voies à une autoroute pour régler les heures de pointe — félicitations, vous avez maintenant un embouteillage plus large.
Faits intéressants et un peu d’histoire (pour que vous arrêtiez de discuter avec la gravité)
- MariaDB a été forké depuis MySQL après des inquiétudes sur l’acquisition de Sun par Oracle ; il a gardé la compatibilité MySQL tout en divergeant sur les moteurs de stockage et les fonctionnalités.
- ClickHouse est né chez Yandex pour l’analytique à l’échelle du web ; sa conception suppose que les gros scans et les agrégats rapides sont la norme, pas l’exception.
- Le stockage en colonnes est devenu courant en analytique parce que lire moins d’octets compte plus que « avoir le bon index » une fois que les jeux de données sont grands.
- La compression est une fonctionnalité dans les stores colonne : une meilleure compression signifie moins d’I/O, et moins d’I/O signifie plus de vitesse. Ce n’est pas seulement pour économiser du disque.
- MergeTree (la famille d’engines cœur de ClickHouse) repose sur des merges en arrière-plan et des parties immuables — excellent pour ingestion + lectures, avec des modes de défaillance différents des B-arbres.
- Les materialized views dans ClickHouse sont souvent utilisées comme « pré-agrégations », un motif popularisé par les systèmes OLAP pour échanger stockage contre latence de requête prévisible.
- Les schémas en étoile et les tables de faits dénormalisées sont devenus courants parce que joindre des tables transactionnelles normalisées à l’échelle analytique est coûteux et fragile.
- Le lag de réplication comme symptôme précède les stacks analytiques modernes : « exécuter des rapports sur une réplique » est un hack depuis les premiers jours de MySQL, et ça fait encore mal.
Ce que ClickHouse fait bien (et ce qu’il fait mal)
ClickHouse est une base OLAP conçue pour des lectures rapides sur de grands ensembles de données. Il lit des données en colonnes,
exécute des requêtes de manière vectorisée, compresse agressivement, et peut monter horizontalement.
Il est conçu pour « donnez-moi des agrégats sur un mois d’événements », pas pour « mettre à jour une ligne par clé primaire 5 000 fois par seconde ».
Points forts de ClickHouse
- Scans et agrégats rapides : group-bys, buckets temporels, calculs de percentiles, listes top-N.
- Haute compression : vous obtenez souvent une empreinte de stockage étonnamment réduite pour des données de type événement.
- Ingestion orientée append : insérez par lots ; laissez les merges nettoyer plus tard.
- Options de pré-agrégation : les materialized views peuvent construire des tables de résumé à l’arrivée des données.
- Exécution distribuée : échelonnez les lectures et le stockage sur shards/réplicas avec un design adapté.
Faiblesses de ClickHouse (là où les gens se font mal)
- Les mises à jour/suppressions ne sont pas OLTP : oui, vous pouvez faire des mutations, mais elles sont lourdes et ce n’est pas la voie idéale.
- Les jointures peuvent être coûteuses si vous le traitez comme un schéma OLTP normalisé et attendez des miracles.
- Modèles de consistance éventuelle : vous concevrez pour les données tardives, la déduplication et l’idempotence.
- Nuances opérationnelles : merges, parts, watermarks disques, et limites mémoire ne sont pas des connaissances optionnelles.
Recommandation principale : gardez MariaDB pour les transactions et l’état source de vérité.
Utilisez ClickHouse pour les rapports, tableaux de bord et explorations. Ne les laissez pas se battre pour les mêmes ressources.
Cadre de décision : garder MariaDB, optimiser ou décharger
Tous les rapports lents ne nécessitent pas une nouvelle base. Certains nécessitent une meilleure requête, une table de résumé,
ou l’admission que le tableau de bord n’a pas besoin d’une fraîcheur seconde par seconde. Mais une fois que la charge est fondamentalement analytique,
MariaDB continuera de vous facturer des intérêts sous forme de machines plus grosses, plus de réplicas et plus d’incidents.
Quand rester sur MariaDB (pour l’instant)
- Le jeu de données est petit (tient confortablement en mémoire) et les requêtes sont simples.
- Les rapports peuvent être pré-calculés la nuit sans impact métier.
- La majeure partie de la lenteur provient de bugs de requête évidents (prédicats manquants, N+1 au niveau rapport, jointures croisées accidentelles).
- Vous n’avez pas la capacité opérationnelle pour gérer un autre système pour l’instant.
Quand décharger vers ClickHouse
- Les rapports scannent des millions de lignes régulièrement et nécessitent une latence interactive.
- Les tableaux de bord tournent en continu et concurrencent le trafic de production.
- Vous avez besoin de découper/filtrer de manière flexible sur des données de type événement (séries temporelles, logs, clics, commandes, mouvements d’inventaire).
- « Exécuter sur une réplique » provoque du lag de réplication, ou la réplique est devenue critique pour la production de toute façon.
Quand repenser le produit à la place
Parfois la solution honnête n’est pas technique. C’est fixer des attentes.
Si la finance veut « une analyse de cohorte sur toute la durée » à la demande sur cinq ans de données, la réponse est :
construisez le pipeline et le stockage adaptés, et acceptez que cela coûte de l’argent et du temps.
Guide de diagnostic rapide
Vous voulez le goulot d’étranglement en quelques minutes, pas après une semaine de tuning à l’oreille. Voici l’ordre qui marche en pratique.
Première étape : est-ce que MariaDB étouffe sur le CPU, l’I/O, les verrous ou la mémoire ?
- CPU saturé : probablement de mauvais plans, de grosses jointures, pas de sélectivité, tri/group-by lourd.
- Borne par l’I/O : défauts du buffer pool, scans de table, débordement de tables temporaires, mauvaise localité, trop de lectures aléatoires.
- Verrou : verrous de métadonnées, longues transactions, ou rapports tenant des verrous plus longtemps que prévu.
- Pression mémoire : tris, tables temporaires, ou buffer pool trop petit provoquant du churn.
Deuxième étape : le décalage de workload est-il le vrai problème ?
- Les requêtes nécessitent de scanner « la plupart de la table » même avec des index.
- Le métier demande des group-bys de haute cardinalité et des filtres flexibles.
- Les rapports sont fréquents et s’exécutent pendant les heures de pointe.
Troisième étape : le pipeline de données est-il votre problème caché ?
- Le lag de réplique rend « exécuter sur la réplique » inutile.
- CDC ou ETL introduisent des doublons ou des événements hors ordre.
- Les changements de schéma cassent le pipeline et vous le découvrez une semaine plus tard.
Quatrième étape : confirmez en mesurant une requête représentative de bout en bout
Choisissez une requête qui fait mal. Obtenez son plan dans MariaDB, exécutez-la avec traçage, et quantifiez les lignes examinées, les tables temporaires,
les passes de tri, et le temps passé en attente. Puis décidez : optimiser dans MariaDB ou arrêter de le torturer et décharger.
Une citation pour rester honnête : « L’espoir n’est pas une stratégie. » — Gene Kranz
Tâches pratiques : commandes, sorties et décisions (12+)
Ce ne sont pas des commandes « jouet ». Ce sont les actions quotidiennes que vous utilisez pour décider de la suite.
Pour chaque tâche : exécutez la commande, interprétez la sortie, puis prenez une décision.
Task 1: Find the top slow queries in MariaDB (slow query log)
cr0x@server:~$ sudo pt-query-digest /var/log/mysql/mariadb-slow.log | head -n 30
# 120s user time, 3s system time, 1.23M rss, 2.45G vsz
# Current date: Mon Dec 30 10:12:44 2025
# Hostname: db-primary
# Files: mariadb-slow.log
# Overall: 8.42k total, 97 unique, 0.23 QPS, 0.01x concurrency
# Time range: 2025-12-29T09:00:00 to 2025-12-30T09:00:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 13200s 100ms 95s 1.6s 12s 4.3s 450ms
# Rows examine 2.1e11 0 9.2e8 2.5e7 1.1e8 6.3e7 8.2e6
# Query 1: 38% RPS, 61% time, 80% rows examined
Ce que cela signifie : Les lignes examinées sont énormes par rapport aux lignes retournées. C’est le classique « requête analytique sur des tables OLTP ».
Décision : Si les pires coupables font des group-bys sur de grandes plages temporelles, planifiez un déchargement.
Si c’est une seule requête avec un prédicat manquant, corrigez la requête d’abord.
Task 2: Check current MariaDB threads and whether they’re stuck
cr0x@server:~$ mariadb -e "SHOW PROCESSLIST\G" | sed -n '1,60p'
*************************** 1. row ***************************
Id: 8123
User: report_user
Host: 10.2.4.19:52311
db: app
Command: Query
Time: 184
State: Sending data
Info: SELECT customer_id, count(*) FROM orders WHERE created_at >= '2025-10-01' GROUP BY customer_id
*************************** 2. row ***************************
Id: 8130
User: app_user
Host: 10.2.7.11:49821
db: app
Command: Query
Time: 2
State: Updating
Info: UPDATE inventory SET qty=qty-1 WHERE sku='X'
Ce que cela signifie : « Sending data » pendant des centaines de secondes est souvent un scan/agrégation, pas un transfert réseau.
Décision : Si les threads de rapport sont de longue durée et nombreux, limitez-les, déplacez-les vers un système séparé, ou les deux.
Task 3: Confirm whether the query plan is doing a scan
cr0x@server:~$ mariadb -e "EXPLAIN SELECT customer_id, count(*) FROM orders WHERE created_at >= '2025-10-01' GROUP BY customer_id\G"
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: range
possible_keys: idx_orders_created_at
key: idx_orders_created_at
rows: 48219321
Extra: Using index condition; Using temporary; Using filesort
Ce que cela signifie : Même avec un index de range, vous scannez ~48M de lignes, puis temp + filesort pour le group-by.
Décision : Si « rows » est de l’ordre de dizaines de millions et que le rapport est fréquent, arrêtez d’optimiser en surface. Déchargez.
Task 4: Check InnoDB buffer pool behavior (is it thrashing?)
cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+------------+
| Variable_name | Value |
+---------------------------------------+------------+
| Innodb_buffer_pool_read_requests | 9923412331 |
| Innodb_buffer_pool_reads | 183492211 |
+---------------------------------------+------------+
Ce que cela signifie : Le ratio indique que beaucoup de lectures sont satisfaites depuis la mémoire, mais 183M de lectures physiques reste énorme.
Surveillez la tendance pendant les fenêtres de reporting.
Décision : Si les lectures du buffer pool augmentent fortement quand les rapports tournent et que la latence applicative suit, isolez la charge analytique.
Task 5: Check for temp table spills (a silent killer)
cr0x@server:~$ mariadb -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Created_tmp_disk_tables | 421193 |
| Created_tmp_tables | 702881 |
+-------------------------+----------+
Ce que cela signifie : Les tables temporaires sur disque sont coûteuses : I/O supplémentaire, latence accrue, plus de contention.
Décision : Si les tables temporaires sur disque augmentent rapidement pendant les rapports, soit redessinez les requêtes, soit arrêtez de les exécuter sur MariaDB.
Task 6: Measure replication lag if you’re running reports on replicas
cr0x@server:~$ mariadb -h db-replica -e "SHOW SLAVE STATUS\G" | egrep 'Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 1870
Ce que cela signifie : 31 minutes de retard. Votre « réplique de reporting » est maintenant une machine à remonter le temps.
Décision : Arrêtez de traiter les réplicas comme des moteurs analytiques. Déchargez vers ClickHouse ou construisez des réplicas analytiques dédiés avec des garanties différentes.
Task 7: Identify whether the host is IO-bound (Linux)
cr0x@server:~$ iostat -xz 1 3
Linux 6.1.0 (db-primary) 12/30/2025
avg-cpu: %user %nice %system %iowait %steal %idle
52.21 0.00 6.12 18.44 0.00 23.23
Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s w_await %util
nvme0n1 2100.0 268800.0 0.0 0.00 8.10 128.0 980.0 105600.0 12.40 98.70
Ce que cela signifie : %util proche de 99% et un iowait élevé : le stockage est saturé. Les rapports lisent trop.
Décision : Vous pouvez monter le stockage, mais c’est acheter du temps. Mieux : réduisez le volume de scans en déplaçant l’analytique vers un stockage colonne.
Task 8: Check MariaDB table sizes and growth (capacity pressure)
cr0x@server:~$ mariadb -e "SELECT table_name, round((data_length+index_length)/1024/1024/1024,2) AS gb FROM information_schema.tables WHERE table_schema='app' ORDER BY (data_length+index_length) DESC LIMIT 10;"
+-------------------+-------+
| table_name | gb |
+-------------------+-------+
| events | 612.45 |
| orders | 189.12 |
| order_items | 141.77 |
| sessions | 88.04 |
+-------------------+-------+
Ce que cela signifie : Les tables volumineuses avec usage mixte OLTP + analytique (comme events) sont des candidats privilégiés au déchargement.
Décision : Commencez par les plus grandes tables à lecture-intensive ; gardez les tables transactionnelles dans MariaDB.
Task 9: Baseline ClickHouse server health
cr0x@server:~$ clickhouse-client -q "SELECT version(), uptime(), round(memory_usage/1024/1024) AS mem_mb FROM system.metrics WHERE metric IN ('MemoryTracking')"
24.9.2.42
86400
512
Ce que cela signifie : Vous pouvez suivre le tracking mémoire et l’uptime rapidement ; si c’est déjà élevé à l’idle, vous avez un problème de charge ou de configuration.
Décision : Définissez des limites mémoire sensées et enquêtez sur les requêtes runaway avant d’embarquer les tableaux de bord.
Task 10: Check ClickHouse parts/merges (are you drowning in tiny parts?)
cr0x@server:~$ clickhouse-client -q "SELECT table, count() AS parts, sum(rows) AS rows, round(sum(bytes_on_disk)/1024/1024/1024,2) AS gb FROM system.parts WHERE active GROUP BY table ORDER BY parts DESC LIMIT 5"
events 18234 891233112 122.31
orders 1820 83211299 18.44
Ce que cela signifie : 18k parts pour events suggère trop d’inserts petits ou un mauvais partitionnement.
Décision : Regroupez les inserts, ajustez le partitionnement, et assurez-vous que les merges peuvent suivre ; sinon la latence des requêtes sera en dents de scie.
Task 11: Find slow queries in ClickHouse (system.query_log)
cr0x@server:~$ clickhouse-client -q "SELECT query_duration_ms, read_rows, read_bytes, memory_usage, query FROM system.query_log WHERE type='QueryFinish' AND event_time > now()-3600 ORDER BY query_duration_ms DESC LIMIT 5"
12034 981233112 44120341212 2147483648 SELECT customer_id, count() FROM events WHERE ts > now()-86400 GROUP BY customer_id
Ce que cela signifie : Les read_rows/read_bytes vous disent si la requête scanne trop ; memory_usage indique un grand état de group-by.
Décision : Si les lectures sont énormes, reconsidérez la clé primaire/ORDER BY et le partitionnement ; si la mémoire est énorme, envisagez la pré-agrégation.
Task 12: Verify disk watermarks and free space on ClickHouse nodes
cr0x@server:~$ df -h /var/lib/clickhouse
Filesystem Size Used Avail Use% Mounted on
/dev/md0 3.6T 3.1T 420G 89% /var/lib/clickhouse
Ce que cela signifie : ClickHouse a besoin de marge pour les merges ; fonctionner proche du plein rend les merges lents et les échecs étranges.
Décision : Gardez une marge significative ; si vous êtes au-dessus d’environ ~80–85% de manière constante, planifiez la capacité ou des politiques de cycle de vie maintenant.
Task 13: Verify CDC lag (binlog position tracking via a connector)
cr0x@server:~$ sudo journalctl -u mariadb-to-clickhouse-cdc --since "10 min ago" | tail -n 8
Dec 30 10:01:11 cdc1 cdc[1842]: last_binlog=mysql-bin.003211:918233112
Dec 30 10:01:21 cdc1 cdc[1842]: last_binlog=mysql-bin.003211:918244901
Dec 30 10:01:31 cdc1 cdc[1842]: lag_seconds=3
Ce que cela signifie : Un lag bas et stable est ce que vous voulez. Les pics signifient une pression en aval ou des problèmes de connecteur.
Décision : Si le lag monte pendant les fenêtres de rapports, votre déchargement est incomplet : soit les tableaux de bord frappent encore MariaDB, soit ClickHouse n’ingère pas assez vite.
Task 14: Confirm ClickHouse is actually using the index (data skipping) via EXPLAIN
cr0x@server:~$ clickhouse-client -q "EXPLAIN indexes=1 SELECT count() FROM events WHERE ts >= now()-3600 AND customer_id=12345"
Indexes:
PrimaryKey
Keys:
ts
customer_id
Condition: (ts >= (now() - 3600)) AND (customer_id = 12345)
Ce que cela signifie : Vous voulez des conditions qui s’alignent avec le ORDER BY pour que ClickHouse puisse sauter des granules.
Décision : Si vos filtres courants n’apparaissent pas dans la clé primaire, votre schéma lutte contre vos requêtes.
Conception du déchargement : flux de données, latence et exactitude
« Décharger l’analytique » sonne bien jusqu’à ce que vous posiez les questions ennuyeuses : Quelle fraîcheur doit-on garantir ?
Que se passe-t-il quand une ligne est mise à jour ? Comment traiter les suppressions ? Quelle est la source de vérité ?
Choisissez votre pattern de déchargement
- ETL par lots : exports nocturnes/horaires de MariaDB vers ClickHouse. Simple, peu coûteux, pas en temps réel.
- CDC (change data capture) : stream des changements de binlog vers ClickHouse en continu. Plus de composants, latence plus faible.
- Dual-write : l’application écrit à la fois dans MariaDB et ClickHouse. Voie rapide vers des regrets sauf si vous avez une discipline stricte.
Pour la plupart des entreprises : ETL par lots pour les rapports à faible enjeu, CDC pour les tableaux de bord opérationnels, et évitez le dual-write à moins d’aimer déboguer la consistance à 2h du matin.
Définissez la « justesse » comme un adulte
La justesse analytique n’est que rarement « correspond exactement à l’OLTP à chaque milliseconde ». En général c’est :
- Les données sont complètes jusqu’à T moins N minutes.
- Les événements tardifs sont pris en charge (fenêtre de backfill).
- La déduplication est déterministe.
- Les métriques métier sont définies une fois, pas réinventées par tableau de bord.
Comment gérer les mises à jour et suppressions depuis MariaDB
Dans MariaDB, une mise à jour de ligne est une journée normale. Dans ClickHouse, vous modélisez généralement le changement dans le temps :
- Événements immuables : faits append-only (commande créée, paiement réglé). Cas idéal.
- ReplacingMergeTree : conservez la version la plus récente via une colonne de version ; les doublons sont fusionnés avec le temps.
- CollapsingMergeTree : modélisez un signe (+1/-1) pour la sémantique insert/delete, utile pour certains flux d’événements.
- Mutations : lourdes ; utilisez-les avec parcimonie pour des corrections véritables, pas pour des mises à jour routinières.
Si votre table OLTP est « état courant » (comme l’inventaire courant), vous pouvez la répliquer dans ClickHouse,
mais n’attendez pas qu’elle se comporte comme une table mise à jour transactionnellement. Pour l’analytique, les tables d’état sont souvent dérivées des événements de toute façon.
Modélisation de schéma qui ne se dégrade pas
La manière la plus rapide de rendre ClickHouse lent est d’importer votre schéma MariaDB normalisé tel quel puis d’être surpris.
La deuxième manière la plus rapide est de choisir un ORDER BY qui ne correspond pas à vos filtres réels.
Commencez par les questions, pas par les tables
Listez vos 10 principales requêtes de tableau de bord. Les vraies, pas les aspirationales. Pour chacune, capturez :
- Filtres de plage temporelle (dernière heure, dernier jour, plage arbitraire)
- Dimensions à haute sélectivité (customer_id, org_id, tenant_id)
- Dimensions de group-by (country, plan, product_id)
- Cardinalité attendue (combien de groupes ?)
- Fréquence de rafraîchissement et SLO
Stratégie de clé d’ordre : le super-pouvoir « sauter les données »
La clé primaire de ClickHouse (l’expression ORDER BY dans les tables MergeTree) n’est pas un index B-tree.
C’est la manière dont les données sont triées physiquement, permettant de sauter des blocs quand les prédicats correspondent à l’ordre.
Si chaque tableau de bord filtre par temps et par locataire, votre clé d’ordre commence souvent par ceux-ci :
(tenant_id, ts) ou (ts, tenant_id). Lequel vient en premier dépend des patterns d’accès.
- Principalement « un locataire, beaucoup de plages temporelles » : (tenant_id, ts)
- Principalement « tranches temporelles globales sur tous les locataires » : (ts, tenant_id)
Partitionnement : restez ennuyeux
Le partitionnement aide à gérer le cycle de vie des données et la portée des merges. Sur-partitionner crée trop de parts et des douleurs opérationnelles.
Un pattern courant : partitionner par mois pour les données d’événements, par jour pour des volumes extrêmement élevés, ou par tenant seulement si les tenants sont énormes et isolés.
Materialized views : pré-agrégez pour des tableaux de bord prévisibles
La plainte « le tableau de bord est lent » est souvent « group-by sur 90 jours est lent ». Pré-agrégez les métriques chaudes.
Construisez des tables de résumé indexées par les dimensions que vous filtrez vraiment. Ensuite les tableaux de bord frappent les résumés, pas les faits bruts.
Blague n°2 : Les materialized views, c’est comme préparer ses repas — vous passez un peu de temps en amont pour ne pas pleurer devant de la nourriture à emporter à 23h.
Exploitation et fiabilité : faire tourner ClickHouse comme il faut
Décharger l’analytique n’est pas « installer ClickHouse et faire le malin ». Vous introduisez un nouveau système de production.
Il a besoin de monitoring, de sauvegardes, de planification de capacité, de discipline sur les changements de schéma, et d’une réponse aux incidents.
À surveiller (hygiène SRE minimale viable)
- Lag d’ingestion : du binlog MariaDB à la disponibilité de la table ClickHouse.
- Arriéré de merges : nombre de parts, merges en cours, octets à merger.
- Utilisation disque et espace libre : les merges ont besoin de marge ; les disques presque pleins causent des défaillances en cascade.
- Percentiles de latence des requêtes : pour les requêtes de dashboard et l’exploration ad-hoc.
- Utilisation mémoire et OOM kills : les group-bys peuvent allouer agressivement sans limites.
- Santé des réplicas : si vous utilisez la réplication, surveillez la queue de réplication et les erreurs.
Sauvegardes : décidez ce que vous protégez
Les données ClickHouse sont souvent dérivées. Cela ne veut pas dire que vous ne les sauvegardez pas.
Cela signifie que votre stratégie de sauvegarde peut être différente :
- Si vous pouvez réhydrater depuis des logs bruts/object storage, les sauvegardes servent une récupération plus rapide, pas une survie existentielle.
- Si ClickHouse contient une vérité métier soignée (tables de métriques, données corrigées), sauvegardez-les comme si ça comptait — parce que ça compte.
Isolation des ressources : empêchez les tableaux de bord de devenir un déni de service
Utilisez users/profiles/quotas. Fixez des limites mémoire par requête et par utilisateur. Utilisez du queueing si nécessaire.
L’objectif : qu’une requête lourde d’analyste ne prive pas les tableaux de bord opérationnels.
Changements de schéma : traitez-les comme des déploiements en production
Les changements de schéma ClickHouse sont souvent plus simples que les migrations OLTP, mais ont toujours un rayon d’impact :
les materialized views dépendent des colonnes ; les pipelines CDC dépendent du schéma ; les tableaux de bord dépendent des sémantiques.
Planifiez les changements avec compatibilité, et versionnez vos transformations.
Trois mini-histoires d’entreprise (douleur incluse)
Mini-histoire 1 : L’incident causé par une mauvaise hypothèse
Une entreprise B2B de taille moyenne avait une configuration familière : MariaDB primaire, une réplique étiquetée « reporting », et un outil BI
pointé sur cette réplique. Tout le monde se sentait malin. Les écritures restaient sur le primaire, les lectures sur la réplique, paix mondiale accomplie.
L’hypothèse erronée était subtile : ils croyaient que le lag de réplication serait « assez petit » pour les tableaux de bord, et si ça devenait gros,
ce serait évident. Ce ne l’était pas. L’outil BI mettait en cache des résultats, les utilisateurs exportaient des CSV, et personne ne remarquait que le chiffre du « revenu du mois en cours »
était parfois en retard de trente minutes pendant les heures de pointe. Les données étaient fausses, mais suffisamment cohérentes pour sembler crédibles.
Alors vint un lancement produit. Le volume d’écritures a grimpé, et les rapports lourds aussi.
Le lag de la réplique a sauté, et l’outil BI est devenu plus lent. Quelqu’un « a corrigé » en pointant l’outil BI vers le primaire pour « juste un jour ».
Ce jour-là a inclus un scan de table de plusieurs heures et quelques group-bys coûteux. La latence au checkout a augmenté ; l’app a retenté ; les écritures ont augmenté encore ;
le primaire a commencé à saturer le disque.
L’incident n’était pas une erreur catastrophique unique. C’était l’accumulation d’une mauvaise décision d’architecture : utiliser la réplication comme système analytique.
Le postmortem a la tournure habituelle : la réplique était déjà critique pour la production parce qu’elle alimentait des tableaux de bord clients.
Ce n’était donc pas « agréable à avoir ». C’était une dépendance sans SLO ni garde-fous.
La correction a été ennuyeuse et structurelle : déplacer l’analytique vers ClickHouse, garder la réplique pour le failover, définir des attentes de fraîcheur, et ajouter des alarmes de lag.
La réplication est redevenue ce qu’elle devait être : un outil de résilience, pas un moteur de reporting.
Mini-histoire 2 : L’optimisation qui a échoué
Une autre entreprise a déchargé vers ClickHouse et a célébré. Les tableaux de bord sont passés de minutes à secondes.
Puis ils ont « optimisé » l’ingestion en streamant chaque ligne comme un insert une par une parce que « le temps réel » sonnait bien en réunion.
En quelques semaines, la latence des requêtes est devenue erratique. Parfois rapide, parfois horrible. Le cluster ClickHouse semblait sain jusqu’à ce qu’il ne le soit plus.
Le nombre de parts a grimpé. Les merges tournaient constamment. Les disques ont été occupés par le travail en arrière-plan. Le cluster n’était pas tombé ; il était juste perpétuellement irrité.
L’échec était classique : ClickHouse est append-friendly, mais il veut des lots.
Les inserts ligne par ligne créent beaucoup de petites parts, et beaucoup de petites parts créent de la pression de merge, et la pression de merge vole l’I/O aux requêtes.
Les utilisateurs ont vécu cela comme « les tableaux de bord sont instables » ce qui est, argumentablement, pire que « les tableaux de bord sont lents », car c’est plus difficile à faire confiance.
La remédiation n’a pas été un réglage magique. Ils ont groupé les inserts (basés sur le temps ou la taille), réduit la granularité des partitions,
et mis en place des limites opérationnelles : seuils de parts maximum par partition avec alerting. Ils ont aussi accepté que « temps réel »
signifie « en une minute » pour la plupart des métriques, pas « en une seconde ».
C’était une bonne leçon : le système le plus rapide est celui que vous ne forcez pas dans un comportement pathologique.
Mini-histoire 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise
Une entreprise liée aux paiements faisait tourner MariaDB et ClickHouse. Rien de spectaculaire.
Ils avaient un contrat de données écrit : un petit document décrivant chaque table de métriques, les champs sources,
la logique de transformation, plus une « fenêtre de fraîcheur » et une requête de test qui validait les comptes de lignes.
Chaque changement de schéma dans MariaDB touchant des tables répliquées nécessitait une revue légère :
est-ce que ça casse le CDC ? ClickHouse a-t-il besoin d’une nouvelle colonne ? une materialized view doit-elle être mise à jour ?
Le processus n’était pas aimé, mais il était cohérent. Ils exécutaient aussi un job de réconciliation quotidien :
comparer counts et sums entre MariaDB et ClickHouse pour une fenêtre temporelle glissante.
Un vendredi, un changement applicatif apparemment inoffensif a commencé à écrire des NULLs dans un champ de dimension que les tableaux de bord groupaient.
MariaDB ne s’en est pas soucié. ClickHouse non plus. Le tableau de bord, cependant, a montré une chute soudaine dans une métrique clé.
La vérification de réconciliation l’a détecté en une heure parce que la cardinalité du groupe a changé fortement.
L’astreinte n’a pas eu besoin de deviner. Ils ont rollbacké le changement applicatif, backfillé la dimension manquante dans ClickHouse depuis les événements sources,
et le tableau de bord exécutif du lundi matin n’est pas devenu un drame judiciaire.
Personne n’a eu de trophée pour ce processus. C’est pour ça que ça a marché.
Erreurs courantes : symptôme → cause racine → correctif
1) Symptom: “Reports are slow” only during business hours
Cause racine : les rapports concurrencent la charge OLTP pour le CPU et l’I/O ; le churn du buffer pool l’amplifie.
Fix : déplacez les tableaux de bord vers ClickHouse ; si vous devez rester sur MariaDB, planifiez les rapports lourds hors-pointe et ajoutez des timeouts de requête.
2) Symptom: MariaDB replica lag grows whenever BI runs
Cause racine : la réplique effectue de lourdes lectures et n’arrive pas à appliquer les écritures assez vite ; ou l’I/O est saturé par les scans.
Fix : cessez d’utiliser une réplique de réplication pour l’analytique ; déchargez. En interim, ajoutez une « réplique analytique » dédiée avec ressources isolées et acceptez que ce soit une solution provisoire.
3) Symptom: ClickHouse dashboards are fast sometimes, slow other times
Cause racine : trop de petites parts et merges lourds ; pattern d’ingestion pathologique.
Fix : regroupez les inserts ; réduisez le nombre de partitions ; surveillez les parts ; assurez-vous que les disques ont de la marge ; ajustez les merges prudemment.
4) Symptom: ClickHouse query reads billions of rows for a narrow filter
Cause racine : ORDER BY ne s’aligne pas avec les prédicats typiques ; mauvais saut de données.
Fix : redessinez la clé d’ordre de la table ; envisagez des projections ou des rollups matérialisés ; ne reproduisez pas aveuglément le schéma OLTP.
5) Symptom: Data in ClickHouse doesn’t match MariaDB
Cause racine : CDC avec doublons, événements hors ordre, suppressions/mises à jour manquantes.
Fix : utilisez des clés idempotentes, des colonnes de version, et des patterns de déduplication (par ex. ReplacingMergeTree) ; implémentez des contrôles de réconciliation et des workflows de backfill.
6) Symptom: ClickHouse runs out of disk unexpectedly
Cause racine : les merges ont besoin d’espace temporaire ; rétention non appliquée ; hypothèses de haute compression échouées à cause de choix de types.
Fix : appliquez TTL/retention ; gardez le disque sous des seuils sécuritaires ; utilisez les bons types (LowCardinality quand approprié), et ne stockez pas des blobs JSON comme plan principal.
7) Symptom: MariaDB query “optimized” with a new index but app got slower
Cause racine : l’index ajouté a augmenté l’amplification d’écriture ; churn du cache ; l’optimizer a choisi un plan pire pour les requêtes OLTP.
Fix : rollback de l’index ; utilisez des indexes couvrants uniquement là où ils sont sélectifs ; séparez la charge analytique au lieu d’empiler des index.
Listes de contrôle / plan pas à pas
Étapes pas à pas : décharger l’analytique correctement
-
Inventoriez votre charge de reporting.
Collectez les 20 requêtes principales, leur fréquence, et les tableaux de bord/exports qui les déclenchent. -
Classifiez les requêtes : adaptées OLTP vs lourdes scans analytiques.
Si une requête examine des dizaines de millions de lignes régulièrement, c’est de l’analytique. -
Définissez des objectifs de fraîcheur.
« Dans les 5 minutes » est une vraie exigence ; « temps réel » est un mot de réunion. -
Sélectionnez l’approche d’ingestion.
ETL par lots pour reporting horaire/quotidien ; CDC pour tableaux de bord quasi temps réel. -
Concevez les tables ClickHouse à partir des requêtes.
Choisissez partitionnement etORDER BYen fonction des patterns de filtres. -
Commencez par un domaine.
Exemple : analytics events/orders, pas toute la base. -
Construisez la réconciliation.
Comptes de lignes, sommes, et métriques « connues bonnes » sur des fenêtres glissantes. Alertez sur la dérive. -
Déplacez les tableaux de bord progressivement.
Exécutez-les en shadow-run contre ClickHouse et comparez les résultats avant la bascule. -
Bridez et protégez.
Appliquez timeouts et limites de concurrence sur les rapports MariaDB pendant la migration. -
Opérationnalisez ClickHouse.
Monitoring, sauvegardes, planification de capacité, et runbooks avant de déclarer victoire.
Checklist : quoi mettre en place le premier jour (minimum)
- Slow query log MariaDB activé et analysé quotidiennement.
- Rétention du query log ClickHouse et dashboards pour les requêtes principales.
- Alertes d’utilisation disque avec seuils conservateurs.
- Métrique + alerte de lag d’ingestion.
- Surveillance merges/parts.
- Une politique sur qui peut exécuter des requêtes lourdes ad-hoc et comment elles sont limitées.
FAQ
1) Can’t I just add more MariaDB replicas and run reports there?
Vous pouvez, et beaucoup d’équipes le font. C’est une rustine. Vous utilisez toujours un moteur row-store OLTP pour du travail scan-heavy,
et vous vous battrez toujours contre le lag, le churn du cache, et le matériel coûteux. Si le reporting est stratégique, construisez un système analytique.
2) Is ClickHouse a replacement for MariaDB?
Pas pour l’OLTP. ClickHouse peut stocker beaucoup de données et répondre vite, mais il n’est pas conçu pour des mises à jour transactionnelles,
des contraintes strictes, et des mises à jour point par point à haute fréquence comme MariaDB.
3) What’s the biggest schema mistake when moving to ClickHouse?
Importer des tables OLTP normalisées et s’attendre à ce que les jointures se comportent comme dans un entrepôt. Concevez des tables de faits et choisissez un ORDER BY
aligné sur vos filtres. Rendez vos requêtes communes peu coûteuses par construction.
4) How fresh can my dashboards be with ClickHouse?
Avec CDC et une mise en lot raisonnable, « dans la minute » est courant. « En quelques secondes » est possible mais augmente le risque opérationnel,
surtout si vous transformez l’ingestion en usine à petites parts.
5) What about deletes and GDPR-style erasure?
Planifiez-le tôt. Pour une suppression véritable, vous pourriez avoir besoin de mutations ciblées ou de politiques TTL selon votre modèle de données.
Si vous avez des exigences légales d’effacement, ne les balayez pas d’un revers de main — concevez le pipeline et le stockage en conséquence.
6) Should we pre-aggregate everything with materialized views?
Non. Pré-aggrégez les quelques métriques chaudes et coûteuses, surtout les tuiles de tableau de bord avec contraintes de latence strictes.
Gardez les faits bruts pour la flexibilité, mais ne faites pas refaire à chaque requête de tableau de bord le même scan de données brutes.
7) How do I keep analysts from blowing up ClickHouse with bad queries?
Utilisez profiles/quotas, limitez la mémoire par requête, et fournissez des tables cadrées pour l’exploration courante.
Aussi : apprenez aux gens à filtrer par temps en premier. La culture est une fonctionnalité de performance.
8) Is “run reports on MariaDB after hours” a valid long-term plan?
Seulement si l’entreprise accepte la latence et que la croissance des données reste modeste. En pratique, le reporting s’étend,
« hors heures » disparaît à travers les fuseaux horaires, et vous revenez ici. Déchargez tôt si le reporting compte.
9) What if our “reports” need exact transactional consistency?
Alors définissez quelles rapports l’exigent vraiment. La plupart ne l’exigent pas. Pour les rares qui le font, gardez-les dans MariaDB ou générez-les
à partir d’un snapshot cohérent et acceptez un délai de reporting. Ne forcez pas chaque tableau de bord à respecter l’exigence la plus stricte.
10) What’s the simplest first win with ClickHouse?
Déchargez les tables de type événement (page views, audits, événements de cycle de vie de commande) et construisez quelques tables de résumé pour les tableaux de bord principaux.
Vous verrez un soulagement immédiat sur MariaDB et des gains rapides pour la latence des tableaux de bord.
Étapes pratiques suivantes
Si les rapports sont lents, traitez-le comme un problème de production, pas une gêne BI. Votre base OLTP vous dit qu’on lui demande de faire le mauvais travail.
Mesurez la douleur, identifiez les pires coupables, et décidez si vous optimisez une requête ou changez d’architecture.
Faites ceci ensuite, dans l’ordre :
- Récupérez le slow query log et classez par temps total et lignes examinées.
- Choisissez un rapport « tueur » représentatif et confirmez qu’il s’agit d’une agrégation scan-heavy.
- Fixez un objectif de fraîcheur et choisissez ETL par lots ou CDC en conséquence.
- Construisez une table de faits ClickHouse conçue autour des filtres et group-bys principaux du tableau de bord.
- Déplacez un tableau de bord, validez les résultats avec réconciliation, puis itérez.
- Ajoutez des garde-fous (quotas, limites mémoire, monitoring) avant d’inviter toute l’entreprise.
Le but n’est pas d’adorer ClickHouse. C’est d’arrêter de punir MariaDB pour être bon dans les transactions.
Mettez chaque système sur sa voie, et vous obtiendrez des rapports plus rapides, moins d’incidents, et une rotation d’astreinte plus calme.