блог о bi, №1 в рунете

Лучшие практики дизайна схем, отладки запросов
и поддержки БД в Greenplum

- Greenplum -

Лучшие практики – это свод методов, подходов к чему-либо, которые в результате использования показали свои превосходство над альтернативами. Это слияние знаний и опыта, позволяющее использовать продукт верно и оптимально.

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

В рамках данной статьи мы постараемся не только рассказать о правилах, которых следует придерживаться, но и объяснить почему их следует придерживаться. Будут рассмотрены особенности дизайна схем и способы оптимизации запросов в реалиях MPP-архитектуры Greenplum. Также уделим внимание части аспектов поддержки базы.

Дизайн схем

Greenplum является аналитической базой и лучше всего работает с денормализованными таблицами.

При проектировании важно учитывать следующие факторы:

Типы данных

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

Используйте типы данных с наименьшим необходимым размером. Пускай, использование TEXT или VARCHAR вместо CHAR и не влияет на производительность, зато позволяет сохранить место на диске. В документации Greenplum рекомендовано не заполнять диск более чем на 70%, так как базе для работы необходимо некоторое количество свободного места.

Модели хранения

Greenplum предоставляет несколько моделей хранения данных.
Heap storage является моделью хранения по умолчанию и поддерживает только строковую ориентацию.
Append-optimized storage позволяет использовать как строковую, так и колоночную ориентацию; по умолчанию ориентация строковая.
Следует отметить, что Greenplum поддерживает гибридное хранение – при использовании секционирования для каждой из секций можно задать свой формат хранения.

Приведем рекомендации по выбору и использованию моделей хранения:

Таблицы с append-optimized storage предназначены для хранения больших таблиц фактов, которые нечасто обновляются и используются для аналитических запросов, также append-optimized storage экономит около 20 байт на строку.
Однако, для этого типа хранения операции удаления и обновления, особенно частые и с небольшим количеством строк, не эффективны. Файлы с данными append-optimized таблиц на самом деле append-only – доступны только для добавления. Физически перезаписать хранящиеся строки невозможно. Однако особенности реализации, о которых в рамках статьи говорить будет излишне, делают операции DELETE и UPDATE возможными с точки зрения пользователя.

Heap и Append-Optimized Storage

Используйте heap storage, если над таблицей будут проводиться итеративные пакетные или одиночные операции модификации данных.
Используйте heap storage, если над таблицей будут проводиться параллельные операции модификации данных.
При создании heap таблиц размещайте столбцы в следующем порядке: ключи распределения и секционирования, типы с фиксированным размером от большего к меньшему, типы с переменным размером. Это позволит достичь выравнивания данных и улучшит производительность.
Используйте append-optimized, если данные в таблице обновляются нечасто и крупными пакетами.
Избегайте одиночных операций модификации данных над append-optimized таблицами.
Не проводите параллельные операции обновления или удаления данных над append-optimized таблицами. Параллельные операции пакетной вставки допустимы.

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

Row-oriented и Column-oriented

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

Сжатие

Greenplum предоставляет множество вариантов сжатия для append-optimized таблиц или секций. Сжатие позволяет улучшить ввод/вывод за счет увеличения количества данных, получаемых с каждой операцией чтения с диска.
При выборе метода сжатия существует следующие нюансы и хорошие практики:

Задавать сжатие следует на уровне секций. Учитывайте, добавленные секции не наследуют настройки сжатия таблицы – их нужно задавать отдельно.
Лучший уровень сжатия предоставляет алгоритм RLE. Увеличения уровня сжатия, как правило, выражается в более компактном хранении места на диске, однако требует дополнительных процессорных циклов для записи и чтения.
Алгоритм RLE лучше всего работает с файлами, содержащими повторяющиеся данные.
Сортировка данных и комбинирование различных параметров сжатия позволяют достичь наилучшего уровня производительности.
Никогда не используйте сжатие в базе, если данные хранятся в файловой системе со сжатием.
В целом, оптимальный алгоритм сжатия определяется методом проб. Тестируйте различные типы сжатия и упорядочивания данных, чтобы определить наилучший метод для ваших данных.

Распределение данных

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

Если есть возможность, следует распределять крупные объединяемые таблицы по одному ключу, что позволит достичь локального объединения. В таком случае данные соединяются на сегментах Greenplum независимо, что может существенно повысить производительность за счет отсутствия движения данных. Тут стоит еще раз поднять тему важности одинаковых типов данных для одинаковых полей в разных таблицах – одинаковые значения с разными типами данных могут хэшироваться по-разному, из-за чего распределения по одним сегментам не произойдет.

В случае неравномерного распределения возникает перекос в данных (Data Skew), зачастую являющийся причиной низкой производительности. Крайне важно проверять распределение после изменения данных в базе. Следующий запрос выведет максимальное и минимальное количество строк на сегментах для таблицы facts и процентное отклонение между этими значениями:


SELECT
    max(c) AS "Max Seg Rows", min(c) AS "Min Seg Rows", 
    (max(c)-min(c))*100.0/max(c) AS "Percentage Difference Between Max & Min" 
FROM (SELECT count(*) c, gp_segment_id FROM facts GROUP BY 2) AS a;

Помимо перекоса в данных, может возникать вычислительный перекос (processing skew). Вычислительный перекос возникает во время выполнения запроса, за счет чего его тяжелее отследить. Если при запросе не работают отдельные сегменты, проблема, возможно, в вычислительном перекосе. В первую очередь стоит обратить внимание на spill файлы – они создаются, когда Greenplum не хватает оперативной памяти для выполнения запроса. Поскольку скорость работы жесткого диска значительно ниже скорости оперативной памяти, это серьезно влияет на производительность.
Как правило, избавить от spill файлов помогает изменение запроса – например, использование временных таблиц.

В схеме gp_toolkit присутствуют два представления, помогающие отследить перекос:

В представлении gp_toolkit.gp_skew_coefficients в колонке skccoeff отображается коэффициент вариации для таблицы. Чем значение ниже, тем лучше.
В представлении gp_toolkit.gp_skew_idle_fractions в колонке siffraction отображается процент системы, который простаивает во время сканирования таблицы. К примеру, значение в 0.1 говорит о перекосе в 10%. Перекос более чем в 10% – это повод изменить распределение таблицы.

Существуют следующие рекомендации касающиеся распределения:

Явно задавайте принцип распределения. Распределение по умолчанию использует первую колонку в таблице, что редко является оптимальным.
Для распределения желательно использовать одну колонку.
Колонка распределения должна иметь либо уникальные значения, либо очень высокую кардинальность. Если в колонке много NULL-значений, не стоит использовать ее в качестве ключа распределения, поскольку все записи с таким значением будут определены на один сегмент.
Если не удается достигнуть равномерного распределения данных по одной колонке, следует попытаться использовать распределение по двум полям. Однако это редко помогает достичь более равномерного распределения и требует дополнительное время на хэширование.
Если ключ распределения из двух колонок не позволил достичь равномерного распределения данных, следует использовать случайное распределение. Распределение более чем по двум колонкам редко является более производительным, поскольку требует лишней пересылки данных между сегментами.
Группировка по ключу распределения будет происходить быстрее за счет отсутствия пересылки данных.
Не используйте в качестве ключей распределения поля, используемые при фильтрации.
Не стоит также выбирать даты как ключ распределения. Зачастую, данные выбираются за какой-то период, поэтому даты следует оставить как поле для секционирования.

Секционирование

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

При принятии решения о секционировании следует учитывать следующее:

Используйте секционирование только на больших таблицах. Для таблиц в несколько тысяч строк трудности поддержания секций перевешивают прирост в производительности.
Используйте секционирование только в том случае, если в запросах удается достичь partition elimination. В противном случае секционирование только навредит. Убедиться в достижении partition elimination можно использовав конструкцию EXPLAIN.
Никогда не секционируйте таблицу по ключу распределения.
Если есть возможность, отдавайте предпочтение range partitioning перед list partitioning.
Будьте аккуратны при создании секций на колоночных таблицах. Количество физических файлов для колоночных таблиц равно количеству сегментов умноженному на количество колонок умноженному на количество секций. В целом, хорошей практикой считается ограничить количество файлов на сегменте ста тысячами.
Постарайтесь, чтобы данные в секциях были распределены равномерно. Если данные распределены равномерно на 10 секций, то сканирование, попадающее в одну из секций, будет работать в 10 раз быстрее чем сканирование таблицы без секционирования.
Планировщик может выборочно сканировать секции только когда запрос содержит простое ограничение: =, <, <=, >, >=, <>
Выборочное сканирование работает с функциями с категориями изменчивости STABLE или IMMUTABLE, но не VOLATILE. Например, при условии date > CURRENT_DATE планировщик будет выборочно сканировать секции, а при time > TIMEOFDAY – нет.
Постарайтесь не использовать многоуровневое секционирование. Усложнение административных задач, как правило, перевесит прирост в производительности. Для производительности, масштабируемости и удобства сопровождения нужно найти баланс между скоростью сканирования и количеством секций.

Индексы

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

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

Индексы могут улучшить работу запросов с высокой селективностью. Например, можно создать индекс на одной колонке в колоночной таблице, если эта колонка используется в drill through запросах.
Не создавайте индексы на колонках, которые часто обновляются.
Удаляйте индексы перед загрузкой данных.
Для столбцов с высокой селективностью выбирайте B-tree индекс.
Используйте bitmap индексы на колонках с небольшой кардинальностью – от 100 до 100000 уникальных значений.
Не создавайте bitmap индексы на обновляемых колонках.
В целом, не стоит использовать индексы на секционированных таблицах. Если такая потребность есть, поле секционирования должно отличать от поля индексации.

Оптимизатор и план запроса

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

https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-config_params-guc_config.html

В Greenplum используется cost-based optimizer – при построении плана запроса оптимизатор рассчитывает стоимость каждого из действий и выбирает наименее затратный метод. Стоимость в 1.0 равна одному последовательному прочтению страницы с диска.

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

https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/admin_guide-query-topics-query-piv-opt-notes.html

За выбор оптимизатора отвечает параметр enable_optimizer. При значении ON, будет использоваться GPORCA.

Конструкции EXPLAIN и EXPLAIN ANALYZE позволяют просмотреть план запроса, что дает возможность оценить, как сильно каждое из действий влияет на производительность, и понять, как улучшить скорость работы.

EXPLAIN выводит приблизительный план, без выполнения запроса. EXPLAIN ANALYZE выполняет запрос и выводит действительный план с дополнительной информацией, такой как:

Общее время (total elapsed time) в миллисекундах
Количество сегментов, задействованных в выполнении
Количество строк, возвращенных сегментом, который произвел наибольшее количество строк при выполнении операции вместе с ID этого сегмента
Использованная при операции память
Время, затраченное на то, чтобы получить первую строку и общее время, затраченное на получение всех строк для сегмента, который произвел больше всего строк

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

При необходимости просмотра плана с помощью EXPLAIN ANALYZE на запросах, модифицирующих данные (INSERT, UPDATE, DELETE), стоит использовать транзакции (BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;) для избежания влияния на данные.

План представляет собой древовидную структуру, в узлах которой указываются операции. Чтение плана производится снизу вверх – каждый узел передает результат узлу, расположенному прямо над ним. Стоимость и время в узле рассчитывается с включением всех дочерних узлов. То есть, значения самого верхнего узла, как правило gather motion, и является значениями всего запроса.
В рамках данной статьи не будем вдаваться в подробности чтения плана запроса, лишь отметим моменты, на которые стоит обратить внимание.

Как уже отмечалось в разделе про секционирование, проверяйте partition elimination. Для достижения partition elimination необходимо, чтобы в предикате запроса было условие по полю секционирования. Так же, предикат не должен содержать подзапросы или VOLATILE функции.
Оцените порядок выполнения операций. Желательно, чтобы наиболее крупные таблицы возникали как можно выше в плане выполнения, чтобы минимизировать количество строк, передаваемых от узла к узлу. Если в ходе анализа плана выявлено, что порядок выполнения не оптимален, убедитесь в актуальности статистики таблицы. Запуск ANALYZE, вероятно, поможет в генерации более удачного плана.
spill так же отображается в плане запроса. В таком случае slice, при выполнении которого сгенерировались спилл-файлы, будет отмечен звездочкой *, а также будет указан размер спилл-файлов. Следует понимать, что при тяжелых запросах, от спилл-файлов никуда не деться, однако их наличие может быть признаком неоптимального запроса.
Обратите внимание на узлы с операциями Sort или Aggregate. Если данные операции выполняются над большим количеством строк, то есть возможность улучшить производительность изменив запрос. HashAggregate лучше подходит для большого количества строк и большинство операций Sort может быть заменено на HashAggregate, если переписать запрос. Чтобы планировщик отдавал предпочтение HashAggregate, параметр конфигурации enable_groupagg должен быть установлен в ON.
Если в плане запроса замечена операция broadcast motion над большим количеством строк, то следует предпринять попытку ее устранить. Одним из способов является изменение параметра gp_segments_for_planner. Данный параметр указывает для какого количества сегментов следует строить планы выполнение, и с ростом данного значение растет и стоимость операций движения. При увеличении данного значения планировщик чаще будет выбирать redistribute motion вместо broadcast motion.

Поддержка базы

Статистика

Как упоминалось в предыдущем пункте, в Greenplum используется cost-based оптимизатор, и для того, чтобы оптимизатор был в состоянии адекватно оценивать стоимость операций и строить оптимальные планы запросов ему необходима актуальная статистика таблиц. Своевременная статистика, в целом, является важнейшим из предварительных требований для достижения хорошей производительности. Для сбора статистики существуют конструкция ANALYZE и утилита analyzedb.

Важно найти компромисс между точностью статистики и временем, затрачиваемым на анализ.
ANALYZE не просматривает всю таблицу – он производит случайную выборку значений. За количество этих значений отвечает параметр default_statistics_target, который может принимать значения от 1 до 1000; значением по умолчанию является 100. Увеличение данного параметра может улучшить качество планов, особенно для колонок с нерегулярными закономерностями в данных.

Приведем рекомендации по сбору статистики:

Если необходимо обновить статистику для множества таблиц, используйте analyzedb. analyzedb обновляет статистику инкрементно и параллельно. Для append-optimized таблиц статистика обновляется только если данные в таблицах были изменены. Для heap таблиц статистика обновляется всегда.
Всегда используйте ANALYZE после команд INSERT, UPDATE, DELETE, значительно изменивших данные в таблице.
Всегда собирайте статистику после создания индексов.
Если сбор статистики для большой таблицы занимает слишком много времени, используйте ANALYZE только на колонках, используемых при объединении, фильтрации, сортировке или группировке. Также при изменении данных в одной из секции достаточно использовать ANALYZE на этой секции.
После добавления секции используйте analyzedb на корневой секции.

Bloat

Раздувание базы данных (database bloat) – это свободное место на диске, которое не было восстановлено для переиспользования. Для устранения bloat существует конструкция VACUUM.

Для heap таблиц после изменения данных строка помечается как устаревший в карте видимости, а VACUUM помечает устаревшие строки как свободное пространство и делает их доступными для перезаписи. Для часто обновляемых таблиц нормально иметь небольшое количество устаревших строк, однако если допустить значительное раздувание таблицы, то это замедлит ввод/вывод и повлияет на скорость запросов.

Крайне важно периодически использовать VACUUM, особенно после операций UPDATE и DELETE над большим количеством строк, чтобы избежать необходимости использования VACUUM FULL.

Если количество устаревших строк становится настолько большим, что места в карте видимости становится недостаточно, чтобы их отследить, нужно использовать VACUUM FULL. VACUUM FULL накладывает ACCESS EXLUSIVE блокировку, создает новую таблицу, копирует данные и удаляет старую таблицу. Это довольно затратная операция, и для больших таблиц она может занять много времени.

Как говорилось ранее, физически записать новые данные в append-optimized таблицу невозможно. Поэтому VACUUM перезаписывает таблицу в новые файлы без устаревших строк; операция аналогична VACUUM FULL над heap таблицами.
Однако перезапись происходит только тогда, когда процент устаревших строк превышает количество, установленное в параметре gp_appendonly_compaction_threshold, по умолчанию равное 10%. Таким образом, VACUUM может сжать таблицы лишь на части сегментов.
Трудоемкое переиспользование пространства одна из причин, по которой UPDATE и DELETE не рекомендуется использовать над append-only таблицами.

Подведем итог по восстановлению места на дисках:

Используйте VACUUM после UPDATE или DELETE над большим количеством строк.
Вместо VACUUM FULL используйте CREATE TABLE … AS с последующим удалением оригинальной таблицы.
Регулярно используйте VACUUM над системными таблицами, поскольку они используются в большинстве операций и их раздувание может привести к деградации производительности всей базы. Если же дошло до необходимости использования VACUUM FULL, необходимо сначала остановить всю активность в базе. По окончании, соберите статистику системных таблиц.
Никогда не посылайте команду kill процессу VACUUM над системными таблицами.
Для восстановления места, используемого индексами, используйте REINDEX.

Вернуться к блогу

Лучшие практики дизайна схем, отладки запросови поддержки БД в Greenplum

Лучшие практики дизайна схем, отладки запросов
и поддержки БД в Greenplum