Présentation
Un opérateur de l'IoT industriel disposait de tables ClickHouse performantes à leur création, mais qui se dégradaient régulièrement à mesure que les données de capteurs s'accumulaient. Les requêtes des tableaux de bord lisaient la majeure partie de chaque table, car la clé de tri ne correspondait pas à la façon dont les données étaient filtrées. AceMQ a refondu le schéma en fonction du mix de requêtes réel.
Défi
Les performances de ClickHouse dépendent largement de la quantité de données qu'une requête peut ignorer, ce qui tient à l'ordre de la clé de tri, au partitionnement et à la granularité de l'index. Les tables existantes avaient été créées avec une clé de tri commençant par un identifiant à forte cardinalité : l'index primaire creux ne permettait donc presque aucun saut pour les filtres sur plages temporelles qu'appliquait chaque tableau de bord.
Environnement
Cluster ClickHouse sur site stockant plusieurs années de télémétrie de capteurs pour les tableaux de bord de supervision des usines.
Approche
AceMQ a profilé le mix de requêtes réel et mesuré, pour chaque requête, combien de granules étaient lus par rapport au nombre de granules contenant des lignes correspondantes. Cet écart a guidé la refonte de la clé de tri et du partitionnement. Les codecs et la compression ont ensuite été choisis colonne par colonne en fonction des caractéristiques mesurées des données, plutôt que par défaut.
Solution
- Profilage du mix de requêtes des tableaux de bord et mesure des granules lus par rapport aux granules contenant des correspondances
- Réordonnancement des clés de tri pour commencer par les colonnes réellement filtrées par les requêtes, rétablissant le saut d'index
- Choix d'une granularité de partition qui limite le nombre de parts tout en permettant l'élagage des partitions sur les plages temporelles
- Application de codecs par colonne adaptés aux caractéristiques mesurées des données, pour les séries temporelles et les colonnes à faible cardinalité
- Mise en place de vues matérialisées et de tables d'agrégats pour les agrégations récurrentes des tableaux de bord
- Définition de politiques TTL pour déplacer les données anciennes vers un stockage moins coûteux et les faire expirer selon un calendrier documenté
Résultat obtenu
Les requêtes des tableaux de bord sont passées d'un scan de la majeure partie de chaque table à la lecture d'une petite fraction, et les temps de réponse habituels ont été améliorés d'environ un ordre de grandeur. Les changements de codecs et de TTL ont aussi nettement réduit l'empreinte de stockage.