Connection pooling: переиспользование соединений с базой

Прямой ответ

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

pool = psycopg_pool.ConnectionPool(
    conninfo="dbname=app",
    min_size=5,
    max_size=20,
    max_idle=300,
)
with pool.connection() as conn:
    conn.execute("SELECT 1")

Соединение берётся из пула и возвращается после выполнения, поэтому установка соединения выполняется один раз на пул, а не на каждый запрос. О пулах соединений — в статье про пулы соединений.

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

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

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

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

Избыточный размер. Пул больше, чем сервер обслуживает эффективно, приводит к конкуренции за ресурсы, поэтому время запроса растёт.

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

Отдельный аспект — пул на экземпляр приложения. Каждый экземпляр держит собственный пул, поэтому суммарное число соединений равно произведению размера пула на число экземпляров. При масштабировании экземпляров суммарное число соединений растёт и упирается в предел сервера.

Режимы пулера и транзакции

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

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

Режим транзакции. Соединение освобождается после завершения транзакции, поэтому переиспользование выше, но состояние сессии между транзакциями не сохраняется.

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

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

Утечки соединений и проверка загрузки

Соединение, не возвращённое в пул, остаётся занятым, поэтому доступный размер пула сокращается.

Утечка. Соединение, взятое из пула и не возвращённое, остаётся занятым, поэтому последующие запросы ожидают освобождения.

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

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

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

О пулинге в PostgreSQL — в статье про PgBouncer; о влиянии соединений на запросы — в статье про оптимизацию запросов БД.

Практика: настройка пулинга соединений

1. Задавайте размер пула по возможностям сервера. Размер больше возможностей сервера добавляет конкуренцию, а не пропускную способность.

2. Учитывайте число экземпляров приложения. Суммарное число соединений равно произведению размера пула на число экземпляров.

3. Выбирайте режим пулера по использованию транзакций. Режим транзакции не сохраняет состояние сессии между транзакциями.

4. Возвращайте соединение в пул после использования. Соединение, не возвращённое в пул, сокращает доступный размер.

5. Ограничивайте время ожидания соединения. Запрос сверх предела завершается с ошибкой, поэтому отказ не распространяется.

6. Проверяйте число активных и ожидающих соединений. Рост ожидания указывает на исчерпание пула либо на утечку.

7. Фиксируйте метрики пула. Число занятых соединений и время ожидания показывают загрузку пула.

Рекомендации:

1. Не открывайте соединение на каждый запрос. Стоимость открытия превышает стоимость короткого запроса, поэтому пул сокращает задержку.
2. Учитывайте суммарное число соединений при масштабировании. Рост экземпляров увеличивает суммарное число соединений до предела сервера.
3. Сопоставляйте режим пулера с использованием подготовленных выражений. Режим транзакции не сохраняет состояние сессии.
4. Фиксируйте утечки в метриках. Рост числа занятых соединений без роста нагрузки указывает на утечку.
5. Пересматриваем размер пула при изменении числа экземпляров. Новое число экземпляров меняет суммарное число соединений.

Практический сценарий: запросы ожидали соединения

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

1. Диагностика. Проверка состояния пула показала, что все соединения заняты, а число ожидающих запросов растёт. Соединения, взятые в обработчике исключений, не возвращались в пул, поэтому доступный размер сокращался.
2. Причина. Соединение не возвращалось в пул при ошибке, поэтому доступный размер пула сокращался до нуля.
3. Действия. (а) Перевели получение соединения в контекстный менеджер, чтобы возврат выполнялся независимо от результата; (б) ограничили время ожидания соединения, чтобы запрос завершался с ошибкой вместо неопределённого ожидания; (в) добавили метрику числа занятых соединений, чтобы утечка обнаруживалась; (г) проверили размер пула относительно числа экземпляров и сократили суммарное число соединений; (д) убедились по метрикам, что число занятых соединений возвращается к исходному после обработки.
4. Результат. Соединения возвращаются в пул, поэтому ожидание исчезло и время ответа сократилось.

Вывод: соединение, не возвращённое в пул, сокращает доступный размер, поэтому запросы ожидают освобождения. Возврат соединения независимо от результата и ограничение времени ожидания устраняют ожидание и предотвращают распространение отказа.

FAQ

Зачем нужен пул соединений? Установка соединения требует обмена и выделения ресурсов, поэтому открытие дороже короткого запроса. Пул переиспользует соединения, поэтому стоимость открытия оплачивается один раз.

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

Чем различаются режимы пулера? Моментом освобождения соединения: режим сессии держит соединение до закрытия, режим транзакции освобождает после транзакции, режим запроса — после каждого запроса.

Как проявляется утечка соединений? Ростом времени ожидания при отсутствии загрузки базы. Соединение, не возвращённое в пул, сокращает доступный размер.

Источники: PgBouncer config, PostgreSQL connection settings, pg_stat_activity — ссылки в шапке.