Распределение данных

Андрей Аксенов

Contents

Обзор
- Типы распределения
- Рекомендации по выбору типа распределения
Настройка типа распределения
Просмотр информации о распределении
- Просмотр настроек распределения таблицы
- Проверка распределения данных
Изменение типа распределения
Пример: Некорректный выбор типа распределения
- Рекомендуемый тип распределения
- Некорректный тип распределения

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

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

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

┌──────────────── Сегмент 0 ─────────────┐   ┌──────────────── Сегмент 1 ─────────────┐
│ customers (id, customer_name)          │   │ customers (id, customer_name)          │
│     1   │ 'Andrew Fuller'              │   │     11  │ 'Robert King'                │
│     2   │ 'Michael Suyama'             │   │     12  │ 'Laura Callahan'             │
│     ...                                │   │     ...                                │
│ orders (id, customer_id, product_name) │   │ orders (id, customer_id, product_name) │
│     101 │ 1  │ 'Laptop'                │   │     303 │ 11 │ 'Keyboard'              │
│     106 │ 1  │ 'Smartphone'            │   │     305 │ 11 │ 'Headphones'            │
│     110 │ 2  │ 'Tablet'                │   │     311 │ 12 │ 'Smartwatch'            │
│     117 │ 2  │ 'Monitor'               │   │     315 │ 12 │ 'Laptop'                │
│     ...                                │   │     ...                                │
└────────────────────────────────────────┘   └────────────────────────────────────────┘

┌──────────────── Сегмент 2 ─────────────┐   ┌──────────────── Сегмент 3 ─────────────┐
│ customers (id, customer_name)          │   │ customers (id, customer_name)          │
│     21  │ 'Nancy Davolio'              │   │     31  │ 'Steven Buchanan'            │
│     22  │ 'Janet Leverling'            │   │     32  │ 'Margaret Peacock'           │
│     ...                                │   │     ...                                │
│ orders (id, customer_id, product_name) │   │ orders (id, customer_id, product_name) │
│     502 │ 21 │ 'Smartphone'            │   │     701 │ 31 │ 'Smartphone'            │
│     504 │ 21 │ 'Speakers'              │   │     705 │ 31 │ 'Laptop'                │
│     508 │ 22 │ 'Printer'               │   │     712 │ 32 │ 'Keyboard'              │
│     511 │ 22 │ 'Tablet'                │   │     717 │ 32 │ 'Smartwatch'            │
│     ...                                │   │     ...                                │
└────────────────────────────────────────┘   └────────────────────────────────────────┘

Для указания типа распределения используйте выражение DISTRIBUTED в команде CREATE TABLE:

CREATE TABLE <table_name> (
  <column_definitions>
)
[
  -- Хеш-распределение
  DISTRIBUTED BY (<column_name> [<opclass>] [, ... ] )

  -- Случайное распределение
  | DISTRIBUTED RANDOMLY

  -- Реплицированное распределение
  | DISTRIBUTED REPLICATED
];

Хеш-распределение

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

ПРИМЕЧАНИЕ

Вы можете указать кастомную хеш-функцию для хеш-распределения с помощью параметра opclass, который задает класс операторов хеширования. Кастомный класс операторов хеширования позволяет использовать другой способ сравнения вместо оператора равенства (=), используемого по умолчанию. Это может быть полезно для поддержки локальных соединений (co-located joins) с использованием кастомных операторов — например, для сравнения без учета регистра или определения оператора равенства, специфичного для определенной предметной области. Смотрите статью CREATE OPERATOR CLASS в документации PostgreSQL, чтобы узнать, как определить новый класс операторов.
Случайное распределение

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

Хранит полную копию таблицы на каждом сегменте кластера. Подходит для небольших таблиц, например, таблиц-справочников. Использование реплицированного распределения помогает избежать перемещения данных (motion) при соединениях.

Всегда явно задавайте тип распределения при создании таблицы. Если выражение DISTRIBUTED отсутствует, Greengage DB по умолчанию применяет хеш-распределение, используя в качестве ключа распределения либо столбцы PRIMARY KEY (если заданы), либо первый столбец таблицы. Это поведение регулируется параметром конфигурации gp_create_table_random_default_distribution, который по умолчанию имеет значение OFF.

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

Для обеспечения оптимальной производительности SQL-запросов данные должны быть равномерно распределены по всем сегментам кластера. Неравномерное распределение или перекос данных (skew) может привести к снижению производительности, так как на сегменты с большим объемом данных приходится увеличенная нагрузка при выполнении SQL-запросов. Выбирайте ключ распределения, который уникален для каждой записи, например, первичный ключ.

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

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

ПРИМЕЧАНИЕ

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

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

Для достижения наилучшей производительности все сегменты должны обрабатывать равные доли нагрузки SQL-запросов. Нагрузка может быть распределена неравномерно, если тип распределения данных в таблице не согласуется с условиями запроса. Например, таблица sales распределяется по product_id. Если в запросе используется фильтрация по конкретному product_id (в выражении WHERE) для анализа продаж этого продукта, то нагрузка будет сосредоточена на одном сегменте.

РЕКОМЕНДАЦИЯ

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

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Затем создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Пример ниже показывает, как создать оптимизированную для добавления (Append-optimized, AO) таблицу customers с хеш-распределением строк по столбцу customer_id:

CREATE TABLE customers
(
    customer_id   INTEGER,
    customer_name VARCHAR(25)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Тестовые данные

INSERT INTO customers (customer_id, customer_name)
SELECT customer_number               AS customer_id,
       'customer' || customer_number AS name
FROM generate_series(1, 1000) AS customer_number;

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

CREATE TABLE orders
(
    order_id    INTEGER,
    customer_id INTEGER,
    amount      DECIMAL(6, 2)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (customer_id);

Тестовые данные

INSERT INTO orders (order_id, customer_id, amount)
SELECT order_number                               AS order_id,
       FLOOR(RANDOM() * 1000 + 1)::INTEGER        AS customer_id,
       ROUND((100 + RANDOM() * 1000)::NUMERIC, 2) AS amount
FROM generate_series(1, 100000) AS order_number;

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

CREATE TABLE events
(
    event_id   SERIAL,
    timestamp  TIMESTAMP    NOT NULL,
    user_id    INTEGER      NOT NULL,
    event_type VARCHAR(255) NOT NULL
)
    WITH (appendoptimized = true)
    DISTRIBUTED RANDOMLY;

Тестовые данные

INSERT INTO events (timestamp, user_id, event_type)
SELECT NOW() - (INTERVAL '7 days' * random())                                     AS timestamp,
       (random() * 10)::INTEGER + 1                                               AS user_id,
       (ARRAY ['click', 'launch', 'purchase', 'logout'])[floor(random() * 4 + 1)] AS event_type
FROM generate_series(1, 4000);

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

CREATE TABLE pickup_points
(
    point_id     INTEGER,
    address      TEXT,
    phone_number TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED REPLICATED;

Тестовые данные

INSERT INTO pickup_points (point_id, address, phone_number)
SELECT point_id_seq,
       'Address ' || point_id_seq,
       '123-456-' || LPAD(point_id_seq::TEXT, 4, '0')
FROM generate_series(1, 100) AS point_id_seq;

Чтобы просмотреть параметры распределения указанной таблицы, используйте метакоманду \d:

\d orders

В приведенном ниже выводе раздел Distributed by показывает, что таблица orders распределена по столбцу customer_id:

   Append-Only Table "public.orders"
   Column    |     Type     | Modifiers
-------------+--------------+-----------
 order_id    | integer      |
 customer_id | integer      |
 amount      | numeric(6,2) |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (customer_id)

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

Функция get_ao_distribution() показывает распределение строк в оптимизированной для добавления (AO) таблице:
```
SELECT get_ao_distribution('orders');
```
Вывод показывает, сколько строк находится на каждом сегменте:
```
 get_ao_distribution
---------------------
 (1,24828)
 (0,25823)
 (3,24064)
 (2,25285)
```
Представление gp_toolkit.gp_skew_coefficients показывает перекос в распределении данных, вычисляя коэффициент вариации для данных, хранимых на каждом сегменте:
```
SELECT skcrelname, skccoeff
FROM gp_toolkit.gp_skew_coefficients;
```
Результат может выглядеть следующим образом:
```
  skcrelname   |        skccoeff
---------------+-------------------------
 customers     | 2.865891368027290280000
 orders        | 2.979247332241260000000
 events        | 2.222611077089286890000
 pickup_points | 0.000000000000000000000
```
Чем меньше значение, тем лучше. Большие значения указывают на большую диспропорцию распределения данных.
Представление gp_toolkit.gp_skew_idle_fractions показывает перекос в распределении данных, рассчитывая процент времени простоя системы при сканировании таблицы, что является индикатором вычислительного дисбаланса:
```
SELECT sifrelname, siffraction
FROM gp_toolkit.gp_skew_idle_fractions;
```
Результат может выглядеть так:
```
  sifrelname   |      siffraction
---------------+------------------------
 customers     | 0.02723735408560311284
 orders        | 0.03187081284126553847
 events        | 0.02439024390243902439
 pickup_points | 0.00000000000000000000
```
Например, значение 0.02 указывает на перекос в 2%. Для таблиц с перекосом более 10% следует пересмотреть их настройки распределения.
Таблица pg_catalog.gp_segment_configuration содержит информацию о сегментах кластера. Поле gp_segment_id соответствует столбцу content в таблице gp_segment_configuration и может использоваться для получения количества строк для каждого сегмента. Следующий SQL-запрос показывает количество строк по сегментам, а также отклонение от минимального и максимального значения:
```
SELECT 'orders'                                                   AS "Table name",
       MAX(row_count)                                             AS "Max seg rows",
       MIN(row_count)                                             AS "Min seg rows",
       (MAX(row_count) - MIN(row_count)) * 100.0 / MAX(row_count) AS "Percentage difference between Max & Min"
FROM (SELECT COUNT(*) AS row_count, gp_segment_id
      FROM orders
      GROUP BY gp_segment_id) AS seg_row_counts;
```
Результат может выглядеть так:
```
 Table name | Max seg rows | Min seg rows | Percentage difference between Max & Min
------------+--------------+--------------+-----------------------------------------
 orders     |        25781 |        24056 |                      6.6909739730809511
```
ПРИМЕЧАНИЕ

Если обратиться к системному столбцу gp_segment_id для реплицируемой таблицы, запрос завершится с ошибкой — Greengage DB не поддерживает такие обращения.

Вы можете изменить тип распределения с помощью команды ALTER TABLE. Чтобы указать новый тип распределения, используйте выражение SET DISTRIBUTED:

ALTER TABLE pickup_points
    SET DISTRIBUTED BY (point_id);

Вы также можете перераспределить данные в таблице, используя текущий тип распределения. Для этого установите параметр reorganize в значение true в выражении SET WITH:

ALTER TABLE orders
    SET WITH (reorganize = true);

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

ВАЖНО

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

В этом разделе показано, как неправильный выбор типа распределения может негативно повлиять на производительность аналитического запроса, использующего соединение (join).

Этот запрос выполняет соединение таблиц customers и orders по общему ключу распределения customer_id, агрегирует общую сумму покупок для каждого клиента и возвращает пять клиентов с наибольшей суммой покупок:

SELECT c.customer_id,
       c.customer_name AS customer_name,
       SUM(o.amount)   AS total_purchases
FROM customers c
         JOIN orders o USING (customer_id)
GROUP BY c.customer_id, c.customer_name
ORDER BY total_purchases DESC
LIMIT 5;

EXPLAIN предоставляет подробную информацию о плане выполнения запроса:

EXPLAIN (COSTS OFF)
SELECT c.customer_id,
       c.customer_name AS customer_name,
       SUM(o.amount)   AS total_purchases
FROM customers c
         JOIN orders o USING (customer_id)
GROUP BY c.customer_id, c.customer_name
ORDER BY total_purchases DESC
LIMIT 5;

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

 Limit
   ->  Gather Motion 4:1  (slice1; segments: 4)
         Merge Key: (sum(o.amount))
         ->  Limit ...

Полный план запроса

                                 QUERY PLAN
----------------------------------------------------------------------------
 Limit
   ->  Gather Motion 4:1  (slice1; segments: 4)
         Merge Key: (sum(o.amount))
         ->  Limit
               ->  Sort
                     Sort Key: (sum(o.amount))
                     ->  HashAggregate
                           Group Key: c.customer_id, c.customer_name
                           ->  Hash Join
                                 Hash Cond: (o.customer_id = c.customer_id)
                                 ->  Seq Scan on orders o
                                 ->  Hash
                                       ->  Seq Scan on customers c
 Optimizer: Pivotal Optimizer (GPORCA)
(14 rows)

Измените ключ распределения таблицы orders с customer_id на order_id:

ALTER TABLE orders
    SET DISTRIBUTED BY (order_id);

Запустите EXPLAIN еще раз:

EXPLAIN (COSTS OFF)
SELECT c.customer_id,
       c.customer_name AS customer_name,
       SUM(o.amount)   AS total_purchases
FROM customers c
         JOIN orders o USING (customer_id)
GROUP BY c.customer_id, c.customer_name
ORDER BY total_purchases DESC
LIMIT 5;

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

Redistribute Motion — перемещает кортежи между сегментами для выполнения соединения. Это требуется потому, что таблица customers распределена по customer_id, а таблица orders — по order_id.
Broadcast Motion — копия таблицы отправляется на все сегменты, так как они не содержат необходимых данных для выполнения соединения. Для небольших таблиц это не вызывает проблем, однако при соединении больших таблиц может привести к снижению производительности.

 Limit
   ->  Gather Motion 4:1  (slice3; segments: 4)
         Merge Key: (pg_catalog.sum((sum(o.amount))))
         ....
         ->  Redistribute Motion 4:4  (slice2; segments: 4)
             ....
                 ->  Broadcast Motion 4:4  (slice1; segments: 4)
                 ....

Полный план запроса

                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit
   ->  Gather Motion 4:1  (slice3; segments: 4)
         Merge Key: (pg_catalog.sum((sum(o.amount))))
         ->  Limit
               ->  Sort
                     Sort Key: (pg_catalog.sum((sum(o.amount))))
                     ->  HashAggregate
                           Group Key: c.customer_id, c.customer_name
                           ->  Redistribute Motion 4:4  (slice2; segments: 4)
                                 Hash Key: c.customer_id, c.customer_name
                                 ->  Result
                                       ->  HashAggregate
                                             Group Key: c.customer_id, c.customer_name
                                             ->  Hash Join
                                                   Hash Cond: (o.customer_id = c.customer_id)
                                                   ->  Seq Scan on orders o
                                                   ->  Hash
                                                         ->  Broadcast Motion 4:4  (slice1; segments: 4)
                                                               ->  Seq Scan on customers c
 Optimizer: Pivotal Optimizer (GPORCA)
(20 rows)

Сжатие данных

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

Распределение данных

Обзор

Типы распределения

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

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

Локальные и распределенные операции

Равномерная обработка запросов

Настройка типа распределения

Хеш-распределение

Случайное распределение

Реплицированное распределение

Просмотр информации о распределении

Просмотр настроек распределения таблицы

Проверка распределения данных

Изменение типа распределения

Пример: Некорректный выбор типа распределения

Рекомендуемый тип распределения

Некорректный тип распределения