🥇Когато 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' 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 — в отличие от 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: Конфликт интересов

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

За да не се случи това, „жертваме себе си“ — ако за определено (допустимо малко) време блокировката не успеем да получим, то получаваме от базата exception, но поне не пречим на другите.

За целта задаваме променлива на сесия 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 не може да се справи – почистваме таблицата ръчно