Datenbank-Connection-Pooling: PgBouncer und ProxySQL

# Ubuntu/Debian
apt update
apt install -y pgbouncer

# CentOS/AlmaLinux
dnf install -y pgbouncer

# Version prüfen
pgbouncer --version

nano /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 = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = pgbouncer_admin

# Benutzer-Hashes aus PostgreSQL abrufen
psql -U postgres -c "SELECT '\"' || usename || '\"' || ' \"' || passwd || '\"' FROM pg_shadow;"

nano /etc/pgbouncer/userlist.txt

"appuser" "md5HASH_HIER"
"pgbouncer_admin" "md5HASH_HIER"

systemctl start pgbouncer
systemctl enable pgbouncer
systemctl status pgbouncer

; SESSION: Eine Serververbindung pro Client — am kompatiblen, am wenigsten effizient
pool_mode = session

; TRANSACTION: Verbindung wird nur während der Transaktion gehalten — empfohlen
pool_mode = transaction

; STATEMENT: Verbindung nach jeder SQL-Anweisung freigegeben — effizienteste, keine Prepared Statements
pool_mode = statement

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

SHOW POOLS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW STATS;
RELOAD;

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
systemctl start proxysql
systemctl enable proxysql

mysql -h 127.0.0.1 -P 6032 -u admin -padmin

-- MySQL-Server hinzufügen
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES
  (0, '127.0.0.1', 3306, 1000, 200),
  (1, '192.168.1.101', 3306, 1000, 200);

-- Benutzer hinzufügen
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'StarkesPW123!', 0);

-- Query-Routing-Regeln
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (1, 1, '^SELECT.*FOR UPDATE', 0, 1),
  (2, 1, '^SELECT', 1, 1);

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;

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: '127.0.0.1',
  port: 6033,
  user: 'appuser',
  password: 'StarkesPW123!',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 20,
  queueLimit: 0,
  enableKeepAlive: true
});

async function getUsers() {
  const [rows] = await pool.execute('SELECT * FROM users WHERE active = ?', [1]);
  return rows;
}

from sqlalchemy import create_engine, text
from sqlalchemy.pool import QueuePool

engine = create_engine(
    'postgresql://appuser:StarkesPW123!@127.0.0.1:6432/myapp',
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=1800,
    pool_pre_ping=True
)

with engine.connect() as conn:
    result = conn.execute(text('SELECT * FROM users WHERE active = :active'), {'active': 1})
    users = result.fetchall()