Всем привет!

В прошлой статье мы с вами разобрались, как устроена MPP-архитектура Greenplum.  Сегодня мы в сотрудничестве с @imzorin углубимся и разберемся, что представляет из себя DDL в этом хранилище. Также постараемся выделить основные моменты, на которые стоит обращать внимание при выборе типа таблиц, дистрибуции и прочего.

Виды таблиц

В Greenplum существует два основных вида таблиц: heap и AOT (Append-Optimized Table).

Если вы ранее работали с Postgres, то будет несложно понять, что такое heap-таблицы, так как они представляют из себя стандартную физическую структуру из Postgres: на каждом сегменте Greenplum записи таблицы складываются последовательно в один файл.

Этот вид таблиц выбирается, если таблица небольшая и в будущем придется много работать с данными (удалять и/или изменять). К примеру, это предпочтительный выбор для таблиц-словарей.

Для создания heap-таблицы необходимо в DDL прописать параметр APPENDONLY равный false. Например:

сreate table t1 (
  col1 integer,
  col2 varchar(100) 
) with (APPENDONLY=false) 
distributed by (col1);

В примере вы видите строку distributed by (col1). Таким образом мы задаём дистрибьюцию, о которой поговорим позже.

Append-optimized tables (AOT) – таблицы со специальной структурой, обладающие рядом особых возможностей. Например, для этого вида таблиц доступна любая ориентация данных, а также все виды компрессии. Данный тип таблиц использует меньше ресурсов, по ним быстрее проходит сбор статистики. 

У этого типа таблиц сложная внутренняя структура: таблицы содержат ссылки на файлы, в которых хранятся большие блоки данных. У каждой таблицы есть внутренний индекс, обеспечивающий её работу. Это индекс строится по системным полям, и он не используется в запросах. Каждая APPEND-OPTIMIZED-таблица создаёт несколько системных объектов
в Greenplum и несколько файлов на диске.

AOT-таблицы рекомендуется создавать, если:

• Таблица будет содержать много данных 

• Таблица состоит из множества столбцов

• Агрегация выполняется только над небольшим количеством столбцов

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

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

Для создания AOT-таблицы необходимо в DDL прописать параметр APPENDONLY равный true. Например:

сreate table t1 ( 
  col1 integer,
  col2 varchar(100) 
) with (APPENDONLY=true) 
distributed by (col1);

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

Ориентация данных у таблиц в GP бывает строковая и колончатая.

Строковую структуру (row) рекомендуется использовать для небольших таблиц, в которых часто изменяются данные (то есть для heap). Или же, если многие поля AOT таблицы часто используются. Связано это с тем, что выгрузки с такой ориентацией данных делаются куда эффективнее за счет сохранения данных таблицы на сегменте в одном файле.

Однако к таким таблицам нельзя применять компрессию RLE_TYPE (о компрессии подробнее далее).

Колончатая структура (column) применима только (!) к AOT. Данные каждой колонки хранятся в отдельном файле, что замедляет работу системы, однако к таким данным можно выбирать любой вид компрессии, который будет работать эффективнее. Выбирать именно этот вид ориентации данных рекомендуется, если:

• В запросах используется лишь некоторая часть полей таблицы

• В запросах часто используется агрегация

• Большая таблица, которую нужно хорошо сжать

Ориентация данных задаётся на этапе создания DDL:

сreate table t1 (
  col1 integer,
  col2 varchar(100) 
) with (appendonly=true, ORIENTATION = [row/column]) 
distributed by (col1);

Компрессия

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

В Greenplum существует несколько видов компрессии:

При выборе компрессии надо помнить, что алгоритмы ZLIB и ZSTD доступны как для строковой ориентации таблиц, так и для колоночной. Алгоритм RLE_TYPE в свою очередь доступен только для колоночной ориентации таблиц.

При выборе типа и уровня сжатия для таблиц, учитывайте следующие факторы:

• Загрузка CPU. Для применения компрессии всегда должна быть доступная мощность CPU.

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

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

В нашей практике, исходя из вводных от различных Заказчиков, самый популярный выбор сжатия - это ZSTD с уровнем сжатия 1-5.

Рассмотрим два примера создания компрессии:

1. Компрессия ко всей таблице:

сreate table t1 ( 
  col1 integer, 
  col2 varchar(100) 
) with (appendonly=true, orientation = column,
COMPRESSTYPE=[quicklz/zlib/rle_type/zstd], COMPRESSLEVEL=1) 
distributed by (col1);

2. Компрессия определенного столбца:

сreate table t1 ( 
  col1 integer ENCODING (COMPRESSTYPE=quicklz),
  col2 varchar(100)     
) with (appendonly=true, orientation = column) 
distributed by (col1);

Подведем итог в сравнительной таблице двух видов таблиц:

Дистрибьюция

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

Если смотреть глобально, то цель дистрибьюции это:

•  Равномерное распределение данных

•  Локальность операций

Факторы, влияющие на равномерное распределение данных:

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

•  В ключе не должно быть null.

•  В ключе не должно быть значений по умолчанию.

Факторы, влияющие на локальность:

•  Использование ключей в join, group by, partition. Одинаковый ключ в разных таблицах - залог быстродействия при их взаимодействии.

•  Количество полей в ключе. Если ключ состоит из нескольких полей, то локальные операции по таблице будут при участии всех полей, входящих в ключ, и ровно в таком же их порядке. Также очевидно, что хэширование множества полей будет выполняться дольше, нежели хэширование одного поля. То есть для Greenplum distributed by (col1, col2) ≠ distributed by (col2, col1)

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

Дистрибьюция данных делится на 3 основных вида:

•  DISTRIBUTED BY. Самый часто используемый вид дистрибьюции (можно даже сказать, что другие два скорее являются исключениями). Чтобы корректно распределить данные по первичным сегментам, для выбранных полей формируется хэш-значения, на основе которых и будет проходить распределение. 

сreate table t1 ( 
  col1 integer, 
  col2 varchar(100) 
) with (appendonly=false) 
distributed by (col1);

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

сreate table t1 ( 
  col1 integer,  
  col2 varchar(100) 
) with (appendonly=false) 
distributed replicated;

•  DISTRIBUTED RANDOMLY. Как понятно из названия, это – приблизительно равномерное распределение данных между сегментами. Greenplum использует RR-алгоритм для данного вида дистрибьюции. Что касается практики, данное распределение используется, если не подходит ни одно из двух предыдущих. 

сreate table t1 ( 
  col1 integer, 
  col2 varchar(100) 
) with (appendonly=false) 
distributed randomly;

Чтобы проверить дистрибьюцию существующей таблицы, то это можно сделать запрос: select pg_get_table_distributedby(‘schema.my_table'::regclass::oid);

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

•  Предпочтительнее для ключа дистрибьюции использовать поля типа Integer – так как хэш для них вычисляется гораздо быстрее.

•  Поля, которые могут быть потенциальным фильтром (то есть которые в перспективе часто будут встречаться в where) – не стоит выбирать в качестве ключа распределения. Иначе при фильтрации по ключу дистрибьюции будут участвовать только часть сегментов.

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

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

Партиционирование – разделение таблицы на отдельные логические части. В целом функционал партиционирования остается в Greenplum таким же, как и в Postgre. Однако в GP можно создавать партиции большего размера, чем в других видах БД, за счет MPP архитектуры.

Первый вид партиционирования - by range, можно использовать для периодов (чаще всего для временных):

create table t1 (  
  col1 integer, 
  col2 varchar(100),  
processed_dt date
) 
distributed by (col1) 
PARTITION BY RANGE(processed_dt) 
( 
  PARTITION p1 START('2020-01-01'::DATE) END('2029-12-31'::DATE) 
  EVERY('1       month'::INTERVAL),
  DEFAULT PARTITION EXTRA
);

Выше мы создали партицию p1 по полю processed_dt с 1 января 2020 года до 31 декабря 2029 года с интервалом в 1 месяц. При отсутствии значений в данном интервале, данные будут попадать в партицию extra.

Второй вид партиционирования – партиционирование по значениям (by list).

create table t1 (  
  col1 integer,  
  col2 varchar(100) 
)
distributed by (col1) 
PARTITION BY LIST(col2) 
(   
  PARTITION p1 VALUES('A'), 
  PARTITION p2 VALUES('B'), 
  DEFAULT PARTITION OTHERS   
);

Третий вид партиционирования используется для хэш-значений какого-либо поля (применимо для версии Greenplum 7+):

create table t1 ( 
  col1 integer, 
  col2 varchar(100), 
processed_dt date 
)
distributed by (col1)
PARTITION BY HASH(col1);

Партиции также можно добавлять и удалять:

ALTER TABLE t1 ADD PARTITION p2 START('2030-01-01'::DATE) END('2039-12-31'::DATE);

ALTER TABLE t1 DROP PARTITION FOR ('A');

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

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

CREATE TABLE sales ( 
  trans_id int,  
  date date, 
  amount decimal(9,2),
  region text) 
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date) 
SUBPARTITION BY LIST (region)  
SUBPARTITION TEMPLATE (SUBPARTITION usa VALUES ('usa'),
            SUBPARTITION  asia VALUES ('asia'), 
            SUBPARTITION europe VALUES ('europe'),
            DEFAULT SUBPARTITION other_regions)
(START (date '2011-01-01') INCLUSIVE END (date '2012-01-01')
EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates);

CREATE TABLE sales (  
  trans_id int,  
  date date,  
  amount decimal(9,2),   
  region text) 
DISTRIBUTED BY (trans_id) 
PARTITION BY RANGE (date) 
SUBPARTITION BY LIST (region) 
SUBPARTITION TEMPLATE       
(SUBPARTITION usa VALUES ('usa'), 
  SUBPARTITION asia VALUES ('asia'), 
  SUBPARTITION europe VALUES ('europe'), 
  DEFAULT SUBPARTITION other_regions)    
(START (date '2011-01-01') INCLUSIVE END (date '2012-01-01') 
EXCLUSIVE EVERY (INTERVAL '1 month'),
DEFAULT PARTITION outlying_dates);

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

Посмотреть партиции можно в системной таблице pg_partition

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

Для ответа на вопрос, стоит ли делать партиции для таблицы, просмотрите чек-лист ниже.

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

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

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

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

Заключение

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

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