Как ускорить запросы с group by в ClickHouse?

Question

[DamskiyUgodnik]

Решил на новом проекте попробовать использовать ClickHouse (до этого такого опыта не было). И столкнулся с проблемой долгой группировки (ну или запросов с группировкой в целом).

Что имеем:
Необходимо хранить данные об изменениях элементов. Есть id элемента и к примеру 5 значений. Сейчас данные идут в postgresql с "предобработкой" (проверяется предыдущее значение элемента, если оно отличается от текущего, в базу добавляется текущее значение + в отдельную колонку значение дельты ну и время фиксации изменения).

Далее данные скриптом "переливаются" в ClickHouse для построения аналитических отчётов (ну по крайней мере так планируется делать).

По данным:

• Планируемое количество элементов ~ 150 млн
  
• Количество изменений в день ~ 15 млн записей (примерно 200мб)
  
• Данные планируется хранить за 3-5 лет, но 95% отчётов будет строиться за последние полгода. Т.е. ~ 5млд записей на год и ~ 70gb по размеру таблицы
  

Требования к отчётам:

• Хочется делать группировки по дням (периодам, например неделя, месяц) и элементам. Например, получить 100 элементов, у которых самая большая сумма дельты или самое большое количество изменений за последние 7 дней.
  
• Отчёт должен генерироваться не дольше секунды
  

Текущие показатели:
Пример запроса

SELECT count(*) as m, sum(delta_1) as delta_sum FROM change_history GROUP BY id_element ORDER BY m DESC

• Сейчас в тестовой базе в 88 млн строк (около 900мб по размеру таблицы)
  
• ClickHouse из коробки, никаких настроек не производилось
  
• Время выполнения - 10-12 сек в первый раз и 3-4 сек при повторных запросах
  

По железу:
Тестовый стенд с убунтой

• 64гб оперативки
  
• 2x Intel Xeon E5-2650
  
• Какой-то обычный hdd sata 7200 на 8тб
  

Структура таблицы в ClickHouse:

create table qty_history
(
   dt            DateTime('Europe/Moscow'),  
   id_element    UInt64,
   val_1         UInt64,
   delta_1       UInt64,
   INDEX indx_id_element (id_element) TYPE minmax GRANULARITY 32
)
engine = MergeTree()
ORDER BY (id_element,dt)
PARTITION BY toYYYYMMDD(toDate(dt))

Что интересует:
В какую сторону рыть в плане производительности? точнее с чего начинать сначала? Инфы по ClickHouse много, и чтобы всё перелопатить надо время, а принимать решение вписываться в ClickHouse надо сейчас и в данный момент не понятно, можно ли это как-то оптимизировать без лютого "масштабирования" по железу.

Сейчас план такой:

1. Попробовать поиграться с индексами и структурой
   
2. Попробовать другие движки таблиц
   
3. Попробовать загнать данные в память, чтобы они читались из оперативки а не с диска (но вроде как при первом изучение так нельзя)
   
4. Попробовать на другом железе с более быстрыми дисками (ssd, raid) (есть подозрение что всё упирается в диск, т.к. процессоры вообще не загружаются при работе)
   
5. Попробовать собрать кластер, вроде разработчики обещают почти кратное ускорение, но в это сходу не хочется вписываться, т.к. выглядит как какая-то длинная история
   

Answer 1

[Vitsliputsli]

Время выполнения - 10-12 сек в первый раз и 3-4 сек при повторных запросах

В первй раз идет "прогрев" кеша диска, поэтому долго. Повторный запрос в 3-4 секунды наверное вполне нормальный, т.к. скорее всего необходимо обработать большое кол-во данных (но, конечно, нужно смотреть план, чтобы понимать сколько он обработал).
Решение стандартное - стройте проекции. Агрегирующие с группировкой по дням, для группировок по дням, неделям, месяцам. И top-проекции для "получить 100 элементов, у которых самая большая сумма". Но нужно посмотреть как это делать, так как я больше работал с другой колоночной СУБД.

Answer 2

[mayton2019]

Да ничего тут нельзя особо сделать. 10 секунд - холодный запуск группировки по 88 млн строк - это вполне себе хорошая цифра. Сомнительно что железо выдавит из себя больше. Ведь так или иначе нужно эти 88 млн пересчитать и даже будь это все в памяти - все равно обойти каждую ячейку. А дальше дело будет только хуже. Ведь табличка растет.

Есть техники микро-батчинга когда большая задача разбиватеся на порции. Например у тебя есть дневной партишен на 15 млн. Делишь его на часовые. Получается по 625 тыщ строк. Уже лучше.

Делаешь некую кумулятивную табличку. Типа

create table charge_cumulative(
  id long,
  cnt_cumulative long,
  delta_sum_cumulative long
)

Ну и на каждый микро-батч добавляешь к ней значения count, delta_sum. У тебя вроде удачно получается что можно только складывать.