کله چې VACUUM ناکام شي، موږ میز په لاسي ډول پاکوو

VACUMUM یوازې په 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 FULL به زموږ سره مرسته ونکړي.

د میز "ماتول".

مګر موږ د ډاډ لپاره پوهیږو چې دا پوښتنه زموږ میز ته اړتیا نلري. له همدې امله ، موږ به لاهم هڅه وکړو چې د میز څخه هرڅه غیر ضروري له مینځه وړلو سره د سیسټم فعالیت کافي حدونو ته راستون کړو - لږترلږه "په لاسي ډول" ، ځکه چې 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: لاهم خلا

موږ مخکې نه پوهیږو چې ایا موازي پوښتنه زموږ سره د پام وړ مداخله کوي - په حقیقت کې څومره ریکارډونه "تاریخ" شوي دي له پیل راهیسې. له همدې امله، کله چې موږ پریکړه وکړو چې په یو ډول میز پروسس کړو، په هر حالت کې، موږ باید لومړی په هغې باندې عمل وکړو VACUMUM - د VACUUM FULL برعکس، دا د لوستلو لیکلو ډاټا سره کار کولو موازي پروسو کې مداخله نه کوي.

په ورته وخت کې ، دا کولی شي سمدلاسه ډیری هغه څه پاک کړي چې موږ یې لیرې کول غواړو. هو، او په دې میز کې راتلونکی پوښتنې به موږ ته لاړ شي د "ګرم کیچ" لخوا، کوم چې به د دوی موده کمه کړي - او له همدې امله زموږ د خدماتو لیږد لخوا د نورو بلاک کولو ټول وخت.

#2: ایا څوک په کور کې دي؟

راځئ وګورو چې ایا په میز کې کوم څه شتون لري:

TABLE tbl LIMIT 1;

که چیرې یو ریکارډ پاتې نه وي، نو موږ کولی شو په ساده کولو سره په پروسس کولو کې ډیر څه خوندي کړو باور وکړئ:

دا د هر میز لپاره د غیر مشروط DELETE کمانډ په څیر عمل کوي، مګر خورا ګړندی دی ځکه چې دا واقعیا میزونه سکین نه کوي. سربیره پردې ، دا سمدلاسه د ډیسک ځای خلاصوي ، نو له دې وروسته د VACUUM عملیاتو ترسره کولو ته اړتیا نشته.

ایا تاسو اړتیا لرئ د میز ترتیب کاونټر بیا تنظیم کړئ (د بیا پیل پیژندنه) پریکړه ستاسو پورې اړه لري.

#3: هرڅوک - په وار وار واخله!

له هغه ځایه چې موږ په خورا رقابتي چاپیریال کې کار کوو، پداسې حال کې چې موږ دلته ګورو چې په جدول کې هیڅ ننوتل شتون نلري، یو څوک کولی شي مخکې له مخکې یو څه لیکلي وي. موږ باید دا معلومات له لاسه ورنکړو، نو څه؟ دا سمه ده، موږ باید ډاډ ترلاسه کړو چې هیڅوک نشي کولی دا د ډاډ لپاره لیکلی شي.

د دې کولو لپاره موږ باید فعال کړو د لړۍ وړ- زموږ د راکړې ورکړې لپاره جلا کول (هو، دلته موږ معامله پیل کوو) او میز "په کلکه" بند کړئ:

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

د بلاک کولو دا کچه د هغه عملیاتو لخوا ټاکل کیږي چې موږ یې ترسره کول غواړو.

#4: د ګټو ټکر

موږ دلته راغلي یو او غواړو نښه "لاک" کړو - که چیرې یو څوک پدې وخت کې فعال وي، د بیلګې په توګه، له هغې څخه لوستل؟ موږ به د دې بلاک خوشې کیدو ته انتظار وباسو ، او نور څوک چې لوستل غواړي موږ ته به ننوځي ...

د دې څخه د مخنیوي لپاره، موږ به "ځان قرباني کړو" - که موږ نشو کولی په یو ټاکلي (د منلو وړ لنډ) وخت کې تالاش ترلاسه کړو، نو بیا به موږ د اډې څخه استثنا ترلاسه کړو، مګر لږترلږه موږ به ډیره مداخله ونه کړو. نور

د دې کولو لپاره، د سیشن متغیر تنظیم کړئ lock_timeout (د 9.3+ نسخو لپاره) یا/او بیان_ وخت پای. د یادولو لپاره اصلي شی دا دی چې د بیان_timeout ارزښت یوازې د راتلونکي بیان څخه پلي کیږي. يعنې په ګلونګ کې دا ډول - کار نه کوي:

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

د دې لپاره چې وروسته د متغیر د "زاړه" ارزښت بیا رغولو سره معامله ونه کړو، موږ فورمه کاروو سیمه ایز تنظیم کړئ، کوم چې د اوسني لیږد لپاره د ترتیب ساحه محدودوي.

موږ په یاد لرو چې بیان_timeout په ټولو راتلونکو غوښتنو باندې تطبیق کیږي ترڅو معامله د نه منلو وړ ارزښتونو ته وغځول شي که چیرې په جدول کې ډیری ډیټا شتون ولري.

#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;

ایا دا امکان نلري چې ډاټا دوهم ځل کاپي کړئ؟په اصولو کې، دا ممکنه ده که چیرې د میز ایډ پخپله د 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