Panoramica
Un operatore IoT industriale aveva tabelle ClickHouse che funzionavano bene alla creazione e peggioravano costantemente con l'accumularsi dei dati dei sensori. Le query delle dashboard leggevano la maggior parte di ogni tabella perché la sort key non corrispondeva al modo in cui i dati venivano filtrati. AceMQ ha riprogettato lo schema attorno al mix reale di query.
Sfida
Le prestazioni di ClickHouse dipendono in larga misura da quanti dati una query riesce a saltare, il che si riduce all'ordine della sort key, al partizionamento e alla granularità dell'indice. Le tabelle esistenti erano state create con una sort key che metteva al primo posto un identificativo ad alta cardinalità, per cui il primary index sparse non consentiva quasi alcuno skipping per i filtri su intervalli temporali applicati da ogni dashboard.
Ambiente
Cluster ClickHouse on-premises che archivia anni di telemetria dei sensori per le dashboard di monitoraggio degli impianti.
Approccio
AceMQ ha profilato il mix reale di query e misurato, per ciascuna, quanti granule venissero letti rispetto a quanti contenessero righe corrispondenti. È stato questo divario a guidare la riprogettazione di sort key e partizionamento. Le scelte di codec e compressione sono state poi compiute colonna per colonna in base alle caratteristiche misurate dei dati, anziché ai valori predefiniti.
Soluzione
- Profilato il mix di query delle dashboard e misurati i granule letti rispetto a quelli contenenti corrispondenze
- Sort key riordinate in modo da iniziare con le colonne su cui le query filtrano davvero, ripristinando lo skipping dell'indice
- Scelta una granularità di partizionamento che limita il numero di part pur consentendo il partition pruning sugli intervalli temporali
- Applicati codec per colonna in base alle caratteristiche misurate dei dati, per le serie temporali e le colonne a bassa cardinalità
- Introdotte materialized view e tabelle aggregate per i rollup ricorrenti delle dashboard
- Definite policy TTL per spostare i dati più vecchi su storage meno costoso e farli scadere secondo una pianificazione documentata
Esito
Le query delle dashboard sono passate dalla scansione della maggior parte di ogni tabella alla lettura di una piccola frazione, e i tempi di risposta tipici sono migliorati di circa un ordine di grandezza. Anche l'occupazione di storage è calata in modo significativo grazie alle modifiche a codec e TTL.