Database Connection Pooling: PgBouncer and ProxySQL

# 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;

# /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;