Анализируй и оптимизируй: статистика таблиц и планы выполнения SQL-запросов в Greenplum

Автор Категория ,
Анализируй и оптимизируй: статистика таблиц и планы выполнения SQL-запросов в Greenplum

Чтобы сделать наши курсы по Greenplum и аналитике больших данных еще более полезными, сегодня рассмотрим особенности выполнения SQL-запросов в этой MPP-СУБД. Читайте далее, зачем и когда запускать оператор анализа табличной статистики ANALYZE, как он связан с планом выполнения SQL-запроса и какие инструменты помогут дата-инженеру, аналитику или разработчику повысить их производительность.

Как выполняются SQL-запросы в СУБД: краткий ликбез

Прежде всего напомним общий процесс выполнения SQL-запросов в СУБД. Сперва SQL-выражение преобразуется в план выполнения запроса – последовательность операций, нужных для получения результата, включая следующие [1]:

  • Выборка результатов через вложенные циклы (итеративные процессы поиска данных в каждой из соединяемых таблиц) и слияние. Если объединяемые таблицы имеют индексы по сравниваемым полям, они могут объединяться с помощью слияния, когда оба индекса сканируются и в них ищутся одинаковые значения. Если колонок в индексах достаточно для получения итогового результата, то чтение таблиц не выполняется. Иначе выполняется прямой доступ к сливаемым таблицам для получения не индексных колонок, нужных для результата. Если слияния недостаточно для получения итогового результата, то для каждой строки, полученной слиянием, выполняется до 2-х серий вложенных циклов (для каждой из сливаемых таблиц). Поскольку в индексах данные отсортированы, слияние дешевле вложенных циклов, но план выполнения не может начинаться со слияния.
  • Сортировка и группировка, а также выполнение агрегаций. Это выполняется опционально, если не найдено путей доступа для получения результата в нужном порядке.

Оптимизатор запросов в СУБД использует статистическую информацию, которая хранится хранящуюся в базе данных вместе с таблицами и индексами. На основе этой статистики оптимизатор оценивает альтернативные способы формирования результатов SQL-запроса, стараясь выбрать самый эффективный план выполнения. Просмотр и изучение планов выполнения запросов, созданных оптимизатором, позволяют оптимизировать их, изменив сам запрос или создав дополнительный индекс [1]. Выделяют 2 стратегии поиска оптимального плана [2]:

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

В любом случае, оптимизация SQL-запроса сводится к поиску минимума функции стоимости от перестановки таблиц. Независимо от стратегии, оптимизатор анализирует стоимость для произвольной перестановки, предоставляемых другим алгоритмом. В СУБД Greenplum (GP) оптимизатор также оценивает множество стратегий выполнения SQL-запроса и выбирает наименее затратный метод с учетом следующих факторов [3]:

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

Оптимизатор Greenplum создает планы выполнения SQL-запросов на основе статистики таблиц. Таким образом, чтобы составить лучший план выполнения запроса, эта статистическая информация должна быть точной. Для получения таких данных в GP есть специальный оператор ANALYZE, о котором мы поговорим далее.

Что такое оператор ANALYZE и зачем он нужен в Greenplum

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

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

Запускать оператор ANALYZE рекомендуется в следующих случаях:

  • после загрузки данных;
  • после операций создания индексов (CREATE INDEX),
  • после операций INSERT, UPDATE и DELETE, которые значительно изменяют базовые данные.

ANALYZE блокирует чтение таблиц, поэтому его можно запускать параллельно с некоторыми другими действиями СУБД. Однако, не следует запускать ANALYZE при выполнении операций загрузки, INSERT, UPDATE, DELETE и CREATE INDEX.

Поскольку запуск ANALYZE на большой таблице может выполняться долго, есть смысл сгенерировать статистику для ее отдельных столбцов через команду ANALYZE table(column, …). Как правило, сюда включаются столбцы, используемые в JOIN-соединениях и условиях WHERE, а также выражениях SORT, GROUP BY или HAVING. Для таблицы с большим количеством разделов можно применить ANALYZE только к отдельным разделам, которые изменились, например, при добавлении нового, запустив сбор статистики для родительской (основной) таблицы или для конечных узлов – файлов разделов, где фактически хранятся данные и статистика. Промежуточные файлы партиционированных таблиц не хранят данных или статистики, поэтому на них оператор ANALYZE не работает. Найти имена партиционированных таблиц и их разделов можно в системном каталоге pg_partitions с помощью команды SELECT partitiontablename from pg_partitions WHERE tablename=’parent_table.

Время создания статистики с помощью оператора ANALYZE зависит от ее качества (точности): чем точнее нужны данные, тем длительнее будет период их сбора. Поэтому, чтобы достичь разумного компромисса между ресурсами и результатом при исследовании большой таблицы, ANALYZE берет случайную выборку ее содержимого, а не анализирует каждую строку в отдельности. Для задания точности анализа используются следующие настроечные параметры оператора [4]:

  • default_statistics_target – определяет количество выборочных значений для всех столбцов таблицы, от 1 до 1000, значение по умолчанию равно 100. Этот параметр применяется ко всем столбцам и указывает количество значений, которые хранятся в списке общих значений. Повышение default_statistics_target может улучшить качество оценок планировщика запросов, особенно для столбцов с нерегулярными шаблонами данных. Параметр default_statistics_target устанавливается на уровне мастера/сессии и требует перезагрузки.
  • gp_autostats_mode вместе с параметром gp_autostats_on_change_threshold определяет, когда запускается операция автоматического анализа, чтобы планировщик добавил к запросу шаг ANALYZE. По умолчанию значение параметра gp_autostats_mode установлено on_no_stats, что запускает сбор статистики для операций CREATE TABLE AS SELECT, INSERT или COPY для любой таблицы, для которой нет существующей статистики. Установка значения on_change запустит сбор статистики только тогда, когда количество затронутых строк превышает порог, определенный параметром gp_autostats_on_change_threshold, по умолчанию равный 2147483647. Автоматический сбор статистики с on_change запускается для операций CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT и COPY. Установка параметра gp_autostats_mode в значение none отключает автоматический сбор статистики.

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

Разобравшись с оператором анализа и сбора статистики по таблицам, отметим, какие инструменты помогают дата-аналитику и SQL-разработчику оптимизировать выполнение запросов в Greenplum. Такими операторами являются EXPLAIN и EXPLAIN ANALYZE [3], которые не только отображают план выполнения запроса и предполагаемую стоимость запроса, но и дают дополнительную полезную информацию, подробно о которой мы поговорим в следующий раз.

Узнайте больше про администрирование и эксплуатацию MPP-СУБД Greenplum для эффективного хранения и аналитики больших данных на новом практическом курсе «Greenplum для инженеров данных» в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве.

Источники

  1. https://ru.wikipedia.org/wiki/План_выполнения_запроса
  2. https://ru.wikipedia.org/wiki/Оптимизация_запросов_СУБД
  3. https://gpdb.docs.pivotal.io/6-16/best_practices/tuning_queries.html
  4. https://gpdb.docs.pivotal.io/6-16/best_practices/analyze.html