F.3. aqo
Модуль aqo представляет собой расширение Postgres Pro Enterprise для оптимизации запросов по стоимости выполнения. Используя методы машинного обучения, а точнее модификацию алгоритма k-NN, aqo улучшает оценку количества строк, что может способствовать выбору лучшего плана и, как следствие, ускорению запросов.
Модуль aqo может собирать статистику по всем выполняемым запросам, за исключением запросов, обращающихся к системным отношениям. Если запросы различаются только константами, они считаются относящимися к одному классу. Для каждого класса модуль aqo сохраняет для машинного обучения качество оценки количества строк, время планирования, время и статистику выполнения. Но основе этих данных aqo строит новый план выполнения и использует его для следующего запроса того же класса. В тестах aqo показал значительное увеличение производительности для сложных запросов.
Важно
Оптимизация запросов с использованием aqo не поддерживается на ведомых серверах.
Расширение aqo сохраняет все данные обучения (aqo_data), запросы (aqo_query_texts), параметры запросов (aqo_queries) и статистику выполнения запросов (aqo_query_stat) в файлах. При запуске aqo эти данные загружаются в разделяемую память. Вы можете обращаться к данным aqo, используя функции и представления.
Предупреждение
Учтите, что aqo может работать некорректно непосредственно после обновлений расширения, которые изменяют его ядро, и после обновлений Postgres Pro. Поэтому после каждого обновления Postgres Pro следует вызывать функцию aqo_reset()
и выполнять команду DROP EXTENSION aqo
.
F.3.1. Установка и подготовка
Расширение aqo включено в состав Postgres Pro Enterprise. Установив Postgres Pro Enterprise, выполните следующие действия, чтобы подготовить aqo к работе:
Добавьте
aqo
в параметр shared_preload_libraries в файлеpostgresql.conf
:shared_preload_libraries = 'aqo'
Библиотеку aqo нужно предварительно загрузить при запуске сервера, так как адаптивная оптимизация запросов должна быть включена для всего кластера.
Создайте расширение aqo, выполнив следующий запрос:
CREATE EXTENSION aqo;
Когда расширение будет создано, вы можете приступить к оптимизации запросов.
Чтобы отключить aqo на уровне кластера, выполните:
DROP EXTENSION aqo;
Примечание
Когда модуль aqo удаляется и вновь создаётся командами DROP EXTENSION
-> CREATE EXTENSION
, он сохраняет своё внутреннее состояние.
Чтобы удалить все данные из хранилища aqo, включая собранную статистику, вызовите функцию aqo_reset()
.
F.3.1.1. Конфигурирование
По умолчанию aqo не влияет на быстродействие запросов. Чтобы включить адаптивную оптимизацию запросов для базы данных, добавьте переменную aqo.mode
в файл postgresql.conf
и перезапустите кластер. В зависимости от модели использования базы данных вы можете выбрать один из следующих режимов:
intelligent
— в этом режиме выполняется автонастройка запросов на основе статистики, собранной по классам запросов.forced
— в этом режиме собирается статистика по всем запросам, вне зависимости от их класса.controlled
— в этом режиме используется стандартный планировщик для любых новых запросов, но для уже известных классов запросов продолжают использоваться ранее заданные параметры планирования.learn
— в этом режиме собирается статистика по всем выполненным запросам и обновляются данные о классах запросов.frozen
— в этом режиме статистика, собранная для запросов известных классов, используется, но новые данные не собираются. Этот режим можно использовать для уменьшения влияния aqo на время планирования и исполнения запросов.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 будет продолжать собирать статистику и применять оптимизированные алгоритмы планирования.
В режиме learn
собирается статистика по всем выполненным запросам и обновляются данные о классах запросов. Этот режим похож на режим intelligent
, за исключением того, что интеллектуальная настройка на класс запроса не производится.
Если вы хотите уменьшить влияние aqo на время планирования и исполнения запросов, вы можете использовать режим frozen
. В этом режиме статистика, собранная 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;
Для каждого класса запросов хранятся следующие свойства:
queryid
содержит идентификатор запроса, однозначно определяющий класс запроса.learn_aqo
включает сбор статистики для данного класса запросов.use_aqo
включает предсказание количества строк средствами aqo для следующего выполнения данного класса запросов.fspace_hash
содержит уникальный идентификатор пространства признаков, в котором собирается статистика для этого класса запросов. По умолчаниюfspace_hash
равняетсяqueryid
.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 -- для данного класса запросов: SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1, LATERAL aqo_queries_update(q1.queryid, NULL, true, true, false) AS q2 WHERE queryid = (SELECT queryid 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; -- Отключение обучения для прекращения сбора статистики и -- начала использования оптимизированного плана: SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1, LATERAL aqo_queries_update(q1.queryid, NULL, false, true, false) AS q2 WHERE queryid = (SELECT queryid FROM aqo_query_texts WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');
Чтобы предотвратить интеллектуальную настройку для определённого класса запросов, отключите свойство auto_tuning
:
SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1,
LATERAL aqo_queries_update(q1.queryid, NULL, true, true, false) AS q2
WHERE queryid = 'hash
');
Здесь хеш
— это значение хеша для данного класса запросов. В результате aqo не будет автоматически менять свойства learn_aqo
и use_aqo
.
Чтобы отключить дальнейшее обучение для некоторого класса запросов, выполните следующую команду:
SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1,
LATERAL aqo_queries_update(q1.queryid, NULL, false, true, false) AS q2
WHERE queryid = 'hash
');
Здесь хеш
— это значение хеша для данного класса запросов.
Чтобы полностью отключить aqo для всех запросов и использовать стандартный планировщик Postgres Pro, выполните:
SELECT count(*) FROM (SELECT queryid FROM aqo_queries) AS q1, LATERAL aqo_queries_update(q1.queryid, NULL, false, false, false) AS q2 WHERE queryid IN (SELECT queryid FROM aqo_query_texts);
F.3.3. Справка
F.3.3.1. Параметры конфигурации
aqo.mode
Определяет режим работы aqo. Возможные значения перечислены в Таблице F.2.
Таблица F.2. Параметры
aqo.mode
Значение Описание intelligent
Автонастройка запросов на основе статистики, собранной по классам запросов. forced
Собирается статистика по всем запросам, вне зависимости от их класса. controlled
Режим по умолчанию. Для всех новых запросов используется стандартный планировщик, но для уже известных классов запросов может использоваться ранее собранная статистика. learn
Собирается статистика по всем выполненным запросам и обновляются данные о классах запросов. frozen
Используется статистика, собранная для запросов известных классов, но новые данные не собираются, чтобы уменьшить влияние aqo на время планирования и исполнения запросов. disabled
Полностью отключает aqo для всех запросов. При этом конфигурация и собранная статистика aqo сохраняется для возможного использования в будущем. aqo.show_hash
Показывать хеш-значение, вычисленное из дерева запросов и однозначно идентифицирующее класс запросов или класс узлов плана. Начиная с Postgres Pro версии 14, модуль aqo использует в качестве идентификатора класса запроса идентификатор самого Postgres Pro, чтобы обеспечить согласованность с другими расширениями, такими как pg_stat_statements. Таким образом, идентификатор запроса можно получить из поля
Query Identifier
в выводе командыEXPLAIN ANALYZE
.aqo.show_details
Добавить некоторые детали в вывод команды
EXPLAIN
запроса, такие как предсказание или хеш подпространства признаков, и отобразить некоторую дополнительную информацию, специфичную для aqo.aqo.join_threshold
Игнорировать запросы, содержащие количество соединений меньше указанного.
aqo.statement_timeout
Определяет начальное значение так называемого «умного» тайм-аута операторов, который необходим для ограничения времени выполнения при ручном обучении aqo на специальных запросах с неудовлетворительным прогнозом количества строк. Расширение aqo может динамически изменять умный тайм-аут операторов во время этого обучения. По умолчанию оно равно нулю.
aqo.force_collect_stat
Собирать статистику выполнения запросов даже в режиме
disabled
. Хотя никаких предсказаний при этом не делается, добавляются некоторые издержки.aqo.dsm_size_max
Определяет максимальный размер динамической разделяемой памяти, которую модуль aqo может выделить для хранения данных обучения.
aqo.fs_max_items
Определяет максимальное количество пространств признаков, с которыми может работать aqo.
aqo.fss_max_items
Определяет максимальное количество подпространств признаков, с которыми может работать aqo.
aqo.wide_search
Включает поиск соседей с одним и тем же подпространством признаков среди разных классов запросов.
aqo.querytext_max_size
Определяет максимальный размер запроса в представлении aqo_query_texts.
aqo.min_neighbors_for_predicting
Определяет минимальное количество соседей, необходимое для предсказания количества строк. Если их количество меньше указанного значения, aqo не будет делать предсказания.
aqo.predict_with_few_neighbors
Позволяет aqo делать предсказания с меньшим количеством соседей, чем было найдено.
F.3.3.2. Представления
F.3.3.2.1. aqo_query_texts
В представлении aqo_query_texts
классифицируются все классы запросов, обрабатываемые aqo. Для каждого класса запросов в представлении отображается текст первого проанализированного запроса этого класса.
Таблица F.3. Представление aqo_query_texts
Имя столбца | Описание |
---|---|
queryid | Содержит идентификатор запроса, однозначно определяющий класс запроса. |
query_text | Содержит текст первого проанализированного запроса данного класса. |
F.3.3.2.2. aqo_queries
В представлении aqo_queries
отображаются свойства оптимизации для разных классов запросов.
Таблица F.4. Представление aqo_queries
Свойство | Описание |
---|---|
queryid | Содержит идентификатор запроса, однозначно определяющий класс запроса. |
learn_aqo | Включает сбор статистики для данного класса запросов. |
use_aqo | Включает предсказание количества строк средствами aqo для следующего выполнения данного класса запросов. Если модель оценки стоимости неполная, это может привести к замедлению при выполнении запросов. |
fspace_hash | Задаёт уникальный идентификатор отдельного пространства, в котором собирается статистика для данного класса запросов. По умолчанию fspace_hash равняется queryid . Вы можете присвоить ему другой queryid , чтобы оптимизировать разные классы запросов вместе. В результате может сократиться объём памяти для моделей и даже увеличиться скорость запросов. Однако изменение этого свойства может приводить и к неожиданному поведению aqo, так что использовать это следует, только если вы точно понимаете, что делаете. |
auto_tuning | Показывает, будет ли aqo пытаться настроить другие параметры для данного запроса. По умолчанию автонастройка включена в интеллектуальном режиме (intelligent ). |
smart_timeout | Показывает значение «умного» тайм-аута операторов для данного класса запросов. |
count_increase_timeout | Показывает, сколько раз увеличивался «умный» тайм-аут операторов для данного класса запросов. |
F.3.3.2.3. aqo_data
В представлении aqo_data
отображаются данные машинного обучения для уточнения оценки количества строк. Чтобы стереть всю собранную статистику для определённого класса запросов, вы можете удалить из представления aqo_data
все строки с соответствующим fs
.
Таблица F.5. Представление aqo_data
Данные | Описание |
---|---|
fs | Хеш пространства признаков. |
fss | Хеш подпространства признаков. |
nfeatures | Размер подпространства признаков для узла плана запроса. |
features | Логарифм избирательности, на котором основано предсказание количества строк. |
targets | Логарифм количества строк для узла плана запроса. |
reliability | Равнозначно:
|
oids | Список идентификаторов таблиц, которые участвовали в предсказании для этого узла. |
F.3.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.3.3.3. Функции
Модуль aqo добавляет несколько функций в каталог Postgres Pro.
F.3.3.3.1. Функции управления хранилищем
Важно
Функции aqo_queries_update
, aqo_query_texts_update
, aqo_query_stat_update
и aqo_data_update
изменяют представления aqo. Поэтому вызывайте эти функции только в том случае, если вы понимаете логику адаптивной оптимизации запросов.
aqo_cleanup
() →setof integer
Удаляет данные, относящиеся к классам запросов, которые связаны (возможно частично) с удалёнными отношениями. Возвращает количество удалённых пространств признаков (классов) и подпространств признаков. Игнорирует удаление других объектов.
aqo_enable_class
(queryid
bigint
) →void
Устанавливает для
learn_aqo
,use_aqo
иauto_tuning
значение true для данного класса запросов.aqo_disable_class
(queryid
bigint
) →void
Устанавливает для
learn_aqo
,use_aqo
иauto_tuning
значение false для данного класса запросов.aqo_drop_class
(queryid
bigint
) →integer
Удаляет все данные, относящиеся к заданному классу запросов, из хранилища aqo. Возвращает количество записей, удалённых из хранилища aqo.
aqo_reset
() →bigint
Удаляет следующие данные из хранилища aqo: данные машинного обучения, тексты запросов, свойства оптимизации для классов запросов и статистику. Возвращает количество записей, удалённых из хранилища aqo.
aqo_queries_update
(queryid
bigint
,fs
bigint
,learn_aqo
boolean
,use_aqo
boolean
,auto_tuning
boolean
) →boolean
Присваивает новые значения следующим параметрам в представлении aqo_queries для данного класса запросов:
fspace_hash
,learn_aqo
,use_aqo
иauto_tuning
. Значение NULL означает «оставить как есть».aqo_query_texts_update
(queryid
bigint
,query_text
text
) →boolean
Изменяет или добавляет запись в представление aqo_query_texts для данного
queryid
.aqo_query_stat_update
(queryid
bigint
,execution_time_with_aqo
double precision[]
,execution_time_without_aqo
double precision[]
,planning_time_with_aqo
double precision[]
,planning_time_without_aqo
double precision[]
,cardinality_error_with_aqo
double precision[]
,cardinality_error_without_aqo
double precision[]
,executions_with_aqo
bigint[]
,executions_without_aqo
bigint[]
) →boolean
Изменяет или добавляет запись в представление aqo_query_stat для данного
queryid
.aqo_data_update
(fs
bigint
,fss
integer
,nfeatures
integer
,features
double precision[][]
,targets
double precision[]
,reliability
double precision[]
,oids
oid[]
) →boolean
Изменяет или добавляет запись в представление aqo_data для заданных
fs
иfss
.
F.3.3.3.2. Функции управления памятью
aqo_memory_usage
() →setof record
Отображает размеры контекстов памяти и хеш-таблиц aqo.
F.3.3.3.3. Функции аналитики
aqo_cardinality_error
(controlled
boolean
) →setof record
Показывает ошибку оценки количества строк для каждого класса запросов. Если
controlled
имеет значениеtrue
, показывает ошибку оценки для последнего выполнения запроса с включённым aqo. Еслиcontrolled
имеет значение false, возвращает среднюю ошибку оценки количества строк для всех записанных в журнал выполнений запросов с отключённым aqo.aqo_execution_time
(controlled
boolean
) →setof record
Показывает время выполнения для каждого класса запросов. Если
controlled
имеет значение true, показывает время выполнения последнего запроса с включённым aqo. Еслиcontrolled
имеет значение false, возвращает среднее время выполнения запроса для всех записанных в журнал выполнений с отключённым aqo.
F.3.4. Автор
Олег Иванов