Налаштування Connection Pooling (PgBouncer/ProxySQL) для веб-додатку

Ми часто стикаємося з ситуацією, коли без пулінгу з'єднань кожен HTTP-запит до PostgreSQL або MySQL відкриває окреме TCP-з'єднання. Встановлення з'єднання займає 5–50 мс і потребує виділення ~5–10 MB пам'яті на стороні бази. При 500 одночасних запитах це 500 з'єднань, кожне з яких тримає фоновий процес. Без пулера база даних швидко впирається в ліміт max_connections, що призводить до помилок 500 і падіння продуктивності. Connection pooling за допомогою PgBouncer або ProxySQL — це стандарт для промислових веб-додатків. Налаштування пулінгу окупається за 2–3 місяці за рахунок зниження витрат на хмарні сервери до 70%, що економить від $1000 на місяць. У цій статті ми розповімо, як налаштувати PgBouncer для PostgreSQL і ProxySQL для MySQL, щоб усунути ці проблеми. Наш багаторічний досвід показує, що правильно налаштований connection pooler може знизити навантаження на БД на 50–80% і позбавити від простоїв. Transaction mode в PgBouncer збільшує пропускну здатність у 5–10 разів порівняно з session mode, а PgBouncer у 20 разів легше за ProxySQL за споживанням пам'яті (2 MB проти 40 MB). Замовте безкоштовний аудит — підберемо оптимальне рішення під ключ.

Коли це потрібно

Симптоми проблеми зі з'єднаннями: max_connections в PostgreSQL досягає ліміту (за замовчуванням 100), додаток отримує помилку FATAL: remaining connection slots are reserved, час відповіді бази зростає нелінійно при навантаженні. Для MySQL/MariaDB аналогічно — Too many connections. Пулінг потрібен при піковій кількості воркерів додатку > 50 і при використанні PHP-FPM, Gunicorn, Unicorn — тобто там, де кожен процес тримає своє з'єднання. Оптимізація підключень до БД дозволяє заощадити до 70% бюджету на хмарні бази даних.

Як PgBouncer знижує навантаження на PostgreSQL?

PgBouncer — легковаговий проксі (один процес, ~2 MB RAM) з трьома режимами пулінгу. Найчастіше ми використовуємо transaction mode: з'єднання займається лише на час транзакції. Це дає максимальну ефективність, але потребує адаптації prepared statements.

Кроки з налаштування PgBouncer

1. Встановлення пакету: apt install pgbouncer
2. Редагування конфігурації /etc/pgbouncer/pgbouncer.ini (див. нижче)
3. Створення файлу користувачів /etc/pgbouncer/userlist.txt
4. Перезапуск служби: systemctl restart pgbouncer
5. Налаштування додатку на порт 6432

Конфігурація /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

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

Підключення додатку через 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.

Коли використовувати ProxySQL замість PgBouncer?

ProxySQL — значно потужніший за PgBouncer: вміє роутинг запитів, read/write split, автоматичне перемикання при падінні мастера. Він незамінний, якщо у вас MySQL-кластер з репліками або потрібне гнучке розподілення навантаження. ProxySQL обробляє запити в 10 разів швидше за конкурентів.

Кроки з налаштування ProxySQL

1. Завантаження та встановлення пакету
2. Запуск служби systemctl start proxysql
3. Підключення до адмінського інтерфейсу mysql -h 127.0.0.1 -P 6032 -u admin -padmin
4. Додавання серверів та правил

# Встановлення ProxySQL
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-інтерфейс
mysql -h 127.0.0.1 -P 6032 -u admin -padmin
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Read/write split через query rules — весь SELECT (крім FOR UPDATE) на репліку, решта на мастер.

Порівняння PgBouncer та ProxySQL

Режими пулінгу PgBouncer

Типові проблеми при connection pooling

При використанні transaction mode можливі такі труднощі:

• Prepared statements можуть створюватися в одному з'єднанні, а виконуватися в іншому. Рішення: увімкнути емуляцію prepared statements на рівні драйвера (PDO::ATTR_EMULATE_PREPARES).
• Тимчасові таблиці та pg_temp не працюють, тому варто використовувати CTE або постійні таблиці.
• Довгі транзакції (понад 30 секунд) знижують ефективність пулу — необхідно оптимізувати запити.

Що входить у налаштування під ключ?

• Аудит поточної конфігурації БД та додатку.
• Налаштування пулера (PgBouncer або ProxySQL) під ваше навантаження.
• Інтеграція з додатком (Laravel, Django, Symfony та ін.).
• Тестування під навантаженням з моніторингом.
• Документація з експлуатації.
• Навчання команди.
• Підтримка протягом місяця після запуску.

Строки та вартість

Базове налаштування PgBouncer з тестуванням — 1 робочий день, вартість від $500. ProxySQL з read/write split та моніторингом — 2–3 дні, від $1200. Якщо потрібна інтеграція з існуючим додатком та усунення проблем з prepared statements — плюс 1 день на налагодження (від $300). Ми гарантуємо, що після налаштування кількість одночасних з'єднань перестане бути вузьким місцем. Пам'ять сервера БД скорочується на 80%. Напишіть нам для безкоштовного аудиту — допоможемо підібрати оптимальне рішення для вашого навантаження під ключ за 3 дні.

Послуги бекенд-розробки: production-grade надійність

На production-сервері о 3:14 ночі черга Laravel Jobs перестала оброблятися — 40 000 необроблених завдань у Redis. Причина: worker упав через memory leak у статичній змінній Eloquent observer, supervisor не перезапустив через misconfigured stopwaitsecs. Ми розбирали такий інцидент на проекті з 500 RPS: діагностика 4 години, фікс — 20 хвилин. Щоб ви не втрачали гроші, пропонуємо послуги бекенд-розробки з акцентом на production-grade надійність — 10+ років досвіду, 50+ проектів, 5 років на ринку. Оцінимо ваш проект за 2 дні.

Які проблеми вирішуємо

N+1 запити: головний вбивця швидкості

N+1 — найпоширеніша причина повільних сторінок у Laravel-додатках. Стандартна історія: сторінка працювала нормально на dev з 10 записами, на production з 10 000 — 8-секундне завантаження.

Laravel Debugbar у dev-оточенні показує кількість запитів. Більше 20 — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профілювання: логує всі запити, jobs, mail, notifications з деталізацією. Після впровадження eager loading час завантаження сторінки падає з 8 с до 0.3 с — у 27 разів.

Memory leak у статичних змінних

У Laravel Octane або Swoole додаток тримається в пам’яті між запитами. Статичні змінні не скидаються — призводять до неконтрольованого росту пам’яті. Використовуємо defer-функції та контейнерні біндинги для коректного скидання стану.

Неправильний connection pool

Rails, Laravel, Django відкривають нове з'єднання PostgreSQL на кожен PHP/Python процес. 100 воркерів — 100 з'єднань. PostgreSQL деградує від 200+ активних з'єднань через overhead на управління.

PgBouncer у transaction pooling: 1000 воркерів → 20–50 реальних з'єднань. Це знижує latency на 40% та зменшує витрати на хостинг на 30% — при середній вартості хостингу $2,000/міс економить $600/міс. GIN-індекс для JSONB до 100 разів швидший за B-tree при пошуку.

Як Octane справляється з високим навантаженням?

Laravel Octane (RoadRunner або Swoole) прибирає overhead bootstrap на кожен HTTP-запит. Приріст: 3–8x на синтетичних бенчмарках, 2–4x на реальних додатках. Важливо: не зберігати стан у статичних змінних — застосовуємо це на проектах >1000 RPS.

Як PostgreSQL допомагає уникнути повільних запитів?

Використовуємо composite indexes для WHERE + ORDER BY, partial indexes для фільтрів з високою селективністю, GIN-індекси для JSONB та full-text search. to_tsvector + GIN замість LIKE '%query%' — запобігає seq scan навіть на мільйонах записів. Аналізуємо плани через EXPLAIN ANALYZE та pg_stat_statements.

Як обрати стек для вашого проекту?

Node.js виправданий для realtime: Laravel публікує події в Redis Pub/Sub, Node.js підписується та транслює клієнтам. Go — для goroutines (10k з'єднань на сервер — норма), але розробка повільніша, ніж Laravel.

Чому Redis критичний для продуктивності?

Redis виконує кілька ролей:

Redis Cluster для горизонтального масштабування, Sentinel для автоматичного failover. Замовте консультацію щодо оптимізації Redis для вашого проекту.

Що входить в роботу під ключ

• Архітектурне проектування (документація API, схема БД, діаграма сервісів)
• Реалізація за узгодженим ТЗ з code review
• Налаштування CI/CD (GitHub Actions, Docker), моніторингу (Sentry, Grafana), алертингу
• Навантажувальне тестування (k6, wrk) зі звітом
• Передача вихідних кодів, доступів, інструкція з деплою
• Навчання команди замовника (2–3 сесії)
• Гарантійна підтримка 1 місяць після здачі

Орієнтири по термінах

Вартість розраховується індивідуально після аналізу вимог до навантаження, інтеграцій та бізнес-логіки. Зв'яжіться з нами для безкоштовного аудиту вашого поточного backend — отримайте план оптимізації за 2 дні. Замовте консультацію та дізнайтеся, як знизити витрати на інфраструктуру на 30% без втрати продуктивності.