ProHoster > Blog > Идораи > Вақте ки ВАКУМ кор намекунад, мо мизро дастӣ тоза мекунем

Вақте ки ВАКУМ кор намекунад, мо мизро дастӣ тоза мекунем

VACUUM метавонад аз ҷадвал дар PostgreSQL танҳо чӣ "тоза" кунад касе дида наметавонад - яъне ягон дархости фаъол вуҷуд надорад, ки пеш аз тағир додани ин сабтҳо оғоз шудааст.

Аммо чӣ мешавад, агар чунин намуди ногувор (боркунии дарозмуддати OLAP дар пойгоҳи додаҳои OLTP) ҳанӯз вуҷуд дошта бошад? Чӣ хел мизи фаъол тағйирёбанда тоза дар иҳотаи пурсишҳои тӯлонӣ ва ба раке қадам намезанед?

Вақте ки ВАКУМ кор намекунад, мо мизро дастӣ тоза мекунем

Гузоштани раке

Аввалан, биёед муайян кунем, ки мушкилоте, ки мо мехоҳем ҳал кунем ва чӣ гуна он метавонад ба миён ояд.

Одатан ин ҳолат рӯй медиҳад дар болои мизи нисбатан хурд, вале дар он чо ба амал меояд бисёр дигаргуниҳо. Одатан ин ё дигар метр / агрегатҳо / рейтингҳо, ки дар он UPDATE аксар вақт иҷро карда мешавад, ё буфер-навбат барои коркарди баъзе ҷараёни доимии рӯйдодҳо, ки сабтҳои онҳо доимо INSERT/DELETE карда мешаванд.

Биёед кӯшиш кунем, ки вариантро бо рейтингҳо дубора тавлид кунем:

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;

Ва дар баробари ин, дар робитаи дигар, дархости дароз ва тӯлонӣ оғоз меёбад, ки баъзе омори мураккабро ҷамъоварӣ мекунад, аммо ба мизи мо таъсир намерасонад:

SELECT pg_sleep(10000);

Ҳоло мо арзиши яке аз ҳисобкунакҳоро чанд маротиба навсозӣ мекунем. Барои тозагии таҷриба, биёед ин корро кунем дар амалиёти алоҳида бо истифода аз dblinkдар асл чӣ гуна рӯй хоҳад дод:

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 як сабти мухлати ичро 7 баробар кам шуд — аз 0.524 мс то 3.808 мс? Ва рейтинги мо торафт сусттар сохта мешавад.

Ҳамааш айби MVCC аст.

Ин ҳама дар бораи Механизми MVCC, ки боиси он мегардад, ки дархост тамоми версияҳои қаблии вурудро аз назар гузаронад. Пас биёед мизи худро аз версияҳои "мурда" тоза кунем:

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

Оҳ, ҳеҷ чиз барои тоза кардан нест! Параллел Дархости иҷрошаванда ба мо халал мерасонад - охир, ӯ метавонад рӯзе мехоҳад ба ин версияҳо муроҷиат кунад (агар чӣ?) ва онҳо бояд барои ӯ дастрас бошанд. Ва аз ин рӯ, ҳатто VACUUM FULL ба мо кӯмак намекунад.

«Фур кардани» миз

Аммо мо аниқ медонем, ки ин дархост ба ҷадвали мо ниёз надорад. Аз ин рӯ, мо то ҳол кӯшиш хоҳем кард, ки кори системаро ба ҳадди мувофиқ баргардонем ва ҳама чизи нолозимро аз ҷадвал хориҷ кунем - ҳадди аққал "дастӣ", зеро VACUUM таслим мекунад.

Барои равшантар кардани он, биёед ба мисоли ҳолати ҷадвали буферӣ назар кунем. Яъне, ҷараёни зиёди INSERT/DELETE вуҷуд дорад ва баъзан ҷадвал комилан холӣ аст. Аммо агар он холӣ набошад, мо бояд мундариҷаи ҷории онро захира кунед.

№ 0: Арзёбии вазъият

Маълум аст, ки шумо метавонед ҳатто пас аз ҳар як амалиёт бо ҷадвал коре кунед, аммо ин чандон маъно надорад - хароҷоти нигоҳдорӣ бешубҳа аз интиқоли дархостҳои мақсаднок зиёдтар хоҳад буд.

Биёед меъёрҳоро таҳия кунем - "вақти амал кардан расидааст", агар:

  • ВАКУМ кайҳо пеш ба кор андохта шуда буд
    Мо бори вазнинро интизорем, пас бигзор он бошад Сонияҳои 60 аз охирин [авто] ВАКУУМ.
  • андозаи ҷадвали ҷисмонӣ аз ҳадаф калонтар аст
    Биёед онро ҳамчун ду баробар зиёд шумораи саҳифаҳо (блокҳои 8KB) нисбат ба андозаи ҳадди ақал муайян кунем - 1 блк барои теппа + 1 блк барои ҳар як индекс - барои ҷадвали эҳтимолан холӣ. Агар мо интизор шавем, ки миқдори муайяни додаҳо ҳамеша дар буфер "муқаррарӣ" боқӣ мемонад, тағир додани ин формула оқилона аст.

Дархости тасдиқ

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: Ҳанӯз ВАКУУМ

Мо пешакӣ намедонем, ки оё дархости мувозӣ ба мо ба таври назаррас халал мерасонад - маҳз чӣ қадар сабтҳо аз оғози он "кӯҳнашуда" шудаанд. Аз ин рӯ, вақте ки мо тасмим гирифтем, ки ҷадвалро бо ягон роҳ коркард кунем, дар ҳар сурат, мо бояд аввал онро иҷро кунем VACUUM - бар хилофи VACUUM FULL, он ба равандҳои параллелӣ, ки бо маълумоти хондан ва навиштан кор мекунанд, халал намерасонад.

Ҳамзамон, он метавонад фавран аксари чизҳоеро, ки мо нест кардан мехоҳем, тоза кунад. Бале, ва дархостҳои минбаъдаи ин ҷадвал ба мо хоҳанд рафт аз ҷониби "кэш гарм", ки давомнокии онҳоро кам мекунад - ва аз ин рӯ, вақти умумии бастани дигарон тавассути транзаксияи хидматрасонии мо.

# 2: Оё касе дар хона аст?

Биёед тафтиш кунем, ки оё дар ҷадвал ягон чиз вуҷуд дорад:

TABLE tbl LIMIT 1;

Агар ягон сабт боқӣ намонад, пас мо метавонем бо анҷом додани коркард бисёр чизҳоро сарфа кунем ТРУНКАТ:

Он барои ҳар як ҷадвал ҳамчун фармони бешарти DELETE амал мекунад, аммо хеле тезтар аст, зеро он аслан ҷадвалҳоро скан намекунад. Ғайр аз он, он дарҳол фазои дискро холӣ мекунад, бинобар ин пас аз анҷом додани амалиёти ВАКУУМ лозим нест.

Оё шумо бояд ҳисобкунаки пайдарпайии ҷадвалро аз нав танзим кунед (RESTART IDENTITY) ба шумо вобаста аст.

# 3: Ҳама - навбат гиред!

Азбаски мо дар муҳити рақобати шадид кор мекунем, дар ҳоле ки мо дар ин ҷо тафтиш мекунем, ки дар ҷадвал ягон сабт мавҷуд нест, касе метавонист дар он ҷо чизе нависад. Мо набояд ин маълумотро аз даст диҳем, пас чӣ? Дуруст аст, мо бояд боварӣ ҳосил кунем, ки касе онро аниқ навишта наметавонад.

Барои ин мо бояд фаъол созем СЕРИАЛИСТОН-изолятсияи транзаксияи мо (бале, дар ин ҷо мо транзаксияро оғоз мекунем) ва ҷадвалро "зиқ" қуфл кунед:

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

Ин сатҳи басташавӣ бо амалҳое, ки мо мехоҳем дар он иҷро кунем, муайян карда мешавад.

# 4: Бархӯрди манфиатҳо

Мо ба ин ҷо меоем ва мехоҳем аломатро "қуфл кунем" - чӣ мешавад, агар касе дар он лаҳза дар он фаъол бошад, масалан, хондан аз он? Мо мунтазири баровардани ин блокро «овехта» мекунем ва дигарон, ки мехоҳанд хонанд, рӯ ба рӯ мешаванд...

Барои пешгирӣ кардани ин, мо "худро қурбон мекунем" - агар мо дар муддати муайян (қобили қобили қабул) қулфро ба даст оварда натавонем, пас мо аз пойгоҳ истисно мегирем, аммо ҳадди аққал мо аз ҳад зиёд дахолат намекунем. дигарон.

Барои ин, тағирёбандаи сессияро таъин кунед lock_timeout (барои версияҳои 9.3+) ё/ва изҳорот_вахт. Чизи асосие, ки бояд дар хотир дошт, ин аст, ки арзиши statement_timeout танҳо аз изҳороти навбатӣ татбиқ мешавад. Яъне дар часпак ин тавр - кор намекунад: 

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

Барои он ки дертар бо барқарор кардани арзиши "кӯҳна"-и тағирёбанда сарукор нашавем, мо формаро истифода мебарем МАҲАЛЛӢ таъин кунед, ки доираи танзимотро ба амалиёти ҷорӣ маҳдуд мекунад.

Мо дар хотир дорем, ки statement_timeout ба ҳама дархостҳои минбаъда дахл дорад, то транзаксия наметавонад ба арзишҳои қобили қабул дароз нашавад, агар дар ҷадвал маълумотҳои зиёд мавҷуд бошанд.

# 5: Маълумотро нусхабардорӣ кунед

Агар ҷадвал пурра холӣ набошад, маълумот бояд бо истифода аз ҷадвали муваққатии ёрирасон дубора захира карда шавад:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Имзо ДАР БОРАИ ТАРКИ маънои онро дорад, ки дар лаҳзаи анҷоми транзаксия, ҷадвали муваққатӣ мавҷудияти худро қатъ мекунад ва лозим нест, ки онро дар контексти пайваст дастӣ нест кунед.

Азбаски мо тахмин мезанем, ки маълумоти "зинда" зиёд нест, ин амалиёт бояд хеле зуд сурат гирад.

Хуб, ин ҳама! Пас аз анҷом додани транзаксия фаромӯш накунед Таҳлил кунед ки агар зарур бошад, статистикаи чадвалро ба эътидол оварад.

Якҷоя кардани скрипти ниҳоӣ

Мо ин "псевдо-питон" -ро истифода мебарем:

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

Оё мумкин аст, ки маълумотро бори дуюм нусхабардорӣ накунед?Аслан, мумкин аст, агар худи оиди ҷадвал ба ягон фаъолият аз ҷониби BL ё FK аз ҷониби 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;

Биёед скриптро дар ҷадвали манбаъ иҷро кунем ва ченакҳоро тафтиш кунем:

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

Ҳама чиз кор кард! Ҷадвал 50 маротиба кам шудааст ва ҳама НАВОРХО боз босуръат кор мекунанд.

Манбаъ: will.com

Илова Эзоҳ