GreenPlum. Обзорная информация

Введение

Симметричная мультипроцессорная архитектура (SMP)

Особенность:

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

Яркие представители: Oracle, Sybase, PostgreSQL

Преимущества:

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

Недостатки:

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

Архитектура массивно-параллельной обработки данных (MPP)

Особенность:

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

Недостатки:

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

Greenplum - реляционная СУБД с архитектурой MPP без разделения ресурсов. Он предназначен для хранения и обработки больших объемов данных методом распределения данных и обработки запросов на нескольких серверах. Лучше всего подходит для построения корпоративных хранилищ данных, решения аналитических задач и задач машинного обучения и искусственного интеллекта. В основе лежит PostgreSQL. По сути, это множество PostgreSQL работающих как одна СУБД.

Начало 2005, потом ядро на github в 2015, и пошло поехало

Архитектура СУБД Greenplum

Мастер

Входная точка для СУБД. Экземпляр БД PostgreSQL, к которому клиенты подключаются и отправляют запросы. Мастер координирует работу с другими экземплярами системы, которые называются сегменты. На мастере располагается Глобальный Системный Каталог - метаданные обо всех объектах. Мастер не хранит никаких пользовательских данных. Все пользовательские данные хранятся на сегментах.

Функции мастера:

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

Сегменты

Отдельные экземпляры БД PostgreSQL, которые хранят определенную порцию пользовательских данных и выполняют большую часть обработки запросов. Когда мастер получил запрос, на каждом сегменте создаются процессы для обработки этого запроса. Затем, каждый сегмент параллельно обработывает свою порцию данных и возвращает финальный результат своей работы на мастер.

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

Интерконнект

Это сетевой слой архитектуры GreenPlum. Обеспечивает взаимосвязь между мастером, сегментами и сетевой инфраструктуры в которой развернут кластер. Пропускная способность сети должна быть не менее 10 м/бит. По умолчанию обмен данными происходит по протоколу UDP. GreenPlum проводит дополнительную проверку пакетов, поэтому надежность такая же как и у TCP.

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

В GreenPlum общее время выполнения запроса измеряется выполнением на всех сегментах. Максимальная скорость выполнения ограничивается скоростью самого медленного сегмента. Поэтому надо стремиться к равномерному распределению данных по всем сегментам системы.

Равномерное распределение + Особенность ваших данных = Лучшая производительность

Для каждой таблицы задается своя политика дистрибьюции.

CREATE TABLE clients DISTRIBUTED BY id ... ;
ALTER TABLE clients SET DISTRIBUTED BY group ;

ключ должен учитивыть равномерность распределения данных
добиться локальности операций (соединяемые таблицы имеет равный ключ дистрибьюции)

Существуют три вида дистрибьюции:

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

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

использовать поле с высокой селективностью (идентификаторы)
отсутствие null или значений по умолчанию

Проверка распределения строк:

SELECT gp_segment_id, count(1)
FROM sales
GROUP BY gp_segment_id;

Перераспределение данных:

для добавления нового сегмента
для избежания раздутия таблиц

ALTER TABLE sales SET WITH (REORGANIZE=true);

Рекомендации:

явно задавать ключ распределения при создании таблицы, в том числе для временных таблиц
избегать указывать в качестве ключа атрибутов которые будете часто указывать в WHERE
избегать указывать в качестве ключа атрибуты с типом date, timestamp
предпочтительно использовать в качестве ключа тип int
предпочтительно использовать в качестве ключа столбцы, которые участвуют как ключ соединения (JOIN)

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

Логическое разделение на части по определенному критерию.

на больших таблицах значительно улучшает производительность
упращает сопровождение БД

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

Виды партиционирования:

PARTITION BY RANGE - по диапазону значений
PARTITION BY LIST - по списку значений
PARTITION BY … SUBPARTITION BY … многоуровеневое - комбинация предыдущих типов

Можно указывать:

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

Нельзя:

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

Информация о партиционированных таблицах и их иерархии:

SELECT * FROM pg_catalog.pg_partitions WHERE tablename = 'sales';

Информация о колонках, которые используются для партиционирования:

SELECT * FROM pg_catalog.pg_partition_colomns WHERE tablename = 'sales';

Рекомендации:

применять только на больших таблицах
только если фильтры (WHERE) будут исключать часть партиций из просмотра
когда нужно обеспечить скользящие окно данных при периодической загрузке или архивации
стараться выбирать по диапазону, а не по списку
партиционировать по часто используемому столбцу
убедиться, что запросы выборочно просматривают партиции, исключая ненужные (EXPLAIN)
выбирать наилучший способ физического хранения для разных партиций (например, по строкам / по колонкам)

Не рекомендуется:

создавать большое количество партиций
использовать дефолтную партицию (она всегда просматривается при выполнении любого запроса)
использовать многоуровневое партиционирование (обычно приводит к созданию большого количества партиций)
НИКОГДА не использовать секционирование по столбцу, который является ключом дистрибьюции
не создавать слишком много партиций при колоночной ориентации (количество_физических_файлов = сегменты * столбцы * партиции)

Объекты

Таблицы

Конструкция для копирования полной структуры таблиц

CREATE TABLE new_orders (LIKE order);

Методы хранения

HEAP - используется по умолчанию. Строковая ориентация. Унаследован из Postgresql.

Подходит:

для небольших таблиц ( < 300 000 строк или < 100 мб)
частые изменения данных (update, delete)
единичные операции вставки (insert)
параллельные операции обновления / удаление / вставка (OLTP нагрузка)

APPEND_OPTIMIZED.

Возможности:

можно выбирать ориентацию (строковую или колоночную)
можно сжимать применяя различные алгоритмы компрессии
менее ресурсоемкие в обслуживании (например, сбор статистики може быть выполнен инкрементально и, таким образом, быстрее)

Ограничения:

подходит только для данных, которые редко изменяются. При этом вставка этих данных происходит batch-ами
заметный прирост производительности проявляется только на больших таблицах
не поддерживает INDEX UNIQUE, PRIMARY KEY

Когда рекомендуют выбирать:

для таблиц, которые содержат много данных. Как правило это таблицы фактов
для таблиц, которые создаются однажды и далее к ним вополняются запросы или добавляются данные batch-ами (OLAP нагрузка)

Доступна как строковая так и колоночная ориентация.

ROW - для таблиц над которыми производятся:

множественные обновления
частые операции вставки
частая выборка одновременно большого количества столбцов (наприер SELECT *)

COLUMN - для таблиц:

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

Использование колоночной ориентации дает прирост скорости в оперции с таблицей если при этом использутся малое количество столбцов относительно общего числа. Так же этот тип хранения существенно уменьшает количество занимаемого таблицей места примерно на 30%. Реализуют хранение данных из одной колонки в одном файле

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

В строках хранятся “горячие” данные, т.е постоянно востребованные для оперативного анализа. А в колонках “холодные”, т.е архивные.

Следует учесть, что в Greenplum, в отличие от некоторых РСУБД, невозможно удалить таблицу, не удалив все зависимые объекты (включая представления).

Виды компрессии:

Ориентация	К каким объектам применима	Алгоритм компрессии
Строки	Таблицы	ZLIB, ZSTD
Колонки	Колонки. Таблицы	RLE_TYPE, ZLIB, ZSTD

Описание алгоритмов компрессии:

Алгоритм	Уровни сжатия	Описание
ZSTD	от 1 до 9	Позволяет выбрать оптимальное соотношение степени сжатия и скорости обработки данных. Используется чаще всего
RLE_TYPE	от 1 до 4	Более эффективен, чем ZLIB и ZSTD, в случаях, когда в данных есть много повторяющихся значений в большом количестве идущих подряд строк
ZLIB	от 1 до 19	Возможен более высокий уровень сжатия, но при низкой скорости

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

степень сжатия (экономия дискового пространства);
потребление CPU;
скорость сжатия;
скорость декомпрессии (сканирования) при чтении данных.

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

Одно и то же имя объекта можно свободно использовать в разных схемах, например, и schema1, и schema2 могут содержать таблицы с именем mytable.

Цели использования схем:

объединение объектов базы данных в логические группы для облегчения управления ими;
работа разных приложений с одной БД без конфликтов имён;
независимая работа несколько пользователей с одной БД.

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

Виды констрейнтов в Greenplum:

Значение	Описание
NOT NULL	Данная колонка не может иметь значение NULL
DEFAULT	Обеспечивает значение по умолчанию для колонки в случае, если данные не указаны
UNIQUE	Все значения в данной колонке уникальны. Замечание: доступно только в heap-таблицах
PRIMARY KEY	Уникальный идентификатор каждой записи в таблице БД. Замечание: это комбинация NOT NULL и UNIQUE, доступно только в heap-таблицах.
FOREIGN KEY	Уникальный идентификатор записи в другой таблице БД. Замечание: допускается в синтаксисе, но не работает
CHECK	Гарантирует, что все значения в колонке соответствуют определённому условию
INDEX	Используется для быстрого создания и получения данных из БД

Типы данных

Используйте наименьшие возможные типы данных для полей (INT вместо BIGINT, TEXT вместо CHAR(n))
По возможности используйте специализированные типы данных (INET, CIDR, JSON, MACADDR, ..)
Используйте одинаковые типы данных в полях, по которым выполняются JOIN-операции (разные типы в полях для JOIN могут приводить к увеличению времени выполнения операции из-за самого нежелательного движения данных - redistribute motion).
Создавайте свои типы данных, в некоторых случаях они могут дать огромный прирост производительности (например, при использовании типов-справочников)

Обратите внимание на следующие особенности Greenplum:

Изменение типа данных, используемого для объекта, влечет необходимость пересоздания этого объекта для учета изменений.
Повышение разрядности (точности) атрибута (например, varchar 10 → 20) ведет к удалению всех связанных объектов БД.
В общем случае типы данных влияют на производительность системы, поэтому желательно использовать «наименьший» из возможных типов данных.

Индексы

btree
bitmap
gist

btree (сбалансированное дерево):

используется по умолчанию
операции сравнения (><=)
between
is null, is not null
like

bitmap (двоичная карта):

мало уникальных значений, много повторений (низкая кардинальность)
read чаще чем update (не oltp)
в условиях когда в WHERE используются OR/AND с полями с таким индексом

gist (особый btree индекс, применяется для редких типов данных):

геоданные
текстовые документы
графические файлы

В GreenPlum индексы надо использовать с осторожностью. Потому что:

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

Рекомендации:

применять другие способы оптимизации. Применение индексов в DWH должна выполняться в последнюю очередь
индексы подходят для близкой OLTP-нагрузке (частый выбор небольшого числа строк с WHERE)
уменьшая стоимость запроса (random_page_cost), можно форсировать использование индекса планировщиком
если данные в таблице сжаты, то запрос к такой таблице с использованием индекса будет разжимать только те строки, которые запрашиваются, что может сэкономить время и ресурсы процессора
при загрузке данных в таблицу с индексами удалите индексы, загрузите данные, затем создайте индексы заново
для часто обновляемых таблиц снижайте значение коэффициента заполнения индекса FILLFACTOR. Этот параметр определяет степень заполненности блоков данных таблиц на диске и связан со скоростью и компактностью записи

Представления

View - это сохраненный запрос.

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

Преимущества использования представлений:

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

Рекурсивные представления

Рекурсивным называется представление в тексте которого используется ссылка на это же представление.

CREATE RECURSIVE VIEW <view_name>
(<columns>) AS
(
    <query>
    UNION ALL
    <recursive_query>
);

Материализованные представления

сохраняют результат запроса на диск
можно обращаться как к обычным таблицам
содержит данные актуальные на момент последнего обновления
обновляется по запросу пользователя REFRESH MATERIALIZED VIEW
могут значительно ускорить доступ к данным

Есть возможность указывать опции хранения как для обычных таблиц (WITH (applendonly=true, compresstype=zstd)). В конце DISTRIBUTED RANDOMLY;
Так же можно создавать индексы как для обычной таблицы.

Временно ограничить доступ пользователей к не актуальным данным:

REFRESH MATERIALIZED VIEW test_sales WITH NO DATA;
SELECT * FROM test_sales; -- ERROR пока не обновим стандартным способом

Функции

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

Функции в Greenplum вызываются с помощью предложения SELECT с последующим указанием имени функции и входящих параметров.

SELECT sqrt(225);
SELECT avg(price) FROM test_table;
SELECT user_func('one', 340);

Типы функций

Greenplum поддерживает несколько типов функций:

внутренние функции Greenplum и PostgreSQL, которых насчитывается более 2,5 тысяч
функции написанные с использованием языка SQL запросов
процедурные функции, которые написаны на процедурных расширениях известных языков программирования, таких как Python, Perl, Java и R.
также функции могут быть скомпилированы на языке C

Обычно два самых востребованных языка для функций в Greenplum являются SQL , а также PL/pgSQL - это процедурное расширение языка SQL унаследованное от Postgres.

Перегрузка функций

В Greenplum поддерживается такая опция как перегрузка функций. Данная опция позволяет создавать несколько функций с одинаковым именем.

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

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

Классы функций (Волатильность)

В Greenplum существует несколько классов функций в зависимости от ее поведения при вызове:

IMMUTABLE:

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

STABLE:

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

VOLATILE:

используется по-умолчанию, и сообщает о том, что значение функции может меняться в ходе выполнения транзакции. Примеры таких функций: timeofday, random, setval. Используйте такие типы функций с осторожностью. Чаще всего они выполняются только на мастере.

Если вы уверены, что ваша функция относится к классу IMMUTABLE или STABLE – обязательно явно укажите это в теле функции.

Опция EXECUTE ON

Так как Greenplum - это MPP система, то возникает вопрос, где может и должна выполнятся функция, на мастере или на сегментах.

Место выполнения функции определяется в предложении EXECUTE ON в теле функции.

Существуют следующие возможные варианты:

EXECUTE ON ANY:

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

EXECUTE ON MASTER:

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

EXECUTE ON ALL SEGMENTS:

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

Типы процедурных языков по признаку доверия

В Greenplum все процедурные языки подразделяются на два типа по признаку доверия.

Если язык относится к типу Trusted – то это означает, что при использовании данного языка пользователь не может вносить изменения на уровне файловой системы, например выходить в командную оболочку SHELL и так далее, то есть в этом случае пользователь не может нанести вред системе.

Если язык относится к типу Untrusted – то использование данного языка может нанести вред СУБД. Создание функций на таких языках доступно только ролям с правами суперпользователя, а выполнение таких функций доступно всем.

Следует отметить, что у некоторых языков есть две версии – trusted и untrusted.

Определить тип языка можно по следующему списку:

PL/pgSQL – trusted (установлен по-умолчанию)
PL/R – untrusted
PL/Python – untrusted (установлен по-умолчанию)
PL/Container (доступны Python и R) – trusted (установлен по-умолчанию)
PL/Java – trusted и untrusted
PL/Perl – trusted и untrusted
PL/C – trusted

SQL функции

Выполняют последовательность SQL команд
Возвращают результат последнего запроса в теле функции
- Возвращает первую строку результата последнего запроса
- ИЛИ может вернуть результат заранее преопределенного типа данных
Тело функции должно содержать SQL выражения завершенные “;”
Могут содержать выражения SELECT, INSERT, UPDATE или DELETE
Не могут содержать ROLLBACK, SAVEPOINT, BEGIN или COMMIT
Последнее выражение должно быть SELECT, кроме случая когда тип возвращаемого значения VOID
Если тип возвращаемого значения VOID - функция не возвращает ничего (аналог процедуры в других СУБД)

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

SELECT add(13240, 3242);

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

-- Еще один вариант предыдущего примера, но с предопределением типа
CREATE TYPE dup_result AS (f1 int, f2 text);
CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

-- Еще один вариант предыдущего примера, но с использованием функции table
CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(4);

-- Пример функции, которая выполняется на всех сегментах с волатильной функцией now()
CREATE FUNCTION run_on_segs (text) returns setof text as $$
  begin
    return next ($1 || ' - ' || now()::text );
  end;
 $$ language plpgsql VOLATILE EXECUTE ON ALL SEGMENTS;

SELECT run_on_segs('my test');

Функции PL/pgSQL

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

Написание запросов

При больших значениях OFFSET работает неэффективно

Сочетание запросов (операции над множествами)

объединение
- запрос1 UNION [ALL] запрос2
пересечение
запрос1 INTERSECT [ALL] запрос2
запрос1 EXCEPT [ALL] запрос2
вычитание

Требование:

одинаковое количество столбцов
одинаковые или совместимые типы данных

Списки VALUES

Позволяет создать постоянную таблицу которую можно использовать в запросе не создавая и не наполняя таблицу в БД.

VALUES

создает “постоянную таблицу” без объекта TABLE
вычисляет значение строки / множество значений строк
используется для формирования констант в большой команда или отдельно
в составе других команд синтаксис допускает использование VALUES везде где допускается SELECT, так как грамматически она воспринимается как SELECT

VALUES ( expression [, ...] ) [, ...]
    [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

Подзапросы WITH (CTE)

WITH

определяют “временные таблицы” в рамках одного запроса
позволяют ссылаться на них в основной команде
могут использоваться в SELECT, INSERT, UPDATE, DELETE

Рекурсивные

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

Агрегатные выражения

Агрегатные функции

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

-- Порядок входных значений и использование ORDER BY
-- агрегатная функция с выражением ORDER BY

SELECT array_agg(a ORDER BY b DESC) FROM table;

-- Вычисление процентиля и сортировка входых значений

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households;

-- получаем 50-ый процентиль (медиану) значения столбца income. Здесь 0.5 — это непосредственный аргумент; если бы дробь процентиля менялась от строки к строке, это не имело бы смысла.
-- Вычисление процентиля с фильтрацией

SELECT
count(*) AS unfiltered,
count(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);

-- предложение FILTER подает агрегатной функции только те входные строки, для которых условие_фильтра вычислено как истинное; другие строки отбрасываются.

Оконные функции

DML операции и транзакции

DML операции

SELECT
INSERT
UPDATE
DELETE
- так же есть DELETE FROM t1 USING t2 WHERE t1.id=t2.id

Не относится к dml: TRUNCATE - быстрое удаление данных и освобождение занимаемое таблицей места

Транзакции

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

суть транзакции: объединение последовательных DML операций в одну, по принципу “Все или ничего”
промежуточные состояния не видны другим транзакциям
в случае ошибки, откатываются все изменения

BEGIN; -- ТОЧКА С ЗАПЯТОЙ!!!
    UPDATE ...;
    INSERT ...;
    DELETE ...;
COMMIT;

BEGIN или START TRANSACTION - начало транзакции
END или COMMIT - фиксация изменений
ROLLBACK - откат всех изменений транзакции
SAVEPOINT - ставит метку в транзакции для возможности частичного отката. Можно откатить изменения сделанные после этой метки, не затрагивая изменений сделанных до этой метки
ROLLBACK TO SAVEPOINT - откат к ранее установленной метке
RELEASE TO SAVEPOINT - удаляет метку

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

ACID:

Atomicity (атомарность ) - Выполняются либо все операции, либо ни одной
Constistency (согласованность) - БД сохраняет согласованность при успешном завершении транзакции (т.е фиксации результатов операции)
Isolation (Изолированность) - При выполнении транзакции другие параллельные транзакции с теми же данными не оказывают влияние не ее результат. Полная изолированность - это дорогое требование, поэтому существуют разные режимы неполной изоляции транзакций
Durability (стойкость) - Даже при сбоях оборудования изменения, сделанные успешно завершенной транзакцией, сохраняются

Уровни изоляции

В стандарте SQL выделяют четыре основых уровня изоляции:

level	dirty read	Non-repeatable read	phantom read
	грязное чтение	Неповторяемое чтение	фантомное чтение
read uncommited - чтение незафиксированных данных	+	+	+
read commited - чтение зафиксированных данных	-	+	+
repeatable read - повторное чтение	-	-	+
serializable - сериализация	-	-	-

В GreenPlum поддерживается только read commited и serializable. При serializable все транзакции обрабатываются так как будто они запущены последовательно одна из другой. Read commited используется по умолчанию.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- это надо сделать сразу после начала транзакции

Следует учесть:

в режиме serializable транзакции будут прерываться.

Многоверсионность

Multi Version Concurency Control (MVCC) - строки могут иметь множество версий.

Для определения периода актуальности строк используются номера транзакций в рамках которых вносились изменения. Номер транзакции это уникальный идентификатор, который выдаеются транзакции в момент ее старта. Эти индентификаторы хранятся в специальных технических полях xmin и xmax которые есть в каждой таблице. Они не видны по умолчанию, но их можно вывести явно.

xmin - номер транзакции, которая добавила эту запись
xmax - номер транзакции, которая удалила эту запись. Если строка еще не удалялась значение будут 0.

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

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

SET gp_select_invisible = 'true';

Очистка

Удаленные строки :

занимают место на диске- увеличивают время чтения данных

VACUUM, VACUUM FULL - очистка таблиц от неактуальных записей.

VACUUM - помечает место, которые занимали удаленные строки как потенциально свободные. Физически, эти строки не удаляются, но при вставке новых строк в таблицу они будут вставлены на место удаленных. Не требует блокировки на таблицу

VACUUM FULL - делает все тоже самое, но еще удаляет записи и сжимает таблицу. На освободившееся место физически переносятся строки, которые уже были в этой таблице. Именно поэтому физически размер таблицы становится меньше. Требуется эксклюзивная блокировка. Иногда скопировать таблицу проще и быстрее. На мой взгляд, лучше не применять.

Для Append-optimized таблиц:

VACUUM ведет себя как VACUUM FULL, если bloat (раздутие. соотношение не актуальных строк к актуальным) > 10%
VACUUM FULL (или VACUUM bloat > 10%):
- сжимает таблицу, копируя данные в новый файл, и удаляет старый
- не блокируя таблицу (кроме кратковременного момента переключения на новый файл)

Рекомендации:

запускать VACUUM после массовых изменений (UPDATE/DELETE)
при регулярном VACUUM в команде VACUUM FULL нет необходимости
VACUUM FULL может потребоваться для схлопывания больших пустот в таблицах (после VACUUM) и исключения перекосов вычисления
системные каталоги тоже требуют регулярный VACUUM - частота зависит от количества DDL-операций
текущий bloat отображается в представлении gp_toolkit.gp_bloat_diag

Блокировки

Блокировки строк:

чтение никогда не блокирует строки
изменение строки блокирует ее для изменений, но не для чтения

Блокировки таблиц:

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

Рекомендации:

выносить операции, требующие ACCESS EXCLUSIVE из длительных транзакций / функций в отдельные транзакции / функции
ожидающие в очереди блокировки тоже конфликтуют с новыми запросами

Улучшение производительности

Мониторинг текущих запросов

Что проверять:

какие запросы выполняются
какой процесс их выполняет
сколько запрос уже работает

Поля в pg_stat_activity

поле	тип данных	описание
pid	integer	Идентификатор серверного процесса
state	text	Состояние серверного процесса: active (запрос выполняется), idle (ожидает новой команды от клиента), idle in transaction (внутри транзакции но сейчас не выполняет запрос)
query_start	timestamp with zone	Время начала выполнения активного / последнего запроса
query	text	Тест запроса
waiting	boolean	Флаг блокировки текущего запроса другим процессом

SELECT
  now() - query_start AS duration,
  pid,
  username,
  state,
  query
FROM pg_stat_activity
WHERE state !='idle' ORDER BY 1 DESC;

План запроса

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

EXPLAIN

запрос не выполняется
план запроса с оценочной информацией о выполнении, построенный на основе статистики о таблицах

EXPLAIN ANALYZE

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

BEGIN;
    EXPLAIN ANALYZE DELETE FROM sales WHERE product_id=30;
ROLLBACK;

Анализ плана запроса

Действия с данными	Быстрые	Медленные
Сканирование	Sequential Scan	-
Соединение	Hash Join	Nested Loop, Merge
Агрегация с сортировка	Hash Agg	Sort
Перемещение	Redistributed Motion	Broadcast Motion

Оператор	Описание
Materialize	Материализация подзапроса
InitPlan	Предварительный запрос, используемый при выборочном сканировании партиций, для определения какие партиции надо сканировать
Sort	Сортировка строк при подготовке к другой операции
Group by	Группировка строк по одному или нескольким столбцам
Group/Hash Aggregate	Агрегация с использованием хэша
Append	Объединяет наборы данных
Filter	Выбирает строки, используя WHERE
Limit	Ограничивает количество возвращаемых строк

Причины возникновения перекосов вычислений:

неравномерное распределение данных в таблицах из-за плохого ключа дистрибьюции
операции над колонками с низкой кардинальностью и неравномерным распределением значений

К чему ведет:

падение производительности системы
ошибка нехватки памяти

Методы решения и обхода:

перераспределение данных в таблицах с перекосом данных
использование промежуточных временных таблиц с RANDOMLY распределением
изменение текста запроса
принудительный Broadcast motion небольших таблиц справочников:
- через изменение конфигурационных параметров (gp_segments_for_planner)
- изменение типа распределения на DISTRIBUTED REPLICATED

Причины возникновения Spill-файлов:

запрос обрабатывает слишком много файлов за раз
наличие перекоса в данных
лимит памяти под запрос слишком маленький

Методы решения:

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

Мониторинг использования spill-файлов:

gp_workfile_usage_per_query - по запросам
gp_workfile_entries - по операторам
gp_workfile_usage_per_segment - по сегментам

Типы оптимизаторов

GPORCA - используется по умолчанию. Спец создан для GreenPlum, постоянно развивается
Legacy - унаследован от Postgresql. Использование задается явно через конфиг. Может произойти авто переключение, если испльзуется что-то, что не поддерживается первым

SHOW optimazer;
SET optimazer='off'; --переключение на Legacy
SET optimazer='on'; -- переключение на GPORCA

Сбор статистики

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

Необходимо собирать после:

загрузки данных (COPY, GPLOAD, gpfdist)
создания индекса (CREATE INDEX)
после массовых операций INSERT, UPDATE, DELETE

ANALYZE [<table_name>[(<column1>[,...])]];

Имена партиций:

SELECT parttiontablename
FROM pg_partitions
WHERE tablename=<parent_table>;

Системные таблицы и представления статистики:

pg_catalog.pg_statistic - таблица со всей статистикой
pg_catalog.pg_stats - представление со статистикой в удобном формате и по таблицам доступным пользователю
pg_catalog.pg_stat_operations - представление с информацией об операциях над объектами БД
gp_toolkit.gp_stats_missing - представление, содержащее объекты с устаревшей статистикой

SHOW gp_autostats_mode;
SHOW gp_autostats_on_change_threshold;

SET gp_autostats_mode TO 'on_change';
SET gp_autostats_on_change_threshold TO '10000000';

Если сбор статистики выполняется слишком долго, можно запускать его на столбцах:

в условии join
в фильтре
в сортировке
в группировке

Общие рекомендации

Учитывайте особенности архитектуры GreenPlum при проектировании таблиц БД.
Выполняйте сбор статистики (ANALYZE) после операций, изменяющих большой объем данных.
При анализе плана запроса:
- Найдите операции с наибольшей стоимостью (cost) и проверьте:
  - соответствует ли указанное в плане количество строк фактическому
  - актуальна ли используемая статистика
- Убедитесь в том, что отсутствует сканирование лишних партиций (partition elimination, (pruning))
- Соединяйте большие таблицы в конце запросов
- По возможности избегайте Redistribute motion, Broadcast motion, Nested Loop join

← Предыдущая статья

Следующая статья →

Оптимизация хранения данных в Greenplum