🪴 BLOG IT

проблемы с медленными запросами в ClickHouse

5 min read

Когда запросы в ClickHouse выполняются медленно, важно определить узкие места и применить оптимизации. Вот пошаговое руководство по устранению медленных запросов.

1. Выявление медленных запросов с использованием системных таблиц

ClickHouse записывает детали выполнения запросов в таблицу system.query_log, что помогает отслеживать медленные запросы.

✅ Шаг 1: Найдите медленные запросы

SELECT query, query_duration_ms, read_rows, read_bytes
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

📌 Ключевые столбцы для проверки:

  • query_duration_ms: Время выполнения запроса.
  • read_rows: Количество строк, которые были отсканированы.
  • read_bytes: Количество байт, считанных с диска.

🔹 Цель: Определить запросы, которые сканируют слишком много строк или читают слишком много данных.

2. Анализ плана выполнения запроса

ClickHouse предоставляет команду EXPLAIN для просмотра, как выполняется запрос.

✅ Шаг 2: Используйте EXPLAIN для проверки плана выполнения запроса

EXPLAIN PLAN FOR
SELECT SUM(revenue) FROM sales WHERE event_time >= '2024-01-01';

📌 Что искать в выводе:

  • Полное сканирование таблицы? (ReadFromMergeTree без индекса)
  • Неэффективная фильтрация? (отсутствует правильный ORDER BY)
  • Сканируется слишком много строк? (Проверьте read_rows в журнале запросов)

🔹 Цель: Снизить количество ненужных сканирований строк путем оптимизации индексов и фильтрации.

3. Оптимизация проектирования таблицы (ORDER BY, PARTITION BY)

Если EXPLAIN показывает полное сканирование таблицы, схема таблицы может быть неоптимизированной.

✅ Шаг 3: Убедитесь в эффективности ORDER BY

ClickHouse использует разреженные индексы вместо традиционных B-деревьев. ORDER BY должен соответствовать шаблону запросов.

❌ Плохой пример (Неэффективно для запросов по времени)

CREATE TABLE sales (
    id UInt32,
    event_time DateTime,
    revenue Float64
) ENGINE = MergeTree()
ORDER BY id; -- ❌ Неэффективно для запросов по event_time

✅ Хороший пример (Оптимизировано для запросов по дате)

CREATE TABLE sales (
    id UInt32,
    event_time DateTime,
    revenue Float64
) ENGINE = MergeTree()
ORDER BY (event_time, id); -- ✅ Ускоряет фильтрацию по event_time

📌 Исправление:

  • Выбирайте ORDER BY в зависимости от того, какие поля часто используются в WHERE.
  • Если запросы фильтруют по дате, используйте ORDER BY (event_time, id).
  • Если запросы фильтруют по пользователю, используйте ORDER BY (user_id, event_time).

🔹 Влияние: Ускоряет запросы по дате в 10-100 раз.

4. Использование партиционирования для уменьшения объема сканируемых данных

Если запросы сканируют слишком много данных, партиционирование помогает пропустить ненужные разделы.

✅ Шаг 4: Используйте PARTITION BY, чтобы избежать полного сканирования таблицы

CREATE TABLE sales (
    id UInt32,
    event_time DateTime,
    revenue Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time) -- ✅ Партиционирование по месяцам
ORDER BY (event_time, id);

📌 Исправление:

  • Если запросы фильтруют по event_time, используйте партиционирование по месяцам (toYYYYMM(event_time)).
  • Если фильтрация по user_id, используйте хешированное партиционирование.

🔹 Влияние: Уменьшает время запроса в 100 раз.

5. Оптимизация JOIN-ов

ClickHouse загружает всю правую таблицу в память при выполнении JOIN-а, что может замедлить запросы.

✅ Шаг 5: Используйте словари вместо JOIN-ов

Вместо:

SELECT logs.*, users.name
FROM logs
JOIN users ON logs.user_id = users.id;

Используйте словарь:

CREATE DICTIONARY user_dict
(
    id UInt32,
    name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'users'))
LIFETIME(600);

Затем выполните запрос:

SELECT logs.*, dictGet('user_dict', 'name', logs.user_id) FROM logs;

🔹 Влияние: выполняются в 10-100 раз быстрее.

6. Проверка фрагментации MergeTree (OPTIMIZE TABLE)

ClickHouse хранит данные в частях. Если фрагментов слишком много, чтение становится медленным.

✅ Шаг 6: Оптимизируйте слияние таблиц

OPTIMIZE TABLE sales FINAL;

📌 Исправление:

  • Регулярно выполняйте OPTIMIZE TABLE, чтобы уменьшить количество фрагментов.
  • Используйте большие пакеты вставок вместо множества мелких вставок.

🔹 Влияние: Ускоряет запросы за счет уменьшения количества фрагментов.

7. Снижение нагрузки на запросы (GROUP BY, DISTINCT, Агрегации)

Дорогие операции GROUP BY или DISTINCT потребляют много памяти.

✅ Шаг 7: Используйте AggregatingMergeTree для предварительных вычислений

Вместо:

SELECT event_time, count(*) FROM sales GROUP BY event_time;

Создайте материализованное представление для предварительно вычисленных результатов:

CREATE MATERIALIZED VIEW sales_mv
ENGINE = AggregatingMergeTree()
ORDER BY event_time
AS SELECT event_time, count(*) AS event_count FROM sales GROUP BY event_time;

Затем запрос:

SELECT * FROM sales_mv;

🔹 Влияние: Ускоряет запросы с GROUP BY в 10-100 раз.

8. Оптимизация выполнения запросов с использованием max_threads

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

✅ Шаг 8: Отрегулируйте max_threads для запросов с высокой конкурентностью

SET max_threads = 16;
SET max_memory_usage = 10GB;

📌 Исправление: Уменьшите max_threads на перегруженных кластерах, чтобы избежать перегрузки процессора.

🔹 Влияние: Предотвращает ухудшение производительности при высокой конкурентности.

9. Мониторинг производительности запросов (system.metrics)

ClickHouse предоставляет данные о производительности в реальном времени.

✅ Шаг 9: Проверьте метрики производительности в реальном времени

SELECT metric, value FROM system.metrics WHERE metric IN ('Query', 'ReadRows', 'MemoryUsage');

📌 Исправление:

  • Если ReadRows слишком высок, оптимизируйте индексы.
  • Если MemoryUsage слишком высок, уменьшите конкурентность запросов.

🔹 Влияние: Выявляет узкие места ресурсов в реальном времени.

10. Проверка использования диска (system.parts)

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

✅ Шаг 10: Проанализируйте использование диска и выполняющиеся запросы

SELECT table, sum(bytes_on_disk) / 1024 / 1024 AS size_MB FROM system.parts GROUP BY table ORDER BY size_MB DESC;

📌 Исправление:

  • Оптимизируйте сжатие для больших таблиц (CODEC(ZSTD)).
  • Используйте быстрые SSD-диски для ClickHouse.

🔹 Влияние: Снижает узкие места ввода-вывода для больших наборов данных.

🚀 Итоговое резюме: Устранение узких мест производительности запросов

ПроблемаИсправлениеВлияние
Полное сканирование таблицыОптимизировать ORDER BY и PARTITION BY🔥 Запросы в 10-100 раз быстрее
Медленные JOIN-ыИспользовать словари🔥 Поиск в 100 раз быстрее
Слишком много мелких частейВыполнить OPTIMIZE TABLE FINAL;🔥 Меньше накладных расходов на слияние
Высокое использование памяти (GROUP BY)Использовать материализованные представления🔥 Агрегации в 10-100 раз быстрее
Перегрузка процессораУстановить max_threads = 16;🔥 Более стабильная производительность
Медленная работа с дискомИспользовать SSD-диски, сжатие🔥 Снижение задержки чтения

🚀 Заключение:

  • Оптимизация схемы данных — ключ к производительности ClickHouse.
  • Избегайте сканирования лишних строк с помощью ORDER BY, PARTITION BY и эффективных запросов.
  • Мониторьте метрики в реальном времени, чтобы выявлять узкие места.

Graph View

Backlinks