کله چې 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 فکتور لخوا کم شوی دی — له ۰.۵۲۴ ملی ثانیو څخه تر ۳.۸۰۸ ملی ثانیو پورې؟ او زموږ درجه بندي ورځ تر بلې ورو کېږي.

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/DELETEs لوی جریان شتون لري، او ځینې وختونه میز په بشپړ ډول خالي وي. مګر که دا خالي نه وي، موږ باید د هغې اوسنی منځپانګه خوندي کړئ.

#0: د وضعیت ارزونه وکړئ

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

راځئ چې معیارونه جوړ کړو - "د عمل کولو وخت دی" که:

• ویکیوم ډېر وخت وړاندې پیل شو
  موږ د دروند بار تمه لرو، نو پرېږده چې همداسې وي د 60 ثانوي د وروستي [اتومات] خلا راهیسې.
• د میز فزیکي اندازه د هدف څخه لویه ده
  راځئ چې دا د لږترلږه اندازې په پرتله د پاڼو دوه چنده شمیر (8KB بلاکونه) په توګه تعریف کړو - په هر ګڼه کې ۱ بلاک + په هر شاخص کې ۱ بلاک — د احتمالي خالي میز لپاره. که موږ تمه ولرو چې بفر به تل یو څه اندازه معلومات ولري، نو دا معنی لري چې دا فورمول تنظیم کړئ.

د تایید غوښتنه

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

#۱: په هرصورت خلا

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

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

#۲: آیا څوک په کور کې شته؟

راځئ چې وګورو چې ایا په جدول کې کوم څه شته:

TABLE tbl LIMIT 1;

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

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

ایا تاسو اړتیا لرئ چې د جدول ترتیب کاونټر (RESTAR IDENTITY) بیا تنظیم کړئ دا ستاسو پورې اړه لري چې پریکړه وکړئ.

#۳: هرڅوک، یو په یو!

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

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

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

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

#۴: د ګټو ټکر

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

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

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

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

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

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

#۵: معلومات کاپي کړئ

که چیرې جدول په بشپړه توګه خالي نه وي، نو معلومات به د مرستندویه لنډمهاله جدول له لارې بیا خوندي شي:

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

هرڅه کار وکړ! جدول د ۵۰ فکتور له مخې کم شوی، او ټول تازه معلومات بیا په چټکۍ سره روان دي.

سرچینه: www.habr.com