Connection Pooling و مدیریت اتصال‌ها

چرا اتصال را دوباره استفاده کنیم؟

هر اتصال تازه یعنی handshake شبکه، احتمالاً TLS، احراز هویت و ساخت thread در سرور: چند میلی‌ثانیه برای هر درخواست. در بار بالا هم مسئله فقط زمان نیست؛ تعداد اتصال‌ها به max_connections می‌رسد و کاربران خطای Too many connections می‌بینند. Pool تعدادی اتصال باز نگه می‌دارد و به درخواست‌ها قرض می‌دهد.

حساب‌وکتاب ساده

اگر gunicorn با ۴ پروسه و هر کدام ۲ thread اجرا شود و CONN_MAX_AGE بزرگ‌تر از صفر باشد، هر سرور برنامه حداکثر ۸ اتصال پایدار دارد. سه سرور برنامه یعنی ۲۴، به‌علاوه‌ی workerهای Celery و کاربران مدیریتی. max_connections باید این جمع را به‌علاوه‌ی حاشیه‌ی امن پوشش دهد. زیر ASGI توصیه‌ی خود جنگو CONN_MAX_AGE = 0 و استفاده از pooler بیرونی است.

SQLAlchemy برای اسکریپت‌ها و سرویس‌ها

from sqlalchemy import create_engine, text

engine = create_engine(
    "mysql+mysqldb://web:secret@127.0.0.1:3306/carpet_factory?charset=utf8mb4",
    pool_size=10,        # اتصال‌های همیشه باز
    max_overflow=5,      # اتصال اضافه در اوج بار
    pool_timeout=10,     # انتظار برای اتصال آزاد، بعد خطا
    pool_recycle=1800,   # کمتر از wait_timeout سرور
    pool_pre_ping=True,  # بررسی زنده بودن پیش از قرض دادن
)

with engine.begin() as conn:
    rows = conn.execute(
        text("SELECT code, hall FROM looms WHERE is_active = :a"), {"a": True}
    ).all()

پایش اتصال‌ها در سرور

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client, command, COUNT(*) AS n
FROM performance_schema.processlist
GROUP BY user, client, command
ORDER BY n DESC;

SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Aborted_c%';

اگر ده‌ها اتصال با command برابر Sleep و زمان طولانی از یک میزبان می‌بینید، احتمالاً برنامه‌ای اتصال را باز می‌کند و نمی‌بندد.

نکته‌هایی که کمتر کسی می‌داند

• pool_recycle باید از wait_timeout سرور کمتر باشد؛ وگرنه صبح اولین درخواست با MySQL server has gone away (خطای 2006) شکست می‌خورد، چون سرور شب اتصال بی‌کار را بسته است.
• اتصال قرضی وضعیت session را با خود می‌برد: متغیرهای SET @x، جدول‌های موقت و SET SESSION. در کد برنامه از تغییر وضعیت session پرهیز کنید یا قبل از برگرداندن پاکش کنید.
• اتصالی که قبل از fork در پروسه‌ی والد ساخته شده نباید در فرزندان (مثل workerهای Celery در حالت prefork) استفاده شود؛ نتیجه خطاهای عجیبی مثل Packet sequence number wrong است. در SQLAlchemy بعد از fork engine.dispose() بزنید.
• اگر Threads_created پیوسته بالا می‌رود، سرور برای هر اتصال thread تازه می‌سازد؛ بزرگ‌تر کردن thread_cache_size هزینه‌ی اتصال را کم می‌کند.
• ProxySQL برای اتصالی که از متغیر کاربری، جدول موقت یا تراکنش باز استفاده کند multiplexing را خاموش می‌کند؛ کد پر از SET @var مزیتش را از بین می‌برد.