فرض کن یک اپلیکیشن داری که روز به روز بزرگ‌تر می‌شه. تعداد کاربرها افزایش پیدا می‌کنن، تعداد Instanceهای اپلیکیشن رو اضافه می‌کنی، همه چیز خوب به نظر می‌رسه. ولی یک روز متوجه می‌شی که دیتابیسِ PostgreSQL داره کُند می‌شه، حافظه‌اش داره تموم می‌شه، یا بدتر از این، با خطای too many connections مواجه می‌شی. مشکل از کجاست؟ احتمالاً بخاطر اینه که Connection Pooling نداری.

توی این پست توضیح می‌دم که Connection چیه، چرا مدیریت نکردنش می‌تونه گلوگاه دیتابیست بشه، و چطور ابزاری مثل pgBouncer این مشکل رو حل می‌کنه

شرح مشکل

وقتی یک اپلیکیشن می‌خواد با PostgreSQL حرف بزنه، باید یک Connection یا اتصال ایجاد کنه. این اتصال در واقع یک پراسس جداگانه روی سرور دیتابیسه.

هر Connection:

  • حدود ۵ تا ۱۰ مگابایت RAM مصرف می‌کنه، حتی وقتی هیچ کاری نمی‌کنه و فقط توی حالت Idle قرار داره.
  • یک TCP handshake کامل نیاز داره تا برقرار بشه.
  • یک پراسس جدید توی سیستم عامل ایجاد می‌کنه.

وقتی فقط یک Instance از اپلیکیشنت در حال اجراست، مشکلی نیست. ولی اگه اپلیکیشن Scale بشه چی می‌شه؟

فرض کن ۵ تا Instance از اپلیکیشنت داری، و هر کدوم ۱۰ تا Connection باز نگه می‌دارن. نتیجه؟ ۵۰ تا Connection داری که اکثرشون فقط Idle نشستن و منابع می‌خورن. در واقع Postgres داره برای اتصال‌های بی‌کار منابع خرج می‌کنه، نه برای پردازش واقعی.

و اگه اپلیکیشن رو بیشتر Scale کنی با خطای too many connections مواجه می‌شی چون به طور پیش‌فرض توی Postgres حداکثر تعداد اتصال‌ها روی عدد ۱۰۰ تنظیم شده.

راه حل: Connection Pooling

Connection pooling یعنی به جای اینکه هر بار یک Connection جدید بسازیم و بعد ببندیم، یک «استخر» یا Pool از Connectionهای آماده داشته باشیم و بینشون به اشتراک بذاریم. توی دیتابیس‌های مختلف راه‌حل‌های متفاوتی برای Connection Pooling وجود داره. برای مثال توی دیتابیس‌های MySQL میشه از ProxySQL برای Connection Pooling استفاده کرد یا برای MSSQL معمولاً از یک ابزار جداگانه برای مدیریت Connectionها استفاده نمی‌کنن و این موضوع توی سطح کد اپلیکیشن مدیریت میشه. ولی اگر از دیتابیس PostgreSQL استفاده می‌کنید، پیشنهاد من بهتون pgBouncer هست.

pgBouncer چیه؟

pgBouncer یک پروکسی سبک‌وزنه که بین اپلیکیشن و PostgreSQL می‌ذاریش. وظیفه‌اش اینه که:

  1. اتصال‌های زیاد از اپلیکشین‌ها رو قبول کنه.
  2. فقط تعداد کمی Connection واقعی به PostgreSQL باز نگه داره.
  3. این Connectionها رو بین درخواست‌های مختلف به اشتراک بذاره.

نتیجه؟ بیشتر از صد درخواست اتصال از اپلیکیشن‌ها وجود داره ولی فقط ۱۰ تا ۲۰ تا Connection واقعی به Postgres ایجاد میشه بنابراین دیتابیس سبک می‌مونه و منابع اضافی استفاده نمیشه.

pgBouncer سه حالت متفاوت برای Connection Pooling داره که بسته به نیازت باید یکی از اون‌ها رو انتخاب کنی:

  • Session Mode: هر کلاینت تا زمانی که Disconnect نکرده، یک Connection اختصاصی داره. این حالت بیشترین سازگاری و کمترین چالش رو داره، اما تاثیر زیادی توی کاهش تعداد Connectionها نداره.
  • Transaction Mode: اتصال به دیتابیس فقط برای مدت زمان اجرای یک تراکنش (بین دستور BEGIN و COMMIT) به اپلیکیشن داده می‌شه و بلافاصله پس از اتمام تراکنش، پس گرفته می‌شه تا به کلاینت دیگه‌ای داده بشه. این حالت برای اکثر وب‌اپلیکیشن‌ها بهترین انتخابه.
  • Statement Mode: توی این حالت، Connection بعد از هر کوئری SQL آزاد می‌شه. این روش بیشتر از دو روش قبلی باعث کاهش تعداد Connectionها میشه ولی با بعضی قابلیت‌ها مثل Prepared Statements سازگار نیست.

چرا از pgBouncer استفاده کنم؟

  1. کاهش مصرف حافظه روی سرور دیتابیس: به جای صدها پراسس Idle، چند ده تا Connection فعال داری.
  2. سرعت بیشتر: Connectionهای آماده توی Pool نیازی به TCP Handshake ندارن. درخواست‌ها سریع‌تر سرویس می‌گیرن.
  3. محافظت در برابر افزایش ناگهانی ترافیک: وقتی ناگهان ترافیک زیاد می‌شه، pgBouncer جلوی سیل Connection رو به PostgreSQL می‌گیره.
  4. سبک‌ بودن: pgBouncer منابع خیلی کمی مصرف می‌کنه و تقریباً Overhead نداره.

Prepared Statement چیه؟

وقتی یک Query به PostgreSQL می‌فرستی، دیتابیس قبل از اجرا چند مرحله رو طی می‌کنه:

Query ──► Parse ──► Plan ──► Execute ──► Result

مرحله‌های Parse و Plan وقت می‌برن. اگه یک Query رو بارها با پارامترهای مختلف اجرا کنی، هر بار این کار تکرار می‌شه.

برای بهبود سرعت اجرا، Query رو یک بار Parse و Plan می‌کنی، بهش یک اسم اختصاص می‌دی و بعد هر بار فقط پارامترها رو تحویل می‌دی و دیتابیس مستقیم می‌ره سراغ اجرای کوئری.

-- یک بار آماده‌سازی
PREPARE get_user (int) AS
    SELECT * FROM users WHERE id = $1;

-- بارها اجرا با پارامترهای مختلف
EXECUTE get_user(42);
EXECUTE get_user(99);
EXECUTE get_user(7);

اینجاست که داستان جالب می‌شه. PostgreSQL وقتی یک Prepared Statement می‌سازه، اون رو داخل همون Connection ذخیره می‌کنه و نه توی دیتابیس یا حافظه‌ی مشترک. بنابراین اگر Transaction یا Query بعدی با یک Connection دیگه انجام بشه، با خطای prepared statement does not exist مواجه می‌شیم.

توی نسخه‌های قدیمی pgBouncer این مشکل روی هر دو حالت Transaction و Statement وجود داشت ولی از نسخه‌ی 1.21 با تنظیم پارامتر max_prepared_statements توی فایل pgbouncer.ini میشه توی حالت Transaction از Prepared Statement استفاده کرد.

با تنظیم این پارامتر، pgBouncer خودش Prepared Statements رو ردیابی می‌کنه و وقتی Connection عوض می‌شه، اگه Statement روی اون Connection جدید وجود نداشته باشه، pgBouncer به صورت خودکار اون رو دوباره Prepare می‌کنه.

اکثر ORMها و کتابخانه‌های محبوب و متداول مثل SQLAlchemy یا GORM به صورت پیش‌فرض از Prepared Statements استفاده می‌کنن پس موقع استفاده از اونا باید به این موضوع توجه کرد.

حرف آخر

pgBouncer یکی از ساده‌ترین و موثرترین تغییراتیه که می‌تونی توی زیرساخت بدی. فقط باید قبل از پیاده‌سازی اون به این چندتا سوال در مورد اپلیکیشن و محیط عملیاتی اون پاسخ بدی:

  • تعداد Connectionهای فعلی Postgres چقدره؟
  • چند تا Instance از اپلیکیشن در حال اجراست؟
  • هر Instance چندتا Connection روی دیتابیس باز می‌کنه؟
  • آیا ORM اپلیکیشن از Prepared Statements استفاده می‌کنه؟