Работа дашбордов с большими объемами данных

При работе с дашбордами на больших объемах данных возможно снижение производительности: увеличение времени загрузки, нестабильная работа виджетов или некорректное выполнение запросов.

В разделе описаны факторы, влияющие на производительность, и ограничения системы, которые нужно учитывать при настройке инфраструктуры и дашбордов.

Аппаратные ресурсы

Одна из причин снижения производительности — нехватка ресурсов сервера ClickHouse. Для стабильной работы учитывайте технические требования к оборудованию.

Особенности процессных виджетов

Процессные виджеты Карта процесса и Сфера процессов также создают повышенную нагрузку на ClickHouse.

При одинаковом объеме данных такие виджеты могут:

• Потреблять больше оперативной памяти
• Выполнять сложные SQL-запросы (JOIN, подзапросы, временные таблицы)

В дашбордах с процессными виджетами требования к ресурсам сервера, как правило, выше.

Ограничения по памяти

При выполнении сложных запросов ClickHouse использует оперативную память. Если памяти недостаточно:

• Запрос может завершиться с ошибкой
• Используется дисковая подкачка, что замедляет выполнение запросов

Параметры подключения ClickHouse

Производительность дашбордов также зависит от параметров подключения к ClickHouse. Их можно настроить в разделе Хранилища данных.

Рекомендации по организации модели данных

Порог большого объема данных зависит от конфигурации сервера и доступных вычислительных ресурсов. Обычно повышенную нагрузку создают дашборды, обращающиеся к таблицам с несколькими десятками миллионов строк.

Архитектура пространства и модели данных

При работе с большими объемами данных рекомендуется:

• По возможности не использовать все исторические данные в одном пространстве. Предпочтительнее разделить данные между несколькими пространствами — по отделам, направлениям, кварталам или другому критерию анализа. В каждое пространство загружать только необходимый объем данных
• При проектировании архитектуры модели данных и связей между таблицами учитывать, к каким таблицам и как часто дашборд будет обращаться. По возможности строить архитектуру так, чтобы компоненты дашборда не обращались к нескольким таблицам одновременно
• При создании связей между таблицами отдавать приоритет полю-ключу сортировки. Ключ сортировки можно посмотреть в Модели данных при редактировании таблицы
• Хранить объемные таблицы без связей с таблицами, задействованными в дашборде. По возможности не обращаться к ним из дашборда напрямую
• Для связи таблиц и построения дашборда создавать на основе больших таблиц дополнительные таблицы меньшей размерности с нужными агрегациями. Например, лог активности сотрудников с детализацией до миллисекунд можно агрегировать до дня, рассчитав в модуле автоматизации нужные метрики за день по каждому сотруднику
• Если требуется обращаться к большим таблицам без предварительной агрегации — создать с помощью модуля автоматизации буферную таблицу. По кнопке на дашборде подгружать в нее только необходимые данные из основной таблицы по выбранному сотруднику, дню или периоду. Использовать эту таблицу для связей и в дашборде

Оптимизация типов данных и SQL-запросов

• Для строковых полей с менее чем 10 000 уникальных значений рекомендуется использовать тип LowCardinality(String) — это снижает потребление памяти при выполнении запросов Чтобы проверить количество уникальных значений и типы полей в таблице, можно выполнить запрос:

  SELECT * APPLY(x -> toTypeName(x) || ' -> ' || toString(uniqExact(x)))
  FROM table_name
  

  После выполнения запроса найдите поля с типом String, у которых мало уникальных значений (менее 10 000) и это количество не будет расти выше 10 000 по мере накопления данных
• При соединении таблиц через JOIN, если между ними большая разница в объеме данных, таблицу с меньшим количеством строк рекомендуется размещать справа от JOIN Для правой таблицы можно добавить предфильтрацию по ключам соединения — это сокращает объем данных, обрабатываемых при соединении:

  SELECT
    t1.field,
    t1.field1,
    t2.field2
  FROM table_name_1 as t1
  INNER JOIN (
    SELECT field1, field2
    FROM table_name_2
    WHERE field1 IN (SELECT DISTINCT field FROM table_name_1)
  ) as t2 ON t1.field = t2.field1
  

• Вычисления с функцией process() рекомендуется переносить с уровня дашбордов на уровень скриптов предобработки для таблиц
• Логику фильтрации рекомендуется переносить с уровня дашбордов на уровень скриптов предобработки — в виде флагов в таблице