Простое руководство по оптимизации запросов
Этот раздел направлен на иллюстрацию через распространенные сценарии того, как использовать различные техники повышения производительности и оптимизации, такие как анализатор, профилирование запросов или избежание Nullable Columns, с целью улучшения производительности ваших запросов ClickHouse.
Понимание производительности запросов
Лучший момент для размышления о оптимизации производительности – это когда вы настраиваете свою схему данных перед первой загрузкой данных в ClickHouse.
Но давайте будем честными; трудно предсказать, насколько ваши данные вырастут или какие типы запросов будут выполняться.
Если у вас есть существующий развертывание с несколькими запросами, которые вы хотите улучшить, первый шаг – понять, как эти запросы выполняются и почему некоторые выполняются за несколько миллисекунд, а другие требуют больше времени.
ClickHouse имеет богатый набор инструментов, которые помогают вам понять, как выполняется ваш запрос и какие ресурсы потребляются для его выполнения.
В этом разделе мы рассмотрим эти инструменты и то, как их использовать.
Общие соображения
Чтобы понять производительность запросов, давайте рассмотрим, что происходит в ClickHouse при выполнении запроса.
Следующая часть преднамеренно упрощена и использует некоторые сокращения; идея здесь не в том, чтобы утомить вас деталями, а в том, чтобы ознакомить с базовыми концепциями. Для получения дополнительной информации вы можете прочитать о анализаторе запросов.
С очень высокой точки зрения, когда ClickHouse выполняет запрос, происходит следующее:
- Парсинг и анализ запроса
Запрос анализируется и разбирается, и создается общий план выполнения запроса.
- Оптимизация запроса
План выполнения запроса оптимизируется, ненужные данные отсекаются, и из запроса строится конвейер исполнения.
- Исполнение конвейера запроса
Данные читаются и обрабатываются параллельно. Это тот этап, на котором ClickHouse фактически выполняет операции запросов, такие как фильтрация, агрегации и сортировка.
- Финальная обработка
Результаты сливаются, сортируются и форматируются в окончательный результат перед отправкой клиенту.
На самом деле, происходит множество оптимизаций, и мы обсудим их более подробно в этом руководстве, но пока главные концепции дают нам хорошее понимание того, что происходит за кулисами, когда ClickHouse выполняет запрос.
С этим высоким уровнем понимания давайте рассмотрим инструменты, которые предоставляет ClickHouse, и как мы можем их использовать для отслеживания метрик, влияющих на производительность запросов.
Набор данных
Мы будем использовать реальный пример, чтобы проиллюстрировать, как мы подходим к производительности запросов.
Давайте воспользуемся набором данных такси NYC, который содержит данные поездок на такси в NYC. Сначала мы начинаем с загрузки набора данных такси NYC без оптимизации.
Ниже представлен код для создания таблицы и вставки данных из S3-хранилища. Обратите внимание, что мы сознательно выводим схему из данных, что не оптимизировано.
Давайте взглянем на схему таблицы, автоматически выведенную из данных.
Обнаружение медленных запросов
Журналы запросов
По умолчанию ClickHouse собирает и регистрирует информацию о каждом выполненном запросе в журналах запросов. Эти данные хранятся в таблице system.query_log.
Для каждого выполненного запроса ClickHouse ведет статистику, такую как время выполнения запроса, количество прочитанных строк и использование ресурсов, таких как CPU, использование памяти или попадания в кэш файловой системы.
Поэтому журнал запросов – это хорошее место для начала при расследовании медленных запросов. Вы можете легко выявить запросы, которые требуют много времени для выполнения, и отобразить информацию об использовании ресурсов для каждого из них.
Давайте найдем пять самых долгих запросов в нашем наборе данных такси NYC.
Поле query_duration_ms указывает, сколько времени потребовалось для выполнения конкретного запроса. Смотрим на результаты из журналов запросов, мы видим, что первый запрос занимает 2967 мс, что можно улучшить.
Вы также можете захотеть выяснить, какие запросы нагружают систему, исследуя запрос, который потребляет больше всего памяти или CPU.
Давайте изолируем долго работающие запросы, которые мы нашли, и повторно запустим их несколько раз, чтобы понять время ответа.
На этом этапе важно отключить кэш файловой системы, установив параметр enable_filesystem_cache в 0, чтобы улучшить воспроизводимость.
Подводя итог в таблице для удобства чтения.
| Имя | Затраченное время | Обработанные строки | Пиковая память |
|---|---|---|---|
| Запрос 1 | 1.699 сек | 329.04 миллионов | 440.24 MiB |
| Запрос 2 | 1.419 сек | 329.04 миллионов | 546.75 MiB |
| Запрос 3 | 1.414 сек | 329.04 миллионов | 451.53 MiB |
Давайте лучше поймем, что выполняют запросы.
- Запрос 1 вычисляет распределение расстояния в поездках со средней скоростью выше 30 миль в час.
- Запрос 2 находит количество и среднюю стоимость поездок за неделю.
- Запрос 3 вычисляет среднее время каждой поездки в наборе данных.
Ни один из этих запросов не выполняет очень сложную обработку, кроме первого запроса, который вычисляет время поездки на лету каждый раз, когда выполняется запрос. Однако каждый из этих запросов занимает больше одной секунды для выполнения, что в мире ClickHouse является очень долгим временем. Мы также можем отметить, что использование памяти этих запросов составляет больше или менее 400 Мб для каждого запроса, что довольно много. Кроме того, каждый запрос, похоже, читает одно и то же количество строк (т.е. 329.04 миллиона). Давайте быстро подтвердим, сколько строк в этой таблице.
В таблице содержится 329.04 миллионов строк, следовательно, каждый запрос выполняет полное сканирование таблицы.
Оператор Explain
Теперь, когда у нас есть несколько долгих запросов, давайте поймем, как они выполняются. Для этого в ClickHouse поддерживается команда EXPLAIN statement. Это очень полезный инструмент, который предоставляет очень подробный обзор всех этапов выполнения запроса, не выполняя фактически запрос. Хотя это может быть подавляющим для неэксперта ClickHouse, это остается важным инструментом для получения представления о том, как выполняется ваш запрос.
Документация содержит подробное руководство о том, что такое оператор EXPLAIN и как использовать его для анализа выполнения вашего запроса. Вместо того чтобы повторять то, что содержится в этом руководстве, давайте сосредоточимся на нескольких командах, которые помогут нам найти узкие места в производительности выполнения запросов.
Explain indexes = 1
Давайте начнем с EXPLAIN indexes = 1, чтобы исследовать план запроса. План запроса – это дерево, показывающее, как будет выполнен запрос. Там вы можете увидеть, в каком порядке будут выполняться условия из запроса. План запроса, возвращаемый оператором EXPLAIN, можно прочитать снизу вверх.
Давайте попробуем использовать первый из наших долгих запросов.
Вывод простой. Запрос начинается с чтения данных из таблицы nyc_taxi.trips_small_inferred. Затем условие WHERE применяется для фильтрации строк на основе вычисленных значений. Отфильтрованные данные подготавливаются для агрегации, и вычисляются квантилы. Наконец, результат сортируется и выводится.
Здесь мы можем заметить, что никаких первичных ключей не используется, что имеет смысл, так как мы не определяли никакие при создании таблицы. В результате ClickHouse делает полное сканирование таблицы для запроса.
Explain Pipeline
EXPLAIN PIPELINE показывает конкретную стратегию выполнения запроса. Там вы можете увидеть, как ClickHouse фактически выполнил общий план запроса, который мы смотрели ранее.
Здесь мы можем заметить количество потоков, использованных для выполнения запроса: 59 потоков, что указывает на высокую параллельность. Это ускоряет запрос, который занял бы больше времени для выполнения на меньшей машине. Количество потоков, работающих параллельно, может объяснить высокое использование памяти, которое использует запрос.
В идеале, вы бы исследовали все свои медленные запросы таким образом, чтобы выявить ненужные сложные планы запросов и понять количество строк, прочитанных каждым запросом и потребляемые ресурсы.
Методология
Выявить проблемные запросы в рабочем развертывании может быть сложно, поскольку вероятно одновременно выполняется большое количество запросов на вашем развертывании ClickHouse.
Если вы знаете, какой пользователь, база данных или таблицы вызывают проблемы, вы можете использовать поля user, tables или databases из system.query_logs, чтобы сузить поиск.
Как только вы определите запросы, которые хотите оптимизировать, вы можете начать с работы над ними для оптимизации. Одной из распространенных ошибок, которые делают разработчики на этом этапе, является изменение нескольких вещей одновременно, проведение произвольных экспериментов и, как правило, получение смешанных результатов, но, что более важно, отсутствие хорошего понимания того, что сделало запрос более быстрым.
Оптимизация запросов требует структуры. Я не говорю о сложном бенчмаркинге, но наличие простого процесса для понимания того, как ваши изменения влияют на производительность запросов, может многое изменить.
Начните с выявления медленных запросов из журналов запросов, затем исследуйте потенциальные улучшения в изоляции. При тестировании запроса убедитесь, что вы отключили кэш файловой системы.
ClickHouse использует кэширование для ускорения производительности запросов на различных этапах. Это хорошо для производительности запросов, но во время устранения неполадок это может скрывать потенциальные узкие места ввода-вывода или плохую схему таблицы. По этой причине я рекомендую отключить кэш файловой системы во время тестирования. Убедитесь, что он включен в производственном развертывании.
Как только вы определили потенциальные оптимизации, рекомендуется реализовать их по одному, чтобы лучше отслеживать, как они влияют на производительность. Ниже приведена схема, описывающая общий подход.
Наконец, будьте осторожны с выбросами; довольно часто запрос может выполняться медленно, либо потому, что пользователь попытался выполнить дорогой по ресурсоемкости запрос, либо система испытывала нагрузку по другой причине. Вы можете группировать по полю normalized_query_hash, чтобы определить дорогие запросы, которые выполняются регулярно. Вероятно, это те запросы, которые стоит исследовать.
Базовая оптимизация
Теперь, когда у нас есть наша основа для тестирования, мы можем начать оптимизацию.
Лучшее место для начала – это посмотреть, как хранятся данные. Как и для любой базы данных, чем меньше данных мы читаем, тем быстрее будет выполнен запрос.
В зависимости от того, как вы загрузили свои данные, вы могли воспользоваться возможностями ClickHouse для вывода схемы таблицы на основе загруженных данных. Хотя это очень удобно для начала, если вы хотите оптимизировать производительность своего запроса, вам нужно будет проверить схему данных, чтобы лучше соответствовать вашему случаю использования.
Nullable
Как описано в документации по лучшим практикам, избегайте nullable-колонок где возможно. Использовать их часто очень соблазнительно, так как они делают механизм загрузки данных более гибким, но они негативно влияют на производительность, так как дополнительную колонку приходится обрабатывать каждый раз.
Выполнение SQL-запроса, который подсчитывает строки с значением NULL, может легко выявить колонки в ваших таблицах, которые действительно нуждаются в Nullable-значении.
У нас есть только два столбца с нулевыми значениями: mta_tax и payment_type. Остальные поля не должны использовать Nullable-колонку.
Низкая кардинальность
Легкая оптимизация для строк – это максимально эффективное использование типа данных LowCardinality. Как описано в документации по низкой кардинальности, ClickHouse применяет словарное кодирование к колонкам LowCardinality, что значительно повышает производительность запросов.
Легкое правило для определения колонок, являющихся хорошими кандидатами на LowCardinality, состоит в том, что любой столбец с менее чем 10 000 уникальных значений является идеальным кандидатом.
Вы можете использовать следующий SQL-запрос, чтобы найти колонки с низким количеством уникальных значений.
С низкой кардинальностью, эти четыре столбца, ratecode_id, pickup_location_id, dropoff_location_id и vendor_id, являются хорошими кандидатами для типа поля LowCardinality.
Оптимизация типа данных
ClickHouse поддерживает большое количество типов данных. Убедитесь, что вы выбираете возможно самый маленький тип данных, который соответствует вашему случаю использования, чтобы оптимизировать производительность и уменьшить пространство для хранения данных на диске.
Для чисел вы можете проверить минимальное/максимальное значение в вашем наборе данных, чтобы убедиться, что текущее значение точности соответствует реальности вашего набора данных.
Для дат вы должны выбрать точность, соответствующую вашему набору данных и наиболее подходящую для ответов на запросы, которые вы планируете выполнять.
Применение оптимизаций
Давайте создадим новую таблицу, чтобы использовать оптимизированную схему и заново загрузить данные.
Мы снова выполняем запросы с использованием новой таблицы, чтобы проверить улучшения.
| Имя | Запуск 1 - Затраченное время | Затраченное время | Обработанные строки | Пиковая память |
|---|---|---|---|---|
| Запрос 1 | 1.699 сек | 1.353 сек | 329.04 миллионов | 337.12 MiB |
| Запрос 2 | 1.419 сек | 1.171 сек | 329.04 миллионов | 531.09 MiB |
| Запрос 3 | 1.414 сек | 1.188 сек | 329.04 миллионов | 265.05 MiB |
Мы замечаем некоторые улучшения как в времени выполнения, так и в использовании памяти. Благодаря оптимизации в схеме данных мы уменьшаем общий объем данных, который представляет наши данные, что приводит к улучшению потребления памяти и снижению времени обработки.
Давайте проверим размер таблиц, чтобы увидеть разницу.
Новая таблица значительно меньше, чем предыдущая. Мы видим снижение примерно на 34% в объеме дискового пространства для таблицы (7.38 GiB против 4.89 GiB).
Важность первичных ключей
Первичные ключи в ClickHouse работают иначе, чем в большинстве традиционных систем баз данных. В этих системах первичные ключи обеспечивают уникальность и целостность данных. Любая попытка вставить дублирующие значения первичного ключа отклоняется, и обычно создается индекс на основе B-дерева или хеш-таблицы для быстрого поиска.
В ClickHouse цель первичного ключа отличается; он не обеспечивает уникальность и не помогает с целостностью данных. Вместо этого он разработан для оптимизации производительности запросов. Первичный ключ определяет порядок, в котором данные хранятся на диске и реализуется в качестве разреженного индекса, который хранит указатели на первую строку каждой гранулы.
Гранулы в ClickHouse – это самые маленькие единицы данных, читаемые во время выполнения запроса. Они содержат до фиксированного числа строк, определяемого index_granularity, со значением по умолчанию 8192 строки. Гранулы хранятся непрерывно и сортируются по первичному ключу.
Выбор хорошего набора первичных ключей важен для производительности, и на самом деле часто бывает, что данные хранятся в разных таблицах и используются разные наборы первичных ключей для ускорения конкретного набора запросов.
Другие варианты, поддерживаемые ClickHouse, такие как پروекция или материализованное представление, позволяют использовать другой набор первичных ключей на одних и тех же данных. Вторая часть этой серии статей будет более подробно освещать этот вопрос.
Затем мы повторно запускаем наши запросы. Мы компилируем результаты из трех экспериментов, чтобы увидеть улучшения в затраченном времени, обработанных строках и использовании памяти.
| Запрос 1 | |||
|---|---|---|---|
| Запуск 1 | Запуск 2 | Запуск 3 | |
| Затраченное время | 1.699 сек | 1.353 сек | 0.765 сек |
| Обработанные строки | 329.04 миллионов | 329.04 миллионов | 329.04 миллионов |
| Пиковая память | 440.24 MiB | 337.12 MiB | 444.19 MiB |
| Запрос 2 | |||
|---|---|---|---|
| Запуск 1 | Запуск 2 | Запуск 3 | |
| Затраченное время | 1.419 сек | 1.171 сек | 0.248 сек |
| Обработанные строки | 329.04 миллионов | 329.04 миллионов | 41.46 миллионов |
| Пиковая память | 546.75 MiB | 531.09 MiB | 173.50 MiB |
| Запрос 3 | |||
|---|---|---|---|
| Запуск 1 | Запуск 2 | Запуск 3 | |
| Затраченное время | 1.414 сек | 1.188 сек | 0.431 сек |
| Обработанные строки | 329.04 миллионов | 329.04 миллионов | 276.99 миллионов |
| Пиковая память | 451.53 MiB | 265.05 MiB | 197.38 MiB |
Мы видим значительное улучшение во всех аспектах по времени выполнения и использованию памяти.
Запрос 2 наиболее выигрывает от первичного ключа. Давайте посмотрим, как план запроса изменился по сравнению с предыдущим.
Благодаря первичному ключу, был выбран только подмножество гранул таблицы. Это само по себе значительно улучшает производительность запроса, поскольку ClickHouse обрабатывает значительно меньше данных.
Следующие шаги
Надеюсь, это руководство даёт хорошее понимание того, как исследовать медленные запросы с помощью ClickHouse и как сделать их быстрее. Чтобы узнать больше по этой теме, вы можете прочитать о анализаторе запросов и профилировании, чтобы лучше понять, как именно ClickHouse выполняет ваш запрос.
По мере того как вы становитесь более знакомыми со специфическими особенностями ClickHouse, я бы рекомендовал прочитать о ключах партиционирования и индексах пропуска данных, чтобы узнать о более сложных техниках, которые вы можете использовать для ускорения ваших запросов.
