Настройка пулинга соединений PostgreSQL с помощью PgBouncer для высоконагруженных приложений

Введение

Когда базы данных PostgreSQL сталкиваются с большим количеством соединений, производительность может быстро снижаться. Каждое соединение с БД потребляет системные ресурсы, а у PostgreSQL есть практические ограничения на количество одновременных подключений. PgBouncer, легковесный пулер соединений, решает эту проблему, поддерживая пул соединений с базой данных и эффективно распределяя их между клиентскими приложениями.

Почему важен пулинг соединений

Проблема соединений

Расход ресурсов: Каждый фоновый процесс PostgreSQL потребляет 5-10 МБ оперативной памяти
Ограничения соединений: Значение по умолчанию max_connections обычно составляет 100-200
Стоимость создания: Установление нового соединения занимает 1-5 мс
Переключение контекста: Слишком большое количество процессов вызывает «пробуксовку» (thrashing) процессора

Преимущества PgBouncer

Сокращает количество соединений с базой данных в 10-100 раз
Позволяет поддерживать тысячи клиентских подключений с минимальными затратами ресурсов
Обеспечивает постановку соединений в очередь во время пиковых нагрузок
Поддерживает несколько режимов пулинга для различных сценариев использования

Установка и базовая настройка

Установка PgBouncer

В Ubuntu/Debian:

sudo apt update
sudo apt install pgbouncer

В CentOS/RHEL:

sudo yum install pgbouncer

В macOS:

brew install pgbouncer

Структура директорий

/etc/pgbouncer/
  ├── pgbouncer.ini        # Основная конфигурация
  └── userlist.txt         # Учетные данные для аутентификации

Настройка конфигурационного файла

Базовая конфигурация pgbouncer.ini

[databases]
; database_name = host=hostname port=5432 dbname=actual_db
myapp = host=localhost port=5432 dbname=production_db

[pgbouncer]
; Режим пулинга соединений
pool_mode = transaction

; Максимальное количество соединений
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

; Сетевые настройки
listen_addr = 0.0.0.0
listen_port = 6432

; Аутентификация
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Логирование
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Производительность
max_prepared_statements = 0

Понимание режимов пулинга

1. Сессионный пулинг (pool_mode = session)

Поведение: Соединение закрепляется за клиентом на всю сессию
Сценарий: Приложения, использующие временные таблицы, подготовленные выражения (prepared statements)
Эффективность: Низкая (соотношение соединений 1:1)

pool_mode = session

2. Транзакционный пулинг (pool_mode = transaction) — Рекомендуется

Поведение: Соединение возвращается в пул после каждой транзакции
Сценарий: Большинство веб-приложений с короткими транзакциями
Эффективность: Высокая (сокращение в 10-100 раз)

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

3. Пооператорный пулинг (pool_mode = statement)

Поведение: Соединение возвращается после каждого SQL-запроса
Сценарий: Простые запросы только на чтение без транзакций
Эффективность: Максимальная (но очень ограничивающая)

pool_mode = statement
; Использовать с осторожностью - нарушает работу транзакций из нескольких запросов

Настройка аутентификации

Создание userlist.txt

PgBouncer требует отдельный файл аутентификации. Сгенерируйте MD5-хеш и добавьте его в userlist.txt.

Пример userlist.txt:

"app_user" "md5d8578edf8458ce06fbc5bb76a58c5ca4"
"readonly_user" "md5a3c7f5e89d24e7c8b1f9d2e4a6c8b0d2"

Использование auth_query PostgreSQL (Продвинутый уровень)

Запрос к PostgreSQL напрямую для аутентификации:

auth_type = md5
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Оптимальная конфигурация для высокой нагрузки

Определение размера пула соединений

Формула для расчета размера пула:

default_pool_size = (количество ядер × 2) + количество дисков

Для 4-ядерного сервера с SSD:

default_pool_size = 20
reserve_pool_size = 5
max_client_conn = 1000

Полная конфигурация для продакшена

[databases]
production = host=db.example.com port=5432 dbname=prod_db pool_size=30
analytics = host=db-replica.example.com port=5432 dbname=prod_db pool_size=15

[pgbouncer]
pool_mode = transaction

; Лимиты соединений
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 8
reserve_pool_timeout = 3
server_lifetime = 3600
server_idle_timeout = 600

; Сетевые настройки
listen_addr = 0.0.0.0
listen_port = 6432
so_reuseport = 1
pkt_buf = 8192

; Безопасность
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
ignore_startup_parameters = extra_float_digits,options

; Логирование
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

; Производительность
max_prepared_statements = 0
query_timeout = 30
query_wait_timeout = 120

Строка подключения приложения

До использования PgBouncer

# Прямое подключение к PostgreSQL
DATABASE_URL = "postgresql://user:[email protected]:5432/mydb"

После использования PgBouncer

# Подключение через PgBouncer
DATABASE_URL = "postgresql://user:[email protected]:6432/mydb"

Мониторинг и управление

Команды административной консоли

Подключитесь к консоли администратора PgBouncer:

psql -h localhost -p 6432 -U pgbouncer pgbouncer

Основные команды:

-- Показать статистику пулов
SHOW POOLS;

-- Показать активные соединения
SHOW CLIENTS;
SHOW SERVERS;

-- Показать конфигурацию
SHOW CONFIG;

-- Перезагрузить конфигурацию
RELOAD;

Устранение распространенных проблем

Проблема 1: "no more connections allowed"

Решение:

max_client_conn = 5000
default_pool_size = 50

Проблема 2: Высокое значение cl_waiting

Решения:

Увеличьте размер пула
Оптимизируйте медленные запросы
Добавьте резервный пул (reserve_pool)

Проблема 3: Ошибки подготовленных выражений (Prepared Statement)

Решение:

max_prepared_statements = 0

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

До PgBouncer

500 одновременных запросов → 500 соединений с PostgreSQL
Нагрузка на БД: 95% CPU, 8 ГБ RAM
Время отклика: в среднем 250 мс

После PgBouncer

500 одновременных запросов → 25 соединений с PostgreSQL
Нагрузка на БД: 35% CPU, 1 ГБ RAM
Время отклика: в среднем 80 мс
Результат: в 3 раза быстрее, потребление ресурсов меньше на 70%

Заключение

PgBouncer незаменим для масштабирования приложений на PostgreSQL. Он снижает накладные расходы на соединения более чем на 90%, поддерживает в 10-100 раз больше клиентов и значительно сокращает время отклика. Начните с транзакционного режима пулинга и корректируйте настройки на основе данных мониторинга.