Datenbank-Connection-Pooling: PgBouncer und ProxySQL

Datenbank-Connection-Pooling ermöglicht es Anwendungen, vorhandene Datenbankverbindungen wiederzuverwenden, anstatt für jede Anfrage eine neue Verbindung zu öffnen. Dies reduziert den Verbindungsaufbau-Overhead, begrenzt die Anzahl der Verbindungen auf dem Datenbankserver und verbessert die Gesamtleistung erheblich.

Warum Connection Pooling verwenden?

Jede Datenbankverbindung verbraucht Speicher und CPU-Ressourcen auf dem Server. Bei Anwendungen mit hohem Traffic:

Szenario	Ohne Connection Pool	Mit Connection Pool
1000 gleichzeitige Anfragen	1000 DB-Verbindungen	20-50 DB-Verbindungen
Verbindungszeit	5-50ms pro Anfrage	Null (Wiederverwendung)
Speicherverbrauch	Hoch	Niedrig
Skalierbarkeit	Begrenzt	Hoch

PgBouncer für PostgreSQL und ProxySQL für MySQL/MariaDB sind die am häufigsten verwendeten Connection-Pooling-Lösungen. Beide sind in Produktionsumgebungen bewährt.

PgBouncer Installation (PostgreSQL)

Installation

hljs bash

# Ubuntu/Debian
apt update
apt install -y pgbouncer

# CentOS/AlmaLinux
dnf install -y pgbouncer

# Version prüfen
pgbouncer --version

pgbouncer.ini Konfiguration

hljs bash

nano /etc/pgbouncer/pgbouncer.ini

hljs 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

Benutzerliste erstellen

hljs bash

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

hljs bash

systemctl start pgbouncer
systemctl enable pgbouncer
systemctl status pgbouncer

PgBouncer Pool-Modi

hljs ini

; 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

PgBouncer Monitoring

hljs bash

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

hljs sql

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

ProxySQL Installation (MySQL/MariaDB)

hljs bash

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

ProxySQL Konfiguration

hljs bash

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

hljs sql

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

Node.js Integration

hljs javascript

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

Python Integration

hljs python

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()

Fazit

Connection Pooling verbessert die Datenbankleistung bei Anwendungen mit hohem Traffic erheblich. PgBouncer für PostgreSQL und ProxySQL für MySQL sind die ausgereiftesten und zuverlässigsten Lösungen. Mit der richtigen Pool-Modus-Auswahl (Transaction-Modus bietet meist die beste Balance) erzielen Sie maximale Datenbankleistung auf REXE-Servern.

Datenbank-Connection-Pooling: PgBouncer und ProxySQL

Inhaltsverzeichnis