Настройка Connection Pooling (PgBouncer/ProxySQL) для веб-приложения

Настройка Connection Pooling (PgBouncer/ProxySQL) для веб-приложения

Без пулинга соединений каждый HTTP-запрос к PostgreSQL или MySQL открывает отдельное TCP-соединение. Установка соединения занимает 5–50 мс и требует выделения ~5–10 MB памяти на стороне базы. При 500 одновременных запросах это 500 соединений, каждое из которых держит фоновый процесс в PostgreSQL. Решение — connection pooler перед базой.

Когда это нужно

Симптомы проблемы с соединениями: max_connections в PostgreSQL достигает лимита (по умолчанию 100), приложение получает ошибку FATAL: remaining connection slots are reserved, время ответа базы растёт нелинейно при нагрузке. Для MySQL/MariaDB аналогично — Too many connections.

Пулинг нужен при пиковом числе воркеров приложения > 50 и при использовании PHP-FPM, Gunicorn, Unicorn — то есть там, где каждый процесс держит своё соединение.

PgBouncer для PostgreSQL

PgBouncer — легковесный прокси (один процесс, ~2 MB RAM) с тремя режимами пулинга.

Session mode — одно соединение с БД на сессию клиента. Почти нет выигрыша, но полная совместимость с prepared statements и SET.

Transaction mode — соединение занимается только на время транзакции. Самый распространённый вариант. Несовместим с SET вне транзакций и LISTEN/NOTIFY.

Statement mode — соединение возвращается после каждого запроса. Несовместим с транзакциями, применяется редко.

Установка на Ubuntu:

apt install pgbouncer

Конфигурация /etc/pgbouncer/pgbouncer.ini:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
client_idle_timeout = 0
log_connections = 0
log_disconnections = 0

Файл /etc/pgbouncer/userlist.txt — хранит MD5-хэши паролей:

# Генерация хэша: echo -n "passwordusername" | md5sum
echo -n "mysecretmyapp" | md5sum
# Результат вставляем в userlist.txt:
"myapp" "md5<hash>"

При default_pool_size = 20 PgBouncer держит максимум 20 реальных соединений к PostgreSQL, при этом принимая до 1000 клиентских. Подбирать default_pool_size нужно под конкретную нагрузку — формула: (количество ядер CPU сервера БД) * 2 + количество дисков.

Проверка статуса через psql:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

Подключение приложения через PgBouncer

В Laravel .env:

DB_HOST=127.0.0.1
DB_PORT=6432
DB_DATABASE=myapp
DB_USERNAME=myapp
DB_PASSWORD=mysecret

Важный момент для Laravel в transaction mode: отключить prepared statements. В config/database.php:

'pgsql' => [
    'driver' => 'pgsql',
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,
    ],
],

Для Django — аналогично, использовать CONN_MAX_AGE = 0 при transaction pooling, чтобы Django не пытался держать персистентные соединения, которые конфликтуют с пулером.

ProxySQL для MySQL/MariaDB

ProxySQL — значительно мощнее PgBouncer: умеет роутинг запросов, read/write split, автоматическое переключение при падении мастера.

Установка:

# Ubuntu/Debian
wget https://github.com/sysown/proxysql/releases/download/v2.6.3/proxysql_2.6.3-ubuntu22_amd64.deb
dpkg -i proxysql_2.6.3-ubuntu22_amd64.deb
systemctl start proxysql

Конфигурация через admin-интерфейс (порт 6032):

mysql -h 127.0.0.1 -P 6032 -u admin -padmin

Добавление MySQL серверов:

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '192.168.1.10', 3306); -- replica

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Настройка пользователя:

INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('myapp', 'mysecret', 0);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Read/write split через query rules:

INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 0, 1);

INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 1, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Все SELECT (кроме SELECT ... FOR UPDATE) пойдут на реплику в hostgroup 1, всё остальное — на мастер в hostgroup 0.

Размер пула соединений в ProxySQL задаётся через mysql_variables:

SET mysql-max_connections = 2048;
SET mysql-max_transaction_idle_time = 5000;
SET mysql-connection_max_age_ms = 0;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Мониторинг

Для PgBouncer — метрики через pgbouncer_exporter для Prometheus:

docker run -d \
  -e DATA_SOURCE_NAME="postgresql://pgbouncer:[email protected]:6432/pgbouncer" \
  -p 9127:9127 \
  prometheuscommunity/pgbouncer-exporter

Ключевые метрики: pgbouncer_pools_cl_waiting (клиенты в очереди — должно быть 0), pgbouncer_pools_sv_idle (свободные серверные соединения).

Для ProxySQL — встроенный stats schema:

SELECT * FROM stats.stats_mysql_connection_pool;
SELECT * FROM stats.stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;

Типичные проблемы

Prepared statements в transaction mode — наиболее частая проблема с PgBouncer. Приложение создаёт prepared statement в одном соединении, а выполняет в другом. Решение: эмулировать prepared statements на уровне драйвера (PDO EMULATE_PREPARES) или перейти в session mode.

pg_temp схемы и временные таблицы — не работают в transaction mode, так как привязаны к сессии. Использовать CTE или постоянные таблицы с уникальными именами.

Длинные транзакции — при transaction pooling соединение занято всё время транзакции. Длинные транзакции (>30 секунд) уменьшают эффективность пула так же, как и без пулинга.

search_path и SET команды — в transaction mode сбрасываются после транзакции. Если приложение устанавливает search_path через SET, нужно использовать server_reset_query в PgBouncer или передавать схему явно в URL подключения.

Сроки

Базовая настройка PgBouncer с тестированием — 1 рабочий день. ProxySQL с read/write split и настройкой мониторинга — 2–3 дня. Если нужна интеграция с существующим приложением и устранение проблем с prepared statements — плюс 1 день на отладку.