Цикл статей о Greenplum. Часть 2. Оптимальный DDL

Всем привет!

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

Виды таблиц

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

aad57d429ea4cb12e18a6986e340374c.png


Если вы ранее работали с 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 и несколько файлов на диске.

567d35ae4a81a2a6f0f2619d6ccb3627.png

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

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

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

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

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

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

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

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

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

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

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

677264632d50b7d89e76f18b199d16ff.png

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

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

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

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

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

674384d88ccd6a1fc28f2f9ad4b2b6af.png

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

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

Компрессия

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

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

Вид

Уровень сжатия

Описание

QuickLZ

1

Быстрая работа и низкая утилизация CPU, однако низкий уровень сжатия.

ZLIB

1–9

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

RLE_TYPE

1–4

Сжатие с кодированием длин серий (Run-length encoding) считается наиболее эффективным с точки зрения компактного хранения данных на диске. Высокие уровни компактности реализуются за счет дополнительного времени и циклов ЦП на сжатие данных при записи и распаковку при чтении. RLE отлично подходит для файлов с повторяющимися данными.

ZSTD

1–19

Оптимальный вариант компрессии: быстрая работа и сильный уровень сжатия

При выборе компрессии надо помнить, что алгоритмы 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);

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

Heap

AOT

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

Row

Row, column

Хранение файлов

Записи в одном файле

Содержит ссылки на файлы с записями

Компрессия

Все типы, кроме RLE_TYPE. Применимо только ко всей таблице

Любой тип компрессии как для всей таблицы, так и для отдельных столбцов

Особенность таблицы

Небольшие изменчивые таблицы

Большие таблицы, в которых меняются только некоторые поля

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

Дистрибьюция — распределение данных по сегментам 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   
);

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

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

Поле

Тип данных

Описание

parrelid

oid

Идентификатор объекта таблицы

parkind

char

Тип патриции:

•  R — range партиция

•  L — list партиция

parlevel

smallint

Уровень раздела этой строки:

•  0 для родительской таблицы верхнего уровня,

•  каждый последующий уровень имеет порядковый номер — натуральное число (1, 2, …)

paristemplate

boolean

Представляет ли эта строка определение шаблона подраздела (true) или фактический уровень разделения (false).

parnatts

smallint

Количество атрибутов, определяющих этот уровень.

paratts

smallint

Массив номеров атрибутов, участвующих в определении этого уровня.

parclass

oidvector

Идентификатор (ы) классов операторов столбцов раздела.

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

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

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

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

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

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

Заключение.

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

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

© Habrahabr.ru