Приветствуем вас на заключительном этапе в цикле статей о Greenplum. Ранее мы уже обсудили то, как выглядит архитектура системы. Посмотрели «под капот», подробнее обсудили виды хостов и их предназначение, узнали, как обрабатываются запросы пользователей.

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

Освежить память о содержании предыдущих статей можно здесь и здесь.

В данной статье мы совместно с @omoskvinрасскажем о том, что влияет на оптимальность выполнения запросов, как отслеживать различные проблемы и, конечно же, как с ними справляться.

Перекос данных

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

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

Перекос можно встретить не только в данных, но и в обработке (так называемый processing skew). Такое происходит, когда непропорциональный объем данных поступает в один или несколько сегментов и обрабатывается ими. Перекос в обработке не так легко обнаружить, как в данных. Если выходят из строя отдельные сегменты (но не все на хосте), это может быть признаком перекоса обработки.

Чтобы проверить наличие перекоса данных, можно выполнить запрос:

SELECT gp_segment_id, count(1) AS count_1
FROM <scheme_name.table_name> 
GROUP BY gp_segment_id;

В таблицах, кроме тех, где присутствует дистрибьюция replicated, существует техническое поле gp_segment_id. Если вы выполните запрос select *, то данное поле не будет выведено. Однако к нему можно обратиться напрямую (как в предложенном запросе выше), чтобы узнать, на каком сегменте находятся данные.

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

• gp_toolkit.gp_skew_coefficients показывает перекос распределения данных путем расчета коэффициента вариации (CV) для данных, хранящихся в каждом сегменте. В столбце skccoeff показан коэффициент вариации, который рассчитывается как стандартное отклонение, разделенное на среднее значение. Он учитывает как среднее значение, так и изменчивость среднего значения ряда данных. Чем ниже значение, тем лучше. Более высокие значения указывают на большую асимметрию данных.

• gp_toolkit.gp_skew_idle_fractions показывает перекос в распределении данных путем расчета процента системы, которая простаивает во время сканирования таблицы, что является индикатором перекоса вычислений. Столбец siffraction показывает процент системы, которая простаивает во время сканирования таблицы. Это индикатор неравномерного распределения данных или неравномерности обработки запросов. Например, значение 0,1 указывает на асимметрию 10 %, значение 0,5 — на 50 % и т. д. Для таблиц с асимметрией более 10 % следует оценить политику распределения.

Статистика

Как и во многих других СУБД, для увеличения скорости выполнения запросов, собирается статистика. Статистика — это сведения о демографии данных. То есть сведения о распределении значений в столбцах. В Greenplum на основе статистики оптимизатор выбирает, какой именно план запроса реализовать. Статистика может собираться как по таблице в целом, так и по отдельным столбцам или партициям в частности. Для сбора статистики СУБД использует либо команду analyze, либо утилиту analyzedb. При первоначальной вставке данных в пустые таблицы без партиций статистика собирается автоматически.

Уже по традиции системных таблиц в GP существует view с информацией о наличии статистики через gp_toolkit.gp_stats_missing

Поле	Описание
smischema	Имя схемы
smitable	Имя таблицы
smisize	Наличие в этой таблице статистики (t or f)
smicols	Количество столбцов в таблице
smirecs	Общее количество столбцов в таблице, для которых собрана статистика.

Подробнее рассмотрим аnalyzedb, утилиту для быстрого сбора статистики. Она исполняется на сервере Greenplum. Основным преимуществом утилиты является инкрементальный сбор статистики для AOT таблиц, а также параллельный анализ партиций (до 10). Однако, при наличии более 1000 партиций, Greenplum может перестать выполнять инкрементальную анализ, что приводит к ошибкам или замедлению работы. Это одна из причин, почему не стоит делать много лишних партиций. Для heap таблиц инкрементальный сбор не работает вовсе, сканируется вся таблица.

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

Статистику можно собирать и в автоматическом режиме. За это отвечает администратор БД, для расширения кругозора рассмотрим, как это делается. Параметр конфигурации gp_autostats_mode вместе с параметром gp_autostats_on_change_threshold определяет, когда запускается операция автоматического анализа. При запуске автоматического сбора статистики планировщик добавляет в запрос шаг analyze.

По умолчанию gp_autostats_mode имеет значение on_no_stats, что запускает сбор статистики для операций CREATE TABLE AS SELECT, INSERT или COPY, вызываемых владельцем таблицы для любой таблицы, у которой нет существующей статистики.

Установка gp_autostats_mode в режим on_change запускает сбор статистики только тогда, когда количество затронутых строк превышает порог, определенный gp_autostats_on_change_threshold, который имеет значение по умолчанию 2 147 483 647. Следующие операции, вызываемые над таблицей ее владельцем, могут инициировать автоматический сбор статистики с помощью on_change: CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT и COPY.

Установка gp_autostats_mode в значение none отключает автоматический сбор статистики.

Статистику следует собирать:

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

Оптимизатор

Немного освежим в памяти первую статью из цикла. После того, как запрос от клиента попадает на мастер и проходит парсер, он попадает в оптимизатор. В Greenplum существуют два оптимизатора Postgres Planner (legacy optimizer) и Pivotal Optimizer (GPORCA). Верхнеуровнево они работают одинаково, однако, копнув глубже, можно обнаружить нюансы при выборе оптимизатора, которые сильно влияют на производительность.

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

Postgres Planner используется только для запросов к таблицам на мастере. Он может использовать весь функционал GP, но строить не самые оптимальные планы запроса.

Управлять оптимизаторами можно с помощью специальных команд:

• Посмотреть текущий оптимизатор: show optimizer;
• Включить GPORCA: set optimizer = on;
• Включить Postgres planner: set optimizer = off;

Алгоритм при выборе того или иного оптимизатора достаточно простой. Если вы хотите обратиться только к системным таблицам и, например, ознакомиться с конфигурацией кластера, то используйте Postgres planner. Также Postgre будет оптимальнее работать с простыми запросами. В противном случае лучшим выбором будет GPORCA.

План запроса

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

Для построения плана запроса используется ключевое слово explain. Explain делает прогноз на основе существующей статистки.

Команда Explain Analyze строит план запроса с реальным количеством строк и потребляемыми ресурсами. Точность обеспечивает то, что запрос сначала выполняется.

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

Также для каждого slice при выполнении запроса задействован свой QE:

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

Пример использования и вывода explain:

Для удобства вывод также можно сделать в формате JSON файла, используя запрос:

EXPLAIN (FORMAT JSON) SELECT * FROM <table_name>;

Джоины

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

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

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

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

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

Motion

В плане запроса также видно, что помимо обычных операций с базой данных, в Greenplum имеется дополнительный тип операций, называемый motion. Проще говоря, это обмен данных между сегментами. Делится motion на несколько видов.

• Redistribute Motion работает, если соединение таблиц идет не по ключам дистрибьюции. Тогда система автоматически перераспределяет данные по другому ключу дистрибьюции. Этот вид motion выполняется перед операциями агрегации или джоинами, что позволяет добиться локальности выполнения операций.

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

• Gather Motion объединяет на мастере результаты с сегментов. Он присутствует в каждом запросе и является финальным шагом плана.

MVCC + bloat

С производительностью запросов разобрались. Теперь давайте рассмотрим, что делать, если память GP на дисках сильно забивается.

В СУБД для поддержания параллельного транзакционного доступа для таблиц используется MVCC (Multiversion Concurrency Control) модель. Каждый запрос работает с выборкой данных, актуальной на момент старта транзакции. Это позволяет осуществлять чтение и запись без блокировок.

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

• XMIN — идентификатор транзакции, которая добавила запись в таблицу

• XMAX — идентификатор транзакции, которая удалила запись из таблицы

• XID — уникальный transaction ID каждой транзакции. XID представляет собой увеличивающееся 32-битное значение, максимально достигающее 4.294.967.295. Значения XID возобновляются с 3 после достижения максимума.

Рассмотрим некоторые операции на примере таблицы t1:

select xmin, xmax, col1, col2 from t1;

XMIN	XMAX	col1	col2
101	0	1	A
102	0	2	A
103	0	3	A
104	0	4	B

Изменим значение поля col1 для последней строки:

update t1 set col1 = 5 where col2 = 'B';

Включим режим видимости строк и обратимся к таблице ещё раз:

set gp_select_invisible=on; 
select xmin, xmax, col1, col2 from t1;

XMIN	XMAX	col1	col2
101	0	1	A
102	0	2	A
103	0	3	A
104	105	4	B
105	0	5	B

Добавим новую строку в таблицу:

insert into t1 values(4, 'C');
set gp_select_invisible=on;
select xmin, xmax, col1, col2 from t1;

XMIN	XMAX	col1	col2
101	0	1	A
102	0	2	A
103	0	3	A
104	105	4	B
105	0	5	B
106	0	4	C

А теперь удалим строку со значением 3 в столбце col1.

delete from t1 where col1 = 3;
set gp_select_invisible=on;
select xmin, xmax, col1, col2 from t1;

XMIN	XMAX	col1	col2
101	0	1	A
102	0	2	A
103	107	3	A
104	105	4	B
105	0	5	B
106	0	4	C

При выполнении команды DELETE FROM table1; все строки останутся в файле данных, но будут помечены как невидимые для операций SELECT. Команда UPDATE помечает старую версию строки как неактуальную и записывает в файл данных новую актуальную версию. Таким образом, при выполнении DELETE и UPDATE размер файла на диске не уменьшается, занятое место не освобождается.

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

SELECT * FROM gp_toolkit.gp_bloat_diag;

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

Для предотвращения раздуваний необходимо своевременно запускать операцию vacuum, которая работает для каждого вида таблиц по-своему:

• для heap таблиц vacuum помечает блоки данных как готовые к перезаписи, но физически место на диске не освобождает.

• для AOT таблиц vacuum перезаписывает файлы с данными в новый файл (уже без неактуальных блоков). Также существует специальный параметр gp_appendonly_compaction_threshold, который показывает лимит "удалённых" кортежей, после достижения которого vacuum начнёт работать.

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

Помимо операции vacuum, существует также команда vacuum full. При её вызове место на диске освобождается, ведь данные переписываются. Эта операция является более ресурсоемкой и не позволяет производить никаких параллельных операций с таблицей (Access Exclusive блокировка). Поэтому прибегать к ней следует в периоды низкой нагрузки БД.

Также стоит учитывать, что никогда нельзя прерывать работу vacuum full. Это может оставить систему в несогласованном состоянии, что влечет за собой болезненные последствия.

При работе с AOT таблицами vacuum full работает как обычный vacuum с gp_appendonly_compaction_threshold=0.

Spill файлы

Также за «захламление» памяти на диске отвечает недостаток оперативной памяти. Оперативная память отвечает за быстроту вычисления, однако количество такой памяти ограничено и всё, что не помещается, отправляется на диск в качестве spill файлов. Помимо этого, в подобные файлы записываются и данные, которые отправляются на редистрибьюцию (выполняется Redistribute Motion). Также к основным причинам образования spill файлов можно отнести запросы, в которых выполняется hash join, hashaggregate и операторы, использующие результаты работы Sort (например, merge join, windowagg и т.д.). Происходит это из-за того, что при работе этих операций в памяти накапливаются промежуточные данные.

Копить такие файлы — плохая идея, ведь они увеличивают время выполнения запросов. Однако это хороший показатель неоптимальности запроса.
На практике зачастую не удаётся совсем избавиться от spill файлов, поэтому небольшое их количество считается допустимым. Некоторые запросы и вовсе могут создавать spill файлы, даже когда количество оперативной памяти для выполнения более, чем достаточно.

Мониторинг за spill файлами можно осуществлять через:

• gp_toolkit.gp_workfile_usage_per_query — информация о spill в разрезе запроса и сегмента. Это самая полезная таблица для отслеживания собственных запросов. Обратить внимание следует на поля size (размер spill файла в байтах) и numfiles (идентификатор файла).
• gp_toolkit.gp_workfile_entries — информация о spill в разрезе слайса запроса и сегмента
• gp_toolkit.gp_workfile_usage_per_segment — информация о spill в разрезе сегмента

Также отследить факт формирования spill файлов можно и в плане запроса: перед названием слайса появляется знак *:

Чтобы избежать формирования spill файлов, необходимо понимать причины их возникновения ещё глубже.

Самая очевидная причина — большое количество лишних данных. Это легко можно поправить использованием фильтров. Самой распространенной ошибкой является игнорирование актуальности данных. Зачастую в базах встречается флаг актуальности или пользователь может использовать поля с датами для фильтрации нужных строк. Для таблиц с историчностью последний способ — must have.

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

Также к топу популярных ошибок можно отнести использование сортировки. Конечно, конструкция order by не является ошибкой, но работает в Greenplum, мягко говоря, медленно. Происходит это из-за того, что промежуточные результаты сортировки система также хранит сначала в оперативной памяти, а при нехватке места начинает формировать spill файлы. Здесь также сохраняется правило “чем больше данных, тем больше spill файлов”. В оконных функциях зачастую, увы, никуда не деться от использования order by, но при необходимости сортировки результата запроса в целом, лучше пользоваться встроенными в интерфейс функциями, а не прописывать это в запросе.

Заключение

Мы разобрали основные моменты, которые помогут вам следить за оптимальной работой GP. Выяснили, что, к примеру, неправильно подобранный на этапе создания таблицы ключ дистрибьюции, может в будущем стать причиной работы redistributed motion, который в свою очередь породит большое количество spill файлов. Хотя, казалось бы, связь между этими частями функционала не столь велика.

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

Вот и подошёл к концу наш цикл статей. За это время мы познакомились с отличным игроком на рынке MPP-СУБД — Greenplum.

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

Цикл статей о Greenplum. Часть 3. Оптимизация