Пул соединений с базой данных: 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;