جڏهن VACUUM ناڪام ٿيندو، اسان ميز کي دستي طور تي صاف ڪندا آهيون

ويڪيوم پوسٽگري ايس ڪيو ايل ۾ صرف هڪ ٽيبل مان "صاف" ڪري سگهجي ٿو جيڪو ڪو به ڏسي نٿو سگهي. - يعني، انهن رڪارڊن جي تبديلي کان اڳ هڪ به فعال درخواست شروع نه ٿي هئي.

پر جيڪڏهن اهڙي ناخوشگوار قسم (OLTP ڊيٽابيس تي ڊگهي مدت جو OLAP لوڊ) موجود هجي ته ڇا ٿيندو؟ ڪيئن؟ فعال طور تي تبديل ٿيندڙ ٽيبل صاف ڪريو ڊگهن سوالن سان گھريل ۽ ريڪ تي قدم نه رکڻ؟

اچو ته ريڪ کي ترتيب ڏيون.

پهرين، اچو ته وضاحت ڪريون ته اسان جيڪو مسئلو حل ڪرڻ چاهيون ٿا اهو ڇا آهي ۽ اهو ڪيئن پيدا ٿي سگهي ٿو.

هي صورتحال عام طور تي ٿيندي آهي هڪ ننڍڙي ٽيبل تي، پر جنهن ۾ اهو ٿئي ٿو گهڻيون تبديليونعام طور تي اهي يا ته مختلف هوندا آهن ڪائونٽر/مجموعو/درجه بنديون، جنهن تي UPDATE تمام گهڻو ڪيو ويندو آهي، يا بفر قطار ڪجهه مسلسل هلندڙ واقعن جي وهڪري کي پروسيس ڪرڻ لاءِ، جن جا رڪارڊ مسلسل داخل/حذف ڪيا ويندا آهن.

اچو ته ريٽنگ سان ورجن کي ٻيهر پيدا ڪرڻ جي ڪوشش ڪريون:

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 تائين؟ ۽ اسان جي درجه بندي وڌيڪ ۽ وڌيڪ سست رفتاري سان وڌي رهي آهي.

ايم وي سي سي هر شيءِ جو ذميوار آهي.

اهو سڀ ڪجهه آهي ايم وي سي سي ميڪانيزم، جيڪو سوال کي رڪارڊ جي سڀني پوئين ورزن کي ڏسڻ تي مجبور ڪري ٿو. تنهنڪري اچو ته اسان جي "مرده" ورزن جي ٽيبل کي صاف ڪريون:

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/DELETEs جو هڪ وڏو وهڪرو آهي، ۽ ڪڏهن ڪڏهن ٽيبل مڪمل طور تي خالي هوندي آهي. پر جيڪڏهن اهو خالي نه آهي، ته اسان کي گهرجي ان جو موجوده مواد محفوظ ڪريو.

#0: صورتحال جو جائزو وٺو

اهو واضح آهي ته توهان هر آپريشن کان پوءِ ٽيبل سان ڪجهه ڪرڻ جي ڪوشش ڪري سگهو ٿا، پر اهو گهڻو مطلب نٿو رکي - سار سنڀال جو اوور هيڊ واضح طور تي ٽارگيٽ سوالن جي ٿرو پُٽ کان وڌيڪ هوندو.

اچو ته معيار ٺاهيون - "اهو عمل ڪرڻ جو وقت آهي" جيڪڏهن:

• ويڪيوم ڪافي وقت اڳ لانچ ڪيو ويو هو.
  اسان کي ڳري بار جي اميد آهي، تنهنڪري ان کي ٿيڻ ڏيو 60 سيڪنڊ آخري [خودڪار] ويڪيوم کان وٺي.
• ٽيبل جي جسماني سائيز هدف کان وڏي آهي
  اچو ته ان کي گهٽ ۾ گهٽ سائيز جي نسبت صفحن جي ٻٽي تعداد (8KB بلاڪ) جي طور تي بيان ڪريون - 1 بلاڪ في هيپ + 1 بلاڪ في انڊيڪس — هڪ امڪاني طور تي خالي ٽيبل لاءِ. جيڪڏهن اسان توقع ڪريون ٿا ته بفر ۾ هميشه ڪجهه مقدار ۾ ڊيٽا هوندو، ته پوءِ هن فارمولي کي ترتيب ڏيڻ سمجهه ۾ اچي ٿو.

تصديق جي درخواست

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 ويليو صرف ايندڙ بيان سان شروع ٿيندي لاڳو ٿئي ٿي. تنهن ڪري، هن طرح جي ڪنڪيٽينيشن ۾: اهو ڪم نه ڪندو.:

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

بعد ۾ متغير جي "پراڻي" قدر کي بحال ڪرڻ کان بچڻ لاءِ، اسان فارم استعمال ڪندا آهيون مقامي سيٽ ڪريو، جيڪو سيٽنگ جي دائري کي موجوده ٽرانزيڪشن تائين محدود ڪري ٿو.

ياد رکو ته statement_timeout سڀني ايندڙ درخواستن تي لاڳو ٿئي ٿو، تنهن ڪري جيڪڏهن ٽيبل ۾ تمام گهڻو ڊيٽا هجي ته ٽرانزيڪشن ناقابل قبول ڊيگهه تائين نه وڌي سگهي.

#5: ڊيٽا ڪاپي ڪريو

جيڪڏهن ٽيبل مڪمل طور تي خالي نه آهي، ته ڊيٽا کي هڪ معاون عارضي ٽيبل ذريعي ٻيهر محفوظ ڪرڻو پوندو:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

دستخط ڪمٽ ڊراپ تي مطلب ته ٽرانزيڪشن جي آخر ۾ عارضي ٽيبل موجود نه رهندو، ۽ ڪنيڪشن جي حوالي سان ان کي دستي طور تي ختم ڪرڻ جي ڪا ضرورت ناهي.

جيئن ته اسان فرض ڪريون ٿا ته گهڻو "لائيو" ڊيٽا نه آهي، هي آپريشن ڪافي تيز هجڻ گهرجي.

خير، بس اهو ئي آهي! ٽرانزيڪشن مڪمل ڪرڻ کان پوءِ نه وساريو. تجزيو هلايو جيڪڏهن ضروري هجي ته، ٽيبل جي انگن اکرن کي معمول تي آڻڻ لاءِ.

آخري اسڪرپٽ گڏ ڪرڻ

اسين هي "pseudo-python" استعمال ڪريون ٿا:

# собираем статистику с таблицы
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 پاسي يا DB پاسي FK تي ڪنهن ٻئي سرگرمين سان ڳنڍيل نه هجي:

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