הגדרת איגום חיבורים (PgBouncer/ProxySQL) עבור יישומי אינטרנט

לעתים קרובות אנו נתקלים במצבים שבהם כל בקשת HTTP ל-PostgreSQL או MySQL פותחת חיבור TCP חדש. יצירת חיבור אורכת 5–50 אלפיות השנייה ודורשת כ-5–10 MB של זיכרון בצד מסד הנתונים. עם 500 בקשות במקביל, מדובר ב-500 חיבורים, שכל אחד מחזיק תהליך רקע. ללא מאגד (pooler), מסד הנתונים מגיע במהירות למגבלת max_connections, מה שמוביל לשגיאות 500 ולהידרדרות בביצועים. איגוד חיבורים באמצעות PgBouncer או ProxySQL הוא סטנדרט ביישומי אינטרנט בפרודקשן. תצורה נכונה של מאגד יכולה להחזיר את ההשקעה תוך 2–3 חודשים על ידי הפחתת עלויות שרתי הענן בעד 70%. במאמר זה נסביר כיצד להגדיר PgBouncer עבור PostgreSQL ו-ProxySQL עבור MySQL כדי לחסל בעיות אלה. הניסיון הרב שלנו מראה שמאגד חיבורים מוגדר כהלכה יכול להפחית את העומס על מסד הנתונים ב-50–80% ולמנוע השבתות. מצב טרנזקציות ב-PgBouncer מגדיל את התפוקה פי 5–10 בהשוואה למצב סשן. צרו קשר לקבלת ביקורת—נמצא את הפתרון האופטימלי.

מתי זה נחוץ

תסמינים של בעיות חיבור: max_connections ב-PostgreSQL מגיע למגבלה (ברירת מחדל 100), האפליקציה שלכם זורקת FATAL: remaining connection slots are reserved, וזמני התגובה גדלים באופן לא ליניארי תחת עומס. עבור MySQL/MariaDB זה דומה—Too many connections. איגוד חיבורים נחוץ כאשר מספר העובדים השיא של האפליקציה עולה על 50 ואתם משתמשים ב-PHP-FPM, Gunicorn או Unicorn—כאשר כל תהליך מחזיק חיבור משלו. אופטימיזציה של חיבורי מסד נתונים יכולה לחסוך עד 70% מתקציב מסד הנתונים בענן.

כיצד PgBouncer מפחית עומס על PostgreSQL

PgBouncer הוא פרוקסי קל משקל (תהליך יחיד, כ-2 MB RAM) עם שלושה מצבי איגוד. אנו משתמשים לרוב במצב טרנזקציות: חיבור מוחזק רק למשך הטרנזקציה. זה מספק יעילות מקסימלית אך דורש התאמה של הצהרות מוכנות (prepared statements).

שלבים להגדרת PgBouncer

1. התקינו את החבילה: apt install pgbouncer
2. ערכו את קובץ התצורה /etc/pgbouncer/pgbouncer.ini (ראו להלן)
3. צרו קובץ משתמשים /etc/pgbouncer/userlist.txt
4. הפעילו מחדש את השירות: systemctl restart pgbouncer
5. הגדירו את האפליקציה שלכם להתחבר בפורט 6432

תצורה /etc/pgbouncer/pgbouncer.ini:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

עם [databases] myapp = host=127.0.0.1 port=5432 dbname=myapp [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 1000 default_pool_size = 20 min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 3 server_idle_timeout = 600 , PgBouncer שומר על לכל היותר 20 חיבורים אמיתיים ל-PostgreSQL תוך קבלת עד 1000 לקוחות. גודל המאגד צריך להיבחר לפי העומס: הנוסחה היא (מספר ליבות CPU של מסד הנתונים) * 2 + מספר הדיסקים. מסד נתונים מאוגד יכול לטפל ב-10,000 שאילתות/שנייה במקום 1000.

חיבור אפליקציה דרך PgBouncer

ב-Laravel .env:

DB_HOST=127.0.0.1 DB_PORT=6432 DB_DATABASE=myapp DB_USERNAME=myapp DB_PASSWORD=mysecret 

חשוב עבור Laravel במצב טרנזקציות: השבתו הצהרות מוכנות. ב-DB_HOST=127.0.0.1 DB_PORT=6432 DB_DATABASE=myapp DB_USERNAME=myapp DB_PASSWORD=mysecret :

'pgsql' => [ 'driver' => 'pgsql', 'options' => [ PDO::ATTR_EMULATE_PREPARES => true, ], ], 

עבור Django, באופן דומה, השתמשו ב-config/database.php.

מתי להשתמש ב-ProxySQL במקום PgBouncer

ProxySQL חזק משמעותית מ-PgBouncer: הוא תומך בניתוב שאילתות, הפרדת קריאה/כתיבה, ומעבר אוטומטי במקרה תקלה. הוא חיוני אם יש לכם אשכול MySQL עם רפליקות או שצריך חלוקת עומסים גמישה. ProxySQL מעבד בקשות פי 10 מהר יותר מהמתחרים.

שלבים להגדרת ProxySQL

1. הורידו והתקינו את החבילה
2. הפעילו את השירות: 'pgsql' => [ 'driver' => 'pgsql', 'options' => [ PDO::ATTR_EMULATE_PREPARES => true, ], ],
3. התחברו לממשק הניהול: systemctl start proxysql
4. הוסיפו שרתים וכללים

# Установка ProxySQL
wget https://github.com/sysown/proxysql/releases/download/v2.6.3/proxysql_2.6.3-ubuntu22_amd64.deb
dpkg -i proxysql_2.6.3-ubuntu22_amd64.deb
systemctl start proxysql
# Настройка через admin-интерфейс
mysql -h 127.0.0.1 -P 6032 -u admin -padmin
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '127.0.0.1', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

הפרדת קריאה/כתיבה באמצעות כללי שאילתות—שלחו את כל ה-SELECT (למעט FOR UPDATE) לרפליקות, וכל השאר למאסטר.

השוואה בין PgBouncer ו-ProxySQL

מצבי איגוד של PgBouncer

בעיות אופייניות באיגוד חיבורים

• הצהרות מוכנות במצב טרנזקציות. פתרון: אמולציה ברמת הדרייבר או מעבר למצב סשן.
• טבלאות זמניות ו-pg_temp לא עובדות במצב טרנזקציות. השתמשו ב-CTEs או בטבלאות קבועות.
• טרנזקציות ארוכות (>30 שניות) מפחיתות את יעילות המאגד. בצעו אופטימיזציה לשאילתות.

מה כלול בהתקנה?

• ביקורת על תצורת מסד הנתונים והאפליקציה הנוכחית.
• תצורת מאגד (PgBouncer או ProxySQL) מותאמת לעומס שלכם.
• אינטגרציה עם האפליקציה שלכם (Laravel, Django, Symfony וכו').
• בדיקות עומס עם ניטור.
• תיעוד תפעולי.
• הדרכת צוות.
• חודש תמיכה לאחר ההשקה.

לוח זמנים

התקנת PgBouncer בסיסית עם בדיקות אורכת יום עבודה אחד. ProxySQL עם הפרדת קריאה/כתיבה וניטור אורך 2–3 ימים. אם נדרשת אינטגרציה עם אפליקציה קיימת וניפוי הצהרות מוכנות, הוסיפו יום אחד. אנו מבטיחים שאחרי ההתקנה, מספר החיבורים המקבילים לא יהווה עוד צוואר בקבוק. ניתן להפחית את צריכת הזיכרון של שרת מסד הנתונים ב-80%. צרו קשר לקבלת ביקורת—נעזור לבחור את הפתרון האופטימלי לעומס שלכם.