Исходная страница в нативном Markdown: Производительность / Сохранение планов запросов
Примечание
Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
HTML source:pgpro_11_multiplan.htmlArchive:PGPRO-handouts-16.zipСтраниц PDF: 33
Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.
Page 2: Темы
Модуль pgpro_multiplan
Замороженные планы
Песочница
Планы с шаблонами
Базовые планы
Совместная работа с перепланированием
Page 3: Модуль pgpro_multiplan
Стабилизация планов выполнения запросов
заморозка плана для конкретного запроса или группы запросов
разделяемый кеш замороженных планов
планы сохраняются в файлах сервера,
для репликации: pgpro_multiplan.wal_rw
Заменяет устаревшее расширение sr_plan
Комментарии преподавателя
Расширение pgpro_multiplan позволяет «заморозить» определенный план выполнения запроса (или привязать план к целой группе запросов) и при дальнейшем исполнении запроса использовать этот план вместо того, который построил бы планировщик. Стабилизация некоторых планов полезна, например, при обновлении сервера (в новых версиях планировщик в среднем работает лучше, но планы небольшой части запросов могут измениться и в худшую сторону), или для оптимизации отдельного запроса, когда не удается иными средствами убедить планировщик построить нужный план.
Замороженные планы сохраняются в файлы в отдельном каталоге, а работа с ними происходит в разделяемой памяти. Таким образом, в отличие от обычного локального кеша разобранных запросов и их планов, кеш замороженных планов — общий для всех сеансов.
Планы хранятся в файлах, а не в таблицах БД, чтобы обеспечить совместимость расширения с системой 1С. Обратной стороной такого решения является то, что замороженные планы не передаются с мастера на реплику автоматически: если это необходимо, требуется включить параметр pgpro_multiplan.wal_rw на обоих серверах.
До версии Postgres Pro Enterprise 16 включительно в продукте присутствует расширение sr_plan с аналогичным функционалом. Начиная с версии 15, оно считается устаревшим и вместо него рекомендуется использовать расширение pgpro_multiplan.
зависит от системного каталога: OID, структуры таблиц и т. п.
действует только в текущей базе данных
планирование не требуется
Набор указаний — hintset
хранятся указания расширения pg_hint_plan
запрос планируется с учетом указаний
Комментарии преподавателя
План может сохраняться в двух вариантах.
Сериализованный план — это дерево плана, представленное в виде текстовой строки.
Сериализованный план действителен только в текущей базе данных, он зависит от идентификаторов объектов, структуры таблиц и индексов. Например, если пересоздать используемую в плане таблицу (даже с тем же именем), то сохраненный план станет недействительным.
Преимущество сериализованных планов состоит в том, что запрос при выполнении не требуется планировать.
Набор указаний — хранится как указания расширения pg_hint_plan. Для этого требуется, чтобы было установлено и само расширение pg_hint_plan, иначе указания будут проигнорированы. В таком случае план не зависит от идентификаторов объектов. Запрос при выполнении приходится планировать, но указания подсказывают планировщику, какой именно план требуется получить.
Первый из режимов работы расширения — заморозка плана — состоит из трех этапов: регистрации запроса, построения плана и собственно заморозки.
Зарегистрировать запрос можно одним из двух способов:
● функцией pgpro_multiplan_register_query, в которую необходимо передать текст запроса (можно в параметризованном виде) и список типов параметров (можно доверить расширению автоматически определить типы);
● с помощью автоматической регистрации запросов (параметр pgpro_multiplan.auto_tracking, по умолчанию выключен).
При этом запрос идентифицируется двумя хеш-кодами:
● sql_hash — вычисляется по дереву разбора без учета параметров и констант (тот же хеш-код используется, например, в расширении pg_stat_statements; требуется включенный compute_query_id);
● const_hash — вычисляется по типам и значениям констант.
Далее запрос планируется, при этом допускается влиять на план любыми доступными способами: параметрами оптимизатора, с помощью расширений AQO или pg_hint_plan и т. п.
На последнем шаге необходимо заморозить последний выполненный план с помощью функции pgpro_multiplan_freeze. После этого при выполнении зарегистрированного запроса будет обеспечиваться применение замороженного плана. Замороженный план будет применяться только к точно такому же запросу, для которого замораживался план.
По умолчанию функционал расширения pgpro_multiplan выключен. Включим его для текущего сеанса: установим режим frozen, позволяющий использовать замороженные планы.
=> SET pgpro_multiplan.mode = frozen;
SET
Запрос, выбирающий половину бронирований, использует индексный доступ:
QUERY PLAN
---------------------------------------------
Index Scan using bookings_pkey on bookings
Index Cond: (book_ref > '800000'::bpchar)
(2 rows)
Допустим, мы считаем, что последовательное сканирование было бы эффективнее. Воспользуемся расширением, и сначала зарегистрируем интересующий нас запрос (используем не константу, а параметр):
=>SELECT*FROMpgpro_multiplan_register_query('SELECT * FROM bookings WHERE book_ref > $1;');
QUERY PLAN
--------------------------------------------------
Custom Scan (MultiplanScan)
Plan is: tracked
SQL hash: 3221999367686871845
Const hash: 0
Plan hash: 0
-> Index Scan using bookings_pkey on bookings
Index Cond: (book_ref > $1)
(7 rows)
QUERY PLAN
---------------------------------
Custom Scan (MultiplanScan)
Plan is: frozen, serialized
SQL hash: 3221999367686871845
Const hash: 0
Plan hash: 0
-> Seq Scan on bookings
Filter: (book_ref > $1)
(7 rows)
Теперь сервер использует замороженный план: Plan is: frozen, serialized.
Информацию о замороженных планах можно увидеть в представлении pgpro_multiplan_storage:
План можно заморозить и с типом hintset, то есть в виде указаний, сформированных с помощью расширения pg_hint_plan.
Добавим расширение pg_hint_plan в текущую базу данных:
Page 8: Page 8
=>CREATEEXTENSIONpg_hint_plan;
CREATEEXTENSION
В начале этой демонстрации мы уже добавили библиотеку этого расширения в shared_preload_libraries.
Следующий запрос использует последовательное сканирование:
QUERY PLAN
------------------------------------------------------------------------------------------
Custom Scan (MultiplanScan) (cost=0.42..64454.01 rows=55256 width=32)
Plan is: tracked
SQL hash: -8587368766006511412
Const hash: 1941396791
Plan hash: 0
-> Index Scan using ticket_flights_pkey on ticket_flights (cost=0.42..64454.01 rows=.
.55256 width=32)
Index Cond: (flight_id < 1000)
(7 rows)
Заморозим план с типом hintset:
=>SELECTpgpro_multiplan_freeze('hintset');
pgpro_multiplan_freeze
------------------------
t
(1 row)
И проверим план — в нем без указаний pg_hint_plan используется сканирование индекса:
QUERY PLAN
------------------------------------------------------------------------------------------
Custom Scan (MultiplanScan) (cost=0.42..64454.01 rows=55256 width=32)
Plan is: frozen, hintset
SQL hash: -8587368766006511412
Const hash: 1941396791
Plan hash: 0
-> Index Scan using ticket_flights_pkey on ticket_flights (cost=0.42..64454.01 rows=.
.55256 width=32)
Index Cond: (flight_id < 1000)
(7 rows)
На этот раз замена константы приводит к запросу, для которого план не замораживался, поэтому планировщик выбирает план с последовательным сканированием:
Поддерживается перенос планов в основное хранилище
ручное копирование из представления pgpro_multiplan_storage
данные не реплицируются из песочницы на ведомый сервер
Комментарии преподавателя
Расширение pgpro_multiplan позволяет задействовать для хранения замороженных планов песочницу: отдельную, независимую от основного хранилища область разделяемой памяти и каталог на диске. Переключением на альтернативное хранилище планов управляет параметр pgpro_multiplan.sandbox (по умолчанию он выключен). Менять значение этого параметра могут только суперпользователи.
И к основному хранилищу, и к песочнице существует только один общий интерфейс доступа — представление pgpro_multiplan_storage.
План, сохраненный в песочнице, можно перенести в основное хранилище. Для этого необходимо сохранить данные из представления pgpro_multiplan_storage в отдельную таблицу, затем отключить песочницу и восстановить данные в основное хранилище, используя функцию pgpro_multiplan_restore.
Данные из песочницы не реплицируются на ведомый сервер.
Page 11: Песочница
Включим параметр pgpro_multiplan.sandbox, чтобы переключиться на альтернативное хранилище:
=> SET pgpro_multiplan.sandbox = on;
SET
Теперь в представлении pgpro_multiplan_storage пусто, так как в песочнице пока нет ни одного плана:
QUERY PLAN
-----------------------------------------------------
Index Scan using tickets_pkey on tickets
Index Cond: (ticket_no = '0005432001001'::bpchar)
(2 rows)
Зарегистрируем запрос:
=>SELECT*FROMpgpro_multiplan_register_query('SELECT * FROM tickets WHERE ticket_no = $1;');
План с шаблонами (template) хранится в виде набора указаний, как и план с указаниями (hintset). Отличие состоит в том, что такой план применяется ко всех запросам, совпадающим с исходным с точностью до имен таблиц.
Говоря точнее, запрос идентифицируется двумя хеш-кодами:
● sql_hash — построен по дереву разбора,
● table_hash — построен по именам таблиц, не соответствующим регулярному выражению из параметра pgpro_multiplan.wildcards.
Константы (const_hash) не учитываются. Значение параметра pgpro_multiplan.wildcards сохраняется вместе с замороженным планом (table_hash не сохраняется, а вычисляется при необходимости).
План с шаблонами позволяет применять один и тот же план к запросам с одинаковой структурой, но разными названиями таблиц (например, запросы к временным таблицам, генерируемые системой 1С).
Такой план применяется, только если не нашлось точного соответствия (плана с типом serialized или hintset).
QUERY PLAN
----------------------------------------------------------
Index Scan using t1_bookings_book_ref_idx on t1_bookings
Index Cond: (book_ref > '800000'::bpchar)
(2 rows)
Укажем для параметра pgpro_multiplan.wildcards регулярное выражение:
=> SET pgpro_multiplan.wildcards = '^t[0-9]\_bookings';
SET
Этому регулярному выражению соответствуют имена, состоящие из символа t, цифры и суффикса _bookings — как у наших временных таблиц:
=>SELECT't1_bookings'~'^t[0-9]\_bookings';
?column?
----------
t
(1 row)
Поэтому расширение не будет различать запросы к ним.
Снова временно отключим индексный доступ:
=> SET enable_indexscan = off;
SET
Page 16: Page 16
=> SET enable_bitmapscan = off;
SET
Зарегистрируем запрос к таблице t1_bookings:
=>SELECT*FROMpgpro_multiplan_register_query('SELECT * FROM t1_bookings WHERE book_ref > ''800000'';');
В столбце hintstr находится набор указаний для pg_hint_plan.
Вернем оптимизатору все возможности для построения планов:
=> RESET enable_indexscan;
RESET
=> RESET enable_bitmapscan;
RESET
Второй запрос теперь тоже выполняется с помощью последовательного сканирования, поскольку он совпадает с первым с точностью до имен таблиц, соответствующих шаблону. Константы не учитываются, несмотря на то, что запрос регистрировался без параметров:
Базовые планы применяют, когда для одного запроса может существовать несколько удачных планов. Если планировщик выбрал один из базовых планов, запрос выполняется с этим планом. В противном случае расширение принудительно устанавливает для запроса самый дешевый из существующих базовых планов. Базовые планы рассматриваются, только если не нашлось замороженного плана (с типом serialized, hintset или template).
Базовые планы идентифицируются двумя хеш-кодами:
● sql_hash — вычисляется так же, как и для замороженных планов;
● plan_hash — внутренний идентификатор плана.
Константы не учитываются, а const_hash всегда равен 0.
Чтобы добавить план как базовый, можно действовать, как в случае с заморозкой: зарегистрировать запрос, выполнить планирование и заморозить план функцией pgpro_multiplan_freeze с аргументом ‘baseline’.
Другой вариант — использовать автозахват, установив параметр pgpro_multiplan.auto_capturing. В этом случае все выполняющиеся запросы захватываются и их планы становятся кандидатами на сохранение. Захваченные запросы с планами видны в представлении pgpro_multiplan_captured_queries. Захваченный план можно одобрить, вызвав функцию pgpro_multiplan_captured_approve, после чего он попадает в общее хранилище и будет применяться.
Теперь добавим baseline к списку режимов расширения, чтобы планировщик выбирал только одобренные базовые планы. Добавим также режим statistics, чтобы включить сбор статистики:
=> SET pgpro_multiplan.mode = 'frozen,wildcards,baseline,statistics';
SET
Page 21: Page 21
Доступ к статистике можно получить через представление pgpro_multiplan_stats. Пока оно пусто, поскольку сбор статистики был отключен:
QUERY PLAN
-----------------------------------------------
Custom Scan (MultiplanScan)
Plan is: baseline
SQL hash: 3221999367686871845
Const hash: 0
Plan hash: -395657591727867684
-> Seq Scan on bookings
Filter: (book_ref > '800000'::bpchar)
(7 rows)
Вместо индексного сканирования используется план с последовательным сканированием, как наиболее дешевый из одобренных.
Но планировщик имеет право выбрать и другой одобренный план:
QUERY PLAN
---------------------------------------------------------
Custom Scan (MultiplanScan)
Plan is: baseline
SQL hash: 3221999367686871845
Const hash: 0
Plan hash: -6676099641389655070
-> Bitmap Heap Scan on bookings
Recheck Cond: (book_ref > '777777'::bpchar)
-> Bitmap Index Scan on bookings_pkey
Index Cond: (book_ref > '777777'::bpchar)
(9 rows)
Обратите внимание, что константы при идентификации запроса и плана не учитываются.
Посмотрим на собранную статистику. Каждый из двух планов использовался по одному разу:
Если используется расширение pgpro_stats, о котором рассказывается в следующей теме, с его помощью можно собирать более детальную статистику планирования и выполнения запросов. Получить к ней доступ можно с помощью представления pgpro_stats_statements.
Восстановим значения параметров:
Page 22: Page 22
=> RESET enable_seqscan;
RESET
=> RESET enable_indexscan;
RESET
Page 23: Совместная работа
Если план неадекватен, срабатывает условие перепланирования
Расширение pgpro_multiplan перехватывает план и сохраняет его как одобренный или базовый
replan_enable
pgpro_multiplan.aqe_mode
автоматическое сохранение новых планов
индивидуальные значения триггеров перепланирования
сбор статистики перепланирования в реальном времени
Комментарии преподавателя
Сервер может совместно использовать перепланирование и сохранение планов.
Если стандартный планировщик ошибается и срабатывает одно из условий перепланирования, запрос прерывается и отправляется на повторное планирование. Расширение pgpro_multiplan может захватить и одобрить новый план, а при последующих вызовах запроса сразу подставить сохраненный качественный план. Система больше не тратит ресурсы на перепланирование этого конкретного запроса — план стабилен.
Для интеграции нужно включить параметр replan_enable, а параметр pgpro_multiplan.aqe_mode задает список интегрируемых возможностей:
● auto_approve_plan — управление планами в реальном времени.
Если срабатывает триггер перепланирования, расширение pgpro_multiplan сможет автоматически выбрать новый план, одобрить его и сохранить в основное хранилище для последующего использования.
● individual_triggers — индивидуальные триггеры перепланирования для отдельных запросов.
● statistics — сбор статистики перепланирования в реальном времени.
QUERY PLAN
------------------------------------------------------------------------------------------
Gather (cost=1000.42..18693.22 rows=38 width=136) (actual rows=30133 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=0.42..17689.42 rows=16 width=136) (actual rows=10044 loops=3)
-> Parallel Seq Scan on ticket_flights tf (cost=0.00..17554.38 rows=16 width=3.
.2) (actual rows=10044 loops=3)
Filter: ((amount > '50000'::numeric) AND ((fare_conditions)::text = 'Busin.
.ess'::text) AND (length(ticket_no) = 13))
Rows Removed by Filter: 338531
-> Index Scan using tickets_pkey on tickets t (cost=0.42..8.44 rows=1 width=10.
.4) (actual rows=1 loops=30133)
Index Cond: (ticket_no = tf.ticket_no)
Planning Time: 30.136 ms
Execution Time: 307.999 ms
(11 rows)
Из-за большой ошибки в оценке кардинальности предложенный оптимизатором план вряд ли будет наилучшим.
Включим перепланирование и зададим триггер, указав коэффициент допустимой ошибки в узле плана:
=> SET replan_enable = true;
SET
=> SET replan_overrun_limit = 2;
SET
=> SET replan_show_signature = true;
SET
Включим автоматическое одобрение планов:
=> SET pgpro_multiplan.aqe_mode = 'auto_approve_plans';
SET
Префикс aqe_ в именах параметров отражает тот факт, что с 17-й версии Postgres Pro Enterprise перепланирование запросов называется AQE (adaptive query execution).
Запрос был перепланирован в реальном времени (Replanning Attempts: 1). Обратите внимание, что оценки кардинальности улучшились и план изменился.
Заглянем в хранилище планов:
План был захвачен и одобрен как базовый, указания в столбце hintstr предписывают использовать последовательное сканирование и соединение хешированием.
Теперь, когда мы получили адекватный план, отключим перепланирование:
=> RESET replan_enable;
RESET
Отключим автоматическое одобрение и разрешим использование сохраненных базовых планов:
=> RESET pgpro_multiplan.aqe_mode;
RESET
=> SET pgpro_multiplan.mode = 'baseline';
Page 26: Page 26
SET
При следующих выполнениях запроса время планирования заметно сократится — используется сохраненный план:
Расширение pgpro\_multiplan позволяет стабилизировать выполнение запросов, сохраняя план или набор указаний оптимизатору
Поддерживаются несколько вариантов сопоставления запросов с планами
Дополнительное хранилище планов удобно для отладки
Расширение интегрировано с перепланированием запросов
Page 28: Практика
Получите план выполнения запроса для подсчета количества пассажиров, летевших рейсами, которые опоздали на время от одной минуты до четырех часов и с вылетом, и с прибытием.
Включите автоматическое одобрение базовых планов, предварительно переключив pgpro_multiplan на песочницу. Оптимизируйте запрос с помощью перепланирования в реальном времени.
Отключите перепланирование и сохранение планов. Проверьте, что для запроса продолжает использоваться одобренный план.
Отключите режим песочницы и убедитесь, что используется исходный неоптимизированный план.
Комментарии преподавателя
Используйте запрос из темы «Подходы к настройке» курса QPT-16:
{#source-figure-005 caption=“Source slide page 5: Заморозка плана”}
{#source-figure-014 caption=“Source slide page 14: Планы с шаблонами”}
{#source-figure-018 caption=“Source slide page 18: Базовые планы”}