🥇Когато VACUUM не успява — почистваме таблицата ръчно

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.524ms до 3.808ms? И рейтинга ни се изгражда все по-бавно.

Виновен е MVCC

Всичко се свежда до механизма MVCC, който кара заявката да прегледа всички предишни версии на записа. Нека да почистим таблицата си от „мъртви“ версии:

VACUUM VERBOSE tbl;

INFO:  вакуумира "public.tbl"
INFO:  "tbl": намерени 0 подлежащи на премахване, 10026 непремахваеми версии на редове в 45 от 45 страници
ДЕТАЙЛ:  10000 мъртви версии на редове все още не могат да бъдат премахнати, най-старият xmin: 597439602

Ой, а за почистване и няма какво! Паралелно в изпълнение заявка ни пречи — все пак тя някога може да реши да се обърне към тези версии (а вдруг?), и те трябва да бъдат достъпни за нея. И именно затова дори VACUUM FULL няма да ни помогне.

«Скъсяваме» таблицата

Но ние със сигурност знаем, че на тази заявка нашата таблица не е нужна. Затова ще опитаме все пак да върнем производителността на системата в приемливи рамки, изхвърляйки от таблицата всичко излишно — поне и „ръчно“, тъй като VACUUM не справя.

За да бъде по-ясно, да разгледаме примера с таблица-буфер. Тоест, има голям поток от INSERT/DELETE и понякога таблицата остава изцяло празна. Но ако не е празна, ние трябва да запазим текущото й съдържание.

#0: Оцениваем ситуацию

Ясно е, че можем да опитваме нещо с таблицата дори след всяка операция, но не е много смислено — разходите за поддръжка ще бъдат явно повече от пропускната способност на целевите заявки.

Да формулираме критериите — «вече е време да действаме», ако:

VACUUM е стартирал преди достатъчно дълго време
Очакваме голямо натоварване, затова нека да бъде 60 секунди от последния [auto]VACUUM.
физическият размер на таблицата е по-голям от целевия
Да го определим като удвоен брой страници (блокове по 8KB) спрямо минималния размер — 1 blk на heap + 1 blk на всеки от индексите — за потенциално-празна таблица. Ако обаче очакваме, че в буфера „стандартно“ ще остане определено количество данни, тази формула е разумно да се отгледа.

Проверка на заявката

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' от (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 — той, за разлика от VACUUM FULL, не пречи на паралелните процеси да работят с данни за четене и запис.

Също така той може веднага да изчисти голяма част от това, което искаме да премахнем. И следващите запитвания към тази таблица ще преминат през "горещия кеш", което ще намали тяхната продължителност — а, следователно, и общото време за блокиране на другите наши обслужващи транзакции.

#2: Есть кто-нибудь дома?

Нека проверим — има ли изобщо нещо в таблицата:

TABLE tbl LIMIT 1;

Ако не е останал нито един запис, можем значително да спестим време за обработка — просто като изпълним TRUNCATE:

Тя работи по същия начин като безусловна команда DELETE за всяка таблица, но е много по-бърза, тъй като всъщност не сканира таблиците. Освен това, тя веднага освобождава дисково пространство, така че не е необходимо да изпълняваме операция VACUUM след нея.

Дали да нулираме брояча на последователността на таблицата (RESTART IDENTITY) — решете сами.

#3: Все — по-очереди!

Тъй като работим при условия на висока конкурентност, докато проверяваме отсъствието на записи в таблицата, някой може вече да е записал нещо. Не трябва да загубим тази информация, което означава — какво? Правилно, трябва да направим така, че никой да не може наистина да записва.

За това ни е необходимо да включим SERIALIZABLE-изолация за нашата транзакция (да, тук стартираме транзакция) и да заключим таблицата "намертво":

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

Този ниво на блокировка е обусловено от операциите, които искаме да извършим върху нея.

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

Искаме да "заключим" таблицата — а ако в този момент някой е активен на нея, например, чете от нея? Ще "увиснем" в очакване на освобождаването на тази блокировка, а другите, които искат да четат, ще се ударят в нас...

За да не се случи това, "жертваме себе си" — ако в определено (допустимо малко) време не успеем да получим блокировката, ще получим от базата изключение, но поне няма да пречим на другите.

За това ще зададем променлива за сесията lock_timeout (за версии 9.3+) или/и statement_timeoutНай-важното е да запомните, че стойността на statement_timeout се прилага само за следващото изявление. Тоест по този начин в свързването — няма да работи:

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

За да не се налага след това да възстановяваме "старата" стойност на променливата, използваме формата SET LOCAL, която ограничава обхвата на настройката до текущата транзакция.

Запомнете, че statement_timeout се отнася за всички последващи заявки, за да не може транзакцията ни да се разтяга до неприемливи стойности, ако данните в таблицата все пак са много.

#5: Копируем данные

Ако таблицата не е напълно празна — данните ще трябва да се запазят отново чрез помощна временна таблица:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Сигнатура ON COMMIT DROP означава, че в момента на завършване на транзакцията, временната таблица ще престане да съществува, и не е нужно да я изтриваме ръчно в контекста на връзката.

Тъй като предполагаме, че "живите" данни не са много, тази операция трябва да премине сравнително бързо.

Е, това е всичко! Не забравяйте, след като приключите с транзакцията да стартирате ANALYZE за нормализиране на статистиката на таблицата, ако е необходимо.

Събираме финалния скрипт

Използваме следния "псевдопитон":

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

Може ли да не копираме данните втори път?В принципе, може, ако на oid на самата таблица не са свързани някакви други дейности от страна на БЛ или FK от страна на БД:

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 пъти и всички UPDATE отново работят бързо.

Източник: habr.com

Когато VACUUM прави пауза — почистваме таблицата ръчно