Sugedus VACUUM, stalą valome rankiniu būdu

VAKUUMAS gali „išvalyti“ iš PostgreSQL lentelės tik ką niekas nemato - tai yra, nėra nei vienos aktyvios užklausos, kuri buvo pradėta prieš pakeičiant šiuos įrašus.

Bet ką daryti, jei toks nemalonus tipas (ilgalaikė OLAP apkrova OLTP duomenų bazėje) vis dar egzistuoja? Kaip nuvalykite aktyviai persirengimo stalą apsuptas ilgų užklausų ir nelipti ant grėblio?

Sugedus VACUUM, stalą valome rankiniu būdu

Grėblio išdėliojimas

Pirmiausia išsiaiškinkime, kokią problemą norime išspręsti ir kaip ji gali kilti.

Paprastai tokia situacija nutinka ant palyginti mažo stalo, bet kurioje ji atsiranda daug pokyčių. Paprastai tai arba kitaip metrai / agregatai / reitingai, kuriame dažnai vykdomas UPDATE, arba buferis-eilė apdoroti tam tikrą nuolat vykstantį įvykių srautą, kurio įrašai yra nuolat INSERT/DELETE.

Pabandykime atkurti parinktį su įvertinimais:

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;

Ir lygiagrečiai, kitu ryšiu, prasideda ilgas, ilgas prašymas, renkantis sudėtingą statistiką, bet neturi įtakos mūsų stalui:

SELECT pg_sleep(10000);

Dabar mes daug kartų atnaujiname vieno iš skaitiklių vertę. Dėl eksperimento grynumo padarykime tai atskirose operacijose naudojant dblinkkaip tai bus realybėje:

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

Kas nutiko? Kodėl net už paprasčiausią vieno įrašo ATNAUJINIMĄ vykdymo laikas sutrumpėjo 7 kartus - nuo 0.524 ms iki 3.808 ms? Ir mūsų reitingas formuojasi vis lėčiau.

Dėl visko kaltas MVCC.

Viskas apie MVCC mechanizmas, todėl užklausa peržiūri visas ankstesnes įrašo versijas. Taigi nuvalykime stalą nuo „negyvų“ versijų:

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

Oi, nėra ką valyti! Lygiagretus Vykdoma užklausa trukdo mums – juk jis gal kada nors norės atsiversti šias versijas (o jeigu?), ir jos turėtų būti jam prieinamos. Ir todėl net VACUUM FULL mums nepadės.

"Sugriuvus" lentelę

Bet mes tikrai žinome, kad šiai užklausai mūsų lentelės nereikia. Todėl mes vis tiek stengsimės grąžinti sistemos našumą į tinkamas ribas, pašalindami iš lentelės viską, kas nereikalinga - bent jau „rankiniu būdu“, nes VACUUM pasiduoda.

Kad būtų aiškiau, pažvelkime į buferinės lentelės atvejo pavyzdį. Tai yra, yra didelis INSERT/DELETE srautas, o kartais lentelė būna visiškai tuščia. Bet jei jis nėra tuščias, turime išsaugoti dabartinį turinį.

#0: situacijos įvertinimas

Aišku, kad galite pabandyti ką nors padaryti su lentele net po kiekvienos operacijos, tačiau tai nėra labai prasminga - priežiūros išlaidos bus aiškiai didesnės nei tikslinių užklausų pralaidumas.

Suformuluokime kriterijus – „laikas veikti“, jei:

  • VACUUM buvo paleistas gana seniai
    Tikimės didelio krūvio, tad tebūnie 60 sekundžių nuo paskutinio [auto]VAKUUMO.
  • fizinis lentelės dydis yra didesnis nei tikslas
    Apibrėžkime jį kaip dvigubą puslapių skaičių (8 KB blokų), palyginti su minimaliu dydžiu - 1 mlrd. už krūvą + 1 mlrd. už kiekvieną indeksą - už galimai tuščią stalą. Jei tikimės, kad tam tikras duomenų kiekis visada liks buferyje „normaliai“, tikslinga šią formulę pakoreguoti.

Patikrinimo užklausa

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: vis dar VAKUUMAS

Negalime iš anksto žinoti, ar lygiagreti užklausa mums labai trukdo – kiek tiksliai įrašų „paseno“ nuo tada, kai ji prasidėjo. Todėl, kai nusprendžiame kažkaip apdoroti lentelę, bet kuriuo atveju pirmiausia turėtume ją vykdyti VAKUUMAS - skirtingai nei VACUUM FULL, jis netrukdo lygiagrečiams procesams, dirbantiems su skaitymo ir rašymo duomenimis.

Tuo pačiu metu jis gali iš karto išvalyti didžiąją dalį to, ką norėtume pašalinti. Taip, ir vėlesnės užklausos šioje lentelėje bus pateiktos mums pagal "karštą talpyklą", dėl to sutrumpės jų trukmė – taigi ir bendras kitų blokavimo mūsų aptarnavimo operacijos metu laikas.

#2: Ar kas nors namuose?

Pažiūrėkime, ar lentelėje iš viso yra kas nors:

TABLE tbl LIMIT 1;

Jei neliko nė vieno įrašo, galime sutaupyti daug apdorojimo, tiesiog atlikdami veiksmus Sutrumpinti:

Ji veikia taip pat kaip besąlyginė DELETE komanda kiekvienai lentelei, tačiau yra daug greitesnė, nes iš tikrųjų nenuskaito lentelių. Be to, tai iš karto atlaisvina vietos diske, todėl po to nereikia atlikti VACUUM operacijos.

Ar jums reikia iš naujo nustatyti lentelės sekos skaitiklį (RESTART IDENTITY), galite nuspręsti patys.

#3: visi – paeiliui!

Kadangi dirbame itin konkurencingoje aplinkoje, o čia tikriname, ar lentelėje nėra įrašų, kažkas jau galėjo ten ką nors parašyti. Neturėtume prarasti šios informacijos, o kas? Teisingai, turime pasirūpinti, kad niekas to neužsirašytų.

Norėdami tai padaryti, turime įjungti SERIALIZUOJAMAS- mūsų operacijos izoliacija (taip, čia mes pradedame operaciją) ir užrakinti lentelę „tvirtai“:

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

Šį blokavimo lygį lemia operacijos, kurias norime atlikti.

4: interesų konfliktas

Ateiname čia ir norime „užrakinti“ ženklą – o jei kas nors tuo momentu jame būtų aktyvus, pavyzdžiui, skaitė iš jo? Mes „pakabinsime“ laukdami, kol šis blokas bus išleistas, o kiti norintys paskaityti užsuks į mus...

Kad taip nenutiktų, „aukosime save“ – jei per tam tikrą (leistinai trumpą) laiką nepavyko gauti užrakto, gausime išimtį iš bazės, bet bent jau per daug nesikišime. kiti.

Norėdami tai padaryti, nustatykite seanso kintamąjį lock_timeout (9.3 ir naujesnėms versijoms) arba/ir pareiškimo_laikas. Svarbiausia atsiminti, kad pareiškimo_laikas baigtis reikšmė taikoma tik nuo kito sakinio. Tai yra, kaip tai klijuojant - neveiks:

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

Kad vėliau nereikėtų atstatyti „senosios“ kintamojo reikšmės, naudojame formą NUSTATYTI VIETINĮ, kuri apriboja nustatymo apimtį iki dabartinės operacijos.

Prisimename, kad pareiškimo_laikas pasibaigimas taikomas visoms vėlesnėms užklausoms, kad operacija negalėtų išaugti iki nepriimtinų verčių, jei lentelėje yra daug duomenų.

#5: nukopijuokite duomenis

Jei lentelė nėra visiškai tuščia, duomenis reikės įrašyti iš naujo naudojant pagalbinę laikinąją lentelę:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Parašas DĖL ĮSIPAREIGOJIMO reiškia, kad tuo momentu, kai baigiasi operacija, laikinoji lentelė nustos egzistavusi ir nereikės jos rankiniu būdu ištrinti ryšio kontekste.

Kadangi darome prielaidą, kad „gyvų“ duomenų nėra daug, ši operacija turėtų įvykti gana greitai.

Na, tai viskas! Baigę sandorį nepamirškite paleiskite ANALIZĘ prireikus normalizuoti lentelės statistiką.

Galutinio scenarijaus sudarymas

Mes naudojame šį "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;

Ar galima antrą kartą nekopijuoti duomenų?Iš esmės tai įmanoma, jei pats lentelės oidas nėra susietas su jokia kita veikla iš BL pusės arba FK iš DB pusės:

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

Paleiskite scenarijų šaltinio lentelėje ir patikrinkite metrikas:

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

Viskas pavyko! Lentelė susitraukė 50 kartų ir visi ATNAUJINIAI vėl veikia greitai.

Šaltinis: www.habr.com

Добавить комментарий