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 — в этом режиме используется стандартный планировщик для любых новых запросов, но для уже известных типов запросов продолжают использоваться ранее заданные параметры планирования.

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

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

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

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

Важно

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

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

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

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

Примечание

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

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

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

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

Если вы хотите полностью отключить 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.2.3. Использование aqo на ведомом сервере

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

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

  • В режимах intelligent и controlled статистика aqo используется только для уже известных типов запросов.

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

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

Если вы провели тонкую настройку параметров для определённых типов запросов на ведущем, этим параметры переопределят стандартное поведение aqo, если только не выбран режим disabled. За подробностями обратитесь к Подразделу F.2.2.2.

F.2.3. Справка

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

F.2.3.1.1. aqo.mode

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

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

ЗначениеОписание
intelligentАвтонастройка запросов на основе статистики, собранной по типам запросов.
forcedОптимизация всех запросов на общих основаниях, вне зависимости от их типа.
controlledРежим по умолчанию. Для всех новых запросов используется стандартный планировщик, но для уже известных типов запросов может использоваться ранее собранная статистика.
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. Авторы

Олег Иванов , Postgres Professional, Москва, Россия