جب VACUUM ناکام ہوجاتا ہے، تو ہم میز کو دستی طور پر صاف کرتے ہیں۔

خلا PostgreSQL میں کسی ٹیبل سے صرف "صاف" کر سکتے ہیں۔ کوئی نہیں دیکھ سکتا - یعنی، ایک بھی فعال درخواست نہیں ہے جو ان ریکارڈز کو تبدیل کرنے سے پہلے شروع ہوئی ہو۔

لیکن کیا ہوگا اگر ایسی ناخوشگوار قسم (OLTP ڈیٹا بیس پر طویل مدتی OLAP بوجھ) اب بھی موجود ہے؟ کیسے فعال طور پر تبدیل کرنے والی میز کو صاف کریں۔ لمبے لمبے سوالات میں گھرے ہوئے اور ریک پر قدم نہیں رکھا؟

ریک کھولنا

سب سے پہلے، آئیے اس بات کا تعین کریں کہ ہم جس مسئلے کو حل کرنا چاہتے ہیں وہ کیا ہے اور یہ کیسے پیدا ہو سکتا ہے۔

عام طور پر یہ صورت حال ہوتی ہے ایک نسبتاً چھوٹی میز پر، لیکن جس میں یہ ہوتا ہے۔ بہت سی تبدیلیاں. عام طور پر یہ یا مختلف میٹر/مجموعہ/درجہ بندیجس پر اپ ڈیٹ اکثر عمل میں آتا ہے، یا بفر قطار واقعات کے کچھ مسلسل جاری سلسلے پر کارروائی کرنے کے لیے، جن کے ریکارڈ مسلسل داخل/حذف کیے جاتے ہیں۔

آئیے ریٹنگ کے ساتھ آپشن کو دوبارہ پیش کرنے کی کوشش کریں:

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 بلیک + ہر انڈیکس کے لیے 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 کمانڈ کی طرح کام کرتا ہے، لیکن یہ بہت تیز ہے کیونکہ یہ اصل میں ٹیبلز کو اسکین نہیں کرتا ہے۔ مزید یہ کہ یہ فوری طور پر ڈسک کی جگہ خالی کر دیتا ہے، اس لیے بعد میں ویکیوم آپریشن کرنے کی ضرورت نہیں ہے۔

آیا آپ کو ٹیبل سیکوینس کاؤنٹر (ری اسٹارٹ آئیڈینٹیٹی) کو دوبارہ ترتیب دینے کی ضرورت ہے اس کا فیصلہ آپ پر ہے۔

#3: ہر کوئی - باری باری لے لو!

چونکہ ہم ایک انتہائی مسابقتی ماحول میں کام کرتے ہیں، جب کہ ہم یہاں یہ جانچ رہے ہیں کہ ٹیبل میں کوئی اندراج نہیں ہے، اس لیے وہاں کوئی پہلے سے ہی کچھ لکھ سکتا ہے۔ ہمیں اس معلومات سے محروم نہیں ہونا چاہئے، تو کیا؟ یہ ٹھیک ہے، ہمیں اس بات کو یقینی بنانے کی ضرورت ہے کہ کوئی بھی اسے یقینی طور پر لکھ نہ سکے۔

ایسا کرنے کے لیے ہمیں فعال کرنے کی ضرورت ہے۔ سیرئ ایبل-ہمارے لین دین کے لیے تنہائی (ہاں، یہاں ہم ایک لین دین شروع کرتے ہیں) اور میز کو "مضبوطی سے" لاک کریں:

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

بلاکنگ کی اس سطح کا تعین ان کارروائیوں سے ہوتا ہے جو ہم اس پر انجام دینا چاہتے ہیں۔

#4: مفادات کا تصادم

ہم یہاں آتے ہیں اور نشان کو "لاک" کرنا چاہتے ہیں - اگر کوئی اس وقت اس پر متحرک تھا، مثال کے طور پر، اس سے پڑھنا؟ ہم اس بلاک کے جاری ہونے کے انتظار میں "ہینگ" کریں گے، اور دوسرے جو پڑھنا چاہتے ہیں وہ ہم سے ملیں گے...

ایسا ہونے سے روکنے کے لیے، ہم "خود کو قربان کر دیں گے" - اگر ہم ایک خاص (قابل قبول مختصر) وقت کے اندر تالا حاصل کرنے سے قاصر تھے، تو ہمیں اڈے کی طرف سے ایک رعایت ملے گی، لیکن کم از کم ہم اس میں زیادہ مداخلت نہیں کریں گے۔ دوسرے

ایسا کرنے کے لیے سیشن متغیر سیٹ کریں۔ لاک_ٹائم آؤٹ (ورژن 9.3+ کے لیے) یا/اور بیان_وقت ختم. یاد رکھنے کی اہم بات یہ ہے کہ اسٹیٹمنٹ_ٹائم آؤٹ ویلیو صرف اگلے بیان سے لاگو ہوتی ہے۔ یعنی gluing میں اس طرح - کام نہیں کرے گا:

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

متغیر کی "پرانی" قدر کو بعد میں بحال کرنے سے نمٹنے کے لیے، ہم فارم کا استعمال کرتے ہیں۔ مقامی سیٹ کریں۔، جو ترتیب کے دائرہ کار کو موجودہ لین دین تک محدود کرتا ہے۔

ہمیں یاد ہے کہ اسٹیٹمنٹ_ٹائم آؤٹ تمام بعد کی درخواستوں پر لاگو ہوتا ہے تاکہ اگر ٹیبل میں بہت زیادہ ڈیٹا موجود ہو تو ٹرانزیکشن ناقابل قبول اقدار تک نہ بڑھ سکے۔

#5: ڈیٹا کاپی کریں۔

اگر ٹیبل مکمل طور پر خالی نہیں ہے، تو ڈیٹا کو ایک معاون عارضی ٹیبل کا استعمال کرتے ہوئے دوبارہ محفوظ کرنا پڑے گا:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

دستخط کمٹ ڈراپ پر اس کا مطلب یہ ہے کہ جس وقت لین دین ختم ہوتا ہے، عارضی ٹیبل کا وجود ختم ہو جائے گا، اور کنکشن کے تناظر میں اسے دستی طور پر حذف کرنے کی ضرورت نہیں ہے۔

چونکہ ہم فرض کرتے ہیں کہ بہت زیادہ "لائیو" ڈیٹا نہیں ہے، اس لیے یہ آپریشن کافی تیزی سے ہونا چاہیے۔

ٹھیک ہے، یہ سب ہے! لین دین مکمل کرنے کے بعد مت بھولنا تجزیہ چلائیں اگر ضروری ہو تو ٹیبل کے اعدادوشمار کو معمول پر لانے کے لیے۔

حتمی اسکرپٹ کو اکٹھا کرنا

ہم یہ "سیڈو ازگر" استعمال کرتے ہیں:

# собираем статистику с таблицы
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;

کیا دوسری بار ڈیٹا کاپی کرنا ممکن نہیں؟اصولی طور پر، یہ ممکن ہے کہ میز کا oid خود 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