وقتی VACUUM از کار بیفتد، میز را به صورت دستی تمیز می کنیم

واکسن می تواند از جدولی در PostgreSQL فقط چه چیزی را پاک کند هیچ کس نمی تواند ببیند - یعنی یک درخواست فعال وجود ندارد که قبل از تغییر این رکوردها شروع شده باشد.

اما اگر چنین نوع ناخوشایندی (بار OLAP طولانی مدت در پایگاه داده OLTP) هنوز وجود داشته باشد، چه؟ چگونه میز تعویض فعال را تمیز کنید احاطه شده توسط نمایش داده شد طولانی و نه بر روی چنگک جمع کردن؟

باز کردن چنگک

ابتدا بیایید مشخص کنیم مشکلی که می خواهیم حل کنیم چیست و چگونه می تواند ایجاد شود.

معمولا این وضعیت اتفاق می افتد روی یک میز نسبتا کوچک، اما در آن رخ می دهد تغییرات زیادی. معمولاً این یا متفاوت است متر / سنگدانه ها / رتبه بندی، که در آن 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؟ و رتبه ما بیشتر و آهسته تر می شود.

همه اینها تقصیر 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 ثانیه از آخرین [خودکار] خلاء.
• اندازه جدول فیزیکی بزرگتر از هدف است
  بیایید آن را دو برابر تعداد صفحات (بلوک های 8 کیلوبایت) نسبت به حداقل اندازه تعریف کنیم - 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: تضاد منافع

ما به اینجا می‌آییم و می‌خواهیم علامت را قفل کنیم - اگر کسی در آن لحظه روی آن فعال بود، مثلاً از آن می‌خواند، چه می‌شود؟ ما منتظر انتشار این بلوک خواهیم بود و دیگرانی که می خواهند بخوانند با ما برخورد خواهند کرد...

برای جلوگیری از این اتفاق، ما "خودمان را فدا خواهیم کرد" - اگر نتوانستیم در مدت زمان معینی (قابل قبول کوتاه) قفلی را بدست آوریم، از پایه استثنا دریافت خواهیم کرد، اما حداقل ما بیش از حد مداخله نخواهیم کرد. دیگران.

برای این کار متغیر session را تنظیم کنید lock_timeout (برای نسخه های 9.3+) یا/و statement_timeout. نکته اصلی که باید به خاطر داشته باشید این است که مقدار statement_timeout فقط از دستور بعدی اعمال می شود. یعنی در چسب زدن به این صورت - کار نخواهد کرد:

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

برای اینکه بعداً با بازیابی مقدار "قدیمی" متغیر سر و کار نداشته باشیم، از فرم استفاده می کنیم تنظیم محلی، که دامنه تنظیمات را به تراکنش فعلی محدود می کند.

به یاد داریم که statement_timeout برای همه درخواست‌های بعدی اعمال می‌شود، به طوری که اگر داده‌های زیادی در جدول وجود داشته باشد، تراکنش نمی‌تواند به مقادیر غیرقابل قبول برسد.

شماره 5: داده ها را کپی کنید

اگر جدول کاملاً خالی نباشد، داده ها باید با استفاده از یک جدول موقت کمکی دوباره ذخیره شوند:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

امضا ON COMMI Drop به این معنی که در لحظه پایان تراکنش، جدول موقت وجود ندارد و نیازی به حذف دستی آن در زمینه اتصال نیست.

از آنجایی که فرض می کنیم داده های "زنده" زیادی وجود ندارد، این عملیات باید خیلی سریع انجام شود.

خب، همین! پس از انجام معامله فراموش نکنید ANALYZE را اجرا کنید برای عادی سازی آمار جدول در صورت لزوم.

در کنار هم قرار دادن فیلمنامه نهایی

ما از این "شبه پایتون" استفاده می کنیم:

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