Get and analyze AlloyDB explain plans

כדי לזהות צווארי בקבוק בביצועים ולבצע אופטימיזציה של פעולות במסד הנתונים של AlloyDB ל-PostgreSQL, אפשר לקבל ולנתח תוכניות ביצוע. תוכנית ביצוע או תוכנית EXPLAIN היא ייצוג מפורט של האופן שבו המנוע של מסד הנתונים של AlloyDB מתכוון לבצע שאילתת SQL. תוכנית הביצוע מורכבת מעץ של צמתים שמתווה את רצף הפעולות, כמו סריקות טבלה, הצטרפויות, מיון וצבירות, שמסד הנתונים של AlloyDB מבצע כדי לאחזר את הנתונים המבוקשים. כל שלב בתוכנית הזו נקרא צומת.

תוכנית ביצוע מתקבלת באמצעות הפקודה EXPLAIN, שמחזירה את התוכנית שמתכנן השאילתות של AlloyDB יוצר עבור הצהרת SQL נתונה. כלי לתכנון שאילתות, שנקרא גם כלי אופטימיזציה, קובע את הדרך היעילה ביותר להפעיל שאילתת SQL נתונה.

תוכניות הביצוע כוללות את הרכיבים הבאים:

  • צמתי תוכנית: הצמתים האלה מייצגים את השלבים השונים בהרצת השאילתה, כמו סריקה, צירוף או פעולת מיון.
  • זמן הביצוע: תוכנית ה-EXPLAIN כוללת את זמן הביצוע המשוער או בפועל של כל שלב, וכך עוזרת לכם לזהות צווארי בקבוק במסד הנתונים.
  • השימוש במאגר זמני: כאן מוצגת כמות הנתונים שנקראים מהדיסק לעומת המטמון, וכך אפשר לזהות בעיות בקריאת הדיסק.
  • הגדרות הפרמטרים: התוכנית מציגה את הגדרות הפרמטרים שבתוקף במהלך ביצוע השאילתה.

‫PostgreSQL, ובאופן נרחב יותר AlloyDB, תומך בתוכניות ביצוע עבור ההצהרות הבאות:

  • SELECT
  • INSERT
  • UPDATE
  • DECLARE CURSOR
  • CREATE AS
  • CREATE MATERIALIZED VIEW
  • REFRESH MATERIALIZED VIEW
  • EXECUTE

לפני שמתחילים

צריך שיהיו לכם אשכול ומופע של AlloyDB. מידע נוסף זמין במאמר בנושא יצירת אשכול ומופע ראשי שלו.

יצירת תוכנית ביצוע

אתם יוצרים תוכנית ביצוע מאפליקציית לקוח כמו psql, ‏ pgAdmin או DBeaver. ‫AlloyDB תומך ביצירת תוכניות ביצוע בפורמט טקסט או JSON.

כדי ליצור תוכנית ביצוע:

  1. חיבור לקוח psql למופע.

  2. כדי ליצור תוכנית ביצוע בפורמט טקסט, מריצים את הפקודה הבאה:

    SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

  3. כדי ליצור תוכנית ביצוע בפורמט JSON, מריצים את הפקודה הבאה:

    SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)

    הפקודה EXPLAIN כוללת את כל האפשרויות הזמינות – analyze,‏ verbose,‏ columnar_engine,‏ costs,‏ settings,‏ buffers,‏ wal,‏ timing ו-summary – כדי ליצור תוכנית ביצוע מפורטת לשאילתה נתונה בפורמט טקסט או JSON. האפשרות analyze מציינת שהשאילתה מופעלת כדי לספק נתונים סטטיסטיים בפועל של זמן הריצה, וגם את האומדנים של מתכנן השאילתות.

הצגה וניתוח של נתונים בתוכנית EXPLAIN

אחרי שמקבלים תוכנית ביצוע, אפשר לראות ולנתח את התוצאות.

כברירת מחדל, הפלט של EXPLAIN מציג את פעילות השאילתות בצד השרת. כדי למדוד את זמן הלוך ושוב מקצה לקצה, משתמשים באפשרות /timing ב-psql ומייצאים את התוצאות אל /dev/null.

כדי לראות את תוכנית הביצוע שיצרתם, משתמשים בפקודה EXPLAIN לפני שאילתת ה-SQL.

  • ‫EXPLAIN SELECT...: מציג את התוכנית שהכלי לאופטימיזציה יבחר בלי להריץ את השאילתה.
  • ‫EXPLAIN ANALYZE SELECT...: מריץ את השאילתה ומציג את תוכנית החיזוי ואת נתוני הביצוע בפועל, כולל זמני הריצה האמיתיים ומספר השורות.

EXPLAIN בלי ANALYZE

כדי להציג את העלויות המשוערות של השאילתה בכלי לתכנון שאילתות, מריצים הצהרת EXPLAIN בלי האפשרות ANALYZE .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

הפלט של התוכנית כולל את הנתונים הבאים:

  • ‫cost = 0.00..1735481.00: המספר הראשון מציין את העלות של אחזור השורה הראשונה. המספר השני מציין את העלות של שליפת השורה האחרונה.
  • ‫rows = 100000000: זהו מספר השורות המשוער שהשאילתה מחזירה.
  • ‫width = 27: זהו הרוחב המשוער של השורה שהוחזרה, שעוזר להבין את הבלוקים שאליהם הייתה גישה.

האפשרות ANALYZE

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

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

הפלט של התוכנית כולל את הנתונים הבאים:

  • ‫actual time (in ms) = 0.165..9342.424: כאן מוצג הזמן בפועל שנדרש להחזרת השורה הראשונה והזמן הכולל שנדרש להחזרת כל השורות.
  • ‫rows = 100000001: זהו מספר השורות בפועל שמוחזר.
  • ‫loops = 1: הערך הזה חשוב לצמתים של לולאות מקוננות. אם הערך של loops גדול מ-1, מוצג הזמן הממוצע לכל לולאה.
  • ‫Planning Time: 0.025 ms: כאן מצוין הזמן שלקח לכלי התכנון לקבוע את נתיב הביצוע.
  • ‫Execution Time: 13674.794 ms: כאן מצוין משך הביצוע אחרי שהכלי לתכנון קבע את הנתיב.
  • זמן הביצוע הכולל: הסכום של Planning Time ושל Execution Time. (0.025 + 13674.794 = 13674.819)

האפשרות VERBOSE

כדי להוסיף מידע נוסף לתוכנית הביצוע, משתמשים באפשרות VERBOSE. בדוגמה הבאה, השימוש ב-VERBOSE מוסיף שמות טבלאות עם כישורים של סכימה ומציג מזהה שאילתה פנימי שבו אפשר להשתמש כדי לקשר את השאילתה לכלי מעקב אחרים.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

הפלט של התוכנית כולל את הנתונים הבאים:

  • Output Node: רשימת העמודות שנכללות בשאילתה. לפעמים כלי התכנון כולל יותר עמודות ממה שביקשתם, אם הוא קובע שיהיה יקר יותר להשמיט אותן.
  • מזהה שאילתה: המזהה של PostgreSQL שמופה ל-pg_stat_statements.
  • מזהה שאילתה ב-AlloyDB: מזהה השאילתה ב-AlloyDB שאפשר להשתמש בו כדי לקשר בין נתוני תובנות לגבי שאילתות

האפשרות COLUMNAR ENGINE

כדי להציג מידע על מנוע מבוסס-עמודות של AlloyDB, מוסיפים את האפשרות COLUMNAR_ENGINE.

אם הטבלה לא מופיעה במנוע העמודות, אפשר לראות את הסטטוס בעמודה Columnar Check בתוכנית ההסבר הבאה.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

אם הטבלה קיימת במנוע העמודות ונעשה שימוש בעמודות, יופיע Custom Scan לצד נתונים סטטיסטיים על השימוש במנוע העמודות.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

הפלט של התוכנית כולל את הנתונים הבאים:

  • מסנן שאילתה (פרדיקט): כאן מוצג המסנן שהוחל, אם נעשה שימוש במסנן.
  • Rows Removed by Columnar Filter: מספר השורות שהוסרו על ידי המסנן של העמודה.
  • בייטים שאוחזרו ממטמון האחסון: מספר הבייטים שאוחזרו ממטמון האחסון.
  • זמן ההחלפה: הזמן שנדרש להחלפת נתונים ממטמון ה-spill העמודתי (SSD) אם היחס לא מתאים לזיכרון.

האפשרות 'הגדרות'

כדי להציג הגדרות של סשן, מסד נתונים או הגדרות גלובליות שאינן ברירת המחדל שמתכנן התקציב משתמש בהן, מוסיפים את האפשרות SETTINGS.

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

האפשרות BUFFERS

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

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

המידע על המאגר כולל את הפרטים הבאים:

  • shared hit: מספר בלוקי הנתונים בגודל 8kB שנמצאו במטמון המאגר המשותף הראשי של PostgreSQL.
  • shared read: מספר בלוקי הנתונים בגודל 8kB שנקראו ממערכת ההפעלה. לרוב, זה מציין קלט/פלט של הדיסק.
  • ‫dirtied: מספר הבלוקים שלא שונו בעבר ששונו על ידי השאילתה (שינויים במפת החשיפה).
  • ‫written: מספר הבלוקים שסומנו בעבר כ'מלוכלכים' והוצאו מהמטמון על ידי קצה העורפי הזה במהלך עיבוד השאילתה, בדרך כלל בגלל שינויים בביט הרמז או במפת הנראות, ונכתבו לדיסק.
  • פגיעה במטמון מהיר במיוחד: מספר הבלוקים שאוחזרו מהמטמון המהיר במיוחד.
  • I/O Timings: משך הזמן של כל קלט/פלט של דיסק או SSD באלפיות השנייה.
  • תכנון: פעילות חיץ במהלך שלב התכנון, כמו קריאת מטא-נתונים או נתונים סטטיסטיים מטבלאות קטלוג.
  • הזמן שחלף במהלך התכנון בקלט/פלט (I/O): כאן מוצג הזמן שחלף בקלט/פלט אם יש מטא-נתונים שנדרשים לקריאה מהדיסק.

אפשרות WAL

כדי לספק מידע על פעילות Write-Ahead Logging‏ (WAL), משתמשים באפשרות WAL .

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

המידע ב-WAL כולל את הפרטים הבאים:

  • רשומות WAL: מספר רשומות ה-WAL שנקראו כדי לשמור על עקביות.
  • ‫WAL bytes: מספר הבייטים שנקראו מ-WAL כדי לשמור על עקביות.
  • רשומות ששונו אבל עדיין לא נשמרו בנקודת ביקורת: מציין רשומות ששונו אבל עדיין לא נשמרו בנקודת ביקורת.

המאמרים הבאים