על מה EXPLAIN שותק ואיך לגרום לזה לדבר

השאלה הקלאסית שמפתח מביא ל-DBA שלו או בעל עסק מביא ליועץ PostgreSQL כמעט תמיד נשמעת אותו הדבר: "מדוע לוקח כל כך הרבה זמן להשלים בקשות במסד הנתונים?"

סט מסורתי של סיבות:

• אלגוריתם לא יעיל
  כאשר אתה מחליט להצטרף למספר CTEs על פני כמה עשרות אלפי רשומות
• סטטיסטיקה מיושנת
  אם ההתפלגות בפועל של הנתונים בטבלה כבר שונה מאוד מזו שנאספה על ידי ANALYZE בפעם הקודמת
• "תקע" משאבים
  וכבר אין מספיק כוח מחשוב ייעודי של המעבד, ג'יגה-בייט של זיכרון נשאב כל הזמן, או שהדיסק לא יכול לעמוד בקצב של כל ה"רצונות" של מסד הנתונים
• חסימה מתהליכים מתחרים

ואם די קשה לתפוס ולנתח חסימות, אז לכל השאר שאנחנו צריכים תוכנית שאילתות, שניתן להשיג באמצעות אופרטור הסבר (עדיף, כמובן, מיד להסביר (לנתח, לחצץ) ...) או מודול auto_explain.

אבל, כפי שנאמר באותו תיעוד,

"הבנת תוכנית היא אמנות, וכדי לשלוט בה נדרשת מידה מסוימת של ניסיון..."

אבל אתה יכול להסתדר בלעדיו אם אתה משתמש בכלי הנכון!

איך נראית בדרך כלל תוכנית שאילתות? משהו כזה:

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

או ככה:

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

אבל לקרוא את התוכנית בטקסט "מהגיליון" קשה מאוד ולא ברור:

• מוצג בצומת סכום לפי משאבי תת-עץ
  כלומר, כדי להבין כמה זמן לקח להפעיל צומת מסוים, או כמה בדיוק הקריאה הזו מהטבלה העלתה נתונים מהדיסק, אתה צריך איכשהו להחסיר אחד מהשני
• יש צורך בזמן הצומת להכפיל בלולאות
  כן, חיסור היא לא הפעולה הכי מורכבת שצריך לעשות "בראש" - אחרי הכל, זמן הביצוע מצוין כממוצע לביצוע אחד של צומת, ויכולים להיות מאות מהם
• ובכן, וכל זה ביחד מונע מאיתנו לענות על השאלה המרכזית - אז מי "החוליה החלשה ביותר"?

כשניסינו להסביר את כל זה לכמה מאות מהמפתחים שלנו, הבנו שמבחוץ זה נראה בערך כך:

וזה אומר שאנחנו צריכים...

כלי

בו ניסינו לאסוף את כל המכניקה המרכזית שעוזרת להבין "מי אשם ומה לעשות" לפי התוכנית והבקשה. ובכן, ושתף חלק מהחוויה שלך עם הקהילה.
פגוש והשתמש - explain.tensor.ru

נראות של תוכניות

האם קל להבין את התוכנית כשהיא נראית כך?

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

לא ממש.

אבל ככה, בצורה מקוצרתכשמדדי המפתח מופרדים, זה הרבה יותר ברור:

אבל אם התוכנית תהיה מסובכת יותר, הוא יבוא לעזרה חלוקת זמן של piechart לפי צמתים:

ובכן, לאפשרויות הקשות ביותר הוא ממהר לעזור טבלת התקדמות:

לדוגמה, ישנם מצבים די לא טריוויאליים כאשר לתוכנית עשויה להיות יותר משורש אחד בפועל:

רמזים מבניים

ובכן, אם כל מבנה התוכנית והנקודות הכואבות שלה כבר מונחות וגלויות, למה לא להדגיש אותם ליזם ולהסביר אותם ב"שפה הרוסית"?

כבר אספנו כמה תריסר תבניות המלצות כאלה.

פרופיל שאילתות שורה אחר שורה

כעת, אם תצמיד את השאילתה המקורית על התוכנית המנותחת, תוכל לראות כמה זמן הושקע בכל הצהרה בנפרד - משהו כמו זה:

...או אפילו ככה:

החלפת פרמטרים לבקשה

אם "צירפת" לא רק בקשה לתוכנית, אלא גם את הפרמטרים שלה משורת DETAIL של היומן, תוכל בנוסף להעתיק אותה באחת מהאפשרויות:

• עם החלפת ערכים בבקשה
  לביצוע ישיר על הבסיס שלך ופרופיל נוסף

  SELECT 'const', 'param'::text;

• עם החלפת ערך באמצעות PREPARE/EXECUTE
  כדי לחקות את העבודה של המתזמן, כאשר ניתן להתעלם מהחלק הפרמטרי - למשל, כאשר עובדים על טבלאות מחולקות

  DEALLOCATE ALL;
  PREPARE q(text) AS SELECT 'const', $1::text;
  EXECUTE q('param'::text);
  

ארכיון תוכניות

הדבק, נתח, שתף עם עמיתים! התוכניות יישארו בארכיון ותוכל לחזור אליהן מאוחר יותר: explain.tensor.ru/archive

אבל אם אתה לא רוצה שאחרים יראו את התוכנית שלך, אל תשכח לסמן את התיבה "אל תפרסם בארכיון".

במאמרים הבאים אדבר על הקשיים וההחלטות שעולות בעת ניתוח תכנית.

מקור: www.habr.com