Всем привет! Меня зовут Виктор, я работаю в Компании БФТ-Холдинг руководителем группы разработки. Продолжаю цикл статей по работе с Greenplum. В этом материале хочу рассказать, как СУБД Greenplum строит планы и выбирает самый оптимальный, а также разберу типовые проблемы, которые влияют на выбор плана запроса. Статья будет полезна разработчикам Greenplum, которые пока не имеют достаточного опыта «чтения» плана запроса.

Закон Парето (принцип Парето, принцип 80/20) – эмпирическое правило, названное в честь экономиста и социолога Вильфредо Парето, в наиболее общем виде формулируется как «20 % усилий дают 80 % результата, а остальные 80 % усилий – лишь 20 % результата». Согласно принципу Парето, в 80% запросов требуют 20% усилий, но оптимизация 20% запросов может потребовать 80% усилий…

1. Выполнение запроса

Как и другие оптимизаторы СУБД, оптимизатор Greenplum учитывает такие факторы, как количество строк в объединяемых таблицах, доступность индексов и количество данных в столбцах при расчете стоимости планов выполнения. Оптимизатор также учитывает расположение данных, предпочитая выполнять как можно большую часть работы на сегментах кластера и минимизировать объем данных, которые необходимо передать между сегментами для выполнения запроса.

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

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

План запроса — это набор операций, которые база данных Greenplum выполнит для получения результата на запрос. Каждый узел или шаг плана представляет собой операцию с базой данных, такую ​​как сканирование таблицы, объединение, агрегирование или сортировка. Планы читаются и прогоняются снизу вверх.

В дополнение к обычным операциям с базой данных, таким, как сканирование таблиц, объединение и т. д., в базе данных Greenplum есть дополнительный тип операций, называемый motion (перемещение). Операция перемещения предполагает перемещение строчек между сегментами во время обработки запроса. Существуют следующие виды перемещений:

• «Broadcast motion» — каждый сегмент отправляет свои собственные отдельные строки всем остальным сегментам так, что каждый экземпляр сегмента имеет полную локальную копию таблицы. Broadcast motion может быть не таким оптимальным, как Redistribute motion, поэтому оптимизатор обычно выбирает Broadcast motion только для небольших таблиц. Broadcast motion неприемлемо для больших таблиц. В случае, когда данные не распределились по ключу соединения, производится динамическое перераспределение нужных строк из одной из таблиц в другой сегмент.

• «Redistribute motion» — каждый сегмент перехеширует данные и отправляет строки в соответствующие сегменты в соответствии с хэш‑ключом.

• «Gather motion» — данные результатов со всех сегментов собираются в один поток. Это последняя операция для большинства планов запросов.

Чтобы добиться максимального параллелизма во время выполнения запроса, Greenplum делит работу плана запроса на slices. Slice — это часть плана, над которой сегменты могут работать независимо. План запроса делится на фрагменты везде, где в плане происходит операция motion (движение), по одному фрагменту на каждой стороне motion.

Greenplum создает ряд процессов базы данных для обработки запроса. На ведущем устройстве рабочий процесс запросов называется «query dispatcher» (QD). QD отвечает за создание и отправку плана запроса. Он также накапливает и представляет окончательные результаты. В сегментах рабочий процесс запроса называется «query executor» (QE). QE отвечает за выполнение своей части работы и передачу ее промежуточных результатов другим рабочим процессам.

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

Связанные процессы, которые работают над одним и тем же фрагментом плана запроса, но в разных сегментах, называются «gangs». По мере завершения части работы, кортежи передаются по плану запроса от одной группы процессов к другой. Такое межпроцессное взаимодействие между сегментами называется компонентом «interconnect» базы данных Greenplum.

2. Планирование запроса

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

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

GPORCA также улучшает настройку производительности запросов к базе данных Greenplum в следующих областях:

• Запросы к секционированным таблицам.

• Запросы, содержащие общее табличное выражение (CTE).

• Запросы, содержащие подзапросы.

Все параметры конфигурации сервера Postgres Planner игнорируются GPORCA.

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

План запроса представляет собой дерево узлов. Каждый узел в плане представляет одну операцию, например, сканирование таблицы, объединение, агрегацию или сортировку. План запроса «читается» снизу вверх: каждый узел передает строки в узел, расположенный непосредственно над ним. Нижние узлы плана обычно представляют собой операции сканирования таблицы: последовательное, индексное сканирование. Если запрос требует объединения, агрегирования, сортировки или других операций со строками, над узлами сканирования имеются дополнительные узлы для выполнения этих операций. Самыми верхними узлами плана обычно являются узлы движения базы данных Greenplum: redistribute, broadcast, или gather motions. Эти операции перемещают строки между экземплярами сегмента во время обработки запроса.

Предпочтительные действия в плане запроса:

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

• cost (стоимость) — абстрактная единица измерения. По умолчанию стоимость чтения одной страницы с диска (seq_page_cost) равна 1, поэтому иногда оценку измеряют в последовательном чтении одной страницы с диска. Первая оценка — это начальные затраты на получение первой строки, а вторая — общая стоимость получения всех строк. Общая стоимость предполагает, что будут извлечены все строки, что не всегда верно. Например, если в запросе используется LIMIT, будут получены не все строки. Кроме того, стоимость, генерируемая для любого данного оптимизатора, действительна только для сравнения альтернатив плана для данного отдельного запроса и набора статистических данных. Различные запросы могут генерировать планы с разными затратами, даже если оптимизатор остается постоянным.

• rows (строки) — общее количество строк, выводимых этим узлом плана. Это число обычно меньше количества строк, обработанных или просмотренных узлом плана, что отражает предполагаемую избирательность любого WHERE условия предложения. В идеале оценка для самого верхнего узла приблизительно соответствует количеству строк, которые фактически возвращает, обновляет или удаляет запрос.

• width (ширина) — общее количество байтов всех строк, которые выводит этот узел плана.

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

Опция ANALYZE команды EXPLAIN показывает фактическую стоимость выполнения вместе с оценками оптимизатора. Это позволяет увидеть, близки ли оценки оптимизатора к реальности. Используя EXPLAIN ANALYZE, можно получить следующие характеристики выполнения запроса:

• Общее время выполнения (в миллисекундах), в течение которого выполнялся запрос.

• Память, используемая каждым slice плана запроса, а также память, зарезервированная для всего оператора запроса.

• Количество сегментов, участвующих в работе узла плана. Учитываются только сегменты, возвращающие строки.

• Максимальное количество строк, возвращаемых сегментом, создавшим наибольшее количество строк для операции. Если несколько сегментов создают одинаковое количество строк, EXPLAIN ANALYZE отображает сегмент с самым долгим выполнением.

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

• Для соответствующих операций — объем памяти (work_mem), используемый операцией. Если work_mem для выполнения операции в памяти было недостаточно, в плане отображается объем данных, переброшенных на диск для самого низкопроизводительного сегмента.

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

3. Возможные проблемы выполнения запроса

1. Не верная последовательность соединения. Когда запрос включает только две или три таблицы, возможны всего несколько вариантов их соединения. Но их число растёт экспоненциально с увеличением числа задействованных таблиц. Если число таблиц больше десяти, уже практически невозможно выполнить полный перебор всех вариантов, и даже для шести или семи таблиц планирование может занять недопустимо много времени. Когда таблиц слишком много, планировщик PostgreSQL переключается с полного поиска на алгоритм генетического вероятностного поиска в ограниченном числе вариантов. (Порог для этого переключения задаётся параметром выполнения geqo_threshold.) Генетический поиск выполняется быстрее, но не гарантирует, что найденный план будет наилучшим.

Если план не выбирает оптимальный порядок соединения, на уровне сессии можно установить join_collapse_limit=1 и использовать явный JOIN синтаксис в своем операторе SQL, чтобы заставить планировщик Postgres использовать указанный порядок соединения.

2. Перемещение данных. Некоторые узлы плана запроса определяют операции движения. Операции перемещения перемещают строки между сегментами, когда это необходимо для обработки запроса. Узел идентифицирует метод, используемый для выполнения операции перемещения. 

Самым «тяжелым» перемещением является Broadcast motion. Это копирование всех строчек таблицы на каждый сегмент.

Возможные причины:

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

• Не актуальная статистика. Обычно Broadcast motion используется только с небольшим набором строк. Если перемещение Broadcast motion происходит с таблицами, которые содержат большое количество данных, то, возможно, не обновлена статистика и оптимизатор думает, что в таблица содержит небольшой набор данных

  3. Избыточные вычисления. Подзапросы, которые часто называют общими табличными выражениями или CTE, можно рассматривать как определение временных таблиц, которые существуют только для запроса. В каждом случае WITH это фактически использование временных таблиц, на которые можно ссылаться в основной команде. Команда SELECT в WITH предложении оценивается только один раз за выполнение родительского запроса, даже если на нее ссылаются более одного раза в родительском запросе или родственных WITH предложениях. Таким образом, дорогостоящие вычисления, которые необходимы в нескольких местах, могут быть помещены в одно WITH предложение, чтобы избежать избыточной работы. Другое возможное применение — предотвратить нежелательные множественные вычисления функций с побочными эффектами. Однако другая сторона медали заключается в том, что оптимизатор не всегда способен использовать ограничения из родительского запроса в запросе WITH в отличии от обычного подзапроса. Запрос WITH обычно оценивается в том виде, в котором он написан, без подавления строк, которые родительский запрос впоследствии может отбросить. Однако оценка может прекратиться раньше, если ссылки на запрос требуют только ограниченного числа строк.

  4. Не корректная статистика. При выполнении команды EXPLAIN с опцией ANALYZE плановое количество строк может на порядок отличаться от фактического количества строк. Если имеется большое расхождение, возможно, необходимо собрать дополнительную статистику по соответствующим столбцам. Обновление статистики выполняется с помощью команды ANALYZE, которая позволяет планировщику запросов выбирать оптимальные планы запросов. При анализе таблицы, информация о данных сохраняется в таблицах системного каталога (в частности, поля reltuples, relpages в таблице pg_class). Если сохраненная информация устарела, планировщик может создать неэффективные планы. Дату последнего анализа таблицы можно узнать из колонок last_analyze, last_autoanalyze системной таблицы pg_stat_all_tables.

  5. Не используется оптимизатор GPORCA. При планировании важно использовать оптимизатор GPORCA, который оптимизирован для работы с Greenplum. 

Возможные причины:

• Подготовленные операторы с параметризованными значениями.

• Многоуровневые секционированные таблицы.

• Агрегатные функции, которые принимают операторы множества в качестве входных аргументов.

• и т.д.

Полный список возможный причин можно прочитать в GPORCA Limitations.

4. Стейджинговые таблицы

Для оптимальной работы запросов в СУБД гринплам при соединении таблиц лучше всего использовать distributed by по колонкам, которые указаны в условиях соединения. Но в гринплам запросы могут выполнятся по разным колонкам одной и той же таблицы, а в distributed by можно указать только определенный набор колонок, что далеко не всегда подходит под разные задачи. Для решения проблемы с необходимым распределением данных по сегментам, в гринплам можно использовать набор стейджинговых таблиц и фрейворк dbt (data build tool).

dbt — это процесс преобразования данных, который помогает выполнять больше работы, обеспечивая при этом более качественные результаты. Dbt можно использовать для модульности и централизации аналитического кода. Более подробное описание процесса построения витрины с использованием фреймворка dbt будет в отдельной статье на хабре, а пока расскажу в «двух словах».

Процесс построения витрины данных состоит из нескольких этапов:

1. Построение стейджинговых таблиц

2. Агрегация данных

3. Тестирование данных

В данной статье мы рассмотрим только построение стейджинговых таблиц.

В dbt моделях реализована возможность указать следующие основные атрибуты:

• тип формирования данных (‘ephemeral’, ‘table’),

• название таблицы,

• схема,

• поля, по которым будет происходить распределение данных по сегментам.

Пример dbt модели

“

{{

    config

    (

    materialized='table',

    alias='products',

    schema='stg',

    distributed_by=' category',

    orientation='column',

    compresstype='ZSTD',

    compresslevel=4,

    appendonly='true',

    )  

}}

select category, price

from core.products

where …

“

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

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

Используя шаблоны JINJA, можно управлять последовательностью выполнения запросов.

{% if var('param') == 'car' %}

…

{% else %}

…

{% endif%}

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

5. Резюме

Планировщик запросов — сложный механизм, который учитывает большое количество факторов, влияющих на выполнение запросов. К сожалению, нет «универсального лекарства», которое помогло бы «вылечить» долгие и тяжелые запросы. Да, большая часть запросов обычно выполняется за приемлемое время и не требует много усилий. Но некоторые запросы требуют хорошего знания структуры базы данных, объема данных и хорошего понимания «плана запроса», чтобы достичь приемлемого времени выполнения этих запросов.

Тем, кто дочитал до конца +100500 в карму и спасибо за внимание. Надеюсь, материал был вам полезен. Задавайте вопросы в комментариях.