Pulogalamu ya ProHoster > Blog > Ulamuliro > VACUUM ikalephera, timatsuka tebulo pamanja

VACUUM ikalephera, timatsuka tebulo pamanja

VACUUM atha "kuyeretsa" patebulo mu PostgreSQL zomwe palibe amene angawone - ndiko kuti, palibe pempho limodzi logwira ntchito lomwe linayamba zolemba izi zisanasinthidwe.

Koma bwanji ngati mtundu wosasangalatsa wotere (katundu wanthawi yayitali wa OLAP patsamba la OLTP) ukadalipo? Bwanji kuyeretsa mwachangu kusintha tebulo kuzunguliridwa ndi mafunso ataliatali osaponda pachonga?

VACUUM ikalephera, timatsuka tebulo pamanja

Kuyika pansi

Choyamba, tiyeni tione vuto limene tikufuna kuthetsa ndi mmene lingayambire.

Nthawi zambiri izi zimachitika patebulo laling'ono, koma zomwe zimachitika zosintha zambiri. Kawirikawiri izi kapena zosiyana mita/aggregates/magawo, pomwe UPDATE nthawi zambiri amachitidwa, kapena mzere wa buffer kukonza zochitika zomwe zikupitilira, zolembedwa zomwe zimangolowetsa /KUFUTA.

Tiyeni tiyese kupanganso chisankhocho ndi mavoti:

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;

Ndipo mofanana, mu mgwirizano wina, pempho lalitali, lalitali limayamba, kusonkhanitsa ziwerengero zovuta, koma osakhudza tebulo lathu:

SELECT pg_sleep(10000);

Tsopano timasintha mtengo wa imodzi mwazowerengera nthawi zambiri. Kwa chiyero cha kuyesa, tiyeni tichite izi muzochitika zosiyana pogwiritsa ntchito dblinkmomwe zidzakhalire zenizeni:

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

Chinachitika ndi chiyani? Chifukwa chiyani ngakhale kwa UPDATE yosavuta ya mbiri imodzi nthawi yokonzekera idachepetsedwa ndi nthawi 7 - kuchokera 0.524ms mpaka 3.808ms? Ndipo mlingo wathu ukukula pang'onopang'ono.

Zonse ndi zolakwika za MVCC.

Zonse ndi za MVCC ndondomeko, zomwe zimapangitsa kuti funso liyang'ane m'mitundu yonse yam'mbuyomu. Chifukwa chake tiyeni tiyeretse tebulo lathu kuchokera kumitundu "yakufa":

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, palibe choyeretsa! Kufanana Kuthamanga pempho kukusokoneza ife - pambuyo pake, tsiku lina angafune kutembenukira ku matembenuzidwe awa (bwanji ngati?), Ndipo ayenera kupezeka kwa iye. Ndipo chifukwa chake ngakhale VACUUM FULL sichidzatithandiza.

"Kugwetsa" tebulo

Koma tikudziwa motsimikiza kuti funsoli silifuna tebulo lathu. Chifukwa chake, tidzayesetsabe kubweza magwiridwe antchito ku malire oyenera pochotsa zonse zosafunikira patebulo - osachepera "pamanja", popeza VACUUM imasiya.

Kuti timveke bwino, tiyeni tiwone chitsanzo cha tebulo la buffer. Ndiko kuti, pali kutuluka kwakukulu kwa INSERT / DELETE, ndipo nthawi zina tebulo limakhala lopanda kanthu. Koma ngati ilibe kanthu, tiyenera sungani zomwe zili mkati mwake.

#0: Kuyang'ana momwe zinthu ziliri

Zikuwonekeratu kuti mutha kuyesa kuchitapo kanthu ndi tebulo ngakhale mutagwira ntchito iliyonse, koma izi sizomveka - kuwongolera kudzakhala kokulirapo kuposa zomwe zafunsidwa.

Tiyeni tipange njira - "ndi nthawi yoti tichitepo kanthu" ngati:

  • VACUUM idakhazikitsidwa kalekale
    Tikuyembekezera katundu wolemera, choncho zitheke Masekondi a 60 kuyambira [auto]VACUUM yomaliza.
  • Kukula kwa tebulo lakuthupi ndikwambiri kuposa cholinga
    Tiyeni tifotokoze kuwirikiza kawiri kuchuluka kwa masamba (ma block 8KB) okhudzana ndi kukula kochepa - 1 blk pa mulu + 1 blk pa index iliyonse - patebulo lomwe lingakhale lopanda kanthu. Ngati tikuyembekeza kuti kuchuluka kwa deta kudzakhalabe mu buffer "nthawi zambiri", ndizomveka kusintha ndondomekoyi.

Pempho lotsimikizira

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

Sitingadziwe pasadakhale ngati funso lofananira likutisokoneza - ndendende ndi ma rekodi angati "achikale" chiyambireni. Chifukwa chake, tikaganiza zokonza tebulo, mulimonse, tiyenera kuchitapo kanthu VACUUM - mosiyana ndi VACUUM FULL, sichimasokoneza njira zofanana zomwe zimagwira ntchito ndi deta yowerengera.

Nthawi yomweyo, imatha kuyeretsa nthawi yomweyo zambiri zomwe tikufuna kuchotsa. Inde, ndipo mafunso otsatirawa patebuloli apita kwa ife ndi "hot cache", zomwe zidzachepetsa nthawi yawo - ndipo, chifukwa chake, nthawi yonse yoletsa ena ndi ntchito yathu yotumizira.

#2: Kodi alipo kunyumba?

Tiyeni tiwone ngati pali chilichonse patebulopo:

TABLE tbl LIMIT 1;

Ngati palibe mbiri imodzi yomwe yatsala, ndiye kuti tingapulumutse zambiri pakukonza mwa kungochita KUSINTHA:

Zimagwiranso ntchito ngati lamulo la DELETE lopanda malire patebulo lililonse, koma limathamanga kwambiri popeza silimasanthula matebulo. Komanso, nthawi yomweyo imamasula malo a disk, kotero palibe chifukwa chochitira VACUUM pambuyo pake.

Kaya mukufunika kukonzanso kauntala yotsatizana patebulo (RESTART IDENTITY) zili ndi inu kusankha.

#3: Aliyense - sinthanani!

Popeza timagwira ntchito m'malo opikisana kwambiri, tili pano tikuwona kuti palibe zolembedwa patebulo, wina atha kulemba kale kena kalikonse pamenepo. Sitiyenera kutaya chidziwitso ichi, ndiye chiyani? Ndiko kulondola, tiyenera kutsimikizira kuti palibe amene angalembe motsimikiza.

Kuti tichite izi tiyenera kuyatsa SERIALIZABLE-kudzipatula pazochita zathu (inde, apa tikuyamba kugulitsa) ndikutseka tebulo "mwamphamvu":

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

Mulingo wotsekereza uwu umatsimikiziridwa ndi ntchito zomwe tikufuna kuchita pamenepo.

#4: Kusagwirizana kwa chidwi

Timabwera kuno ndipo tikufuna "kutseka" chizindikiro - bwanji ngati wina anali wokangalika panthawiyo, mwachitsanzo, kuwerenga? "Tidzapachika" kuyembekezera kuti chipikachi chitulutsidwe, ndipo ena omwe akufuna kuwerenga adzathamangira mwa ife ...

Kuti izi zisachitike, "tidzadzipereka tokha" - ngati sitinathe kupeza loko mkati mwa nthawi inayake (yovomerezeka yaifupi), ndiye kuti tidzalandira kusiyana ndi maziko, koma sitingasokoneze kwambiri. ena.

Kuti muchite izi, ikani kusintha kwa gawo lock_timeout (zamitundu 9.3+) kapena/ndi statement_timeout. Chinthu chachikulu kukumbukira ndi chakuti mtengo wa statement_timeout umagwira ntchito kuchokera ku mawu otsatirawa. Ndiye kuti, monga chonchi mu gluing - sizingagwire ntchito: 

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

Pofuna kuti tisagwirizane ndi kubwezeretsa mtengo "wakale" wa zosinthika pambuyo pake, timagwiritsa ntchito mawonekedwe KHALANI MALO, zomwe zimachepetsa kuchuluka kwa zoikamo pazochitika zamakono.

Tikukumbukira kuti statement_timeout imagwiranso ntchito pazopempha zonse zotsatila kuti ntchitoyo isapitirire kumitengo yosavomerezeka ngati pali zambiri patebulo.

#5: Koperani deta

Ngati tebulo liribe kanthu, deta iyenera kusungidwanso pogwiritsa ntchito tebulo losakhalitsa lothandizira:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Siginecha ON COMMIT DROP zikutanthauza kuti panthawi yomwe ntchitoyo ikutha, tebulo losakhalitsa lidzasiya kukhalapo, ndipo palibe chifukwa chochichotsa pamanja pazolumikizana.

Popeza timaganiza kuti palibe zambiri "zamoyo" zambiri, ntchitoyi iyenera kuchitika mofulumira kwambiri.

Chabwino, ndizo zonse! Osayiwala mukamaliza kugulitsa thamangani ANALYZE kusintha ziwerengero za tebulo ngati kuli kofunikira.

Kuyika pamodzi script yomaliza

Timagwiritsa ntchito "pseudo-python" iyi:

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

Kodi ndizotheka kusatengera deta kachiwiri?M'malo mwake, ndizotheka ngati oid ya tebulo palokha siyimangiriridwa kuzinthu zina zilizonse kuchokera ku mbali ya BL kapena FK kuchokera ku mbali ya 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;

Tiyeni tiyendetse script pa tebulo loyambira ndikuwona ma 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

Zonse zinayenda bwino! Gome lacheperachepera ka 50 ndipo UPDATEs zonse zikuthamanganso.

Source: www.habr.com

Kuwonjezera ndemanga