Версия сайта для слабовидящих
Как выбирать политики распределения и разделения данных в Greenplum, в чем польза динамического сканирования индексов, зачем регулярно использовать операции VACUUM и ANALYZE, из-за чего тормозят SQL-запросы и как это исправить.
Эффективное распределение и разделение
Будучи основанной на PostgreSQL, Greenplum расширяет возможности этой замечательной СУБД, добавляя операции с массово-параллельной обработкой. Для этого Greenplum использует распределение данных по сегментам – машинам кластера, чтобы обеспечить параллельное выполнение SQL-запросов. Дата-инженер может выбрать один из 3- методов распределения данных: хеширование, случайное распределение или реплицированное. По умолчанию используется хеш-распределение. Когда пользователи создают таблицы без указания метода распределения, система автоматически распределяет кортежи на основе первого хешируемого столбца. Однако рекомендуется явно определить политику распределения, чтобы выбрать столбец, который обычно используется в JOIN-операциях или выступает условием фильтрации. Обычно для этого используются первичные ключи или часто используемые внешние ключи.
Случайное распределение используется в Greenplum реже. При таком распределении данные распределяются по всем сегментам по принципу кругового перебора (Robin Round). Однако, состояние уровня сеанса не поддерживается, чтобы гарантировать единообразие или какой-либо шаблон распределения. Случайное распределение хорошо подходит для ситуации, когда шаблоны запросов не зависят от определенных столбцов, а имеют рандомную природу. Например, события пользовательского поведения (клики, просмотры, запуск приложений и транзакции) генерируются случайным образом непрерывно и без определенной закономерности.
При реплицированном распределении каждый сегмент сохраняет полную копию всех кортежей в таблице. При совместном использовании компактной реплицируемой таблицы вместе с более крупной нет необходимости перераспределять данные по сегментам сети: JOIN-операции могут выполняться непосредственно над сегментами параллельно.
Стратегия распределения данных задается при создании таблицы в команде CREATE TABLE с помощью ключевого слова DISTRIBUTED. Для хеш-распределения явно задается столбец распределения после слов DISTRIBUTED BY, например,
CREATE TABLE "Sales" (
sale_id SERIAL,
customer_id NUMERIC,
customer_name VARCHAR(255),
product_name VARCHAR(255),
purchase_date DATE,
sales_amount DECIMAL(10, 2)
) DISTRIBUTED BY (customer_id);
Для случайного распределения указывается параметр RANDOMLY:
CREATE TABLE "UserEvents" (
event_id SERIAL,
timestamp TIMESTAMP NOT NULL,
user_id INT NOT NULL,
event_type VARCHAR(255) NOT NULL
) DISTRIBUTED RANDOMLY;
А для реплицированного – REPLICATED:
CREATE TABLE "HospitalContacts" (
hospital_name VARCHAR(255) NOT NULL,
contact_name VARCHAR(100),
phone_number VARCHAR(20),
email_address VARCHAR(255),
specialized_department_contacts TEXT[]
) DISTRIBUTED REPLICATED;
Что касается партиционирования данных, т.е. разделения больших таблиц на более мелкие единицы (разделы), в Greenplum есть две стратегии разделения:
- по диапазонам, что отлично подходит для организации данных временных рядов. Задается при создании таблице с помощью ключевого слова PARTITION BY RANGE (…);
- по предопределенным спискам значений, что указывается с помощью ключевого слова PARTITION OF … при создании таблицы.
Оптимизатор запросов Greenplum знает о схемах разделения и генерирует планы выполнения SQL-запросов так, чтобы обходить ненужные разделы. Таким образом, сканирование таблицы выполняется быстрее и время выполнения всего SQL-запроса сокращается. Разделение можно сочетать с распределением, чтобы увеличить параллелизм операций, при этом каждый сегмент обрабатывает лишь часть общего числа кортежей.
Индексация и операции обслуживания для ускорения SQL-запросов
Подробно про индексацию в Greenplum мы писали здесь. Чтобы не повторяться, напомним, что Greenplum поддерживает различные типы индексов, включая B-дерево, bitmap, GIN, BRIN, хеш и пространственную индексацию через расширение PostGIS. Хотя индексы ускоряют поиск и снижают его алгоритмическую сложность, они увеличивают размер базы данных, провоцируют рост накладных расходов при записи данных и требуют обслуживания.
Поскольку индексы дают преимущества для SQL-запросов с высокой селективностью, которые возвращают менее половины строк таблицы, при работе с большими данными это нечастый сценарий. Поэтому индексация не очень распространена в Greenplum, где быстрый поиск достигается и в рамках последовательного сканирования, если каждый сегмент содержит равную часть данных, и все сегменты при чтении работают параллельно.
Но иногда индексы могут повысить производительность SQL-запросов, например, если сканируется только индекс, когда Greenplum извлекает информацию непосредственно из него, минуя базовую таблицу. Хорошо спроектированные индексы включают столбцы, которые часто встречаются в SQL-запросах. Индексы Greenplum поддерживают разделение данных и динамическое сканирование. Индексируемый столбец определяется с помощью ключевого слова INDEX. Например, следующая команда показывает создание индекса B-дерева в столбце суммы заказа:
CREATE INDEX idx_order_amount ON SalesData USING btree (order_amount);
Как мы уже отмечали здесь, оператор VACUUM используется в Greenplum для сборки мусора, подобно Garbage Collector в JVM, высвобождая пространство, занимаемое удаленными или устаревшими кортежами, которые физически не удалены из таблицы. Рекомендуется периодически необходимо команду VACUUM для часто изменяемых таблиц. Этот оператор очищает все таблицы и материализованные представления в базе данных, но не применяется внутри блока транзакции. Необходимость очистки в Greenplum обусловлена MVCC-моделью блокировки/параллелизма (Multi Version Concurrency Control), где запись не блокирует чтение во время операций обновления и удаления благодаря созданию новых версии строк таблицы при обработке данных, а не при физическом удалении или обновлении исходной строки. Поэтому старые версии строк продолжают существовать физически и занимать пространство. После очистки вновь вставленные строки будут повторно использовать высвобожденное пространство, а при отсутствии свободных слотов будут вставлены в конец таблицы каталога, что увеличивает ее размер и снижает производительность обработки данных. Таким образом, VACUUM обновляет карту видимости строк таблицы, которая актуальна для сканирования только индекса. Таким образом, регулярная очистка дает положительный побочный эффект для индекса.
Команда ANALYZE отвечает за сбор массива статистических данных, включая гистограммы столбцов, наиболее распространенные значения (MCV), количество строк, количество различных значений (NDV) и корреляции между столбцами. Эти статистические данные важны для оценки кардинальности, селективности и перекоса данных, чтобы оптимизатор SQL-запросов мог определить оптимальные стратегии использования индекса, соединения, условий фильтрации и методов доступа. Обычно оператор ANALYZE выполняется в фоновом режиме автоматически системной службой (демоном), проверяющей таблицы, количество обновлений и удалений которых превышает заранее заданный порог. Однако, для больших таблиц этот порог достигается редко. А потому рекомендуется регулярно вручную запускать команду анализа ANALYZE вместе с очисткой VACUUM, чтобы гарантировать точность статистики и отсутствие «зомби»-строк, которые были удалены, но продолжают занимать место. Подробнее про оператор ANALYZE мы рассказывали здесь.
ANALYZE часто используется вместе с командой EXPLAIN, которая отображает план запроса и предполагаемую стоимость его выполнения. Это позволяет дата-инженеру понять, как Greenplum планирует выполнить запрос, и помогает выявить потенциальные узкие места при долгом выполнении SQL-запроса, которое может быть вызвано следующими типовыми проблемами:
- чрезмерное количество операций дискового ввода-вывода, когда на диск записывается значительный объем данных;
- план не использует доступные индексы;
- неоптимальные стратегии соединения, например, вложенные циклы вместо хеширования;
- большой промежуточный результат во время соединения, сортировки или агрегации;
- высокое перераспределение или широковещательная передача данных;
- недооценка или переоценка кардинальности запроса;
- отсутствие параллелизма, когда выполнение происходит на координаторе, а не на сегментах Greenplum;
- неэффективное условие фильтрации;
- ненужное сканирование разделов или целых таблиц.
Определив причину торможения SQL-запроса, можно принять точные меры по исправлению ситуации. Читайте в нашей новой статье, как повысить эффективность Greenplum, настроив параметры конфигурации этой СУБД.
Освойте администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
