Расскажу о нашем опыте работы с ClickHouse в TrafficStars. Мы столкнулись с проблемами производительности при масштабировании системы статистики, которая обрабатывает до 70 тысяч запросов в секунду и хранит около 50 ТБ данных.

Эволюция нашей архитектуры: от первоначальной схемы с Vertica и Citus до перехода на ClickHouse, через проблемы с материализованными представлениями и оптимизацию индексов к финальному решению с использованием массивов и структур данных.

TL;DR

При масштабировании системы статистики в TrafficStars мы столкнулись с проблемами производительности ClickHouse. Основные выводы:

  • Материализованные представления могут усугубить проблему — 30 материализованных представлений превратили одну проблему записи в 30
  • Массивы и структуры данных — мощное решение — позволили сократить количество таблиц с 30 до 5 без потери функциональности
  • Гранулярность индексов критична — уменьшение гранулярности помогло решить проблемы с памятью
  • Оптимизатор запросов даёт результат — до 30% запросов оптимизируются автоматически, среднее время упало с 200–250 мс до 55 мс
  • Поддерживаемость важнее производительности — простая система важна не только для разработчиков, но и для бизнеса

Контекст: требования к системе статистики

В TrafficStars мы работаем с рекламной сетью, которая обрабатывает до 70 тысяч запросов в секунду на наши бренды. У нас два дата-центра в Европе и США, множество серверов доставки, события писались в Kafka, а хранение данных обеспечивает ClickHouse. На момент работы над системой (2018 год) у нас было примерно 500 запросов статистики в пиках, около 50 ТБ данных, и это без учёта работы аналитиков и аналитических дашбордов для менеджеров.

Особенности наших данных

  • Крупные клиенты могут приносить до 40% трафика, что создаёт неравномерную нагрузку
  • Резкие всплески трафика при подключении новых площадок (publishers) — нагрузка легко поднимается на 5–10 тысяч запросов в секунду
  • Требование к актуальности: задержка статистики должна быть минимальной, данные нужны в реальном времени или близко к нему

Почему это критично? Во-первых, точный расчёт баланса пользователей — если мы спишем больше денег, это плохо и для клиента, и для нас. Во-вторых, клиенты часто корректируют рекламные кампании, поэтому им нужны актуальные данные для принятия решений.

Эволюция архитектуры: от Vertica к ClickHouse

Начальная схема: Kafka → Vertica → Citus

Когда я пришёл в компанию в 2016 году, архитектура выглядела так:

  1. Kafka — все события писались в Kafka
  2. Workers — обрабатывали данные из Kafka
  3. Vertica — хранила сырые данные несколько дней, считала статистику
  4. Citus — долговременное хранилище агрегированных данных, также отгружались сырые данные в S3 для больших отчётов

В Citus у нас была одна широкая таблица с 29 колонками (сейчас это звучит смешно, но тогда это было нормально). Всё работало более-менее ровно, пока не начались проблемы роста.

Архитектура до перехода на ClickHouse

events
   |
   v
Kafka
   |
   v
Workers
   |
   +---> Vertica (raw data, few days)
   |      |
   |      +---> statistics
   |
   +---> Citus (aggregated data, long-term)
   |      |
   |      +---> wide table (29 columns)
   |      +---> S3 (raw data for reports)

Проблема: горизонтальное масштабирование Citus — дорого, производительность деградировала при росте.

Проблемы с ростом

Производительность начала деградировать:

  • Простые запросы выполнялись за 1.5 секунды и выше
  • Сложные запросы — от 30 секунд до бесконечности (вообще не считались)

Примеры запросов:

  • Простые: считаем количество кликов, денег по клиенту в диапазоне дат
  • Сложные: группировка по нескольким атрибутам, фильтрация по множеству атрибутов

Первая попытка оптимизации: агрегация

Мы попробовали агрессивную агрегацию — создали множество маленьких таблиц для отчётов:

  • Отчёты по операционным системам
  • Отчёты по браузерам
  • Отчёты по языкам
  • И так далее

Плюс осталась одна большая широкая таблица для кастомных отчётов. Это помогло: базовые отчёты стали считаться за 30–150 миллисекунд, освободились ресурсы для сложных запросов (теперь 10 секунд вместо бесконечности). Но горизонтальное масштабирование Citus — это деньги, и много денег. Нужно было новое решение.

Переход на ClickHouse

Почему ClickHouse?

Требования были простые:

  • Горизонтальное масштабирование
  • Отказоустойчивость
  • Низкая стоимость владения
  • Простота эксплуатации

ClickHouse обещал, что всё будет быстро, и мы достаточно быстро его внедрили. Стоимость владения действительно низкая — развёртывание оказалось простым. Мы смогли загрузить данные и даже написали драйвер для нативного протокола (это было проще, чем использовать готовые решения).

Мы убрали Vertica, убрали Citus, и всё стали складывать в ClickHouse.

Первая модель данных: копирование схемы из Citus

Мы сделали прототип — взяли схему, которая была в Citus, и перенесли её в ClickHouse. Несколько запросов работали отлично! Но когда мы начали проверять другие запросы, всё было плохо.

Проблема была в доступе к данным. В ClickHouse есть один индекс — primary key (первичный ключ, кластерный индекс). Он берёт данные и сортирует их согласно ORDER BY, который указан в схеме. Данные отсортированы по первичному ключу, и доступ к данным осуществляется через него.

Например, если у нас индекс был по publisher_id, а нам нужно посмотреть по campaign_id, приходится сканировать большое количество данных — работать медленно.

Решение: отдельные таблицы для разных сущностей

У нас есть две большие категории сущностей:

  • Рекламодатели (advertisers) — рекламные кампании
  • Рекламные площадки (publishers) — всего один (Яндекс)

Мы решили сделать отдельные таблицы для отчётов по рекламодателям и по рекламным площадкам. Это сработало: у нас правильный индекс для рекламодателей, правильный индекс для рекламных площадок — это было быстро и хорошо.

Но как мы хранили данные? У нас не было отдельного хранилища сырых событий и отдельного хранилища агрегированных данных — всё было в одной системе.

Материализованные представления: ошибка масштабирования

Мы подумали: почему бы не использовать материализованные представления (materialized views)? Периодически обсчитывать события, которые мы не можем посчитать в реальном времени.

Материализованные представления в ClickHouse работают очень хорошо, но есть нюанс: они создают триггер на запись. Когда мы вставляем данные в таблицу сырых событий, ClickHouse смотрит, какие есть зависимые таблицы (materialized views), и в цикле применяет блок данных к каждой из них.

Запрос применяется к блоку данных, который мы вставляем, а не ко всей таблице. То есть MAX будет максимумом от этого блока, а не от всех данных таблицы.

Проблема: последовательная запись

В результате мы пишем не в одну таблицу, а ещё плюс 30 (по количеству материализованных представлений). Проблема была в последовательной записи — ClickHouse применяет запросы к блоку данных последовательно для каждой таблицы.

При записи также тратится время на сортировку данных — у всех таблиц под materialized views разные ключи сортировки, нужно сортировать данные для каждой таблицы отдельно.

Итог: мы сами усугубили проблему. У нас была одна проблема, мы её размножили на 30.

Проблема с материализованными представлениями

insert block of data
   |
   v
raw events table
   |
   +---> materialized view #1 (sort by key1)
   |      |
   |      +---> sort block
   |      +---> write to table
   |
   +---> materialized view #2 (sort by key2)
   |      |
   |      +---> sort block
   |      +---> write to table
   |
   +---> ... (x30)
   |
   +---> materialized view #30 (sort by key30)
          |
          +---> sort block
          +---> write to table

Одна запись превращается в 30 последовательных операций сортировки и записи. Производительность падает.

Проблема с партициями и мерджами

ClickHouse использует движок MergeTree. Данные хранятся в партициях, партиции состоят из частей (parts). В каждой части (part) лежат колонки как файлы, где данные записаны один за другим.

Проблема: когда мы пишем в ClickHouse, он всегда создаёт новый part. Чем больше parts, тем больше операций с диском, тем больше проблем. ClickHouse на фоне постоянно делает мерджи — склеивает части, делая их меньше и меньше.

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

Финальное решение: массивы и структуры данных

Мы решили уменьшить количество таблиц, но не хотели потерять функциональность и скорость. Решение — использовать массивы для хранения отчётов.

Как это работает

Вместо множества отдельных таблиц мы храним данные в массивах:

  • Один массив — идентификаторы колонок (например, браузеры)
  • Другой массив — значения к ним (клики, показы и т.д.)

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

В одной таблице можем хранить сразу большое количество отчётов — все те мелкие отчёты, которые у нас были (по операционным системам, браузерам, языкам и т.д.).

Решение: массивы вместо множества таблиц

До (30 таблиц):
table_os_reports
table_browser_reports
table_language_reports
... (x30)
   |
   +---> 30 materialized views
   +---> 30 сортировок при записи

После (5 таблиц с массивами):
reports_table
   |
   +---> row: campaign_id, date
   |      |
   |      +---> array: browser_ids = [1, 2, 3, ...]
   |      +---> array: browser_clicks = [100, 200, 150, ...]
   |      +---> array: os_ids = [1, 2, ...]
   |      +---> array: os_clicks = [50, 75, ...]
   |
   +---> arrayJoin() -> разворачивает в строки при запросе

Одна запись вместо 30, одна сортировка вместо 30.

Структуры данных (Map)

Потом мы пришли к структурам — Map. Это очень удобный инструмент. Например, у нас структура browser_id → browser_count, clicks, и всё что угодно может быть — price, это колонки к нему.

Все длины колонок в структуре должны быть одинаковые. Получается, на каждый индикатор у нас есть какое-то значение с тем же индексом.

Движок ReplacingMergeTree

Мы используем движок ReplacingMergeTree. Это позволяет в некоторых случаях не хранить избыточные агрегаты. Например, для суммы можно хранить просто цифру, а не агрегат — это будет работать быстрее.

Оптимизация при мердже: когда мы пишем данные, они попадают в блок данных, который записывается на диск. Там происходит мердж, и здесь выполняется оптимизация. Для строк, которые отсортированы в блоках последовательно, мы можем их просуммировать или удалить дубликаты. Но это происходит только во время мерджа, не сразу при записи.

Оптимизация: уменьшение количества таблиц

В итоге мы получили:

  • 5 таблиц отчётов вместо 30
  • Две категории: рекламодатели (advertisers) и площадки (publishers)
  • Плюс одна таблица, в которой мы храним данные почти как есть (практически без агрегации), но в реальном времени мы в неё не пишем

Проблемы и их решения

Проблема с гранулярностью индексов

Число 8192 — это гранулярность индекса по умолчанию в ClickHouse1. ClickHouse индексирует не каждую строку, а каждые 8192 строки (по умолчанию) — хранит min/max значения для гранулы.

Когда мы читаем данные с диска, нам нужно распаковать их в память, прочитать все остальные блоки дополнительно (в худшем случае в два раза больше, чем размер индекса), и потом наложить фильтр.

Проблема: у нас был клиент с большими массивами (десятки миллионов значений вместо нескольких тысяч). Когда мы читали данные для клиента с малым количеством данных, ClickHouse читал все соседние блоки, распаковывал их в память, и памяти не хватало — запросы падали.

Решение: мы уменьшили гранулярность индекса. Это не сильно повлияло на использование памяти, но запросы стали работать действительно быстрее, и они стали помещаться в память.

Практическая заметка. Гранулярность индекса — это баланс между размером индекса и точностью фильтрации. Меньшая гранулярность даёт более точную фильтрацию, но увеличивает размер индекса. При работе с данными разного размера (например, когда у одного клиента миллионы записей, а у другого — тысячи) может потребоваться настройка гранулярности под конкретные сценарии. Источник: документация ClickHouse и практический опыт оптимизации.

Оптимизатор запросов

Не все запросы работали быстро. Мы посмотрели и поняли: проблема в индексах. Один индекс — не всегда оптимально. Данные отсортированы по одному ключу, а запросы идут по другому.

Решение: мы написали оптимизатор запросов. Он держит связи между объектами в памяти, периодически загружает справочники. Если у нас есть какой-то объект в условии, но мы понимаем, что по нему индекс тоже есть, мы добавляем это поле в условия фильтрации запроса.

До 30% запросов оптимизируются автоматически. Среднее время запросов упало с 200–250 миллисекунд до 55 миллисекунд.

Итоги и рекомендации

Что мы получили

  1. Более простая поддерживаемая система — это главное преимущество. Для любой системы, которую пишут люди, поддерживаемость важна не только программистам, но и для бизнеса.

  2. Больше данных на том же оборудовании — можем хранить и обрабатывать больше данных, что даёт нам существенное преимущество.

  3. Производительность:

    • 2 параллельных мерджа вместо постоянно 16
    • 5 таблиц отчётов вместо 30
    • Среднее время запросов: 55 миллисекунд

Чего не хватает

  1. Транзакций — у нас несколько таблиц, и это грозит потерей связанности данных. Таблицы могут различаться, потому что туда записали что-то в разное время.

  2. Изменения ключа сортировки — сейчас это обещали выкатить. Появится третий ключ: первичный ключ (обязательно индексируется), ключ сортировки (можно будет менять), и это позволит изменять ключ без переливания таблиц и сэкономит память.

  3. Индексов структур данных — тоже в разработке, нужно будет посмотреть.

Рекомендации

  1. Читайте документацию — это первое и самое важное.

  2. Понимайте систему — ClickHouse достаточно уникальная система, она не совсем привычна, если вы переходите с другой базы данных. У неё свой подход, она работает по-другому.

  3. Не бойтесь проблем — если вы столкнётесь с трудностями, в первое время точно не будет легко. Задача серьёзная, проблемы у вас обязательно будут.

  4. Используйте сообщество — у ClickHouse очень хорошее сообщество в Telegram, там можно задавать вопросы, и вам помогут.

  5. Не используйте везде — лучше, чтобы у вас был уже опыт либо понимание, как это работает и как это должно работать.

Полезные материалы

Сноски

Заключение

ClickHouse — это мощная система, которая может работать очень быстро, но требует понимания её внутренней работы. Наш опыт показал, что даже при понимании системы можно столкнуться с проблемами, но правильный подход к моделированию данных и оптимизации позволяет получить отличные результаты.

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

  1. Гранулярность индекса по умолчанию в ClickHouse — 8192 строки. Это значение можно изменить через параметр index_granularity при создании таблицы. Подробнее см. документацию ClickHouse. ↩︎