ProHoster > Блог > Gudanarwa > Lokacin da VACUUM ya gaza, muna tsaftace tebur da hannu

Lokacin da VACUUM ya gaza, muna tsaftace tebur da hannu

VACUUM zai iya "tsabta" daga tebur a cikin PostgreSQL kawai menene ba wanda zai iya gani - wato, babu buƙatu ɗaya mai aiki da ta fara kafin a canza waɗannan bayanan.

Amma menene idan irin wannan nau'in mara kyau (nauyin OLAP na dogon lokaci akan bayanan OLTP) har yanzu yana wanzu? Yaya tsaftace rayayye canza tebur kewaye da doguwar tambaya ba taka rake ba?

Lokacin da VACUUM ya gaza, muna tsaftace tebur da hannu

Kwanciya rake

Da farko, bari mu tantance mene ne matsalar da muke son magancewa da kuma yadda za ta iya tasowa.

Yawancin lokaci wannan yanayin yana faruwa akan karamin teburi, amma wanda yake faruwa da yawa canje-canje. Yawancin lokaci wannan ko daban mita/aggregates/ratings, wanda ake yawan yin UPDATE, ko buffer - jerin gwano don aiwatar da wasu abubuwan da ke gudana akai-akai, bayanan da ake shigar da su akai-akai.

Bari mu yi ƙoƙarin sake haifar da zaɓi tare da ƙididdiga:

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;

Kuma a cikin layi daya, a wata hanyar, dogon buƙatu mai tsayi yana farawa, tattara wasu ƙididdiga masu rikitarwa, amma baya shafar teburin mu:

SELECT pg_sleep(10000);

Yanzu muna sabunta ƙimar ɗaya daga cikin masu lissafin sau da yawa, sau da yawa. Domin tsarkin gwajin, bari mu yi wannan a daban-daban ma'amaloli ta amfani da dblinkyadda zai faru a zahiri:

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

Me ya faru? Me yasa har ma don mafi sauƙin UPDATE na rikodin guda ɗaya lokacin kisa ya ragu da sau 7 - daga 0.524ms zuwa 3.808ms? Kuma ƙimar mu tana ƙara haɓaka sannu a hankali.

Duk laifin MVCC ne.

Yana da duka game da Hanyoyin ciniki na MVCC, wanda ke sa tambaya ta duba duk nau'ikan shigarwar da suka gabata. Don haka bari mu tsaftace teburin mu daga nau'ikan “matattu”:

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, babu wani abu don tsaftacewa! Daidaici Buƙatar gudu tana tsoma baki tare da mu - bayan haka, yana iya wata rana ya so ya juya ga waɗannan juzu'in (menene idan?), kuma ya kamata su kasance a wurinsa. Don haka ko da VACUUM FULL ba zai taimake mu ba.

"Rushewa" tebur

Amma mun san tabbas cewa wannan tambayar baya buƙatar teburin mu. Sabili da haka, har yanzu za mu yi ƙoƙarin dawo da aikin tsarin zuwa isasshen iyaka ta hanyar kawar da duk abin da ba dole ba daga tebur - aƙalla "da hannu", tun da VACUUM ya daina.

Don ƙarin bayani, bari mu dubi misalin yanayin tebur mai buffer. Ma'ana, akwai babban kwarara na INSERT/DELETE, kuma wani lokacin tebur ba komai bane. Amma idan ba fanko ba, dole ne mu ajiye abinda ke ciki na yanzu.

#0: Tantance halin da ake ciki

A bayyane yake cewa zaku iya ƙoƙarin yin wani abu tare da tebur ko da bayan kowane aiki, amma wannan ba shi da ma'ana sosai - ƙimar kulawa za ta kasance a sarari fiye da abubuwan da ake buƙata na abubuwan da ake buƙata.

Bari mu tsara ma'auni - "lokaci ya yi da za a yi aiki" idan:

  • An ƙaddamar da VACUUM da daɗewa
    Muna tsammanin kaya mai nauyi, don haka bari ya kasance Makonni na 60 tun daga ƙarshe [auto] VACUUM.
  • Girman tebur na zahiri ya fi girma fiye da manufa
    Bari mu ayyana shi a matsayin adadin shafuka sau biyu (blockers 8KB) dangane da mafi ƙarancin girman - 1 blk don tsibi + 1 blk ga kowane fihirisa - don tebur mai yuwuwar fanko. Idan muna tsammanin cewa wasu adadin bayanai koyaushe za su kasance a cikin ma'ajin "a al'ada", yana da kyau a daidaita wannan dabarar.

Neman tabbatarwa

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: Har yanzu VACUUM

Ba za mu iya sani ba tukuna ko tambaya mai kama da juna tana tsoma baki sosai a cikinmu - daidai adadin bayanan da suka zama “kushewa” tun lokacin da aka fara. Don haka, lokacin da muka yanke shawarar aiwatar da teburin ko ta yaya, a kowane hali, yakamata mu fara aiwatar da shi VACUUM - ba kamar VACUUM FULL ba, baya tsoma baki tare da tsarin layi daya da ke aiki tare da bayanan rubuta-karanta.

A lokaci guda, zai iya share yawancin abin da muke son cirewa nan da nan. Ee, kuma tambayoyi masu zuwa akan wannan tebur zasu je gare mu by "hot cache", wanda zai rage tsawon lokacin su - kuma, sabili da haka, jimlar lokacin toshe wasu ta hanyar ma'amalar sabis ɗin mu.

#2: Akwai kowa a gida?

Mu duba idan akwai wani abu a cikin tebur kwata-kwata:

TABLE tbl LIMIT 1;

Idan babu rikodin guda ɗaya da ya rage, to za mu iya yin tanadi mai yawa akan sarrafawa ta hanyar yin kawai TAKAICI:

Yana aiki iri ɗaya da umarnin DELETE mara ƙa'ida ga kowane tebur, amma yana da sauri da sauri tunda ba ya duba tebur a zahiri. Bugu da ƙari, nan da nan yana ba da sarari faifai, don haka babu buƙatar yin aikin VACUUM daga baya.

Ko kana buƙatar sake saita ma'aunin tebur (SAKE SANARWA IDO) ya rage naka don yanke shawara.

#3: Kowa - bi da bi!

Tun da muna aiki a cikin yanayi mai matukar fa'ida, yayin da muke nan muna bincika cewa babu shigarwar a cikin tebur, wani zai iya riga ya rubuta wani abu a can. Kada mu rasa wannan bayanin, to menene? Haka ne, muna bukatar mu tabbatar da cewa babu wanda zai iya rubuta ta tabbata.

Don yin wannan muna buƙatar kunnawa SERIALIZABLE- ware don ma'amalarmu (eh, a nan mun fara ciniki) kuma mu kulle tebur "da kyau":

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

Wannan matakin toshe yana ƙaddara ta ayyukan da muke son yi a kai.

#4: Rikicin sha'awa

Mun zo nan kuma muna so mu "kulle" alamar - menene idan wani yana aiki akan ta a wannan lokacin, misali, karanta daga gare ta? Za mu rataya mu jira a fito da wannan block, kuma wasu masu son karantawa za su ci karo da mu...

Don hana faruwar hakan, za mu “saddakar da kanmu” - idan ba za mu iya samun makulli a cikin wani ɗan gajeren lokaci (wanda aka yarda da shi ba), to za mu sami keɓe daga tushe, amma aƙalla ba za mu tsoma baki da yawa ba. wasu.

Don yin wannan, saita canjin zaman kulle_lokaci (don sigar 9.3+) ko/da sanarwa_lokaci. Babban abin da za a tuna shi ne ƙimar sanarwa_timeout tana aiki ne kawai daga sanarwa ta gaba. Wato kamar wannan a cikin gluing - ba zai yi aiki ba: 

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

Domin kada a yi ma'amala da maido da ƙimar "tsohuwar" mai canzawa daga baya, muna amfani da fom ɗin SATA LOCAL, wanda ke iyakance iyakar saitin zuwa ma'amala na yanzu.

Mun tuna cewa bayanin_timeout ya shafi duk buƙatun da ke gaba ta yadda ma'amala ba za ta iya miƙewa zuwa ƙimar da ba za a yarda da ita ba idan akwai bayanai da yawa a cikin tebur.

#5: Kwafi bayanai

Idan teburin bai cika komai ba, za a sake adana bayanan ta amfani da tebur na wucin gadi:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Sa hannu AKAN RUWAN KWAMI yana nufin cewa a lokacin da cinikin ya ƙare, tebur na wucin gadi zai daina wanzuwa, kuma babu buƙatar share shi da hannu a cikin mahallin haɗi.

Tun da mun ɗauka cewa babu yawancin bayanan "rayuwa", wannan aikin ya kamata ya faru da sauri.

To, shi ke nan! Kar a manta bayan kammala cinikin gudu ANALYZE don daidaita kididdigar tebur idan ya cancanta.

Haɗa rubutun ƙarshe

Muna amfani da wannan "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;

Shin zai yiwu ba kwafi bayanan a karo na biyu?A ka'ida, yana yiwuwa idan oid na teburin da kansa ba a ɗaure shi da wasu ayyuka daga ɓangaren BL ko FK daga gefen 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;

Bari mu gudanar da rubutun akan teburin tushe kuma mu duba ma'auni:

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

Komai ya yi aiki! Tebur ya ragu da sau 50 kuma duk UPDATEs suna sake gudu cikin sauri.

source: www.habr.com

Add a comment