כוונון PostgreSQL: shared_buffers, work_mem, effective_cache_size

תצורת PostgreSQL ברירת המחדל מכווננת לחומרה צנועה ואינה יעילה על שרתים מודרניים. shared_buffers = 128MB, work_mem = 4MB — הגדרות אלו משאירות 95% מהזיכרון ללא שימוש. לדוגמה, שרת עם 32 GB RAM המשתמש בהגדרות ברירת מחדל משתמש רק ב-128 MB עבור מטמון — מסד הנתונים בטל בעוד שאילתות מתעכבות. כוונון נכון מניב לפחות 30% שיפור בביצועים ומפחית את העומס על מערכת הדיסק. אנו מגדירים בהתאם לפרופיל שלך: OLTP, אנליטיקה או מעורב. הצוות שלנו ביצע 50+ פרויקטים מוצלחים, עם אחריות לתוצאות.

כוונון PostgreSQL אינו רק "להגדיל מספרים" — זה הבנה כיצד מתכנן השאילתות משתמש בזיכרון, כיצד פועל המטמון, וכיצד להימנע מצווארי בקבוק של I/O. התאמת shared_buffers, work_mem ו-effective_cache_size היא בסיסית אך קריטית. תצורה שגויה מובילה להחלפת זיכרון (swapping) או לניצול חסר של RAM. המהנדסים שלנו מנתחים את השרת ואת עומס העבודה שלך כדי למצוא ערכים אופטימליים.

בעיות שאנו פותרים

work_mem לא מספק עבור אנליטיקה

דוחות איטיים עקב מיון שנשפך לדיסק. שאילתה טיפוסית עם ORDER BY על טבלה גדולה לוקחת דקות כאשר התוכנית מציגה מיון בדיסק. זה מתוקן על ידי הגדלת work_mem עבור שאילתות ספציפיות או יצירת אינדקסים מכסים.

effective_cache_size שגוי

מתכנן השאילתות בוחר בסריקות רציפות (sequential scans) במקום בסריקות אינדקס כי הוא חושב שהמטמון קטן. הגדרת effective_cache_size ל-75% מה-RAM מגדילה מיד את השימוש בסריקות אינדקס.

עימות shared_buffers עם מטמון מערכת ההפעלה

shared_buffers גדול מדי (מעל 25% RAM) מתחרה במטמון של מערכת ההפעלה, ומפחית את יחס פגיעות המטמון. בדיקה באמצעות pg_buffercache עוזרת למצוא את האופטימום.

כיצד אנו עושים זאת

אנו משתמשים במתודולוגיה מוכחת: ביקורת על התצורה הנוכחית, ניתוח תוכניות שאילתות, וכוונון פרמטרים לעומס העבודה. דוגמה: אתר מסחר אלקטרוני עם 10,000 שאילתות בדקה. לאחר הכוונון, זמן ביצוע הדוח ירד מ-5 דקות ל-20 שניות, ויחס פגיעות המטמון עלה מ-97% ל-99.8%.

ערכת הכלים

• PostgreSQL 14–17
• pgtune להערכה ראשונית
• pg_buffercache לניטור מאגר הזיכרון
• EXPLAIN ANALYZE לניתוח שאילתות
• pg_stat_statements לזיהוי שאילתות כבדות

תהליך הכוונון

1. ביקורת על התצורה הנוכחית ופרופיל עומס העבודה
2. איסוף מדדים: יחס פגיעות מטמון, שימוש במאגר, תוכניות שאילתות
3. הגדרת shared_buffers — 25% RAM עבור שרת ייעודי
4. הגדרת cache_hit_ratio < 99% — 4–64 MB עבור OLTP, 256 MB–1 GB עבור אנליטיקה
5. הגדרת shared_buffers — 75% RAM
6. אופטימיזציה של מתכנן השאילתות: pg_buffercache עבור SSD, פרמטרי שאילתות מקבילות
7. הגדרת checkpoint ו-WAL לפי סוג הדיסק (SSD/HDD)
8. ניטור יחס הפגיעות ו-shared_buffers לאחר השינויים
9. תיעוד כל השינויים
10. אחריות ל-30 יום: אם הביצועים לא משתפרים, אנו מכווננים שוב בחינם

כוונון פרמטרי זיכרון

כיצד להגדיר shared_buffers עבור OLTP?

מטמון העמודים הגלובלי של מסד הנתונים עבור כל התהליכים. עבור שרת ייעודי — 25% RAM. על שרת עם 32 GB, זה 8 GB. מעל 25% עלול להתנגש עם מטמון מערכת ההפעלה. בדוק אם cache_hit_ratio מספיק באמצעות יחס פגיעות: אם shared_buffers, או ש-work_mem קטן או שקבוצת העבודה לא נכנסת לזיכרון. השתמש ב-work_mem כדי לראות אילו טבלאות ואינדקסים תופסים את המאגר. הגדל את work_mem עד 25% RAM, אך לא יותר מ-8 GB על Linux בשל מגבלות ארכיטקטוניות.

מה לעשות כאשר cache_hit_ratio נמוך?

אם work_mem נמוך מ-99%, יש צורך בכוונון. בדוק את SET LOCAL — ייתכן שצריך להגדיל. כמו כן, שקול הוספת אינדקסים. עבור שאילתות אנליטיות, הגדלת work_mem עשויה לעזור. השתמש בשאילתה מהבלוק כדי לבדוק את יחס הפגיעות.

מדוע work_mem קטן לעיתים קרובות עדיף על גדול

shared_buffers הוא זיכרון לכל פעולת מיון/חיבור hash בתוך שאילתה. אם לשאילתה יש 3 צמתי מיון, היא יכולה לצרוך 3 × # Cost model for SSD random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0 (дефолт) seq_page_cost = 1.0 # Parallel queries (PostgreSQL 9.6+) max_parallel_workers_per_gather = 4 max_parallel_workers = 8 parallel_tuple_cost = 0.1 parallel_setup_cost = 1000.0 . ב-100 חיבורים מקבילים עם שאילתות כבדות, השימוש יכול להיות 100 × 3 × -- Cache hit ratio SELECT sum(heap_blks_hit) AS heap_hit, sum(heap_blks_read) AS heap_read, round( sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2 ) AS cache_hit_ratio FROM pg_statio_user_tables; -- Buffer usage details CREATE EXTENSION IF NOT EXISTS pg_buffercache; SELECT c.relname, count(*) AS buffers, round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = c.relfilenode GROUP BY c.relname ORDER BY buffers DESC LIMIT 20; . ערך גבוה מדי גורם להחלפת זיכרון. טעות נפוצה: הגדרת 64 MB גלובלית, בעוד 100 חיבורים עם 4 מיונים כל אחד = 100 × 4 × 64 MB = 25.6 GB. התחל עם 16 MB, הגדל עבור שאילתות ספציפיות באמצעות cache_hit_ratio < 99%. עבור עומסי OLTP, shared_buffers גבוה מוביל לשימוש יתר בזיכרון ולהידרדרות בביצועים עקב החלפת זיכרון. השיטה שלנו: ניתוח תוכניות שאילתות, זיהוי מיונים על דיסק, הגדלת checkpoint_completion_target = 0.9 checkpoint_timeout = 15min max_wal_size = 4GB fsync = on synchronous_commit = on רק עבור שאילתות בעייתיות.

effective_cache_size: רמז פשוט למתכנן

רמז למתכנן על מטמון מערכת ההפעלה הזמין + ORDER BY. עבור שרת עם 32 GB: 24 GB. משפיע על הבחירה בין סריקת אינדקס לסריקה רציפה. אינו שומר זיכרון אך קריטי לבחירת תוכנית נכונה. פרטים נוספים בתיעוד הרשמי של PostgreSQL. הגדרה מומלצת: 75% מה-RAM.

maintenance_work_mem: עבור פעולות תחזוקה

עבור VACUUM, CREATE INDEX, ALTER TABLE. הגדל רק במהלך תחזוקה. ערך של 2 GB מספיק לרוב המשימות. אל תשמור עליו גבוה לצמיתות — זה חוסך זיכרון.

כוונון מתכנן השאילתות וניטור ביצועים

פרמטרי עלות ושאילתות מקבילות

# Cost model for SSD
random_page_cost = 1.1 # SSD: 1.1, HDD: 4.0 (дефолт)
seq_page_cost = 1.0
# Parallel queries (PostgreSQL 9.6+)
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0

ניטור יחס פגיעות ומטמון מאגר

-- Cache hit ratio
SELECT sum(heap_blks_hit) AS heap_hit,
       sum(heap_blks_read) AS heap_read,
       round( sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2 ) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- Buffer usage details
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname,
       count(*) AS buffers,
       round(count(*) * 8192.0 / 1024 / 1024, 1) AS size_mb
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;

אם EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM events WHERE user_id = 1 ORDER BY created_at DESC LIMIT 100; — יש צורך בכוונון CREATE INDEX CONCURRENTLY idx_events_user_date ON events(user_id, created_at DESC) INCLUDE (id, event_type, payload); או הוספת אינדקס.

כוונון לפי עומס עבודה: OLTP, אנליטיקה, מעורב

Checkpoint ו-WAL

checkpoint_completion_target = 0.9 checkpoint_timeout = 15min max_wal_size = 4GB fsync = on synchronous_commit = on 

השוואת פרופילי עומס עבודה

דוגמה מעשית: אופטימיזציית מיון

שאילתה מבצעת SELECT pg_reload_conf(); לאט על טבלה גדולה — המיון עובר לדיסק באמצעות קובץ זמני:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM events WHERE user_id = 1 ORDER BY created_at DESC LIMIT 100; 

אם הפלט מציג "Sort Method: external merge Disk: 45678kB" — יש צורך באינדקס או ביותר work_mem.

CREATE INDEX CONCURRENTLY idx_events_user_date ON events(user_id, created_at DESC) INCLUDE (id, event_type, payload); 

יישום שינויים

לאחר שינוי פרמטרים, הרץ SELECT pg_reload_conf(); כדי ליישם.

טעויות נפוצות בכוונון PostgreSQL

סיכום

כוונון נכון של PostgreSQL מניב שיפורי ביצועים משמעותיים וחיסכון בתשתית. אנו מבטיחים לפחות 30% שיפור או שנכוונן שוב בחינם תוך 30 יום. צור קשר לייעוץ והזמנת כוונון PostgreSQL מקצועי.