Set up database connection pooling with PgBouncer and ProxySQL, MySQL and PostgreSQL pooling, Node.js/Python/PHP integration, and performance optimization tips.
Database connection pooling is a technique that allows applications to reuse pre-established database connections instead of opening a new connection for every request. This eliminates connection setup overhead, limits the number of connections on the database server, and significantly improves overall application performance.
Every database connection consumes memory and CPU on the server side. In high-traffic applications:
| Scenario | Without Pooling | With Pooling |
|---|---|---|
| 1000 concurrent requests | 1000 DB connections | 20-50 DB connections |
| Connection time | 5-50ms per request | Zero (reuse) |
| Memory usage | High | Low |
| Scalability | Limited | High |
PgBouncer for PostgreSQL and ProxySQL for MySQL/MariaDB are the most widely used connection pooling solutions. Both are proven tools in production environments.
# Ubuntu/Debian
apt update
apt install -y pgbouncer
# CentOS/AlmaLinux
dnf install -y pgbouncer
# Check version
pgbouncer --version
nano /etc/pgbouncer/pgbouncer.ini
[databases]
; Database alias = connection details
myapp = host=127.0.0.1 port=5432 dbname=myapp
analytics = host=127.0.0.1 port=5432 dbname=analytics
[pgbouncer]
; Listen address and port
listen_addr = 127.0.0.1
listen_port = 6432
; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Pool mode: session, transaction, statement
pool_mode = transaction
; Connection limits
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Connection timeouts
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15
; Log settings
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
; Admin interface
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
# Get user hashes from PostgreSQL
psql -U postgres -c "SELECT '\"' || usename || '\"' || ' \"' || passwd || '\"' FROM pg_shadow;"
# Create userlist.txt
nano /etc/pgbouncer/userlist.txt
"appuser" "md5HASH_HERE"
"pgbouncer_admin" "md5HASH_HERE"
# Start service
systemctl start pgbouncer
systemctl enable pgbouncer
# Check status
systemctl status pgbouncer
; SESSION mode: one server connection per client connection
; Most compatible, least efficient
pool_mode = session
; TRANSACTION mode: connection held only during transaction
; Recommended mode, ideal for most applications
pool_mode = transaction
; STATEMENT mode: connection released after each SQL statement
; Most efficient but prepared statements not supported
pool_mode = statement
# Connect to admin console
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer
# Pool statistics
SHOW POOLS;
# Client connections
SHOW CLIENTS;
# Server connections
SHOW SERVERS;
# General statistics
SHOW STATS;
# Reload configuration
RELOAD;
# Download package directly
wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql_2.5.5-ubuntu22_amd64.deb
dpkg -i proxysql_2.5.5-ubuntu22_amd64.deb
# Start service
systemctl start proxysql
systemctl enable proxysql
# Connect to admin interface (default: admin/admin)
mysql -h 127.0.0.1 -P 6032 -u admin -padmin
-- Add MySQL servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES
(0, '127.0.0.1', 3306, 1000, 200), -- Write group (master)
(1, '192.168.1.101', 3306, 1000, 200), -- Read group (replica 1)
(1, '192.168.1.102', 3306, 1000, 200); -- Read group (replica 2)
-- Add user
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'StrongPassword123!', 0);
-- Query rules: route SELECTs to read group
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
(1, 1, '^SELECT.*FOR UPDATE', 0, 1), -- SELECT FOR UPDATE -> master
(2, 1, '^SELECT', 1, 1); -- Other SELECTs -> replica
-- Apply changes
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 pool status
SELECT * FROM stats_mysql_connection_pool;
-- Query statistics
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;
-- Server status
SELECT * FROM mysql_servers;
-- Active connections
SELECT * FROM stats_mysql_processlist;
Thread Pool plugin available in MySQL 8.0+:
# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Thread pool
plugin-load-add = thread_pool.so
thread_pool_size = 16
thread_pool_max_active_query_threads = 32
thread_pool_max_unused_threads = 16
thread_pool_stall_limit = 500
# Connection limits
max_connections = 500
back_log = 100
wait_timeout = 600
interactive_timeout = 600
const mysql = require('mysql2/promise');
// Create connection pool
const pool = mysql.createPool({
host: '127.0.0.1',
port: 6033, // ProxySQL port
user: 'appuser',
password: 'StrongPassword123!',
database: 'myapp',
waitForConnections: true,
connectionLimit: 20, // Pool size
queueLimit: 0, // Unlimited queue
enableKeepAlive: true,
keepAliveInitialDelay: 0
});
// Use pool
async function getUsers() {
const [rows] = await pool.execute('SELECT * FROM users WHERE active = ?', [1]);
return rows;
// Connection automatically returns to pool
}
const { Pool } = require('pg');
const pool = new Pool({
host: '127.0.0.1',
port: 6432, // PgBouncer port
user: 'appuser',
password: 'StrongPassword123!',
database: 'myapp',
max: 20, // Maximum connections
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// Use pool
async function query(text, params) {
const start = Date.now();
const res = await pool.query(text, params);
const duration = Date.now() - start;
console.log('Query duration:', duration, 'ms');
return res;
}
// Graceful shutdown
process.on('SIGINT', async () => {
await pool.end();
process.exit(0);
});
from sqlalchemy import create_engine, text
from sqlalchemy.pool import QueuePool
# PostgreSQL + PgBouncer
engine = create_engine(
'postgresql://appuser:StrongPassword123!@127.0.0.1:6432/myapp',
poolclass=QueuePool,
pool_size=10, # Pool size
max_overflow=20, # Additional connection limit
pool_timeout=30, # Connection wait time
pool_recycle=1800, # Connection refresh interval (seconds)
pool_pre_ping=True, # Connection health check
echo=False
)
# Use connection
with engine.connect() as conn:
result = conn.execute(text('SELECT * FROM users WHERE active = :active'), {'active': 1})
users = result.fetchall()
# MySQL + ProxySQL
mysql_engine = create_engine(
'mysql+pymysql://appuser:StrongPassword123!@127.0.0.1:6033/myapp',
pool_size=10,
max_overflow=20,
pool_recycle=3600,
pool_pre_ping=True
)
import asyncpg
import asyncio
async def create_pool():
pool = await asyncpg.create_pool(
host='127.0.0.1',
port=6432,
user='appuser',
password='StrongPassword123!',
database='myapp',
min_size=5,
max_size=20,
command_timeout=60
)
return pool
async def get_users(pool):
async with pool.acquire() as conn:
rows = await conn.fetch('SELECT * FROM users WHERE active = $1', True)
return rows
<?php
$dsn = 'mysql:host=127.0.0.1;port=6033;dbname=myapp;charset=utf8mb4';
$options = [
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
];
try {
$pdo = new PDO($dsn, 'appuser', 'StrongPassword123!', $options);
} catch (PDOException $e) {
throw new RuntimeException('Database connection failed: ' . $e->getMessage());
}
$stmt = $pdo->prepare('SELECT * FROM users WHERE active = ?');
$stmt->execute([1]);
$users = $stmt->fetchAll();
[pgbouncer]
; For high-traffic environments
max_client_conn = 2000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
; Connection lifetime
server_lifetime = 3600
server_idle_timeout = 300
; TCP optimization
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5
; Performance monitoring
stats_period = 60
-- Increase connection pool size
UPDATE global_variables SET variable_value = '500'
WHERE variable_name = 'mysql-max_connections';
-- Connection reuse duration
UPDATE global_variables SET variable_value = '3600000'
WHERE variable_name = 'mysql-connection_max_age_ms';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
Connection pooling dramatically improves database performance in high-traffic applications. PgBouncer for PostgreSQL and ProxySQL for MySQL are the most mature and reliable solutions. Choosing the right pool mode (transaction mode is usually the best balance) and integrating properly with your application language will help you achieve maximum database performance on REXE servers.
Transaction mode is recommended for most applications. In this mode, the connection is held only during the transaction and immediately returns to the pool. Session mode is the most compatible but least efficient. Statement mode is the most efficient but causes issues if you use prepared statements. ORMs like Django and Rails work fine in transaction mode.
ProxySQL uses hostgroups: hostgroup 0 for writes (master) and hostgroup 1 for reads (replica). By adding rules to the mysql_query_rules table, you can route SELECT queries to replicas and INSERT/UPDATE/DELETE to the master. This distributes read load across replicas.
General rule: pool size = (CPU cores * 2) + disk count. For a 4-core server, 10-20 connections is usually sufficient. Too many connections increases context switching overhead. Run load tests with pgbench or sysbench to find the optimal value. Monitor current usage with SHOW POOLS; in PgBouncer.
Yes, it is absolutely necessary in production. Opening a new connection per HTTP request is both slow (5-50ms overhead) and overloads the database server. The mysql2 and pg libraries have built-in pool support. Setting connectionLimit to 10-20 per application server is a good starting point.
This is a known limitation of transaction mode. Solutions: 1) Switch to session mode (lower performance), 2) Disable prepared statements on the application side (in Node.js pg: prepare: false), 3) PgBouncer 1.21+ added prepared statement support in transaction mode - upgrade your version.
Step-by-step MySQL and MariaDB installation on Linux, mysql_secure_installation, creating databases and users, basic SQL commands, remote access configuration, and performance tuning.
PostgreSQL installation on Ubuntu/Debian and RHEL/CentOS, creating users and databases, basic SQL commands, remote connection setup, and backup strategies. Step-by-step guide.
Redis and Memcached installation on Ubuntu/Debian, basic commands, persistence settings, TTL configuration, Redis vs Memcached comparison, and application integration.