יצירה וניהול של רמזים עם שמות

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

ההינטים בעלי השם ב-AlloyDB תומכים בכל ההינטים מהתוסף pg_hint_plan בקוד פתוח ובהינטים הבאים למנוע מבוסס-עמודות:

  • ‫ColumnarScan(table): מאלץ סריקה של עמודות בטבלה.

  • ‫NoColumnarScan(table): משבית סריקה של עמודות בטבלה.

ב-AlloyDB אפשר ליצור הינטים בעלי שם גם לשאילתות עם פרמטרים וגם לשאילתות בלי פרמטרים, שבמסמך הזה נקראות שאילתות תלויות פרמטרים.

תהליך עבודה

כדי להשתמש בהינטים בעלי שם:

  1. מזהים את השאילתה שרוצים ליצור בשבילה הינטים בעלי שם.

  2. יוצרים הינטים בעלי שם עם הינטים שיופעלו בפעם הבאה שהשאילתה תופעל.

  3. אימות ההחלה של ההינטים בעלי השם.

בדף הזה, הטבלה והאינדקס הבאים משמשים כדוגמאות:

CREATE TABLE t(a INT, b INT);
CREATE INDEX t_idx1 ON t(a);

  DROP EXTENSION IF EXISTS google_auto_hints;

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

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

  • מפעילים את התכונה "הינטים בעלי שם" במכונה. מגדירים את הדגל alloydb.enable_named_hints לערך on. אפשר להפעיל את הדגל הזה ברמת השרת או ברמת הסשן. כדי לצמצם את התקורה שעלולה לנבוע מהשימוש בתכונה הזו, צריך להפעיל את הדגל הזה רק ברמת הסשן.

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

    כדי לוודא שהדגל מופעל, מריצים את הפקודה show alloydb.enable_named_hints;. אם הדגל מופעל, הפלט מחזיר את הערך "on".

  • בכל מסד נתונים שבו רוצים להשתמש בהינטים בעלי שם, יוצרים תוסף במסד הנתונים מהמכונה הראשית של AlloyDB בתור המשתמש alloydbsuperuser או postgres:

    CREATE EXTENSION google_auto_hints CASCADE;

התפקידים הנדרשים

כדי לקבל את ההרשאות שדרושות ליצירה ולניהול של הינטים בעלי שם, בקשו מהאדמין להקצות לכם את התפקידים הבאים בממשק ניהול הזהויות והרשאות הגישה (IAM):

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

GRANT INSERT,DELETE,UPDATE ON hint_plan.plan_patches, hint_plan.hints TO role_name;
GRANT USAGE ON SEQUENCE hint_plan.hints_id_seq, hint_plan.plan_patches_id_seq TO role_name;

זיהוי השאילתה

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

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

  • מריצים את הפקודה EXPLAIN (VERBOSE), כמו בדוגמה הבאה:

    EXPLAIN (VERBOSE) SELECT * FROM t WHERE a = 99;
                        QUERY PLAN
----------------------------------------------------------
Seq Scan on public.t  (cost=0.00..38.25 rows=11 width=8)
  Output: a, b
  Filter: (t.a = 99)
Query Identifier: -6875839275481643436

    בפלט, מזהה השאילתה הוא -6875839275481643436.

  • שליחת שאילתה לתצוגה pg_stat_statements.

    אם הפעלתם את התוסף pg_stat_statements, אתם יכולים למצוא את מזהה השאילתה באמצעות שליחת שאילתה לתצוגה pg_stat_statements, כמו בדוגמה הבאה:

    select query, queryid from pg_stat_statements;

יצירת הינטים בעלי שם

כדי ליצור הינטים בעלי שם, משתמשים בפונקציה google_create_named_hints(), שיוצרת שיוך בין השאילתה לבין ההינטים במסד הנתונים.

SELECT google_create_named_hints(
HINTS_NAME=>'HINTS_NAME',
SQL_ID=>QUERY_ID,
SQL_TEXT=>QUERY_TEXT,
APPLICATION_NAME=>'APPLICATION_NAME',
HINTS=>'HINTS',
DISABLED=>DISABLED);

מחליפים את מה שכתוב בשדות הבאים:

  • ‫HINTS_NAME: שם להינטים בעלי השם. המזהה הזה חייב להיות ייחודי במסד הנתונים.

  • ‫SQL_ID (אופציונלי): מזהה השאילתה שעבורה אתם יוצרים את ההינטים בעלי השם.

    כדי ליצור הינטים בעלי שם, אתם יכולים להשתמש במזהה השאילתה או בטקסט של השאילתה (הפרמטר SQL_TEXT). עם זאת, מומלץ להשתמש במזהה השאילתה כדי ליצור הינטים בעלי שם, כי AlloyDB מאתר באופן אוטומטי את טקסט השאילתה המנורמל על סמך מזהה השאילתה.

  • ‫SQL_TEXT (אופציונלי): טקסט השאילתה של השאילתה שעבורה יוצרים את ההינטים בעלי השם.

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

  • ‫APPLICATION_NAME (אופציונלי): שם אפליקציית הלקוח של הסשן שבו רוצים להשתמש בהינטים בעלי השם. מחרוזת ריקה מאפשרת להחיל את ההינטים בעלי השם על השאילתה, בלי קשר לאפליקציית הלקוח שמנפיקה את השאילתה.

  • ‫HINTS: רשימה מופרדת ברווחים של ההנחיות לשאילתה.

  • DISABLED (אופציונלי): BOOL. אם הערך הוא TRUE, ההינטים בעלי השם נוצרים בהתחלה כהינטים מושבתים.

דוגמה:

SELECT google_create_named_hints(
HINTS_NAME=>'my_hint1',
SQL_ID=>-6875839275481643436,
SQL_TEXT=>NULL,
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL);

השאילתה הזו יוצרת הינט בעל שם בשם my_hint1. ההנחיה IndexScan(t) מיושמת על ידי המתכנן כדי לאלץ סריקת אינדקס בטבלה t בהרצה הבאה של שאילתת הדוגמה הזו.

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

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

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

יצירת הינטים בעלי שם שרגישים לפרמטרים

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

לדוגמה, הרצת השאילתה הבאה מאפשרת לשאילתה אחרת, כמו SELECT * FROM t WHERE a = 99;, להשתמש בהינטים בעלי השם my_hint2 כברירת מחדל.

SELECT google_create_named_hints(
  HINTS_NAME=>'my_hint2',
  SQL_ID=>NULL,
  SQL_TEXT=>'SELECT * FROM t WHERE a = ?;',
  APPLICATION_NAME=>'',
  HINTS=>'SeqScan(t)',
  DISABLED=>NULL);

לאחר מכן, שאילתה כמו SELECT * FROM t WHERE a = 99; יכולה להשתמש בהינטים בעלי השם my_hint2 כברירת מחדל.

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

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

כדי ליצור הינטים בעלי שם שרגישים לפרמטרים, מגדירים את הפרמטר SENSITIVE_TO_PARAM של הפונקציה google_create_named_hints() לערך TRUE, כמו בדוגמה הבאה:

SELECT google_create_named_hints(
HINTS_NAME=>'my_hint3',
SQL_ID=>NULL,
SQL_TEXT=>'SELECT * FROM t WHERE a = 88;',
APPLICATION_NAME=>'',
HINTS=>'IndexScan(t)',
DISABLED=>NULL,
SENSITIVE_TO_PARAM=>TRUE);

בשאילתה SELECT * FROM t WHERE a = 99; אי אפשר להשתמש בהינטים בעלי השם my_hint3, כי הליטרל "99" לא תואם ל-"88".

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

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

אימות היישום של ההינטים בעלי השם

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

  • משתמשים בפקודה EXPLAIN או בפקודה EXPLAIN (ANALYZE).

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

    SET pg_hint_plan.debug_print = ON;
SET client_min_messages = LOG;

  • משתמשים בתוסף auto_explain.

ניהול הינטים בעלי שם

ב-AlloyDB אפשר לראות, להפעיל, להשבית ולמחוק הינטים בעלי שם.

הצגת הינטים בעלי שם

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

postgres=>\x
postgres=>select * from google_named_hints_view limit 1;
-[ RECORD 1 ]-----+-----------------------------
hints_name | my_hint1
sql_id | -6875839275481643436
id | 9
query_string | SELECT * FROM t WHERE a = ?;
application_name |
hints | IndexScan(t)
disabled | f

הפעלת הינטים בעלי שם

כדי להפעיל הינטים קיימים בעלי שם, משתמשים בפונקציה google_enable_named_hints(HINTS_NAME). כברירת מחדל, הינטים בעלי שם מופעלים כשיוצרים אותם.

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

SELECT google_enable_named_hints('my_hint1');

השבתת הינטים בעלי שם

כדי להשבית הינטים בעלי שם קיימים, משתמשים בפונקציה google_disable_named_hints(HINTS_NAME).

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

SELECT google_disable_named_hints('my_hint1');

מחיקת הינטים בעלי שם

כדי למחוק הינטים בעלי שם, משתמשים בפונקציה google_delete_named_hints(HINTS_NAME).

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

SELECT google_delete_named_hints('my_hint1');

השבתת התכונה "הינטים בעלי שם"

כדי להשבית את התכונה "הינטים בעלי שם" במכונה, מגדירים את הדגל alloydb.enable_named_hints לערך off. מידע נוסף זמין במאמר הגדרת דגלים לניהול מסד נתונים של מכונה.

מגבלות

להינטים בעלי שם יש את המגבלות הבאות:

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

  • שימוש בהינטים בעלי שם כדי להשפיע על בחירת התוכנית עלול להפריע לשיפורים עתידיים בכלי האופטימיזציה של AlloyDB. חשוב לבדוק שוב את הבחירה להשתמש בהינטים בעלי שם, ולשנות אותם בהתאם כשמתרחשים האירועים הבאים:

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

מידע נוסף על המגבלות מופיע במאמרי העזרה של pg_hint_plan.

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