Как лучше настроить GPORCA для оптимизации SQL-запросов в Greenplum

В рамках программы курсов по Greenplum и Arenadata DB, сегодня рассмотрим важную для разработчиков и администраторов тему об особенностях оптимизатора SQL-запросов GPORCA, который ускоряет аналитику больших данных лучше встроенного PostgreSQL-планировщика. Читайте далее, как выбирать ключ дистрибуции, почему для GPORCA важна унифицированная структура многоуровневой партиционированной таблицы и каким образом оптимизаторы обрабатывают таблицы без статистики.

Что такое GPORCA: краткий ликбез по оптимизаторам SQL-запросов в Greenplum

Напомним, в Greenplum есть два оптимизатора SQL-запросов: встроенный на основе PostgreSQL-планировщика и более быстрый ORCA-вариант, который называется GPORCA. Ориентируясь на область Big Data, GPORCA расширяет возможности планирования и оптимизации PostgreSQL-планировщика в средах с многоядерной архитектурой. В частности, GPORCA улучшает настройку производительности SQL-запросов к партиционированным таблицам, поддерживая CTE-запросы с общими табличными выражениями и с подзапросами.

По умолчанию в этой MPP-СУБД используется именно GPORCA, с версии 6.13 применяя методы динамического программирования для многосторонних соединений таблиц и «жадные» алгоритмы оптимизации для ускорения SQL-запросов. В Greenplum 6.14 оптимизатор GPORCA включает обновления, который еще более сокращают время оптимизации и улучшают планы выполнения SQL-запросов для больших соединений [1]. Подробно о том, какие именно математические алгоритмы обеспечивают быстроту работы GPORCA-оптимизаторы, мы писали здесь. А сейчас детально рассмотрим некоторые особенности этого компонента Greenplum и Arenadata DB.

Greenplum для инженеров данных и аналитиков данных

Код курса

GPDE

Ближайшая дата курса

27 января, 2025

Продолжительность

24 ак.часов

Стоимость обучения

72 000 руб.

Тонкости ORCA-оптимизатора и лучшие практики его настройки

GPORCA позволяет выполнять update поля партиционирования и дистрибуции, однако, этот оптимизатор очень требователен к статистике таблиц, собрать которую помогает оператор ANALYZE [2]. Как рабоатет этот оператор, мы рассказывали здесь. Официальная документация Greenplum отмечает, что для эффективного выполнения SQL-запроса с помощью GPORCA, он должен соответствовать следующим критериям [3]:

• ключи партиционирования заданы по одному, а не по нескольким столбцам таблицы;
• многоуровневая партиционированная таблица является унифицированной. Это означает, что она создана с помощью предложения SUBPARTITION в выражении CREATE TABLE и имеет единообразную структуру для каждого узла раздела на одном уровне иерархии. Ограничения ключа раздела тоже согласованны и единообразны, а также совпадают для дочерних таблиц. При этом названия разделов могут быть разными.
• Параметр конфигурации сервера optimizer_enable_master_only_queries включен при работе только с основными таблицами, такими как системная таблица pg_attribute. Поскольку общее включение этого параметра снижает производительность коротких запросов к каталогу, его необходимо задавать только для сеанса или отдельного SQL-запроса.
• Статистика собрана в корневом разделе партиционированной таблицы.
• Рекомендуется, чтобы число разделов в партиционированной таблице не превышало 20 000. Иначе следует рассмотреть возможность изменения ее схемы.

На обработку SQL-запросов с помощью GPORCA влияют следующие параметры конфигурации сервера Greenplum [3]:

• optimizer_cte_inlining_bound – управляет объемом строк для CTE-запросов с условием WHERE.
• optimizer_force_multistage_agg – указывает GPORCA на выбор многоступенчатого агрегатного плана для отдельного скалярного агрегата. Когда это значение выключено (по умолчанию), GPORCA выбирает между одноэтапным и двухэтапным агрегированным планом в зависимости от стоимости SQL-запроса, что мы разбирали в этой статье.
• optimizer_force_three_stage_scalar_dqa – указывает GPORCA на выбор плана с многоступенчатыми агрегатами.
• optimizer_join_order – устанавливает уровень оптимизации запроса для упорядочивания соединений, указывая, какие типы альтернативных вариантов следует оценивать. Про операторы JOIN-соединений в Greenplum читайте здесь.
• optimizer_join_order_threshold – указывает максимальное количество дочерних элементов соединения, для которых GPORCA использует алгоритм упорядочения соединений на основе динамического программирования.
• optimizer_nestloop_factor – управляет коэффициентом стоимости соединения вложенного цикла (Nested Loop Join) при оптимизации SQL-запроса.
• optimizer_parallel_union – контролирует степень распараллеливания для запросов с UNION или UNION ALL. Если этот параметр включен, GPORCA может сгенерировать план запроса дочерних операций для UNION или UNION ALL, выполняемых параллельно на экземплярах сегмента Greenplum.
• optimizer_sort_factor – контролирует фактор стоимости к операциям сортировки во время оптимизации запроса, позволяя корректировать его при наличии перекоса данных.
• gp_enable_relsize_collection – управляет тем, как GPORCA и PostgreSQL- планировщик обрабатывают таблицу без статистики. Если она недоступна, GPORCA использует значение по умолчанию для оценки количества строк. Когда это значение включено, GPORCA использует оценочный размер таблицы. Для корневого раздела партиционированной таблицы этот параметр игнорируется – при отсутствии статистики для нее GPORCA всегда использует значение по умолчанию. Можно использовать оператор ANALZYE ROOTPARTITION для сбора статистики по корневому разделу.

А следующие параметры конфигурации сервера Greenplum управляют отображением и логированием информации [3]:

• optimizer_print_missing_stats (по умолчанию true) управляет отображением информации о столбце (команда display) при отсутствии статистики для запроса;
• optimizer_print_optimization_stats контролирует логирование метрик GPORCA-оптимизатора для SQL-запроса (по умолчанию выключено).

Для каждого запроса GPORCA создает мини-дампы с описанием контекста оптимизации, которые используются службой поддержки VMware для анализа проблем с Greenplum. Файлы минидампа находятся в каталоге основных данных и называются следующим образом Minidump_date_time.mdp. Когда команда EXPLAIN ANALYZE использует GPORCA, в плане показывается только количество удаляемых разделов, а все просканированные разделы не отображаются. Чтобы имя просканированных разделов отображалось в логах сегментов Greenplum, следует включить параметр конфигурации сервера:

SET gp_log_dynamic_partition_pruning = on.

В заключение отметим, что при работе с распределенными СУБД для оптимизации SQL-запроса важен не только он, но и особенности хранения данных. В Greenplum они физически хранятся на разных сегментах, разделенные случайным образом или по значению хэш-функции от одного или нескольких полей. Рекомендуются следующие советы по выбору этого поля, называемого ключ дистрибуции [2]:

• минимум NULL- значений, которые будут распределены на один сегмент, что может привести к перекосу данных
• тип данных integer, с которым лучше всего работает часто используемый вариант соединения таблиц Hash Join;
• более одного поля в ключе дистрибуции увеличивает время хэширования и часто требуют передачи данных между сегментами Greenplum при соединении таблиц.
• заданный ключ дистрибуции обычно лучше случайного;
• для оптимального соединения таблиц одинаковые значения должны быть расположены на одном сегменте Greenplum, а тип полей в Join-условии должен быть одинаков во всех таблицах.
• Не следует использовать в качестве ключей дистрибуции поля, которые используются при фильтрации запросов с выражением WHERE из-за неравномерного распределения нагрузки.
• Не стоит использовать один и тот же столбец в качестве ключа партиционирования и дистрибуции, т.к. в этом случае SQL-запрос будет выполняться целиком на одном сегменте, исключая преимущества распараллеливания. Подробнее об этом мы рассказываем в новой статье.

Администрирование Greenplum / Arenadata DB

Код курса

GRAD

Ближайшая дата курса

2 декабря, 2024

Продолжительность

40 ак.часов

Стоимость обучения

120 000 руб.

Освойте тонкости администрирования и эксплуатации Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на авторских курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве.

Источники

1. https://greenplum.org/faster-optimization-of-join-queries-in-orca/
2. https://habr.com/ru/company/rostelecom/blog/442758/
3. https://gpdb.docs.pivotal.io/6-17/admin_guide/query/topics/query-piv-opt-overview.html