Connection Pooling: PgBouncer and ProxySQL
Database connections are expensive resources: each costs RAM (thread stack, buffers), requires authentication, opens file descriptor. Web application with 100 concurrent users × 10 requests per user = 1000 concurrent connections × 100-200 KB per connection = 100-200 MB just for connection overhead.
Connection pooling sits between application and database: application connects to pool, pool maintains 50 real connections to database. Result: 1000 app connections become 50 DB connections.
PgBouncer: PostgreSQL Connection Pool
Installation:
apt-get install pgbouncer
Configuration /etc/pgbouncer/pgbouncer.ini:
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
# Pool modes
pool_mode = transaction
# How many connections to keep to backend
min_pool_size = 10
default_pool_size = 25
max_pool_size = 100
# Connections to application (clients)
max_client_conn = 1000
# Cleanup idle connections
idle_in_transaction_session_timeout = 300000 # 5 min
server_idle_timeout = 600 # 10 min to backend
# Logging
logfile = /var/log/pgbouncer/pgbouncer.log
loglevel = info
# Stats
stats_period = 15
Pool modes:
- session — connection to backend lasts as long as client connection
- transaction — connection back to pool after each transaction (recommended for web)
- statement — connection back after each statement (strictest, rarely used)
Monitoring:
# Connect to admin console
psql -U pgbouncer -d pgbouncer -p 6432 -h localhost
# View stats
SHOW stats;
SHOW clients;
SHOW servers;
# Reload config without restart
RELOAD;
ProxySQL: MySQL Connection Pool and Query Routing
Installation:
apt-get install proxysql
Configuration via admin interface:
-- Connect to admin interface
mysql -u admin -p 'admin' -P 6032 -h 127.0.0.1
-- Add backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
(0, 'primary-db-1', 3306, 1000, 100),
(1, 'replica-1', 3306, 1000, 100),
(1, 'replica-2', 3306, 1000, 100);
-- Query rules: route SELECT to replicas
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup, active) VALUES
(1, '^SELECT ', 1, 1), -- hostgroup 1 = replicas
(2, '.*', 0, 1); -- everything else = primary (hostgroup 0)
-- Apply
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
-- Monitor
SHOW STATS;
Application connects via:
$pdo = new PDO(
'mysql:host=127.0.0.1;port=6033;dbname=mydb',
'app_user',
'password'
);