F.3. aqo
Модуль aqo
представляет собой расширение Postgres Pro Enterprise для оптимизации запросов по стоимости выполнения. Используя методы машинного обучения, а точнее модификацию алгоритма k-NN, aqo
улучшает оценку количества строк, что может способствовать выбору лучшего плана и, как следствие, ускорению запросов.
Модуль aqo
может собирать статистику по всем выполняемым запросам, за исключением запросов, обращающихся к системным отношениям. Если запросы различаются только константами, они считаются относящимися к одному типу. Для каждого типа модуль aqo
сохраняет для машинного обучения качество оценки количества строк, время планирования, время и статистику выполнения. Но основе этих данных aqo
строит новый план выполнения и использует его для следующего запроса того же типа. В тестах aqo
показал значительное увеличение производительности для сложных запросов.
F.3.1. Установка и подготовка
Расширение aqo
включено в состав Postgres Pro Enterprise. Установив Postgres Pro Enterprise, выполните следующие действия, чтобы подготовить aqo
к работе:
Добавьте
aqo
в параметр shared_preload_libraries в файлеpostgresql.conf
:shared_preload_libraries = 'aqo'
Библиотеку
aqo
нужно предварительно загрузить при запуске сервера, так как адаптивная оптимизация запросов должна быть включена для всего кластера. В противном случаеaqo
будет работать только в тех сеансах, в которых вы создадите расширениеaqo
.Создайте расширение
aqo
, выполнив следующий запрос:CREATE EXTENSION aqo;
Когда расширение будет создано, вы можете приступить к оптимизации запросов.
Чтобы отключить aqo
на уровне кластера и удалить всю собранную статистику, выполните:
DROP EXTENSION aqo;
F.3.1.1. Конфигурирование
По умолчанию aqo
не влияет на быстродействие запросов. Чтобы включить адаптивную оптимизацию запросов для базы данных, добавьте переменную aqo.mode
в файл postgresql.conf
и перезапустите кластер. В зависимости от модели использования базы данных вы можете выбрать один из следующих режимов:
intelligent
— в этом режиме выполняется автонастройка запросов на основе статистики, собранной по типам запросов.forced
— в этом режиме предпринимается попытка оптимизировать все новые запросы на общих основаниях, вне зависимости от их типа.controlled
— в этом режиме используется стандартный планировщик для любых новых запросов, но для уже известных типов запросов продолжают использоваться ранее заданные параметры планирования.disabled
— в этом режимеaqo
отключается для всех запросов, даже для запросов известных типов. Этот режим можно использовать для временного отключенияaqo
с сохранением конфигурации и собранной статистики.
Чтобы динамически изменить параметры aqo
в текущем сеансе, выполните следующую команду:
SET aqo.mode = 'режим
';
Здесь режим
— название режима оптимизации, который будет использоваться.
Важно
В режиме intelligent
aqo
может работать не очень хорошо, если в вашей рабочей нагрузке встречаются запросы множества разных типов. В этом случае вы можете попробовать сменить режим оптимизации на controlled
.
F.3.2. Использование
F.3.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
будет продолжать собирать статистику и применять оптимизированные алгоритмы планирования.
Если вы хотите полностью отключить aqo
, вы можете переключить его в режим disabled
. В этом случае для всех запросов будет использоваться стандартный планировщик, но конфигурация и собранная статистика aqo
сохранятся для возможного использования в будущем.
F.3.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.3.2.3. Использование aqo на ведомом сервере
В конфигурациях с ведомыми серверами статистика aqo
на ведомом поступает от ведущего в режиме «только для чтения». Запросы на ведомых не меняют статистику aqo
вне зависимости от текущих параметров aqo
. Если вы хотите изменить статистику на ведомом сервере для одного или нескольких типов запросов, вы должны запустить эти запросы на ведущем, а затем синхронизировать с ним ведомый.
Вы можете определить, будет ли использоваться статистика aqo
для запросов на ведомом, независимо от конфигурации ведущего, переключая режимы aqo
. По умолчанию aqo
оптимизирует запросы на ведомом следующим образом:
В режимах
intelligent
иcontrolled
статистикаaqo
используется только для уже известных типов запросов.В режиме
forced
расширениеaqo
пытается оптимизировать неизвестные запросы, основываясь на общей статистике, собранной в режимеforced
на ведущем (если она там есть). Если общая статистика не найдена,aqo
оставляет оптимизацию стандартному планировщику запросов. Для всех ранее наблюдавшихся типов запросов будет использоваться доступная статистика.В режиме
disabled
использование собранной статистики отключается полностью, и для всех запросов используется стандартный планировщик.
Если вы провели тонкую настройку параметров для определённых типов запросов на ведущем, этим параметры переопределят стандартное поведение aqo
, если только не выбран режим disabled
. За подробностями обратитесь к Подразделу F.3.2.2.
F.3.3. Справка
F.3.3.1. Конфигурационные переменные
F.3.3.1.1. aqo.mode
Определяет режим оптимизации aqo
.
Таблица F.2. Параметры aqo.mode
Значение | Описание |
---|---|
intelligent | Автонастройка запросов на основе статистики, собранной по типам запросов. |
forced | Оптимизация всех запросов на общих основаниях, вне зависимости от их типа. |
controlled | Режим по умолчанию. Для всех новых запросов используется стандартный планировщик, но для уже известных типов запросов может использоваться ранее собранная статистика. |
disabled | Полностью отключает aqo для всех запросов. При этом конфигурация и собранная статистика aqo сохраняется для возможного использования в будущем. |
F.3.3.2. Таблицы
Важно
Вы можете вручную изменить параметры оптимизации в таблице aqo_queries
. Однако модифицировать содержимое других таблиц не следует, так как это может привести к неожиданным результатам.
F.3.3.2.1. Таблица aqo_query_texts
В таблице aqo_query_texts
классифицируются все типы запросов, обрабатываемые aqo
.
Таблица F.3. Таблица aqo_query_texts
Имя столбца | Описание |
---|---|
query_hash | Содержит хеш-значение, однозначно идентифицирующее тип запроса. |
query_text | Содержит пример запроса данного типа. |
F.3.3.2.2. Таблица aqo_queries
Таблица aqo_queries
содержит свойства оптимизации для разных типов запросов.
Таблица F.4. Таблица 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.3.3.2.3. Таблица aqo_data
Таблица aqo_data
содержит данные машинного обучения для уточнения оценки количества строк. Чтобы стереть всю собранную статистику для определённого типа запросов, вы можете удалить из таблицы aqo_data
все строки с соответствующим fspace_hash
.
F.3.3.2.4. Таблица aqo_query_stat
Таблица aqo_query_stat
содержит статистику выполнения запросов, группируемую по типам запросов, Расширение aqo
использует эти данные, когда для определённого типа запросов включено свойство auto_tuning
.
Таблица F.5. Таблица 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.3.4. Автор
Олег Иванов