Простое руководство по оптимизации запросов
Этот раздел иллюстрирует, как использовать различные техники повышения производительности и оптимизации, такие как анализатор, профилирование запросов или избежание Nullable Columns, с целью улучшения производительности запросов ClickHouse.
Понимание производительности запросов
Лучший момент, чтобы задуматься об оптимизации производительности, — это когда вы настраиваете свою схему данных перед первой загрузкой данных в ClickHouse.
Но давайте будем честными; трудно предсказать, насколько сильно вырастут ваши данные или какие типы запросов будут выполняться.
Если у вас уже есть развертывание с несколькими запросами, которые вы хотите улучшить, первым шагом будет понимание того, как эти запросы выполняются и почему некоторые исполняются за несколько миллисекунд, в то время как другие занимают больше времени.
ClickHouse имеет богатый набор инструментов, которые помогут вам понять, как выполняется ваш запрос и какие ресурсы потребляются для его выполнения.
В этом разделе мы рассмотрим эти инструменты и то, как их использовать.
Общие соображения
Чтобы понять производительность запроса, давайте посмотрим, что происходит в ClickHouse, когда запрос выполняется.
Следующая часть намеренно упрощена и не содержит деталей; здесь идея не утопить вас в подробностях, а дать вам общее представление о базовых понятиях. Для получения дополнительной информации вы можете прочитать о анализаторе запросов.
С очень высокого уровня, когда ClickHouse выполняет запрос, происходит следующее:
- Парсинг и анализ запроса
Запрос парсится и анализируется, создается общий план выполнения запроса.
- Оптимизация запроса
План выполнения запроса оптимизируется, ненужные данные отбрасываются, и строится конвейер запросов из плана запроса.
- Выполнение конвейера запроса
Данные читаются и обрабатываются параллельно. Это тот этап, на котором ClickHouse фактически выполняет операции запроса, такие как фильтрация, агрегация и сортировка.
- Финальная обработка
Результаты объединяются, сортируются и форматируются в окончательный результат, прежде чем быть отправленными клиенту.
На самом деле происходит много оптимизаций, и мы обсудим их немного подробнее в этом руководстве, но пока эти основные концепции дадут нам хорошее понимание того, что происходит за кулисами, когда ClickHouse выполняет запрос.
С этим высоким уровнем понимания давайте рассмотрим инструменты, которые предоставляет ClickHouse, и как мы можем использовать их для отслеживания показателей, влияющих на производительность запросов.
Набор данных
Мы будем использовать реальный пример, чтобы проиллюстрировать, как мы подходим к производительности запросов.
Давайте использовать набор данных такси в Нью-Йорке, который содержит данные о поездках на такси в NYC. Сначала мы начинаем с загрузки набора данных о такси Нью-Йорка без оптимизации.
Ниже приведена команда для создания таблицы и вставки данных из S3-ведра. Обратите внимание, что мы намеренно выводим схему из данных, что не оптимизировано.
Давайте взглянем на схему таблицы, автоматически выведенную из данных.
Поиск медленных запросов
Журналы запросов
По умолчанию ClickHouse собирает и регистрирует информацию о каждом выполненном запросе в журналах запросов. Эти данные хранятся в таблице system.query_log.
Для каждого выполненного запроса ClickHouse регистрирует статистику, такую как время выполнения запроса, количество прочитанных строк и использование ресурсов, таких как CPU, использование памяти или попадания в кэш файловой системы.
Поэтому журнал запросов — хорошее место для начала при расследовании медленных запросов. Вы можете легко обнаружить запросы, которые требуют много времени для выполнения, и отобразить информацию об использовании ресурсов для каждого из них.
Давайте найдем топ пять длительных запросов в нашем наборе данных о такси в Нью-Йорке.
Поле query_duration_ms указывает, сколько времени заняло выполнение конкретного запроса. Рассматривая результаты журналов запросов, мы видим, что первый запрос занимает 2967 мс для выполнения, и его можно улучшить.
Вы также можете захотеть узнать, какие запросы нагружают систему, исследуя запросы, которые используют наибольшее количество памяти или CPU.
Давайте изолируем длительные запросы, которые мы нашли, и повторно запустим их несколько раз, чтобы понять время ответа.
На этом этапе важно отключить кэш файловой системы, установив настройку enable_filesystem_cache в 0 для улучшения воспроизводимости.
Подведем итог в таблице для удобного чтения.
| Название | Время выполнения | Обработанные строки | Пиковая память |
|---|---|---|---|
| Запрос 1 | 1.699 сек | 329.04 миллиона | 440.24 MiБ |
| Запрос 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. Это очень полезный инструмент, который предоставляет очень детальный обзор всех этапов выполнения запроса без фактического его выполнения. Хотя для эксперта 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 МиБ |
| Запрос 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, такие как проекции или материализованные представления, позволяют использовать другой набор первичных ключей на тех же данных. Вторая часть этой серии блогов подробнее разберет это.
Выбор первичных ключей
Выбор правильного набора первичных ключей - это сложная задача, и может потребоваться компромиссы и эксперименты, чтобы найти наилучшие комбинации.
На данный момент мы будем следовать этим простым практикам:
- Используйте поля, которые чаще всего используются для фильтрации в запросах.
- Сначала выбирайте столбцы с низкой кардинальностью.
- Рассмотрите возможность использования временного компонента в вашем первичном ключе, так как фильтрация по времени в наборах данных с отметками времени довольно распространена.
В нашем случае мы будем экспериментировать со следующими первичными ключами: 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 Мб | 337.12 Мб | 444.19 Мб |
| Запрос 2 | |||
|---|---|---|---|
| Запуск 1 | Запуск 2 | Запуск 3 | |
| Время выполнения | 1.419 сек | 1.171 сек | 0.248 сек |
| Обработанные строки | 329.04 миллионов | 329.04 миллионов | 41.46 миллионов |
| Пиковая память | 546.75 Мб | 531.09 Мб | 173.50 Мб |
| Запрос 3 | |||
|---|---|---|---|
| Запуск 1 | Запуск 2 | Запуск 3 | |
| Время выполнения | 1.414 сек | 1.188 сек | 0.431 сек |
| Обработанные строки | 329.04 миллионов | 329.04 миллионов | 276.99 миллионов |
| Пиковая память | 451.53 Мб | 265.05 Мб | 197.38 Мб |
Мы можем видеть значительное улучшение по всем параметрам в времени выполнения и использовании памяти.
Запрос 2 получает наибольшую выгоду от первичного ключа. Давайте посмотрим, как созданный план запроса отличается от предыдущего.
Благодаря первичному ключу был выбран только подмножество гранул таблицы. Это само по себе значительно улучшает производительность запроса, так как ClickHouse обрабатывает значительно меньше данных.
Следующие шаги
Надеюсь, это руководство поможет вам лучше понять, как исследовать медленные запросы с ClickHouse и как сделать их быстрее. Чтобы подробнее изучить эту тему, вы можете прочитать больше о анализаторе запросов и профилировании, чтобы лучше понять, как именно ClickHouse выполняет ваш запрос.
По мере того как вы будете лучше знакомиться с особенностями ClickHouse, я рекомендую вам ознакомиться с ключами партиционирования и индексами пропуска данных, чтобы узнать о более сложных техниках, которые вы можете использовать для ускорения ваших запросов.