Nuair a theipeann ar fholúntas, glanaimid an tábla de láimh

VACUUM in ann “glanadh suas” ó thábla i PostgreSQL ach cad é ní féidir le duine ar bith a fheiceáil - is é sin, níl aon iarratas gníomhach amháin a thosaigh sular athraíodh na taifid sin.

Ach cad a tharlóidh má tá a leithéid de chineál míthaitneamhach (ualach OLAP fadtéarmach ar bhunachar sonraí OLTP) fós ann? Conas glan tábla atá ag athrú go gníomhach timpeallaithe ag ceisteanna fada agus gan céim ar raca?

Unfolding an raca

Ar an gcéad dul síos, déanaimis a chinneadh cad é an fhadhb a theastaíonn uainn a réiteach agus conas a d’fhéadfadh sé teacht chun cinn.

Обычно такая ситуация случается ar bhord sách beag, ach ina dtarlaíonn sé a lán athruithe. De ghnáth seo nó difriúil méadar/comhiomláin/rátálacha, ar a ndéantar UPDATE go minic, nó maolán-scuaine roinnt sruth imeachtaí leanúnacha a phróiseáil, a mbíonn taifid de shíor á ISTEACH/Scriosta.

Déanaimis iarracht an rogha a atáirgeadh le rátálacha:

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 go comhthreomhar, i nasc eile, tosaíonn iarratas fada, fada, ag bailiú roinnt staitisticí casta, ach gan cur isteach ar ár mbord:

SELECT pg_sleep(10000);

Anois déanaimid nuashonrú ar luach ceann de na cuntair go minic, go minic. Chun íonacht an turgnaimh, déanaimis é seo in idirbhearta ar leith ag baint úsáide as dblinkconas a tharlóidh sé i ndáiríre:

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

Что же произошло? Почему даже для простейшего UPDATE единственной записи am forghníomhaithe díghrádaithe faoi 7 n-uaire - ó 0.524ms go 3.808ms? Agus tá ár rátáil ag tógáil níos mó agus níos moille.

Tá an locht ar fad ar MVCC.

Tá sé ar fad faoi Meicníocht MVCC, rud a fhágann go bhféachann an cheist trí gach leagan den iontráil roimhe seo. Mar sin glanaimis ár mbord ó leaganacha “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

Ой, а чистить-то и нечего! Параллельно Tá an t-iarratas reatha ag cur isteach orainn - tar éis an tsaoil, b'fhéidir go mbeadh sé ag iarraidh casadh ar na leaganacha seo lá éigin (cad más rud é?), agus ba chóir go mbeadh siad ar fáil dó. Agus mar sin ní chuideoidh fiú folúntas iomlán linn.

“Ag titim” an tábla

Ach tá a fhios againn go cinnte nach bhfuil ár mbord ag teastáil ón gceist sin. Mar sin, déanfaimid iarracht fós feidhmíocht an chórais a thabhairt ar ais go teorainneacha leordhóthanacha trí gach rud nach bhfuil gá leis a dhíchur ón tábla - ar a laghad "de láimh", ós rud é go n-éireoidh folúntas.

Chun é a dhéanamh níos soiléire, déanaimis féachaint ar shampla tábla maolánach. Is é sin le rá, tá sreabhadh mór IONSÁIGH/Scriosta, agus uaireanta bíonn an tábla iomlán folamh. Ach mura bhfuil sé folamh, ní mór dúinn sábháil a bhfuil ann faoi láthair.

#0: An cás a mheas

Tá sé soiléir gur féidir leat iarracht rud éigin a dhéanamh leis an tábla fiú tar éis gach oibríochta, ach ní dhéanann sé seo mórán ciall - is léir go mbeidh an forchostas cothabhála níos mó ná tréchur na gceisteanna sprice.

Сформулируем критерии — «уже пора действовать», если:

• Seoladh VACUUM i bhfad ó shin
  Táimid ag súil le ualach trom, mar sin lig dó a bheith 60 soicind ón [uathoibríoch] deiridh.
• tá méid tábla fisiciúil níos mó ná an sprioc
  Sainmhínímid é mar dhá oiread líon na leathanach (bloic 8KB) i gcomparáid leis an íosmhéid - 1 blk le haghaidh gcarn + 1 blk do gach innéacs - le haghaidh tábla a d'fhéadfadh a bheith folamh. Má táimid ag súil go bhfanfaidh méid áirithe sonraí i gcónaí sa mhaolán “go hiondúil”, tá sé réasúnach an fhoirmle seo a tweak.

Iarratas fíoraithe

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: Folamh fós

Ní féidir a fhios againn roimh ré an bhfuil ceist chomhthreomhar ag cur isteach go mór orainn - go díreach cé mhéad taifead atá “as dáta” ó thosaigh sé. Mar sin, nuair a shocraímid an tábla a phróiseáil ar bhealach ar bith, in aon chás, ba cheart dúinn é a fhorghníomhú ar dtús VACUUM - murab ionann agus VACUUM FULL, ní chuireann sé isteach ar phróisis chomhthreomhara a oibríonn le sonraí inléite.

Ag an am céanna, is féidir leis an chuid is mó de na rudaí ba mhaith linn a bhaint a ghlanadh láithreach. Sea, agus cuirfear ceisteanna ar an tábla seo chugainn ina dhiaidh sin le "hot cache", rud a laghdóidh a ré - agus, dá bhrí sin, an t-am iomlán a bhaineann le daoine eile a bhlocáil ag ár n-idirbheart seirbhísithe.

#2: Есть кто-нибудь дома?

Déanaimis seiceáil an bhfuil aon rud sa tábla ar chor ar bith:

TABLE tbl LIMIT 1;

Mura bhfuil taifead amháin fágtha, ansin is féidir linn go leor a shábháil ar phróiseáil ach a dhéanamh TRUNCATE:

Feidhmíonn sé mar an gcéanna le hordú DELETE neamhchoinníollach do gach tábla, ach tá sé i bhfad níos tapúla ós rud é nach ndéanann sé na táblaí a scanadh i ndáiríre. Thairis sin, scaoileann sé spás diosca láithreach, agus mar sin ní gá oibríocht folúis a dhéanamh ina dhiaidh sin.

Is fút féin atá an cinneadh cé acu an gá duit an cuntar seicheamh tábla a athshocrú (RESTART IDENTITY).

#3: Gach duine - beir seal!

Ós rud é go n-oibrímid i dtimpeallacht an-iomaíoch, agus muid anseo ag seiceáil nach bhfuil aon iontrálacha sa tábla, d'fhéadfadh go mbeadh rud éigin scríofa ag duine éigin ann cheana féin. Níor cheart dúinn an fhaisnéis seo a chailleadh, mar sin cad é? Sin ceart, ní mór dúinn a chinntiú nach féidir le duine ar bith é a scríobh síos go cinnte.

Chun seo a dhéanamh ní mór dúinn a chumasú SRAITHREACH- leithlisiú dár n-idirbheart (tá, cuirimid tús le hidirbheart anseo) agus glasáil an tábla “go docht”:

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

Cinntear an leibhéal blocála seo ag na hoibríochtaí a theastaíonn uainn a dhéanamh air.

#4: Coinbhleacht leasa

Teacht againn anseo agus ba mhaith linn a "glas" an comhartha - cad a tharlaíonn má bhí duine éigin gníomhach air ag an nóiméad, mar shampla, ag léamh uaidh? Beimid “hang” ag fanacht go scaoilfear an bloc seo, agus rachaidh daoine eile atá ag iarraidh léamh isteach chugainn...

Chun é seo a chosc, déanfaimid “féin íobairt” - mura mbeimis in ann glas a fháil laistigh de thréimhse ama áirithe (gearr inghlactha), gheobhaidh muid eisceacht ón mbonn, ach ar a laghad ní chuirfimid isteach ró-mhór air. daoine eile.

Chun seo a dhéanamh, socraigh athróg an tseisiúin glas_am istigh (le haghaidh leaganacha 9.3+) nó/agus ráiteas_am istigh. Is é an rud is mó le cuimhneamh ná nach mbaineann luach an ráitis_ama ach amháin ón gcéad ráiteas eile. Is é sin, mar seo i gluing - ní oibreoidh:

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

Chun nach mbeidh orainn déileáil le “sean” luach na hathróige a athbhunú níos déanaí, úsáidimid an fhoirm SET ÁITIÚIL, a chuireann teorainn le raon feidhme an tsocraithe don idirbheart reatha.

Cuimhnímid go mbaineann statement_timeout le gach iarratas ina dhiaidh sin ionas nach féidir leis an idirbheart síneadh chuig luachanna do-ghlactha má tá go leor sonraí sa tábla.

#5: Cóipeáil sonraí

Mura bhfuil an tábla go hiomlán folamh, ní mór na sonraí a athshábháil trí úsáid a bhaint as tábla sealadach cúnta:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Síniú ON COMMIT Drop Ciallaíonn sé sin go dtiocfaidh deireadh leis an idirbheart faoi láthair, go scoirfidh an tábla sealadach de bheith ann, agus ní gá é a scriosadh de láimh i gcomhthéacs an naisc.

Ós rud é go nglactar leis nach bhfuil mórán sonraí “beo” ann, ba cheart go ndéanfaí an oibríocht seo go tapa go leor.

Ну вот как бы и все! Не забывайте после завершения транзакции reáchtáil ANAILÍS staitisticí tábla a normalú más gá.

An script deiridh a chur le chéile

Úsáidimid 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;

An féidir gan na sonraí a chóipeáil an dara huair?I bprionsabal, is féidir mura bhfuil oid an tábla féin ceangailte le haon ghníomhaíochtaí eile ó thaobh BL nó FK ón 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;

Rithfimid an script ar an tábla foinse agus seiceáil an mhéadracht:

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

D'oibrigh gach rud amach! Laghdaigh an tábla 50 uair agus tá gach NUASHONRUITHE ag rith go tapa arís.

Foinse: will.com