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

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

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

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

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

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

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

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

    הצהרה אחת מזהה הפרות של מפתח ייחודי ומפתח ראשי, והצהרה אחרת מזהה מגוון של אי-התאמות אחרות.

  3. תיקון חוסר עקביות באינדקס

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

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

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

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

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

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

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

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

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

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

מוודאים שגרסת הלקוח 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)

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

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

הכנה ליצירה מחדש של האינדקס

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

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

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

קוד לדוגמה

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 ומעלה במהלך פעולת יצירת האינדקס מחדש.

הגדרת מספר העובדים המקבילים

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

קוד לדוגמה

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

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

    קוד לדוגמה

    SELECT pg_cancel_backend(PID);

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

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