Выявление узких мест запросов

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

Перед началом работы

Начните с повторяющегося шаблона медленных запросов, который хотите исследовать. Если вы ещё не выявили такой шаблон, процесс описан в руководстве Диагностика медленных запросов. Чтобы выполнить примеры из этого руководства без изменений, создайте и загрузите таблицу nyc_taxi.trips_small_inferred, если ещё не сделали этого:

Настройка демонстрационного набора данных

Размер исходного файла Parquet составляет примерно 5,8 ГБ. Его загрузка может занять несколько минут в зависимости от скорости сети и доступных ресурсов.

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.trips_small_inferred
ORDER BY () EMPTY
AS SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

В демонстрационной таблице используется ORDER BY (), поэтому фильтр по дате не может задействовать ключ сортировки для исключения данных при чтении. Используйте этот пример для отработки метода сравнения, а не как ориентир по производительности.

Принцип работы

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

Выполните исходный запрос, чтобы получить базовые значения.
Сохраните GROUP BY, замените агрегатные вычисления в запросе на count и удалите последующие операции, например сортировку и форматирование вывода.
Удалите группировку и выполните count без группировки, чтобы приблизительно оценить объём работы, связанный со сканированием, фильтрацией и любыми JOIN.

Эти стадии непосредственно применимы к обычным агрегатным запросам с группировкой. Для более сложных запросов применяйте тот же принцип к одному блоку SELECT за раз: сохраняйте эквивалентные источники данных и фильтры, удаляйте по одной операции и проверяйте план выполнения после каждого изменения.

Эти различия — диагностические оценки, а не точные измерения стадий выполнения ClickHouse. Изменение запроса может изменить его план выполнения, считываемые столбцы и данные, передаваемые между стадиями. Используйте результаты для формирования гипотезы. Затем проверьте её с помощью журнала запросов и EXPLAIN.

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

Чтобы результаты измерений можно было сравнивать, соблюдайте следующие правила:

Не изменяйте секции FROM, JOIN, PREWHERE и WHERE, чтобы во всех сравнениях использовались одни и те же данные и временной диапазон.
Запускайте каждую версию запроса несколько раз при схожей нагрузке на систему.
Обеспечьте одинаковые условия кэширования. Либо выполняйте каждый вариант запроса с предварительным прогревом перед фиксацией измерений, либо отключите перечисленные ниже кэши. Не сравнивайте запуски с кэшем и без него.
Фиксируйте репрезентативную длительность, например медиану результатов повторных запусков после прогрева, а не ориентируйтесь на самый быстрый или самый медленный результат.
Изменяйте по одной переменной за раз, чтобы можно было связать различие в производительности с конкретным изменением.

Для диагностического сравнения без кэша отключите файловый кэш ClickHouse для удалённых данных, кэш запросов и кэш условий запроса. Также отключите неявные проекции, чтобы count в запуске C не использовал оптимизированный план выполнения, обходящий сканирование, которое вы намерены сравнить.

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;
SET optimize_use_implicit_projections = 0;

Эти операторы SET действуют только в текущем сеансе. Выполняйте все сравнительные запросы в этом сеансе или применяйте одинаковые настройки при каждом запуске. Настройка файлового кэша не отключает кэш страниц операционной системы и не отключает все кэши ClickHouse. По завершении закройте выделенный сеанс или верните каждой настройке прежнее значение.

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

Назначьте каждому запуску уникальный ID запроса или запишите ID, созданный интерфейсом выполнения запросов. Например, обозначайте повторные запуски как bottleneck-a-1, bottleneck-a-2 и bottleneck-a-3. При использовании clickhouse-client передавайте --query_id your-query-id при выполнении запроса.
Выполните каждый сравнительный запрос несколько раз в одинаковых условиях. Отделяйте прогревочные запуски от измеряемых.
Сбросьте журнал запросов перед поиском недавно завершённых запросов:
SYSTEM FLUSH LOGS;
Если вы не можете выполнить SYSTEM FLUSH LOGS, дождитесь автоматического сброса журнала запросов, затем повторите поиск. Если запись так и не появилась, убедитесь, что журналирование запросов включено, у вас есть доступ на чтение system.query_log и вы обращаетесь к узлу, на котором выполнялся запрос.
Найдите завершённую запись для каждого ID запроса. system.query_log записывает события QueryStart и QueryFinish для завершённого запроса. Отфильтруйте записи по QueryFinish: оно содержит окончательную длительность, число прочитанных строк и байт, а также пиковое потребление памяти:
SELECT query_id, query_duration_ms, read_rows, read_bytes, memory_usage FROM system.query_log WHERE type = 'QueryFinish' AND query_id = 'your-query-id' ORDER BY event_time_microseconds DESC LIMIT 1;
Для каждой версии запроса используйте медианную длительность измеряемых запусков. Запишите read_rows, read_bytes и пиковое потребление памяти для запуска, наиболее близкого к этой медиане, чтобы измерения были привязаны к фактическому запуску.

Для распределённых запросов значение memory_usage в записи QueryFinish инициирующего запроса не отражает пиковое потребление памяти по всему кластеру. Используйте initial_query_id для проверки дочерних записей QueryFinish на участвующих узлах.

Используйте таблицу наподобие приведённой ниже, чтобы упорядочить репрезентативные измерения. Дополнительные сведения о полях и конфигурации см. в system.query_log.

Таблица
CSV

Запуск	Версия запроса	Репрезентативная длительность	`read_rows`	`read_bytes`	Пиковое потребление памяти
A	Исходный запрос
B	Сгруппированный `count`
C	Несгруппированный `count`

query-comparison.csv

Run,Query version,Representative duration,read_rows,read_bytes,Peak memory
A,Original query,,,,
B,Grouped count,,,,
C,Ungrouped count,,,,

Последовательно выполняйте всё более простые запросы

Для демонстрации всех трёх сравнений в примере используется сгруппированная рабочая нагрузка по диапазону дат. Этот метод можно применить и к другому запросу, не воспроизводя приведённый пример. Если запрос не содержит GROUP BY, пропустите запуск B, как описано ниже.

Запуск A: измерение исходного запроса

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

SELECT
    payment_type,
    count() AS trip_count,
    formatReadableQuantity(sum(trip_distance)) AS total_distance,
    avg(total_amount) AS total_amount_avg,
    avg(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type
ORDER BY trip_count DESC;

Запишите полученные показатели как запуск A.

Запуск B: сохранение группировки с count

Сохраните в запросе FROM, JOIN, PREWHERE, WHERE и ключи группировки. Замените агрегатные выражения на сгруппированный count. Удалите операции после агрегации, включая исходные выражения сортировки и вывода.

SELECT
    payment_type,
    count() AS trip_count
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type;

Запуск B по-прежнему сканирует и фильтрует данные, выполняет все JOIN и формирует группы. Сравните его длительность с запуском A, чтобы оценить вклад исходных агрегатных выражений и операций после агрегации. Также сравните read_bytes, поскольку удаление агрегатных выражений может исключить некоторые столбцы из чтения.Если исходный запрос не содержит GROUP BY, изолировать этап группировки невозможно. Пропустите запуск B и сравните исходный запрос непосредственно с запуском C.

Запуск C: удаление группировки

Удалите GROUP BY и верните один count. Оставьте секции FROM, JOIN, PREWHERE и WHERE без изменений, чтобы оставшаяся работа была сопоставимой.

SELECT count()
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01';

Запуск C даёт базовый уровень для операций, сохраняемых в его плане выполнения, а не изолированную оценку сканирования или фильтрации. Сравните его с запуском B, чтобы оценить вклад группировки. Также сравните read_bytes, поскольку удаление ключа группировки может сократить число читаемых столбцов. Возвращаемый count показывает, сколько строк поступает на агрегацию после применения сохранённых фильтров и JOIN.Прежде чем интерпретировать результаты запуска C, убедитесь, что его план выполнения читает нужный источник данных и применяет сохранённые фильтры. Проекция или подсчёт на основе метаданных могут изменить выполняемую работу. Чтобы получить базовый уровень на основе сканирования, отключите оптимизацию, указанную в плане, для всех трёх запусков: используйте optimize_use_implicit_projections = 0 для неявной проекции, optimize_use_projections = 0 для явной проекции или optimize_trivial_count_query = 0 для подсчёта без фильтра на основе метаданных таблицы.Если запуск C по-прежнему выполняется медленно, исследуйте сохраняемые в нём операции, начиная со сканирования и фильтрации. Используйте журнал запросов и EXPLAIN, чтобы подтвердить предполагаемое узкое место, прежде чем изменять запрос.

Интерпретация различий

Сравнивайте репрезентативные длительности повторных запусков, а не вычитайте два отдельных значения времени. Большие устойчивые различия показывают, что следует исследовать далее:

Наблюдение	Возможные узкие места	Дальнейшее исследование
Запуск A значительно медленнее запуска B	Агрегатные выражения, сортировка, другие операции после агрегации или чтение дополнительных столбцов	Проверьте ресурсоёмкие агрегатные функции, выражения, `ORDER BY`, `read_bytes` и пиковое потребление памяти
Запуск B значительно медленнее запуска C	Группировка, количество уникальных групп или чтение ключей группировки	Проверьте ключи группировки, количество групп, `read_bytes` и пиковое потребление памяти
Запуск C остаётся медленным	Сканирование, фильтрация, JOIN или другая операция, сохраняющаяся в запуске C	Проверьте количество прочитанных строк и байтов, использование первичного ключа, индексы пропуска данных и план выполнения; затем подтвердите предполагаемое узкое место
Длительности всех трёх запусков схожи	Источник задержки может быть общим для всех трёх версий либо упрощение могло изменить план выполнения	Сравните `read_rows`, `read_bytes` и пиковое потребление памяти между запусками. Если эти показатели также схожи, исследуйте операции, сохраняющиеся в запуске C. В противном случае сравните планы выполнения, чтобы выявить различия

Сравните число прочитанных строк с результатом count

Сравните read_rows для запуска C со значением, возвращаемым его count. Например, если read_rows равно 100 миллионам, а count возвращает 1 миллион, ClickHouse просканировал примерно 100 исходных строк на каждую подсчитанную строку. Это означает, что фильтр отсеял большую часть строк, прочитанных из таблицы, но не позволяет определить причину. Это соотношение предназначено для простого сканирования одной таблицы. Для запросов с несколькими источниками данных или проекциями интерпретируйте read_rows с учетом плана выполнения. В ClickHouse 25.9 и более поздних версиях перед проверкой использования индексов отключите кэш условий запроса и динамическое применение индексов пропуска данных:

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

Затем с помощью EXPLAIN indexes = 1 посмотрите, какие индексы использовал ClickHouse и сколько частей и гранул отсёк каждый из них. Если ClickHouse выбрал больше гранул, чем ожидалось, проверьте, соответствуют ли фильтры ключу сортировки таблицы и позволяют ли отсечение партиций или индекс пропуска данных исключить больше гранул. Если в плане нет раздела Indexes, EXPLAIN не сообщил об отсечении по индексам для этого запроса. В отличие от этого, при аналитическом запросе ко всей таблице ожидается чтение большей её части.

Проверьте предполагаемое узкое место

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

Если узкое место связано со сканированием или фильтрацией, используйте EXPLAIN indexes = 1 с описанными выше настройками, чтобы увидеть, какие индексы использует ClickHouse и сколько частей и гранул исключает каждый индекс. Проверьте, использует ли план неявную проекцию вместо ожидаемого сканирования.
Если узкое место связано с группировкой или агрегацией, изучите соответствующие события профиля запроса и пиковое потребление памяти.
Если запуск C по-прежнему выполняется медленно и содержит JOIN, сравните его с диагностическим запросом, в котором JOIN удаляются по одному. Значительное сокращение длительности указывает на то, что удалённый JOIN создаёт существенную нагрузку. Поскольку удаление JOIN меняет смысл запроса, используйте это сравнение только для изоляции времени выполнения, а изменения количества строк интерпретируйте отдельно.
Если узкое место связано с другой операцией, сохранённой в запуске C, изучите план выполнения и соответствующие события профиля запроса.

Подробнее об информации об индексах, возвращаемой EXPLAIN, см. в руководстве по диагностике медленных запросов. Внесите одно целевое изменение, затем повторите запуски A, B и C в тех же условиях. Убедитесь, что изменение сократило объём целевой работы и не переместило узкое место в другое место.

Следующие шаги

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

ClickHouse guides

Use case guides

Выявление узких мест запросов

Перед началом работы

Принцип работы

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

Последовательно выполняйте всё более простые запросы

Интерпретация различий

Сравните число прочитанных строк с результатом count

Проверьте предполагаемое узкое место

Следующие шаги

​Перед началом работы

​Принцип работы

​Создайте воспроизводимую базовую конфигурацию

​Последовательно выполняйте всё более простые запросы

​Интерпретация различий

​Сравните число прочитанных строк с результатом count

​Проверьте предполагаемое узкое место

​Следующие шаги

Перед началом работы

Принцип работы

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

Последовательно выполняйте всё более простые запросы

Интерпретация различий

Сравните число прочитанных строк с результатом count

Проверьте предполагаемое узкое место

Следующие шаги