ProHoster > Блог > Administrado > Kiam VACUUM malsukcesas, ni purigas la tablon permane

Kiam VACUUM malsukcesas, ni purigas la tablon permane

VACUUMO povas "purigi" de tabelo en PostgreSQL nur kio neniu povas vidi - tio estas, ne ekzistas eĉ unu aktiva peto, kiu komenciĝis antaŭ ol tiuj rekordoj estis ŝanĝitaj.

Sed kio se tia malagrabla tipo (longtempa OLAP-ŝarĝo sur OLTP-datumbazo) ankoraŭ ekzistas? Kiel purigi aktive ŝanĝanta tablon ĉirkaŭita de longaj demandoj kaj ne paŝi sur rastilon?

Kiam VACUUM malsukcesas, ni purigas la tablon permane

Malfaldante la rastilon

Unue, ni determinu, kio estas la problemo, kiun ni volas solvi, kaj kiel ĝi povas ekesti.

Kutime ĉi tiu situacio okazas sur relative malgranda tablo, но в которой происходит очень много изменений. Kutime ĉi tio aŭ malsama metroj/agregaĵoj/taksoj, sur kiu UPDATE estas ofte efektivigita, aŭ bufro-vico prilabori iun konstante daŭrantan fluon de eventoj, pri kiuj registroj estas konstante INSERT/DELETE.

Ni provu reprodukti la opcion kun taksoj:

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;

А параллельно, в другом соединении, стартует долгий-долгий запрос, собирающий какую-то сложную статистику, но ne influante nian tablon:

SELECT pg_sleep(10000);

Nun ni ĝisdatigas la valoron de unu el la nombriloj multfoje. Por la pureco de la eksperimento, ni faru ĉi tion en apartaj transakcioj uzante 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

Kio okazis? Kial eĉ por la plej simpla ĜISDATIGO de ununura rekordo ekzekuttempo degradita je 7 fojojn — с 0.524ms до 3.808ms? Да и рейтинг наш строится все медленнее и медленнее.

Ĉio estas kulpo de MVCC.

Ĉio temas MVCC-mekanismo, kiu igas la demandon trarigardi ĉiujn antaŭajn versiojn de la eniro. Do ni purigu nian tablon el "mortintaj" versioj:

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

Ho, estas nenio por purigi! Paralele La kuranta peto malhelpas nin — ведь он когда-то может захотеть обратиться к этим версиям (а вдруг?), и они должны быть ему доступны. И поэтому даже VACUUM FULL нам не поможет.

"Falante" la tablon

Sed ni certe scias, ke tiu demando ne bezonas nian tabelon. Sekve, ni ankoraŭ provos redoni la sisteman rendimenton al taŭgaj limoj forigante ĉion nenecesan de la tablo - almenaŭ "mane", ĉar VACUUM cedas.

Por pliklarigi ĝin, ni rigardu la ekzemplon de la kazo de bufrotabelo. Tio estas, estas granda fluo de INSERT/DELETE, kaj foje la tablo estas tute malplena. Sed se ĝi ne estas malplena, ni devas konservi ĝian aktualan enhavon.

#0: Taksi la situacion

Estas klare, ke vi povas provi fari ion per la tabelo eĉ post ĉiu operacio, sed ĉi tio ne havas multe da senco - la bontenado superkompeto klare estos pli granda ol la trafluo de la celaj demandoj.

Ni formulu la kriteriojn - "estas tempo agi" se:

  • VACUUM estis lanĉita antaŭ sufiĉe longa tempo
    Ni atendas pezan ŝarĝon, do lasu ĝin esti 60 sekundoj ekde la lasta [aŭto]VAKUO.
  • fizika tablograndeco estas pli granda ol celo
    Ni difinu ĝin kiel duoble la nombro da paĝoj (8KB-blokoj) rilate al la minimuma grandeco - 1 blk por amaso + 1 blk por ĉiu indekso — для потенциально-пустой таблицы. Если же мы ожидаем, что в буфере «штатно» будет всегда оставаться некоторый объем данных, эту формулу разумно подтюнить.

Konfirma peto

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: Ankoraŭ VAKUUM

Ni ne povas scii anticipe ĉu paralela konsulto signife malhelpas nin - precize kiom da registroj fariĝis "malaktualaj" de kiam ĝi komenciĝis. Sekve, kiam ni decidas iel prilabori la tabelon, ĉiukaze ni unue devas ekzekuti ĝin VACUUMO - male al VACUUM FULL, ĝi ne malhelpas paralelajn procezojn laborantajn kun lego-skribaj datumoj.

Samtempe ĝi povas tuj purigi plejparton de tio, kion ni ŝatus forigi. Jes, kaj postaj demandoj sur ĉi tiu tablo iros al ni по «горячему кэшу», что сократит их продолжительность — а, значит, и суммарное время блокировки других нашей обслуживающей транзакцией.

#2: Ĉu iu estas hejme?

Ni kontrolu ĉu estas io ajn en la tabelo:

TABLE tbl LIMIT 1;

Se ne restas eĉ unu rekordo, tiam ni povas ŝpari multe pri prilaborado per simple faro TRANĈI:

Она действует так же, как безусловная команда DELETE для каждой таблицы, но гораздо быстрее, так как она фактически не сканирует таблицы. Более того, она немедленно высвобождает дисковое пространство, так что выполнять операцию VACUUM после неё не требуется.

Ĉu vi devas rekomencigi la tabelan sinsekvan nombrilon (RESTART IDENTECO) dependas de vi decidi.

#3: Ĉiuj - laŭvice!

Ĉar ni laboras en tre konkurenciva medio, dum ni ĉi tie kontrolas, ke ne estas enskriboj en la tabelo, iu povus jam skribis ion tie. Ni ne devus perdi ĉi tiun informon, do kio? Ĝuste, ni devas certigi, ke neniu povas skribi ĝin certe.

Por fari tion ni devas ebligi SERIALIGebla-izolado por nia transakcio (jes, ĉi tie ni komencas transakcion) kaj ŝlosi la tablon "streze":

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

Ĉi tiu nivelo de blokado estas determinita de la operacioj, kiujn ni volas fari sur ĝi.

#4: Конфликт интересов

Мы тут приходим и хотим табличку «залочить» — а если на ней в этот момент кто-то был активен, например, читал из нее? Мы «повиснем» в ожидании освобождения этой блокировки, а другие желающие почитать упрутся уже в нас…

Por eviti ke tio okazu, ni "oferos nin" - se ni ne povis akiri seruron ene de certa (akcepteble mallonga) tempo, tiam ni ricevos escepton de la bazo, sed almenaŭ ni ne tro enmiksiĝos. aliaj.

Por fari tion, agordu la sean variablon lock_timeout (por versioj 9.3+) aŭ/kaj statement_timeout. La ĉefa afero memorinda estas, ke la valoro statement_timeout validas nur de la sekva deklaro. Tio estas, tiel en gluado - ne funkcios: 

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

Чтобы не заниматься потом восстановлением «старого» значения переменной, используем форму AJRO LOKA, которая ограничивает область действия настройки текущей транзакцией.

Ni memoras, ke statement_timeout validas por ĉiuj postaj petoj tiel ke la transakcio ne povas etendi al neakcepteblaj valoroj se estas multaj datumoj en la tabelo.

#5: Kopiu datumoj

Если таблица оказалась не совсем пустая — данные придется пересохранять через вспомогательную временную табличку:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Subskribo SUR COMMIT DROP signifas, ke en la momento la transakcio finiĝas, la provizora tablo ĉesos ekzisti, kaj ne necesas permane forigi ĝin en la konekto kunteksto.

Ĉar ni supozas, ke ne ekzistas multaj "vivaj" datumoj, ĉi tiu operacio devus okazi sufiĉe rapide.

Nu, jen ĉio! Ne forgesu post plenumi la transakcion kuri ANALYZE por normaligi tabelajn statistikojn se necese.

Kunmeti la finan skripton

Ni uzas ĉi tiun "pseŭdo-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;

Ĉu eblas ne kopii la datumojn duan fojon?Principe, estas eble se la oido de la tablo mem ne estas ligita al iuj aliaj agadoj de la BL-flanko aŭ FK de la DB-flanko:

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

Ni rulu la skripton sur la fonta tabelo kaj kontrolu la metrikojn:

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

Ĉio funkciis! La tablo ŝrumpis je 50 fojojn kaj ĉiuj ĜISDATIgoj denove funkcias rapide.

fonto: www.habr.com

Aldoni komenton