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

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

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

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

Накладные расходы ресурсов: Каждое соединение PostgreSQL имеет фоновый процесс и накладные расходы памяти.
Ограничения соединений: max_connections конечен, и его чрезмерное увеличение может сделать базу данных менее стабильной, а не более быстрой.
Затраты на запуск: Создание новых соединений с базой данных добавляет задержку.
Переключение контекста: Слишком много активных фоновых процессов может тратить процессор впустую.

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

Позволяет многим клиентам приложения совместно использовать меньшее количество серверных соединений PostgreSQL.
Ставит клиентов в очередь, когда пул занят, вместо того чтобы перегружать базу данных.
Поддерживает режимы пула сессий, транзакций и операторов.
Предоставляет операционные команды, такие как SHOW POOLS, SHOW CLIENTS и RELOAD.

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

Установка 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]
; имя_базы = host=имя_хоста port=5432 dbname=фактическая_бд
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

Значения по умолчанию для пакетов различаются в зависимости от дистрибутива, поэтому после установки проверьте установленный файл службы и путь к конфигурации. Во многих пакетах Linux основным файлом является /etc/pgbouncer/pgbouncer.ini.

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

1. Пул сессий (pool_mode = session)

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

pool_mode = session

2. Пул транзакций (pool_mode = transaction) - Рекомендуется

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

pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000

Пул транзакций является хорошим выбором по умолчанию для веб-приложений типа запрос/ответ, но он может нарушить предположения о функциях уровня сессии. Будьте осторожны с временными таблицами, переменными сессии, консультативными блокировками, удерживаемыми вне транзакции, LISTEN/NOTIFY и подготовленными операторами на уровне драйвера, если вы не протестировали их с вашей версией PgBouncer и настройками.

3. Пул операторов (pool_mode = statement)

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

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

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

Создание userlist.txt

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

Пример userlist.txt:

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

Для MD5-паролей в стиле PostgreSQL значением является md5 плюс MD5-хеш от password + username. Не вставляйте поддельные хеши в продакшн; генерируйте записи из ваших реальных имен пользователей и паролей или используйте более безопасный метод аутентификации, поддерживаемый вашей средой.

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

PgBouncer может запрашивать PostgreSQL для получения учетных данных пользователя, если пользователь не найден в auth_file, но для этого требуется auth_user, под которым PgBouncer может войти. Минимальный пример выглядит так:

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

Ограничьте привилегии пользователя аутентификации и следуйте рекомендациям вашей версии PostgreSQL. Многие команды используют функцию SECURITY DEFINER вместо предоставления прямого доступа к данным паролей каталога.

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

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

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

Для типичного веб-приложения вы можете начать с этого и корректировать:

default_pool_size = 25
reserve_pool_size = 5
max_client_conn = 1000

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

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

[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

max_prepared_statements = 0 отключает отслеживание подготовленных операторов PgBouncer. Последние версии PgBouncer могут поддерживать подготовленные операторы на уровне протокола в пуле транзакций, если это значение установлено выше нуля, но перед включением протестируйте свой драйвер и рабочую нагрузку.

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

До 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"

Это может означать, что PgBouncer отклоняет клиентские соединения или PostgreSQL отклоняет серверные соединения. Проверьте, где появляется ошибка.

Возможные изменения на стороне PgBouncer:

max_client_conn = 5000
default_pool_size = 50

Прежде чем увеличивать оба значения, убедитесь, что лимит файловых дескрипторов вашей ОС и max_connections PostgreSQL могут поддерживать новые итоговые значения. Самому PgBouncer также требуется достаточно файловых дескрипторов для клиентских и серверных сокетов.

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

Решения:

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

Проблема 3: Ошибки подготовленных операторов

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

Отключите подготовленные операторы на стороне драйвера.
Используйте пул сессий для этой рабочей нагрузки.
Протестируйте поддержку подготовленных операторов PgBouncer с положительным значением max_prepared_statements на последней версии PgBouncer.

Консервативная настройка:

max_prepared_statements = 0

Практический пример развертывания

Представьте, что ваши серверы приложений могут открывать сотни одновременных HTTP-запросов, но база данных работает лучше всего с несколькими десятками активных запросов. Направьте приложение на PgBouncer на порту 6432, установите max_client_conn достаточно высоким для всплесков клиентов и держите default_pool_size близким к количеству соединений с базой данных, которые вы действительно хотите активными для этой пары база данных/пользователь.

Затем проверьте с помощью:

SHOW POOLS;
SHOW STATS;

Если cl_waiting остается выше нуля при нормальном трафике, сначала исследуйте медленные запросы, прежде чем просто увеличивать размер пула. PgBouncer защищает PostgreSQL от шквала соединений, но не делает медленные SQL-запросы быстрыми.

Практический вывод

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