Поддержка подготовленных запросов

Сведения

Функциональность доступна только для редакций Enterprise и Enterprise для ERP-систем.

Подготовленные запросы (prepared statements) — это механизм оптимизации запросов в системах управления базами данных (СУБД), используемый для повышения производительности и безопасности выполнения SQL-запросов.

Для работы кластера высокой доступности Pangolin на данный момент рекомендована конфигурация с настройкой менеджера пула соединений Pangolin Pooler в транзакционном режиме.

Необходимость использования транзакционного режима обусловлена двумя факторами:

• установкой большого количества подключений со стороны клиентских приложений;
• ограниченностью количества процессов Pangolin, обслуживающих подключения клиентских приложений. Такое ограничение связано с особенностями архитектуры Pangolin.

Исходя из указанного, менеджер пула соединений кластера высокой доступности эксплуатируется в транзакционном режиме, при котором формируются разделенные пулы соединений:

• со стороны клиентских приложений к менеджеру пула соединений;
• со стороны менеджера пула соединений к Pangolin.

А также происходит выделение соединений к Pangolin из пула на время выполнения транзакции, инициированной со стороны клиентского приложения.

Изменения настроек для сессионных переменных, подготовленные запросы (prepared statements), планы запросов и значения связанных переменных для подготовленных запросов сохраняются в контексте процесса, обслуживающего клиентское подключение на Pangolin, которое вызвало установку значения сессионной переменной или подготовку запроса. Подключение не переинициализируется на менеджера пула соединений при смене клиента, использующего данное подключение. Cостояние сохраняется даже при переключении клиентских подключений на одно и то же физическое соединение с сервером.

Pangolin Pooler предоставляет возможность выполнить сброс значений сессионных настроек при передаче соединения с Pangolin другому соединению клиентского приложения, но это приводит к падению производительности и не обеспечивает решения проблемы подготовленных запросов.

Указанное выше делает невозможным использование подготовленных запросов (например, используемых драйвером jdbc) через менеджер пула соединений в транзакционном режиме и приводит к необходимости отключения использования подготовленных запросов на стороне клиентских приложений, использующих jdbc. Это способствует снижению производительности выполнения запросов, которые могли бы выполняться как подготовленные запросы.

Также возможно возникновение коллизий, связанных с изменением драйверами режима транзакций на стороне клиентских приложений read-only/read-write, в соответствии с логикой работы драйверов, и несоответствием ожидаемого режима транзакций фактически установленному в контексте процесса Pangolin, что приводит к ошибкам при выполнении запросов к Pangolin.

Реализованные функции

Реализованные функции в рамках данной функциональности:

• для клиентских приложений:

  • прозрачное использование подготовленных запросов при включенном транзакционном режиме менеджера пула соединений кластера высокой доступности;
  • прозрачное переключение режимов транзакций read-only/read-write при включенном транзакционном режиме менеджера пула соединений кластера высокой доступности;
  • прозрачное переключение значений search_path (восстановление списка используемых схем для клиентской сессии при включенном транзакционном режиме менеджера пула соединений кластера высокой доступности);
  • прозрачное переключение сессионных и текущих ролей при включенном транзакционном режиме менеджера пула соединений кластера высокой доступности;

• повышенная производительность выполнения запросов, которые могут выполняться как подготовленные, относительно режима с отключенными подготовленными запросами (конкретные показатели повышения производительности сильно зависят от сложности подготавливаемых запросов);

• отсутствие необходимости дополнительной настройки параметров драйверов на стороне клиентских приложений.

Ограничения

Функциональность имеет следующие ограничения:

• решение не обеспечивает прозрачности работы с подготовленными запросами при переключении клиентского приложения между серверами кластера высокой доступности Pangolin, то есть запрос, подготовленный на одном сервере не будет доступен к выполнению на другом;
• решение накладывает ограничение на количество одновременно обслуживаемых клиентских сессий и на количество единовременно хранящихся подготовленных запросов в рамках сессии. Ограничение регулируется настроечными параметрами экземпляров Pangolin;
• решение не позволяет хранить и обрабатывать для клиентских сессий значения search_path длиной более чем 1023 байта (задается настроечным параметром);
• при достижении предела количества сессий или подготовленных запросов на сессию, решение будет пытаться вытеснить наиболее старые, по времени создания, экземпляры аналогичных сущностей. При невозможности это сделать – будет отказывать в выполнении операции, в рамках которой производится попытка выполнить добавление нового экземпляра;
• решение не рекомендовано применять для выполнения одинаковых запросов, часто переподготавливаемых с разными именами.

Настройка

Конфигурационные параметры Pangolin Pooler

В Pangolin Pooler реализованы следующие конфигурационные параметры:

Параметр	Описание	Значение
session_region_id	Идентификатор региона для клиентских сессий, генерируемый алгоритмом snowflakeID	Значение по умолчанию – 0. Диапазон значений 0-23
session_worker_id	Идентификатор обработчика для клиентских сессий, генерируемый алгоритмом snowflakeID	Значение по умолчанию – 1. Диапазон значений 0-1023

Примечание

Необходимость отдельного session_worker_id обусловлена возможностью запуска на одном узле нескольких процессов Pangolin Pooler. Для разделения множества идентификаторов между такими процессами используется параметр session_worker_id - свой для каждого процесса Pangolin Pooler узла (session_region_id).

Необходимо учитывать, что каждый узел кластера должен иметь свое значение параметра session_region_id.

Конфигурационные параметры СУБД Pangolin

В СУБД Pangolin доступны следующие конфигурационные параметры:

Параметр	Тип	Описание	Ограничение значения	Значение по умолчанию
shared_prepared_statements	boolean	Управляет включением/выключением функциональности разделяемых подготовленных запросов	on/off	off
max_client_sessions	integer	Максимальное количество клиентских сессий. Рекомендуется устанавливать с двухкратным запасом для «демпфирования» асинхронности удаления данных отключенных сессий	3 - 2147483647	300
max_prepared_statements_per_session	integer	Максимальное количество именованных запросов на клиентскую сессию	3 - 2147483647	25
max_shared_prepared_statements_names	integer	Предел количества хранимых в shared memory уникальных подготовленных запросов	3 - 2147483647	50
max_local_prepared_statements_names	integer	Предел количества локально хранимых в памяти процессов бэкенда «картированных» наименований подготовленных запросов	1 - 2147483647	100
max_local_prepared_statements	integer	Предел количества локально хранимых в памяти процессов бэкенда подготовленных запросов	1 - 2147483647	50
shared_prepared_statements_search_path_length	integer	Максимальная длина значения search_path клиентской сессии, включая нулевой байт окончания строки	16 - 1024	128

Также в СУБД Pangolin доступны следующие функции:

• shared_prepared_statements - признак включения функциональности разделяемых подготовленных запросов;
• get_prepared_statements_list - список общих подготовленных запросов с характеристиками;
• get_prepared_statements_stat - общее количество подготовленных запросов;
• get_prepared_statements_session_stat - количество подготовленных запросов для указанной сессии;
• get_client_sessions_stat - общее количество зарегистрированных клиентских сессий;
• get_client_sessions_sources - список источников клиентских сессий (Pangolin Pooler) с характеристиками;
• get_client_sessions - список клиентских сессий с характеристиками;
• get_session_stmts_list – карта подготовленных запросов (ID клиентской сессии и имя, хеш подготовленного запроса).

Описание функций (входные параметры / возвращаемые значения) и примеры использования представлены в документе «Список PL/SQL функций продукта» раздел «Подготовленные запросы» (документ доступен в личном кабинете).

Карты подготовленных запросов

У подготовленных запросов есть несколько карт:

• локальная карта процесса – идентификаторы сессии и имени запроса на хеш подготовленного запроса;
• локальная карта хеша запроса на план подготовленного запроса;
• карта в общей памяти идентификатора сессии и имени подготовленного запроса на хеш подготовленного запроса;
• карта текстов подготовленных запросов.

Конфигурационные параметры СУБД оказывают влияние на производительность механизма подготовленных запросов (shared_prepared_statements). Если установленная емкость карт недостаточна, приложение столкнется с ограничением размера хранимых подготовленных запросов.

Например, если приложение активно использует 15 разных подготовленных запросов, уникальных по тексту, типам параметров и схеме, рекомендуемый минимальный объем хранилища для карт подготовленных запросов должен составлять минимум 15 элементов. Оптимально предусмотреть некоторый запас, увеличив этот показатель до большего значения. При недостаточном размере карты, новый уникальный подготовленный запрос приведет к ее исчерпанию, вследствие чего приложение выдаст ошибку о переполнении.

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

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

Инициализация режима

Для работы в режиме поддержки подготовленных запросов в транзакционном режиме Pangolin Pooler необходимо выполнить на кластере высокой доступности:

• настройку параметров в postgresql.conf (или postgres.yml в конфигурации с Pangolin Manager) экземпляров БД;
• настройки идентификаторов региона и обработчика в pangolin-pooler.ini экземпляров Pangolin Pooler;
• включение транзакционного режима в pangolin-pooler.ini экземпляров Pangolin Pooler;
• перезапуск экземпляров СУБД Pangolin;
• перезапуск экземпляров компонента Pangolin Pooler.

Управление

Включение функциональности

Для кластера высокой доступности выполните следующие шаги:

1. Настройте конфигурацию СУБД в файле postgres.yml. Установите параметр shared_prepared_statements в значение on:

   shared_prepared_statements = on

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

3. Перечитайте конфигурацию и перезагрузите экземпляр СУБД. Пример команд:

   sudo su - postgres
   /opt/pangolin-manager/bin/pangolin-manager-ctl -c /etc/pangolin-manager/postgres.yml reload clustername
   /opt/pangolin-manager/bin/pangolin-manager-ctl -c /etc/pangolin-manager/postgres.yml restart clustername
   exit

4. Настройте конфигурацию Panolin Pooler (идентификаторы региона и обработчика) в pangolin-pooler.ini экземпляров Pangolin Pooler.

   • Значение session_worker_id, различное для каждого экземпляра Panolin Pooler:

     [pgbouncer]
     ...
     session_worker_id = 11

   • Значение session_region_id, одинаковое для всех узлов кластера высокой доступности:

     [pgbouncer]
     ...
     session_region_id = 3

5. Проверьте включение транзакционного режима в pangolin-pooler.ini экземпляров Pangolin Pooler.

6. Примените настроенные параметры Pangolin Pooler:

   sudo systemctl reload pangolin-pooler
   sudo systemctl restart pangolin-pooler

В решении standalone, для активации режима поддержки подготовленных запросов, выполните следующие шаги:

1. Настройте конфигурацию СУБД в файле postgresql.conf. Установите параметр shared_prepared_statements в значение on:

   shared_prepared_statements = on

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

3. Перечитайте конфигурацию и перезагрузите экземпляра СУБД. Пример команд:

   pg_ctl reload
   sudo systemctl restart postgresql

Отключение функциональности

Для того, чтобы отключить режим поддержки подготовленных запросов в Pangolin, необходимо:

1. Установите параметр СУБД shared_prepared_statements в значение off. Выполните в postgresql.conf или postgres.yml в конфигурации с Pangolin Manager:

   shared_prepared_statements = off

2. Примените измененные параметры:

   • для конфигурации без Pangolin Manager:

     pg_ctl reload
     sudo systemctl restart postgresql

   • для конфигурации c Pangolin Manager:

     sudo su - postgres
     /opt/pangolin-manager/bin/pangolin-manager-ctl -c /etc/pangolin-manager/postgres.yml reload clustername
     /opt/pangolin-manager/bin/pangolin-manager-ctl -c /etc/pangolin-manager/postgres.yml restart clustername
     exit

Описание ролей пользователей

Управление параметрами настройки функциональности выполняется пользователями с правами Администраторов СУБД.

Вызов функций мониторинга и получения статистики по реализованной функциональности выполняется пользователями с правами Администратора СУБД или пользователями, которым делегированы права на вызов таких функций.

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

Интерфейс пользователя

Сохраняется реализованный ранее в ядре Pangolin интерфейс работы с подготовленными запросами:

• выполнение SQL-запросов PREPARE, EXECUTE, DESCRIBE и DEALLOCATE;
• выполнение драйверами запросов расширенного протокола PostgreSQL, в том числе через драйвера jdbc.

Управление размером памяти для подготовленных запросов

Для управления размером потребляемой памяти доступны параметры:

Параметр	Тип	Значение по умолчанию	Описание
plan_cache_lru_memsize	integer	0	Определяет размер потребления пользовательским процессом памяти, используемой для хранения общих планов, выполнения подготовленных с помощью команды PREPARE SQL-запросов (prepared statements). Нулевое значение означает, что память закешированных общих планов подготовленных запросов не ограничивается
plan_cache_lru_size	integer	0	Определяет максимальное количество закешированных общих планов выполнения подготовленных SQL-запросов. Нулевое значение означает, что количество закешированных общих планов подготовленных запросов не ограничивается

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

Сведения

Управление размером памяти для подготовленных запросов доступно только для редакций Enterprise и Enterprise для ERP-систем.

Ввод значения лимита памяти возможен в «B», «kB», «MB», «GB», «TB», при этом введенное значение округляется до «kB».

Параметры недоступны в редакции Pangolin Standard. При установке параметра в системный лог выводится сообщение Force reset "plan_cache_lru_(memsize/size)" due to license agreement с уровнем WARNING.

Запросы, полученные с использованием JDBC-драйвера, выполняются как неименованные подготовленные запросы и также учитываются при проверке лимитов.

Запросы из PL/pgSQL функций выполняются с использованием кешированных планов запросов и также учитываются при проверке лимитов.

Параметры лимитов можно установить на уровне сессии, роли, БД, системы. Значения лимитов будут использоваться в порядке понижения приоритета (RESET устанавливает следующий лимит по приоритету):

• уровень сессии;
• уровень роли;
• уровень БД;
• уровень системы.

Параметры лимитов для роли/БД устанавливаются при старте сессии. Значения на уровне сессии может установить любая роль.

Информацию о сохраненных общих планах выполнения подготовленных запросов текущей сессии можно получить с помощью представленного ниже запроса:

SELECT * FROM pg_backend_memory_contexts WHERE name = 'CachedPlan';

примечание

Внутренняя реализации PostgreSQL такова, что по умолчанию первые 5 раз планировщик выполняет подготовленный запрос по частному плану исполнения. После пятого выполнения планировщик переключится на использование общего плана и только после этого общий план подготовленного запроса сохраняется. Частные планы также кешируются в памяти, но не учитываются в ограничениях размера памяти, так как будут удалены после выполнения запроса, который его использует.

Описание принципов работы и особенностей функциональности

Решение построено на следующих базовых принципах:

• решение предназначено только для работы в транзакционном режиме Pangolin Pooler;

• Pangolin Pooler производит присваивание открываемым клиентским сессиям идентификаторов (используется алгоритм Snowflake ID). Присваиваемые идентификаторы уникальны среди всего множества Pangolin Pooler, работающих в кластере;

• при закрытии клиентского соединения с Pangolin Pooler по первому свободному соединению передается сообщение о закрытии клиентской сессии для удаления ее информации из карт контекста;

• при установлении связи между клиентским соединением и компонентом Pangolin Pooler обязательно передаются идентификатор привязанной клиентской сессии (клиентского соединения) и идентификационный номер текущего статуса контроля (baseline) Pangolin Pooler. Последний нужен для отслеживания случаев падения Pangolin Pooler и удаления информации всех клиентских сессий, уже не актуальных (находящихся ниже baseline);

• при получении сообщения с идентификатором клиентских сессий серверный процесс Pangolin сохраняет контекст (значения сессионных переменных клиентской сессии, см. функцию get_client_sessions) в общей памяти и поднимает значения сессионных переменных новой клиентской сессии, если они есть в общей памяти;

• подготовленный запрос на базовом уровне идентифицируется хешем, вычисленным из указанных выше параметров, и на уровне привязки к клиентским сессиям — сочетанием идентификатора клиентской сессии и имени подготовленного запроса в клиентской сессии, в привязке к хешу запроса;

• при выполнении подготовки запроса по SQL-запросу или сообщению расширенного протокола происходит поиск ранее сохраненного в общей памяти (и на диске) подготовленного запроса с таким же текстом, набором параметров и search_path. Это нужно для избегания многократного хранения больших объемов одинаковой информации о запросах. При отсутствии такого ранее сохраненного запроса происходит его сохранение в файл на диске в директории pg_prep_stats базы данных. Это необходимо, так как тексты запросов могут быть потенциально очень большими. Также происходит регистрация привязки сочетания идентификатора клиентской сессии с именем подготовленного запроса к хешу подготовленного запроса в карте в общей памяти. Для неименованных запросов имя запроса принимается равным плейсхолдеру;

• при необходимости выполнения подготовленного запроса в процессе бэкенда производится следующий порядок действий (предполагается, что в бэкенде уже установлен контекст нужной клиентской сессии):

  1. Производится поиск подготовленного запроса, сохраненного в локальном кеше процесса, для текущей клиентской сессии и указанного имени. Если такой находится, то используется этот запрос. Параметры, влияющие на локальный кеш подготовленных запросов процессов бэкендов:

     • max_local_prepared_statements_names — для карты «клиентская сессия + имя = хеш»;
     • max_local_prepared_statements — для непосредственно уникальных подготовленных запросов;

  2. Если запрос не находится локально, то производится поиск в общей памяти, по карте «клиентская сессия + имя = хеш» и по хешу сам подготовленный запрос. Если не находится, то на этом выполнение подготовленного запроса завершается, так как его нет.

  3. Если запрос нашелся в общей памяти, то он поднимается в кеш процесса бэкенда. Если там нет места — вытесняется, при необходимости, наиболее старая запись из карты имени и наиболее старая запись из карты уникальных подготовленных запросов, на которую нет ссылок из карты имен. Если возможности вытеснения нет, то будет поднята ошибка, поэтому важно правильно оценивать и выставлять значения параметров локального кеша бэкендов.

  4. Если запрос не был найден локально и нашелся только в общей памяти — готовится подготовленный запрос в процессе бэкенда с синтаксическим анализом его структуру. В кеш кладется уже проанализированный и подготовленный запрос. Желательно минимизировать вытеснения уникальных подготовленных запросов, поэтому значения параметра max_local_prepared_statements рекомендуется делать больше, чем ожидаемое количество уникальных подготовленных запросов.

  5. Производится выполнение подготовленного запроса.

• при считывании сохраненного на диске текста запроса производится проверка его целостности и неизменности путем сопоставления с хешем запроса — именем файла. Поэтому не рекомендуется изменять файлы подготовленного запроса вручную – это может нарушить работу системы.