איתור ותיקון של אינדקסים לא עקביים

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

קהילת PostgreSQL פיתחה כלים לזיהוי ולפתרון של בעיות כאלה. הכלים האלה כוללים את amcheck, שמומלץ על ידי קהילת PostgreSQL לזיהוי בעיות עקביות, כולל בעיות שהופיעו בגרסאות קודמות של PostgreSQL 14.

המדריך הזה מיועד למשתמשי AlloyDB ל-PostgreSQL שנתקלים בבעיות העקביות האלה. במדריך יש מידע שעוזר למשתמשי PostgreSQL לזהות ולתקן אינדקסים לא עקביים.

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

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

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

  2. בדיקה אם יש אינדקסים לא עקביים.

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

  3. מתקנים את חוסר העקביות באינדקס.

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

  4. מעקב אחרי פעולות של הוספה מחדש לאינדקס.

    מומלץ לעקוב אחרי ההתקדמות של פעולת ההוספה מחדש לאינדקס כדי לוודא שהיא מתקדמת ולא נחסמת.

  5. מוודאים שהאינדקסים עקביים.

    אחרי שיוצרים מחדש את האינדקס, מומלץ לוודא שאין בו חוסר עקביות.

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

גיבוי הנתונים של אשכול AlloyDB

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

הגדרת ההרשאה alloydbsuperuser

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

צריך לוודא שגרסת הלקוח של psql היא 9.6 ומעלה

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

הפעלת התוסף amcheck

כדי לבדוק אם יש אי-התאמות באינדקס, צריך להפעיל את התוסף amcheck.

דוגמת קוד

  CREATE EXTENSION amcheck;

בדיקה אם יש אינדקסים לא עקביים

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

בדיקה אם יש אי-התאמות

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

דוגמת קוד

DO $$
DECLARE
  r RECORD;
  version varchar(100);
BEGIN
  RAISE NOTICE 'Started relhasindex validation on database: %', current_database();
  FOR r IN
    SELECT indexrelid::regclass relname
      FROM pg_index
    WHERE indrelid NOT IN (SELECT oid FROM pg_class WHERE relhasindex) LOOP
      RAISE LOG 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
      RAISE WARNING 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
  END LOOP;
  RAISE NOTICE 'Finished relhasindex validation on database: %', current_database();

  RAISE NOTICE 'Started b-tree amcheck on database: %', current_database();
  SHOW server_version into version;
  SELECT split_part(version, '.', 1) into version;
  FOR r IN
    SELECT c.oid, c.oid::regclass relname, i.indisunique
      FROM pg_index i
      JOIN pg_opclass op ON i.indclass[0] = op.oid
      JOIN pg_am am ON op.opcmethod = am.oid
      JOIN pg_class c ON i.indexrelid = c.oid
      JOIN pg_namespace n ON c.relnamespace = n.oid
     WHERE am.amname = 'btree'
       AND c.relpersistence != 't'
       AND c.relkind = 'i'
       AND i.indisready AND i.indisvalid LOOP
    BEGIN
      RAISE NOTICE 'Checking index %:', r.relname;
      PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
    EXCEPTION
      WHEN undefined_function THEN
        RAISE EXCEPTION 'Failed to find the amcheck extension';
      WHEN OTHERS THEN
        RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
        RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
        END;
  END LOOP;
  RAISE NOTICE 'Finished b-tree amcheck on database: %', current_database();
END $$;

הפלט אמור להיראות כך:

דוגמת קוד

  WARNING:  Failed to check index t_i_key: relhasindex is false, want true
  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"
.

למידע נוסף על הצגת יומנים, קראו את המאמר הצגת יומנים באמצעות Logs Explorer.

זיהוי ותיקון של הפרות במפתחות ייחודיים וראשיים

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

זיהוי הפרות של מפתחות ייחודיים

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

דוגמת קוד

WITH q AS (
    /* this gets info for all UNIQUE indexes */
    SELECT indexrelid::regclass as idxname,
           indrelid::regclass as tblname,
           indcollation,
           pg_get_indexdef(indexrelid),
           format('(%s)',(select string_agg(quote_ident(attname), ', ')
              from pg_attribute a
              join unnest(indkey) ia(nr) on ia.nr = a.attnum
             where attrelid = indrelid)) as idxfields,
           COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
      FROM pg_index
     WHERE indisunique
     /* next line excludes indexes not affected by collation changes */
       AND trim(replace(indcollation::text, '0', '')) != ''
)
SELECT
 /* the format constructs the query to execute for each index */
 format(
$sql$
DO $$ BEGIN RAISE NOTICE 'checking index %3$I%2$I on table %1$I %4$I'; END;$$;
SELECT this,
       prev,
       /* we detect both reversed ordering or just not unique */
       (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
  FROM (SELECT %2$s AS this,
               lag(%2$s) OVER (ORDER BY %2$s) AS prev
          FROM %1$s %4$s
        ) s
 WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL;  /* change to just '<' if looking for reverse order in index */
$sql$, tblname, idxfields, idxname, whereclause
)
  FROM q
-- LIMIT 20 /* may use limit for testing */
-- the next line tells psql to executes this query and then execute each returned line separately
\gexec

הפלט של הסקריפט אמור להיראות כך:

תשובה

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE

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

תיקון הפרות שקשורות למפתחות כפולים

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

  1. מחלצים את הערך של key_columns מכותרת הטבלה NOTICE, כמו שמוצג בפלט לדוגמה שלמעלה. בדוגמה הבאה, עמודת המפתח היא email.

    תשובה

      NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

    משתמשים בערכים האלה ב-KEY_COLUMNS בשאילתה שבשלב 3.

  2. מוצאים את הסכימה של הטבלה. משתמשים ב-psql כדי להתחבר למסד הנתונים ומריצים את הפקודה הבאה:

    דוגמת קוד

    \dt TABLE_NAME
     הערך בעמודה `schema` הוא הערך שבו משתמשים בשביל SCHEMA_NAME בשאילתה בשלב 3. לדוגמה, בשאילתה הבאה: 
     \dt games
    הפלט אמור להיראות כך: 
     List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)

  3. מריצים את ההצהרות הבאות כדי לאלץ סריקה מלאה של הטבלה ולקבל מפתחות כפולים.

    דוגמת קוד

    SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;

SELECT KEY_COLUMNS, count(*)
  FROM SCHEMA_NAME.TABLE_NAME
GROUP BY KEY_COLUMNS
HAVING count(*) > 1;

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

    לדוגמה, בשאילתה הבאה:

      SELECT name,count(*)
    FROM public.TEST_NAMES
  GROUP BY name
  HAVING count(*) > 1;

    הפלט אמור להיראות כך:

      name                | count
  --------------------+-------
  Johnny              |     2
  Peter               |     2
(2 rows)

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

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

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

  4. אופציונלי אבל מומלץ: ליצור גיבוי לרשומות שמכילות מפתחות כפולים. מריצים את ההצהרה הבאה כדי ליצור רשומות גיבוי:

    דוגמת קוד

    CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
WHERE (KEY_COLUMNS)
IN (KEY_VALUES);
    בהצהרה הזו, KEY_VALUES היא רשימת ערכים שהועתקו מהתוצאה של השלב הקודם. לדוגמה:

    דוגמת קוד

      CREATE TABLE public.TEST_NAMES_bak
  AS SELECT * FROM public.TEST_NAMES
  WHERE (name) IN (('Johnny'),('Peter'))

    אם יש מספר גדול של שורות, קל יותר להחליף את הפרמטר ((KEY_VALUES)) בהצהרה IN בהצהרה SELECT משלב 2, בלי הפרמטר count. לדוגמה:

    דוגמת קוד

      CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
  AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
  WHERE (KEY_COLUMNS)
  IN ( SELECT (KEY_COLUMNS)
  FROM SCHEMA_NAME.TABLE_NAME
  GROUP BY (KEY_COLUMNS)
  HAVING count(*) > 1);

  5. מוסיפים למשתמש תפקיד של רפליקציה כדי להשבית טריגרים:

    דוגמת קוד

    ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;

  6. מריצים את ההצהרה הבאה כדי למחוק את המפתחות הכפולים:

    דוגמת קוד

    BEGIN;

DELETE FROM  SCHEMA_NAME.TABLE_NAME a
      USING  (
              SELECT   min(ctid) AS ctid,
                      KEY_COLUMNS
              FROM     SCHEMA_NAME.TABLE_NAME
              GROUP BY KEY_COLUMNS
                      HAVING count(*) > 1 ) b
      WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
      AND   a.ctid <> b.ctid;

    לדוגמה, עבור KEY_COLUMNS עם כמה עמודות:

    דוגמת קוד

      DELETE FROM public.test_random a
      USING (
             SELECT min(ctid) AS ctid,
             day, rnum
      FROM public.test_random
      GROUP BY day, rnum
             HAVING count(*) > 1 ) b
      WHERE a.day=b.day and a.rnum = b.rnum
      AND a.ctid <> b.ctid;
     כאשר day ו-rnum הם KEY_COLUMNS.

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

    דוגמת קוד

    DELETE FROM  SCHEMA_NAME.TABLE_NAME
WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);

  7. כדי לוודא שהפקודה DELETE החזירה את מספר השורות הצפוי בלי שגיאות:

    1. מריצים את ההצהרה הבאה כדי לזהות את השורות שבהן הטבלאות השתנו:

      דוגמת קוד

      SELECT schemaname, relname, n_tup_del, n_tup_upd
  FROM pg_stat_xact_all_tables
WHERE n_tup_del+n_tup_upd > 0;

    2. אם כל השורות נכונות, מאשרים את הטרנזקציה של DELETE:

      דוגמת קוד

      END;

    3. אם יש שגיאות, מבטלים את השינויים כדי לתקן את השגיאות:

      דוגמת קוד

      ROLLBACK;

  8. אחרי שמוחקים את המפתחות הכפולים, אפשר להוסיף מחדש את האינדקס.

תיקון אינדקסים לא עקביים

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

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

  1. הכנה להוספה מחדש של האינדקס.

  2. הוספה מחדש של האינדקס.

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

  4. מפעילים שוב את פעולת ההוספה מחדש לאינדקס.

הכנה להוספה מחדש של האינדקס

איך מוצאים את גודל האינדקס

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

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

דוגמת קוד

SELECT i.relname                                      AS index_name,
       pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
FROM   pg_index x
       JOIN pg_class i
         ON i.oid = x.indexrelid
WHERE  i.relname = 'INDEX_NAME';

הפלט של ההצהרה הזו אמור להיראות כך:

תשובה

 index_name | index_size
------------+------------
 my_index   | 16 kB
(1 row)

הגדרת גודל הזיכרון לשימוש באינדוקס מחדש

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

בדוגמה הבאה אפשר לראות איך מגדירים את maintenance_work_mem:

דוגמת קוד

SET maintenance_work_mem TO '1GB';

הוספה לאינדקס של מסדי נתונים גדולים דורשת יותר זמן מאשר מסדי נתונים קטנים. כדי לשפר את המהירות של פעולות ההוספה לאינדקס וההוספה מחדש לאינדקס, מומלץ להגדיר את maintenance_work_mem ל-2% לפחות מזיכרון המכונה במכונות עם זיכרון של 4GB או יותר במהלך פעולת ההוספה מחדש לאינדקס.

הגדרת מספר ה-workers המקבילים

כדי להגדיל את מספר ה-workers המקבילים ליצירת אינדקס מחדש, מגדירים את פרמטר ההגדרה max_parallel_maintenance_workers במסד הנתונים. ערך ברירת המחדל של הפרמטר הזה הוא 2, אבל אפשר להגדיר ערך גבוה יותר כדי להגדיל את מספר ה-workers ליצירת אינדקס מחדש. במכונות עם 8 ליבות vCPU או יותר, מומלץ להגדיר את הערך של הדגל max_parallel_maintenance_workers ל-4.

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

דוגמת קוד

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

הפרמטר max_parallel_maintenance_workers הוא קבוצת משנה של max_worker_processes ומוגבל על ידי max_parallel_workers. אם אתם צריכים יותר תהליכי עבודה במקביל, הגדילו את הערך של max_worker_processes ושל max_parallel_workers.

בדוגמה הבאה אפשר לראות איך מגדירים את max_parallel_maintenance_workers:

דוגמת קוד

SET max_parallel_maintenance_workers TO 4;

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

דוגמת קוד

SELECT
leader.leader_pid,leader.pid "worker_pid",leader.query
FROM
pg_stat_activity leader, pg_stat_activity worker
WHERE leader.leader_pid = worker.pid;

הוספה מחדש של האינדקס לאינדקס

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

כדי להוסיף מחדש את האינדקס באמצעות pg_repack:

  1. מורידים, מקמפלים ומתקינים את הכלי pg_repack מהדף pg_repack.

  2. יוצרים את התוסף pg_repack:

    דוגמת קוד

    CREATE EXTENSION pg_repack;

  3. מריצים את הפקודה הבאה כדי ליצור מחדש את האינדקס בו-זמנית:

    דוגמת קוד

      pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600

    הפלט של הפקודה הזו אמור להיראות כך:

    תשובה

    INFO: repacking index "public.t_i_key"

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

איתור ותיקון של הפרות של מפתחות זרים

במאמר איתור ותיקון של הפרות של מפתחות זרים מוסבר איך לאתר ולתקן הפרות של מפתחות זרים.

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

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

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

  1. כדי לזהות סשנים חוסמים, מריצים את השאילתה הבאה:

    דוגמת קוד

    SELECT pid,
      usename,
      pg_blocking_pids(pid) AS blocked_by,
      query                 AS blocked_query
FROM   pg_stat_activity
WHERE  cardinality(pg_blocking_pids(pid)) > 0;

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

    דוגמת קוד

    SELECT pg_cancel_backend(PID);

אימות העקביות של האינדקסים

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