Оптимизация Greenplum

Greenplum (GP) – массивно параллельная СУБД (Massively ParallelProcessing), обладает линейной масштабируемостью, применяется в системах с большими данными. Логически база данных в GP - это массив отдельных экземпляров баз данных PostgreSQL, расположенных на серверах кластера. Мастер (master instance – экземпляр PostgreSQL, расположенный на мастер-сервере) координирует рабочую нагрузку на другие инстансы (primary segment instance – сегменты), расположенные на серверах-сегментах, которые занимаются обработкой и хранением данных. Сегменты обмениваются данными друг с другом и с мастером через интерконнект (interconnect) при помощи одной или нескольких сетей. GP работает со скоростью самого медленного из своих сегментов, поскольку GP выдает данные только тогда, когда все сегменты завершили необходимые вычисления. Вот почему все сервера-сегменты в кластере должны иметь одинаковые характеристики.

• хранение структурированных данных;
• анализ данных, работа с Business Intelligence приложениями;
• использование алгоритмов машинного обучения.

Преимущества GP

• Полная поддержка SQL ANSI, Common Table Expressions (CTE), оконных функций, хранимых процедур;
• Интеграция с PostgreSQL;
• OLAP + ETL в одной базе, поддержка транзакций и агрегаций;
• Поддержка Row + Columnar (Append Optimized).

Greenplum эффективна на тех операциях, которые можно распараллелить: полное сканирование (Full Scan) таблиц, соединение таблиц (Join), агрегация данных (Aggregation), загрузка данных из внешних источников.

Сегментами GP являются PostgreSQL-инстансы, поэтому применимы многие советы по оптимизации PostgreSQL.
Ниже дан список (чек-лист) специфичных рекомендаций по оптимизации Greenplum.

DISTRIBUTED BY = 80% успеха

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

• Поле(я) дистрибуции должно иметь высокую селективность (большое количество уникальных значений).

• Тип поля дистрибуции желательно целочисленный.

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

• Всегда явно указывать способ распределения в DDL.

• Указывать способ распределения для временных таблиц.

• Для дистрибуции желательно выбирать одно поле или не более двух полей. Дополнительные поля в ключах дистрибуции требуют дополнительное время на хэширование, и зачастую при выполнении JOIN-ов потребуют пересылку данных между сегментами.

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

• Не использовать в качестве полей дистрибуции те поля, что используются при фильтрации запросов в WHERE, ибо в этом случае при выполнении запроса нагрузка будет распределена не равномерно.

• DISTRIBUTED BY (columns) - хэш-распределение: конкретный сегмент выбирается на основе хэшей, которые рассчитываются по указанным полям. Рекомендуется использовать в качестве ключа распределения столбцы первичного ключа (PRIMARY KEY) либо столбцы с уникальными значениями (UNIQUE).

• DISTRIBUTED REPLICATED - распределение данных, при котором копия таблицы сохраняется на каждом сегменте кластера, что позволяет избежать любых перемещений данных при запросах с JOIN-ами. Рекомендуется для небольших / справочных таблиц.
Таблицы менее 300 Мб - в DISTRIBUTED REPLICATED. Избегать Broadcast Motion для таблицы менее 300 Мб. Если EXPLAIN ANALYZE показывает Broadcast Motion > 3x(количество строк в небольшой таблице), то необходимо изменить ключ распределения или переписать SQL-запрос.

• DISTRIBUTED RANDOMLY - случайное распределение данных с использованием алгоритма Round-Robin. Следить! Оптимизатор может взять не тот столбец, и высока вероятность перекоса (Skew) и переноса (Motion) данных. Если таблица участвует в двух и более больших JOIN-ах, то для DISTRIBUTED должен выбираться тот столбец, который после фильтрации имеет наименьшую кардинальность, чтобы сократить Redistribute-Motion.
Случайное распределение допустимо для небольших табличек или тогда, когда нет возможности подобрать одно или два подходящих поля. Рандомное распределение лучше всего работает при массовой вставке данных.

Перемещение данных между сегментами

• Broadcast Motion — каждый сегмент отправляет свою копию данных на другие сегменты. Хорошо только для маленьких таблиц.

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

• Gather Motion — результирующие данные всех сегментов собираются в единый поток в финале запроса.

Broadcast Motion и Redistribute Motion - невыгодные операции, выполняются при каждом запуске запроса. Если в плане запроса есть такие неприятности, то следует подумать о ключах распределения. Операции UNION и DISTINCT являются причиной Motions. На больших таблицах эти операции приводят к созданию большого объёма spill-файлов.
Redistribute Motion лучше Broadcast Motion, укажите в настройках gp_segments_for_planner = большое число.

gp_toolkit.gp_skew_coefficients - это представление показывает перекосы в распределении данных посредством вычисления коэффициента вариации для данных, хранящихся на каждом сегменте. Столбец skccoeff показывает коэффициент вариации, который рассчитывается как стандартное отклонение, поделённое на среднее. Он учитывает как среднее, так и отклонения от среднего в рядах данных. Чем выше это значение, тем больше перекос данных.

gp_toolkit.gp_skew_idle_fractions - это представление показывает перекосы в распределении данных посредством вычисления процента (см. столбец siffraction) ресурсов системы, которые простаивают во время сканирования таблицы, что является индикатором перекоса обработки. Например, значение 0.2 означает 20% перекос. Для таблиц с перекосом данных более 10% требуется пересмотр политики распределения.

Партиционирование

Секционированная таблица - это набор подтаблиц, где каждая партиция - это отдельная таблица. Партиция, в свою очередь, распределена по сегментам через дистрибьюцию. Партиционирование подходит для огромных таблиц фактов. Благодаря MPP-архитектуре Greenplum операция полного сканирования (Full Scan) таблиц выполняется эффективно, можно использовать партиции бóльшего размера по сравнению с другими СУБД.

• Следует избегать создания большого количества партиций. Слишком большое количество партиций может замедлить работы: очистка операцией VACUUM, восстановление сегментов, расширение кластера, проверка использования диска и другие.

• Не использовать многоуровневое секционирование, поскольку после второго уровня количество физических файлов растёт как Segments * Columns * Partitions. Слишком много партиций для колоночных таблиц приводит к росту количества файлов на каждом сегменте и снижению производительности операций чтения / записи: количество файлов равно произведению количества сегментов на количество столбцов и на количество партиций.
Субпартиционирование поддерживается, оно не рекомендуется, потому что обычно субпартиции содержат очень мало данных, либо не содержат их вообще. Нагрузка по поддержке множества партиций и субпартиций превысит любые приросты производительности.

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

• В зависимости от размера кластера размер партиции - от сотен мегабайт до десятков гигабайт.

• Разбивать таблицу на партиции по полю(ям), которые часто используются в условии фильтрации WHERE.

• Никогда не выбирать одно и то же поле и для партиционирования и для распределения, поскольку потеря параллелизма, потеря Partition Elimination на half-сегментах (где партиция одна и та же, но данные разнесены по узлам), неравномерное распределение нагрузки (Data Skew), потеря Colocated Join.

• Для больших фактовых таблиц с партиционированием модель хранения разная: для некоторых партиций может быть строковой, для остальных – колоночной.

Ориентация и сжатие данных

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

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

Сжатие доступно только для Append-Optimized таблиц. Сильное сжатие минимизирует занимаемое место на диске, но требуется больше ресурсов процессоров. Надлежит искать оптимальные настройки сжатия данных, которые не будут приводить к слишком большому времени сжатия и медленному сканированию таблиц.

• Сжатие данных на уровне таблицы (Table Level) применяется ко всей таблице, доступно для таблиц как со строковой, так и с колоночной ориентацией данных;
• Сжатие данных на уровне столбца (Column Level) применяется к отдельному столбцу, что позволяет использовать различные алгоритмы сжатия для отдельных столбцов таблицы. Данный тип сжатия доступен только для таблиц с колоночной ориентацией данных.

Лучшей практикой является установка настроек сжатия на уровне партиции, а не всей таблицы. Добавляемые в партиционированную таблицу новые партиции автоматически не наследуют настройки сжатия, определенные на уровне таблицы.

* ZSTD - обеспечивает и скорость и хорошую степень сжатия, которую можно сконфигурировать с помощью опции compresslevel;
* ZLIB - используется для обратной совместимости. Обычно ZLIB уступает ZSTD при обыкновенных рабочих нагрузках. Использование ZLIB при compresslevel=6 может значительно повысить коэффициент сжатия в сравнении с QuickLZ, но скорость сжатия будет меньше.
Допустимые значения уровня сжатия (параметра compressionlevel) = 1 - 9;
* RLE_TYPE - сжимает лучше других данные тогда, когда одинаковые значения встречаются во множестве последовательных строк. Этот тип сжатия не подходит для таблиц, не содержащих больших наборов повторяющихся данных.
Допустимые значения уровня сжатия = 1 - 4;
* QuickLZ - недоступен в open-source версии, обычно использует меньше ресурсов CPU и сжимает данные быстрее при низких коэффициентах сжатия, чем алгоритм ZLIB. Допустимое значение compressionlevel = 1.

Для большинства задач подходит алгоритм ZSTD с уровнем сжатия данных 1 или 2. Compresslevel больше 10 целесообразен для витрин, которые не подлежат регулярному обновлению.

• Применяется по умолчанию;
• Оптимально для операций UPDATE;
• Доступны алгоритмы сжатия QuickLZ, ZLIB;
• Данные таблицы хранятся на сегменте в одном файле;
• При достижении размера более 1Гб таблица разбивается на файлы по 1Гб.

• Доступны алгоритмы сжатия QuickLZ, ZLIB, RLE;
• Более эффективное сжатие;
• Данные каждой колонки таблицы хранятся в отдельном файле.

SQL-запросы

• В идеальном случае осуществлять JOIN по полям дистрибуции хотя бы одной из таблиц.

• Если в одном запросе используется более 10-15 таблиц, то эвристика планировщика сбивается. Такой запрос надлежит разделить на промежуточные с материализацией. Это старый приём в СУБД Oracle и Teradata.

• Избегать OR, LIKE в условиях соединения таблиц.

• В случае поле = ключ партиционирования и применения функции к этому полю в условии фильтрации (WHERE date_trunc('month',transaction_date)) Orca не будет отсекать ненужные партиции, т.е. не надо использовать функции в этом условии.

• Использование коррелирующих подзапросов в GP крайне нежелательно.

• Операция сортировки (ORDER BY) - одна из самых тяжёлых операций для многих СУБД и не стоит её применять без острой необходимости. Пусть сортировкой занимается клиентское приложение.

• В таблице с колоночным хранением подсчитать количество строк: вместо
SELECT COUNT(*) FROM my_table -- будут извлечены все столбцы
использовать
SELECT COUNT(1) FROM my_table -- будет извлечен только один столбец

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

• При массивной вставке записей в таблицу эффективнее удалить индекс перед INSERT и создать индекс заново после вставки. Это также известная практика в СУБД Oracle и MS SQL.

Планы запросов и оптимизатор

• EXPLAIN – план запроса формируется планировщиком на основе запроса, DDL таблиц и статистики. Выводится оценочное количество записей в промежуточных результатах запроса.
- - Правильная оценка количества записей в таблицах;
- - Правильная оценка количества записей после фильтров;
- - Отсутствие лишних редистрибьюций;
- - Partition Elimination;
- - Признаки вычислительных перекосов. Неравномерность вычислений происходит во время выполнения запроса, когда выполнение операторов Hash Aggregate и Hash Join, вызывает неравномерное выполнение на сегментах. Если максимальное число строк намного выше среднего, то хотя бы один сегмент выполнил гораздо больше работы, чем остальные;
- - Операция HashAggregate предпочтительнее операций Sort и Aggregate, когда требуется отсортировать большое количество строк. Чтобы оптимизатор отдавал предпочтение операции HashAggregate перед операцией сортировки и агрегирования, должен быть параметр конфигурации сервера enable_groupagg = ON;
- - Если план запроса показывает Broadcast Motion с большим количеством строк, следует устранить Broadcast Motion, используя параметр конфигурации сервера gp_segments_for_planner для увеличения оценки стоимости движения. Увеличение количества сегментов (например, gp_segments_for_planner = 100000) увеличивает стоимость движения, тем самым способствуя использованию Redistribute Motion.

• EXPLAIN ANALYZE – для вывода плана запроса Greenplum сначала выполняет этот запрос. План запроса с фактическими значениями количества записей в промежуточных результатах запроса, а также фактическим потреблением ресурсов (память, временные файлы, время выполнения отдельных этапов).
- - Соотношение среднего / максимального количества записей по сегментам;
- - Значение Start Offset By.

План запроса читать полагается снизу вверх.

Index Scan - фильтрация нужных строк по индексу;
Bitmap Heap Scan - находит указатели на записи таблицы из индекса и сортирует их по месту нахождения на диске;
Dynamic Seq Scan - выбор партиций для сканирования;
Seq Scan on Tables - сканируются все строки таблицы;
Nested Loop - вложенный цикл: объём данных в двух связанных таблицах передается в таблицу меньшего размера, например в виде декартова произведения. Хороший вариант соединения маленьких и средних таблиц и плохой вариант для соединения больших таблиц, хорошо использует условия меньше / больше / между;
Hash Join - объединение по хэшу: вычисляется значение хэша для одной из связанных таблиц, сохранение в памяти вместе с хэш-списком, а затем сканирование другой таблицы в поисках полной таблицы, а затем связывание каждой строки с хэш-списком. Наилучший вариант соединения больших таблиц, хуже (чем Nested Loop) работает с условиями меньше / больше / между;
Merge Join - объединение слиянием: две таблицы сортируются по ассоциативному ключу, данные связываются таким же образом, как и сортировка слиянием, что менее эффективно, чем Hash Join. Полное внешнее объединение может быть реализовано только посредством Merge Join. Годится для маленьких таблиц;
Group / Hash Aggregate - группировка с использованием хэша, HashAggregate предпочтительнее Sort and Aggregate, должен быть включен enable_groupagg и переписать запрос;
Append - соединение выборки из партиций;
Materialize - материализация выборки данных, чтобы не собирать её несколько раз.

В Greenplum есть два оптимизатора: встроенный Legacy Optimizer и сторонний оптимизатор Orca - GPORCA. Использовать анализатор Orca. Как правило, оптимизатор GPORCA лучше встроенного, лучше работает с подзапросами и CTE. Orca разрешает выполнять UPDATE поля партиционирования / дистрибуции.
Включить GPORCA на запрос:
SET OPTIMIZER = ON

Оптимизатор Orca очень требователен к статистике. Необходимо осуществлять сбор актуальной статистики. Сбор статистики на огромных таблицах занимает много времени. После любой вставки строк более 5% обязательно ANALYZE tbl, поскольку анализатор Orca недооценивает кардинальность в 10-50 раз и порождает Broadcast Motion вместо Redistribute.

В файле postgresql.conf есть настраиваемый параметр Gp_autostats_mode, управляющий сбором статистики.

None: сбор статистики запрещён;
No_change: при выполнении DML после того, как количество затронутых строк превысит значение, указанное параметром Gp_autostats_on_change_threshold, после DML автоматически выполнится операция сбора статистики для таблицы;
No_no_stats: действует по умолчанию, применяется к базам данных небольшого размера, но для постоянно меняющихся таблиц после первого сбора статистика не собирается. Анализ должен выполняться по расписанию.

Управление памятью

Greenplum как MPP-система активно использует оперативную память для выполнения сложных аналитических операций: агрегации, сортировки, хеширования и оконные функции. Недостаток памяти приводит к сбросу данных на диск (Spilling), что резко замедляет выполнение запросов.
Оптимизация памяти в GP затрагивает три основных уровня: системный (конфигурация), управление рабочей нагрузкой (WLM), уровень запроса.

Системный уровень и конфигурация

На этом уровне определяется, сколько оперативной памяти доступно каждому сегменту и как она распределяется.

* gp_vmem_protect_limit (устаревший, но важный): определяет максимальный объём виртуальной памяти, который может использовать каждый сегмент. Это системный лимит, защищающий сегмент от полного исчерпания памяти и предотвращает сбой операционной системы. Должен быть меньше физической памяти сегмента.
* statement_mem (устаревший): определяет базовый объём памяти, выделяемый каждому оператору запроса.
* resource_groups (современный подход):
MEMORY_LIMIT: определяет процент общей памяти сегмента, который может использовать данная группа ресурсов.
MEMORY_SPILL_RATIO: определяет, какой процент выделенной памяти может быть использован до того, как GP начнет сбрасывать данные на диск. Обычно устанавливается в 100%, чтобы использовать всю выделенную память.

Пример настройки Resource Group. Если сегмент имеет 64 Gb RAM, и хочется выделить 40% этой памяти для аналитической группы:
ALTER RESOURCE GROUP analytics_group SET MEMORY_LIMIT 40

Количество сегментов на физическом хосте напрямую влияет на доступную память. Если хост с 128 Gb RAM и 8 сегментами, то каждый сегмент получит примерно 16 Gb. Если увеличить количество сегментов до 16, то каждый сегмент получит всего 8 Gb.
Меньшее количество сегментов с большим объёмом памяти часто лучше для сложных аналитических запросов, чем большое количество сегментов с малым объёмом памяти.

Управление рабочей нагрузкой (WLM)

WLM гарантирует, что ни один запрос не задушит систему, забрав всю оперативную память.

Контроль параллелизма (CONCURRENCY): ограничение количества одновременно выполняющихся запросов в Resource Group критически важно. Если выделить 40% памяти сегмента группе и позволить 10 запросам выполняться одновременно, то каждый запрос получит лишь 4% памяти сегмента. Если какой-то запрос затребует больше, то начнётся сброс на диск.
Пример: если известно, что тяжёлые запросы требуют минимум 10% памяти сегмента, то надо установить CONCURRENCY не выше 4 (4 * 10% = 40% лимита группы):
ALTER RESOURCE GROUP heavy_queries SET CONCURRENCY 4

Использование MEMORY_SHARED_QUOTA (для Resource Groups): эта настройка позволяет запросам использовать неиспользованную оперативную память других запросов в той же группе. Это повышает общую утилизацию памяти, но может привести к тому, что внезапно запущенный тяжёлый запрос не сможет получить свою долю.

Оптимизация на уровне SQL-запроса

Даже при идеальной конфигурации WLM плохо написанный запрос может вызвать проблемы с оперативной памятью. Операции ORDER BY, GROUP BY, DISTINCT, оконные функции ROW_NUMBER(), LAG(), LEAD() требуют значительного объёма памяти для сортировки и хеширования.

• Использование JOIN по Distribution Key: если таблицы распределены по ключу соединения, GP выполняет Colocated Join, который не требует перемещения данных между сегментами и минимизирует накладные расходы на память.
• Избегать CROSS JOIN и FULL JOIN: они могут генерировать огромные промежуточные наборы данных, требующие много памяти.
• SET enable_hashjoin = OFF: по умолчанию Greenplum предпочитает Hash Join, который требует загрузки меньшей таблицы или её хеш-таблицы в память. Если сервер имеет мало памяти, и соединяются две очень большие таблицы, то Hash Join может привести к сбросу на диск. В таких случаях Nested Loop Join или Merge Join (если данные отсортированы) могут быть медленнее, но более бережливыми к памяти.

Управление промежуточными результатами (CTE и подзапросы): хотя CTE улучшают читаемость, GP по умолчанию не материализует промежуточные результаты CTE, они пересчитываются. Если промежуточный результат CTE очень большой и используется несколько раз, его материализация может потребовать много памяти.
Если промежуточный результат достаточно мал, его можно сохранить во временную таблицу (CREATE TEMP TABLE ...), чтобы избежать повторного вычисления.

Актуальная статистика (ANALYZE) позволяет оптимизатору Greenplum точно оценить размер промежуточных результатов. Если статистика устарела, оптимизатор может недооценить объём данных, выделить слишком мало памяти для Hash Join или сортировки, что приведет к немедленному сбросу данных на диск.

Мониторинг и диагностика Spilling

Ключ к оптимизации — знать, когда и почему происходит сброс данных.
При выполнении EXPLAIN ANALYZE ищите в выводе информацию о сбросе:
-> Sort (cost=...) (rows=...)
(seg0 slice1) Memory: 1000kB, **Spill: 500MB** <-- Это признак проблемы!

• gp_toolkit.gp_resgroup_status: системное представление показывает текущее использование памяти Resource Groups.
• pg_stat_activity: системное представление показывает текущие запросы и их статус.
• Логи сегментов содержат сообщения о сбросе данных на диск.

В старых версиях Greenplum (до Resource Groups) параметр work_mem контролировал память, выделяемую для сортировки и хеширования. Если все ещё используете Resource Queues, увеличение work_mem может помочь, но будьте осторожны, чтобы не превысить gp_vmem_protect_limit.
Современный подход: в Resource Groups work_mem обычно устанавливается в 0, и управление памятью полностью передается Resource Group.

Причины снижения производительности Greenplum

Типичные причины общей деградации производительности Greenplum

• Упала часть сегментов Greenplum и работают зеркалированные сегменты;
• Проблемы с диском на одном из сегментов;
• Чрезмерная нагрузка на файловую систему и каталог из-за большого количества объектов в базе данных;
• Разбухание каталога;
• Высокое потребление ресурсов кластера другими процессами, не Greenplum.

Причины деградации производительности отдельного процесса

• Неоптимальный план запроса из-за отсутствия статистики;
• Неравномерное распределение данных по сегментам в таблицах;
• Блокировка объектов базы данных разными запросами;
• Ограничения очереди ресурсов по количеству активных запросов.