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

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

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

Чтобы выполнить примеры из этого руководства без изменений, создайте и загрузите таблицу nyc_taxi.trips_small_inferred, если ещё не сделали этого:

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

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

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

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

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

• Не изменяйте секции 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;

Этот процесс сочетает контролируемые запуски запросов с измерениями из журнала запросов:

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

1. Назначьте каждому запуску уникальный ID запроса или запишите ID, созданный интерфейсом выполнения запросов. Например, обозначайте повторные запуски как bottleneck-a-1, bottleneck-a-2 и bottleneck-a-3. При использовании clickhouse-client передавайте --query_id your-query-id при выполнении запроса.

2. Выполните каждый сравнительный запрос несколько раз в одинаковых условиях. Отделяйте прогревочные запуски от измеряемых.

3. Сбросьте журнал запросов перед поиском недавно завершённых запросов:

   SYSTEM FLUSH LOGS;

   Если вы не можете выполнить SYSTEM FLUSH LOGS, дождитесь автоматического сброса журнала запросов, затем повторите поиск. Если запись так и не появилась, убедитесь, что журналирование запросов включено, у вас есть доступ на чтение system.query_log и вы обращаетесь к узлу, на котором выполнялся запрос.

4. Найдите завершённую запись для каждого 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;

5. Для каждой версии запроса используйте медианную длительность измеряемых запусков. Запишите read_rows, read_bytes и пиковое потребление памяти для запуска, наиболее близкого к этой медиане, чтобы измерения были привязаны к фактическому запуску.

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

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

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

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;

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;

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

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

Сравните 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 в тех же условиях. Убедитесь, что изменение сократило объём целевой работы и не переместило узкое место в другое место.

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