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

• Documentation
• greenplum-db
• Документация PostgreSQL на русском

Введение

Симметричная мультипроцессорная архитектура (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, в отличие от некоторых РСУБД, невозможно удалить таблицу, не удалив все зависимые объекты (включая представления).

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

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

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

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

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

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

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

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

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

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

Типы данных

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

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

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

Индексы

Про индексы в Posgresql

• 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 подает агрегатной функции только те входные строки, для которых условие_фильтра вычислено как истинное; другие строки отбрасываются.

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

• Оконные функции. Документация Postgresql
• Оконные функции в Postgresql с примерами
• Аналитические функции в Oracle и Postgresql

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 выделяют четыре основых уровня изоляции:

В 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

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;

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

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

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

К чему ведет:

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

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

• перераспределение данных в таблицах с перекосом данных
• использование промежуточных временных таблиц с 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
• Пользуйтесь дополнительными инструментами анализа плана запроса:
  • Инструмент анализа плана запроса