ProHoster > blog > Amministrazzjoni > Meta VACUUM jonqos, aħna naddaf il-mejda manwalment

Meta VACUUM jonqos, aħna naddaf il-mejda manwalment

Vakwu jista '"naddaf" minn tabella f'PostgreSQL biss dak ħadd ma jista’ jara - jiġifieri, m'hemmx talba waħda attiva li bdiet qabel ma nbidlu dawn ir-rekords.

Imma x'jiġri jekk tali tip spjaċevoli (tagħbija OLAP fit-tul fuq database OLTP) għadha teżisti? Kif naddaf mejda li tinbidel b'mod attiv imdawwar minn mistoqsijiet twal u mhux pass fuq rake?

Meta VACUUM jonqos, aħna naddaf il-mejda manwalment

Tqegħid ir-rake

L-ewwel, ejja niddeterminaw x'inhi l-problema li rridu nsolvu u kif tista' tqum.

Normalment din is-sitwazzjoni tiġri fuq mejda relattivament żgħira, iżda li fih iseħħ ħafna bidliet. Normalment dan jew differenti meters/aggregati/klassifikazzjonijiet, li fuqha ħafna drabi titwettaq AĠĠORNAMENT, jew buffer-queue biex tipproċessa xi fluss kontinwu ta' avvenimenti, li r-rekords tagħhom huma kontinwament Daħħal/ĦAĦSAR.

Ejja nippruvaw nirriproduċu l-għażla bil-klassifikazzjonijiet:

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;

U b'mod parallel, f'konnessjoni oħra, tibda talba twila u twila, tiġbor xi statistika kumplessa, iżda ma taffettwax il-mejda tagħna:

SELECT pg_sleep(10000);

Issa aħna naġġornaw il-valur ta 'wieħed mill-counters ħafna, ħafna drabi. Għall-purità tal-esperiment, ejja nagħmlu dan fi tranżazzjonijiet separati bl-użu ta’ dblinkkif se jiġri fir-realtà:

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

X'ġara? Għaliex anke għall-aktar sempliċi AĠĠORNAMENT ta 'rekord wieħed ħin ta 'eżekuzzjoni degradat b'7 darbiet - minn 0.524ms sa 3.808ms? U l-klassifikazzjoni tagħna qed tibni aktar u aktar bil-mod.

Kollox tort tal-MVCC.

Huwa dwar mekkaniżmu MVCC, li tikkawża li l-mistoqsija tħares mill-verżjonijiet preċedenti kollha tad-dħul. Mela ejja naddaf it-tabella tagħna minn verżjonijiet "mejta":

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

Oh, m'hemm xejn x'tnaddaf! Parallel It-talba kurrenti qed tinterferixxi magħna - wara kollox, jista 'xi darba jkun irid idur għal dawn il-verżjonijiet (x'jiġri jekk?), u għandhom ikunu disponibbli għalih. U għalhekk anke VACUUM FULL mhux se jgħinna.

"Kollass" it-tabella

Imma nafu żgur li dik il-mistoqsija m'għandhiex bżonn it-tabella tagħna. Għalhekk, xorta se nippruvaw nirritornaw il-prestazzjoni tas-sistema għal limiti adegwati billi neliminaw dak kollu mhux meħtieġ mit-tabella - għall-inqas "manwalment", peress li VACUUM iċedi.

Biex tagħmilha aktar ċara, ejja nħarsu lejn l-eżempju tal-każ ta 'tabella buffer. Jiġifieri, hemm fluss kbir ta 'INSERT/DELETE, u xi kultant it-tabella hija kompletament vojta. Imma jekk ma jkunx vojt, irridu ħlief il-kontenut attwali tiegħu.

#0: Evalwazzjoni tas-sitwazzjoni

Huwa ċar li tista 'tipprova tagħmel xi ħaġa bit-tabella anke wara kull operazzjoni, iżda dan ma jagħmilx sens - l-overhead ta' manutenzjoni b'mod ċar se jkun akbar mill-fluss tal-mistoqsijiet fil-mira.

Ejja nifformulaw il-kriterji - "wasal iż-żmien li taġixxi" jekk:

  • VACUUM tnieda żmien twil ilu
    Nistennew tagħbija tqila, allura ħalliha tkun 60 sekondi mill-aħħar [awto]VACUUM.
  • Id-daqs fiżiku tal-mejda huwa akbar mill-mira
    Ejja niddefinixxuha bħala d-doppju tan-numru ta' paġni (blokki 8KB) relattiv għad-daqs minimu - 1 blk għal borġ + 1 blk għal kull indiċi - għal mejda potenzjalment vojta. Jekk nistennew li ċertu ammont ta 'dejta dejjem jibqa' fil-buffer "normalment", huwa raġonevoli li tweak din il-formula.

Talba ta' verifika

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: Għadha VACUUM

Ma nistgħux inkunu nafu minn qabel jekk mistoqsija parallela hijiex tinterferixxi magħna b'mod sinifikanti - eżattament kemm rekords saru "skaduti" minn meta bdiet. Għalhekk, meta niddeċiedu li b'xi mod nipproċessaw it-tabella, fi kwalunkwe każ, l-ewwel għandna nwettqu fuqha Vakwu - kuntrarjament għal VACUUM FULL, ma jinterferixxix mal-proċessi paralleli li jaħdmu ma 'data read-write.

Fl-istess ħin, jista 'minnufih tnaddaf ħafna minn dak li nixtiequ neħħi. Iva, u mistoqsijiet sussegwenti fuq din it-tabella se jmorru lilna minn "hot cache", li se jnaqqas it-tul tagħhom - u, għalhekk, il-ħin totali tal-imblukkar ta 'oħrajn bit-tranżazzjoni ta' manutenzjoni tagħna.

#2: Huwa xi ħadd dar?

Ejja niċċekkjaw jekk hemmx xi ħaġa fit-tabella:

TABLE tbl LIMIT 1;

Jekk ma jkunx fadal rekord wieħed, allura nistgħu niffrankaw ħafna fuq l-ipproċessar billi sempliċement nagħmlu TRONKA:

Jaġixxi l-istess bħal kmand ħassar bla kundizzjoni għal kull tabella, iżda huwa ħafna aktar mgħaġġel peress li fil-fatt ma jiskennjax it-tabelli. Barra minn hekk, immedjatament jeħles l-ispazju tad-diska, u għalhekk m'hemmx bżonn li twettaq operazzjoni VACUUM wara.

Jekk għandekx bżonn tirrisettja l-counter tas-sekwenza tal-mejda (RESTART IDENTITÀ) huwa f'idejk li tiddeċiedi.

#3: Kulħadd - jieħu dawra!

Peress li naħdmu f'ambjent kompetittiv ħafna, filwaqt li qegħdin hawn niċċekkjaw li m'hemmx entrati fit-tabella, xi ħadd seta' diġà kiteb xi ħaġa hemmhekk. M'għandniex nitilfu din l-informazzjoni, allura xiex? Hekk hu, irridu niżguraw li ħadd ma jista’ jiktebha żgur.

Biex nagħmlu dan irridu nippermettu SERJALIZZABBLI-iżolament għat-tranżazzjoni tagħna (iva, hawn nibdew tranżazzjoni) u ssakkar it-tabella "sewwa":

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

Dan il-livell ta 'imblukkar huwa determinat mill-operazzjonijiet li rridu nwettqu fuqu.

#4: Kunflitt ta’ interess

Aħna niġu hawn u rridu "jillokkjaw" is-sinjal - x'jiġri jekk xi ħadd kien attiv fuqu f'dak il-mument, pereżempju, jaqra minnu? Aħna se "hang" nistennew li dan il-blokk jiġi rilaxxat, u oħrajn li jridu jaqraw jidħlu magħna...

Biex ma jseħħx dan, aħna se "sagrifikaw lilna nfusna" - jekk ma konniex kapaċi niksbu lock f'ċertu żmien (aċċettabbli qasir), allura nirċievu eċċezzjoni mill-bażi, iżda għallinqas mhux se nindaħlu wisq ma ' oħrajn.

Biex tagħmel dan, issettja l-varjabbli tas-sessjoni lock_timeout (għall-verżjonijiet 9.3+) jew/u statement_timeout. Il-ħaġa prinċipali li għandek tiftakar hija li l-valur statement_timeout japplika biss mid-dikjarazzjoni li jmiss. Jiġifieri, bħal dan fl-inkullar - mhux se taħdem: 

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

Sabiex ma jkollokx għalfejn niffaċċjaw ir-restawr tal-valur "antik" tal-varjabbli aktar tard, nużaw il-formola SET LOKALI, li jillimita l-ambitu tal-issettjar għat-tranżazzjoni kurrenti.

Niftakru li statement_timeout japplika għat-talbiet kollha sussegwenti sabiex it-tranżazzjoni ma tkunx tista' tinfirex għal valuri inaċċettabbli jekk ikun hemm ħafna dejta fit-tabella.

#5: Ikkopja dejta

Jekk it-tabella ma tkunx kompletament vojta, id-dejta trid tiġi salvata mill-ġdid billi tuża tabella temporanja awżiljarja:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Firma FUQ L-IMPENJA WAQT ifisser li fil-mument li tintemm it-tranżazzjoni, it-tabella temporanja se tieqaf teżisti, u m'hemmx bżonn li titħassarha manwalment fil-kuntest tal-konnessjoni.

Peress li nassumu li m'hemmx ħafna dejta "ħaj", din l-operazzjoni għandha sseħħ pjuttost malajr.

Ukoll, dak kollu! Tinsiex wara li tlesti t-tranżazzjoni run ANALYZE biex tiġi normalizzata l-istatistika tat-tabella jekk meħtieġ.

Tgħaqqad l-iskrittura finali

Aħna nużaw dan il-“psewdo-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;

Huwa possibbli li ma tikkopja d-data għat-tieni darba?Fil-prinċipju, huwa possibbli jekk l-oid tat-tabella nnifisha ma jkunx marbut ma 'xi attivitajiet oħra min-naħa BL jew FK min-naħa 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;

Ejja nħaddmu l-iskrittura fuq it-tabella tas-sors u ċċekkja l-metriċi:

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

Kollox ħadem! It-tabella naqset b'50 darba u l-AĠĠORNAMENTI kollha qed jerġgħu jitmexxew malajr.

Sors: www.habr.com

Żid kumment