Простой гид по оптимизации запросов

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

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

Лучший момент для размышлений об оптимизации производительности – это когда вы настраиваете свою схему данных перед первой загрузкой данных в ClickHouse.

Но будем честными; трудно предсказать, насколько вырастут ваши данные или какие типы запросов будут выполняться.

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

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

В этом разделе мы рассмотрим эти инструменты и как их использовать.

Общие соображения

Чтобы понять производительность запросов, давайте посмотрим, что происходит в ClickHouse, когда выполняется запрос.

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

С очень общей точки зрения, когда ClickHouse выполняет запрос, происходит следующее:

• Парсинг и анализ запроса

Запрос разбирается и анализируется, после чего создается общий план выполнения запроса.

• Оптимизация запроса

План выполнения запроса оптимизируется, ненужные данные отсекаются, и строится конвейер запросов из плана запроса.

• Выполнение конвейера запроса

Данные считываются и обрабатываются параллельно. Это этап, на котором ClickHouse фактически выполняет операции запроса, такие как фильтрация, агрегации и сортировки.

• Финальная обработка

Результаты объединяются, сортируются и форматируются в финальный результат перед отправкой клиенту.

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

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

Набор данных

Мы используем реальный пример, чтобы проиллюстрировать, как мы подходим к производительности запросов.

Давайте используем набор данных о такси в Нью-Йорке, который содержит данные о поездках на такси в Нью-Йорке. Сначала мы начнем с загрузки набора данных о такси в Нью-Йорке без оптимизации.

Ниже приведена команда для создания таблицы и вставки данных из ведра S3. Обратите внимание, что мы намеренно выводим схему из данных, что не оптимизировано.

Давайте взглянем на схему таблицы, автоматически выведенную из данных.

Найдите медленные запросы

Журналы запросов

По умолчанию ClickHouse собирает и записывает информацию о каждом выполненном запросе в журналы запросов. Эти данные хранятся в таблице system.query_log.

Для каждого выполненного запроса ClickHouse записывает статистику, такую как время выполнения запроса, количество прочитанных строк и использование ресурсов, таких как использование ЦП, памяти или попадания в кэш файловой системы.

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

Давайте найдем пять самых долгих запросов в нашем наборе данных о такси в Нью-Йорке.

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

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

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

На этом этапе важно отключить кэш файловой системы, установив параметр enable_filesystem_cache в 0 для повышения воспроизводимости.

Сводим результаты в таблицу для удобства чтения.

Название	Затраченное время	Обработано строк	Пиковая память
Запрос 1	1.699 сек	329.04 миллионов	440.24 Мб
Запрос 2	1.419 сек	329.04 миллионов	546.75 Мб
Запрос 3	1.414 сек	329.04 миллионов	451.53 Мб

Давайте лучше поймем, что достигается с помощью запросов.

• Запрос 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.

У нас есть только две колонки со значениями NULL: 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 Мб
Запрос 2	1.419 сек	1.171 сек	329.04 миллионов	531.09 Мб
Запрос 3	1.414 сек	1.188 сек	329.04 миллионов	265.05 Мб

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

Давайте проверим размер таблиц, чтобы увидеть разницу.

Новая таблица значительно меньше, чем предыдущая. Мы видим уменьшение примерно на 34% в объеме дискового пространства для таблицы (7.38 GiB против 4.89 GiB).

Важность первичных ключей

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

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

Гранулы в ClickHouse являются наименьшими единицами данных, читаемыми во время выполнения запроса. Они содержат до фиксированного количества строк, определяемого index_granularity, со значением по умолчанию 8192 строки. Гранулы хранятся последовательно и отсортированы по первичному ключу.

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

Другие опции, поддерживаемые ClickHouse, такие как Projection или Materialized View, позволяют вам использовать другой набор первичных ключей на одних и тех же данных. Вторая часть этой серии блогов будет более подробно освещать это.

Выбор первичных ключей

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

На данный момент мы будем придерживаться следующих простых практик:

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

В нашем случае мы будем экспериментировать с следующими первичными ключами: passenger_count, pickup_datetime и dropoff_datetime.

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

Создайте новую таблицу с первичными ключами и повторно загрузите данные.

Затем мы повторно выполняем наши запросы. Мы компилируем результаты трех экспериментов, чтобы увидеть улучшения по времени выполнения, количеству обработанных строк и потреблению памяти.

Запрос 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, я бы рекомендовал прочитать о ключах партиционирования и data skipping indexes, чтобы ознакомиться с более продвинутыми техниками, которые вы можете использовать для ускорения ваших запросов.