Урок 05

Connection pooling: PgBouncer, ProxySQL

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

Что происходит:

  • Каждый PHP-запрос открывает своё соединение.
  • PostgreSQL создаёт процесс на соединение.
  • Память 10 MB на соединение.
  • Setup 5–20 мс.
  • CPU БД растёт.

Что даёт:

  • 100+ соединений — проблема.
  • CPU БД уходит на fork.
  • Латентность растёт.

Правило: на высоких RPS — pooling обязателен.

Что такое pooling

Пул — держит соединения постоянно.

Что даёт:

  • Не создавать каждый раз.
  • Переиспользовать.
  • Ограничить количество.
  • Меньше CPU.

Правило: pool — между PHP и БД.

PgBouncer

Что это: пулер для PostgreSQL.

Что даёт:

  • Много клиентов — мало соединений.
  • Быстрый setup.
  • Прозрачно для Laravel.

Правило: PgBouncer — стандарт для Postgres.

PgBouncer: установка

sudo apt install pgbouncer

Правило: через apt или из исходников.

PgBouncer: конфиг

/etc/pgbouncer/pgbouncer.ini:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
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

Правило: pool_mode — главная настройка.

Pool modes

session:

  • Соединение на сессию.
  • Не помогает при коротких.

transaction:

  • Соединение на транзакцию.
  • Максимум пользы.
  • Рекомендуется.

statement:

  • Соединение на запрос.
  • Не работает с транзакциями.

Правило: transaction — для Laravel.

Prepared statements

Проблема: transaction mode ломает prepared statements.

Решение: в PHP — выключить.

PDO:

PDO::ATTR_EMULATE_PREPARES => true,

Что даёт: работает с PgBouncer.

Правило: в Laravel — включить emulation.

Laravel + PgBouncer

.env:

DB_HOST=127.0.0.1
DB_PORT=6432

Что даёт: Laravel думает, что это Postgres.

Правило: порт 6432, не 5432.

Laravel: prepared

config/database.php:

'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST'),
    'port' => env('DB_PORT', 5432),
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,
    ],
    // ...
],

Что даёт: совместимость с PgBouncer.

Auth

/etc/pgbouncer/userlist.txt:

"skills" "md5_hashed_password"

Правило: MD5 или SCRAM — не plain.

Systemd

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Проверка:

psql -h 127.0.0.1 -p 6432 -U skills -d skills

Правило: проверить до переключения Laravel.

Метрики PgBouncer

SHOW POOLS;:

cl_active | cl_waiting | sv_active | sv_idle

SHOW STATS;:

  • total_xact_count.
  • avg_xact_time.
  • avg_query_time.

Правило: cl_waiting > 0 — мало соединений.

pgBouncer в Kubernetes

Sidecar:

  • Контейнер рядом с приложением.
  • Per-pod pool.

Правило: в K8s — sidecar.

ProxySQL

Для MySQL:

sudo apt install proxysql

Что даёт:

  • Пул соединений.
  • Query rules.
  • Read/write split.
  • Кэш запросов.

Правило: ProxySQL — аналог PgBouncer для MySQL.

ProxySQL: конфиг

MySQL-таблицы:

INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, '127.0.0.1', 3306);

INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('skills', 'pass', 10);

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

Что даёт: ProxySQL знает серверы.

Laravel + ProxySQL

.env:

DB_HOST=127.0.0.1
DB_PORT=6033

Что даёт: Laravel идёт через ProxySQL.

Persistent connections

В PHP:

PDO::ATTR_PERSISTENT => true,

Что даёт: соединение переиспользуется.

⚠️ Может сломать транзакции.

Правило: persistent — только с pooling.

Octane и пулинг

Что важно:

  • Octane держит соединения.
  • Без pool — много соединений.
  • С PgBouncer — 20 достаточно.

Правило: Octane + pooling — обязательно вместе.

Swoole и pool

Что даёт:

  • Встроенный pool соединений.
  • Один коннект на воркер.
  • Быстрее — нет сетевого setup.

Правило: Swoole pool — лучше для Octane.

Очереди и пулинг

Проблема: 10 воркеров = 10 соединений.

С PgBouncer: 20 общих.

Правило: воркеры — тоже через pool.

Множественные БД

Проблема: 5 БД × 20 соединений = 100.

Что делать:

  • PgBouncer на каждую.
  • Разные pool_size.
  • Lazy соединения.

Правило: каждая БД — свой pool.

Monitoring

Что смотреть:

  • Client wait time.
  • Server utilization.
  • Query time average.
  • Errors.

Правило: алерт на cl_waiting > 0.

Параметры pool_size

Правило:

  • Формула: pool_size = (CPU cores * 2) + disk_spindles.
  • Не больше 100 на БД.
  • Тестировать под нагрузкой.

Пример: 8 ядер, SSD — 20–30.

Правило: не угадывать — бенчмарк.

pgbench

pgbench -c 100 -j 4 -T 60 -S skills

Что даёт: нагрузочный тест.

Правило: замерять до/после pooling.

Latency

Что важно:

  • Localhost — < 0.5 мс.
  • Локальная сеть — 1–2 мс.
  • Разные регионы — 10–100 мс.

Правило: pool — в одном регионе с БД.

HA и pooling

Что важно:

  • PgBouncer сам не HA.
  • Несколько инстансов.
  • HAProxy спереди.

Правило: prod — 2+ PgBouncer.

Отказ PgBouncer

Что делать:

  • Fallback на порт БД.
  • Kill switch в конфиге.
  • Мониторинг процесса.

Правило: pool — не точка отказа.

Практика

1. Установите PgBouncer.

2. Настройте transaction mode.

3. Переключите Laravel на 6432.

4. Включите PDO::ATTR_EMULATE_PREPARES.

5. Проверьте SHOW POOLS.

6. Прогоните pgbench до/после.

7. Настройте алерт на cl_waiting.

8. Установите ProxySQL для MySQL.

9. Настройте Swoole pool для Octane.

10. Замерьте p95 под нагрузкой.

Частые ошибки

1. Session mode:

Не помогает

Решение: transaction.

2. Prepared без emulation:

Ошибки

Решение: EMULATE_PREPARES.

3. Мало pool_size:

Ожидание

Решение: увеличить.

4. Много pool_size:

CPU БД

Решение: бенчмарк.

5. Один PgBouncer:

SPOF

Решение: 2+.

6. Persistent без pool:

Ошибки

Решение: pool.

7. Порт 5432:

В обход pool

Решение: 6432.

8. Без мониторинга:

Тихий bottleneck

Решение: SHOW POOLS.

Итог

  • PgBouncer — пул для Postgres.
  • transaction mode.
  • EMULATE_PREPARES в Laravel.
  • ProxySQL — для MySQL.
  • pool_size — 20–30.
  • pgbench — замер.
  • cl_waiting — алерт.
  • Swoole pool — для Octane.
  • 2+ инстанса для HA.
  • Мониторить обязательно.

Дальше — оптимизация полнотекстового поиска.