Postgres Pro Enterprise 13

Адаптивная оптимизация

Авторские права

Авторы: Алексей Береснев, Илья Баштанов, Павел Толмачев

Использование материалов курса

Некоммерческое использование материалов курса (презентации,

демонстрации) разрешается без ограничений. Коммерческое

использование возможно только с письменного разрешения компании

Postgres Professional. Запрещается внесение изменений в материалы

курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу:

edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за

любые повреждения и убытки, включая потерю дохода, нанесенные

прямым или непрямым, специальным или случайным использованием

материалов курса. Компания Postgres Professional не предоставляет

каких-либо гарантий на материалы курса. Материалы курса

предоставляются на основе принципа «как есть» и компания Postgres

Professional не обязана предоставлять сопровождение, поддержку,

обновления, расширения и изменения.

Темы

Предназначение AQO

Принцип работы AQO

Режимы работы

Предназначение AQO

Исправление неверных оценок кардинальности,

когда штатные средства не помогают

AQO улучшает оценки в запросах с предикатами вида

атрибут = константа

атрибут > константа

Основная сфера применения — сложные аналитические

запросы

Модуль AQO (Adaptive Query Optimization - Адаптивная Оптимизация

Запросов) можно применить в тех случаях, когда стандартные

инструменты улучшения оценок селективности и кардинальности

(подробно описанные в курсе QPT) не срабатывают.

Модуль AQO работает с простыми условиями вида

«атрибут = константа» или «атрибут > константа».

AQO улучшает оценку количества строк, что может способствовать

выбору лучшего плана и, следовательно, ускорению запросов.

Основная сфера применения AQO — сложные аналитические запросы

с повторяющейся структурой, для которых EXPLAIN ANALYZE

показывает неверную оценку кардинальности.

Сбор статистики

по выполняемым

запросам и улучшение

оценок количества строк

с помощью машинного обучения

Лучшие оценки — основа более оптимальных планов

Предназначение AQO

обучение

текст

запроса

дерево

запроса

дерево

запроса

план

запроса

планирование

разбор переписывание

результат

оценки

кардинальности

исполнение

Модуль AQO собирает статистику по выполненным запросам для

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

оценки. Именно это концептуально отличает AQO от обычного

планировщика, на работу которого никак не влияют ранее выполненные

запросы.

Для оценки кардинальности используется метод машинного обучения

на базе алгоритма поиска ближайших соседей k-NN вместо обычной

статистики планировщика.

стат. признаки

Принцип работы AQO

обучение

(learn_aqo)

дерево

запроса

план

запроса

планирование

результат

использование

(use_aqo)

исполнение

класс

запросов

классификация

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

то есть классифицирован. В один класс попадают все запросы

с одинаковыми деревьями разбора, отличающимися только

константами.

При исполнении плана запроса AQO получает статистическую

информацию (признаки, features) о реальной кардинальности

в разных узлах плана. Эта информация сохраняется в привязке

к соответствующему классу запроса — происходит обучение модели.

Данные обучения сохраняются в файлах и при запуске AQO

загружаются в разделяемую память.

Если запросы некоторого класса уже выполнялись и по ним накоплена

статистическая информация, модель используется для уточнения

оценки планировщика.

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

класса запросов. Например, сначала можно обучать модель для какого-

то проблемного запроса, но не использовать ее оценки (которые могут

поначалу оказаться неточными), а после успешного обучения — начать

использовать, но перестать обучать.

Но если детализация до отдельных классов не требуется, AQO можно

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

рассмотрены ниже.

https://postgrespro.ru/docs/enterprise/13/aqo

Режимы работы

Режим learn

Режим controlled

Режим frozen

Режим intelligent

Режим forced

Режим disabled

Режим learn

Начальное обучение AQO на всех запросах

Обучение и использование оценок для всех классов

learn_aqo = true

use_aqo = true

В режиме learn для всех классов запросов выполняется как обучение,

так и использование уточненных оценок. Статистическая информация

сохраняется отдельно для каждого класса запросов.

Этот режим можно использовать для начального обучения AQO на всех

запросах, но следует иметь в виду, что обычно этого не требуется —

большая часть запросов планируется правильно и без AQO,

а в некоторых случаях применение AQO может и ухудшить

производительность.

Режим controlled

Основной режим для ручного управления AQO

(используется по умолчанию)

Обучение и использование оценок для известных классов

learn_aqo = true

use_aqo = true

Новые классы запросов игнорируются

learn_aqo = false

use_aqo = false

Если в режиме learn AQO обучается и используется для всех запросов,

то в режиме controlled — который установлен по умолчанию — AQO

продолжает обучаться на известных классах запросов и улучшать их

оценки, но игнорирует новые классы запросов.

Этот режим используется по умолчанию и предназначен для ручного

управления обучением и применением AQO на уровне отдельных

классов запросов. Например, можно разрешить AQO только для

некоторых классов запросов (имеющих значительные ошибки оценок

кардинальности), а все остальные запросы будут обрабатываться

обычным образом.

Режим frozen

Как controlled, но без дообучения

Использование имеющихся оценок для известных классов

learn_aqo = false

use_aqo = true

Новые классы запросов игнорируются

learn_aqo = false

use_aqo = false

В режиме frozen статистика, собранная aqo для запросов известных

классов, используется, но новые данные не собираются.

Этот режим позволяет снизить влияние AQO на время планирования

и исполнения запросов, но при изменении данных точность

планирования будет ухудшаться.

Полностью автоматический режим

Решение об обучении и использовании оценок

принимается автоматически

auto_tuning = true

learn_aqo = true false

use_aqo = false true

Режим intelligent

В режиме intelligent выполняется автонастройка параметров обучения

и использования – для всех запросов устанавливается свойство

auto_tuning = true.

Все новые запросы получают свойства learn_aqo = true и use_aqo =

false, то есть статистика по ним собирается, но не используется.

Запросы выполняются несколько раз с использованием AQO и без него.

Чем быстрее запрос выполняется с AQO, тем вероятнее, что

автонастройка включит параметр use_aqo.

Если в режиме intelligent производительность запроса не увеличивается

после 50 итераций, AQO прекращает сбор статистики и перестает

управлять свойствами класса.

Этот режим позволяет использовать AQO в полностью автоматическом

режиме, но точность принимаемых решений может оказаться

неудовлетворительной, поэтому режим следует применять

с осторожностью.

Режим forced

Общая статистика для всех запросов для экономии памяти

запросы принудительно относятся к одному классу

Данные обучения взаимно несовместимы с данными

других режимов

learn_aqo = true

use_aqo = true

В режиме forced не выполняется классификация запросов, а статистика

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

Данные машинного обучения, собранные в любом другом режиме,

неприменимы для режима forced и наоборот.

Режим forced может быть полезен для оптимизации нагрузки

с множеством различных классов запросов, поскольку требует меньше

памяти, чем остальные режимы, разделяющие статистику по классам.

Однако это может приводить и к ухудшению оценок.

Режим disabled

Временное отключение AQO с сохранением собранной

статистики

В режиме disabled отключается использование AQO для всех запросов,

даже для запросов известных классов.

Собранная статистика и свойства, установленные для классов,

сохраняются и могут быть использованы в дальнейшем.

Итоги

Адаптивная оптимизация запросов AQO — инструмент

для улучшения планов методом машинного обучения

Используется для автоматического исправления неверных

оценок кардинальности, которые сложно исправлять

традиционными методами

AQO допускает тонкую настройку на уровне отдельных

классов запросов, имеет несколько режимов для упрощения

администрирования

Практика

1. В базе данных demo создайте индексы для таблицы flights

по столбцу scheduled_departure и для ticket_flights по столбцу

fare_conditions.

2. Отключите параллельное выполнение, увеличьте размер

рабочей памяти до 256 Мбайт и выполните запрос для

получения количества пассажиров, летавших бизнес-классом

с 01.08.2016 и прибывших с опозданием не более часа.

3. Оптимизируйте запрос с помощью AQO.

2. Используйте следующий запрос:

SELECT t.ticket_no

FROM flights f

JOIN ticket_flights tf ON f.flight_id = tf.flight_id

JOIN tickets t ON tf.ticket_no = t.ticket_no

WHERE f.scheduled_departure > '2016-08-01'::timestamptz

AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'

AND tf.fare_conditions = 'Business';

3. Сбросьте статистику AQO и исследуйте план запроса в режиме

обучения (learn).