Überblick
Ein Industrial-IoT-Betreiber hatte ClickHouse-Tabellen, die beim Anlegen gut performten und mit wachsendem Bestand an Sensordaten stetig nachließen. Dashboard-Abfragen lasen den Großteil jeder Tabelle, weil der Sort Key nicht dazu passte, wie die Daten gefiltert wurden. AceMQ gestaltete das Schema rund um den tatsächlichen Abfragemix neu.
Herausforderung
Die Performance von ClickHouse hängt weitgehend davon ab, wie viele Daten eine Abfrage überspringen kann – und das bestimmen Reihenfolge des Sort Keys, Partitionierung und Index-Granularität. Die bestehenden Tabellen waren mit einem Sort Key angelegt worden, der einen Identifier mit hoher Kardinalität an die erste Stelle setzte. Der Sparse Primary Index ermöglichte daher bei den Zeitbereichsfiltern, die jedes Dashboard verwendete, so gut wie kein Überspringen.
Umgebung
On-Premises-ClickHouse-Cluster mit mehrjähriger Sensortelemetrie für Dashboards zur Anlagenüberwachung.
Vorgehen
AceMQ profilierte den realen Abfragemix und maß pro Abfrage, wie viele Granules gelesen wurden und wie viele davon tatsächlich passende Zeilen enthielten. Diese Differenz bestimmte die Neugestaltung von Sort Key und Partitionierung. Codecs und Kompression wurden anschließend spaltenweise anhand gemessener Dateneigenschaften gewählt statt nach Standardwerten.
Lösung
- Abfragemix der Dashboards profiliert und gelesene Granules mit Granules mit Treffern verglichen
- Sort Keys so umsortiert, dass sie mit den Spalten beginnen, nach denen Abfragen tatsächlich filtern – damit greift das Index-Skipping wieder
- Partitionsgranularität gewählt, die die Anzahl der Parts begrenzt und dennoch Partition Pruning bei Zeitbereichen ermöglicht
- Codecs je Spalte passend zu gemessenen Dateneigenschaften für Zeitreihen und Spalten mit niedriger Kardinalität eingesetzt
- Materialized Views und Aggregattabellen für die wiederkehrenden Dashboard-Rollups eingeführt
- TTL-Richtlinien definiert, um gealterte Daten auf günstigeren Storage zu verschieben und nach dokumentiertem Zeitplan auslaufen zu lassen
Resultat
Statt den Großteil jeder Tabelle zu scannen, lesen Dashboard-Abfragen heute nur noch einen kleinen Bruchteil, und typische Antwortzeiten verbesserten sich um etwa eine Größenordnung. Auch der Speicherbedarf sank durch die Änderungen an Codecs und TTL spürbar.