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

לִשְׁאוֹב יכול "לנקות" מטבלה ב-PostgreSQL רק מה אף אחד לא יכול לראות - כלומר, אין בקשה פעילה אחת שהתחילה לפני שהרשומות הללו שונו.

אבל מה אם סוג כל כך לא נעים (עומס OLAP לטווח ארוך על מסד נתונים OLTP) עדיין קיים? אֵיך נקי שולחן החתלה באופן פעיל מוקף בשאילתות ארוכות ולא לדרוך על מגרפה?

פתיחת המגרפה

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

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

בואו ננסה לשחזר את האפשרות עם דירוגים:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- по этому индексу будем строить рейтинг

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

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

SELECT pg_sleep(10000);

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

DO $$
DECLARE
  i integer;
  tsb timestamp;
  tse timestamp;
  d double precision;
BEGIN
  PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  FOR i IN 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$);
    tse = clock_timestamp();
    IF i % 1000 = 0 THEN
      d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5);
    END IF;
  END LOOP;
  PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

NOTICE:  i =  1000, exectime = 0.524
NOTICE:  i =  2000, exectime = 0.739
NOTICE:  i =  3000, exectime = 1.188
NOTICE:  i =  4000, exectime = 2.508
NOTICE:  i =  5000, exectime = 1.791
NOTICE:  i =  6000, exectime = 2.658
NOTICE:  i =  7000, exectime = 2.318
NOTICE:  i =  8000, exectime = 2.572
NOTICE:  i =  9000, exectime = 2.929
NOTICE:  i = 10000, exectime = 3.808

מה קרה? למה אפילו לעדכון הפשוט ביותר של רשומה בודדת זמן הביצוע ירד פי 7 - מ-0.524ms ל-3.808ms? והדירוג שלנו נבנה יותר ויותר לאט.

הכל באשמת MVCC.

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

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages
DETAIL:  10000 dead row versions cannot be removed yet, oldest xmin: 597439602

הו, אין מה לנקות! מַקְבִּיל בקשת הריצה מפריעה לנו - הרי אולי מתישהו ירצה לפנות לגרסאות הללו (מה אם?), והן אמורות להיות זמינות עבורו. ולכן גם ואקום מלא לא יעזור לנו.

"ממוטט" את השולחן

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

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

#0: הערכת המצב

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

בואו ננסח את הקריטריונים - "הגיע הזמן לפעול" אם:

• VACUUM הושק לפני די הרבה זמן
  אנו מצפים לעומס כבד, אז תן לזה להיות 60 שניות מאז הוואקום [אוטומטי] האחרון.
• גודל השולחן הפיזי גדול מהיעד
  בוא נגדיר את זה כמספר כפול של עמודים (בלוקים של 8KB) ביחס לגודל המינימלי - 1 blk עבור ערימה + 1 blk עבור כל מדד - עבור שולחן שעלול להיות ריק. אם אנו מצפים שכמות מסוימת של נתונים תמיד תישאר במאגר "בדרך כלל", סביר לשנות את הנוסחה הזו.

בקשת אימות

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

מס' 1: עדיין ואקום

אנחנו לא יכולים לדעת מראש אם שאילתה מקבילה מפריעה לנו באופן משמעותי - כמה רשומות בדיוק הפכו "לא מעודכנות" מאז שהחלה. לכן, כאשר אנו מחליטים איכשהו לעבד את הטבלה, בכל מקרה, עלינו קודם כל לבצע עליה לִשְׁאוֹב - בניגוד ל-VACUUM FULL, הוא אינו מפריע לתהליכים מקבילים בעבודה עם נתונים של קריאה-כתיבה.

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

מס' 2: מישהו בבית?

בוא נבדוק אם יש משהו בטבלה בכלל:

TABLE tbl LIMIT 1;

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

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

אם אתה צריך לאפס את מונה רצף הטבלה (Restart IDENTITY) תלוי בך להחליט.

מס' 3: כולם - מתחלפים!

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

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

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

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

#4: ניגוד עניינים

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

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

לשם כך, הגדר את משתנה ההפעלה lock_timeout (עבור גרסאות 9.3+) או/ו statement_timeout. הדבר העיקרי שיש לזכור הוא שהערך statement_timeout חל רק מהמשפט הבא. כלומר, ככה בהדבקה - לא יעבוד:

SET statement_timeout = ...;LOCK TABLE ...;

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

אנו זוכרים ש- statement_timeout חל על כל הבקשות הבאות כך שהעסקה לא יכולה להימתח לערכים בלתי מקובלים אם יש הרבה נתונים בטבלה.

מס' 5: העתק נתונים

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

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

חֲתִימָה ON COMMIT DROP אומר שברגע שהעסקה מסתיימת, הטבלה הזמנית תפסיק להתקיים, ואין צורך למחוק אותה ידנית בהקשר החיבור.

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

טוב זה הכל! אל תשכח לאחר השלמת העסקה להפעיל את ANALYZE לנרמל סטטיסטיקות טבלה במידת הצורך.

הרכבת התסריט הסופי

אנו משתמשים ב"פסאודו-פיתון" הזה:

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

האם ניתן לא להעתיק את הנתונים בשנית?באופן עקרוני, זה אפשרי אם אויד הטבלה עצמו אינו קשור לפעילויות אחרות מצד BL או FK מצד DB:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

בואו נריץ את הסקריפט בטבלת המקור ונבדוק את המדדים:

VACUUM tbl;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
  LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
  CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
  TRUNCATE TABLE tbl;
  INSERT INTO tbl TABLE _tmp_swap;
COMMIT;

relpages | size_norm | size   | vaclag
-------------------------------------------
       0 |     24576 |  49152 | 32.705771

הכל הסתדר! הטבלה הצטמצמה פי 50 וכל העדכונים שוב פועלים במהירות.

מקור: www.habr.com