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

ويڪيوم PostgreSQL ۾ ٽيبل مان ”صاف“ ڪري سگھي ٿو صرف ڇا ڪو به ڏسي نٿو سگهي - اھو آھي، ھڪڙو ھڪڙو فعال درخواست نه آھي جيڪو انھن رڪارڊن کي تبديل ڪرڻ کان اڳ شروع ڪيو ويو آھي.

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

ريڪ ڪڍڻ

پهرين، اچو ته اهو طئي ڪريون ته اسان ڪهڙو مسئلو حل ڪرڻ چاهيون ٿا ۽ اهو ڪيئن پيدا ٿي سگهي ٿو.

عام طور تي اهڙي صورتحال ٿئي ٿي هڪ نسبتا ننڍي ميز تي، پر جنهن ۾ اهو ٿئي ٿو تمام گهڻيون تبديليون. عام طور تي هي يا مختلف ميٽر/مجموعي/درجه بندي، جنهن تي 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 FULL به اسان جي مدد نه ڪندو.

"ٽڙڻ" ٽيبل

پر اسان پڪ ڄاڻون ٿا ته اهو سوال اسان جي ٽيبل جي ضرورت ناهي. تنهن ڪري، اسان اڃا تائين ڪوشش ڪنداسين ته سسٽم جي ڪارڪردگي کي مناسب حد تائين واپس آڻيندي ميز مان هر غير ضروري کي ختم ڪندي - گهٽ ۾ گهٽ "دستي طور تي"، ڇاڪاڻ ته VACUUM ڏئي ٿو.

ان کي وڌيڪ واضح ڪرڻ لاء، اچو ته هڪ بفر ٽيبل جي صورت جو مثال ڏسو. اهو آهي، INSERT/DELETE جو هڪ وڏو وهڪرو آهي، ۽ ڪڏهن ڪڏهن ٽيبل مڪمل طور تي خالي هوندو آهي. پر جيڪڏهن اهو خالي ناهي، اسان کي گهرجي ان جي موجوده مواد کي محفوظ ڪريو.

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

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

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

• VACUUM ڪافي وقت اڳ شروع ڪيو ويو
  اسان کي ڳري بار جي اميد آهي، تنهنڪري اهو ٿيڻ ڏيو 60 سيڪنڊ آخري [خودڪار] VACUUM کان وٺي.
• جسماني ٽيبل جي سائيز حدف کان وڏي آهي
  اچو ته ان جي وضاحت ڪريون پيج جي تعداد کان ٻه ڀيرا (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

اسان اڳ ۾ نه ٿا ڄاڻون ته ڇا هڪ متوازي سوال اسان سان خاص طور تي مداخلت ڪري رهيو آهي - بلڪل ڪيترا رڪارڊ "تاريخ کان ٻاهر" ٿي چڪا آهن جڏهن کان اهو شروع ٿيو. تنهن ڪري، جڏهن اسان ڪنهن به طريقي سان ميز تي عمل ڪرڻ جو فيصلو ڪيو، ڪنهن به صورت ۾، اسان کي پهريان ان تي عمل ڪرڻ گهرجي ويڪيوم - 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+ لاءِ) يا/۽ بيان_وقت ختم. ياد رکڻ لاءِ بنيادي شيءِ اها آهي ته بيان_timeout قدر صرف ايندڙ بيان کان لاڳو ٿئي ٿو. اهو آهي، هن طرح gluing ۾ - ڪم نه ڪندو:

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

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

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

#5: ڊيٽا نقل ڪريو

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

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

دستخط ON COMMIT DROP مطلب ته هن وقت ٽرانزيڪشن ختم ٿي ويندي، عارضي جدول ختم ٿي ويندو، ۽ ڪنيڪشن جي حوالي سان ان کي دستي طور ختم ڪرڻ جي ڪا ضرورت ناهي.

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

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

آخري رسم الخط گڏ ڪرڻ

اسان هي "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 پاسي کان يا 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