קבלת תוכניות הסבר וניתוח שלהן ב-AlloyDB

כדי לזהות צווארי בקבוק בביצועים ולשפר את הפעולות במסד הנתונים של 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 without 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

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

  • צומת פלט: מציג את העמודות שנכללות בשאילתה. לפעמים הכלי לתכנון מסלולים כולל יותר עמודות מהנדרש, אם הוא קובע שהשמטה שלהן תהיה יקרה יותר.
  • מזהה שאילתה: המזהה של 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: מספר השורות שהוסרו על ידי המסנן העמודתי.
  • בייטים שאוחזרו ממטמון האחסון: מספר הבייטים שאוחזרו ממטמון האחסון.
  • זמן החלפה: הזמן שנדרש להחלפת נתונים ממטמון העמודות (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

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

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

אפשרות 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 כדי לשמור על עקביות.
  • רשומות ששונו אבל עדיין לא נשמרו בנקודת ביקורת: מציין רשומות ששונו אבל עדיין לא נשמרו בנקודת ביקורת.

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