🥇Kana VACUUM ikatadza, tinochenesa tafura nemaoko

VACUUM inogona "kuchenesa" kubva patafura muPostgreSQL chete chii hapana anogona kuona - ndiko kuti, hapana chikumbiro chimwe chete chinoshanda chakatanga marekodhi aya asati achinjwa.

Asi ko kana mhando isingafadzi yakadaro (yakareba OLAP mutoro pane OLTP dhatabhesi) ichiripo? Sei yakachena kushingaira kuchinja tafura wakakomberedzwa nemibvunzo mirefu uye usingatsike pareki?

Kubhedhenura raki

Kutanga, ngationei kuti dambudziko ratinoda kugadzirisa nderei uye kuti ringamuka sei.

Kazhinji izvi zvinoitika patafura diki, asi mazvinoitika shanduko dzakawanda. Kazhinji izvi kana zvakasiyana metres/aggregates/ratings, iyo UPDATE inowanzoitwa, kana buffer-queue kugadzirisa zvimwe zvinogara zvichienderera mberi zvezviitiko, marekodhi ayo anogara INSERT/DELETE.

Ngatiedzei kuburitsa sarudzo nemaresheni:

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;

Uye zvakafanana, mune imwe hukama, chikumbiro chakareba, chakareba chinotanga, kuunganidza mamwe manhamba akaoma, asi kwete kukanganisa tafura yedu:

SELECT pg_sleep(10000);

Iye zvino isu tinovandudza kukosha kweimwe yekaunda kakawanda, kakawanda. Nokuda kwekuchena kwekuedza, ngatiitei izvi mukutengeserana kwakasiyana uchishandisa dblinkkuti zvichaitika sei muchokwadi:

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

Chii chaitika? Nei kunyangwe iri nyore UPDATE yerekodhi rimwe chete nguva yekuuraya yakaderedzwa ne7 nguva - kubva 0.524ms kusvika 3.808ms? Uye chiyero chedu chiri kuvaka zvakanyanya uye zvishoma nezvishoma.

Zvose imhosva yeMVCC.

Izvo zvese ndezve MVCC nzira, izvo zvinoita kuti mubvunzo utarise mushanduro dzese dzakapfuura dzekupinda. Saka ngatichenese tafura yedu kubva kushanduro “dzakafa”:

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

Oo, hapana chekuchenesa! Parallel Kumhanya chikumbiro kuri kutikanganisa - shure kwezvose, rimwe zuva angada kutendeukira kune idzi shanduro (ko kana?), Uye dzinofanira kuwanikwa kwaari. Uye saka kunyange VACUUM FULL haizotibatsiri.

"Kupunza" tafura

Asi isu tinoziva zvechokwadi kuti iwo mubvunzo haudi tafura yedu. Naizvozvo, isu ticharamba tichiedza kudzosa mashandiro ehurongwa kumiganhu yakakwana nekubvisa zvese zvisina basa kubva patafura - zvirinani "nemaoko", sezvo VACUUM ichipa.

Kuti zvinyatsojeka, ngatitarisei muenzaniso wenyaya yetafura yebuffer. Ndiko kuti, kune kuyerera kukuru kwe INSERT / DELETE, uye dzimwe nguva tafura haina zvachose. Asi kana isina chinhu, tinofanira chengetedza zviri mukati maro.

#0: Kuongorora mamiriro

Zviri pachena kuti iwe unogona kuedza kuita chimwe chinhu netafura kunyange mushure mekushanda kwega kwega, asi izvi hazviiti kuti zvive nemusoro - kugadzirisa kwepamusoro kuchave kwakakura kudarika kubudiswa kwemibvunzo yakatarisa.

Ngatigadzirei maitiro - "inguva yekuita" kana:

VACUUM yakatangwa kare kare
Tinotarisira mutoro unorema, saka ngazvive zvakadaro 60 seconds kubva yekupedzisira [auto]VACUUM.
Saizi yetafura yemuviri yakakura kupfuura chinangwa
Ngatizvitsanangure zvakapetwa kaviri nhamba yemapeji (8KB zvidhinha) zvine chekuita nehukuru hushoma - 1 blk pamurwi + 1 blk pane imwe neimwe index - yetafura inogona kunge isina chinhu. Kana isu tichitarisira kuti imwe nhamba yedata ichagara iri mubuffer "kazhinji", zvine musoro kugadzirisa iyi fomula.

Chikumbiro chekuongorora

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

Hatingazive pachine nguva kuti mubvunzo unofambirana uri kutikanganisa zvakanyanya - mangani marekodhi "apera" kubva payakatanga. Naizvozvo, kana isu tasarudza neimwe nzira kugadzirisa tafura, chero zvakadaro, isu tinofanira kutanga taita pairi VACUUM - Kusiyana neVACUUM FULL, haikanganisi maitiro akafanana anoshanda nekuverenga-kunyora data.

Panguva imwecheteyo, inogona kuchenesa pakarepo zvakawanda zvezvatinoda kubvisa. Ehe, uye inotevera mibvunzo patafura ino ichaenda kwatiri ne "hot cache", izvo zvinoderedza nguva yavo - uye, saka, iyo yakazara nguva yekuvharira vamwe nesevhisi yedu yekutengeserana.

#2: Pane munhu kumba here?

Ngatitarisei kana paine chero chinhu mutafura zvachose:

TABLE tbl LIMIT 1;

Kana pasina kana rekodhi rimwe chete rasara, saka tinogona kuchengetedza zvakawanda pakugadzirisa nekungoita CHINONYANYA:

Inoita zvakafanana senge isina magumo DELETE murairo wetafura yega yega, asi inokurumidza zvakanyanya sezvo isingatarise matafura. Uyezve, inokurumidza kusunungura dhisiki nzvimbo, saka hapana chikonzero chekuita VACUUM oparesheni mushure.

Kunyangwe iwe uchida kuseta patsva kaunda yekutevedzana kwetafura (RESTART IDENTITY) zviri kwauri kuti usarudze.

#3: Munhu wese - chinjana!

Sezvo isu tichishanda munzvimbo ine makwikwi zvakanyanya, isu tiri pano tichitarisa kuti hapana zvakapinda mutafura, mumwe munhu anogona kunge akatonyora chimwe chinhu ipapo. Hatifanirwe kurasikirwa neruzivo urwu, saka chii? Ndizvozvo, tinofanira kuva nechokwadi chokuti hapana anogona kunyora pasi zvechokwadi.

Kuti tiite izvi tinofanira kugonesa SERIALIZABLE-kuparadzaniswa kwekutengesa kwedu (hongu, pano tinotanga kutengeserana) uye kukiya tafura "zvakasimba":

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

Iyi nhanho yekuvharira inotarwa nemashandiro atinoda kuita pairi.

#4: Kupesana kwechido

Isu tinouya pano uye tinoda "kuvhara" chiratidzo - ko kana mumwe munhu akashanda pairi panguva iyoyo, semuenzaniso, kuverenga kubva mairi? Isu "ticharembera" takamirira kuti block iyi iburitswe, uye vamwe vanoda kuverenga vachamhanya matiri ...

Kudzivirira izvi kuti zvisaitike, isu "tichazvipira" - kana isu takatadza kuwana kukiya mukati meimwe nguva (inogamuchirika ipfupi) nguva, isu tinozogamuchira kusarudzika kubva pachigadziko, asi zvirinani isu hatizopindire zvakanyanya. vamwe.

Kuti uite izvi, sarudza shanduko yemusangano lock_timeout (yeshanduro 9.3+) kana/uye statement_timeout. Chinhu chikuru chekurangarira ndechekuti iyo statement_timeout kukosha inongoshanda kubva pane inotevera chirevo. Kureva, seizvi mukunamira - hazvishande:

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

Kuti urege kutarisana nekudzorera kukosha kwe "yekare" yekuchinja gare gare, tinoshandisa fomu GADZIRA MUNHU, iyo inogadzirisa chiyero chekugadzirisa kune ikozvino kutengeserana.

Isu tinorangarira kuti statement_timeout inoshanda kune zvese zvikumbiro zvinotevera kuitira kuti kutengeserana kusatambanuke kune isingagamuchirwe hunhu kana paine data rakawanda mutafura.

#5: Kopa data

Kana iyo tafura isina zvachose, iyo data ichafanirwa kuchengetwa zvakare uchishandisa yekubatsira yechinguva tafura:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Siginecha ON COMMIT DROP zvinoreva kuti pakupera kwekutengeserana, tafura yenguva pfupi ichamira kuvapo, uye hapana chikonzero chekuidzima nemaoko mumamiriro ekubatanidza.

Sezvo isu tichifungidzira kuti hapana yakawanda ye "live" data, kuvhiya uku kunofanira kuitika nekukurumidza.

Zvakanaka, ndizvo chete! Usakanganwa mushure mekupedza kutengeserana mhanya ANALYZE kugadzirisa nhamba dzetafura kana zvichidikanwa.

Kubatanidza script yekupedzisira

Isu tinoshandisa iyi "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;

Zvinoita here kusakopa iyo data kechipiri?Muchidimbu, zvinogoneka kana iyo oid yetafura pachayo isina kusungirirwa kune chero zvimwe zviitiko kubva kudivi reBL kana FK kubva kudivi reDB:

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

Ngatimhanyei script pane iyo tafura tafura uye titarise metrics:

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

Zvose zvakabudirira! Tafura yadzikira neka50 uye ese maUPDATE ari kumhanya nekukurumidza zvakare.

Source: www.habr.com

Kana VACUUM ikakundikana, tinochenesa tafura nemaoko