pgBouncer и лавина соединений: как мы уронили connection count с 800 до 50

В проекте миграции с Oracle мы планировали gap-анализ и настраивали репликацию. Но оказалось, что ещё до переезда данных нас ждал сюрприз, про который в документации по миграции обычно пишут вскользь: модель соединений в PostgreSQL принципиально отличается от Oracle, и legacy-приложения об этом не знают.

Откуда берётся лавина

Oracle традиционно работает в архитектуре с процессом-диспетчером: клиентские соединения мультиплексируются через shared server или через специализированные connection pool на уровне самого Oracle. Приложение может держать открытое соединение почти бесплатно - оно не обязательно означает отдельный тяжёлый серверный процесс.

PostgreSQL устроен иначе. Каждое клиентское соединение - это отдельный процесс на сервере (postmaster форкает postgres на каждый connect). Процесс ест память - около 5-10 МБ в спокойном состоянии, плюс рабочие буферы при активном запросе. При 800 одновременных соединениях это уже несколько гигабайт только на обслуживание подключений, большинство из которых в конкретный момент ничего не делают.

Наши legacy-приложения были написаны в логике «открыл соединение - подержал - закрыл когда удобно». При Oracle это работало. При тестовой нагрузке на PostgreSQL картина была неприятная: счётчик соединений в pg_stat_activity рос быстро, планировщик начинал тормозить, а ядро жаловалось на количество процессов.

pgBouncer как прокси

pgBouncer - это лёгкий connection pooler для PostgreSQL, написанный на C. Он встаёт между приложениями и PostgreSQL: приложения подключаются к pgBouncer (на тот же порт 5432, только на другом хосте или порту), а pgBouncer держит фиксированный пул реальных соединений к PostgreSQL и раздаёт их приложениям по требованию.

Работает в трёх режимах:

• Session mode. Соединение с PostgreSQL выдаётся клиенту на всё время его сессии. Самый совместимый, но экономии почти нет.
• Transaction mode. Соединение выдаётся только на время транзакции, после коммита/роллбека возвращается в пул. Основной режим для нас.
• Statement mode. Соединение выдаётся на один запрос. Самое агрессивное мультиплексирование, но ломает мультистейтментные транзакции - не подходит для большинства приложений.

Мы выбрали transaction mode.

Что пришлось проверить перед включением

Transaction mode имеет ограничения, которые важно понять до деплоя, а не после:

SET и временные настройки сессии. Если приложение делает SET search_path = myschema или SET work_mem = ... в начале сессии, рассчитывая что это сохранится, - в transaction mode это не гарантировано. После возврата соединения в пул оно может уйти к другому клиенту. Мы прошлись по коду приложений и нашли несколько таких мест - их пришлось или убрать, или перенести в параметры подключения через options в строке DSN.

Prepared statements. В transaction mode prepared statements не работают между транзакциями - они привязаны к соединению. Приложения которые используют named prepared statements (через PREPARE/EXECUTE) без переподготовки - сломаются. Наши приложения в основном шли через JDBC с pgjdbc-драйвером в режиме простого протокола, так что проблема не проявилась, но проверить было нужно.

Advisory locks. Сессионные advisory locks (pg_advisory_lock) в transaction mode держатся только в рамках одной транзакции, дальше поведение непредсказуемо - соединение ушло к другому клиенту. Если есть код который использует advisory locks для межпроцессной синхронизации - это сломается незаметно и в самый неподходящий момент.

Конфигурация

Конфиг pgBouncer минималистичный:

[databases]
mydb = host=pg-primary port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0

default_pool_size = 50 - это реальные соединения к PostgreSQL. max_client_conn = 1000 - сколько клиентских соединений pgBouncer принимает сам. Разница между этими числами и есть суть мультиплексирования.

Параметр server_idle_timeout позволяет pgBouncer держать соединения в пуле живыми, не переустанавливая их при каждом запросе. Для нашей нагрузки 600 секунд - нормально.

Результат

После переключения приложений на pgBouncer счётчик реальных соединений в PostgreSQL (pg_stat_activity) упал до 40-55 активных. Тысяча клиентских соединений со стороны приложений мультиплексируются через этот пул.

Нагрузка на планировщик процессов Linux снизилась ощутимо. Потребление памяти на PostgreSQL стало предсказуемым. И что приятно - ни одно из приложений не потребовало изменения кода: строку подключения поменяли на pgBouncer, и всё.

Статистику пула смотрим через psql, подключившись к специальной базе pgbouncer (pgBouncer слушает там административные запросы):

SHOW POOLS;
SHOW STATS;

cl_active, cl_waiting, sv_active, sv_idle - эти четыре числа дают полную картину того что происходит.

Где сейчас

pgBouncer стоит между всеми внешними приложениями и PostgreSQL, и пока это стабильно. Единственный открытый вопрос - мониторинг: хочется видеть cl_waiting (клиенты которые ждут свободного соединения из пула) в Zabbix, чтобы вовремя заметить если пул окажется узким местом. Это следующий шаг.

Для проектов DWH и аналитики с legacy-нагрузкой pgBouncer в transaction mode - первое что стоит поставить перед тем как разбираться с остальным.