ClickHouse для событий сайта: схема таблиц и оценка диска на месяц

Сырые JSON-события за день весят много, и соблазн прост: взять размер дневного файла, умножить на тридцать и заказать диск с запасом. Но это расчёт размера логов, а не размера таблицы ClickHouse. В базе данные лежат колонками, отсортированы, сжаты, снабжены разреженным индексом, а часть места на время занимают слияния. Поэтому сначала разберём одно событие и таблицу под него, а потом научимся измерять то, что реально лежит на диске.

Одно событие: что из него нужно аналитике

Типичное событие сайта в JSON выглядит щедро: время, адрес страницы, реферер, строка User-Agent целиком, язык, размер экрана, имя события, десяток параметров, иногда идентификатор посетителя и IP-адрес. Каждое поле хочется сохранить «на всякий случай». Но у каждой колонки есть цена на диске, а у лишнего персонального поля ещё и цена риска. Принцип минимизации данных из W3C Privacy Principles говорит ровно об этом: ограничивать данные тем, что необходимо для цели, потому что меньше данных — меньше риск раскрытия и нежелательного использования, в том числе для данных, которые заранее не признаны чувствительными.

Начните не со схемы, а со списка вопросов, на которые должна отвечать таблица. Допустим, это такие вопросы:

• сколько просмотров страниц и целевых действий было по дням и часам;
• какие страницы и источники трафика приводят к целевому действию;
• как распределяются события по типам устройств и странам или языкам;
• как меняется доля ошибок на клиентской стороне.

Из этих вопросов поля выводятся почти механически. Нужны время события, тип события, путь страницы без строки запроса, источник трафика в укрупнённом виде (домен реферера, а не полный адрес), класс устройства, язык, пара полей для ошибки. Постоянный идентификатор посетителя для такого набора вопросов не нужен вовсе: мы считаем события и страницы, а не прослеживаем людей. IP-адрес и сырой User-Agent тоже лучше не тащить: класс устройства и браузер можно вычислить на входе и записать короткой меткой. Важная оговорка: исключение явных полей само по себе не делает набор анонимным. Если вы добавите длинный параметр запроса с чем-то личным, он всё равно окажется в таблице, так что путь страницы стоит чистить до записи.

Теперь типы. Здесь экономия видна сразу, потому что на сжатие влияют типы данных, ключ сортировки и кодеки: всё это задаётся схемой, как отмечено в материале о сжатии в ClickHouse. Тип события, класс устройства и язык — короткие наборы значений, для них подходит LowCardinality(String) или Enum. Время — DateTime. Путь страницы — обычная строка, но заранее нормализованная: без параметров и идентификаторов в адресе, иначе число различных значений разрастётся, и сжатие ухудшится.

CREATE TABLE web_events
(
    event_time DateTime,
    event_type LowCardinality(String),
    page_path String,
    ref_domain LowCardinality(String),
    device LowCardinality(String),
    lang LowCardinality(String),
    error_code UInt16
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, toDate(event_time), page_path)
TTL event_time + INTERVAL 30 DAY DELETE;

Конструкции ENGINE = MergeTree(), ORDER BY, PARTITION BY и TTL, а также CODEC для колонок описаны в документации MergeTree. Конкретный набор полей и ключ выше — наш учебный вариант, а не готовый рецепт: ниже мы сравним его с соперником.

Два порядка данных и два типовых запроса

ORDER BY в MergeTree задаёт ключ сортировки, а если PRIMARY KEY отдельно не указан, он же служит первичным. Это не ограничение уникальности: строки с одинаковым ключом разрешены, и для потока событий это как раз то, что нужно, ведь два одинаковых клика в одну секунду — два события. Данные сортируются внутри частей, и от порядка зависят две вещи: насколько быстро запрос отсекает ненужное и насколько хорошо сжимаются колонки, потому что похожие значения оказываются рядом.

Возьмём двух кандидатов и два запроса, которые будут выполняться чаще всего.

Запрос первого рода: количество событий по типам за последние семь дней. Запрос второго рода: динамика ошибок по страницам за две недели. Для первого естественно начинать ключ с даты: нужный диапазон лежит подряд. Для второго лучше начать с типа события: все ошибки сгруппированы вместе, а дата идёт следом. Если ваш дашборд в основном обзорный, выигрывает A, если вы чаще вскрываете отдельные типы событий, то B. Универсального ответа нет, и честный способ выбрать — создать две таблицы с одинаковыми данными и сравнить их размеры и время ваших запросов. Размеры мы научимся сравнивать ниже; время запросов можно смотреть по своим реальным выборкам.

Заметьте влияние на сжатие. В варианте B внутри одного типа события страницы и даты упорядочены, соседние значения похожи, и колонки сжимаются плотнее, чем если бы разные типы событий перемешивались внутри дня. В варианте A порядок по дате даёт аккуратную колонку времени, но тип события чередуется. Какой из эффектов перевесит, зависит от вашего распределения событий, поэтому коэффициент сжатия нельзя взять из чужой статьи. Для колонки времени можно попробовать цепочку CODEC(Delta, ZSTD): Delta полезен для последовательностей с небольшими разностями соседних значений. Но в разобранном документацией сравнении Delta улучшает одни колонки и ухудшает другие, а повышение уровня ZSTD увеличивает затраты на сжатие. Поэтому кодек стоит принимать только после сравнения на своих данных, колонка за колонкой, а не по общим рекомендациям.

Партиции: инструмент жизненного цикла, не ускоритель

Соблазнительно разбить таблицу по дням или даже по типам событий: вдруг запросы ускорятся. Однако, как объясняется в разделе о выборе ключа партиционирования, партиции прежде всего обслуживают жизненный цикл данных. При вставке строки раскладываются по значениям ключа в отдельные части, а высокая кардинальность ключа увеличивает число частей и может закончиться ошибкой too many parts. Влияние на чтение неоднозначно: одним запросам с подходящим фильтром партиции помогают, а запросы, читающие много партиций, могут из-за них замедляться. К тому же части разных партиций между собой не сливаются.

Для хранения в тридцать дней разумен самый грубый вариант: помесячная партиция toYYYYMM(event_time), которая показана и в документации MergeTree. Тогда вам не придётся отслеживать сотни мелких групп частей. Минус месячной партиции мы увидим позже при TTL: старые строки удаляются при слиянии, а не по часам, и партиция месяца живёт дольше, чем хочется. Если для вас важнее точное чистое удаление, можно рассмотреть и более мелкий ключ, например по неделе, но тогда придётся самим следить за числом частей. Партиционирование по идентификаторам клиентов или посетителей не рекомендуется вовсе, а у нас такого идентификатора нет, что кстати.

Откуда берутся части и почему мелкие вставки дороги

Каждая вставка создаёт новую отсортированную неизменяемую часть. Фоновые слияния потом объединяют небольшие части в более крупные внутри партиции, как описано в разделе о слияниях частей. Если скрипт отправляет по одному событию на запрос, части рождаются быстрее, чем фон успевает их объединять. У каждой вставки есть постоянные накладные расходы, и в материале о пакетных вставках рекомендуется собирать на клиенте не менее 1000 строк, а ориентиром называют 10000–100000 строк на пакет. Это рекомендация, а не обязательный размер для любого запроса. Если клиент пакеты собирать не может, например события приходят от множества браузеров, можно переложить пакетирование на сервер с помощью asynchronous inserts, но семантику подтверждения записи тогда нужно настроить и понять отдельно.

Для нашего потока лучше поставить между сайтом и ClickHouse небольшой буфер: сервис или очередь, которые накапливают события несколько секунд и пишут пачкой. Это упрощает и диск: меньше частей — меньше метаданных, меньше файлов с засечками индекса и меньше фоновой работы. Заметим сразу, что мелкие вставки раздувают не только число частей, но и общий объём, поскольку маленькая часть сжимается хуже большой: у неё нет шанса собрать похожие значения рядом. До слияния вы платите диском за неудобный режим записи.

Слияние — не бесплатная магия. Оно читает исходные части, распаковывает, объединяет, снова сжимает и записывает новые файлы колонок, индекс и метаданные результата. Всё это тратит процессор, память и диск, а несколько фоновых потоков могут работать одновременно. И главное для нашего расчёта: исходные части после слияния помечаются неактивными и удаляются позже, через настраиваемый интервал. Значит, пока идёт слияние и некоторое время после него, на диске лежат и старые, и новая части. Это и есть та самая временная надбавка, которую простое «размер данных × 30» не учитывает.

Измеряем: system.parts по активным частям

Теперь от теории к цифрам. Каждая строка таблицы system.parts описывает одну часть таблицы MergeTree. Нас интересуют несколько полей: active=1 отмечает используемые части; rows — число строк; bytes_on_disk — суммарный размер всех файлов части; data_compressed_bytes и data_uncompressed_bytes — размеры сжатых и несжатых данных без вспомогательных файлов вроде marks; marks_bytes отдельно учитывает засечки. Поля database, table и partition позволяют ограничиться своей таблицей и партицией.

SELECT
    partition,
    count() AS parts,
    sum(rows) AS rows,
    sum(data_uncompressed_bytes) AS raw_bytes,
    sum(data_compressed_bytes) AS compressed_bytes,
    sum(marks_bytes) AS marks_bytes,
    sum(bytes_on_disk) AS bytes_on_disk
FROM system.parts
WHERE database = currentDatabase()
  AND table = 'web_events'
  AND active = 1
GROUP BY partition
ORDER BY partition;

Этот запрос — наша инженерная выборка по документированным полям, а не рецепт из справки. Зачем столько столбцов? Потому что каждый отвечает на свой вопрос. Разница между raw_bytes и compressed_bytes показывает, как сжались колонки на вашем распределении значений. Сумма bytes_on_disk сообщает полный размер файлов активных частей, и именно она лежит в основе бюджета, а не data_compressed_bytes: последний не включает все служебные файлы. Отношение bytes_on_disk к rows даёт главную величину — байты на событие на диске после сжатия, с индексом и метаданными частей. Не путайте её с размером JSON-строки события: это совсем другое число.

Чтобы выяснить, какие колонки стоят дороже всего, обратитесь к system.columns, где в тех же терминах доступны data_compressed_bytes и data_uncompressed_bytes по колонкам; такое сравнение показано и в материале о сжатии. Часто оказывается, что львиную долю занимает одна колонка — обычно путь страницы или какой-то параметр. Это и есть кандидат на нормализацию либо на LowCardinality, но решать по замеру, а не по ощущению.

Работает это и как способ сравнить варианты A и B. Создайте две таблицы, залейте в обе один и тот же срез за сутки, дождитесь слияний и сравните bytes_on_disk на строку. Вот пример рассуждения с условными числами, подставленными для иллюстрации: пусть при варианте A вышло 18 байт на событие, а при B — 15. При потоке в 20 миллионов событий в сутки разница составит около 60 МБ в сутки, или порядка 1,8 ГБ за тридцать дней. Для диска в десятки гигабайт это заметно, но это не повод отказываться от варианта, который лучше отвечает на ваши запросы: скорость нужных выборок может весить больше, чем два гигабайта. Решение остаётся за вами, но теперь оно основано на разности в байтах, а не на спорах.

Что идёт в расчёт кроме активных данных

Есть три статьи, которых нет в сумме по активным частям.

Первая — неактивные части. Запрос выше их исключает условием active = 1. Но после слияния старые части ещё какое-то время лежат на диске и ждут удаления. Если убрать условие и сгруппировать по active, вы увидите, сколько места держится за уже выполненные слияния. Эта величина колеблется: сразу после крупного слияния она велика, потом падает. Поэтому смотрите на максимум за несколько дней, а не на случайный момент.

Вторая — слияния в процессе. Таблица system.merges показывает выполняющиеся прямо сейчас слияния и мутации: число объединяемых частей num_parts, прогресс progress от 0 до 1, elapsed в секундах, исходные source_part_names и результирующую result_part_name, а также total_size_bytes_compressed, rows_read, rows_written и memory_usage. Это состояние текущих операций, а не исторический журнал, поэтому вы не прочтёте там вчерашние слияния. Чтобы получить картину, придётся сэмплировать: например, раз в минуту сохранять выборку в отдельную маленькую таблицу и потом смотреть максимум суммы total_size_bytes_compressed. Он даёт оценку того, сколько данных одновременно переписывается и, значит, сколько места под новую часть нужно вдобавок к исходным.

SELECT
    table,
    num_parts,
    round(progress, 2) AS progress,
    round(elapsed) AS elapsed_s,
    total_size_bytes_compressed,
    result_part_name,
    is_mutation
FROM system.merges
WHERE database = currentDatabase()
  AND table = 'web_events';

Практическое следствие: запас под слияния нужен не вообще, а под самое большое слияние, которое возможно в вашей схеме. При помесячной партиции самые крупные части лежат внутри одного месяца, и худший случай по месту — это объединение крупных частей месяца, пока исходные ещё не удалены. Грубо рассуждая, нужен резерв порядка размера самого крупного одновременно сливаемого набора частей. Точное число покажет ваш мониторинг, а не наша формула.

Третья — мутации. Классические ALTER TABLE ... UPDATE и DELETE не меняют строку на месте: как сказано в разделе об избегании мутаций, они выполняются как фоновые мутации затронутых частей, создают новые части и держат исходные до готовности замены. Даже небольшое изменение может вызвать значительную перезапись. В нашей таблице событий мутации лучше не планировать вовсе: если нужно убрать событие, придётся переписывать целые части, и диск временно почти удвоит занятое место для затронутых данных. Заодно подумайте, зачем вам это: если событие нужно удалять по запросу, то причина должна быть решена выбором полей, а не массовыми правками.

TTL: тридцать дней — это не ровно тридцать дней

Для хранения в месяц в схеме стоит TTL event_time + INTERVAL 30 DAY DELETE. Условие вычисляется как Date или DateTime, а пример с INTERVAL 1 MONTH DELETE приведён в документации MergeTree. Но есть важная деталь: удаление просроченных строк происходит при слиянии частей. Между слияниями SELECT ещё может возвращать строки с истёкшим TTL, а диск — держать старые данные. Следовательно, фактический срок хранения чуть больше расчётного, и в бюджет надо закладывать не ровно тридцать суточных объёмов, а тридцать плюс «хвост» — те части, до которых слияние ещё не добралось.

Как оценить хвост? Посмотрите в system.parts минимальное значение min_time или идентификатор партиции у самых старых активных частей и сравните с датой тридцатидневной давности. Если там регулярно лежат части старше срока, хвост ненулевой, и его размер нужно приплюсовать. Не стоит обещать себе, что данные исчезнут в момент истечения: это не так. Если срок хранения для вас юридически или договорно критичен, TTL — не единственный механизм, и вопрос стоит проверить отдельно, а не полагаться на фоновое удаление.

Тут и проявляется компромисс месячных партиций: часть частей прошлого месяца ещё живёт, пока в ней остаются непросроченные строки, и слить её с частью следующего месяца нельзя, ведь части разных партиций не сливаются. Если вас раздражает такой хвост, обдумайте партиции покороче, помня о цене в числе частей.

Собираем бюджет месяца

Теперь можно сложить формулу из измеренных величин. Пусть:

• N — среднее число событий в сутки;
• b — байты на событие на диске: sum(bytes_on_disk) / sum(rows) по активным частям;
• D — срок хранения в сутках;
• h — хвост TTL в сутках: сколько лишнего обычно лежит сверх срока;
• M — максимальный размер одновременно перезаписываемых данных, оцененный по замерам system.merges;
• I — максимум неактивных частей, ждущих удаления.

Тогда ориентир для диска таблицы: N × b × (D + h) + M + I. Остальное пространство нужно под операционную систему, журналы и другие таблицы ClickHouse, а файловую систему не рекомендуется заполнять до конца, так что к результату добавляйте свой запас. Эта формула — наша методика, и каждая переменная в ней измеряется, а не подбирается.

Условный пример, чтобы увидеть порядок величин. Пусть N = 20 миллионов событий в сутки, b = 16 байт, D = 30, h = 3. Тогда основная часть равна 20 000 000 × 16 × 33 ≈ 10,6 ГБ. Допустим, замеры показали M около 2 ГБ и I около 1 ГБ. Получаем примерно 13,6 ГБ. Если в ту же копию ClickHouse подмешать JSON размером 600 байт на событие, то наивный подсчёт «сырой размер × 30» даст 360 ГБ — разница в десятки раз. Именно это расхождение и скрывает сценарий из начала статьи: сырой JSON не равен таблице.

Эти цифры здесь только иллюстрация; ваш b может оказаться и меньше, и намного больше, в зависимости от путей страниц, числа различных значений и выбранного ключа. Не переносите наши числа к себе и не ищите «типичный коэффициент сжатия»: схема и данные определяют его целиком.

Чувствительность: что будет, если сайт изменится

Формула хороша тем, что показывает, на что реагировать при росте. Три рычага работают по-разному.

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

Второй — срок хранения D. Перейти с тридцати дней на девяносто — значит утроить основную часть бюджета, а хвост h и резерв M тоже вырастут, потому что крупнее станут части. Здесь стоит подумать о двухуровневом хранении: подробные события тридцать дней, а дальше только агрегаты по дням в отдельной маленькой таблице. Агрегаты занимают на порядки меньше, потому что каждая строка — день и тип события, а не отдельный клик. Но состав агрегата нужно придумать заранее, иначе по нему не ответить на новые вопросы.

Третий — само поведение схемы. Добавление новой колонки с тонким набором значений почти ничего не стоит, а колонка со строкой произвольной длины способна удвоить b. Перед внедрением нового поля залейте срез в тестовую таблицу и сравните sum(bytes_on_disk)/sum(rows) до и после. Так поле получает цену в байтах до того, как попадёт в основную таблицу.

Где всё это размещать

Тридцать дней событий и фоновые слияния — рабочая нагрузка на диск и процессор, поэтому тип хранилища играет роль. На VPS MAATRIX предлагается NVMe-хранилище, процессоры AMD EPYC и root-доступ. Для установки ClickHouse понадобится именно root, а настройка описана в материале как установить и настроить ClickHouse на VPS. Размеры дисков в линейке разные: от 40 ГБ NVMe у тарифа Ba до 320 ГБ у Djed, но эти ресурсы не обещание ёмкости, то есть не гарантия, что на них поместится какой-то определённый поток событий. Выбирать тариф нужно после того, как вы получили результат формулы выше для своего потока: в нашем условном примере выход около 14 ГБ требует диска заметно больше, чем это число, с учётом системы, журналов и запаса, а реальные цифры вы подставите сами. Особенно внимательно отнеситесь к оперативной памяти и числу ядер: слияния потребляют процессор и память, а их потребление видно в memory_usage таблицы system.merges.

Формула, которую нужно пересчитывать

Итоговая формула — не константа, а измерительный прибор. Раз в неделю или после любого заметного изменения на сайте запускайте запрос по system.parts, записывайте b и N, смотрите самый старый активный срез на предмет хвоста h и сэмплируйте system.merges для оценки M. Если b вдруг прыгнул, значит поменялись данные: появилось поле с высокой кардинальностью или пошёл мусорный трафик. Если растёт число частей в одной партиции, значит, мелкие вставки опять обгоняют слияния. Если сумма неактивных частей не падает, слияния и удаление не успевают за потоком. Каждая из этих ситуаций видна в числах задолго до того, как диск закончится, а расчёт на месяц превращается в регулярную сверку: байты на событие, события в сутки, срок хранения, хвост и резерв под слияния.