Voltar para todos os casos de uso

ConsultoriaIndústria / manufatura

Sort keys e projections que deixam a varredura pequena

Operadora de plataforma de IoT industrial

Redesenho das chaves ORDER BY, do particionamento, dos codecs e das materialized views para que as consultas dos dashboards leiam uma pequena fração dos dados, em vez de fazer varreduras completas.

Visão geral

Uma operadora de IoT industrial tinha tabelas ClickHouse que funcionavam bem quando foram criadas e se degradavam continuamente à medida que os dados dos sensores se acumulavam. As consultas dos dashboards liam a maior parte de cada tabela porque a sort key não correspondia à forma como os dados eram filtrados. A AceMQ redesenhou o schema em torno do mix real de consultas.

Desafio

O desempenho do ClickHouse é determinado em grande parte pela quantidade de dados que uma consulta consegue pular, o que depende da ordem da sort key, do particionamento e da granularidade do índice. As tabelas existentes tinham sido criadas com uma sort key que colocava um identificador de alta cardinalidade em primeiro lugar, então o índice primário esparso quase não permitia pular dados nos filtros por intervalo de tempo que todos os dashboards aplicavam.

Ambiente

Cluster ClickHouse on-premises armazenando vários anos de telemetria de sensores para dashboards de monitoramento de plantas industriais.

Abordagem

A AceMQ analisou o mix real de consultas e mediu, para cada consulta, quantos granules eram lidos em relação a quantos continham linhas correspondentes. Essa diferença orientou o redesenho da sort key e do particionamento. Em seguida, as escolhas de codecs e compressão foram feitas por coluna, com base nas características medidas dos dados, e não nos padrões.

Solução

  1. Análise do mix de consultas dos dashboards e medição dos granules lidos em relação aos granules com correspondências
  2. Reordenação das sort keys para começar pelas colunas que as consultas realmente filtram, restaurando o salto de dados pelo índice
  3. Escolha de uma granularidade de partição que limita a quantidade de parts e ainda permite o partition pruning por intervalo de tempo
  4. Aplicação de codecs por coluna adequados às características medidas dos dados, para séries temporais e colunas de baixa cardinalidade
  5. Criação de materialized views e tabelas de agregação para os rollups recorrentes dos dashboards
  6. Definição de políticas de TTL para mover os dados antigos para um armazenamento mais barato e expirá-los em um cronograma documentado

Resultado obtido

As consultas dos dashboards deixaram de varrer a maior parte de cada tabela e passaram a ler uma pequena fração, e os tempos de resposta típicos melhoraram em cerca de uma ordem de grandeza. O espaço ocupado em armazenamento também caiu de forma significativa com as mudanças de codecs e de TTL.

Tecnologias

ClickHouseKafkaGrafanaLinux

Precisa de orientação de arquitetura para ClickHouse?

Os engenheiros ClickHouse sêniores da AceMQ já conduziram exatamente este tipo de projeto. Seja para orientação de arquitetura, correção mão na massa ou uma parceria gerenciada contínua, estamos prontos para ajudar.