Модуль 11: Глубокая оптимизация ClickHouse для витрин: внутренняя “физика”, планы выполнения, настройки, профилирование, память/CPU/IO, JOIN/AGG/ORDER BY, Kafka/S3, репликация, и рецепты устранения проблем
Это техническая статья с подробными объяснениями «зачем и как», кодом, чек-листами и реальными кейсами. Цель — чтобы инженер витрин умел диагностировать и чинить производительность и надёжность, а не только «пересоздавать таблицы»
Картина мира: где берётся скорость ClickHouse
ClickHouse — это колоночное хранение + векторное выполнение + MergeTree (части/мерджи) + префиксная сортировка (ORDER BY).
Скорость чтения определяется 4 факторами:
- Партиции и ключ сортировки (пропуск партиций + «узкие» чтения по первичному ключу).
- Объём прочитанных байтов (только нужные колонки, PREWHERE, data-skipping индексы).
- Типы вычислений (агрегаты-состояния вместо тяжёлых distinct/перцентилей «на лету»).
- Параллелизм и физика (части, потоки, фоновые мерджи, NVMe/S3-кэш, сети).
Вставки и обслуживание зависят от размера блоков, числа мелких частей, фоновых пулов, и «здоровья» репликации/Keeper.
Диагностика: с чего начинать
Быстрый осмотр (5 минут)
- Топ тяжёлых запросов за сутки:
SELECT query, any(user) u,
sum(read_bytes) rb, sum(read_rows) rr,
round(avg(query_duration_ms)/1000,2) s
FROM system.query_log
WHERE event_time >= now()-INTERVAL 1 DAY AND type='QueryFinish'
GROUP BY query ORDER BY rb DESC LIMIT 50;- Активные части/мерджи:
SELECT table, partition, count() parts, sum(rows) r, sum(bytes_on_disk) b FROM system.parts WHERE active GROUP BY table, partition ORDER BY parts DESC LIMIT 20; SELECT table, elapsed, progress FROM system.merges ORDER BY elapsed DESC LIMIT 20;
- Лаг репликации:
SELECT database, table, count() AS q, max(create_time) latest FROM system.replication_queue GROUP BY database, table ORDER BY q DESC;
Профилирование конкретного запроса
- EXPLAIN PIPELINE / EXPLAIN AST — понять этапы, потоки, пушдаун.
-
В system.query_log: read_bytes, read_rows, memory_usage, ProfileEvents.* (например, SelectedParts, SelectedMarks, ResultRows, OSReadBytes).
Чтение «много, но быстро» — дисковая/сетовая пропускная; «мало, но долго» — CPU/функции/джойны.
Хранение: партиции, ключ, гранулярность, индексы, проекции
Партиционирование
- События → день; продажи/финансы → месяц.
-
Критерий: чтобы ретро-пересчёты/перестановка партиций делались одной командой.
Риск: партиция = час → тысячами частей забьёте мерджи.
Правило: чем мельче лодка (партиция), тем тяжелее волны (мерджи).
ORDER BY под реальные WHERE
- События: (event_time, user_id)
- Продажи: (day, shop_id, sku_id)
-
Балансы: (day, account_id)
Антипаттерн: длинный ключ из 5–7 полей «на всякий случай» → рост метаданных, без ощутимого скипа.
Гранулярность индекса
- index_granularity (обычно 8192 строк) и/или index_granularity_bytes.
- Для «узких» запросов маленькая гранула помогает скипать, но ускорит ли — проверяйте на стенде (слишком мелкая → больше “marks”, выше overhead).
Data-skipping индексы (осторожно)
- bloom_filter — для IN/LIKE по ID/строкам;
- set — малые домены;
-
minmax — есть «из коробки» на ключе.
Правило: индекс ставим после замеров, на паттерне, где он экономит read_bytes.
Проекции (projections)
- Могут ускорять стабильные GROUP BY/ORDER BY. Включать после тестов; это не замена хорошему ORDER BY и агрегатам-состояниям.
Вставки: блоки, “мелкая дробь”, Kafka/MV
Размер вставок
- Вставляйте блоками сотни тысяч/миллионы строк.
- Для stream: буферизуйте (Kafka Engine/MV), настраивайте микро-батчи.
Kafka → MV → MergeTree
Ключевые параметры (идея):
- kafka_num_consumers: 2–12 (по партициям Kafka и CPU).
- kafka_max_block_size / частота флашей (не роняйте в 1–5 тыс. строк).
-
В MV — агрегировать окно, а не «всю историю».
Риск: тысячи мелких parts → фоновые мерджи «задыхаются», свежесть падает.
Фикс: укрупняем блок, используем буферную таблицу и периодические INSERT … SELECT.
Асинхронные вставки
- async_insert / wait_for_async_insert — снижает накладные, но проверяйте на консистентности в ваших SLА.
Агрегации и DISTINCT: “состояния” вместо «на лету»
Суммы/количества — sumState/…Merge
-- запись sumState(amount) AS amount_state -- чтение sumMerge(amount_state) AS net_sales
Уникальные — uniq*State/…Merge
- Быстрый стандарт: uniqCombinedState / uniqCombinedMerge.
-
Точный, но дорогой: uniqExact.
Единожды договоритесь о методе и фиксируйте в паспорте метрики (см. Модуль 1/10).
Перцентили — TDigest/Timing в состоянии
- На сырых миллиардах — нельзя.
- Паттерн: минутные/часовые состояния quantileTDigestState(p)(x) → чтение …Merge.
JOIN: стратегии, память, распределённые JOIN
Малое к большому: словари vs JOIN
- Небольшие справочники → Dictionary (dictGet*()), быстрее и стабильнее, чем JOIN.
- SCD по дате → ASOF JOIN или материализация «на дату факта» при записи.
Хеш-JOIN и лимиты
- Следите за max_bytes_in_join, max_rows_in_join.
- При риске spill — лучше предагрегировать/сузить набор до JOIN.
Распределённые запросы
- Пушдаун фильтров на шарды обязателен.
- Делайте локальную агрегацию на шардах, потом сбор.
- Ключ шардинга согласуйте с WHERE (часто tenant_id/region_id).
Риск: Distributed тянет «пол-мира», локальный быстрый.
Фикс: пересмотреть шардинг и WHERE, настроить preferred_block_size_bytes, max_threads, убедиться в pushdown.
Память, экстернализация и профили
Глобальные и пользовательские лимиты
- max_memory_usage (роль/профиль), max_threads, max_execution_time.
- Для BI-ролей — жёсткие профили (см. Модуль 6/10).
Внешняя агрегация/сортировка (spill)
- Пороги: max_bytes_before_external_group_by, max_bytes_before_external_sort.
- Диск под spill должен быть быстрым и с запасом; тем не менее spill — это уже “SOS”, оптимизируйте запрос/агрегаты.
Блок и вектор
- max_block_size (по строкам) и «узкие» колонки в PREWHERE уменьшают IO/CPU.
- Не притаскивайте широкие строки/JSON «просто так».
Фоновые задачи: мерджи, мутации, потоки
Пулы фоновых задач
- Пулы mergе/fetch/исправлений должны соответствовать CPU/IO.
- Симптом: system.merges переполнен, elapsed растёт → укрупняйте вставки, уменьшайте число малых партиций, временно «сбавьте» ingest.
Мутации (ALTER UPDATE/DELETE)
- Это дорогая операция (перезапись частей). Используйте по партициям и в низкую нагрузку.
- По возможности заменяйте ночным overlay партиции (пересборка с нуля → REPLACE PARTITION).
Репликация, Keeper и кворум
Реплики и кворум вставки
- Для критичных таблиц → 2 реплики на шард, insert_quorum по потребности.
- Следить за system.replication_queue, не копить сотни/тысячи задач на таблицу.
Keeper (координация)
- Минимум 3 узла, лучше 5; следите за latency и логом кворума.
- Сеть и диск Keeper — быстро и стабильно; “шумные соседи” убивают кластер.
Риск: лаг репликации → «разъезд» данных между репликами и таймауты чтения.
Фикс: разгрузить ingest/мерджи, увеличить фоновые пулы, проверить сеть/дисковую очередь.
S3/объектное и кэш
Диски и политики
- storage_policy: hot(NVMe) → warm → cold(S3).
- TTL MOVE TO VOLUME — автоматический «перевоз» старых партиций.
Файловый кэш и размеры частей
- Включайте filesystem cache для S3; выбирайте разумный размер кэша (десятки-сотни ГБ на ноду).
- Настраивайте минимальный размер part для выгоды кэша; слишком мелкие части не кэшируются эффективно.
Риск: сетевой “штормик” из-за S3 на «горячем окне».
Фикс: держите горячие N дней на локальном NVMe; только «архив» — в S3.
Планы выполнения: EXPLAIN/PIPELINE и “красные флажки”
Смотрим на:
- число потоков чтения, фазы ReadFromMergeTree, AggregatingTransform, PartialSorting, MergingSorted.
- попали ли фильтры в PREWHERE/WHERE pushdown;
- случился ли read-in-order (экономит сортировку) при ORDER BY совместимом с ключом;
- есть ли слив на PartialSorting/ExternalSorting (признак больших промежуточных).
Красные флаги в логах:
- SelectedParts ≫ разумного (дробь);
- SelectedMarks близко к MarksTotal (плохой WHERE/ключ);
- MemoryTracker превышения;
- ReadFromStorage без предикатов.
Тюнинг по типу нагрузки (рецепты)
События (NRT, много строк, лёгкие поля)
- Партиция день; ORDER BY (event_time, user_id).
- Ingest: Kafka→MV, микробатчи.
- Агрегаты: минутные/часовые состояния (DAU, p95, topK).
- BI → только из rollup-вьюх; дефолтный период 24–48 часов.
Риски: part-explosion, перцентили «на лету».
Фиксы: укрупнение блоков, TDigest состояния, алерты parts/partition.
Ритейл-продажи (денежные, фиксированная гранулярность)
- Партиция месяц; ORDER BY (day, shop_id, sku_id, tx_id).
- AggregatingMergeTree с sumState и uniq*State.
- Ретро-окно 14–30 дней.
Риски: Summing на ретро-данных; валюты/календари «смешаны».
Фиксы: только Aggregating; раздельные VIEW «на дату операции/отчёта».
Финансы/остатки/CDC
- Ingest Debezium→STAGE (нормализация upsert).
- Факт: ReplacingMergeTree(version) (BI без FINAL).
- Ночной overlay окна и агрегация в состояния.
Риски: «томбы», дубли апдейтов.
Фиксы: ключ идемпотентности, версия, quarantine нарушений.
Кейсы «почини в проде»
«Вчера дашборды стали медленнее ×3»
Диагностика:
- query_log: изменились read_bytes или время на PartialSorting/Aggregating.
- parts: выросло число parts → мерджи отстают.
- replication_queue: лаг?
Фиксы:
- Включить ограничение «дорогих» плиток (дефолтный период/лимиты).
- Оптимизировать вставки (микробатчи), точечный OPTIMIZE партиций.
- Вынести тяжёлые uniq/quantile в состояния.
«Ночью упала свежесть на 2 часа»
Диагностика:
- system.merges/parts → part-explosion, крупная мутация.
- Kafka задержка / MV «залипли».
Фиксы:
- Приостановить глубокие ретро-пересчёты; выкатить ingestion-буфер.
- Растянуть фоновый пул, отложить мутации.
«Вчера Net Sales не сходится с CORE»
Диагностика:
- Новый статус в источнике → UNKNOWN в маппинге.
- Смена курса валют «задним числом».
Фиксы:
- Обновить маппинг, ретро-пересчёт окна; зафиксировать в паспорте метрики.
Настройки: «шпаргалка» (начните с малого, меряйте до/после)
Конкретные значения зависят от железа и нагрузки. Ниже — куда смотреть.
-
Память/потоки (профили пользователей):
max_memory_usage, max_threads, max_execution_time, max_rows_to_read, max_bytes_to_read. -
Агрегации/сортировки:
group_by_two_level_threshold, group_by_two_level_threshold_bytes,
max_bytes_before_external_group_by, max_bytes_before_external_sort. -
Чтение:
max_streams, merge_tree_min_rows_for_concurrent_read, read_in_order_two_level_merge_threshold, optimize_read_in_order. -
Вставки:
max_insert_block_size, async_insert, input_format_parallel_parsing. -
JOIN:
max_bytes_in_join, max_rows_in_join, join_algorithm (по ситуации). -
Фоны:
размеры пулов merges/fetch/assign; следите, чтобы не был занижен относительно CPU. -
S3/кэш:
объём filesystem cache, политики томов, размер части для выгоды кэша.
Правило: меняете 1–2 параметра за раз → замер до/после на типовых запросах и «боевом» окне данных.
Антипаттерны и риски (сводная таблица)
|
Антипаттерн |
Симптом |
Как исправить |
|---|---|---|
|
FINAL в продуктивных VIEW |
Взрыв latency, память |
Исключить FINAL, обеспечить чистую запись (Replacing с версией / Aggregating-состояния) |
|
COUNT DISTINCT/перцентили «на лету» |
Десятки секунд/минут |
Хранить uniq*State/quantile*State и читать …Merge |
|
toDate(ts) в WHERE |
FULL SCAN партиций |
Фильтр по интервалу; бакет в SELECT |
|
Длинный ORDER BY |
Рост метаданных, эффекта нет |
Короткий префикс под реальные WHERE |
|
Part-explosion |
Мерджи отстают, свежесть падает |
Микробатчи, буферы, оптимизация MV |
|
Большие мутации днём |
Спайки IO/CPU |
Ночные окна, overlay партиций |
|
JOIN больших на большие |
Таймаут/память |
Предагрегат/фильтрация, словари, ASOF/пред-джойн |
|
Холодное на S3 без кэша |
Пила по латентности |
Filesystem cache, «горячее окно» локально |
|
Несогласованные календари/валюты |
Отчёты «не бьются» |
Разные VIEW, паспорт метрики |
Чек-листы инженера (операционные)
Перед выпуском новой витрины/версии
- Проверен ORDER BY на реальном WHERE (query-лог).
- Тяжёлые метрики → состояния.
- Нет FINAL/SELECT * в VIEW.
- Дефолтные фильтры в BI (период/лимиты).
- Нагрузочное сравнение до/после на стенде (реальный объём, топ-запросы).
- Алерты на freshness/parts/merges настроены.
При инциденте «медленно»
- Снять топ из system.query_log (rb/rr/ms).
- Проверить parts/merges/replication_queue.
- Ограничить проблемный дашборд (дефолтный период).
- Выделить pre-aggregate / поправить WHERE.
- План действий и ETA — в канал.
Короткий «cookbook» (готовые фрагменты)
Фильтр в PREWHERE + узкие колонки:
SELECT day, shop_id, sum(net_sales) FROM mart_sales_wide PREWHERE day BETWEEN today()-28 AND today()-1 WHERE shop_id IN (101,205,309) GROUP BY day, shop_id;
Top-N в группах:
SELECT shop_id, sku_id, sum(net_sales) s FROM vw_retail_daily WHERE day = yesterday() GROUP BY shop_id, sku_id ORDER BY shop_id, s DESC LIMIT 10 BY shop_id;
ASOF JOIN (значение «на момент»):
SELECT s.tx_datetime, s.sku_id, s.qty, p.price FROM sales s ASOF JOIN price_snapshots p ON p.sku_id = s.sku_id AND p.valid_from <= s.tx_datetime;
Bitmap-пересечение аудиторий:
WITH w AS (SELECT bitmapOr(users_bm) bm FROM agg_active_daily_bitmap WHERE day >= today()-7), y AS (SELECT users_bm FROM agg_active_daily_bitmap WHERE day = yesterday()) SELECT bitmapCardinality(bitmapAnd((SELECT bm FROM w), (SELECT users_bm FROM y)));
Итоги
Оптимизация ClickHouse — это не «включить волшебный флаг», а системная практика:
- Правильная физика (партиции, ORDER BY, гранулы, части).
- Подготовленные метрики (состояния uniq/quantile/%/topK).
- Рациональные JOIN (словари, ASOF, пред-джойны).
- Управляемые вставки (микробатчи, без «дроби»).
- Наблюдаемость и дисциплина (query-лог, parts/merges, алерты).
- Локальная «горячая» память/диск, S3 только для «холода».
- Планы и профили (EXPLAIN/PIPELINE, ProfileEvents) перед “настройкой ручек”.
С таким подходом ваши витрины остаются быстрыми и предсказуемыми, даже когда объёмы и аудитория растут.
Arenadata QuickMarts (ADQM) — корпоративная платформа на базе ClickHouse для быстрого слоя витрин и near-real-time аналитики. Решает задачи «быстрых» дашбордов и API с низкой латентностью и высокой конкуррентностью, работает поверх вашего DWH/лейкхауса как serving-уровень. Даёт предсказуемую производительность на терабайтно-петабайтных объёмах за счёт колоночного хранения, компрессии и предагрегатов (Materialized Views, AggregatingMergeTree), подключается к Kafka/S3 и стандартным BI-инструментам по SQL/HTTP. Для корпоративных ИТ ADQM предлагает поддержку и SLA, отказоустойчивые кластеры (HA/DR), безопасность (RBAC, LDAP/OIDC, шифрование трафика и данных), мониторинг и резервное копирование. Платформа хорошо ложится на методологию курса: семантика vw_*, роллап-слои, NRT-ингест, SLO/наблюдаемость и «гвардейки» для BI/API. Итог — быстрый запуск витрин за недели, снижённые риски в проде и предсказуемая стоимость владения.
