تجميع اتصالات قاعدة البيانات: PgBouncer و ProxySQL

# Ubuntu/Debian
apt update
apt install -y pgbouncer

# CentOS/AlmaLinux
dnf install -y pgbouncer

# التحقق من الإصدار
pgbouncer --version

nano /etc/pgbouncer/pgbouncer.ini

[databases]
; اسم مستعار لقاعدة البيانات = معلومات الاتصال
myapp = host=127.0.0.1 port=5432 dbname=myapp
analytics = host=127.0.0.1 port=5432 dbname=analytics

[pgbouncer]
; عنوان ومنفذ الاستماع
listen_addr = 127.0.0.1
listen_port = 6432

; المصادقة
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; وضع التجميع: session, transaction, statement
pool_mode = transaction

; حدود الاتصالات
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; مهلة الاتصال
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15

; إعدادات السجل
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

; الواجهة الإدارية
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

# الحصول على هاشات المستخدمين من PostgreSQL
psql -U postgres -c "SELECT '\"' || usename || '\"' || ' \"' || passwd || '\"' FROM pg_shadow;"

# إنشاء ملف userlist.txt
nano /etc/pgbouncer/userlist.txt

"appuser" "md5HASH_HERE"
"pgbouncer_admin" "md5HASH_HERE"

# تشغيل الخدمة
systemctl start pgbouncer
systemctl enable pgbouncer

# التحقق من الحالة
systemctl status pgbouncer

; وضع SESSION: اتصال خادم واحد لكل اتصال عميل
; الأكثر توافقاً، الأقل كفاءة
pool_mode = session

; وضع TRANSACTION: الاتصال يُحتجز فقط طوال مدة المعاملة
; الوضع الموصى به، مثالي لمعظم التطبيقات
pool_mode = transaction

; وضع STATEMENT: الاتصال يُحرَّر بعد كل عبارة SQL
; الأكثر كفاءة لكن لا يدعم 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;

# لـ Ubuntu/Debian
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.x.x/repo_pub_key' | apt-key add -
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.x.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list
apt update
apt install -y proxysql2

# بديل: تنزيل الحزمة مباشرة
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

# الاتصال بالواجهة الإدارية (افتراضي: admin/admin)
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

-- إضافة خوادم MySQL
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES
  (0, '127.0.0.1', 3306, 1000, 200),    -- مجموعة الكتابة (master)
  (1, '192.168.1.101', 3306, 1000, 200), -- مجموعة القراءة (replica 1)
  (1, '192.168.1.102', 3306, 1000, 200); -- مجموعة القراءة (replica 2)

-- إضافة مستخدم
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'StrongPass123!', 0);

-- قواعد التوجيه: توجيه SELECT إلى مجموعة القراءة
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);              -- باقي SELECT -> replica

-- تطبيق التغييرات
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;

-- حالة تجميع الاتصالات
SELECT * FROM stats_mysql_connection_pool;

-- إحصائيات الاستعلامات
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;

-- حالة الخوادم
SELECT * FROM mysql_servers;

-- الاتصالات النشطة
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

# حدود الاتصالات
max_connections = 500
back_log = 100
wait_timeout = 600
interactive_timeout = 600

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

// إنشاء تجميع الاتصالات
const pool = mysql.createPool({
  host: '127.0.0.1',
  port: 6033,          // منفذ ProxySQL
  user: 'appuser',
  password: 'StrongPass123!',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 20,  // حجم التجميع
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

async function getUsers() {
  const [rows] = await pool.execute('SELECT * FROM users WHERE active = ?', [1]);
  return rows;
  // الاتصال يعود تلقائياً إلى التجميع
}

const { Pool } = require('pg');

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,          // منفذ PgBouncer
  user: 'appuser',
  password: 'StrongPass123!',
  database: 'myapp',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

async function query(text, params) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  console.log('وقت الاستعلام:', duration, 'مللي ثانية');
  return res;
}

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

# PostgreSQL + PgBouncer
engine = create_engine(
    'postgresql://appuser:StrongPass123!@127.0.0.1:6432/myapp',
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
    pool_recycle=1800,
    pool_pre_ping=True,
    echo=False
)

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

<?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,
];

try {
    $pdo = new PDO($dsn, 'appuser', 'StrongPass123!', $options);
} catch (PDOException $e) {
    throw new RuntimeException('فشل الاتصال بقاعدة البيانات: ' . $e->getMessage());
}

[pgbouncer]
; للبيئات عالية الحركة
max_client_conn = 2000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10

; مدة حياة الاتصال
server_lifetime = 3600
server_idle_timeout = 300

; تحسين TCP
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5

-- زيادة حجم تجميع الاتصالات
UPDATE global_variables SET variable_value = '500'
WHERE variable_name = 'mysql-max_connections';

-- مدة إعادة استخدام الاتصال
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;