Þegar VACUUM bilar hreinsum við borðið handvirkt

VACUUM getur "hreinsað upp" af töflu í PostgreSQL aðeins hvað enginn getur séð - það er ekki ein virk beiðni sem byrjaði áður en þessum skrám var breytt.

En hvað ef svo óþægileg tegund (langtíma OLAP álag á OLTP gagnagrunn) er enn til? Hvernig hreint virkan skiptiborð umkringdur löngum fyrirspurnum og ekki stíga á hrífu?

Að leggja út hrífuna

Fyrst skulum við ákveða hvert vandamálið sem við viljum leysa er og hvernig það getur komið upp.

Venjulega gerist þetta ástand á tiltölulega litlu borði, en þar sem það kemur fyrir miklar breytingar. Venjulega þetta eða öðruvísi metrar/upplag/einkunnir, þar sem UPDATE er oft keyrt, eða biðminni-röð til að vinna úr einhverjum stöðugum straumi atburða, þar sem skrár eru stöðugt INSERT/DELETE.

Við skulum reyna að endurskapa valkostinn með einkunnum:

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;

Og samhliða, í öðru sambandi, byrjar löng, löng beiðni, safna flóknum tölfræði, en hefur ekki áhrif á borðið okkar:

SELECT pg_sleep(10000);

Nú uppfærum við verðgildi eins af teljara mörgum, mörgum sinnum. Fyrir hreinleika tilraunarinnar skulum við gera þetta í aðskildum viðskiptum með dblinkhvernig það mun gerast í raun og veru:

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

Hvað gerðist? Hvers vegna jafnvel fyrir einföldustu UPDATE á einni skrá framkvæmdartími styttist um 7 sinnum — frá 0.524ms til 3.808ms? Og einkunn okkar er að byggjast meira og hægar.

Þetta er allt MVCC að kenna.

Þetta snýst allt um MVCC vélbúnaður, sem veldur því að fyrirspurnin flettir í gegnum allar fyrri útgáfur af færslunni. Svo skulum við hreinsa borðið okkar af „dauðum“ útgáfum:

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

Ó, það er ekkert að þrífa! Samhliða Rekstrarbeiðnin truflar okkur - þegar allt kemur til alls gæti hann einhvern tíma viljað snúa sér að þessum útgáfum (hvað ef?), og þær ættu að vera honum aðgengilegar. Og þess vegna mun jafnvel VACUUM FULL ekki hjálpa okkur.

„Hruna saman“ borðið

En við vitum fyrir víst að sú fyrirspurn þarf ekki töfluna okkar. Þess vegna munum við samt reyna að skila afköstum kerfisins í fullnægjandi mörk með því að útrýma öllu óþarfa af borðinu - að minnsta kosti "handvirkt", þar sem VACUUM gefst upp.

Til að gera það skýrara skulum við skoða dæmið um tilfelli biðminnistöflu. Það er, það er mikið flæði af INSERT/DELETE og stundum er borðið alveg tómt. En ef það er ekki tómt verðum við vista núverandi innihald þess.

#0: Að meta ástandið

Það er ljóst að þú getur reynt að gera eitthvað við töfluna jafnvel eftir hverja aðgerð, en það þýðir ekki mikið - viðhaldskostnaðurinn verður greinilega meiri en afköst markmiðsfyrirspurnanna.

Við skulum móta viðmiðin - „það er kominn tími til að bregðast við“ ef:

• VACUUM var hleypt af stokkunum fyrir nokkuð löngu síðan
  Við gerum ráð fyrir miklu álagi, svo látum það vera 60 sekúndur frá síðasta [sjálfvirku]VACUUM.
• líkamleg borðstærð er stærri en markmiðið
  Við skulum skilgreina það sem tvöfaldan fjölda síðna (8KB blokkir) miðað við lágmarksstærð - 1 blk fyrir hrúgu + 1 blk fyrir hverja vísitölu - fyrir hugsanlega tómt borð. Ef við gerum ráð fyrir því að ákveðið magn af gögnum verði alltaf í biðminni „venjulega“, er sanngjarnt að fínstilla þessa formúlu.

Staðfestingarbeiðni

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: Enn Tómarúm

Við getum ekki vitað fyrirfram hvort samhliða fyrirspurn trufli okkur verulega - nákvæmlega hversu margar færslur hafa orðið „úreltar“ síðan hún hófst. Þess vegna, þegar við ákveðum að vinna úr töflunni á einhvern hátt, ættum við í öllum tilvikum fyrst að framkvæma á henni VACUUM - ólíkt VACUUM FULL, truflar það ekki samhliða ferli sem vinna með les- og skrifa gögn.

Á sama tíma getur það strax hreinsað út flest það sem við viljum fjarlægja. Já, og síðari fyrirspurnir á þessari töflu munu fara til okkar eftir "hot cache", sem mun draga úr tímalengd þeirra - og þar af leiðandi heildartíma þess að loka fyrir aðra með þjónustuviðskiptum okkar.

#2: Er einhver heima?

Við skulum athuga hvort það sé eitthvað í töflunni:

TABLE tbl LIMIT 1;

Ef það er ekki ein skrá eftir, þá getum við sparað mikið í vinnslu með því einfaldlega að keyra LYKJA:

Það virkar eins og skilyrðislaus DELETE skipun fyrir hverja töflu, en er miklu hraðari þar sem hún skannar ekki töflurnar. Þar að auki losar það strax um pláss, svo það er engin þörf á að framkvæma VACUUM aðgerð á eftir.

Hvort þú þarft að endurstilla töfluraðarteljarann ​​(RESTART IDENTITY) er þitt að ákveða.

#3: Allir - skiptast á!

Þar sem við vinnum í mjög samkeppnisumhverfi, á meðan við erum hér að athuga hvort engar færslur séu í töflunni, gæti einhver þegar skrifað eitthvað þar. Við ættum ekki að missa þessar upplýsingar, hvað svo? Það er rétt, við þurfum að passa að enginn geti skrifað það niður með vissu.

Til að gera þetta þurfum við að virkja Raðhæft-einangrun fyrir viðskiptin okkar (já, hér byrjum við viðskipti) og læstu borðinu „þétt“:

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

Þetta stig lokunar ræðst af aðgerðunum sem við viljum framkvæma á því.

#4: Hagsmunaárekstrar

Við komum hingað og viljum „læsa“ merkinu - hvað ef einhver væri virkur á því á því augnabliki, til dæmis að lesa úr því? Við munum „hanga“ og bíða eftir að þessi kubb verði gefin út og aðrir sem vilja lesa munu rekast á okkur...

Til að koma í veg fyrir að þetta gerist munum við „fórna okkur“ - ef við gátum ekki náð lás innan ákveðins (viðunandi stutts) tíma, þá fáum við undanþágu frá stöðinni, en við munum að minnsta kosti ekki trufla okkur of mikið af öðrum.

Til að gera þetta skaltu stilla lotubreytuna lock_timeout (fyrir útgáfur 9.3+) eða/og statement_timeout. Aðalatriðið sem þarf að muna er að gildið statement_timeout gildir aðeins frá næstu setningu. Það er, svona í límingu - mun ekki virka:

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

Til þess að þurfa ekki að takast á við að endurheimta „gamla“ gildi breytunnar síðar notum við formið SETJA LOKALT, sem takmarkar umfang stillingarinnar við núverandi viðskipti.

Við munum að statement_timeout á við um allar síðari beiðnir þannig að viðskiptin geta ekki teygt sig upp í óviðunandi gildi ef það er mikið af gögnum í töflunni.

#5: Afritaðu gögn

Ef taflan er ekki alveg tóm verður að vista gögnin aftur með því að nota bráðabirgðatöflu:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Undirskrift ON COMMIT DROP þýðir að á því augnabliki sem færslunni lýkur mun bráðabirgðataflan hætta að vera til og það er engin þörf á að eyða henni handvirkt í tengingarsamhenginu.

Þar sem við gerum ráð fyrir að það sé ekki mikið af „lifandi“ gögnum ætti þessi aðgerð að fara fram nokkuð hratt.

Jæja, það er allt! Ekki gleyma eftir að hafa lokið viðskiptum keyra ANALYSE til að staðla töflutölfræði ef þörf krefur.

Að setja saman lokahandritið

Við notum þetta „gervi-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;

Er ekki hægt að afrita gögnin í annað sinn?Í grundvallaratriðum er það mögulegt ef horn borðsins sjálfs er ekki bundið við neina aðra starfsemi frá BL hlið eða FK frá DB hlið:

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

Við skulum keyra handritið á upprunatöflunni og athuga mælikvarðana:

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

Allt gekk upp! Taflan hefur dregist saman um 50 sinnum og allar uppfærslur ganga hratt aftur.

Heimild: www.habr.com