ProHoster > Blog > administrasie > Wanneer VACUUM misluk, maak ons ​​die tafel met die hand skoon

Wanneer VACUUM misluk, maak ons ​​die tafel met die hand skoon

VACUUM kan "skoonmaak" van 'n tabel in PostgreSQL net wat niemand kan sien nie - dit wil sê, daar is nie 'n enkele aktiewe versoek wat begin het voordat hierdie rekords verander is nie.

Maar wat as so 'n onaangename tipe (langtermyn OLAP-lading op 'n OLTP-databasis) steeds bestaan? Hoe maak aktief aantrektafel skoon omring deur lang navrae en nie op 'n hark trap nie?

Wanneer VACUUM misluk, maak ons ​​die tafel met die hand skoon

Die hark uitlê

Kom ons bepaal eers wat die probleem is wat ons wil oplos en hoe dit kan ontstaan.

Gewoonlik gebeur hierdie situasie op 'n relatief klein tafel, maar waarin dit voorkom baie veranderinge. Gewoonlik dit of anders meters/aggregate/graderings, waarop UPDATE dikwels uitgevoer word, of buffer-tou om een ​​of ander voortdurend deurlopende stroom van gebeure te verwerk, waarvan rekords voortdurend INSERT/DELETE is.

Kom ons probeer om die opsie met graderings weer te gee:

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;

En in parallel, in 'n ander verband, 'n lang, lang versoek begin, versamel 'n paar komplekse statistieke, maar nie ons tafel beïnvloed nie:

SELECT pg_sleep(10000);

Nou werk ons ​​die waarde van een van die tellers baie, baie keer op. Vir die suiwerheid van die eksperiment, kom ons doen dit in afsonderlike transaksies met behulp van dblinkhoe dit in werklikheid sal gebeur:

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

Wat het gebeur? Hoekom selfs vir die eenvoudigste UPDATE van 'n enkele rekord uitvoeringstyd met 7 keer verswak - van 0.524ms tot 3.808ms? En ons gradering bou al hoe stadiger.

Dit is alles MVCC se skuld.

Dit gaan alles oor MVCC meganisme, wat veroorsaak dat die navraag deur alle vorige weergawes van die inskrywing kyk. So kom ons maak ons ​​tafel skoon van "dooie" weergawes:

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

O, daar is niks om skoon te maak nie! Parallel Die lopende versoek meng in met ons - hy kan immers eendag na hierdie weergawes wil wend (wat as?), en dit behoort vir hom beskikbaar te wees. En daarom sal selfs VAKUUM VOL ons nie help nie.

"Invou" die tafel

Maar ons weet vir seker dat daardie navraag nie ons tabel nodig het nie. Daarom sal ons steeds probeer om die stelselwerkverrigting tot voldoende limiete terug te bring deur alles wat onnodig is van die tabel uit te skakel - ten minste "handmatig", aangesien VACUUM ingee.

Om dit duideliker te maak, kom ons kyk na die voorbeeld van die geval van 'n buffertabel. Dit wil sê, daar is 'n groot vloei van INSERT/DELETE, en soms is die tabel heeltemal leeg. Maar as dit nie leeg is nie, moet ons stoor die huidige inhoud daarvan.

#0: Beoordeel die situasie

Dit is duidelik dat jy selfs na elke operasie iets met die tabel kan probeer doen, maar dit maak nie veel sin nie – die instandhoudingsbokoste sal duidelik groter wees as die deurset van die teikennavrae.

Kom ons formuleer die kriteria - "dit is tyd om op te tree" as:

  • VACUUM is redelik lank gelede bekendgestel
    Ons verwag 'n swaar vrag, so laat dit wees 60 sekondes sedert die laaste [outo]VAKUUM.
  • fisiese tafelgrootte is groter as teiken
    Kom ons definieer dit as twee keer die aantal bladsye (8KB blokke) relatief tot die minimum grootte - 1 blk vir hoop + 1 blk vir elke indeks - vir 'n potensieel leë tafel. As ons verwag dat 'n sekere hoeveelheid data altyd "normaalweg" in die buffer sal bly, is dit redelik om hierdie formule aan te pas.

Verifikasie versoek

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: Steeds VAKUUM

Ons kan nie vooraf weet of 'n parallelle navraag beduidend met ons inmeng nie - presies hoeveel rekords het "verouderd" geword sedert dit begin het. Daarom, wanneer ons besluit om die tabel op een of ander manier te verwerk, moet ons in elk geval eers daarop uitvoer VACUUM - anders as VACUUM FULL, meng dit nie in met parallelle prosesse wat met lees-skryfdata werk nie.

Terselfdertyd kan dit die meeste van wat ons graag wil verwyder onmiddellik skoonmaak. Ja, en daaropvolgende navrae op hierdie tabel sal na ons gaan deur "hot cache", wat hul duur sal verminder - en dus die totale tyd om ander deur ons dienstransaksie te blokkeer.

#2: Is iemand tuis?

Kom ons kyk of daar enigsins iets in die tabel is:

TABLE tbl LIMIT 1;

As daar nie 'n enkele rekord oor is nie, kan ons baie bespaar op verwerking deur eenvoudig te hardloop AFKNIPPEL:

Dit tree dieselfde op as 'n onvoorwaardelike DELETE-opdrag vir elke tabel, maar is baie vinniger aangesien dit nie eintlik die tabelle skandeer nie. Boonop maak dit onmiddellik skyfspasie vry, so dit is nie nodig om 'n VACUUM-bewerking daarna uit te voer nie.

Of jy die tabelvolgorde-teller (HERBEGIN IDENTITEIT) moet terugstel, is aan jou om te besluit.

#3: Almal – maak beurte!

Aangesien ons in 'n hoogs mededingende omgewing werk, terwyl ons kyk vir die afwesigheid van inskrywings in die tabel, kon iemand reeds iets daar geskryf het. Ons moet nie hierdie inligting verloor nie, so wat? Dis reg, ons moet seker maak dat niemand dit vir seker kan neerskryf nie.

Om dit te doen moet ons aktiveer SERIALISEERBAAR-isolasie vir ons transaksie (ja, hier begin ons 'n transaksie) en sluit die tafel "styf":

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

Hierdie vlak van blokkering word bepaal deur die bewerkings wat ons daarop wil uitvoer.

#4: Botsing van belange

Ons kom hierheen en wil die bord “sluit” - wat as iemand op daardie oomblik aktief daarop was, byvoorbeeld, daaruit gelees het? Ons sal “hang” en wag dat hierdie blok vrygestel word, en ander wat wil lees sal ons raakloop...

Om te voorkom dat dit gebeur, sal ons onsself "opoffer" - as ons nie 'n slot binne 'n sekere (aanvaarbare kort) tyd kon kry nie, dan sal ons 'n uitsondering van die basis ontvang, maar ons sal ten minste nie te veel inmeng met ander.

Om dit te doen, stel die sessie veranderlike lock_timeout (vir weergawes 9.3+) of/en statement_timeout. Die belangrikste ding om te onthou is dat die statement_timeout-waarde slegs van toepassing is vanaf die volgende stelling. Dit wil sê, so in gom - sal nie werk nie: 

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

Om nie te doen met die herstel van die "ou" waarde van die veranderlike later nie, gebruik ons ​​die vorm STEL PLAASLIK, wat die omvang van die instelling tot die huidige transaksie beperk.

Ons onthou dat statement_timeout van toepassing is op alle daaropvolgende versoeke sodat die transaksie nie tot onaanvaarbare waardes kan strek as daar baie data in die tabel is nie.

#5: Kopieer data

As die tabel nie heeltemal leeg is nie, sal die data weer gestoor moet word deur 'n tydelike hulptabel te gebruik:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Handtekening OP COMMIT DROP beteken dat op die oomblik dat die transaksie eindig, die tydelike tabel ophou om te bestaan, en dit is nie nodig om dit handmatig in die verbindingskonteks uit te vee nie.

Aangesien ons aanvaar dat daar nie baie "lewendige" data is nie, behoort hierdie operasie redelik vinnig te plaasvind.

Wel, dis al! Moenie vergeet nadat die transaksie voltooi is nie hardloop ANALISE om tabelstatistieke te normaliseer indien nodig.

Stel die finale draaiboek saam

Ons gebruik hierdie "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;

Is dit moontlik om nie die data 'n tweede keer te kopieer nie?In beginsel is dit moontlik as die oid van die tabel self nie gekoppel is aan enige ander aktiwiteite van die BL-kant of FK van die DB-kant af nie:

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

Kom ons voer die skrif op die brontabel uit en kontroleer die maatstawwe:

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

Alles het uitgewerk! Die tabel het met 50 keer gekrimp en alle UPDATEs loop weer vinnig.

Bron: will.com

Voeg 'n opmerking