Расширяемость

Пул соединений

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

Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк

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

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

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

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

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

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

курса.

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

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

edu@postgrespro.ru

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

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

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

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

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

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

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

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

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

Темы

Для чего используется пул соединений

Пул соединений в общей архитектуре системы

Доступные варианты, PgBouncer

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

Вопросы аутентификации

Управление пулом

Особенности разработки при наличии пула

Подготовка соединений

Один клиент, один процесс

клиент

PostgreSQL

Создание соединения обходится дорого

аутентификация, наполнение кешей

Большое количество соединений замедляет работу сервера

Каждое соединение расходует память

процесс

Вспомним архитектуру PostgreSQL. Когда клиент подключается

к серверу, для него создается отдельный обслуживающий процесс,

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

количество обслуживающих процессов на сервере равно количеству

клиентов.

При большом количестве клиентов это вызывает проблемы:

●

Создание соединений обходится дорого, в основном из-за того, что

установка соединения требует аутентификации (особенно в случае

шифрованных соединений) и наполнения кешей (например, кеша

системного каталога).

●

Большое количество процессов замедляет работу сервера.

Основная причина в том, что для создания снимка данных (а это

частая операция) приходится просматривать список всех имеющихся

процессов. И такой просмотр требует блокировки внутренних

структур данных.

●

Каждый процесс занимает определенную часть памяти сервера.

Максимальное количество соединений, не вызывающее сложностей,

зависит от многих факторов (наличие свободной памяти, количество

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

тысяч.

Пул соединений

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

Менеджер пула соединений

открывает и удерживает несколько соединений с сервером

для клиентов выглядит как сервер PostgreSQL,

для сервера PostgreSQL выглядит как клиент

клиент

PostgreSQL

пул

процесс

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

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

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

решить упомянутые проблемы. (Можно провести некоторую аналогию

с передачей данных по сети: между абонентами нет выделенного

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

имеющиеся каналы связи.)

Чтобы одновременно работать с большим количеством клиентов,

используется пул соединений.

Специальная программа — менеджер пула — открывает и удерживает

некоторое (небольшое) количество соединений с сервером БД. Для

сервера БД все выглядит так, как будто имеются несколько

долгоживущих соединений.

Клиенты подключаются не к серверу БД, а к менеджеру пула. Для этого

он реализует тот же клиентский протокол, что и PostgreSQL,

«притворяясь» для клиентов базой данных. Клиентские запросы

переадресуются серверу БД, используя одно из имеющихся свободных

соединений.

Место в архитектуре

клиент

PostgreSQL

На сервере приложения

клиент использует быстрое локальное подключение

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

пул

процесс

Можно по-разному встроить пул соединений в архитектуру

информационной системы.

Один вариант — разместить менеджер пула на сервере приложения.

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

или драйвером PostgreSQL, хотя при необходимости можно

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

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

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

с сервером БД перестает играть роль, поскольку выполняется только

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

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

с СУБД в зависимости от нагрузки, но идея остается той же самой —

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

Место в архитектуре

клиент

PostgreSQL

На сервере баз данных

если используется несколько серверов приложений

Встроенного пула соединений нет

пул

процесс

клиент

С другой стороны, если имеется несколько серверов приложений,

то пул соединений имеет смысл устанавливать на сервере БД — иначе

будет сложно ограничить количество соединений.

При этом в PostgreSQL пока нет встроенного пула соединений, нужно

использовать сторонние решения.

Место в архитектуре

клиент

PostgreSQL

И на серверах приложений, и на сервере баз данных

полезные свойства комбинируются

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

пул

процесс

клиент

пул

Вообще говоря, в этом случае может иметь смысл использовать

менеджеры пулов и на серверах приложений, и на сервере БД, чтобы

одновременно и сократить время подключения, и ограничить

количество соединений.

Следует учитывать, что каждый дополнительный узел на пути от

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

но они, как правило, незначительны.

Доступные продукты

PgBouncer

стандарт де-факто

используем далее как пример

Pgpool-II

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

Odyssey

многопоточный, высокопроизводительный

и др.

Как уже говорилось, многие, если не все, серверы приложений и просто

драйверы, реализующие клиент-серверный протокол PostgreSQL,

предоставляют возможность организации пула соединений на

клиентской стороне.

Как уже говорилось, встроенного менеджера пула в PostgreSQL пока

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

менеджеры — проекты с открытым исходным кодом.

PgBouncer (https://www.pgbouncer.org/) используется очень широко

и является стандартом де-факто. Это легкий пул соединений, без

дополнительных функций. Именно эту программу мы будем

рассматривать дальше в этой теме.

Pgpool-II (https://www.pgpool.net) разрабатывает компания SRA OSS

(Япония). Это не только пул соединений, но и балансировщик нагрузки.

Он реализует интересный функционал (например, анализ трафика для

того чтобы отличать пишущие транзакции от только читающих).

Odyssey (https://github.com/yandex/odyssey) — пул соединений от

компании Яндекс. Основное преимущество состоит в многопоточной

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

Это, конечно, не полный список.

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

«Пул сеансов»

может иметь смысл только для коротких сеансов

«Пул транзакций»

разные транзакции одного клиентского сеанса

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

основной режим

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

«Пул операторов»

запрещает транзакции, состоящие более чем из одного оператора

Новый пул соединений создается для каждой пары

«база данных — роль»

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

В режиме «пул сеансов» клиенту предоставляется выделенное

соединение из числа доступных. Этот режим полезен только в случае

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

транзакцию и тут же отключается. Например, это может быть система

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

Режим «пул транзакций» является наиболее универсальным и

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

транзакции. Такой режим полезен и в случае, когда клиент

устанавливает долгоживущее соединение. Но из-за такого режима

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

при разработке приложений. Мы поговорим о них немного позже.

PgBouncer имеет еще один режим — «пул операторов», но он

фактически бесполезен, поскольку просто запрещает выполнение

транзакций, состоящих более чем из одного оператора (работает

только с режимом автофиксации).

Любое соединение с сервером всегда выполняется к определенной

базе данных и под определенной ролью. Поэтому на каждую пару

«база данных — роль» менеджер пулов создает новый пул соединений.

Из-за этого схема с пулом соединений вряд ли имеет смысл, если

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

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

(с аутентификацией конечных пользователей на уровне приложения).

Аутентификация

Менеджер пула аутентифицирует соединения клиентов,

сервер баз данных аутентифицирует соединения пула

требуется специальная настройка

можно использовать файл формата pg_hba.conf

клиент

PostgreSQL

пул

процесс

Известно, что аутентификацию выполняет сервер БД. Но при наличии

пула соединений нельзя полагаться только на нее.

Поскольку в данном случае менеджер пула является точкой входа для

клиентов, он сам должен выполнять аутентификацию аналогично тому,

как это делает сервер БД. Иначе любой клиент сможет получить

несанкционированный доступ к уже открытому соединению (которое

было аутентифицировано сервером БД один раз при открытии).

Таким образом, схема получается следующая:

1. Менеджер пула выполняет аутентификацию входящего соединения.

Для этого ему требуются настройки, более или менее аналогичные

имеющимся в PostgreSQL. PgBouncer позволяет обойтись более

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

файла формата pg_hba.conf. Необходимо учитывать, что менеджер

пула может поддерживать не все методы аутентификации. Например,

PgBouncer напрямую не поддерживает GSSAPI, LDAP и т. п. (Однако

поддержка PAM позволяет использовать различные методы.)

2. При успешной аутентификации, если потребуется открыть новое

соединение с базой данных, менеджер пула передает PostgreSQL

информацию, полученную от клиента (например, имя и пароль),

а PostgreSQL аутентифицирует входящее соединение обычным

образом. (Хотя в общем случае менеджер пула может подключаться

к PostgreSQL под другой ролью, игнорируя идентификацию клиента.)

Управление

Консоль управления PgBouncer

подключение psql к псевдобазе pgbouncer

SHOW — ряд команд для вывода состояния и другой информации

PAUSE — приостановка клиентов (для перезагрузки базы данных)

RESUME — возобновление работы

RELOAD — перечитывание файла конфигурации

и другие возможности

Для управления PgBouncer используется консоль, к которой можно

подключиться с помощью обычного psql, указывая специальную БД

pgbouncer.

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

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

на время перезагрузки сервера БД и т. п.

Особенности разработки

Действия, локализованные в сеансе, а не в транзакции

подготовленные операторы (PREPARE/EXECUTE)

временные таблицы (кроме ON COMMIT DROP)

функция currval

установка конфигурационных параметров (SET/RESET)

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

межпроцессные уведомления (LISTEN/NOTIFY)

курсоры с фразой WITH HOLD

загрузка разделяемых библиотек (LOAD)

расширения, сохраняющие состояние на уровне сеанса

Режим «пула транзакций» имеет для разработчика приложений

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

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

сервера БД. Поэтому не будут работать (точнее, будут работать не так,

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

на время сеанса, а не транзакции.

К ним относятся подготовленные операторы (если подготовкой

управляет клиент), использование временных таблиц, функции currval,

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

сеанса, межпроцессные уведомления, курсоры WITH HOLD, загрузка

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

сеанса (idle_session_timeout) и подобные. А также расширения,

реализующие «глобальные переменные» уровня сеанса, такие как

pg_variables (https://postgrespro.ru/docs/enterprise/16/pg-variables).

В версии pgBouncer 1.21 (16.10.2023) реализована поддержка

подготовленных операторов на стороне клиента. Надо учитывать,

что команды уровня SQL (PREPARE, EXECUTE, DEALLOCATE)

по-прежнему направляются напрямую на сервер, поэтому подготовка

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

непосредственно формирующих сообщения протокола (например,

для python или perl).

https://www.pgbouncer.org/changelog.html#pgbouncer-121x

Подготовка соединений

Начальные действия и изоляция

connect_query server_reset_query

соединение

используется

server_re set_query_always = off

PgBouncer предоставляет параметры, позволяющие с помощью SQL-

команды

●

настраивать только что открытое соединение,

●

очищать состояние соединения после использования его клиентом.

Запрос, заданный в атрибуте connect_query строки соединения,

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

выдано первому клиенту).

Запрос, заданный в параметре server_reset_query, по умолчанию

срабатывает только в режиме пула сеансов и полностью сбрасывает

состояние сеанса, включая содержимое всех кешей. Если установить

server_reset_query_always = on, запрос будет срабатывать и в режиме

пула транзакций. Тогда, если приложение не обеспечивает должную

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

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

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

таблиц и др.

Итоги

На каждое клиентское соединение создается

обслуживающий процесс на сервере

Большие количество соединений приводит к проблемам

Пул соединений позволяет одновременно работать многим

клиентам, ограничивая количество соединений с сервером

Транзакционный режим пула имеет особенности

с точки зрения разработки приложений

Практика

1. Убедитесь, что при работе приложения через пул соединений

(порт 6432) разные пользователи обычно видят одну и ту же

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

хранимых функций так, чтобы приложение работало

корректно как без пула соединений, так и с ним.

2. Использование пула мешает выводу в журнал сообщений

сервера информации, относящейся только к одному клиенту.

Наше приложение вызывает в начале каждой транзакции

функцию webapi.trace (или empapi.trace), если в служебной

панели установлен признак трассировки. Реализуйте эти

функции так, чтобы они устанавливали параметр сервера

log_min_duration_statements = 0 и облегчали идентификацию

клиента. Проверьте работу по журналу сообщений.

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

подключение через пул соединений на порту 6432 (настройка по

умолчанию). Pgbouncer настроен точно так же, как было показано

в демонстрации.

1. Проблема воспроизводится следующим образом. Откройте две

вкладки браузера с книжным магазином. В первой войдите как alice

и положите в корзину одну или несколько книг. Во второй зайдите как

bob и проверьте корзину.

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

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

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

«Приложение 2.0».

2. Необходимые функции уже имеются в базе данных, но ничего не

делают. Функция webapi.trace принимает на вход токен аутентификации

(или NULL, если пользователь не вошел в систему).

Для идентификации можно использовать параметр log_line_prefix

с маской %a, выводящей имя приложения. Имя приложения

устанавливается параметром application_name, в которое можно

записать имя пользователя, если оно известно.

Практика+

1. В двух сеансах подключитесь к базе student ролью student

через пул соединений. В третьем сеансе подключитесь

к консоли pgbouncer и изучите вывод команд SHOW CLIENTS

и SHOW SERVERS.

2. Перепишите следующий фрагмент SQL-кода так, чтобы он

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

INSERT INTO master(id, s)

SELECT nextval('m_seq'), 'm1';

INSERT INTO detail(id, m_id, s)

SELECT nextval('d_seq'), currval('m_seq'), 'd1';

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

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

соединений в режиме транзакций.