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

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

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

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

Гнатюк

Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

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

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

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

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

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

курса.

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

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

edu@postgrespro.ru

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

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

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

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

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

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

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

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

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

Темы

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

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

Обучение без классификации

Классификация запросов

Песочница

Модуль адаптивной оптимизации запросов AQO (Adaptive Query

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

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

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

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

«атрибут = константа», «атрибут > константа» и подобными.

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

на коэффициент коррекции AQO. Это может способствовать выбору

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

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

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

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

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

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

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

отклонение кардинальности от вычисленной планировщиком

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

атрибут оператор-сравнения константа

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

запросы

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

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

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

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

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

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

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

обучение

текст

запроса

дерево

запроса

дерево

запроса

план

запроса

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

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

результат

оценки

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

исполнение

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

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

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

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

запросы.

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

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

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

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

для узлов плана

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

обучение

(learn_aqo)

дерево

запроса

план

запроса

результат

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

(use_aqo)

классы

запросов

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

aqo.enable

aqo.mode

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

aqo.advanced = on

Расширение AQO включается параметром aqo.enable.

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

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

каждого узла плана — происходит обучение модели. Полученные

данные сохраняются в файлах и при запуске AQO загружаются

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

Когда статистическая информация накоплена, модель может

использоваться для уточнения оценки планировщика.

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

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

и к другим планам с теми же узлами.

Если установить параметру aqo.advanced значение on, выполняется

классификация: в один класс попадают запросы, отличающиеся только

константами. В таком случае статистика будет собираться и

применяться для каждого класса отдельно.

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

работы (параметр aqo.mode).

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

Без классификации

Режим learn

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

начальное обучение

Режим frozen

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

стабилизация

Если классификация не применяется (aqo.advanced = off), все запросы

относятся к одному общему классу. В этом случае имеют смысл два

режима работы: learn и frozen.

В использующемся по умолчанию режиме learn модель обучается на

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

планов.

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

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

варьируя константы, до тех пор, пока не будет достигнуто необходимое

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

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

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

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

Затем расширение переводится в режим frozen, в котором собранная

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

собираются.

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

на время планирования запросов. Конечно, при дальнейшем изменении

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

можно на время вернуться в режим обучения, чтобы обновить

статистику.

Классификация запросов

Режимы learn и frozen

Режим controlled

статистика применяется (use_aqo),

но обновляется (learn_aqo) только для известных запросов

стабилизация без «заморозки»

Режим intelligent

автоматическое управление обучением и применением (auto_tune)

сначала только сбор статистики;

применение при реальном улучшении, иначе — отказ от использования

Тонкая настройка

ручное управление свойствами use_aqo, learn_aqo, auto_tune

При включенной классификации (aqo.advanced = on) статистика

собирается отдельно для каждого класса запросов; класс составляют

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

В этом случае к уже рассмотренным режимам работы learn и frozen

добавляются еще два: controlled и intelligent (без классификации они не

отличаются от обычного learn).

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

запросов: она продолжает обучаться на них (свойство learn_aqo) и

используется для уточнения оценок (use_aqo). Но для новых классов

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

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

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

В режиме intelligent расширение самостоятельно управляет обучением

и использованием (свойство auto_tune). Новые запросы сначала

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

использоваться (включается свойство use_aqo). Далее модель может

выключаться или включаться в зависимости от того, уменьшает ли ее

использование время планирования и выполнения запроса. Если после

50 итераций улучшения не происходит, AQO перестает собирать

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

(выключает свойства use_aqo и learn_aqo).

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

принимаемые решения могут оказаться недостаточно точными.

Свойствами use_aqo, learn_aqo и auto_tune можно управлять вручную

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

Песочница

Отдельная база знаний

в общей памяти сервера

изолирована от основной базы

общая для всех сеансов, где включена (aqo.sandbox = on)

Позволяет

экспериментировать с AQO, не влияя на основную активность

работать на физической реплике

В AQO есть возможность организовать дополнительную базу знаний,

не связанную с основной — песочницу.

На сервере существует единственная песочница, которая

располагается в его общей памяти. Во всех сеансах, в которых задано

значение aqo.sandbox = on, расширение AQO будет работает не

с основной базой знаний, а с общей песочницей.

Это дает возможность экспериментировать с настройками, не влияя на

выполняющиеся запросы пользователей, а затем переносить

статистику в основную базу знаний.

Также песочница позволяет обучать AQO на запросах, выполняющихся

на физической реплике. По умолчанию (если aqo.wal_rw = true)

изменения основной базы знаний записываются в WAL и передаются на

реплику. Поэтому запросы на реплике могут использовать статистику

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).