Nuair a dh’ fhailicheas VACUUM, glanaidh sinn am bòrd le làimh

VACUUM chan urrainn dhaibh “glanadh” bho bhòrd ann am PostgreSQL dìreach dè chan fhaic duine - is e sin, chan eil aon iarrtas gnìomhach ann a thòisich mus deach na clàran sin atharrachadh.

Ach dè ma tha seòrsa cho mì-thlachdmhor (luchd OLAP fad-ùine air stòr-dàta OLTP) fhathast ann? Ciamar glan gu gnìomhach ag atharrachadh clàr air a chuairteachadh le ceistean fada agus gun a bhith ceum air ràcan?

A 'sgaoileadh an ròin

An toiseach, feuchaidh sinn ri faighinn a-mach dè an duilgheadas a tha sinn airson fhuasgladh agus ciamar a dh’ èiricheas e.

Mar as trice bidh an suidheachadh seo a’ tachairt air bòrd an ìre mhath beag, ach anns am bheil e a' tachairt tòrr atharrachaidhean. Mar as trice seo no eadar-dhealaichte meatairean / co-chruinneachaidhean / rangachadh, air am bi ÙRACHADH gu tric air a chur gu bàs, no ciudha bufair gus cuid de shruth thachartasan leantainneach a phròiseasadh, agus tha clàran dhiubh an-còmhnaidh INSERT / DELETE.

Feuchaidh sinn ris an roghainn ath-riochdachadh le rangachadh:

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;

Agus ann an co-shìnte, ann an ceangal eile, tha iarrtas fada, fada a 'tòiseachadh, a' cruinneachadh cuid de staitistig iom-fhillte, ach gun a bhith toirt buaidh air ar bòrd:

SELECT pg_sleep(10000);

A-nis bidh sinn ag ùrachadh luach aon de na cunntairean iomadh uair. Airson purrachd an deuchainn, dèanamaid seo ann an gnothaichean fa leth a’ cleachdadh dblinkmar a thachras e ann an da-rìribh:

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

Dè a thachair? Carson eadhon airson an ÙRACHADH as sìmplidh de aon chlàr ùine cur gu bàs air a lughdachadh 7 tursan - bho 0.524ms gu 3.808ms? Agus tha an rangachadh againn a’ togail barrachd is nas slaodaiche.

Tha e uile an urra ri MVCC.

Tha e mu dheidhinn Uidheam MVCC, a bheir air a’ cheist coimhead tro gach dreach roimhe den inntrig. Mar sin glanaidh sinn ar bòrd bho dhreachan “marbh”:

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, chan eil dad ri ghlanadh! Co-shìnte Tha an t-iarrtas ruith a 'cur bacadh oirnn - às deidh a h-uile càil, is dòcha gum bi e uaireigin ag iarraidh tionndadh gu na dreachan sin (dè ma tha?), Agus bu chòir dhaibh a bhith rim faighinn leis. Agus mar sin cha chuidich eadhon VACUUM FULL sinn.

“A’ crìonadh” am bòrd

Ach tha fios againn le cinnt nach fheum a’ cheist sin ar bòrd. Mar sin, feuchaidh sinn fhathast ri coileanadh an t-siostaim a thilleadh gu crìochan iomchaidh le bhith a’ cuir às do gach rud neo-riatanach bhon chlàr - co-dhiù “le làimh”, leis gu bheil VACUUM a’ toirt a-steach.

Gus a dhèanamh nas soilleire, leig dhuinn sùil a thoirt air an eisimpleir de chùis clàr bufair. Is e sin, tha sruthadh mòr de INSERT / DELETE, agus uaireannan tha am bòrd gu tur falamh. Ach mura h-eil e falamh, feumaidh sinn sàbhail an t-susbaint a th’ ann an-dràsta.

#0: A’ measadh an t-suidheachaidh

Tha e soilleir gun urrainn dhut feuchainn ri rudeigin a dhèanamh leis a ’bhòrd eadhon às deidh gach gnìomh, ach chan eil seo a’ dèanamh mòran ciall - bidh e soilleir gum bi an cosgais cumail suas nas àirde na trochur nan ceistean targaid.

Feuch an cruthaich sinn na slatan-tomhais - “tha an t-àm ann a dhol an gnìomh” ma tha:

• Chaidh VACUUM a chuir air bhog o chionn fhada
  Tha sinn an dùil ri luchd trom, mar sin leig leis a bhith 60 diogan bhon [auto] VACUUM mu dheireadh.
• tha meud clàr corporra nas motha na an targaid
  Nach mìnich sinn e mar a dhà uimhir an àireamh de dhuilleagan (blocaichean 8KB) an coimeas ris a’ mheud as lugha - 1 blk airson tiùrr + 1 blk airson gach clàr-amais - airson bòrd a dh'fhaodadh a bhith falamh. Ma tha sinn an dùil gum fuirich cuid de dhàta an-còmhnaidh sa bhufair “mar as trice”, tha e reusanta am foirmle seo a tweakadh.

Iarrtas dearbhaidh

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: Fhathast VACUUM

Chan urrainn dhuinn fios a bhith againn ro-làimh a bheil ceist co-shìnte a’ cur dragh mòr oirnn - gu cinnteach cia mheud clàr a tha “a-mach à aois” bho thòisich e. Mar sin, nuair a cho-dhùnas sinn an clàr a phròiseasadh ann an dòigh air choreigin, co-dhiù, bu chòir dhuinn a chuir gu bàs an toiseach air VACUUM - eu-coltach ri VACUUM FULL, chan eil e a’ cur bacadh air pròiseasan co-shìnte ag obair le dàta leughaidh-sgrìobhaidh.

Aig an aon àm, faodaidh e a 'mhòr-chuid de na tha sinn airson a thoirt air falbh a ghlanadh sa bhad. Tha, agus thèid ceistean às deidh sin air a’ chlàr seo thugainn le "hot cache", a lughdaicheas an ùine aca - agus, mar sin, an ùine iomlan airson casg a chuir air feadhainn eile leis a’ ghnothach seirbheis againn.

#2: A bheil duine aig an taigh?

Feuch an dèan sinn cinnteach a bheil dad sam bith sa chlàr:

TABLE tbl LIMIT 1;

Mura h-eil aon chlàr air fhàgail, is urrainn dhuinn tòrr a shàbhaladh air giullachd le bhith dìreach a’ dèanamh TRÈANAMH:

Bidh e ag obair mar an ceudna ri àithne DELETE gun chumhachan airson gach clàr, ach tha e tòrr nas luaithe leis nach eil e dha-rìribh a’ sganadh na bùird. A bharrachd air an sin, bidh e a’ saoradh àite diosc sa bhad, agus mar sin chan eil feum air gnìomhachd VACUUM a dhèanamh às deidh sin.

Tha e an urra riut fhèin co-dhùnadh am feum thu an cuntair sreath bùird ath-shuidheachadh (RESTART IDENTITY).

#3: A h-uile duine - gabh mu seach!

Leis gu bheil sinn ag obair ann an àrainneachd air leth farpaiseach, fhad ‘s a tha sinn an seo a’ dèanamh cinnteach nach eil inntrigidhean sa chlàr, dh’ fhaodadh cuideigin a bhith air rudeigin a sgrìobhadh an sin mu thràth. Cha bu chòir dhuinn am fiosrachadh seo a chall, mar sin dè? Tha sin ceart, feumaidh sinn dèanamh cinnteach nach urrainn do dhuine sam bith a sgrìobhadh sìos le cinnt.

Gus seo a dhèanamh feumaidh sinn a bhith comasach SRAIDIACH- aonaranachd airson ar gnothach (tha, an seo tòisichidh sinn malairt) agus glasaidh sinn am bòrd “gu teann”:

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

Tha an ìre seo de bhacadh air a dhearbhadh leis na h-obraichean a tha sinn airson a dhèanamh air.

#4: Còmhstri eadar com-pàirt

Bidh sinn a 'tighinn an seo agus tha sinn airson an soidhne "glasadh" - dè ma bha cuideigin gnìomhach air aig an àm sin, mar eisimpleir, a' leughadh bhuaithe? Bidh sinn “crochte” a’ feitheamh ris a’ bhloc seo a leigeil ma sgaoil, agus ruithidh feadhainn eile a tha airson leughadh a-steach thugainn...

Gus casg a chuir air seo, nì sinn “ìobradh oirnn fhìn” - mura b’ urrainn dhuinn glas fhaighinn taobh a-staigh ùine shònraichte (gu math goirid), gheibh sinn eisgeachd bhon bhunait, ach co-dhiù cha chuir sinn cus bacadh air. cuid eile.

Gus seo a dhèanamh, suidhich caochladair an t-seisein glas_ùine a-mach (airson dreachan 9.3+) no/agus aithris_ùine a-mach. Is e am prìomh rud ri chuimhneachadh nach eil an luach aithris_timeout a’ buntainn ach bhon ath aithris. Is e sin, mar seo ann an gluing - chan obraich:

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

Gus nach fheum sinn dèiligeadh ri ath-nuadhachadh “seann” luach an caochlaideach nas fhaide air adhart, cleachdaidh sinn am foirm SET IONADAIL, a tha a’ cuingealachadh farsaingeachd an t-suidheachaidh don ghnothach gnàthach.

Tha sinn a’ cuimhneachadh gu bheil aithris_timeout a’ buntainn ris a h-uile iarrtas às deidh sin gus nach urrainn don ghnothach sìneadh gu luachan neo-iomchaidh ma tha tòrr dàta sa chlàr.

# 5: Dèan lethbhreac den dàta

Mura h-eil am bòrd gu tur falamh, feumar an dàta a shàbhaladh a-rithist le clàr sealach cuideachail:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Ainm-sgrìobhte AIR COMMIT DROP a’ ciallachadh, aig an àm a thig an gnothach gu crìch, nach bi am bòrd sealach ann, agus nach fheumar a dhubhadh às le làimh ann an co-theacsa ceangail.

Leis gu bheil sinn a’ gabhail ris nach eil mòran dàta “beò” ann, bu chòir an obair seo tachairt gu math luath.

Uill, tha sin uile! Na dì-chuimhnich às deidh dhut an gnothach a chrìochnachadh run ANAILEADH gus staitistig bùird a dhèanamh àbhaisteach ma tha sin riatanach.

A 'cur ri chèile an sgriobt mu dheireadh

Bidh sinn a’ cleachdadh an “pseudo-python” seo:

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

A bheil e comasach gun an dàta a chopaigeadh an dàrna turas?Ann am prionnsapal, tha e comasach mura h-eil oid a’ bhùird fhèin ceangailte ri gnìomhachd sam bith eile bho thaobh BL no FK bhon taobh 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;

Feuch an ruith sinn an sgriobt air a’ chlàr stòr agus thoir sùil air na meatrach:

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

Dh’ obraich a h-uile càil a-mach! Tha an clàr air crìonadh 50 uair agus tha a h-uile ÙRACHADH a’ ruith gu sgiobalta a-rithist.

Source: www.habr.com