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

Greenplum (GP) – массивно параллельная СУБД (Massively ParallelProcessing), обладает линейной масштабируемостью, применяется в системах с большими данными.

• хранение структурированных данных;
• анализ данных, работа с 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 = большое число.

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

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

• Следует избегать создания большого количества партиций;

• Не использовать многоуровневое секционирование, поскольку после второго уровня количество физических файлов растёт как Segments * Columns * Partitions ;

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

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

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

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

Сжатие доступно только для Append-Optimized таблиц.

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

* ZSTD - обеспечивает и скорость и хорошую степень сжатия, которую можно сконфигурировать с помощью опции compresslevel;
* ZLIB - используется для обратной совместимости. Обычно ZLIB уступает ZSTD при обыкновенных рабочих нагрузках;
* RLE_TYPE - сжимает лучше других данные тогда, когда одинаковые значения встречаются во множестве последовательных строк. Этот тип сжатия не подходит для таблиц, не содержащих больших наборов повторяющихся данных.

Для большинства задач подходит алгоритм 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 не будет отсекать ненужные партиции, т.е. не надо использовать функции в этом условии.

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

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

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

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

• EXPLAIN – план запроса формируется планировщиком на основе запроса, DDL таблиц и статистики. Выводится оценочное количество записей в промежуточных результатах запроса.
- - Правильная оценка количества записей в таблицах;
- - Правильная оценка количества записей после фильтров;
- - Отсутствие лишних редистрибьюций;
- - Partition Elimination.

• 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

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

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

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

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