Veritabanı Bağlantı Havuzu: PgBouncer ve ProxySQL

# Ubuntu/Debian
apt update
apt install -y pgbouncer

# CentOS/AlmaLinux
dnf install -y pgbouncer

# Sürümü kontrol et
pgbouncer --version

nano /etc/pgbouncer/pgbouncer.ini

[databases]
; Veritabanı alias = bağlantı bilgileri
myapp = host=127.0.0.1 port=5432 dbname=myapp
analytics = host=127.0.0.1 port=5432 dbname=analytics

[pgbouncer]
; Dinleme adresi ve portu
listen_addr = 127.0.0.1
listen_port = 6432

; Kimlik doğrulama
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Havuz modu: session, transaction, statement
pool_mode = transaction

; Bağlantı limitleri
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; Bağlantı zaman aşımı
server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15

; Log ayarları
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

; Admin arayüzü
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

# PostgreSQL'den kullanıcı hash'lerini al
psql -U postgres -c "SELECT '\"' || usename || '\"' || ' \"' || passwd || '\"' FROM pg_shadow;"

# userlist.txt dosyasını oluştur
nano /etc/pgbouncer/userlist.txt

"appuser" "md5HASH_BURAYA"
"pgbouncer_admin" "md5HASH_BURAYA"

# Servisi başlat
systemctl start pgbouncer
systemctl enable pgbouncer

# Durumu kontrol et
systemctl status pgbouncer

; SESSION modu: Her istemci bağlantısı için bir sunucu bağlantısı
; En uyumlu mod, en az verimli
pool_mode = session

; TRANSACTION modu: Bağlantı sadece transaction süresince tutulur
; Önerilen mod, çoğu uygulama için ideal
pool_mode = transaction

; STATEMENT modu: Her SQL ifadesi için bağlantı serbest bırakılır
; En verimli ama prepared statements desteklenmez
pool_mode = statement

# Admin konsoluna bağlan
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

# Havuz istatistikleri
SHOW POOLS;

# İstemci bağlantıları
SHOW CLIENTS;

# Sunucu bağlantıları
SHOW SERVERS;

# Genel istatistikler
SHOW STATS;

# Yapılandırmayı yeniden yükle
RELOAD;

# Ubuntu/Debian için
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

# Alternatif: doğrudan paket indir
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

# Servisi başlat
systemctl start proxysql
systemctl enable proxysql

# Admin arayüzüne bağlan (varsayılan: admin/admin)
mysql -h 127.0.0.1 -P 6032 -u admin -padmin

-- MySQL sunucularını ekle
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections)
VALUES
  (0, '127.0.0.1', 3306, 1000, 200),   -- Yazma grubu (master)
  (1, '192.168.1.101', 3306, 1000, 200), -- Okuma grubu (replica 1)
  (1, '192.168.1.102', 3306, 1000, 200); -- Okuma grubu (replica 2)

-- Kullanıcı ekle
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'GucluSifre123!', 0);

-- Sorgu kuralları: SELECT'leri okuma grubuna yönlendir
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);              -- Diğer SELECT -> replica

-- Bağlantı havuzu ayarları
UPDATE mysql_variables SET variable_value = '200'
WHERE variable_name = 'mysql-max_connections';

UPDATE global_variables SET variable_value = '10'
WHERE variable_name = 'mysql-connection_max_age_ms';

-- Değişiklikleri uygula
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;

-- Bağlantı havuzu durumu
SELECT * FROM stats_mysql_connection_pool;

-- Sorgu istatistikleri
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;

-- Sunucu durumu
SELECT * FROM mysql_servers;

-- Aktif bağlantılar
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

# Bağlantı limitleri
max_connections = 500
back_log = 100
wait_timeout = 600
interactive_timeout = 600

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

// Bağlantı havuzu oluştur
const pool = mysql.createPool({
  host: '127.0.0.1',
  port: 6033,          // ProxySQL portu
  user: 'appuser',
  password: 'GucluSifre123!',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 20,  // Havuz boyutu
  queueLimit: 0,        // Sınırsız kuyruk
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
});

// Havuzdan bağlantı al ve kullan
async function getUsers() {
  const [rows] = await pool.execute('SELECT * FROM users WHERE active = ?', [1]);
  return rows;
  // Bağlantı otomatik olarak havuza geri döner
}

// Havuz istatistikleri
console.log('Havuz boyutu:', pool.pool._allConnections.length);

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

const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,          // PgBouncer portu
  user: 'appuser',
  password: 'GucluSifre123!',
  database: 'myapp',
  max: 20,             // Maksimum bağlantı
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Havuzu kullan
async function query(text, params) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  console.log('Sorgu süresi:', 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:GucluSifre123!@127.0.0.1:6432/myapp',
    poolclass=QueuePool,
    pool_size=10,           # Havuz boyutu
    max_overflow=20,        # Ek bağlantı limiti
    pool_timeout=30,        # Bağlantı bekleme süresi
    pool_recycle=1800,      # Bağlantı yenileme süresi (saniye)
    pool_pre_ping=True,     # Bağlantı sağlık kontrolü
    echo=False
)

# Bağlantıyı kullan
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:GucluSifre123!@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='GucluSifre123!',
        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
// PHP'de PDO kalıcı bağlantılar (persistent connections)
$dsn = 'mysql:host=127.0.0.1;port=6033;dbname=myapp;charset=utf8mb4';
$options = [
    PDO::ATTR_PERSISTENT => true,          // Kalıcı bağlantı
    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', 'GucluSifre123!', $options);
} catch (PDOException $e) {
    throw new RuntimeException('Veritabani baglantisi basarisiz: ' . $e->getMessage());
}

// Sorgu calistir
$stmt = $pdo->prepare('SELECT * FROM users WHERE active = ?');
$stmt->execute([1]);
$users = $stmt->fetchAll();

// config/database.php
'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '6033'),  // ProxySQL portu
    'database' => env('DB_DATABASE', 'myapp'),
    'username' => env('DB_USERNAME', 'appuser'),
    'password' => env('DB_PASSWORD', ''),
    'options' => [
        PDO::ATTR_PERSISTENT => true,
    ],
],

[pgbouncer]
; Yüksek trafikli ortamlar için
max_client_conn = 2000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10

; Bağlantı yaşam süresi
server_lifetime = 3600
server_idle_timeout = 300

; TCP optimizasyonu
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5

; Performans izleme
stats_period = 60

-- Bağlantı havuzu boyutunu artır
UPDATE global_variables SET variable_value = '500'
WHERE variable_name = 'mysql-max_connections';

-- Bağlantı yeniden kullanım süresi
UPDATE global_variables SET variable_value = '3600000'
WHERE variable_name = 'mysql-connection_max_age_ms';

-- Sorgu önbelleği
UPDATE global_variables SET variable_value = '1'
WHERE variable_name = 'mysql-query_cache_stores_empty_result';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;