Простое руководство по оптимизации запросов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Набор данных

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

Используем набор данных NYC Taxi, который содержит данные о поездках на такси в NYC. Сначала мы начинаем с загрузки набора данных NYC Taxi без оптимизации.

Ниже представлена команда для создания таблицы и вставки данных из 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. Это очень полезный инструмент, который предоставляет очень детальный обзор всех стадий выполнения запроса без фактического выполнения запроса. Хотя это может быть подавляющим для неэксперта 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

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

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

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