“Too many connections” lúc 3 giờ sáng
Database có max_connections=200. Mỗi app instance mở 50 connections. Triển khai 5 instances = 250 connections → database từ chối kết nối mới. Không ai deploy gì. Đơn giản là auto-scaling thêm 1 instance khi traffic tăng.
Đây không phải bug application — đây là thiếu connection pooling. Và “tăng max_connections lên 500” không phải giải pháp: mỗi connection tốn RAM (PostgreSQL ~2-10MB/connection process, MySQL ~256KB-2MB/thread), context switch tăng, và throughput thực tế giảm sau một ngưỡng nhất định.
Bài này cover hai pooler chính:
- PgBouncer — cho PostgreSQL
- ProxySQL — cho MySQL/MariaDB
- Pattern chung: pool mode nào cho workload nào, sizing connection pool, và monitoring trong production
Tại sao cần connection pooling?
Mỗi connection không miễn phí
| Engine | RAM mỗi connection | CPU overhead | Context switch impact |
|---|---|---|---|
| PostgreSQL | 2-10 MB (process fork) | Cao (mỗi conn = 1 process) | Rõ rệt sau ~200 conn |
| MySQL | 256 KB-2 MB (thread) | Trung bình (thread pool) | Rõ rệt sau ~500 conn |
Vấn đề với direct connection từ app
App Instance 1 ── 50 connections ──┐
App Instance 2 ── 50 connections ──┤
App Instance 3 ── 50 connections ──┤→ PostgreSQL (max_connections=200)
App Instance 4 ── 50 connections ──┘ ❌ FATAL: too many connections
Giải pháp: pooler proxy
App Instance 1 ─┐ ┌── 30 connections ──→ PostgreSQL
App Instance 2 ─┤→ PgBouncer ────┤ (max 30 server conn,
App Instance 3 ─┤ (port 6432) └ phục vụ 200 client conn)
App Instance 4 ─┘
Pooler hấp thụ hàng nghìn client connections, chỉ mở vài chục connections thực tới database. App không cần biết.
PgBouncer: connection pooling cho PostgreSQL
PgBouncer là lightweight pooler, single-threaded event-loop (libevent), dùng cực ít RAM (~2MB baseline). Chuẩn de-facto trong hệ sinh thái PostgreSQL.
Cài đặt
# Debian/Ubuntu
sudo apt install pgbouncer
# RHEL/Fedora
sudo dnf install pgbouncer
Cấu hình tối thiểu (/etc/pgbouncer/pgbouncer.ini)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 500
max_db_connections = 50
# Log & admin
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
File userlist.txt chứa username/password hash:
# Lấy SCRAM hash từ pg_shadow (PG 14+ dùng scram-sha-256 mặc định)
sudo -u postgres psql -c "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname='myapp'" -tA | sudo tee /etc/pgbouncer/userlist.txt
Ba pool mode — đây là quyết định quan trọng nhất
| Mode | Cách hoạt động | Phù hợp | KHÔNG phù hợp |
|---|---|---|---|
| Session | 1 client conn = 1 server conn suốt phiên | SET session vars, prepared statements, LISTEN/NOTIFY | High concurrency (dễ cạn pool) |
| Transaction | Server conn chỉ gán trong thời gian 1 transaction | Web app, API server — default cho 90% use case | SET session vars không persist giữa các transaction |
| Statement | Server conn chỉ gán trong thời gian 1 statement | Read-only, auto-commit, pool nhỏ nhất | Transaction multi-statement (BEGIN…COMMIT không hoạt động) |
Quy tắc: nếu app là REST API → pool_mode = transaction. Nếu app dùng LISTEN/NOTIFY, prepared statements nặng, hoặc SET ROLE → pool_mode = session.
Monitoring PgBouncer
Kết nối vào PgBouncer admin console:
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
Các query hữu ích:
-- Pool status tổng quan
SHOW POOLS;
-- Stats per database
SHOW STATS;
-- Client connections đang mở
SHOW CLIENTS;
-- Server connections (tới PostgreSQL)
SHOW SERVERS;
-- Tạm dừng pool (để restart PostgreSQL phía sau)
PAUSE mydb;
-- Tiếp tục pool
RESUME mydb;
Performance tuning
# Số connection server tối đa cho mỗi pool (giữa PgBouncer và PG)
default_pool_size = 25
# Max client connections (từ app tới PgBouncer)
max_client_conn = 500
# Reserve pool: dành sẵn connections khi pool cạn
reserve_pool_size = 5
reserve_pool_timeout = 3 # giây, sau đó mới dùng reserve
# Timeout idle connections
server_idle_timeout = 600 # 10 phút, đóng server conn idle
client_idle_timeout = 0 # 0 = không giới hạn (default)
query_timeout = 0 # 0 = không giới hạn, nên set để kill query treo
ProxySQL: connection pooling + query routing cho MySQL
ProxySQL là lớp proxy thông minh cho MySQL/MariaDB: nó làm connection pooling, read/write splitting, query routing, và caching. Nặng hơn PgBouncer nhưng mạnh hơn cho MySQL ecosystem.
Cấu hình qua admin interface
ProxySQL cấu hình qua SQL (kết nối admin port 6032):
-- Kết nối admin
mysql -h 127.0.0.1 -P 6032 -u admin -padmin
-- Định nghĩa backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment)
VALUES
(0, 'db-primary.internal', 3306, 100, 'Write group'),
(1, 'db-replica-1.internal', 3306, 100, 'Read group'),
(1, 'db-replica-2.internal', 3306, 100, 'Read group');
-- Định nghĩa user app
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('myapp', 'mypass', 0); -- default là write group
-- Query rules: route SELECT tới read replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
(1, 1, '^SELECT .* FOR UPDATE', 0, 1), -- SELECT FOR UPDATE → write
(2, 1, '^SELECT .*', 1, 1), -- SELECT → read replica
(3, 1, '^INSERT|^UPDATE|^DELETE|^ALTER', 0, 1); -- DML → write
-- Load vào runtime và save
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
Connection pooling trong ProxySQL
-- Xem global variables
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-%';
-- Số connection tối đa từ ProxySQL tới backend
SET mysql-max_connections = 500;
-- Connection pool lifetime: idle timeout server conn (ms)
SET mysql-connection_max_idle_ms = 3600000; -- 1 giờ
-- Client connection timeout
SET mysql-default_query_timeout = 30000; -- 30 giây
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
Monitoring ProxySQL
-- Pool status
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, Queries
FROM stats_mysql_connection_pool;
-- Query stats (top slow queries)
SELECT hostgroup, digest_text, count_star, sum_time/count_star AS avg_latency_us
FROM stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 10;
-- Client connections
SELECT * FROM stats_mysql_processlist;
Sizing connection pool: công thức thực tế
pool_size = (số CPU core của database) * 2 đến * 4
Nhưng quan trọng hơn là đo thực tế:
# PostgreSQL: active queries tại peak
SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'active';
# MySQL: threads connected
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
Heuristic thực tế:
Threads_runninghiếm khi vượt quá 2× CPU core. Nếu vượt, query đang chờ lock/IO.- Pool size = 2-4× CPU core là điểm khởi đầu, tăng dần nếu thấy
SHOW POOLSbáocl_waiting(PgBouncer) hoặcConnUsedluôn max (ProxySQL).
Dấu hiệu pool quá nhỏ
- PgBouncer:
SHOW POOLS→cl_waiting> 0 thường xuyên - ProxySQL:
ConnUsed=ConnOK,Querieschững lại khi tăng client - Application: latency tăng đột ngột nhưng DB CPU không cao → app đang chờ connection
Dấu hiệu pool quá lớn
- PostgreSQL: context switch tăng,
idle in transactionnhiều - MySQL:
Threads_connectedcao nhưngThreads_runningthấp - Database memory usage tăng không tương ứng với throughput
Application-level pooling: khi nào đủ?
Hầu hết framework có built-in pool: HikariCP (Java), psycopg2.pool (Python), pgxpool (Go), Sequelize/Prisma (Node). Khi nào cần pooler riêng?
| Tình huống | App pool đủ? | Nên dùng pooler riêng |
|---|---|---|
| 1-3 app instances, connection ổn định | ✅ Đủ | Không |
| 5+ app instances, tổng conn > 100 | ⚠️ Ranh giới | PgBouncer/ProxySQL |
| Multi-service (monolith + microservices) cùng DB | ❌ | Bắt buộc |
| Serverless / Lambda (mỗi invocation = conn mới) | ❌ | PgBouncer + pool_mode=transaction |
| Cần read/write splitting | ❌ | ProxySQL |
| Cần failover transparent giữa primary/replica | ❌ | ProxySQL |
Playbook: setup pooler cho production
1. Đo baseline: SHOW POOLS / SHOW STATUS trước khi cài pooler
2. Cài PgBouncer (PG) hoặc ProxySQL (MySQL) trên cùng host hoặc dedicated node
3. Cấu hình pool_mode = transaction (cho web app), pool_size = 2× CPU core
4. Monitoring: SHOW POOLS, cl_waiting, avg_wait_time
5. Chuyển 1 app instance dùng pooler port (6432 / 6033), quan sát 30 phút
6. Nếu OK, chuyển toàn bộ instance
7. Giảm max_connections trên database (để chừa chỗ cho admin emergency)
Pooler là lớp bảo hiểm, không phải giải pháp cho query chậm
Connection pooling giải quyết bài toán quản lý connection, không giải quyết query chậm. Nếu pool đầy vì query mất 30 giây, pooler chỉ giúp bạn queue gọn hơn — vấn đề gốc vẫn là query cần tối ưu (bài 2, 3). Pooler + query optimization + monitoring là bộ ba giữ database ổn định khi traffic tăng.
Liên hệ các bài trong loạt
- Trước: Phần 15: Zero-downtime schema migration
- Liên quan: Phần 7: Tối ưu MySQL/PostgreSQL
- Liên quan: Phần 9: Monitoring và bảo trì
- Liên quan: Phần 12: Distributed SQL và sharding
Câu hỏi hay gặp
1. “Nên đặt pooler ở đâu: cùng host DB hay riêng?”
Cùng host DB: latency thấp nhất (Unix socket/localhost), đơn giản nhất, chi phí thấp. Riêng: khi cần failover, multi-DB routing, hoặc DB host hết tài nguyên cho thêm process. 80% trường hợp nên cài cùng host DB.
2. “Transaction mode có làm mất data từ SET không?”
Có thể. Ví dụ SET search_path TO tenant_1; SELECT ...; — PgBouncer pool_mode=transaction không đảm bảo câu SELECT tiếp theo dùng cùng server connection. Giải pháp: dùng SET LOCAL (chỉ trong transaction), hoặc pool_mode=session nếu app phụ thuộc nhiều session state.
3. “Serverless/Lambda làm sao dùng connection pool?”
Không thể pool ở app (mỗi invocation riêng biệt). Cần PgBouncer pool_mode=transaction ở tầng giữa, và app connect tới PgBouncer. PgBouncer handle cả nghìn short-lived connections từ Lambda.
4. “Làm sao biết pool đã đủ hay chưa?”
PgBouncer: SHOW POOLS — nếu cl_active thường xuyên không gần pool_size, pool dư. ProxySQL: ConnUsed so với ConnOK. Tăng pool từ từ, không nhảy từ 25 lên 200 trong 1 lần.
Đọc thêm
- PgBouncer documentation
- ProxySQL documentation
- AWS RDS Proxy — PgBouncer managed, tích hợp IAM
- HikariCP sizing guide
← Phần 15: Zero-downtime schema migration
→ Mục lục loạt Database Optimization