F.2. aqo

Модуль aqo представляет собой расширение Postgres Pro Enterprise для оптимизации запросов по стоимости выполнения. Используя методы машинного обучения, а точнее модификацию алгоритма k-NN, aqo улучшает оценку количества строк, что может способствовать выбору лучшего плана и, как следствие, ускорению запросов.

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

F.2.1. Установка и подготовка

Расширение aqo включено в состав Postgres Pro Enterprise. Установив Postgres Pro Enterprise, выполните следующие действия, чтобы подготовить aqo к работе:

  1. Добавьте aqo в параметр shared_preload_libraries в файле postgresql.conf:

    shared_preload_libraries = 'aqo'

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

  2. Создайте расширение aqo, выполнив следующий запрос:

    CREATE EXTENSION aqo;

Когда расширение будет создано, вы можете приступить к оптимизации запросов.

Чтобы отключить aqo на уровне кластера и удалить всю собранную статистику, выполните:

DROP EXTENSION aqo;

F.2.1.1. Конфигурирование

По умолчанию aqo не влияет на быстродействие запросов. Чтобы включить адаптивную оптимизацию запросов для базы данных, добавьте переменную aqo.mode в файл postgresql.conf и перезапустите кластер. В зависимости от модели использования базы данных вы можете выбрать один из следующих режимов:

  • intelligent — в этом режиме выполняется автонастройка запросов на основе статистики, собранной по классам запросов.

  • forced — в этом режиме предпринимается попытка оптимизировать все новые запросы на общих основаниях, вне зависимости от их класса.

  • controlled — в этом режиме используется стандартный планировщик для любых новых запросов, но для уже известных классов запросов продолжают использоваться ранее заданные параметры планирования.

  • learn — в этом режиме собирается статистика по всем выполненным запросам и обновляются данные о классах запросов.

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

Чтобы динамически изменить параметры aqo в текущем сеансе, выполните следующую команду:

SET aqo.mode = 'режим';

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

Важно

В режиме intelligent aqo может работать не очень хорошо, если в вашей рабочей нагрузке встречаются запросы множества разных классов. В этом случае вы можете попробовать сменить режим на controlled.

F.2.2. Использование

F.2.2.1. Выбор режима работы для оптимизации запросов

Если у вас часто выполняются однотипные запросы, например, ваше приложение выдаёт ограниченное число всевозможных классов запросов, вы можете воспользоваться интеллектуальным режимом (intelligent) для улучшения планирования таких запросов. В этом режиме aqo анализирует выполнение каждого запроса и собирает статистику. При этом статистика по разным классам запросов собирается отдельно. Если производительность не увеличивается после 50 итераций, расширение aqo уступает планирование стандартному планировщику запросов.

Примечание

Вы можете просмотреть текущий план запроса, воспользовавшись стандартной командой Postgres Pro EXPLAIN с указанием ANALYZE. За подробностями обратитесь к Разделу 14.1.

Так как в режиме intelligent различные классы запросов анализируются отдельно, aqo может не улучшить производительность, если запросы в рабочей нагрузке постоянно меняются. Для такого динамического профиля нагрузки стоит перевести aqo в режим controlled или попробовать режим forced.

В режиме forced aqo не разделяет собранную статистику по классам запросов и пытается оптимизировать их одинаково. Этот режим может быть полезен для оптимизации нагрузки с множеством различных классов запросов и требует меньше памяти, чем интеллектуальный режим. Но так как в режиме forced не производится интеллектуальная настройка на класс запроса, для некоторых запросов производительность может снизиться. Если вы наблюдаете снижение производительности в этом режиме, переключите aqo в режим controlled.

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

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

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

F.2.2.2. Тонкая настройка aqo

Для обращения к таблицам aqo и изменения расширенных свойств запросов необходимо иметь права суперпользователя.

Работая в интеллектуальном режиме (intelligent), aqo назначает уникальное хеш-значение каждому классу запросов для разделения собираемой статистики. В режиме forced статистика всех ранее ненаблюдаемых классов запросов собирается вместе, в одной записи для общего класса с хешем, равным 0. Просмотреть все обработанные классы запросов и их хеш-значения можно в таблице aqo_query_texts:

SELECT * FROM aqo_query_texts;

Разные классы запросов имеют собственные свойства оптимизации. Эти свойства хранятся в таблице aqo_queries:

SELECT * FROM aqo_queries;

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

  • query_hash содержит хеш-значение, однозначно идентифицирующее класс запроса.

  • learn_aqo включает сбор статистики для данного класса запросов.

  • use_aqo включает предсказание количества строк средствами aqo для следующего выполнения данного класса запросов.

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

  • auto_tuning показывает, будет ли aqo пытаться менять другие параметры для данного запроса. По умолчанию автонастройка включена в интеллектуальном режиме (intelligent).

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

 -- Добавление нового класса запросов в таблицу aqo_queries:

SET aqo.mode='intelligent';
SELECT * FROM a, b WHERE a.id=b.id;
SET aqo.mode='controlled';

 -- Отключение автонастройки, включение learn_aqo и use_aqo 
 -- для данного класса запросов:

UPDATE aqo_queries SET use_aqo=true, learn_aqo=true, auto_tuning=false 
  WHERE query_hash = (SELECT query_hash from aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');

 -- Запуск EXPLAIN ANALYZE и наблюдение изменённого плана:

EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id;
EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id;

 -- Отключение обучения для прекращения сбора статистики и
 -- начала использования оптимизированного плана:

UPDATE aqo_queries SET learn_aqo=false 
  WHERE query_hash = (SELECT query_hash from aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');

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

DELETE FROM aqo_data;

Также можно сбросить статистику для определённого класса запросов, добавив ограничение поля fspace_hash по его хешу. Например:

DELETE FROM aqo_data WHERE fspace_hash = (SELECT fspace_hash FROM aqo_queries 
  WHERE query_hash = (SELECT query_hash from aqo_query_texts 
  WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;'));

Чтобы предотвратить интеллектуальную настройку для определённого класса запросов, отключите свойство auto_tuning:

UPDATE aqo_queries SET auto_tuning=false WHERE query_hash = 'хеш';

Здесь хеш — это значение хеша для данного класса запросов. В результате aqo не будет автоматически менять свойства learn_aqo и use_aqo.

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

UPDATE aqo_queries SET learn_aqo=false WHERE query_hash = 'хеш';

Здесь хеш — это значение хеша для данного класса запросов.

Чтобы полностью отключить aqo для всех запросов и использовать стандартный планировщик PostgreSQL, выполните:

UPDATE aqo_queries SET use_aqo=false, learn_aqo=false, auto_tuning=false;

F.2.3. Справка

F.2.3.1. Конфигурационные переменные

F.2.3.1.1. aqo.mode

Определяет режим оптимизации aqo.

Таблица F.3. Параметры aqo.mode

ЗначениеОписание
intelligentАвтонастройка запросов на основе статистики, собранной по классам запросов.
forcedСобирается статистика по всем запросам, вне зависимости от их класса.
controlledРежим по умолчанию. Для всех новых запросов используется стандартный планировщик, но для уже известных классов запросов может использоваться ранее собранная статистика.
learnСобирается статистика по всем выполненным запросам и обновляются данные о классах запросов.
disabledПолностью отключает aqo для всех запросов. При этом конфигурация и собранная статистика aqo сохраняется для возможного использования в будущем.

F.2.3.2. Таблицы

Важно

Вы можете вручную изменить параметры оптимизации в таблице aqo_queries. Модифицировать содержимое других таблиц следует только при чётком понимании механизмов адаптивной оптимизации.

F.2.3.2.1. Таблица aqo_query_texts

В таблице aqo_query_texts классифицируются все классы запросов, обрабатываемые aqo.

Таблица F.4. Таблица aqo_query_texts

Имя столбцаОписание
query_hashСодержит хеш-значение, однозначно идентифицирующее класс запроса.
query_textСодержит текст первого проанализированного запроса данного класса.

F.2.3.2.2. Таблица aqo_queries

Таблица aqo_queries содержит свойства оптимизации для разных классов запросов.

Таблица F.5. Таблица aqo_queries

СвойствоОписание
query_hashСодержит хеш-значение, однозначно идентифицирующее класс запроса.
learn_aqoВключает сбор статистики для данного класса запросов.
use_aqoВключает предсказание количества строк средствами aqo для следующего выполнения данного класса запросов. Если модель оценки стоимости неполная, это может привести к замедлению при выполнении запросов.
fspace_hashЗадаёт уникальный идентификатор отдельного пространства, в котором собирается статистика для данного класса запросов. По умолчанию fspace_hash равняется query_hash. Вы можете присвоить ему другой query_hash, чтобы оптимизировать разные классы запросов вместе. В результате может сократиться объём памяти для моделей и даже увеличиться скорость запросов. Однако изменение этого свойства может приводить и к неожиданному поведению aqo, так что использовать это следует, только если вы точно понимаете, что делаете.
auto_tuningОпределяет, будет ли aqo пытаться настраивать другие свойства выполнения для данного запроса. По умолчанию автонастройка включена в интеллектуальном режиме. В других режимах новые запросы не добавляются в aqo_queries автоматически. Вы можете изменить это поведение, присвоив переменной auto_tuning значение true.

F.2.3.2.3. Таблица aqo_data

Таблица aqo_data содержит данные машинного обучения для уточнения оценки количества строк. Чтобы стереть всю собранную статистику для определённого класса запросов, вы можете удалить из таблицы aqo_data все строки с соответствующим fspace_hash.

F.2.3.2.4. Таблица aqo_query_stat

Таблица aqo_query_stat содержит статистику выполнения запросов, группируемую по классам запросов. Расширение aqo использует эти данные, когда для определённого класса запросов включено свойство auto_tuning.

Таблица F.6. Таблица aqo_query_stat

ДанныеОписание
execution_time_with_aqoВремя выполнения запросов со включённым aqo.
execution_time_without_aqoВремя выполнения запросов с отключённым aqo.
planning_time_with_aqoВремя планирования запросов со включённым aqo.
planning_time_without_aqoВремя планирования запросов с отключённым aqo.
cardinality_error_with_aqoОшибка оценки количества строк в планах запросов, выбранных со включённым aqo.
cardinality_error_without_aqoОшибка оценки количества строк в планах запросов, выбранных с отключённым aqo.
executions_with_aqoЧисло запросов, выполненных со включённым aqo.
executions_without_aqoЧисло запросов, выполненных с отключённым aqo.

F.2.4. Автор

Олег Иванов