Всем привет!
В прошлой статье мы с вами разобрались, как устроена MPP-архитектура Greenplum. Сегодня мы в сотрудничестве с @imzorin углубимся и разберемся, что представляет из себя DDL в этом хранилище. Также постараемся выделить основные моменты, на которые стоит обращать внимание при выборе типа таблиц, дистрибуции и прочего.
Виды таблиц
В Greenplum существует два основных вида таблиц: heap и AOT (Append-Optimized Table).
![](https://habrastorage.org/getpro/habr/upload_files/aad/57d/429/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 и несколько файлов на диске.
![](https://habrastorage.org/getpro/habr/upload_files/567/d35/ae4/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 таблицы часто используются. Связано это с тем, что выгрузки с такой ориентацией данных делаются куда эффективнее за счет сохранения данных таблицы на сегменте в одном файле.
![](https://habrastorage.org/getpro/habr/upload_files/677/264/632/677264632d50b7d89e76f18b199d16ff.png)
Однако к таким таблицам нельзя применять компрессию RLE_TYPE (о компрессии подробнее далее).
Колончатая структура (column) применима только (!) к AOT. Данные каждой колонки хранятся в отдельном файле, что замедляет работу системы, однако к таким данным можно выбирать любой вид компрессии, который будет работать эффективнее. Выбирать именно этот вид ориентации данных рекомендуется, если:
В запросах используется лишь некоторая часть полей таблицы
В запросах часто используется агрегация
Большая таблица, которую нужно хорошо сжать
![](https://habrastorage.org/getpro/habr/upload_files/674/384/d88/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 |
Хранение файлов |
Записи в одном файле |
Содержит ссылки на файлы с записями |
Компрессия |
Не поддерживается |
ZSTD, ZLIB - для Row-таблиц |
Особенность таблицы |
Небольшие изменчивые таблицы |
Большие таблицы, в которых меняются только некоторые поля |
Дистрибьюция
Дистрибьюция – распределение данных по сегментам 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
Поле |
Тип данных |
Описание |
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 немного больше. Мы смогли выделить некоторые правила, которые стоит учитывать при выборе дистрибьюции, ориентации данных, вспомнили что такое партиционирование и можем двигаться дальше.
Следующая статья будет заключительной и в ней мы разберемся, что нам поможет оптимизировать запросы, как мониторить и предвосхищать проблемы.