🥇VAKUUM ishlamay qolsa, stolni qo'lda tozalaymiz

VAKUM PostgreSQL-dagi jadvaldan faqat nimani "tozalash" mumkin hech kim ko'ra olmaydi - ya'ni, ushbu yozuvlar o'zgartirilgunga qadar boshlangan bitta faol so'rov yo'q.

Ammo bunday noxush tur (OLTP ma'lumotlar bazasida uzoq muddatli OLAP yuki) hali ham mavjud bo'lsa-chi? Qanaqasiga faol o'zgaruvchan stolni tozalang uzoq so'rovlar bilan o'ralgan va rake qadam emas?

Rakni yotqizish

Birinchidan, biz hal qilmoqchi bo'lgan muammo nima ekanligini va u qanday paydo bo'lishi mumkinligini aniqlaymiz.

Odatda bu holat sodir bo'ladi nisbatan kichik stolda, lekin u qaysi joyda sodir bo'ladi juda ko'p o'zgarishlar. Odatda bu yoki boshqacha metr/agregatlar/reytinglar, qaysi UPDATE tez-tez bajariladi, yoki bufer navbati doimiy ravishda davom etuvchi voqealar oqimini qayta ishlash, ularning yozuvlari doimiy ravishda INSERT/DELETE.

Variantni reytinglar bilan takrorlashga harakat qilaylik:

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;

Va parallel ravishda, boshqa aloqada, uzoq, uzoq so'rov boshlanadi, ba'zi murakkab statistik ma'lumotlarni to'playdi, lekin bizning stolimizga ta'sir qilmaydi:

SELECT pg_sleep(10000);

Endi biz hisoblagichlardan birining qiymatini ko'p marta yangilaymiz. Tajribaning tozaligi uchun keling, buni qilaylik dblink yordamida alohida tranzaktsiyalardabu haqiqatda qanday sodir bo'ladi:

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

Nima bo'ldi? Nima uchun hatto bitta yozuvning eng oddiy YANGILANISHI uchun ham bajarilish muddati 7 baravar kamaygan — 0.524 ms dan 3.808 ms gacha? Va bizning reytingimiz tobora sekin rivojlanmoqda.

Hammasiga MVCC aybdor.

Hammasi haqida MVCC mexanizmi, bu so'rovni kirishning barcha oldingi versiyalarini ko'rib chiqishga olib keladi. Keling, stolimizni "o'lik" versiyalardan tozalaymiz:

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

Oh, tozalash uchun hech narsa yo'q! Parallel Ishlayotgan so'rov bizga xalaqit bermoqda - Axir, u bir kun kelib ushbu versiyalarga murojaat qilishni xohlashi mumkin (agar bo'lsa?) va ular unga mavjud bo'lishi kerak. Va shuning uchun hatto VACUUM FULL ham bizga yordam bermaydi.

Jadvalni "yiqitish"

Ammo biz aniq bilamizki, bu so'rov bizning jadvalimizga kerak emas. Shuning uchun, biz hali ham jadvaldan keraksiz narsalarni yo'q qilish orqali tizim ish faoliyatini tegishli chegaralarga qaytarishga harakat qilamiz - hech bo'lmaganda "qo'lda", chunki VACUUM taslim bo'ladi.

Buni yanada aniqroq qilish uchun keling, bufer jadvalining misolini ko'rib chiqaylik. Ya'ni, INSERT/DELETE ning katta oqimi mavjud va ba'zida jadval butunlay bo'sh bo'ladi. Ammo bo'sh bo'lmasa, biz kerak uning joriy tarkibini saqlang.

№ 0: Vaziyatni baholash

Har bir operatsiyadan keyin ham jadval bilan biror narsa qilishga urinib ko'rishingiz mumkinligi aniq, ammo bu unchalik ma'noga ega emas - texnik xizmat ko'rsatish xarajatlari maqsadli so'rovlarning o'tkazish qobiliyatidan kattaroq bo'lishi aniq.

Keling, mezonlarni tuzamiz - "harakat qilish vaqti keldi", agar:

VAKUUM ancha oldin ishga tushirilgan
Biz og'ir yukni kutamiz, shuning uchun bo'lsin 60 soniya oxirgi [avto]VAKUUMdan beri.
jismoniy stol hajmi maqsadlidan kattaroq
Keling, uni minimal hajmga nisbatan ikki baravar ko'p sahifalar soni (8KB bloklari) deb belgilaymiz - Uyum uchun 1 blk + har bir indeks uchun 1 blk - potentsial bo'sh jadval uchun. Agar ma'lum miqdordagi ma'lumotlar har doim "odatda" buferda qolishini kutsak, ushbu formulani o'zgartirish maqsadga muvofiqdir.

Tasdiqlash so'rovi

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: Hali ham VAKUUM

Parallel so'rov bizga sezilarli darajada xalaqit beradimi yoki yo'qligini oldindan bila olmaymiz - u boshlanganidan beri qancha yozuvlar "eskirgan" bo'lib qolgan. Shuning uchun, biz qandaydir tarzda jadvalni qayta ishlashga qaror qilganimizda, har qanday holatda, biz birinchi navbatda uni bajarishimiz kerak VAKUM - VACUUM FULL-dan farqli o'laroq, u o'qish va yozish ma'lumotlari bilan ishlaydigan parallel jarayonlarga xalaqit bermaydi.

Shu bilan birga, u biz olib tashlamoqchi bo'lgan ko'p narsalarni darhol tozalashi mumkin. Ha, va bu jadvaldagi keyingi so'rovlar bizga o'tadi "issiq kesh" orqali, bu ularning davomiyligini qisqartiradi - va shuning uchun bizning xizmat ko'rsatish tranzaksiyamiz orqali boshqalarni blokirovka qilishning umumiy vaqti.

# 2: Uyda kimdir bormi?

Jadvalda biror narsa bor-yo'qligini tekshirib ko'raylik:

TABLE tbl LIMIT 1;

Agar bitta yozuv qolmagan bo'lsa, biz oddiygina ishlov berish orqali ko'p narsalarni tejashimiz mumkin QISMAT:

U har bir jadval uchun shartsiz DELETE buyrug'i bilan bir xil ishlaydi, lekin jadvallarni skanerdan o'tkazmagani uchun ancha tezroq. Bundan tashqari, u darhol disk maydonini bo'shatadi, shuning uchun keyin VAKUUM operatsiyasini bajarishga hojat yo'q.

Jadvallar ketma-ketligi hisoblagichini (RESTART IDENTITY) qayta o'rnatishingiz kerakmi yoki yo'qmi, buni o'zingiz hal qilasiz.

№3: Hamma - navbat bilan!

Biz kuchli raqobat muhitida ishlayotganimiz sababli, jadvalda yozuvlar yo'qligini tekshirayotganimizda, kimdir allaqachon biror narsa yozgan bo'lishi mumkin. Biz bu ma'lumotni yo'qotmasligimiz kerak, nima bo'ladi? To'g'ri, buni hech kim aniq yoza olmasligiga ishonch hosil qilishimiz kerak.

Buning uchun biz faollashtirishimiz kerak SERIAL BO'LADI-bizning tranzaktsiyamiz uchun izolyatsiya (ha, bu erda biz tranzaktsiyani boshlaymiz) va stolni "qattiq" qulflang:

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

Bloklashning bu darajasi biz unda amalga oshirmoqchi bo'lgan operatsiyalar bilan belgilanadi.

№4: manfaatlar to'qnashuvi

Biz bu erga keldik va belgini "qulflashni" xohlaymiz - agar o'sha paytda kimdir faol bo'lgan bo'lsa, masalan, undan o'qish? Biz ushbu blokning chiqarilishini kutamiz va o'qishni istagan boshqalar bizga duch kelishadi ...

Buning oldini olish uchun biz "o'zimizni qurbon qilamiz" - agar biz ma'lum (qabul qilinadigan qisqa) vaqt ichida qulfni ololmasak, biz bazadan istisno olamiz, lekin hech bo'lmaganda biz juda ko'p aralashmaymiz. boshqalar.

Buning uchun seans o'zgaruvchisini o'rnating lock_timeout (9.3+ versiyalari uchun) yoki/va bayonot_vaqt tugashi. Esda tutish kerak bo'lgan asosiy narsa shundaki, statement_timeout qiymati faqat keyingi bayonotda amal qiladi. Ya'ni, yopishtirishda shunday - ishlamaydi:

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

Keyinchalik o'zgaruvchining "eski" qiymatini tiklash bilan shug'ullanmaslik uchun biz shakldan foydalanamiz LOCAL SOZLASH, bu sozlama doirasini joriy tranzaksiya bilan cheklaydi.

Esda tutamizki, statement_timeout barcha keyingi so'rovlar uchun amal qiladi, shunda jadvalda ko'p ma'lumotlar mavjud bo'lsa, tranzaktsiya qabul qilib bo'lmaydigan qiymatlarga cho'zilmasligi mumkin.

№5: Ma'lumotlarni nusxalash

Agar jadval to'liq bo'sh bo'lmasa, ma'lumotlar yordamchi vaqtinchalik jadval yordamida qayta saqlanishi kerak:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Imzo QO'YILIShDA tranzaktsiyaning oxirida vaqtinchalik jadval o'z faoliyatini to'xtatadi va ulanish kontekstida uni qo'lda o'chirishga hojat yo'q degan ma'noni anglatadi.

Biz "jonli" ma'lumotlar ko'p emas deb taxmin qilganimiz sababli, bu operatsiya juda tez amalga oshirilishi kerak.

Xo'sh, hammasi shu! Tranzaktsiyani tugatgandan so'ng unutmang ANALYZE ni ishga tushiring agar kerak bo'lsa, jadval statistikasini normallashtirish.

Yakuniy skriptni birlashtirish

Biz ushbu "pseudo-python" dan foydalanamiz:

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

Ma'lumotlarni ikkinchi marta ko'chirmaslik mumkinmi?Asosan, agar jadvalning o'zi BL tomonidagi boshqa harakatlarga yoki JB tomonidan FK bilan bog'lanmagan bo'lsa, mumkin:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Skriptni manba jadvalida ishga tushiramiz va ko'rsatkichlarni tekshiramiz:

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

Hammasi chiqdi! Jadval 50 marta qisqardi va barcha UPDATElar yana tez ishlamoqda.

Manba: www.habr.com

VAKUUM bajarilmasa, biz stolni qo'lda tozalaymiz